func writeHeaderRowToSheet(columns []string, sheet string) error {

	activeSheet, err := getActiveSheet(sheet)

	if err != nil {
		return err
	}

	headerFont := xlsx.NewFont(12, "Verdana")
	headerFont.Bold = true
	headerFont.Underline = true
	headerStyle := xlsx.NewStyle()
	headerStyle.Font = *headerFont

	var cell *xlsx.Cell

	row := activeSheet.AddRow()
	for _, col := range columns {
		cell = row.AddCell()
		cell.SetStyle(headerStyle)
		cell.Value = strings.ToTitle(strings.Replace(col, "_", " ", -1))
	}
	return nil
}
func writeFooterRowToSheet(totals []int64, sheet string) error {
	activeSheet, err := getActiveSheet(sheet)

	if err != nil {
		return err
	}

	footerFont := xlsx.NewFont(12, "Verdana")
	footerFont.Bold = true
	footerStyle := xlsx.NewStyle()
	footerStyle.Font = *footerFont

	var cell *xlsx.Cell
	row := activeSheet.AddRow()
	cell = row.AddCell()
	cell.SetStyle(footerStyle)
	cell.Value = "Total"
	for i := 1; i < len(totals); i++ {
		cell = row.AddCell()
		cell.SetStyle(footerStyle)
		cell.SetFloatWithFormat(float64(totals[i]), "#,##0")
	}

	activeSheet.AddRow()
	activeSheet.AddRow()

	return nil

}
Example #3
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 + "\",\"明細\")")
	}
}
func AnalyzeEntity() {
	file := xlsx.NewFile()
	sheet := file.AddSheet("Sheet1")
	sheet.SetColWidth(4, 4, 60)
	sheet.SetColWidth(5, 5, 15)

	highlightStyle := xlsx.NewStyle()
	highlightStyle.Fill = xlsx.Fill{"", "", "FFFF0000"}
	highlightStyle.ApplyFill = true
	highlightStyle.ApplyFont = true
	highlightStyle.Font.Size = 11
	highlightStyle.Font.Name = "Calibri"
	highlightStyle.Font.Family = 2
	highlightStyle.Font.Bold = true
	highlightStyle.Font.Color = "FFFF0000"

	normalStyle := xlsx.NewStyle()
	normalStyle.Font.Size = 11
	normalStyle.Font.Name = "Calibri"
	normalStyle.Font.Family = 2

	csvWalk(func(num int, record []string, err error) {
		fmt.Println("line", num+1)

		for i, _ := range record {
			removeBookTitleMark(&record[i])
		}
		// fmt.Println(record)

		sheetRowSlice := make([]string, 0)

		row := sheet.AddRow()

		// 判断有没有书名号的实体
		if num > 0 && len(record) >= 3 {
			results := searchInIDOL(record[2])
			//fmt.Println("results", results)

			sheetRowSlice = append(sheetRowSlice, record[:4]...)

			originTitle := record[1]

			titles := make([]string, 0)
			pureTitles := make([]string, 0)
			ids := make([]string, 0)
			for index, result := range results {
				titles = append(titles,
					fmt.Sprintf("%d,%s", index+1, result.title))
				pureTitles = append(pureTitles, result.pureTitle)
				ids = append(ids, fmt.Sprintf("%d,%d", index+1, result.id))
			}
			sheetRowSlice = append(sheetRowSlice, strings.Join(titles, "\n"))
			sheetRowSlice = append(sheetRowSlice, strings.Join(ids, "\n"))

			for index, cellContent := range sheetRowSlice {
				cell := row.AddCell()
				if index == 2 && len(pureTitles) > 0 && originTitle == pureTitles[0] {
					cell.SetStyle(highlightStyle)
				} else {
					cell.SetStyle(normalStyle)
				}
				cell.Value = cellContent
			}
		} else {
			row.WriteSlice(&record, -1)
		}
	})

	err := file.Save(OUTPUT_FILENAME)
	if err != nil {
		fmt.Printf(err.Error())
	}
}