Golang xlsx.NewFile函数代码示例

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 generateXLSXFromCSV(csvPath string, XLSXPath string, delimiter string) error {
	csvFile, err := os.Open(csvPath)
	if err != nil {
		return err
	}
	defer csvFile.Close()
	reader := csv.NewReader(csvFile)
	if len(delimiter) > 0 {
		reader.Comma = rune(delimiter[0])
	} else {
		reader.Comma = rune(';')
	}
	xlsxFile := xlsx.NewFile()
	sheet, err := xlsxFile.AddSheet(csvPath)
	if err != nil {
		return 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 {
		fmt.Printf(err.Error())
	}
	return xlsxFile.Save(XLSXPath)
}

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 getOnLineList() {
	File := xlsx.NewFile()
	sheet := File.AddSheet("online")
	File.AddSheet("update")
	File.AddSheet("insert")
	row := sheet.AddRow()
	row.WriteSlice(&head, len(head))
	Engine := Engines()
	result := new(NxServerState)
	lines, _ := Engine.Rows(result)
	defer lines.Close()
	lines.Next()
	r := new(NxServerState)
	var i int = 1
	for {
		err := lines.Scan(r)
		if err != nil {
			return
		}
		r.ServerName = convert([]byte(r.ServerName))
		row := sheet.AddRow()
		row.WriteStruct(r, len(head))
		if !lines.Next() {
			break
		}
		i++
	}
	File.Save("server.xlsx")
}

// ImportCSV func
func ImportCSV(csvPath string, delimiter string) (*xlsx.File, error) {
	csvFile, err := os.Open(csvPath)
	if err != nil {
		return nil, err
	}
	defer csvFile.Close()
	reader := csv.NewReader(csvFile)
	if len(delimiter) > 0 {
		reader.Comma = rune(delimiter[0])
	} else {
		reader.Comma = rune(',')
	}
	xlsxFile := xlsx.NewFile()
	sheet, err := xlsxFile.AddSheet(csvPath)
	if err != nil {
		return nil, 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()
	}
	// parse csv file error
	if err != nil {
		return nil, err
	}
	// do save
	return xlsxFile, nil
}

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 SubjectSpreadsheet(e env.Env, f database.Filter, subj *subject.Subject, w io.Writer) error {

	file := xlsx.NewFile()

	g, err := e.GroupByFilteredClass(strconv.Itoa(subj.SubjID), "", f)
	if err != nil {
		return err
	}

	sheet, err := file.AddSheet("Progress Grid")
	if err != nil {
		return err
	}
	subjectGrid(sheet, g, subj, f.NatYear)

	sheet, err = file.AddSheet("Students")
	if err != nil {
		return err
	}
	subjectBroadsheet(e, subj, sheet, g)

	sheet, err = file.AddSheet("Export Details")
	if err != nil {
		return err
	}
	exportInfoSheet(sheet, e, f)

	file.Write(w)
	return nil
}

//驻外团、处
func zwtc() {

	doc, err := goquery.NewDocument(URL_ZWTC)
	if err != nil {
		log.Fatal(err)
	}

	file := xlsx.NewFile()
	sheet := file.AddSheet("驻外团、处")

	totalUrl := 0

	doc.Find(".w .fr .bd a").Each(func(i int, contentSelection *goquery.Selection) {
		name := contentSelection.Text()
		if href, exists := contentSelection.Attr("href"); exists {
			href = URL_ZWTC + strings.Replace(href, ".", "", 1)
			err := parse(sheet, href)
			if err != nil {
				fmt.Printf("[read error]第%d个：%s。url:%s。 %v", i+1, name, href, err)
			}
			totalUrl = i + 1
		}
	})

	fileName := fmt.Sprintf("驻外团、处(%d个).xlsx", totalUrl)

	err = file.Save(fileName)
	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 combineCSVtoXLSX(sources []string, output string, delimiter string) error {
	xlsxFile := xlsx.NewFile()
	for _, s := range sources {
		if err := appendCSVtoXLSX(s, xlsxFile, delimiter); err != nil {
			return err
		}
	}
	return xlsxFile.Save(output)
}

// XLSX exports the Dataset as a byte array representing the .xlsx format.
func (d *Dataset) XLSX() (*Exportable, error) {
	file := xlsx.NewFile()
	if err := d.addXlsxSheetToFile(file, "Sheet 1"); err != nil {
		return nil, err
	}

	b := newBuffer()
	file.Write(b)
	return newExportable(b), nil
}

func NewFile(descpool *pbmeta.DescriptorPool) *File {

	self := &File{
		Sheets:   make([]*Sheet, 0),
		SheetMap: make(map[string]*Sheet),
		descpool: descpool,
		Raw:      xlsx.NewFile(),
	}

	return self
}

func createWorkbook(m map[string]messages) {

	var channelNames sort.StringSlice

	workbook := xlsx.NewFile()
	fullPath := filepath.Clean(filepath.Join(outputDir, filename))

	// First, sort the map by channel name
	for channel := range m {
		channelNames = append(channelNames, channel)
	}

	channelNames.Sort()

	for _, channel := range channelNames {
		sheet, err := workbook.AddSheet(channel)
		if err != nil {
			panic(err)
		}

		sort.Sort(sort.Reverse(m[channel]))

		headingRow := sheet.AddRow()
		headingRow.AddCell().SetString("Timestamp")
		headingRow.AddCell().SetString("User")
		headingRow.AddCell().SetString("Message")

		if err := sheet.SetColWidth(0, 1, 20); err != nil {
			panic(err)
		}

		if err := sheet.SetColWidth(2, 2, 200); err != nil {
			panic(err)
		}

		for _, message := range m[channel] {
			messageRow := sheet.AddRow()
			messageRow.AddCell().SetString(message.Timestamp.Format("Jan 02, 2006 | 15:04"))
			messageRow.AddCell().SetString(message.User)
			messageRow.AddCell().SetString(message.Text)
		}

	}

	if err := os.MkdirAll(filepath.Dir(fullPath), os.ModePerm); err != nil {
		panic(err)
	}

	if err := workbook.Save(fullPath); err != nil {
		panic(err)
	}

}

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

// ValueToExcelWithLocale value to excel with locale
// rows should be slice type.
func ValueToExcelWithLocale(sheetName string,
	rows interface{},
	include string,
	exclude string,
	locale string,
	enableLocale bool) (*xlsx.File, error) {

	// check args
	if sheetName == "" {
		sheetName = "Sheet1"
	}

	v := reflect.ValueOf(rows)
	if v.Kind() != reflect.Slice {
		return nil, fmt.Errorf("rows should be a slice")
	}

	file := xlsx.NewFile()
	sheet, err := file.AddSheet(sheetName)
	if err != nil {
		return nil, err
	}
	//fs := mcore.GetArrayFields(rows)
	fs := mcore.GetUsedArrayFields(rows, include, exclude)
	// add header
	row := sheet.AddRow()

	// f means fields
	for _, f := range fs {
		cell := row.AddCell()
		// locale header
		if enableLocale && locale != "" {
			f = mmsg.GetModelFieldLabel(locale, mcore.GetArrayFirstElement(rows), f)
		}
		cell.Value = f
	}

	// add data
	for i := 0; i < v.Len(); i++ {
		rowValue := v.Index(i).Interface()
		row := sheet.AddRow()
		for _, f := range fs {
			cell := row.AddCell()
			// TODO adapter for all kinds data type
			//cell.Value = mcore.GetFieldValue(rowValue, f)
			cv := mcore.GetFieldValueOrigin(rowValue, f)
			cell.SetValue(cv)
		}
	}
	return file, nil
}