Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

While opening excel file . It is showing recovery error #798

Closed
prakash0101 opened this issue Mar 5, 2021 · 17 comments
Closed

While opening excel file . It is showing recovery error #798

prakash0101 opened this issue Mar 5, 2021 · 17 comments
Labels
needs more info This issue can't reproduce, need more info

Comments

@prakash0101
Copy link

prakash0101 commented Mar 5, 2021

Excel_error
SA_Opening_and_Onboarding_NPS_and_FOCUS_Salary_SO_Scores_Feb`21 (2).xlsx

I have screen shot as well as excel file for your refernece.

@xuri
Copy link
Member

xuri commented Mar 6, 2021

Thanks for your feedback, could you create the issue by an issue template and provide more details?

@xuri xuri added the needs more info This issue can't reproduce, need more info label Mar 6, 2021
@AnotherCoolDude
Copy link

Description

Excel can not open saved file. See error above (once you click "yes" it simply says "file is damaged").

Steps to reproduce the issue:

create "test_file.xlsx" in src folder. File works with Excel without problems.

main.go

package main

import (
	"fmt"

	"github.com/360EntSecGroup-Skylar/excelize/v2"
)

func main() {
	//get file
	file, err := open_excel_file("test_file.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}

	//save file
	save_file(file, "test_output.xlsx")
}

func open_excel_file(path string) (*excelize.File, error) {
	f, err := excelize.OpenFile(path)
	if err != nil {
		return excelize.NewFile(), err
	}
	return f, nil
}

func save_file(file *excelize.File, name string) error {
	err := file.SaveAs(name)
	return err
}

Describe the results you expected:

file can be opened with excel

Output of go version:

go version go1.16.2 darwin/amd64

Excelize version or commit ID:
v2.3.2

Environment details (OS, Microsoft Excel™ version, physical, etc.):
macOS Mojave version 10.14.6
Microsoft Excel 16.45

@AnotherCoolDude
Copy link

It works if I copy the content of the test_file (which has been generated automatically as the result of a db query) into a new file. So there must be something wrong with the meta data of the generated file? I am not sure how to debug this

@xuri
Copy link
Member

xuri commented Mar 23, 2021

Hi @AnotherCoolDude, thanks for your feedback, could you provide the code to produce this issue? Note that open data stream from database query by OpenReader.

@AnotherCoolDude
Copy link

Hi @xuri
the code above does produce the issue at least for me. The export I receive is already an excel file, so there is no stream to work with. But I think the issue can be closed, since the reason for my issue is the excel file itself, not your library.

@xuri
Copy link
Member

xuri commented Mar 23, 2021

Hi @AnotherCoolDude, thanks for your feedback. There still no more details about this issue from the issue author. I close this issue and welcome left comments if you have any more questions. /cc @prakash0101

@xuri xuri closed this as completed Mar 23, 2021
@Rorke76753
Copy link

I got this issue either. Have you fixed this?

@xuri
Copy link
Member

xuri commented Dec 1, 2021

Hi @Rorke76753, there are multiple reasons for cause file corrupted, which version of the Excelize are you using? Could you show us a complete, standalone example program or reproducible demo? and please try to upgrade to the master branch code.

@Rorke76753
Copy link

Hi @Rorke76753, there are multiple reasons for cause file corrupted, which version of the Excelize are you using? Could you show us a complete, standalone example program or reproducible demo? and please try to upgrade to the master branch code.

Output of go version:

go version go1.13.15 darwin/amd64

Excelize version or commit ID:
v2.3.2

Environment details (OS, Microsoft Excel™ version, physical, etc.):
macOS Big Sur 11.6.1
Microsoft Excel 16.45

Code:

type BasicExcelExporter struct {
	excelExporter iSpecificExporter
}

func (b BasicExcelExporter) ExportAsExcel(data []excel_handler.IExcelDbObject) (string, error) {
	file := excelize.NewFile()

	index := 0
	sheetIndex := 1
	filePath := b.excelExporter.generateFileName()


	writeData := data[index:]
	err := b.writeSheet(file, data, sheetIndex)
	if err != nil {
		return "", err
	}
  
	err := file.SaveAs(filePath)
	if err != nil {
		return "", err
	}
	return filePath, nil
}

func (b BasicExcelExporter) writeSheet(file *excelize.File, data []excel_handler.IExcelDbObject, sheetIndex int) error {
	sheetName := b.excelExporter.getSheetName() + strconv.Itoa(sheetIndex)
	_ = file.NewSheet(sheetName)
	err := b.setTemplateHeader(file, sheetName)
	if err != nil {
		return err
	}
	err = b.setCellValue(file, sheetName, data)
	if err != nil {
		return err
	}
	return nil
}

//set first row of the sheet
func (b BasicExcelExporter) setTemplateHeader(file *excelize.File, name string) error {
	topRow := 1
	header := b.excelExporter.getTemplateHeader()
	for i := range header {
		column := convertDecimalToBase26(i + 1)
		cellName := getCellName(topRow, column)
		err := file.SetCellValue(name, cellName, header[i])
		if err != nil {
			return err
		}
	}

	return nil
}

//input the values 
func (b BasicExcelExporter) setCellValue(file *excelize.File, sheetName string, data []excel_handler.IExcelDbObject) error {
	row := 2
	for i := range data {
    //values is a set of function which like func(excel_handler.IExcelDbObject) string
    //this function provide the getter from struct field to a string value which store in excel
		values := data[i].GetExcelValue()
		for j := range values {
			column := convertDecimalToBase26(j + 1)
			cell := getCellName(row, column)
			err := file.SetCellValue(sheetName, cell, values[j](data[i]))
			if err != nil {
				return err
			}
		}
		row++
	}
	return nil
}

func getCellName(row int, column string) string {
	return fmt.Sprintf("%s%d", column, row)
}

func convertDecimalToBase26(columnNumber int) string {
	ans := make([]byte, 0)
	for columnNumber > 0 {
		columnNumber--
		ans = append(ans, 'A'+byte(columnNumber%26))
		columnNumber /= 26
	}
	for i, n := 0, len(ans); i < n/2; i++ {
		ans[i], ans[n-1-i] = ans[n-1-i], ans[i]
	}
	return string(ans)
}

@xuri
Copy link
Member

xuri commented Dec 1, 2021

Hi @Rorke76753, what's your input data look likes, could you provides a simple code with entry main function to reproduce this issue? In addition, just using ColumnNumberToName to convert the integer to Excel sheet column title.

@Rorke76753
Copy link

Rorke76753 commented Dec 1, 2021

Hi @Rorke76753, there are multiple reasons for cause file corrupted, which version of the Excelize are you using? Could you show us a complete, standalone example program or reproducible demo? and please try to upgrade to the master branch code.

Output of go version:

go version go1.13.15 darwin/amd64

Excelize version or commit ID: v2.3.2

Environment details (OS, Microsoft Excel™ version, physical, etc.): macOS Big Sur 11.6.1 Microsoft Excel 16.45

Code:

type BasicExcelExporter struct {
	excelExporter iSpecificExporter
}

func (b BasicExcelExporter) ExportAsExcel(data []excel_handler.IExcelDbObject) (string, error) {
	file := excelize.NewFile()

	index := 0
	sheetIndex := 1
	filePath := b.excelExporter.generateFileName()


	writeData := data[index:]
	err := b.writeSheet(file, data, sheetIndex)
	if err != nil {
		return "", err
	}
  
	err := file.SaveAs(filePath)
	if err != nil {
		return "", err
	}
	return filePath, nil
}

func (b BasicExcelExporter) writeSheet(file *excelize.File, data []excel_handler.IExcelDbObject, sheetIndex int) error {
	sheetName := b.excelExporter.getSheetName() + strconv.Itoa(sheetIndex)
	_ = file.NewSheet(sheetName)
	err := b.setTemplateHeader(file, sheetName)
	if err != nil {
		return err
	}
	err = b.setCellValue(file, sheetName, data)
	if err != nil {
		return err
	}
	return nil
}

//set first row of the sheet
func (b BasicExcelExporter) setTemplateHeader(file *excelize.File, name string) error {
	topRow := 1
	header := b.excelExporter.getTemplateHeader()
	for i := range header {
		column := convertDecimalToBase26(i + 1)
		cellName := getCellName(topRow, column)
		err := file.SetCellValue(name, cellName, header[i])
		if err != nil {
			return err
		}
	}

	return nil
}

//input the values 
func (b BasicExcelExporter) setCellValue(file *excelize.File, sheetName string, data []excel_handler.IExcelDbObject) error {
	row := 2
	for i := range data {
    //values is a set of function which like func(excel_handler.IExcelDbObject) string
    //this function provide the getter from struct field to a string value which store in excel
		values := data[i].GetExcelValue()
		for j := range values {
			column := convertDecimalToBase26(j + 1)
			cell := getCellName(row, column)
			err := file.SetCellValue(sheetName, cell, values[j](data[i]))
			if err != nil {
				return err
			}
		}
		row++
	}
	return nil
}

func getCellName(row int, column string) string {
	return fmt.Sprintf("%s%d", column, row)
}

func convertDecimalToBase26(columnNumber int) string {
	ans := make([]byte, 0)
	for columnNumber > 0 {
		columnNumber--
		ans = append(ans, 'A'+byte(columnNumber%26))
		columnNumber /= 26
	}
	for i, n := 0, len(ans); i < n/2; i++ {
		ans[i], ans[n-1-i] = ans[n-1-i], ans[i]
	}
	return string(ans)
}
func TestExport(t *testing.T) {
	exporter := GenerateTestExporter()
	_, err := exporter.ExportAsExcel([]excel_handler.IExcelDbObject{testStruct{a: "a"}, testStruct{a: "b"}})
	if err != nil {
		panic(err.Error())
	}
}

func GenerateTestExporter() BasicExcelExporter {
	return BasicExcelExporter{excelExporter: testExporter{}}
}

type testStruct struct {
	a string
}

func (t testStruct) SetTime(time uint32) {
}

func (t testStruct) GetExcelValue() []func(object excel_handler.IExcelDbObject) string {
	return getters
}
func getA(object excel_handler.IExcelDbObject) string {
	tmp := object.(testStruct)
	return tmp.a
}

var getters = []func(object excel_handler.IExcelDbObject) string{getA}

type testExporter struct {
}

func (t testExporter) getSheetName() string {
	return "sheet"
}

func (t testExporter) generateFileName() string {
	return t.getFilePath() + "test.xlsx"
}

func (t testExporter) getFilePath() string {
	//temp, project relative path
  return ""
}

func (t testExporter) getTemplateHeader() excel_handler.ExcelTplRow {
	return excel_handler.ExcelTplRow{"test"}
}

package excel_handler
type IExcelDbObject interface 
	GetExcelValue() []func(object IExcelDbObject) string
}

this is my test method, and how can i send my export file to you to figure the problem

@Rorke76753
Copy link

@xuri I fix this problem now. It was caused by my create sheet name "Sheet1" conflict with the default sheet.Thank.

@jamalkaksouri
Copy link

I'm using the most recent package version, but when I try to open the exported file in Microsoft Excel, I get this error popping up.
The issue is that this file with the extension opens and displays correctly in the VS Code environment, but it doesn't open properly in Microsoft Excel!

bbb
ccc
eee

@xuri
Copy link
Member

xuri commented Aug 11, 2023

Hi @jamalkaksouri, which version of Go are you using? There are some incompatible changes in the Go 1.21.0 encoding/xml library, which will cause generate corrupted workbook, please try to using Go 1.20 or previous Go version, also reference the issue #1465, #1595, #1603 and and golang/go#61881.

@jamalkaksouri
Copy link

I appreciate your quick reply.
Yes, I'm using Go version 1.21.0. In the previous version, 1.20, and lower, there is no issue.

@jeannot-muller
Copy link

I bumped into this today. I can confirm that downgrading GO fixes the issue. If it might help: GO 1.20.7 didn't work either for me, but after downgrading to go1.19.12 everything is working again (using exelize v2.8.0. Thank you.

@harrybui03

This comment was marked as off-topic.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
needs more info This issue can't reproduce, need more info
Projects
None yet
Development

No branches or pull requests

7 participants