One Corrupt Sheet #37
Description
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