func (p *program) run() { var id int var q string var daySend int = 0 conf, err := os.Stat(Path + "config.json") config := getJSON(Path + "config.json") cS := config.Settings cF := config.Filters days := []int{cS.Day1, cS.Day2, cS.Day3, cS.Day4, cS.Day5, cS.Day6, cS.Day7} emailUser := &EmailUser{ cS.MailFrom, decodePass(cS.MailPass), cS.MailServer, cS.MailPort, } usersList := "" lim := "" for k, _ := range cF.Users { a := cF.Users[k] if a == 1 { usersList += lim + k } lim = ", " } doorsList := "" lim = "" for k, _ := range cF.Doors { a, _ := cF.Doors[k] if a == 1 { doorsList += lim + k } lim = ", " } summaryFilters := "" if usersList != "" && cS.UsersFlag == 1 { summaryFilters += " AND l.USERID IN (" + usersList + ")" } if doorsList != "" && cS.DoorsFlag == 1 { summaryFilters += " AND l.APID IN (" + doorsList + ")" } db, err := sql.Open("mysql", cS.ServerUser+ ":"+ decodePass(cS.ServerPass)+ "@tcp("+ cS.ServerIp+ ":"+ strconv.Itoa(cS.ServerPort)+ ")/") checkErr(err) getLastId(db, &id) q = `SELECT l.LOGTIME, l.CLIENTIP, CASE WHEN ISNULL(u.NAME) THEN '<Нет>' ELSE (u.NAME) END AS UNAME, CASE WHEN ISNULL(d.NAME) THEN '<Нет>' ELSE (d.NAME) END AS DNAME, l.TEXT, p.NAME as OPNAME FROM ` + "`tc-db-main`" + `.userlog AS l LEFT OUTER JOIN ` + "`tc-db-main`" + `.devices AS d ON l.APID=d.ID LEFT OUTER JOIN ` + "`tc-db-main`" + `.personal as u ON l.OBJID=u.ID LEFT OUTER JOIN ` + "`tc-db-main`" + `.personal as p ON l.USERID=p.ID WHERE l.ID > ? ` + summaryFilters + ` ORDER BY l.LOGTIME` for true { today := int(time.Time.Weekday(time.Now())) nowHour := int(time.Time.Hour(time.Now())) nowMin := int(time.Time.Minute(time.Now())) check, err := os.Stat(Path + "config.json") if conf.ModTime() != check.ModTime() { go p.run() return } if today == 0 { today = 7 } for _, v := range days { if v == today && cS.StartHour == nowHour && cS.StartMin == nowMin && daySend != today { weekAgo := time.Time.AddDate(time.Now(), 0, 0, -7) now := time.Now() qxls := "" qxls = `SELECT l.LOGTIME, l.CLIENTIP, CASE WHEN ISNULL(u.NAME) THEN '<Нет>' ELSE (u.NAME) END AS UNAME, CASE WHEN ISNULL(d.NAME) THEN '<Нет>' ELSE (d.NAME) END AS DNAME, l.TEXT, p.NAME as OPNAME FROM ` + "`tc-db-main`" + `.userlog AS l LEFT OUTER JOIN ` + "`tc-db-main`" + `.devices AS d ON l.APID=d.ID LEFT OUTER JOIN ` + "`tc-db-main`" + `.personal as u ON l.OBJID=u.ID LEFT OUTER JOIN ` + "`tc-db-main`" + `.personal as p ON l.USERID=p.ID WHERE l.LOGTIME BETWEEN '` + weekAgo.Format(time.RFC3339) + `' AND '` + now.Format(time.RFC3339) + `' ` + summaryFilters + ` ORDER BY l.LOGTIME` rowsXls, err := db.Query(qxls) checkErr(err) var logStringsXls []LogString for rowsXls.Next() { LSX := LogString{} err := rowsXls.Scan(&LSX.LogTime, &LSX.ClientIp, &LSX.UName, &LSX.DName, &LSX.Text, &LSX.OpName) checkErr(err) logStringsXls = append(logStringsXls, LSX) } var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell var buf bytes.Buffer file = xlsx.NewFile() sheet = file.AddSheet("Отчёт") row = sheet.AddRow() cell = row.AddCell() cell.Value = fmt.Sprintf("Отчёт за %02d.%02d - %02d.%02d", int(weekAgo.Month()), weekAgo.Day(), int(now.Month()), now.Day()) row = sheet.AddRow() for _, v := range logStringsXls { row = sheet.AddRow() cell = row.AddCell() cell.Value = v.LogTime if t := cS.EcU; t == 1 { cell = row.AddCell() cell.Value = v.OpName } if t := cS.EcIP; t == 1 { cell = row.AddCell() cell.Value = v.ClientIp } if t := cS.EcD; t == 1 { cell = row.AddCell() cell.Value = v.DName } if t := cS.EcO; t == 1 { cell = row.AddCell() cell.Value = v.UName } cell = row.AddCell() cell.Value = v.Text } err = file.Save(Path + "spnx.xlsx") checkErr(err) xlsxFile, _ := ioutil.ReadFile(Path + "spnx.xlsx") checkErr(err) encoded := base64.StdEncoding.EncodeToString(xlsxFile) lineMaxLength := 500 nbrLines := len(encoded) / lineMaxLength for i := 0; i < nbrLines; i++ { buf.WriteString(encoded[i*lineMaxLength:(i+1)*lineMaxLength] + "\n") } buf.WriteString(encoded[nbrLines*lineMaxLength:]) header := make(map[string]string) header["From"] = emailUser.Username header["To"] = cS.MailTo header["Subject"] = "Отчёт" header["MIME-Version"] = "1.0" header["Content-Type"] = "application/csv; name=\"spnx.xlsx\"" header["Content-Transfer-Encoding"] = "base64" header["Content-Disposition"] = "attachment; filename=\"spnx.xlsx\"" message := "" for k, v := range header { message += fmt.Sprintf("%s: %s\r\n", k, v) } message += "\r\n" + fmt.Sprintf("%s\r\n", buf.String()) SendMail(emailUser.Username, cS.MailTo, emailUser.EmailServer, strconv.Itoa(emailUser.Port), message, ) daySend = today } } lastId := id rows, err := db.Query(q, id) checkErr(err) getLastId(db, &id) var logStrings []LogString for rows.Next() { LS := LogString{} err := rows.Scan(&LS.LogTime, &LS.ClientIp, &LS.UName, &LS.DName, &LS.Text, &LS.OpName) checkErr(err) logStrings = append(logStrings, LS) } if lastId != id && lastId != 0 { text := "" for _, v := range logStrings { text += fmt.Sprintf("%s", v.LogTime) if t := cS.EcU; t == 1 { text += fmt.Sprintf("\tПользователь: %s", v.OpName) } if t := cS.EcIP; t == 1 { text += fmt.Sprintf(" (%s)", v.ClientIp) } if t := cS.EcD; t == 1 { text += fmt.Sprintf("\tТочка прохода: %s", v.DName) } if t := cS.EcO; t == 1 { text += fmt.Sprintf("\tОбъект: %s", v.UName) } text += fmt.Sprintf("\t%s\n", v.Text) } header := make(map[string]string) header["From"] = emailUser.Username header["To"] = cS.MailTo header["Subject"] = "Отчёт" message := "" for k, v := range header { message += fmt.Sprintf("%s: %s\r\n", k, v) } message += "\r\n" + text SendMail(emailUser.Username, cS.MailTo, emailUser.EmailServer, strconv.Itoa(emailUser.Port), message, ) } time.Sleep(cS.Delay * time.Second) } }
func appendCSVtoXLSX(csvPath string, xlsxFile *xlsx.File, delimiter string) error { csvFile, err := os.Open(csvPath) if err != nil { return fmt.Errorf("failed to open csv %s: %s", csvPath, err) } defer csvFile.Close() reader := csv.NewReader(csvFile) if len(delimiter) > 0 { reader.Comma = rune(delimiter[0]) } else { reader.Comma = rune('\t') } sheet, err := xlsxFile.AddSheet(csvPath) if err != nil { return fmt.Errorf("failed to add sheet %s: %s", csvPath, err) } fields, err := reader.Read() for err == nil { row := sheet.AddRow() for _, field := range fields { cell := row.AddCell() cell.Value = field } fields, err = reader.Read() } if err != nil && err != io.EOF { return fmt.Errorf("failed to load row %s", err) } return nil }
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 }
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 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()) } }
//экспорт данных 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 (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) }
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 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 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 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 (d *Dataset) addXlsxSheetToFile(file *xlsx.File, sheetName string) error { sheet, err := file.AddSheet(sheetName) if err != nil { return nil } back := d.Records() for i, r := range back { row := sheet.AddRow() for _, c := range r { cell := row.AddCell() cell.Value = c if i == 0 { cell.GetStyle().Font.Bold = true } } } return nil }
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 (this *HistoryController) CreateExcel() { path := beego.AppConfig.String("xlspath") 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("Sheet1") if err != nil { beego.Error(err) return } 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 = "包装规格(kg/桶)" 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 = "备注" for _, v := range Recode { row = sheet.AddRow() cell = row.AddCell() cell.Value = GetCustomerObj(v.ClientId).Name cell = row.AddCell() cell.Value = v.OrderId product := GetProductObj(v.ProductId) cell = row.AddCell() cell.Value = product.PId cell = row.AddCell() cell.Value = product.PName cell = row.AddCell() cell.Value = product.PType cell = row.AddCell() cell.Value = FormatTime(v.CTime) cell = row.AddCell() cell.Value = fmt.Sprintf("%0.1f", v.Count) cell = row.AddCell() cell.Value = fmt.Sprintf("%0.4f", v.Price) cell = row.AddCell() cell.Value = fmt.Sprintf("%0.4f", v.Price*v.Count) cell = row.AddCell() cell.Value = v.Remark } now := time.Now().Format("2006-01-02_15-04") err = file.Save(path + "myrecod " + now + ".xlsx") if err != nil { beego.Error(err) return } this.Redirect("/history", 302) }
/************************ excel 输出 ***************************/ func init() { Output["excel"] = func(self *Collector, dataIndex int) { defer func() { if err := recover(); err != nil { logs.Log.Error("%v", err) } }() var file *xlsx.File var sheets = make(map[string]*xlsx.Sheet) var row *xlsx.Row var cell *xlsx.Cell var err error // 创建文件 file = xlsx.NewFile() // 添加分类数据工作表 for _, datacell := range self.DockerQueue.Dockers[dataIndex] { var subNamespace = util.FileNameReplace(self.subNamespace(datacell)) if _, ok := sheets[subNamespace]; !ok { // 添加工作表 sheet, err := file.AddSheet(subNamespace) if err != nil { logs.Log.Error("%v", err) continue } sheets[subNamespace] = sheet // 写入表头 row = sheets[subNamespace].AddRow() for _, title := range self.GetRule(datacell["RuleName"].(string)).GetOutFeild() { cell = row.AddCell() cell.Value = title } cell = row.AddCell() cell.Value = "当前链接" cell = row.AddCell() cell.Value = "上级链接" cell = row.AddCell() cell.Value = "下载时间" } row = sheets[subNamespace].AddRow() for _, title := range self.GetRule(datacell["RuleName"].(string)).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) } folder := config.COMM_PATH.TEXT + "/" + self.startTime.Format("2006年01月02日 15时04分05秒") filename := fmt.Sprintf("%v/%v__%v-%v.xlsx", folder, util.FileNameReplace(self.namespace()), self.sum[0], self.sum[1]) // 创建/打开目录 f2, err := os.Stat(folder) if err != nil || !f2.IsDir() { if err := os.MkdirAll(folder, 0777); err != nil { logs.Log.Error("Error: %v\n", err) } } // 保存文件 err = file.Save(filename) if err != nil { logs.Log.Error("%v", err) } } }
func main() { row := 21 col := 6 biddata := make([][]string, row) for i := range biddata { biddata[i] = make([]string, col) } var file *xlsx.File // var sheet *xlsx.Sheet // var excel_row *xlsx.Row // var cell *xlsx.Cell var err_excel error file = xlsx.NewFile() _, err_excel = file.AddSheet("Sheet1") if err_excel != nil { fmt.Printf(err_excel.Error()) } dbinfo := fmt.Sprintf("postgres://%s:%s@%s/%s?sslmode=disable", DB_USER, DB_PASSWORD, DB_ADDR, DB_NAME) fmt.Println(dbinfo) db, err_db := sql.Open("postgres", dbinfo) if err_db != nil { fmt.Printf("Coudn't connect to BID: %s", err_db) } defer db.Close() rows, err_select := db.Query("select * from monit.monit_report") if err_select != nil { fmt.Printf("Coudn't run query to BID: %s", err_select) } i := 0 for rows.Next() { var resourceId string var bidupdated time.Time var beginDateTime string var endDateTime string var bid_data_min string var bid_data_max string err_fetch := rows.Scan(&resourceId, &bidupdated, &beginDateTime, &endDateTime, &bid_data_min, &bid_data_max) if err_fetch != nil { fmt.Printf("Coudn't fetch data from BID: %s", err_fetch) } biddata[i] = []string{resourceId, bidupdated.Format(time.RFC3339), beginDateTime, endDateTime, bid_data_min, bid_data_max} i++ } matrix := [][]string{ []string{"dp.hydrometcentre.esimo.ru:8080", "RU_Hydrometcentre_42", "RU_Hydrometcentre_46", "RU_Hydrometcentre_60", "RU_Hydrometcentre_61", "RU_Hydrometcentre_62", "RU_Hydrometcentre_63", "RU_Hydrometcentre_64", "RU_Hydrometcentre_65", "RU_Hydrometcentre_66", "RU_Hydrometcentre_68", "RU_Hydrometcentre_69", "RU_Hydrometcentre_122"}, []string{"dpms.meteo.ru", "RU_RIHMI-WDC_67", "RU_RIHMI-WDC_1196", "RU_RIHMI-WDC_1198", "RU_RIHMI-WDC_1172", "RU_RIHMI-WDC_1197", "RU_RIHMI-WDC_1242", "RU_RIHMI-WDC_1195"}, } csvfile, err_csv := os.Create("ir_stat.csv") if err_csv != nil { panic("Error creating CSV file") } defer csvfile.Close() is_stat, err_is := os.Open("data_csv.txt") if err_is != nil { fmt.Println("Error during reading IS report") } defer is_stat.Close() is_reader := csv.NewReader(is_stat) is_report, _ := is_reader.ReadAll() writer := csv.NewWriter(csvfile) writer.Write([]string{"sep=,"}) t := time.Now() title := t.Format(time.RFC850) + "\n" writer.Write([]string{"Время генерации справки: " + title}) writer.Write([]string{"Идентификатор ИР", "ПД", "СИ", "БИД (время обновления)", "БИД", "ГИС"}) // slices for i := 0; i < len(matrix); i++ { addr := "http://" + matrix[i][0] + "/dpms/controller?action=getResourceCache&resourceId=" for j := 1; j <= len(matrix[i][1:]); j++ { resource := matrix[i][j] fmt.Println(resource) res, err := http.Get(addr + resource) if err != nil { panic(err.Error()) } body, err := ioutil.ReadAll(res.Body) var root Root err_parse := xml.Unmarshal([]byte(body), &root) if err_parse != nil { fmt.Printf("error: %root", err_parse) } beginDateTime := root.Metadata.TemporalExtent.BeginDateTime endDateTime := root.Metadata.TemporalExtent.EndDateTime // get min/max dates from GIS layer_min_date, layer_max_date := getWMSLayersDates(resource) var layer_temporal string if layer_min_date == layer_max_date { layer_temporal = layer_min_date } else if layer_min_date == "" { layer_temporal = layer_max_date } else if layer_max_date == "" { layer_temporal = layer_min_date } else { layer_temporal = layer_min_date + " - " + layer_max_date } cronExpression := getCronExpression(matrix[i][0], resource) cronExpression = "запуск в " + getCronStartTime(cronExpression) // ид ИР, ПД, СИ, БИД (время обновления), БИД, ГИС writer.Write([]string{resource, cronExpression, "", "", "", "", "", ""}) // поиск по кешу СИ var is_data_time string for _, is_report_record := range is_report { if is_report_record[0] == (resource + "_1.nc") { is_data_time = is_report_record[2] is_date, _ := time.Parse(is_date_pattern, is_data_time) fmt.Println(is_date) is_data_time = is_date.Format(global_date_pattern) } } // поиск по БИД var bid_update_time string var bid_md_begin string var bid_md_end string var bid_data_min string var bid_data_max string for z := range biddata { if resource == biddata[z][0] { bid_update_time = biddata[z][1] bid_md_begin = biddata[z][2] bid_md_end = biddata[z][3] bid_data_min = biddata[z][4] bid_data_max = biddata[z][5] } } bid_temporal := bid_data_min + "-" + bid_data_max writer.Write([]string{"метаданные", beginDateTime + "-" + endDateTime, "", bid_update_time, bid_md_begin + "-" + bid_md_end, ""}) writer.Write([]string{"данные", "", is_data_time, "", bid_temporal, layer_temporal}) } writer.Flush() } }
/************************ 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 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) } } }
func main() { var examFiles []string var scoreFile *xlsx.File var row *xlsx.Row files, err := ioutil.ReadDir(fexamDir) if err != nil { log.Println("Error reading fexam directory!") os.Exit(1) } for _, file := range files { filename := file.Name() if strings.HasSuffix(filename, ".xlsx") { examFiles = append(examFiles, fexamDir+filename) } } //create score xlsx file to save student score scoreFile = xlsx.NewFile() scoreSheet, err := scoreFile.AddSheet("Sheet1") if err != nil { log.Println(err.Error()) } //loop student exam xlsx file ,read sheet1 col2 to judge startTime := time.Now().UnixNano() for _, examFile := range examFiles { score := 0 xlFile, error := xlsx.OpenFile(examFile) //open excel file if error != nil { log.Println("Error reading examfile") } //get student's name,no,class //example:-Unlicensed-13715050_袁慧敏_13医器_java.xlsx s := strings.Split(examFile[20:strings.Index(examFile, ".xlsx")], "_") stuNo := s[0] stuName := s[1] stuClass := s[2] sheet := xlFile.Sheets[0] var stuAnswer []string for _, row := range sheet.Rows { if row != nil { cell := row.Cells[2] stuAnswer = append(stuAnswer, fmt.Sprintf("%s", cell.String())) } } fmt.Println(stuAnswer) //get the choice right number rightChoiceNum := right(stuAnswer[1:21], choiceAnswer) //get the blank right number rightBlankNum := right(stuAnswer[22:27], blankAnswer) score = rightChoiceNum*3 + rightBlankNum*5 fmt.Println(score) row = scoreSheet.AddRow() //add student no cellstuno := row.AddCell() cellstuno.Value = stuNo //add student name cellname := row.AddCell() cellname.Value = stuName //add student class cellclass := row.AddCell() cellclass.Value = stuClass //add student score cellscore := row.AddCell() cellscore.Value = strconv.Itoa(score) } err = scoreFile.Save("./score/score.xlsx") endTime := time.Now().UnixNano() log.Printf("试卷批改完成 共加载:%d 条记录, 所花时间:%.1f ms\n", len(examFiles), float64(endTime-startTime)/1000000) if err != nil { log.Println(err.Error()) } }
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 Run(ow func(output string), fetch int, isReset bool, isCheckDate bool, filterDate time.Time) { if isRunning { log.Println("isRunning is true ... Skip the exeuction") return } else { isRunning = true defer stopProcess() } helpcase.IsCheckDate = isCheckDate helpcase.CheckDate = filterDate init2() outputWindow = ow outputWindow("\n處理中\n") go printProgress() defer f.Close() if isReset { log.Println("Cleaning database ...") helpcase.CleanDB() log.Println("Cleaning database done") } go helpcase.SubHelpcaseTableParserListener() go helpcase.SubHelpcaseDetailParserListener(fetch) a := helpcase.New("http://search.appledaily.com.tw/charity/projlist/", 20) var result = a.Parse() if !result { return } // helpcase.WaitGroupForMainTable.Wait() helpcase.WaitGroupForDetail.Wait() log.Println("Begin to retry failed request ...") for _, sno := range helpcase.FailureQueue { hp := helpcase.GetHelpcase(sno) log.Println("retry " + hp.SerialNo) if hp != nil { helpcase.WaitGroupForDetail.Add(1) helpcase.BeginToProcessHelpcase(hp) } } outputWindow("\n\n") outputWindow("\nParse結束,產生Excel檔案中\n") var file *xlsx.File var sheet1 *xlsx.Sheet log.Println("Generating Excel ...") file = xlsx.NewFile() sheet1 = file.AddSheet("Sheet1") createSheet1(sheet1) sheet2 := file.AddSheet("Sheet2") createSheet2(sheet2) err := file.Save(outfolder + "/main.xlsx") if err != nil { outputWindow(err.Error()) } log.Println("Generating Excel Done") createDonation() stopProcess() outputWindow("\n") outputWindow("完成!\n") outputWindow("結束時間:" + time.Now().Format("2006/01/02 03:04:05") + "\n") dir, _ := os.Getwd() outputWindow("請在下列目錄取得檔案:" + dir + outfolder + "\n") }
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") }
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 exportPaidOrders(syncName string, conn redis.Conn, rewardIDs []string) { resp, err := http.Get("http://country.io/names.json") if err != nil { logrus.Fatal(err) } defer resp.Body.Close() jsonCountriesData, err := ioutil.ReadAll(resp.Body) if err != nil { logrus.Fatal(err) } //countriesMap := make(map[string]interface{}) countriesMap := make(map[string]string) err = json.Unmarshal([]byte(jsonCountriesData), &countriesMap) if err != nil { logrus.Fatal(err) } values, err := redis.Values(conn.Do("SMEMBERS", syncName)) if err != nil { logrus.Fatal(err) } var file *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell file = xlsx.NewFile() sheet, err = file.AddSheet("Sheet1") if err != nil { logrus.Fatal(err) } // get invalid orders, to exclude them form export invalidOrders, errInvalidOrders := redis.Strings(redis.Values(conn.Do("SMEMBERS", syncName+"_invalidOrders"))) if errInvalidOrders != nil { logrus.Fatal(errInvalidOrders) } // logrus.Println("invalidOrders:", invalidOrders) conn.Send("MULTI") // tmp nbEntries := 0 nbPaymentInvalid := 0 nbInvalid := 0 for _, value := range values { b, ok := value.([]byte) if ok { orderID := string(b) conn.Send("HGETALL", syncName+"_order_"+orderID) } } values, err = redis.Values(conn.Do("EXEC")) if err != nil { logrus.Fatal(err) } for _, value := range values { stringMap, _ := redis.StringMap(value, nil) nbItems, _ := strconv.Atoi(stringMap["nbItems"]) if nbItems == 0 { continue } accept := true // filter reward ids if rewardIDs != nil && len(rewardIDs) > 0 { accept = false // HACK: considering there's only one // single item per order (id: 0) rewardID := stringMap["item0_product"] //logrus.Println(rewardID) for _, id := range rewardIDs { if id == rewardID { accept = true break } } } if accept { paymentStatus, _ := strconv.Atoi(stringMap["status"]) accept = clientapi.OrderStatus(paymentStatus) == clientapi.OrderStatusPaymentDone if !accept && clientapi.OrderStatus(paymentStatus) == clientapi.OrderStatusInvalid { nbPaymentInvalid++ } } if accept { // invalid orders are the one that can't be sent because users made a mistake // in the shipping address (lefting one or more fields blank) accept = isValid(invalidOrders, stringMap["orderId"]) if !accept { nbInvalid++ } } if accept { //logrus.Println(stringMap["statusDisplay"]) // logrus.Println(stringMap) // logrus.Println(stringMap["firstName"]+" "+stringMap["lastName"], "|", // stringMap["shippingAddr1"], "|", // stringMap["shippingAddr2"], "|", // stringMap["shippingCity"], "|", // stringMap["shippingCode"], "|", // stringMap["shippingCountry"], "|", // stringMap["email"], "|") row = sheet.AddRow() cell = row.AddCell() cell.Value = stringMap["firstName"] + " " + stringMap["lastName"] cell = row.AddCell() cell.Value = stringMap["shippingAddr1"] cell = row.AddCell() cell.Value = stringMap["shippingAddr2"] cell = row.AddCell() cell.Value = stringMap["shippingCity"] // state cell = row.AddCell() if stringMap["shippingCountry"] == "US" { cell.Value = stringMap["shippingState"] } cell = row.AddCell() cell.Value = stringMap["shippingCode"] // country name cell = row.AddCell() cell.Value = countriesMap[stringMap["shippingCountry"]] cell = row.AddCell() cell.Value = stringMap["shippingCountry"] // phone number cell = row.AddCell() cell.Value = stringMap["shippingPhoneNumber"] cell = row.AddCell() cell.Value = stringMap["email"] nbEntries++ // if nbDisplayed >= 10 { // break // } } } fileName := "/data/" + syncName + ".xlsx" err = file.Save(fileName) if err != nil { logrus.Fatal(err) } logrus.Println(nbEntries, "entries written in", fileName) logrus.Println("(" + strconv.Itoa(nbPaymentInvalid) + " payment invalid)") logrus.Println("(" + strconv.Itoa(nbInvalid) + " invalid shipping addresses)") }
/************************ 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 Unload(p Panel, fname string) { // ADD ERROR // p.Clean() switch path.Ext(fname) { case ".csv": // Convert to CSV-like structure width := p.Size().Width length := p.Size().Length headers := make(map[string]int) colnum := 0 for head, _ := range p { headers[head] = colnum colnum += 1 } records := [][]string{} record := make([]string, width) for head, _ := range p { record[headers[head]] = head } records = append(records, record) for i := 0; i < length; i++ { record := make([]string, width) for head, col := range p { var val string switch t := col[i].(type) { case time.Time: d110, _ := dateFormat(110) tt := t.Format(d110) fmt.Println(tt) val = tt default: val = fmt.Sprintf("%v", t) } record[headers[head]] = val } records = append(records, record) } // fmt.Println(records) // Write to file file := new(os.File) if _, err := os.Stat(fname); err == nil { file, _ = os.OpenFile(fname, os.O_RDWR|os.O_APPEND, 0777) } else { file, _ = os.Create(fname) } w := csv.NewWriter(file) // w.WriteAll(records) for _, record := range records { if err := w.Write(record); err != nil { log.Fatalln("error writing record to csv:", err) } } w.Flush() // Write any buffered data to the underlying writer if err := w.Error(); err != nil { log.Fatal(err) } case ".xml": case ".tsv": case ".json": export := []map[string]interface{}{} // width := p.Size().Width length := p.Size().Length for i := 0; i < length; i++ { row := make(map[string]interface{}) for header, series := range p { row[header] = series[i] } export = append(export, row) } j, _ := json.Marshal(export) file := new(os.File) if _, err := os.Stat(fname); err == nil { file, _ = os.OpenFile(fname, os.O_RDWR|os.O_APPEND, 0777) } else { file, _ = os.Create(fname) } _, err := file.Write(j) if err != nil { fmt.Println(err) } file.Close() case ".xlsx", ".xls": // Convert to CSV-like structure width := p.Size().Width length := p.Size().Length headers := make(map[string]int) colnum := 0 for head, _ := range p { headers[head] = colnum colnum += 1 } records := [][]string{} record := make([]string, width) for head, _ := range p { record[headers[head]] = head } records = append(records, record) for i := 0; i < length; i++ { record := make([]string, width) for head, col := range p { var val string switch t := col[i].(type) { case time.Time: d110, _ := dateFormat(110) tt := t.Format(d110) fmt.Println(tt) val = tt default: val = fmt.Sprintf("%v", t) } record[headers[head]] = val } records = append(records, record) } // fmt.Println(records) // Write to file // file := new(os.File) // if _, err := os.Stat(fname); err == nil { // file, _ = os.OpenFile(fname, os.O_RDWR|os.O_APPEND, 0777) // } else { // file, _ = os.Create(fname) // } var excel *xlsx.File var sheet *xlsx.Sheet var row *xlsx.Row var cell *xlsx.Cell var err error excel = xlsx.NewFile() sheet, err = excel.AddSheet("Sheet1") if err != nil { fmt.Printf(err.Error()) } for _, rec := range records { row = sheet.AddRow() for _, field := range rec { cell = row.AddCell() cell.Value = fmt.Sprintf("%s", field) } } err = excel.Save(fname) if err != nil { fmt.Printf(err.Error()) log.Fatalln("error writing record to xlsx:", err) } default: file := new(os.File) if _, err := os.Stat(fname); err == nil { file, _ = os.OpenFile(fname, os.O_RDWR|os.O_APPEND, 0777) } else { file, _ = os.Create(fname) } file.Write([]byte(fmt.Sprintf("%s", p))) } }