search

stephen-hardy / xlsx.js

XLSX.js is a JavaScript library for converting the data in base64 XLSX files into JavaScript objects - and back! Please note that this library is licensed under the Microsoft Office Extensible File License - a license NOT approved by the OSI. While this license is based off of the MS-PL, which is OSI-approved, there are significant differences.
http://blog.innovatejs.com/?tag=xlsx-js
Other
575 stars 122 forks source link

One Corrupt Sheet #37

Open ltodd opened 9 years ago

ltodd commented 9 years ago

I've got an implementation that works except for some uncommon and bizarre cases. So I've Isolated an example of a worksheet value that causes the issue.

The xlsx is created but when it's loaded in excel, the 2nd sheet is corrupted and empty after repair. Excel says it's an issue with the sheets.xml not having a single root node. My guess is that the file is empty on extract. This original value for worksheets causes the error (yes I know it's long but modifying it further tends to remove the bug):

worksheets = JSON.parse('[{"name":"Total cost - Pass","data":[["","12 AM","1 AM","2 AM","3 AM","4 AM","5 AM","6 AM","7 AM","8 AM","9 AM","10 AM","11 AM","12 PM","1 PM","2 PM","3 PM","4 PM","5 PM","6 PM","7 PM","8 PM","9 PM","10 PM","11 PM","Daily Totals"],["9/11/14","2.04","2.25","0.00","1.35","2.64","12.62","31.13","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","52.04"],["Hourly totals","2.04","2.25","0.00","1.35","2.64","12.62","31.13","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","52.04"],["Generated from:","redacted"]]},{"name":"Total cost - Comm","data":[["","12 AM","1 AM","2 AM","3 AM","4 AM","5 AM","6 AM","7 AM","8 AM","9 AM","10 AM","11 AM","12 PM","1 PM","2 PM","3 PM","4 PM","5 PM","6 PM","7 PM","8 PM","9 PM","10 PM","11 PM","Daily Totals"],["9/11/14","0.96","1.06","0.00","0.64","1.24","5.93","14.64","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","24.47"],["Hourly totals","0.96","1.06","0.00","0.64","1.24","5.93","14.64","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","24.47"],["Generated from:","redacted"]]},{"name":"Coverage","data":[["","12 AM","1 AM","2 AM","3 AM","4 AM","5 AM","6 AM","7 AM","8 AM","9 AM","10 AM","11 AM","12 PM","1 PM","2 PM","3 PM","4 PM","5 PM","6 PM","7 PM","8 PM","9 PM","10 PM","11 PM","Daily Totals"],["9/11/14","100.00","100.00","100.00","100.00","100.00","100.00","93.33","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","99.05"],["Hourly totals","100.00","100.00","100.00","100.00","100.00","100.00","93.33","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","99.05"],["Generated from:","redacted"]]}]')

Wow! You've found a huge value that makes it crash. Woopty freakin doo.

Here's where it gets weird. If i change worksheets[1].data[0][0] = " " instead of "", IT WORKS.

worksheets = JSON.parse('[{"name":"Total cost - Pass","data":[["","12 AM","1 AM","2 AM","3 AM","4 AM","5 AM","6 AM","7 AM","8 AM","9 AM","10 AM","11 AM","12 PM","1 PM","2 PM","3 PM","4 PM","5 PM","6 PM","7 PM","8 PM","9 PM","10 PM","11 PM","Daily Totals"],["9/11/14","2.04","2.25","0.00","1.35","2.64","12.62","31.13","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","52.04"],["Hourly totals","2.04","2.25","0.00","1.35","2.64","12.62","31.13","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","52.04"],["Generated from:","redacted"]]},{"name":"Total cost - Comm","data":[[" ","12 AM","1 AM","2 AM","3 AM","4 AM","5 AM","6 AM","7 AM","8 AM","9 AM","10 AM","11 AM","12 PM","1 PM","2 PM","3 PM","4 PM","5 PM","6 PM","7 PM","8 PM","9 PM","10 PM","11 PM","Daily Totals"],["9/11/14","0.96","1.06","0.00","0.64","1.24","5.93","14.64","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","24.47"],["Hourly totals","0.96","1.06","0.00","0.64","1.24","5.93","14.64","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","24.47"],["Generated from:","redacted"]]},{"name":"Coverage","data":[["","12 AM","1 AM","2 AM","3 AM","4 AM","5 AM","6 AM","7 AM","8 AM","9 AM","10 AM","11 AM","12 PM","1 PM","2 PM","3 PM","4 PM","5 PM","6 PM","7 PM","8 PM","9 PM","10 PM","11 PM","Daily Totals"],["9/11/14","100.00","100.00","100.00","100.00","100.00","100.00","93.33","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","99.05"],["Hourly totals","100.00","100.00","100.00","100.00","100.00","100.00","93.33","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","99.05"],["Generated from:","redacted"]]}]')

Here's another one. If i set worksheets[1].data[3][2] = " " instead of having it undefined, it also works.

worksheets = JSON.parse('[{"name":"Total cost - Pass","data":[["","12 AM","1 AM","2 AM","3 AM","4 AM","5 AM","6 AM","7 AM","8 AM","9 AM","10 AM","11 AM","12 PM","1 PM","2 PM","3 PM","4 PM","5 PM","6 PM","7 PM","8 PM","9 PM","10 PM","11 PM","Daily Totals"],["9/11/14","2.04","2.25","0.00","1.35","2.64","12.62","31.13","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","52.04"],["Hourly totals","2.04","2.25","0.00","1.35","2.64","12.62","31.13","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","52.04"],["Generated from:","redacted"]]},{"name":"Total cost - Comm","data":[["","12 AM","1 AM","2 AM","3 AM","4 AM","5 AM","6 AM","7 AM","8 AM","9 AM","10 AM","11 AM","12 PM","1 PM","2 PM","3 PM","4 PM","5 PM","6 PM","7 PM","8 PM","9 PM","10 PM","11 PM","Daily Totals"],["9/11/14","0.96","1.06","0.00","0.64","1.24","5.93","14.64","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","24.47"],["Hourly totals","0.96","1.06","0.00","0.64","1.24","5.93","14.64","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","24.47"],["Generated from:","redacted"," "]]},{"name":"Coverage","data":[["","12 AM","1 AM","2 AM","3 AM","4 AM","5 AM","6 AM","7 AM","8 AM","9 AM","10 AM","11 AM","12 PM","1 PM","2 PM","3 PM","4 PM","5 PM","6 PM","7 PM","8 PM","9 PM","10 PM","11 PM","Daily Totals"],["9/11/14","100.00","100.00","100.00","100.00","100.00","100.00","93.33","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","99.05"],["Hourly totals","100.00","100.00","100.00","100.00","100.00","100.00","93.33","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","99.05"],["Generated from:","redacted"]]}]')

Finally, If I remove the first sheet, it works.

worksheets = JSON.parse('[{"name":"Total cost - Comm","data":[["","12 AM","1 AM","2 AM","3 AM","4 AM","5 AM","6 AM","7 AM","8 AM","9 AM","10 AM","11 AM","12 PM","1 PM","2 PM","3 PM","4 PM","5 PM","6 PM","7 PM","8 PM","9 PM","10 PM","11 PM","Daily Totals"],["9/11/14","0.96","1.06","0.00","0.64","1.24","5.93","14.64","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","24.47"],["Hourly totals","0.96","1.06","0.00","0.64","1.24","5.93","14.64","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","24.47"],["Generated from:","redacted"]]},{"name":"Coverage","data":[["","12 AM","1 AM","2 AM","3 AM","4 AM","5 AM","6 AM","7 AM","8 AM","9 AM","10 AM","11 AM","12 PM","1 PM","2 PM","3 PM","4 PM","5 PM","6 PM","7 PM","8 PM","9 PM","10 PM","11 PM","Daily Totals"],["9/11/14","100.00","100.00","100.00","100.00","100.00","100.00","93.33","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","99.05"],["Hourly totals","100.00","100.00","100.00","100.00","100.00","100.00","93.33","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","99.05"],["Generated from:","redacted"]]}]')

If I copy the value of sheet 1 into sheet 2, It works fine. It's this one sheet that causes trouble. Those first two tricks don't need to be done on other sheets for them to work.

I've walked through the xlsx.js code while it runs and xlWorksheets.files.xl.worksheets.sheet2.xml looks exactly like the other sheets with a few different numbers. Any ideas? Do I need to provide more info?

Thanks