Golang xlsx.Row类代码示例

/*-----------------------------------------------------------------------------
-- FUNCTION:    generateHeaders
--
-- DATE:        February 13, 2016
--
-- REVISIONS:	 (DATE AND INFO)
--
-- DESIGNER:		Marc Vouve
--
-- PROGRAMMER:	Marc Vouve
--
-- INTERFACE:		generateHeaders(i interface{}, row *xlsx.Row)
--         i:   the interface to generate headers from.
--       row:   a pointer for i's field names to be written to.
--
-- RETURNS: 		void
--
-- NOTES:			This function uses reflect to get the name of the element and print
--            the name of the element to a row.
------------------------------------------------------------------------------*/
func generateHeaders(i interface{}, row *xlsx.Row) {
	fields := reflect.ValueOf(i)
	for i := 0; i < fields.NumField(); i++ {
		cell := row.AddCell()
		cell.SetString(fields.Type().Field(i).Name)
	}
}

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
}

/*-----------------------------------------------------------------------------
-- FUNCTION:    Generate Report
--
-- DATE:        February 13, 2016
--
-- REVISIONS:	 (DATE AND INFO)
--
-- DESIGNER:		Marc Vouve
--
-- PROGRAMMER:	Marc Vouve
--
-- INTERFACE:		func generateRow(i interface{}, row *xlsx.Row)
--         i:   the interface to generate headers from.
--       row:   a pointer to a row from a spreadsheet for the i to be printed on.
--
-- RETURNS: 		void
--
-- NOTES:			This function uses reflect to get the name of the element and print
--            the name of the element to a row.
------------------------------------------------------------------------------*/
func generateRow(i interface{}, row *xlsx.Row) {
	fields := reflect.ValueOf(i)
	for i := 0; i < fields.NumField(); i++ {
		cell := row.AddCell()
		cell.SetValue(fields.Field(i).Interface())
	}
}

// 写每一个*.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
}

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 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 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())
	}
}

//WriteToRow write content to a excel row
func (checkResult *CheckResult) WriteToRow(row *xlsx.Row) {
	value := reflect.ValueOf(*checkResult)
	for i := 0; i < value.NumField(); i++ {
		oneField := value.Field(i)
		cell := row.AddCell()
		if oneField.Type().Name() == "string" {
			cell.SetString(oneField.String())
		} else {
			fieldContent := oneField.Int()
			if fieldContent == 2 {
				// style := xlsx.NewStyle()
				// font := xlsx.DefaultFont()
				cell.GetStyle().Font.Color = "FFFF0000"
				cell.SetBool(fieldContent == 1)
			} else if fieldContent == 1 {
				cell.GetStyle().Font.Color = "FF00FF00"
				cell.SetBool(fieldContent == 1)
			}
		}
	}
}

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 ExportReservationsToExcel(reservations []*models.Reservation, filename string) error {
	xl, err := xlsx.OpenFile(ExportFolder + DefaultReservationExportExcelFilename)
	if err != nil {
		return errors.New("导出失败:打开模板文件失败")
	}
	sheet := xl.Sheet["export"]
	if sheet == nil {
		return errors.New("导出失败:打开工作表失败")
	}
	var row *xlsx.Row
	var cell *xlsx.Cell
	for _, reservation := range reservations {
		row = sheet.AddRow()
		// 学生申请表
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.Name)
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.Gender)
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.StudentId)
		cell = row.AddCell()
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.School)
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.Hometown)
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.Mobile)
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.Email)
		cell = row.AddCell()
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.Problem)
		// 预约信息
		cell = row.AddCell()
		cell.SetString(reservation.TeacherFullname)
		cell = row.AddCell()
		cell.SetString(reservation.StartTime.Format(DATE_PATTERN))
		// 咨询师反馈表
		cell = row.AddCell()
		cell = row.AddCell()
		cell = row.AddCell()
		cell.SetString(reservation.TeacherFeedback.Problem)
		cell = row.AddCell()
		cell.SetString(reservation.TeacherFeedback.Solution)
		// 学生反馈表
		cell = row.AddCell()
		cell = row.AddCell()
		cell.SetString(reservation.StudentFeedback.Score)
		cell = row.AddCell()
		cell.SetString(reservation.StudentFeedback.Feedback)
		if !strings.EqualFold(reservation.StudentFeedback.Choices, "") {
			for i := 0; i < len(reservation.StudentFeedback.Choices); i++ {
				cell = row.AddCell()
				switch reservation.StudentFeedback.Choices[i] {
				case 'A':
					cell.SetString("非常同意")
				case 'B':
					cell.SetString("一般")
				case 'C':
					cell.SetString("不同意")
				default:
				}
			}
		}
	}
	err = xl.Save(ExportFolder + filename)
	if err != nil {
		return errors.New("导出失败:保存文件失败")
	}
	return nil
}

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 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個字"
}