Golang的Excelize库如何复制单元格及其格式?

huangapple go评论95阅读模式
英文:

golang excelize how to copy the cell and it's format

问题

我使用github.com/xuri/excelize/v2来处理Excel文件。

我将多个Excel文件中的多个工作表合并到一个Excel文件的一个工作表中。

以下是示例代码。

    var mergedRows [][]string
	for _, f := range files {
		excelPath := folder + "/" + f.Name()
		rows := loadXlsx(excelPath, sheetName)
		for _, row := range rows[rowOffset:] {
			mergedRows = append(mergedRows, row)
		}
	}

	saveXlsx(aggregatedFilePath, sheetName, mergedRows, rowOffset)


...

func loadXlsx(xlsxPath string, sheetName string) [][]string {

	f, err := excelize.OpenFile(xlsxPath)
	if err != nil {
		log.Fatal(err)
	}

	defer func() {
		if err := f.Close(); err != nil{
			fmt.Println(err)
		}
	}()

	rows, err := f.GetRows(sheetName)
	if err != nil {
		log.Fatal(err)
	}

	return rows
}

func saveXlsx(path string, sheetName string, rows [][]string, rowOffset int) {

	f, err := excelize.OpenFile(path)
	if err != nil {
		log.Fatal(err)
	}

	defer func() {
		if err := f.Close(); err != nil{
			fmt.Println(err)
		}
	}()


	index := f.GetSheetIndex(sheetName)
	offset := 1
	sequence := 1
	for _, row := range rows{
		row[0] = strconv.Itoa(sequence)
		sequence = sequence + 1
		offset = offset + 1
		axis := "A" + strconv.Itoa(offset)
		f.SetSheetRow(sheetName, axis, &row)
	}

	for index, _ := range rows[0] {
		axis, _ := excelize.CoordinatesToCellName(index, 2)
		column, _ := excelize.ColumnNumberToName(index)
		styleId, _ := f.GetCellStyle(sheetName, axis)
		cellType, _ := f.GetCellType(sheetName, axis)
		fmt.Println(styleId)
		fmt.Println(cellType)
		f.SetColStyle(sheetName, column, styleId)
	}


	f.SetActiveSheet(index)
	if err := f.Save(); err != nil {
		fmt.Println(err)
	}

}

这个代码可以工作,但存在一些数据格式问题。数字的样式被复制了,但不起作用;日期被复制了,但值错误。

  1. 在源文件中,有一些带有2位小数格式的数字,显示为70.12,而在输出文件中,格式相同但显示为70.119

  2. 在源文件中,有一些以Y/m/d格式表示的日期,显示为2022/1/12,而在输出文件中,格式相同但显示为01-12-22

英文:

I use github.com/xuri/excelize/v2 to process the excel file.

I append many sheets in many excel files into one sheet in one excel.

Below is the sample code.

    var mergedRows [][]string
for _, f := range files {
excelPath := folder + "/" + f.Name()
rows := loadXlsx(excelPath, sheetName)
for _, row := range rows[rowOffset:] {
mergedRows = append(mergedRows, row)
}
}
saveXlsx(aggregatedFilePath, sheetName, mergedRows, rowOffset)
...
func loadXlsx(xlsxPath string, sheetName string) [][]string {
f, err := excelize.OpenFile(xlsxPath)
if err != nil {
log.Fatal(err)
}
defer func() {
if err := f.Close(); err != nil{
fmt.Println(err)
}
}()
rows, err := f.GetRows(sheetName)
if err != nil {
log.Fatal(err)
}
return rows
}
func saveXlsx(path string, sheetName string, rows [][]string, rowOffset int) {
f, err := excelize.OpenFile(path)
if err != nil {
log.Fatal(err)
}
defer func() {
if err := f.Close(); err != nil{
fmt.Println(err)
}
}()
index := f.GetSheetIndex(sheetName)
offset := 1
sequence := 1
for _, row := range rows{
row[0] = strconv.Itoa(sequence)
sequence = sequence + 1
offset = offset + 1
axis := "A" + strconv.Itoa(offset)
f.SetSheetRow(sheetName, axis, &row)
}
for index, _ := range rows[0] {
axis, _ := excelize.CoordinatesToCellName(index, 2)
column, _ := excelize.ColumnNumberToName(index)
styleId, _ := f.GetCellStyle(sheetName, axis)
cellType, _ := f.GetCellType(sheetName, axis)
fmt.Println(styleId)
fmt.Println(cellType)
f.SetColStyle(sheetName, column, styleId)
}
f.SetActiveSheet(index)
if err := f.Save(); err != nil {
fmt.Println(err)
}
}

This works, except some data format issues. the number's style is copyed, but not works; the date is copyed, but with wrong value.

  1. In the source file, there has some number with 2 decimal format and shows like 70.12, while in the output file the format is the same but shows like 70.119.

  2. In the source file, there has some date with Y/m/d format and shows like 2022/1/12, while in the output file the format is the same but shows like 01-12-22.

答案1

得分: 1

手册中可以得知:

func (f *File) GetRows(sheet string, opts ...Options) ([][]string, error)

如果单元格的格式可以应用于单元格的值,则将使用应用的值,否则将使用原始值。

所以在我的问题中,rows, err := f.GetRows(sheetName) 将复制带有格式的日期和数字值,而不是原始数字。格式化的值可能会转换为不相等的值。

解决方法是使用RawCellValue选项设置为true来读取原始值:

rows, err := f.GetRows(sheetName, excelize.Options{RawCellValue:true})

如果格式发生了变化,只需将原始文件的样式应用到新文件中即可。

英文:

From the manual

func (f *File) GetRows(sheet string, opts ...Options) ([][]string, error)

> If the cell format can be applied to the value of the cell, the
> applied value will be used, otherwise the original value will be used.

So in my question, rows, err := f.GetRows(sheetName) will copy the date and number value with format, not the original number. The formated value may be convert to non equal value.

The solution is just read the raw value with RawCellValue option true,

rows, err := f.GetRows(sheetName, excelize.Options{RawCellValue:true})

If the format is changed, just apply the style from the original file to the new file.

huangapple
  • 本文由 发表于 2022年3月1日 21:37:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/71309324.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定