英文:
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)
}
}
这个代码可以工作,但存在一些数据格式问题。数字的样式被复制了,但不起作用;日期被复制了,但值错误。
-
在源文件中,有一些带有2位小数格式的数字,显示为
70.12
,而在输出文件中,格式相同但显示为70.119
。 -
在源文件中,有一些以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.
-
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 like70.119
. -
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 like01-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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论