excel generated but when i open it in excel 2016, i am getting an XML error

maniya

Hi, I have a excel sheet getting generated but when it is exported to excel which is internally using a jszip llibrary , i am getting error only in office excel 2016, it works on other platforms like libreoffice, wps office, open office

the error is:

This XML file does not appear to have any style information associated with it. The document tree is shown below.
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error231600_01.xml</logFileName>
<summary>Errors were detected in file 'C:\Users\Owner\Downloads\paymentReport_20240715_124433_6834.xlsx'</summary>
<repairedRecords>
<repairedRecord>Repaired Records: Worksheet properties from /xl/workbook.xml part (Workbook)</repairedRecord>
</repairedRecords>
</recoveryLog>

the error is quite vague as i am unable to add debugging, because this gives no indicator how it happening, i even tried adding one record but error happens, now excel does not show any kid of warning that strings are served as numbers or etc etc

not sure at this point, how can i debug this

please guide

maniya

i even tried to sanitize the sheetnames and limiting them to 31 chrs but still an error, if need i can attach excel sheet

kthorngren

Are you doing any customization of the Excel export output?

Can you post a link to your page or create a test case that replicates the error so we can help debug?
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

kthorngren

Does this example work with Excel 2016?

Kevin

maniya

@kthorngren that works, foing to my previous question, the group exportt does throw an error
https://datatables.net/forums/discussion/79230/row-grouping-success-but-missing-styles#latest

maniya

i changed my code
https://jsfiddle.net/64pcqgf2/4/

kthorngren

foing to my previous question, the group exportt does throw an error

Yes, that example is highly customized. The error suggests that something in the generated sheet template is not compatible with Excel 2016. I don't have Excel 2016 to try it and don't know what the issue might be. I'm not an expert with the Excel XML format. Someone else may have Excel 2016 to take a look.

I would start by trying the last example provided in the thread you linked to. Does it work with Excel 2016?

<logFileName>error231600_01.xml</logFileName>

Did you look at the generated log file to see if there is more info?

i changed my code

Please provide the steps to replicate the issue using your test case:
https://jsfiddle.net/64pcqgf2/4/

Kevin

kthorngren

As suggested in the excelHtml5 docs you may want to look at using SheetJS for this export.

Kevin

maniya

when you say to use SHEETJS, i think i have to change a lot of code which is too much work it seems.

maniya

I am attaching the files to this post which replicates the error

https://datatables.net/forums/uploads/editor/v6/fp69jo7dlqad.zip

kthorngren

when you say to use SHEETJS, i think i have to change a lot of code which is too much work it seems.

Possibly, I have not looked at SheetsJS. It probably has better support for the Excel formats. I think it will read a table. It may grab the RowGroup rows as they are in the tbody. Also possibly there is a way to provide data via Javascript which would allow you to build the rows and groups and totals via JS.

It may be more work to learn the XML format to understand why the error is occurring with using the custom output than to use SheetJS.

I am attaching the files to this post which replicates the error

I ran it and didn't see the error. Not surprising since I don't have Excel 2016.

1. Did you try the last example from this thread with Excel 2016? Does it have the same error?

2. Have you looked at the error log generated?

<logFileName>error231600_01.xml</logFileName>

Kevin

maniya

i am not sure how can i look at this error, i did renamed the file to zip and it created some xml files inside it but i no issue in there
not sure how can i fix it

kthorngren

i am not sure how can i look at this error, i did renamed the file to zip and it created some xml files inside it but i no issue in there

Are you referring to the error log or something else?

not sure how can i fix it

Stack Overflow might be a good resource to troubleshoot the error you are getting. Maybe use these tags: xml, openxml, excel

Did you try the last example from this thread with Excel 2016? Does it have the same error?

If the example I provided doesn't generate the error then its something added afterwards. Possibly the problem is from the added styles.

Kevin

kthorngren

Just for fun I decided to show a simple SheetJS example with row grouping:
https://live.datatables.net/wovozahe/1/edit

It uses a custom button like this example.

I started with the export example from the docs. Instead of fetching a JSON file I used rows().data() with toArray() to get the row data. Use selector-modifier is desired.

columns().titles() is used to get the header titles.

A for loop is used to build the row data passed to SheetJS. It removes the id property as its not displayed in the table. It checks tor the group name to change and inserts a row for the group name. Here you will perform your sum calculations, etc.

Nothing is changed below the /* generate worksheet and workbook */ section of the code except this statement to apply the table header titles:

XLSX.utils.sheet_add_aoa(worksheet, [ titles ], { origin: "A1" });

Your mission, should you decide to accept it, is to learn how to format the RowGroup rows/cells the way you want.

Does the example I linked to work with Excel 2016? If yes it might be easier to lear hwo o use SheetJS than to learn/troubleshoot how to use the XNL format.

Kevin