Problem

After a bit of time trying to figure out why my files were always being corrupted when using workbook.xlsx.writeFile(), I came to find out it was the named ranges (under the formulas tab) in the template I was reading that was causing the issue - more specifically, it was the way the ranges were being extracted and re-entered into a "new" xlsx file. To give you an idea of what I was going through, here's the xml for causing the issue:

First, the Excel Error

Excel found unreadable content in 'test.xlsx'. Do you want to recover the contents of this 
workbook? If you trust the source of this workbook, click Yes

Working XLSX Example

<definedName name="_xlnm.Print_Area" localSheetId="0">'12pm'!$C$1:$AX$12</definedName>
<definedName name="_xlnm.Print_Titles" localSheetId="0">'12pm'!$C:$E</definedName>

How ExcelJS is Rewriting it

<definedName name="_xlnm.Print_Area" localSheetId="0">12pm!C1:AX12</definedName>
<definedName name="_xlnm.Print_Titles" localSheetId="0">&apos;12pm&apos;!$$C:$$E</definedName>

As you can see, the first definedName named _xlnm.Print_Area had all of its apostrophes and dollar signs removed. _xlnm.Print_Titles however is replacing every apostrophe with ' entities, and for some reason, it is adding a new dollar sign on every run of the program (i.e. if I were to run it again, it would rewrite it as '12pm'!$$$C:$$$E)

Fortunately, I found out the apostrophes and dollar signs aren't necessary for these kind of simple names, so the _xlnm.Print_Area name works perfectly fine. The unfortunate side of things is the _xlnm.Print_Titles corrupts the file and sends excel into repair mode, at which point it manually deletes the names causing the issue.

Because I got very impatient trying to navigate through the code to find out why the latter of the two was doing all sorts of weird stuff (hint hint cough cough document the code a little more thoroughly please ^^), I decided to hard-patch the issue with a little filter.

Solution

If you're having the same issue as I am, (v. 1.6.1) navigate to the exceljs/dist/es5/xlsx/xform/book/defined-name-xform.js file, go to line 25:25, and replace the

xmlStream.writeText(model.ranges.join(','));

with

xmlStream.writeText(model.ranges.join(',').split('').filter(s => s !== '$' && s !== "\'").join(''));

This will remove all apostrophes and dollar signs before it writes to a file, thus making it readable by Excel.

Of course this is just a temporary fix, and I hope @guyonroche will be able to fix it permanently soon.