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

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

Seb8Seb8 Posts: 4Questions: 1Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • tangerinetangerine Posts: 3,348Questions: 36Answers: 394

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

  • Seb8Seb8 Posts: 4Questions: 1Answers: 0

    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>

  • kthorngrenkthorngren Posts: 20,258Questions: 26Answers: 4,763

    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

  • Seb8Seb8 Posts: 4Questions: 1Answers: 0

    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?

  • Seb8Seb8 Posts: 4Questions: 1Answers: 0

    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!

  • kthorngrenkthorngren Posts: 20,258Questions: 26Answers: 4,763
    Answer ✓

    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

Sign In or Register to comment.