File corrupted

[Andrewpqc]

求助Issue，没按照issue格式来。excel生成后打开发现“xxx.xlsx”中的部分内容有问题。是否让我们尽量尝试恢复? 如果您信任此工作簿的源，请单击“是” 是什么原因？有人遇到过吗？

[xuri]

Please follow by issue template to provide code/attachments and steps to reproduce the issue.

[xuri]

Hi @Andrewpqc, I'll close this issue, if the problem was still is gone, please provides more details and reopen this issue.

[ycll]

求助Issue，没按照issue格式来。excel生成后打开发现“xxx.xlsx”中的部分内容有问题。是否让我们尽量尝试恢复? 如果您信任此工作簿的源，请单击“是” 是什么原因？有人遇到过吗？

Need add content-length header

[dingweihua]

@xuri I still encountered this issue. When i open the saved xlsx file, it asks me "发现“员工表.xlsx”中的部分内容有问题。是否让我们尽量尝试恢复？".
Could you help comment on it? Here is the details:

[xuri]

@dingweihua Please follow by issue template to provide code/attachments and steps to reproduce the issue.

[dingweihua]

@xuri Here is the full code template which produces the excel file. Could you please dig into it? Thanks!

// ListToExcel 将数据列表转成excel表格
func ListToExcel(list interface{}, title, sheetName string) *excelize.File {
	f := excelize.NewFile()
	defaultSheet := "Sheet1"
	// 标题行
	titleLineNum := 0
	if title != "" {
		titleLineNum = 1
	}

	lines := 0
	fieldList := make([]ExcelField, 0)
	hasColumnA := false
	maxCol := "A"
	switch reflect.TypeOf(list).Kind() {
	case reflect.Array, reflect.Slice:
		listValue := reflect.ValueOf(list)
		lines = listValue.Len()
		for i := 0; i < lines; i++ {
			value := listValue.Index(i)
			if i == 0 {
				// 解析表格的列
				rfValue := reflect.TypeOf(value.Interface())
				for j := 0; j < rfValue.NumField(); j++ {
					field := rfValue.Field(j)
					excelTag := field.Tag.Get("excel")
					column := GetTagFieldValue(excelTag, "column")
					desc := GetTagFieldValue(excelTag, "desc")
					width := GetTagFieldValue(excelTag, "width")
					widthFloat, err := strconv.ParseFloat(width, 10)
					if err != nil {
						panic(err)
					}
					fieldItem := ExcelField{
						Name:   field.Name,
						Column: column,
						Desc:   desc,
						Width:  widthFloat,
					}
					fieldList = append(fieldList, fieldItem)
					// 判断是否有 A 这一列
					if (!hasColumnA) && column == "A" {
						hasColumnA = true
					}
					// 设置列名
					if err = f.SetCellValue(defaultSheet, column+strconv.Itoa(titleLineNum+1), desc); err != nil {
						panic(err)
					}
					// 获取最大的列
					if strings.Compare(column, maxCol) > 0 {
						maxCol = column
					}
				}
				// 设置序号列列名
				if !hasColumnA {
					if err := f.SetCellValue(defaultSheet, "A"+strconv.Itoa(titleLineNum+1), "序号"); err != nil {
						panic(err)
					}
				}
			}

			for _, field := range fieldList {
				// 设置单元格值
				if !hasColumnA {
					if err := f.SetCellValue(defaultSheet, "A"+strconv.Itoa(titleLineNum+2+i), i+1); err != nil {
						panic(err)
					}
				}
				if err := f.SetCellValue(defaultSheet, field.Column+strconv.Itoa(titleLineNum+2+i), value.FieldByName(field.Name)); err != nil {
					panic(err)
				}
				// 设置单元格宽度
				if err := f.SetColWidth(defaultSheet, field.Column, field.Column, field.Width); err != nil {
					panic(err)
				}
			}
		}
	}
	if title != "" {
		// 设置title
		if err := f.SetCellValue(defaultSheet, "A"+strconv.Itoa(titleLineNum), title); err != nil {
			panic(err)
		}
		// 合并title单元格
		if err := f.MergeCell(defaultSheet, "A"+strconv.Itoa(titleLineNum), maxCol+strconv.Itoa(titleLineNum)); err != nil {
			panic(err)
		}
		// title单元格居中
		style1, _ := f.NewStyle(`"{alignment":{"horizontal":"center"}}`)
		if err := f.SetCellStyle(defaultSheet, "A1", maxCol+"1", style1); err != nil {
			panic(err)
		}
	}
	// 设置边框和单元格居中
	style, err := f.NewStyle(`{"border":[{"type":"left","color":"000000","style":1},{"type":"right","color":"000000","style":1},{"type":"top","color":"000000","style":1},{"type":"bottom","color":"000000","style":1}],"alignment":{"horizontal":"center"}}`)
	if err != nil {
		panic(err)
	}
	if err = f.SetCellStyle(defaultSheet, "A1", maxCol+strconv.Itoa(titleLineNum+1+lines), style); err != nil {
		panic(err)
	}
	// 冻结0列，标题行和列名行
	if err = f.SetPanes(defaultSheet, `{"freeze":true,"x_split":0,"y_split":`+strconv.Itoa(titleLineNum+1)+`}`); err != nil {
		panic(err)
	}
	// 修改sheet name
	if sheetName != "" {
		f.SetSheetName(defaultSheet, sheetName)
	}
	return f
}

type ExcelField struct {
	Name   string
	Column string
	Desc   string
	Width  float64
}

func GetTagFieldValue(tag, field string) string {
	i1 := strings.Index(tag, field)
	if i1 > -1 {
		i2 := i1 + len(field) + 1
		i3 := strings.Index(tag[i2:], ";")
		if i3 > -1 {
			return tag[i2 : i2+i3]
		} else {
			return tag[i2:]
		}
	}
	return ""
}

type User struct {
	Name    string `excel:"column:B;desc:姓名;width:30"`
	Age     int    `excel:"column:C;desc:年龄;width:10"`
	Address string `excel:"column:D;desc:地址;width:50"`
}

func main() {
	list := make([]User, 0)
	user1 := User{
		Name:    "张三",
		Age:     18,
		Address: "北京东三环",
	}
	user2 := User{
		Name:    "李四",
		Age:     21,
		Address: "上海人民广场",
	}
	user3 := User{
		Name:    "王五",
		Age:     22,
		Address: "长沙开福区",
	}
	list = append(list, user1, user2, user3)
	f := ListToExcel(list, "员工信息表", "员工表")
	if err := f.SaveAs("./员工表.xlsx"); err != nil {
		panic(err)
	}
}

[xuri]

Hi @dingweihua, file corrupted caused by missing top_left_cell when set panes, specify the location of the top-left visible cell in the bottom right pane:

if err = f.SetPanes(defaultSheet, `{"freeze":true,"split": false,"x_split":0,"y_split":`+strconv.Itoa(titleLineNum+1)+`,"top_left_cell":"A3"}`); err != nil {
    panic(err)
}

[dingweihua]

@xuri Yes, it's fixed! Thanks very much!

[MrWrong77]

`package main

import (
"fmt"

"github.com/xuri/excelize/v2"

)

func main() {
f := excelize.NewFile()
// Create a new sheet.
index := f.NewSheet("Sheet2")
// Set value of a cell.
f.SetCellValue("Sheet2", "A2", "Hello world.")
f.SetCellValue("Sheet1", "B2", 100)
// Set active sheet of the workbook.
f.SetActiveSheet(index)
// Save spreadsheet by the given path.
if err := f.SaveAs("Book1.xlsx"); err != nil {
fmt.Println(err)
}
}`

official example has the same problem.I have no idea , please help @xuri

[xuri]

Hi @MrWrong77, which version of the Excelize library and Go language version are you using?

[MrWrong77]

Hi @MrWrong77, which version of the Excelize library and Go language version are you using?

go 1.21.0 excelize 2.80 & 2.4.1

[xuri]

@MrWrong77 if it does not work with Go 1.21.0, please reference the issues #1465, #1595, #1603, #1608, #1614, #1619, #1620, #1621, #1623, #1633, #1637, #1641, #1642, #1648, #1651, #1652, #1656, #1657 and #1660. I have added notice on the README and documentation website for this.

[MrWrong77]

@MrWrong77 if it does not work with Go 1.21.0, please reference the issues #1465, #1595, #1603, #1608, #1614, #1619, #1620, #1621, #1623, #1633, #1637, #1641, #1642, #1648, #1651, #1652, #1656, #1657 and #1660. I have added notice on the README and documentation website for this.

thanks a lot.

[haiyang-zeng]

@xuri I still encountered this issue. When i open the saved xlsx file, it asks me "发现“员工表.xlsx”中的部分内容有问题。是否让我们尽量尝试恢复？". Could you help comment on it? Here is the details:

@xuri I encountered the same issue, I generate a excel file, and it can open on WPS, but report the same warning just like the picture when open it on microsoft excel.

go version go1.21.11 linux/amd64
excelize version [email protected]

code:
func WriteToExcel(data []map[string]any) (*excelize.File, error) {
// 获取表头字段
titleRow := make([]string, 0)
for field := range data[0] {
titleRow = append(titleRow, field)
}

file := excelize.NewFile()
sheetName := "Sheet1"

// 设置标题行
for i, field := range titleRow {
	cell, err := excelize.CoordinatesToCellName(i+1, 1)
	if err != nil {
		return nil, err
	}
	err = file.SetCellValue(sheetName, cell, field)
	if err != nil {
		return nil, err
	}
}

// 插入数据行
for i := 0; i < len(data); i++ {
	for j, field := range titleRow {
		cell, err := excelize.CoordinatesToCellName(j+1, i+2)
		if err != nil {
			return nil, err
		}
		err = file.SetCellValue(sheetName, cell, data[i][field])
		if err != nil {
			return nil, err
		}
	}
}

return file, nil

}

could you please help me solve this issue...

[xuri]

Hi @haiyang-zeng. Thanks for your feedback. Could you show us a complete, standalone example program with main function or reproducible demo?

[haiyang-zeng]

Hi @haiyang-zeng. Thanks for your feedback. Could you show us a complete, standalone example program with main function or reproducible demo?

`func WriteToExcel(data []map[string]any) (*excelize.File, error) {
// 获取表头字段
titleRow := make([]string, 0)
for field := range data[0] {
titleRow = append(titleRow, field)
}

file := excelize.NewFile()
sheetName := "Sheet1"

// 设置标题行
for i, field := range titleRow {
	cell, err := excelize.CoordinatesToCellName(i+1, 1)
	if err != nil {
		return nil, err
	}
	err = file.SetCellValue(sheetName, cell, field)
	if err != nil {
		return nil, err
	}
}

// 插入数据行
for i := 0; i < len(data); i++ {
	for j, field := range titleRow {
		cell, err := excelize.CoordinatesToCellName(j+1, i+2)
		if err != nil {
			return nil, err
		}
		err = file.SetCellValue(sheetName, cell, data[i][field])
		if err != nil {
			return nil, err
		}
	}
}

return file, nil

}

func main() {
data := []map[string]any{
{
"1": 1,
"2": 2,
},
{
"1": 1,
"2": 2,
},
}

file, err := WriteToExcel(data)
if err != nil {
	fmt.Println(err)
}

file.SaveAs("output.xlsx")

}
`
I found that when I use the function file.saveAs(), the save file can open with Microsoft excel, but when I use the function file.Write(w), w's type is io.writer, and download it with postman, the download file can't open with Microsoft excel.
It's occured in company's project, so I need to write a simple sample to reproduce it...

[xuri]

Hi @haiyang-zeng, I tested your code, and it works well. Please show us a reproducible demo.

[haiyang-zeng]

`func WriteToExcel(w http.ResponseWriter, r *http.Request) {
w.Header().Set("Content-Type", "application/octet-stream")
w.Header().Set("Content-Disposition", "attachment; filename=test.xlsx")

data := []map[string]any{
	{
		"1": 1,
		"2": 2,
	},
	{
		"1": 1,
		"2": 2,
	},
}

// 获取表头字段
titleRow := make([]string, 0)
for field := range data[0] {
	titleRow = append(titleRow, field)
}

file := excelize.NewFile()
sheetName := "Sheet1"

// 设置标题行
for i, field := range titleRow {
	cell, err := excelize.CoordinatesToCellName(i+1, 1)
	if err != nil {
		fmt.Fprint(w, err.Error())
	}
	err = file.SetCellValue(sheetName, cell, field)
	if err != nil {
		fmt.Fprint(w, err.Error())
	}
}

// 插入数据行
for i := 0; i < len(data); i++ {
	for j, field := range titleRow {
		cell, err := excelize.CoordinatesToCellName(j+1, i+2)
		if err != nil {
			fmt.Fprint(w, err.Error())
		}
		err = file.SetCellValue(sheetName, cell, data[i][field])
		if err != nil {
			fmt.Fprint(w, err.Error())
		}
	}
}

err := file.Write(w)
if err != nil {
	fmt.Fprint(w, err.Error())
}

}

func main() {
http.HandleFunc("/write_to_excel", WriteToExcel)
err := http.ListenAndServe("10.104.238.25:8013", nil)
if err != nil {
fmt.Fprint(os.Stderr, err)
}
}`

I run this code, and the file can open with Microsoft excel, but I use the company self developed go framework in project to realize the function, so I can't ensure if it's caused by this

[haiyang-zeng]

it's works well use raw go framework, but seems not good in self developed go framework....

[xuri]

it's works well use raw go framework, but seems not good in self developed go framework....

Sorry, I can't confirm that the issue related with this library.

[haiyang-zeng]

I see, doesn't matter, thanks for you reply.