func main() { var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell var err error var RawNbr int = 6 file = xlsx.NewFile() sheet = file.AddSheet("Sheet1") // 每列标题 text := [6]string{"ID", "鱼类型", "出现帧", "脚本ID", "X", "Y"} for i := 0; i < RawNbr; i++ { if i == 0 { row = sheet.AddRow() } cell = row.AddCell() cell.Value = text[i] } files, err := ListDir("../data/timeline", ".dat") for j := 0; j < len(files); j++ { write(files[j], sheet, 6) } err = file.Save("../outexcel/巡游鱼路径.xlsx") if err != nil { fmt.Printf(err.Error()) } }
func main() { var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell var err error var RawNbr int = 4 file = xlsx.NewFile() sheetName := [2]string{"Big", "Small"} datafile := [2]string{"../data/moveline/big", "../data/moveline/small"} for i, v := range sheetName { sheet = file.AddSheet(v) // 每列标题 text := [4]string{"ID", "X", "Y", "Angle"} for i := 0; i < RawNbr; i++ { if i == 0 { row = sheet.AddRow() } cell = row.AddCell() cell.Value = text[i] } files, _ := ListDir(datafile[i], ".dat") for j := 0; j < len(files); j++ { write(files[j], sheet, RawNbr) } } fmt.Println("正在向excel写数据......") err = file.Save("../outexcel/单条鱼路径.xlsx") if err != nil { fmt.Printf(err.Error()) } }
func (client *TrelloClient) formatterExcel(cards []*TrelloCardSearchResult) (err error) { var ( file *xlsx.File sheet *xlsx.Sheet row *xlsx.Row cell *xlsx.Cell ) client.config.QuoteChar = "" // we do not need quoting in excel file = xlsx.NewFile() if sheet, err = file.AddSheet("Sheet1"); err != nil { return } for _, card := range cards { row = sheet.AddRow() for _, column := range client.buildOutputLine(card) { cell = row.AddCell() cell.Value = column } } if err != nil { fmt.Printf(err.Error()) } err = file.Write(os.Stdout) if err != nil { fmt.Printf(err.Error()) } return err }
// 写每一个*.dat数据 func write(infile string, sheet *xlsx.Sheet, rawNbr int) { fmt.Println("正在处理", infile) var id int start := strings.LastIndexAny(infile, "\\") id, _ = strconv.Atoi(infile[start+1 : len(infile)-4]) fmt.Println(id) var cell *xlsx.Cell var row *xlsx.Row file, err := os.Open(infile) if err != nil { fmt.Println("Failed to open the input file ", infile) return } defer file.Close() br := bufio.NewReader(file) var indexi int = 0 _, _, _ = br.ReadLine() for { line, isPrefix, err1 := br.ReadLine() if err1 != nil { if err1 != io.EOF { err = err1 } break } if isPrefix { fmt.Println("A too long line, seems unexpected.") return } str := string(line) // Convert []byte to string if len(str) < 5 { break } split := str[1 : len(str)-1] array := strings.Split(split, ",") row = sheet.AddRow() cell = row.AddCell() cell.SetInt(id) indexi = 0 for _, v := range array { if indexi >= rawNbr-1 { break } indexi = indexi + 1 cell = row.AddCell() value, _ := strconv.Atoi(v) cell.SetInt(value) } } return }
// Xlsx writes the day statistics in xlsx format func (fsd *FlightStatDay) Xlsx(s *xlsx.Sheet) { r := s.AddRow() c := r.AddCell() c.SetDate(fsd.Date) c.NumFmt = "dd.mm.yyyy" r.AddCell().SetInt(fsd.Flights) r.AddCell().SetFloatWithFormat(fsd.Airtime.Minutes(), "0.00") }
func addRow(sheet *xlsx.Sheet, headers ...string) { row := sheet.AddRow() for _, header := range headers { cell := row.AddCell() cell.Value = header } }
//AddCell sheet func AddCell(sheet *xlsx.Sheet, row, col int) *xlsx.Cell { // hhh for row >= len(sheet.Rows) { sheet.AddRow() } for col >= len(sheet.Rows[row].Cells) { sheet.Rows[row].AddCell() } return sheet.Cell(row, col) }
func createSheet2(sheet *xlsx.Sheet) { r := helpcase.GetAllHelpcaseDetail() var row *xlsx.Row var cell *xlsx.Cell row = sheet.AddRow() cell = row.AddCell() cell.Value = "編號" cell = row.AddCell() cell.Value = "報導標題" cell = row.AddCell() cell.Value = "刊登日期" cell = row.AddCell() cell.Value = "星期" cell = row.AddCell() cell.Value = "按讚數" cell = row.AddCell() cell.Value = "段落數" cell = row.AddCell() cell.Value = "報導字數" cell = row.AddCell() cell.Value = "報導內圖片數" cell = row.AddCell() cell.Value = "報導URL" cell = row.AddCell() cell.Value = "報導內容全部" for _, helpcase := range r { row = sheet.AddRow() cell = row.AddCell() cell.Value = helpcase.SerialNo cell = row.AddCell() cell.Value = helpcase.Title cell = row.AddCell() cell.Value = helpcase.Date cell = row.AddCell() cell.SetFormula("weekday(\"" + helpcase.Date + "\",2)") cell = row.AddCell() cell.SetInt(helpcase.LikeCount) cell = row.AddCell() cell.SetInt(helpcase.ParagraphCount) cell = row.AddCell() cell.SetInt(helpcase.WordCount) cell = row.AddCell() cell.SetInt(helpcase.ImgCount) cell = row.AddCell() cell.SetFormula("HYPERLINK(\"" + helpcase.DetailUrl + "\",\"" + helpcase.DetailUrl + "\")") cell = row.AddCell() cell.Value = helpcase.Content } }
func (s *UtilTestSuite) TestCheckGetHeader(c *C) { GetCheckResultHeader() checkResult := CheckResult{} var file *xlsx.File var sheet *xlsx.Sheet file = xlsx.NewFile() sheet, _ = file.AddSheet("Sheet1") sheet.AddRow() checkResult.WriteToRow(sheet.AddRow()) c.Log(checkResult) }
// Xlsx calls each day and then writes the month statistics in xlsx format func (fsm *FlightStatMonth) Xlsx(s *xlsx.Sheet) { days := []int{} for d, _ := range fsm.Day { days = append(days, d) } sort.Ints(days) for _, d := range days { fsd := fsm.Day[d] fsd.Xlsx(s) } r := s.AddRow() r.AddCell().SetString(fmt.Sprintf("Total %s", fsm.Date.Format("January 2006"))) r.AddCell().SetInt(fsm.Flights) r.AddCell().SetFloatWithFormat(fsm.Airtime.Minutes(), "0.00") }
// Xlsx calls each month and then writes the year statistics in xlsx format func (fsy *FlightStatYear) Xlsx(s *xlsx.Sheet) { month := []int{} for m, _ := range fsy.Month { month = append(month, int(m)) } sort.Ints(month) for _, m := range month { fsm := fsy.Month[time.Month(m)] fsm.Xlsx(s) } r := s.AddRow() r.AddCell().SetString(fmt.Sprintf("Total %d", fsy.Year.Year())) r.AddCell().SetInt(fsy.Flights) r.AddCell().SetFloatWithFormat(fsy.Airtime.Minutes(), "0.00") }
func createSheet1(sheet *xlsx.Sheet) { r := helpcase.GetAllHelpcase() var row *xlsx.Row var cell *xlsx.Cell row = sheet.AddRow() cell = row.AddCell() cell.Value = "編號" cell = row.AddCell() cell.Value = "報導標題" cell = row.AddCell() cell.Value = "刊登日期" cell = row.AddCell() cell.Value = "星期" cell = row.AddCell() cell.Value = "狀態" cell = row.AddCell() cell.Value = "累計(元)" cell = row.AddCell() cell.Value = "捐款明細" font := &xlsx.Font{Color: "blue", Underline: true} style := xlsx.NewStyle() style.Font = *font for _, helpcase := range r { row = sheet.AddRow() cell = row.AddCell() cell.Value = helpcase.SerialNo cell = row.AddCell() cell.Value = helpcase.Title cell = row.AddCell() cell.Value = helpcase.Date cell = row.AddCell() cell.SetFormula("weekday(\"" + helpcase.Date + "\",2)") cell = row.AddCell() cell.Value = helpcase.Status cell = row.AddCell() cell.SetInt(helpcase.Amount) cell.NumFmt = "#,##0 ;(#,##0)" cell = row.AddCell() cell.SetStyle(style) cell.SetFormula("HYPERLINK(\"http://search.appledaily.com.tw/charity/projdetail/proj/" + helpcase.SerialNo + "\",\"明細\")") } }
func main() { var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell var err error file = xlsx.NewFile() sheet = file.AddSheet("Sheet1") row = sheet.AddRow() cell = row.AddCell() cell.Value = "I am a cell!" err = file.Save("d:/Desktop/demo_write.xlsx") if err != nil { fmt.Printf(err.Error()) } }
func subjectGrid(sheet *xlsx.Sheet, g group.Group, subj *subject.Subject, natYear string) { pg := g.ProgressGrid(subj, natYear) row := sheet.AddRow() row = sheet.AddRow() blankCell(row) newCell(row, "Progress Grid: "+subj.Subj, newStyle("Title", "None", "None", "Left")) row = sheet.AddRow() row = sheet.AddRow() blankCell(row) newCell(row, "KS2", newStyle("Bold", "None", "Bottom", "Left")) for _, grd := range pg.Grades { newCell(row, grd, newStyle("Bold", "None", "Bottom", "Center")) } newCell(row, "VA", newStyle("Bold", "None", "Bottom", "Center")) for i, ks2 := range pg.KS2 { row := sheet.AddRow() blankCell(row) newCell(row, ks2, newStyle("Bold", "None", "None", "Left")) for j := range pg.Grades { pgCell := pg.Cells[i][j] switch { case pg.CellVA[i][j] < -0.33: newInt(row, len(pgCell.Students), newStyle("Default", "Red", "None", "Center")) case pg.CellVA[i][j] > 0.67: newInt(row, len(pgCell.Students), newStyle("Default", "Green", "None", "Center")) default: newInt(row, len(pgCell.Students), newStyle("Default", "Yellow", "None", "Center")) } } newFloat(row, pg.RowVA[i], "+0.00;-0.00;0.00", newStyle("Default", "None", "None", "Center")) } row = sheet.AddRow() blankCell(row) newCell(row, "Total", newStyle("Bold", "None", "None", "None")) for i := range pg.Grades { newInt(row, pg.Counts[i], newStyle("Bold", "None", "None", "Center")) } newFloat(row, g.SubjectVA(subj.Subj).VA, "+0.00;-0.00;0.00", newStyle("Bold", "None", "None", "Center")) }
func test2AddSheet() { var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell file, _ = xlsx.OpenFile("MyXLSXFile.xlsx") sheet = file.AddSheet("addSheet") row = sheet.AddRow() cell = row.AddCell() cell.Value = "addSheet000101" cell = row.AddCell() cell.Value = "addSheet中文1" err := file.Save("MyXLSXFile.xlsx") if err != nil { fmt.Printf(err.Error()) } }
func test2() { var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell var err error file, _ = xlsx.OpenFile("MyXLSXFile.xlsx") sheet = file.Sheet["Sheet1"] row = sheet.AddRow() cell = row.AddCell() cell.Value = "000101" cell = row.AddCell() cell.Value = "中文1" err = file.Save("MyXLSXFile1.xlsx") if err != nil { fmt.Printf(err.Error()) } }
func addHeader(sheet *xlsx.Sheet) { var cell *xlsx.Cell var row *xlsx.Row row = sheet.AddRow() cell = row.AddCell() cell.SetString("筆數") cell = row.AddCell() cell.Value = "捐款人姓名" cell = row.AddCell() cell.Value = "累計(元) " cell = row.AddCell() cell.Value = "捐款明細 / 捐款日期" cell = row.AddCell() cell.Value = "星期" cell = row.AddCell() cell.Value = "捐款日期與報導日期間隔差時間" cell = row.AddCell() cell.Value = "捐款人姓名> 4個字" }
func test1() { var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell var err error file = xlsx.NewFile() sheet = file.AddSheet("Sheet1") row = sheet.AddRow() cell = row.AddCell() cell.Value = "000101" cell = row.AddCell() cell.Value = "中文" err = file.Save("MyXLSXFile.xlsx") if err != nil { fmt.Printf(err.Error()) } }
func addEmbassyInfoByDetails(sheet *xlsx.Sheet, value string, continentName string, embassyNameCN string, embassyNameEN string) { row := sheet.AddRow() var cellFlag *xlsx.Cell var cellContinentName *xlsx.Cell var cellCountryName *xlsx.Cell var cellEmbassyNameCN *xlsx.Cell var cellEmbassyNameEN *xlsx.Cell var cellMobile *xlsx.Cell var cellAddress *xlsx.Cell var cellAddressalid *xlsx.Cell var cellValue *xlsx.Cell cellFlag = row.AddCell() cellFlag.Value = "B" cellContinentName = row.AddCell() cellContinentName.Value = continentName cellCountryName = row.AddCell() cellCountryName.Value = "" cellEmbassyNameCN = row.AddCell() cellEmbassyNameCN.Value = embassyNameCN cellEmbassyNameEN = row.AddCell() cellEmbassyNameEN.Value = embassyNameEN cellMobile = row.AddCell() cellMobile.Value = "" cellAddress = row.AddCell() cellAddress.Value = "" cellAddressalid = row.AddCell() cellAddressalid.Value = "" cellValue = row.AddCell() cellValue.Value = value }
//экспорт данных datas в файл xlsx используя сортировку keys - массив указывающий в каком порядке выводить в таблицу func savetoxlsx0(namef string, datas map[string]DataTelMans, keys []string) { var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell var err error file = xlsx.NewFile() sheet, err = file.AddSheet("лог звонков") if err != nil { fmt.Println(err.Error()) } //заголовок таблицы row = sheet.AddRow() // добавить строку cell = row.AddCell() // добавить ячейку в текущей строке cell.Value = "выгружено: " + time.Now().String() row = sheet.AddRow() // добавить строку titletab := []string{"ФИО РГ", "номер телефона", "ФИО менеджера", "всего продолжит-ть", "всего кол-во звонков", "кол-во уникальных телефонов", "кол-во результ. звонков", "продолжительность уникальных", "средняя время звонка"} for i := 0; i < len(titletab); i++ { cell = row.AddCell() // добавить ячейку в текущей строке cell.Value = titletab[i] } for i := 0; i < len(keys); i++ { key := keys[i] row = sheet.AddRow() cell = row.AddCell() cell.Value = datas[key].fio_rg cell = row.AddCell() cell.Value = key cell = row.AddCell() cell.Value = datas[key].fio_man cell = row.AddCell() cell.Value = sec_to_s(datas[key].totalsec) cell = row.AddCell() cell.Value = strconv.Itoa(datas[key].totalzv) cell = row.AddCell() cell.Value = strconv.Itoa(datas[key].kolunik) cell = row.AddCell() cell.Value = strconv.Itoa(datas[key].kolresult) cell = row.AddCell() cell.Value = sec_to_s(datas[key].secresult) cell = row.AddCell() cell.Value = sec_to_s(devidezero(datas[key].totalsec, datas[key].totalzv)) } err = file.Save(namef) if err != nil { fmt.Println(err.Error()) } }
func (client *TrelloClient) memberFormatterExcel(members []*TrelloMember) (err error) { var ( file *xlsx.File sheet *xlsx.Sheet row *xlsx.Row cell *xlsx.Cell ) client.config.QuoteChar = "" // we do not need quoting in excel file = xlsx.NewFile() if sheet, err = file.AddSheet("Sheet1"); err != nil { return } header := strings.Split(client.config.SearchResultFields, ",") row = sheet.AddRow() for _, column := range header { cell = row.AddCell() cell.Value = strings.Title(column) } for _, member := range members { row = sheet.AddRow() for _, column := range client.buildMemberSlice(member) { cell = row.AddCell() cell.Value = column } } if err != nil { fmt.Printf(err.Error()) } err = file.Write(os.Stdout) if err != nil { fmt.Printf(err.Error()) } return err }
func addWarningInfo(sheet *xlsx.Sheet, value string, title string, time string, source string, url string) { row := sheet.AddRow() var cellTitle *xlsx.Cell var cellTime *xlsx.Cell var cellSource *xlsx.Cell var cellUrl *xlsx.Cell var cellValue *xlsx.Cell cellTitle = row.AddCell() cellTitle.Value = title cellTime = row.AddCell() cellTime.Value = time cellSource = row.AddCell() cellSource.Value = source cellUrl = row.AddCell() cellUrl.Value = url cellValue = row.AddCell() cellValue.Value = value }
func addEmbassyInfo(sheet *xlsx.Sheet, value string, continentName string, embassyNameCN string, embassyNameEN string) { row := sheet.AddRow() var cellFlag *xlsx.Cell var cellContinentName *xlsx.Cell var cellEmbassyNameCN *xlsx.Cell var cellEmbassyNameEN *xlsx.Cell var cellValue *xlsx.Cell cellFlag = row.AddCell() cellFlag.Value = "A" cellContinentName = row.AddCell() cellContinentName.Value = continentName cellEmbassyNameCN = row.AddCell() cellEmbassyNameCN.Value = embassyNameCN cellEmbassyNameEN = row.AddCell() cellEmbassyNameEN.Value = embassyNameEN cellValue = row.AddCell() cellValue.Value = value }
/************************ excel 输出 ***************************/ func init() { Output["excel"] = func(self *Collector, dataIndex int) { defer func() { if err := recover(); err != nil { Log.Println(err) } }() var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell var err error folder1 := "result/data" folder2 := folder1 + "/" + self.startTime.Format("2006年01月02日 15时04分05秒") filename := folder2 + "/" + util.FileNameReplace(self.Spider.GetName()+"_"+self.Spider.GetKeyword()+" "+fmt.Sprintf("%v", self.sum[0])+"-"+fmt.Sprintf("%v", self.sum[1])) + ".xlsx" // 创建文件 file = xlsx.NewFile() // 添加分类数据工作表 for Name, Rule := range self.GetRules() { // 跳过不输出的数据 if len(Rule.GetOutFeild()) == 0 { continue } // 添加工作表 sheet = file.AddSheet(util.ExcelSheetNameReplace(Name)) // 写入表头 row = sheet.AddRow() for _, title := range Rule.GetOutFeild() { cell = row.AddCell() cell.Value = title } cell = row.AddCell() cell.Value = "当前链接" cell = row.AddCell() cell.Value = "上级链接" cell = row.AddCell() cell.Value = "下载时间" num := 0 //小计 for _, datacell := range self.DockerQueue.Dockers[dataIndex] { if datacell["RuleName"].(string) == Name { row = sheet.AddRow() for _, title := range Rule.GetOutFeild() { cell = row.AddCell() vd := datacell["Data"].(map[string]interface{}) if v, ok := vd[title].(string); ok || vd[title] == nil { cell.Value = v } else { cell.Value = util.JsonString(vd[title]) } } cell = row.AddCell() cell.Value = datacell["Url"].(string) cell = row.AddCell() cell.Value = datacell["ParentUrl"].(string) cell = row.AddCell() cell.Value = datacell["DownloadTime"].(string) num++ } } // Log.Printf("[任务:%v | 关键词:%v | 小类:%v] 输出 %v 条数据!!!\n", self.Spider.GetName(), self.Spider.GetKeyword(), Name, num) } // 创建/打开目录 f2, err := os.Stat(folder2) if err != nil || !f2.IsDir() { if err := os.MkdirAll(folder2, 0777); err != nil { Log.Printf("Error: %v\n", err) } } // 保存文件 err = file.Save(filename) if err != nil { Log.Println(err) } } }
func (this *DataBrowserController) genExcelFile(header []string, selectedColumn []string, datas []tk.M, dataSummary []tk.M, DisplaySumList []SumList) (string, error) { today := time.Now().UTC() fileName := "files/databrowser_" + today.Format("2006-01-02T150405") + ".xlsx" var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell var err error file = xlsx.NewFile() sheet, _ = file.AddSheet("Sheet1") for i, data := range datas { if i == 0 { row = sheet.AddRow() for _, hdr := range header { cell = row.AddCell() cell.Value = hdr } } row = sheet.AddRow() for _, field := range selectedColumn { cell = row.AddCell() cell.SetValue(this.getExcelValue(data, field)) } } if DisplaySumList != nil && len(DisplaySumList) > 0 { var summary = dataSummary[0] row = sheet.AddRow() cell = row.AddCell() cell.SetValue("Average") cell.Merge(len(DisplaySumList)-1, 0) row = sheet.AddRow() for _, i := range DisplaySumList { cell = row.AddCell() if i.tipe == "string" || i.tipe == "date" { cell.SetValue("-") } else { field := strings.Replace(i.field, ".", "", -1) + "avg" cell.SetValue(summary.Get(field)) } } row = sheet.AddRow() cell = row.AddCell() cell.Merge(len(DisplaySumList)-1, 0) cell.SetValue("Total") row = sheet.AddRow() for _, i := range DisplaySumList { cell = row.AddCell() if i.tipe == "string" || i.tipe == "date" { cell.SetValue("-") } else { field := strings.Replace(i.field, ".", "", -1) + "sum" cell.SetValue(summary.Get(field)) } } } err = file.Save(fileName) // file := xlsx.NewFile() // sheet := file.AddSheet("Sheet1") // // header := []string{"Matnr", "Matkl"} // // for i, _ := range datas { // // if i == 0 { // // rowHeader := sheet.AddRow() // // for _, hdr := range header { // // cell := rowHeader.AddCell() // // cell.Value = hdr // // } // // } // // } // err := file.Save(fileName) return fileName, err }
func writesheet(project *lair.Project, outfile string) { header := []string{ "#", "Title", "CVSS", "Rating", "Description", "Evidence", "Solution", "CVEs", "References", "Host", "Hostname(s)", "Port", "Service Note", "Issue Note(s)", } var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell file = xlsx.NewFile() sheet, err := file.AddSheet(project.Name) if err != nil { log.Printf(err.Error()) } row = sheet.AddRow() for _, h := range header { cell = row.AddCell() cell.Value = h } for count, issue := range project.Issues { var issuenote string for _, note := range issue.Notes { issuenote += note.Title issuenote += note.Content + "\n" } for _, host := range issue.Hosts { var refs []string for _, ref := range issue.References { refs = append(refs, ref.Link) } row = sheet.AddRow() cell = row.AddCell() cell.SetInt(count + 1) cell = row.AddCell() cell.Value = issue.Title cell = row.AddCell() cell.SetFloat(issue.CVSS) cell = row.AddCell() cell.Value = issue.Rating cell = row.AddCell() cell.Value = issue.Description cell = row.AddCell() cell.Value = issue.Evidence cell = row.AddCell() cell.Value = issue.Solution cell = row.AddCell() cell.Value = strings.Join(issue.CVEs, "\n") cell = row.AddCell() cell.Value = strings.Join(refs, "\n") cell = row.AddCell() cell.Value = host.IPv4 cell = row.AddCell() cell.Value = gethostname(host.IPv4, &project.Hosts) cell = row.AddCell() cell.SetInt(host.Port) cell = row.AddCell() cell.Value = getcomment(&project.Hosts, issue.Title, host.IPv4, host.Port) cell = row.AddCell() cell.Value = issuenote } } err = file.Save(outfile) if err != nil { log.Fatal("Fatal: Unable to write file") } }
func createDonation() { var currentYear = 0 var currentMonth = 0 r := helpcase.GetAllHelpcase() var file *xlsx.File var sheet1 *xlsx.Sheet log.Println("donation export begin") for _, hp := range r { dt := helpcase.GetAllDonationDetail(hp.SerialNo) if len(dt) == 0 { continue } test, _ := time.Parse("2006/1/2", hp.Date) var isCurrent = isCurrentYearMonthMatch(currentYear, currentMonth, test.Year(), int(test.Month())) if !isCurrent { if file != nil { var err error if currentMonth < 10 { err = file.Save(outfolder + "/donation_" + strconv.Itoa(currentYear) + "0" + strconv.Itoa(currentMonth) + ".xlsx") } else { err = file.Save(outfolder + "/donation_" + strconv.Itoa(currentYear) + strconv.Itoa(currentMonth) + ".xlsx") } if err != nil { fmt.Printf(err.Error()) } } file = xlsx.NewFile() currentYear = test.Year() currentMonth = int(test.Month()) } sheet1 = file.AddSheet(hp.SerialNo) var publishDate, _ = time.Parse("2006/1/2", hp.Date) var cell *xlsx.Cell var row *xlsx.Row addHeader(sheet1) for _, donator := range dt { row = sheet1.AddRow() cell = row.AddCell() cell.SetString(donator.SerialNo) cell = row.AddCell() cell.Value = donator.Name cell = row.AddCell() cell.SetInt(donator.Amount) cell.NumFmt = "#,##0 ;(#,##0)" cell = row.AddCell() cell.Value = donator.Date cell = row.AddCell() cell.SetFormula("weekday(\"" + donator.Date + "\",2)") var dDate, _ = time.Parse("2006/1/2", donator.Date) duration := dDate.Sub(publishDate) cell = row.AddCell() cell.Value = strconv.Itoa(int(duration.Hours() / 24)) cell = row.AddCell() if donator.LongFour == 1 { cell.Value = "YES" } else { cell.Value = "NO" } } row = sheet1.AddRow() row.AddCell() row = sheet1.AddRow() cell = row.AddCell() cell.Value = "捐款頁面的URL" cell = row.AddCell() cell.SetFormula("HYPERLINK(\"http://search.appledaily.com.tw/charity/projdetail/proj/" + hp.SerialNo + "\",\"http://search.appledaily.com.tw/charity/projdetail/proj/" + hp.SerialNo + "\")") row = sheet1.AddRow() cell = row.AddCell() cell.Value = "出刊日期" cell = row.AddCell() cell.Value = hp.Date cell = row.AddCell() cell.Value = "專案狀況" cell = row.AddCell() cell.Value = hp.Status row = sheet1.AddRow() cell = row.AddCell() cell.Value = "捐款總計" cell = row.AddCell() cell.SetInt(hp.Amount) cell.NumFmt = "#,##0 ;(#,##0)" cell = row.AddCell() cell.Value = "捐款筆數" cell = row.AddCell() cell.Value = strconv.Itoa(len(dt)) + "筆" } if file != nil { var err error if currentMonth < 10 { err = file.Save(outfolder + "/donation_" + strconv.Itoa(currentYear) + "0" + strconv.Itoa(currentMonth) + ".xlsx") } else { err = file.Save(outfolder + "/donation_" + strconv.Itoa(currentYear) + strconv.Itoa(currentMonth) + ".xlsx") } if err != nil { fmt.Printf(err.Error()) } } log.Println("donation export done") }
/************************ excel 输出 ***************************/ func (self *Collector) excel(dataIndex int) { defer func() { if err := recover(); err != nil { Log.Println(err) } }() var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell var err error folder1 := "data" _folder2 := strings.Split(cache.StartTime.Format("2006-01-02 15:04:05"), ":") folder2 := _folder2[0] + "时" + _folder2[1] + "分" + _folder2[2] + "秒" folder2 = folder1 + "/" + folder2 filename := folder2 + "/" + self.Spider.GetName() + "_" + self.Spider.GetKeyword() + " " + strconv.Itoa(self.sum[0]) + "-" + strconv.Itoa(self.sum[1]) + ".xlsx" file = xlsx.NewFile() // 添加分类数据工作表 for Name, Rule := range self.GetRules() { // 跳过不输出的数据 if len(Rule.GetOutFeild()) == 0 { continue } sheet = file.AddSheet(Name) row = sheet.AddRow() for _, title := range Rule.GetOutFeild() { cell = row.AddCell() cell.Value = title } cell = row.AddCell() cell.Value = "当前链接" cell = row.AddCell() cell.Value = "上级链接" cell = row.AddCell() cell.Value = "下载时间" num := 0 //小计 for _, datacell := range self.DockerQueue.Dockers[dataIndex] { if datacell["RuleName"].(string) == Name { row = sheet.AddRow() for _, title := range Rule.GetOutFeild() { cell = row.AddCell() vd := datacell["Data"].(map[string]interface{}) if v, ok := vd[title].(string); ok || vd[title] == nil { cell.Value = v } else { j, _ := json.Marshal(vd[title]) cell.Value = string(j) } } cell = row.AddCell() cell.Value = datacell["Url"].(string) cell = row.AddCell() cell.Value = datacell["ParentUrl"].(string) cell = row.AddCell() cell.Value = datacell["DownloadTime"].(string) num++ } } // Log.Printf("[任务:%v | 关键词:%v | 小类:%v] 输出 %v 条数据!!!\n", self.Spider.GetName(), self.Spider.GetKeyword(), Name, num) } // 创建/打开目录 f2, err := os.Stat(folder2) if err != nil || !f2.IsDir() { if err := os.MkdirAll(folder2, 0777); err != nil { Log.Printf("Error: %v\n", err) } } // 保存文件 err = file.Save(filename) if err != nil { Log.Println(err) } }
func EnsureStatistic(toPath string) error { var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell var err error file = xlsx.NewFile() type Source struct { Name string `bson:"_id"` } sources := []Source{} dbsWithOrders := getDbsWithOrders() log.Printf("Databases with orders is: %+v", dbsWithOrders) for _, dbName := range dbsWithOrders { db := d.NewMainDb(config.Main.Database.ConnString, dbName) sheet, err = file.AddSheet(dbName) if err != nil { log.Printf("Error at edding sheet to file") continue } db.Orders.Collection.Pipe( []bson.M{bson.M{"$group": bson.M{"_id": "$source"}}}, ).All(&sources) for _, source := range sources { row = sheet.AddRow() cell = row.AddCell() cell.Value = source.Name row = sheet.AddRow() for _, h_cell := range []string{"Телефон", "Статус", "Дата", "Стоимость", "Адрес подачи", "Адрес назначения", "Позывной автомобиля", "ФИО водителя"} { cell = row.AddCell() cell.Value = h_cell } orders, err := db.Orders.GetBy(bson.M{"source": source.Name}) if err != nil { log.Printf("Error at getting orders from %+v is: %v", config.Main.Database, err) return err } for _, order := range orders { log.Printf("adding row for order %+v", order) user, u_err := db.Users.GetUserById(order.Whom) if u_err != nil || user == nil { log.Printf("No user found at id: %v", order.Whom) continue } row = sheet.AddRow() ph_c := row.AddCell() ph_c.SetString(user.Phone) stat_c := row.AddCell() if state, ok := t.InfinityStatusesName[order.OrderState]; ok { stat_c.SetString(state) } else { stat_c.SetString("Не определен") } time_c := row.AddCell() time_c.SetDateTime(order.When) if len(order.OrderData.Content) > 0 { log.Printf("we have additional data of order %v", order.OrderId) cost := order.OrderData.Get("Cost") if cost != nil { cost_c := row.AddCell() cost_c.SetInt(order.OrderData.Get("Cost").(int)) deliv_c := row.AddCell() deliv_c.SetString(order.OrderData.Get("DeliveryStr").(string)) dest_c := row.AddCell() dest_c.SetString(order.OrderData.Get("DestinationsStr").(string)) car_c := row.AddCell() car_c.SetString(order.OrderData.Get("Car").(string)) driver_c := row.AddCell() driver_c.SetString(order.OrderData.Get("Drivers").(string)) } } log.Printf("Added row: %+v", row) } } } fName := fmt.Sprintf("%v/statistic.xlsx", toPath) file.Save(fName) return nil }
func init() { defer func() { // 获取输出方式列表 for out, _ := range Output { OutputLib = append(OutputLib, out) } util.StringsSort(OutputLib) }() /************************ excel 输出 ***************************/ Output["excel"] = func(self *Collector, dataIndex int) { defer func() { if err := recover(); err != nil { Log.Println(err) } }() var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell var err error folder1 := "result/data" folder2 := folder1 + "/" + self.startTime.Format("2006年01月02日 15时04分05秒") filename := folder2 + "/" + util.FileNameReplace(self.Spider.GetName()+"_"+self.Spider.GetKeyword()+" "+fmt.Sprintf("%v", self.sum[0])+"-"+fmt.Sprintf("%v", self.sum[1])) + ".xlsx" // 创建文件 file = xlsx.NewFile() // 添加分类数据工作表 for Name, Rule := range self.GetRules() { // 跳过不输出的数据 if len(Rule.GetOutFeild()) == 0 { continue } // 添加工作表 sheet = file.AddSheet(util.ExcelSheetNameReplace(Name)) // 写入表头 row = sheet.AddRow() for _, title := range Rule.GetOutFeild() { cell = row.AddCell() cell.Value = title } cell = row.AddCell() cell.Value = "当前链接" cell = row.AddCell() cell.Value = "上级链接" cell = row.AddCell() cell.Value = "下载时间" num := 0 //小计 for _, datacell := range self.DockerQueue.Dockers[dataIndex] { if datacell["RuleName"].(string) == Name { row = sheet.AddRow() for _, title := range Rule.GetOutFeild() { cell = row.AddCell() vd := datacell["Data"].(map[string]interface{}) if v, ok := vd[title].(string); ok || vd[title] == nil { cell.Value = v } else { cell.Value = util.JsonString(vd[title]) } } cell = row.AddCell() cell.Value = datacell["Url"].(string) cell = row.AddCell() cell.Value = datacell["ParentUrl"].(string) cell = row.AddCell() cell.Value = datacell["DownloadTime"].(string) num++ } } // Log.Printf("[任务:%v | 关键词:%v | 小类:%v] 输出 %v 条数据!!!\n", self.Spider.GetName(), self.Spider.GetKeyword(), Name, num) } // 创建/打开目录 f2, err := os.Stat(folder2) if err != nil || !f2.IsDir() { if err := os.MkdirAll(folder2, 0777); err != nil { Log.Printf("Error: %v\n", err) } } // 保存文件 err = file.Save(filename) if err != nil { Log.Println(err) } } /************************ CSV 输出 ***************************/ Output["csv"] = func(self *Collector, dataIndex int) { defer func() { if err := recover(); err != nil { Log.Println(err) } }() folder1 := "result/data" folder2 := folder1 + "/" + self.startTime.Format("2006年01月02日 15时04分05秒") filenameBase := folder2 + "/" + util.FileNameReplace(self.Spider.GetName()+"_"+self.Spider.GetKeyword()+" "+fmt.Sprintf("%v", self.sum[0])+"-"+fmt.Sprintf("%v", self.sum[1])) // 创建/打开目录 f2, err := os.Stat(folder2) if err != nil || !f2.IsDir() { if err := os.MkdirAll(folder2, 0777); err != nil { Log.Printf("Error: %v\n", err) } } // 按数据分类创建文件 for Name, Rule := range self.GetRules() { // 跳过不输出的数据 if len(Rule.GetOutFeild()) == 0 { continue } file, err := os.Create(filenameBase + " (" + util.FileNameReplace(Name) + ").csv") if err != nil { Log.Println(err) continue } file.WriteString("\xEF\xBB\xBF") // 写入UTF-8 BOM w := csv.NewWriter(file) th := Rule.GetOutFeild() th = append(th, []string{"当前链接", "上级链接", "下载时间"}...) w.Write(th) num := 0 //小计 for _, datacell := range self.DockerQueue.Dockers[dataIndex] { if datacell["RuleName"].(string) == Name { row := []string{} for _, title := range Rule.GetOutFeild() { vd := datacell["Data"].(map[string]interface{}) if v, ok := vd[title].(string); ok || vd[title] == nil { row = append(row, v) } else { row = append(row, util.JsonString(vd[title])) } } row = append(row, datacell["Url"].(string)) row = append(row, datacell["ParentUrl"].(string)) row = append(row, datacell["DownloadTime"].(string)) w.Write(row) num++ } } // 发送缓存数据流 w.Flush() // 关闭文件 file.Close() // 输出报告 // Log.Printf("[任务:%v | 关键词:%v | 小类:%v] 输出 %v 条数据!!!\n", self.Spider.GetName(), self.Spider.GetKeyword(), Name, num) } } /************************ MongoDB 输出 ***************************/ Output["mgo"] = func(self *Collector, dataIndex int) { session, err := mgo.Dial(config.DB_URL) //连接数据库 if err != nil { panic(err) } defer session.Close() session.SetMode(mgo.Monotonic, true) db := session.DB(config.DB_NAME) //数据库名称 collection := db.C(config.DB_COLLECTION) //如果该集合已经存在的话,则直接返回 for i, count := 0, len(self.DockerQueue.Dockers[dataIndex]); i < count; i++ { err = collection.Insert((interface{})(self.DockerQueue.Dockers[dataIndex][i])) if err != nil { panic(err) } } } /************************ HBase 输出 ***************************/ var master = cache.Task.Master var port = ":" + fmt.Sprintf("%v", cache.Task.Port) var hbaseSocket = teleport.New().SetPackHeader("tentinet") var hbaseOnce sync.Once Output["hbase"] = func(self *Collector, dataIndex int) { hbaseOnce.Do(func() { hbaseSocket.Client(master, port) }) for i, count := 0, len(self.DockerQueue.Dockers[dataIndex]); i < count; i++ { hbaseSocket.Request(self.DockerQueue.Dockers[dataIndex][i], "log") } } /************************ Mysql 输出 ***************************/ Output["mysql"] = func(self *Collector, dataIndex int) { db, err := sql.Open("mysql", config.MYSQL_USER+":"+config.MYSQL_PW+"@tcp("+config.MYSQL_HOST+")/"+config.MYSQL_DB+"?charset=utf8") if err != nil { fmt.Println(err) } defer db.Close() var newMysql = new(myTable) for Name, Rule := range self.GetRules() { //跳过不输出的数据 if len(Rule.GetOutFeild()) == 0 { continue } newMysql.setTableName("`" + self.Spider.GetName() + "-" + Name + "-" + self.Spider.GetKeyword() + "`") for _, title := range Rule.GetOutFeild() { newMysql.addColumn(title) } newMysql.addColumn("当前连接", "上级链接", "下载时间"). create(db) num := 0 //小计 for _, datacell := range self.DockerQueue.Dockers[dataIndex] { if datacell["RuleName"].(string) == Name { for _, title := range Rule.GetOutFeild() { vd := datacell["Data"].(map[string]interface{}) if v, ok := vd[title].(string); ok || vd[title] == nil { newMysql.addRow(v) } else { newMysql.addRow(util.JsonString(vd[title])) } } newMysql.addRow(datacell["Url"].(string), datacell["ParentUrl"].(string), datacell["DownloadTime"].(string)). update(db) num++ } } newMysql = new(myTable) } } }