Golang xlsx.File类代码示例

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

//экспорт данных 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 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 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 (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
}

/************************ 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 (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 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:%[email protected]%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)
//.........这里部分代码省略.........