aoa_to_sheet may produce <c r="A1"><v>NaN</v> #2897

New Issue

cristian-cypag · 2023-02-28T15:49:06Z

cristian-cypag commented

2023-02-28 15:49:06 +00:00

I used ParseInt to fillout a specific column of my aoa (Array of Array) and after using

const sheet = xlsx.utils.aoa_to_sheet(aoa);
const workbook = xlsx.utils.book_append_sheet(workbook, sheet, 'sheet_name');
xlsx.writeFile(workbook, filename.xlsx)

for NaN values the sheet1.xml file showed NaN

this makes excel prompt out the following alert at file opening:

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

with the following not-so-much-usefull recovery log:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error210000_01.xml</logFileName><summary>Errors were detected in file 'C:\Users\crival.CYPAG\Repos\cyConfig-backend\test\output_.xlsx'</summary><repairedRecords><repairedRecord>Repaired Records: Cell information from /xl/worksheets/sheet1.xml part</repairedRecord></repairedRecords></recoveryLog>

I fixed my code and I'm not providing NaN values anymore to my aoa but others may encounter the same issue and loose time to understand why suddenly the exported .xlsx file is corrupted. It would be super cool if aoa_to_sheet would convert NaN to acceptable excel info.

I used ParseInt to fillout a specific column of my aoa (Array of Array) and after using ``` const sheet = xlsx.utils.aoa_to_sheet(aoa); const workbook = xlsx.utils.book_append_sheet(workbook, sheet, 'sheet_name'); xlsx.writeFile(workbook, filename.xlsx) ``` for NaN values the sheet1.xml file showed <c r="A1"><v>NaN</v> this makes excel prompt out the following alert at file opening: > We found a problem with some content in 'filename.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes. ![image](/attachments/b9ed5ffa-9ba7-41af-85ac-aee75d826eea) with the following not-so-much-usefull recovery log: ``` <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error210000_01.xml</logFileName><summary>Errors were detected in file 'C:\Users\crival.CYPAG\Repos\cyConfig-backend\test\output_.xlsx'</summary><repairedRecords><repairedRecord>Repaired Records: Cell information from /xl/worksheets/sheet1.xml part</repairedRecord></repairedRecords></recoveryLog> ``` I fixed my code and I'm not providing NaN values anymore to my aoa but others may encounter the same issue and loose time to understand why suddenly the exported .xlsx file is corrupted. It would be super cool if aoa_to_sheet would convert NaN to acceptable excel info.

image.png

20 KiB

sheetjs commented

2023-02-28 18:47:07 +00:00

The error logs definitely leave much to be desired.

In our Pro builds , the NaN guard is in the export codecs. It was decided that the best approach was to show an error in the console and skip NaN values. We will port that over.

Additionally, it probably makes sense to do something sensible in aoa_to_sheet and json_to_sheet. The options are:

A) skip cells, optionally with a warning
B) throw an error
C) translate to an error cell like #DIV/0! or #VALUE!

It's hard to say what is the right option. The error cell mimics the result if you type values in Excel, but it might be surprising for users who didn't know they were exporting NaN values.

The error logs definitely leave much to be desired. In our [Pro builds](https://sheetjs.com/pro) , the NaN guard is in the export codecs. It was decided that the best approach was to show an error in the console and skip NaN values. We will port that over. Additionally, it probably makes sense to do something sensible in `aoa_to_sheet` and `json_to_sheet`. The options are: A) skip cells, optionally with a warning B) throw an error C) translate to an error cell like `#DIV/0!` or `#VALUE!` It's hard to say what is the right option. The error cell mimics the result if you type values in Excel, but it might be surprising for users who didn't know they were exporting NaN values.

sheetjs referenced this issue from a commit

2023-04-23 08:17:11 +00:00

Export NaN/Infinity to error cells (fixes #2897)

sheetjs closed this issue

2023-04-23 08:17:11 +00:00

Sign in to join this conversation.