Buttons3.0.1 - Excel export error with "excel found unreadable content in file"

Seb8

When I am trying to do Excel Export facing the error "Excel found unreadable content in the file.
It would ask you to trust the Excel and if you do trust the source of this workbook, click Yes"> Once I click 'Yes', File tries to open in Protected view but throws an error saying 'Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded. Replaced Part: /xl/worksheets/sheet1.xml part with XML error.

I saw an earlier thread with the same issue but it happened at 1.9.2

I tried html5excel and normally excel the export excel both have the same issue.
CSV works fine somehow.
I've tried Google Chrome on MacOS and Win10.

The second related question is that to make Buttons appear, it looks like "dom": 'lBfrtip', is mandatory
However, after applying the above dom setting, the position of length menu, buttons, searchpane, paging menu, information are moved to center instead of their default position, and I cannot make them return to their default position on the page.

Does someone have the same two issues above?

Buttons3.0.1
DataTables 2.0.3
JQuery 3.7.1

tangerine

dom has been superceded by layout.
What data does Excell find unreadable?

Seb8

Hi Tangerine

Thanks for the reply
Can you elaborate in more detail about using layout instead of DOM.
I've been struggling with buttons not showing up until I added "dom": 'lBfrtip'
Hence I thought it was a mandatory thing

Not sure how to find which data is unreadable? is there a way to know so that I can provide in detail? those are Excel with IP address, hostname, switch name, and switch port information.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error448440_01.xml</logFileName><summary>Errors detected in file 'C:\Users\abc\Downloads\Report.xlsx'</summary><additionalInfo><info>Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.</info></additionalInfo><repairedRecords><repairedRecord>Repaired Records: Cell information from /xl/worksheets/sheet1.xml part</repairedRecord></repairedRecords></recoveryLog>

kthorngren

Can you elaborate in more detail about using layout instead of DOM.

See this doc.

Not sure how to find which data is unreadable?

ITs hard to say without seeing the problem. Do you have any customization configured for the Excel export? Please provide a link to your page or test case replicating the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

Seb8

Hi Kevin

Thank you I'll take a look

For Excel export error, I don't have any customization and only with "buttons": ['excel']
However, I do find something suspicious. I'll elaborate more in detail below.

I'm using Python Flask to send data to HTML to handle.
The source data I have is an excel file, and I changed it to dict format and sent it to HTML

data = pd.read_excel(latest_file).to_dict(orient='records')
return render_template('index.html', output_file_path=os.path.basename(latest_file), data=data)

The problem is, the excel does have empty cell, which was fine.
And when it shows at datatables, it also works fine. The only difference is that instead of showing empty cell, datatables present the cell value as NaN. That's also fine to me.
However, I found whenever I export Excel with cell with value NaN, the error occurs when I open the xlsx file. if I need to keep the empty cell in my source xlsx file, is there a way datatables or somehow I can overcome the export error?

Seb8

just run the datatables debugger
Version Check: DataTables and Buttons are Up to Date
Check for common issues: 16 tests complete. No failures or warnings found!

kthorngren

I would try using Orthogonal data like this example. In the columns.render function check to see if the cell value is NaN and return en empty string otherwise return the cell's data.

Kevin