XLSX files produced by this library need recover by Microsoft Excel when open it.

dablelv commented 8 months ago

The version of tealeg/xlsx is v3.3.4, and the v1 also has the same problem.

The returned excel file from backend service api and download through browser, then open it and the Microsoft Excel will thorw a warning message:

We found a problem with some content in xxx.xslx. Do you want us to try to revocer as much as we can? If you trust the source of this workbook, click Yes.

If I click yes, the excel file will be repaired successfully and can be opened.

My backend service use Gin framework and the summary code to generate excel file and return it to browser as bellow:

func ExportHandler(c *gin.Context) {
    file := xlsx.NewFile()
    sheet, _ := file.AddSheet("InsuranceActive")

    // Add titles.
    titles := []string{
        "Id",
        "CreatedAt",
        "UpdatedAt",
        "CreatorId",
        "UpdaterId",
        "CompanyName",
    }
    row := sheet.AddRow()
    for _, title := range titles {
    cell := row.AddCell()
    cell.Value = title
    }

    // Add rows
    ...

    // Return the excel file.
    c.Header("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    filename := fmt.Sprintf("IndustryInfos_%v.xlsx", time.Now().Format("2006-01-02T15:04:05"))
    c.Header("Content-Disposition", "attachment; filename="+filename)
    if err := file.Write(c.Writer); err != nil {
        c.JSON(http.StatusOK, "failed")
        return
    }
    c.JSON(http.StatusOK, "suceess")
}

tealeg commented 8 months ago

@dablelv hi! This is quite a common error pattern, it usually means that some XML in the file is compliant with the standard, but not structured exactly as Excel would do it. The best debugging information you could give me is to save two versions of the generated file - one before and after it has been recovered. Comparing the XML in the xlsx files would give us a clue what it is that Excel doesn't like.

dablelv commented 8 months ago

@dablelv hi! This is quite a common error pattern, it usually means that some XML in the file is compliant with the standard, but not structured exactly as Excel would do it. The best debugging information you could give me is to save two versions of the generated file - one before and after it has been recovered. Comparing the XML in the xlsx files would give us a clue what it is that Excel doesn't like.

I found the same issue already commited many years ago. The issue link is https://github.com/tealeg/xlsx/issues/53. There has one before and after it has been recovered excel file. The previous issue has been closed, but the problem remains.

tealeg commented 8 months ago

@dablelv - it is unlikely that it's actually the same issue. The symptom is seen for any case where Excel expects the data it receives to follow an exact pattern than is more restrictive than the Office OpenXML standard. The only way for me to know what was wrong is to reproduce the exact case you hit (by having all of the code and the input data) or by having examples of the file before and after Excel has repaired it.

dablelv commented 8 months ago

@dablelv - it is unlikely that it's actually the same issue. The symptom is seen for any case where Excel expects the data it receives to follow an exact pattern than is more restrictive than the Office OpenXML standard. The only way for me to know what was wrong is to reproduce the exact case you hit (by having all of the code and the input data) or by having examples of the file before and after Excel has repaired it.

ok, here is the repaired before and after sample files. xlsx_before_repaired.xlsx xlsx_after_repaired.xlsx

I found that the repaired file's size is more than not repaired.

tealeg commented 8 months ago

@dablelv thanks! I'll try to take a look in the coming days.

github-actions[bot] commented 6 months ago

Stale issue message

github-actions[bot] commented 4 months ago

Stale issue message

jlconrad3 commented 3 months ago

Any updates on this issue? I'm running across the same problem, specifically with timestamps. I've tried every cell.Set... function available. They all result in the same outcome. I read through the issue notes and I will do the same as the previous person by providing a before and after "repaired" file for comparison.

tealeg commented 3 months ago

@dablelv - I'm sorry, I know this has taken an age to get around to. In the case of your files, I think the route cause is that the font set-up in the file cannot render the text embedded in the spreadsheet. The repair XL seems to have done is to replace the font Arial, with the font "Song Dynasty" (宋体) in the file. I'll have to look a bit deeper to work out a solution.

tealeg commented 3 months ago

@jlconrad3 - as I mentioned above. Almost any error in the generated XLSX file would generate this response from excel (it's very difficult to put any two reports together as a single issue. Feel free to provide before and after examples and I'll try to look.

jlconrad3 commented 3 months ago

I finally figured out my issue. It was the column width function. Wants I removed that function call, my exports to excel started working. I'm newish to this code base so I can't confirm that it ever worked, but it's been in the code for 3 year so I'm guessing at some point it worked.

sheet.SetColWidth

tealeg commented 3 months ago

@jlconrad3 interesting, I'll take a look.