Example #1
0
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())
	}
}
Example #2
0
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())
	}
}
Example #3
0
func getHeader(sheet *xlsx.Sheet) (*tool.ExportHeaderV1, error) {

	headerString := strings.TrimSpace(sheet.Cell(0, 0).Value)

	// 可能是空的sheet
	if headerString == "" {
		return nil, nil
	}

	var header tool.ExportHeaderV1

	// 有可能的字符,一定是头
	if strings.Contains(headerString, "ProtoTypeName") ||
		strings.Contains(headerString, "RowFieldName") {
		if err := proto.UnmarshalText(headerString, &header); err != nil {

			return nil, err
		}
	} else {
		// 有字符, 但并不是头
		return nil, nil
	}

	return &header, nil
}
Example #4
0
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
}
Example #5
0
// 写每一个*.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
}
Example #6
0
// 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
	}
}
Example #8
0
//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)
}
Example #9
0
func getHeader(sheet *xlsx.Sheet) *tool.ExportHeader {

	headerString := sheet.Cell(0, 0).Value

	var header tool.ExportHeader

	if err := proto.UnmarshalText(headerString, &header); err != nil {
		return nil
	}

	return &header
}
Example #10
0
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
	}
}
Example #11
0
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)
}
Example #12
0
// 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")
}
Example #13
0
// 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")
}
Example #14
0
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 + "\",\"明細\")")
	}
}
Example #15
0
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"))
}
Example #17
0
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())
	}
}
Example #18
0
//экспорт данных 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())
	}
}
Example #19
0
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())
	}
}
Example #20
0
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())
	}
}
Example #21
0
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個字"
}
Example #22
0
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
}
Example #23
0
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
}
Example #24
0
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
}
Example #25
0
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
}
Example #26
0
/************************ 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)
		}

	}
}
Example #27
0
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
}
Example #28
0
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")
	}
}
Example #29
0
/************************ 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)
	}

}
Example #30
0
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")
}