Microsoft Excel needs to recover XLSX before opening

[anmolnar]

Hi,

Excel pops up a message whether I want to repair the file, because it has found some issues with it.
After recovery it says:

Repaired Records: Cell information from /xl/worksheets/sheet1.xml part

Recovery log:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error212120_01.xml</logFileName>
<summary>Errors were detected in file 'C:\0\test.xlsx'</summary>
<repairedRecords summary="Following is a list of repairs:">
<repairedRecord>Repaired Records: Cell information from /xl/worksheets/sheet1.xml part</repairedRecord>
</repairedRecords>
</recoveryLog>

Please advise.

Thanks,
Andor

[ashleyschuett]

If you have a formula in your spread sheet it could be that you did not assign a result to that cell as well.

I had the same issue when I had a cell with .formula = 'A1+A2' and .result='Nan'

[anmolnar]

No, I don't use formula or any other fancy stuff.
I generate a basic excel file with one sheet with the following code:

var workbook = new Excel.Workbook();
  var sheet = workbook.addWorksheet();
  sheet.columns = [
    { header: 'Account Name', key: 'name', width: 24},
    { header: 'Account ID', key: 'id', width: 40},
    { header: 'Created Date', key: 'createdDate', width: 15},
    { header: 'Last Modified Date', key: 'lastModifiedDate', width: 20},
    { header: 'Created By', key: 'createdBy', width: 40},
    { header: 'Number Of Devices', key: 'numberOfDevices', width: 10},
    { header: 'Number Of End Users', key: 'numberOfEndUsers', width: 10}
  ];

  _.forEach(accounts, function (acc) {
    sheet.addRow({
      name: acc.name,
      id: acc.id,
      createdDate: acc.created,
      lastModifiedDate: acc.lastModified,
      createdBy: acc.createdById,
      numberOfDevices: acc.deviceCount,
      numberOfEndUsers:  acc.endUserCount
    });
  });
  sheet.getRow(1).font = { bold: true };

[jayflo]

I am also getting a similar error:

Excel found unreadable content in ... Do you want to recover the contents of this workbook?

but for Worksheets:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error203880_01.xml</logFileName>
<summary>Errors were detected in file 'C:\...\something.xlsx'</summary>
-<repairedRecords summary="Following is a list of repairs:">
<repairedRecord>Repaired Records: Worksheet properties from /xl/workbook.xml part (Workbook)</repairedRecord>
</repairedRecords>
</recoveryLog>

I am also not using any validation.

[jayflo]

For me, it looks like the worksheet names are the issue: http://stackoverflow.com/questions/11706393/excel-found-unreadable-content-in-xslx

I need to use shorter names.

[Lenok]

@ashleyschuett In this case you should write cell with sheet name:
'Sheet1'!A1+'Sheet1'!A2 instead of A1+A2

[amkoehler]

@jayflo thanks! That was my problem as well. The xml error log from Excel wasn't very helpful with this problem.

[GaneshPL]

I too had the same problem, not sure if this will help everyone. Declare your Excel.Workbook() within the function, this way helped me.

[ShenTengTu]

@GaneshPL Can you provide some example?

Declare your Excel.Workbook() within the function

[GaneshPL]

I had a dedicated JS file to use in my nodejs project to prepare Excel file, where my DB.js file will call Excel.js to prepare, in that I had
var workbook = new Excel.Workbook()
declared along with importing modules, but that doesn't work as the module gets loaded only once per node start. So I declared the above within function in the Excel.js

/*
Other module imports
*/
var prepareExcel = (filename, callBack) =>{
var workbook = new Excel.Workbook()
//prepare Excel file.
}

[sanjayg11]

@ashleyschuett ashleyschuet,
i am facing issue with formulas as mentioned above. any solutions??

[rob2d]

I am having a similar issue. The error I get is:

Repaired Records: String properties from /xl/sharedStrings.xml part (Strings)

Not doing anything fancy here e.g. no formulas or colors, and each tab is within the 31 character limit.

I ran an XML validator pre-repair after extracting the generated xlsx for xl/sharedStrings.xml which (aside from standalone="yes") came back as a valid XML (https://codebeautify.org/xmlvalidator). I also stripped any non ASCII while printing out to the .xlsx file columns just to be sure. This is very weird.

[rob2d]

Just to update in case it helps anyone else, I finally found the source of my problem. I had some data generated in cells which exceeded the length allowed (32,767 characters). There's other constraints on things like row height/cell length/etc which when violated work in non Microsoft products (e.g. Google Docs) but bring up a Microsoft recovery message. You can find that info here: https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

I think it would be a great idea to detect these issues in excelJS and pass a warning. This way, we won't need to troubleshoot blindly and drive ourselves crazy (and usually an excel UI would apply these constraints to the inputs themselves).

Any thoughts on it? I am considering putting in a PR as library this seems to be the best hope at isomorphic excel solutions currently.

[mg1075]

Similar issue experienced when a column with date values also included null values. After replacing null values with empty string (see https://stackoverflow.com/questions/40984737/using-alasql-can-you-replace-null-column-with-empty-string), warning no longer happened.

[satspl]

excel found unreadable content in .xlsx.Do you want to recover error the contents of this workbook? While using excel-js module also, a tab called recovered sheet is added to the file.

[PratixaPathak]

Hi,
I had the similar issue and i have used the font with bold.
After looking into it it seems that what causes the issue is the font part.

Font font = workbook.createFont();
font.setBold(true);
boldAndCenteredCellStyle.setFont(font);

Ok, I figured out what the bug is.

Let's say you create a workbook using POI. This has one font in it by default.
When we create another font on the workbook using workbook.createFont(); a second font is added in the Workbook.

Hence I wrote a static method that I use passing it my current workbook and a current cellstyle that will create a Font with bold for me that will be rendered correctly!
You need to set Family as well as FontHeightInPoints both with the current font style.
public static XSSFFont getNewXSSFFont(XSSFWorkbook workbook, XSSFCellStyle cellStyle)
{
XSSFFont currentFont = (XSSFFont)workbook.GetFontAt(cellStyle.FontIndex);
XSSFFont newFont = (XSSFFont)workbook.CreateFont();
newFont.SetFamily(FontFamily.ValueOf(currentFont.Family));
newFont.IsBold = true;
newFont.FontHeightInPoints = currentFont.FontHeightInPoints;
return newFont;
}

[Siemienik]

#732 #35 #745

[chenop]

In my case the tab name consists single quote: "my tab's".
I just omitted them:
title = title.replace(/'/g, "");

[Alebat]

In my case it seems that long formulas are the issue. I can't figure out how to solve the problem, the xml in the xlsx that is produced looks good.

EDIT> I just figured out: internationalization issue. The functions parameters in formulas have to be separated by "," not by ";" as I was doing.

As per international settings in Italy, decimals are separated by "," not by ".", hence every "," becomes a ";". The library however does not adapt to internationalization settings, correctly.

[tamora-dawn]

I was creating an Excel file from SSRS. The issue turned out to be "invalid-to-Excel" zero values passed by SSRS.

For example, the Expression =Fields!Hours.Value * Fields!rate.Value was returning an "invalid-to-Excel" zero value when the value of the rate field was zero. I substituted the Expression =IIF(CDec(Fields!rate.Value) = CDec(0), CDec(0), Fields!Hours.Value * Fields!rate.Value) which fixed the problem with Excel.

[teja123r]

In my case, the issue was with dropdowns in columns. Apparently, the total length of dropdown list cannot be more than 255. Once I fix that, the issue is resolved.

[rnrneverdies]

In my case, the issue was with data tables. It seems the table name must start with a letter and does not contains any dash ("-") in the name... for instance, using "2020-10-02" as table name is not allowed. Using "d20201002" as table name works fine! hope that helps!

ws.addTable({
  name: "d" + sheetname.replace(/-/gi, ""),
  ...
});

[ttaha8]

In my case, I just read a file and print it again. on reading it, all the cells values are empty objects.
funny thing, if I open the original file > save it (even without making any changes) > close it and then read it with Exceljs, it works fine,

[ZaWertun]

Just bumped into this bug while trying to edit template file produced by the LibreOffice Calc.
I've seen this error in the Excel:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error039440_01.xml</logFileName>
<summary>Errors were detected in file 'C:\Users\...\Documents\test.xlsx'</summary>
-<repairedRecords summary="Following is a list of repairs:">
<repairedRecord>Repaired Records: Worksheet properties from /xl/workbook.xml part (Workbook)</repairedRecord>
</repairedRecords>
</recoveryLog>

Error is gone after opening XLSX template in the MS Excel and saving it again.

[rsgilbert]

My experience has been with adding tables to a worksheet.

I noticed that the popup comes if the table name has a digit.

It also shows up if there is already another table in the workbook (not only worksheet) that has the same name.

Again, it shows up if you assign the table a certain name eg 'c' or 'p'.

For example, this will give an error:

// bad, excel gives warning when you open the file
worksheet.addTable({
                name: 'table5', // bad idea to include a digit
                ref: 'A6',
                headerRow: true,
                style: {
                    theme: 'TableStyleLight11',
                    showRowStripes: true,
                },
                columns: reportColumns(),
                rows: await reportRows(invoices)
            })
```
What you should do is to make sure that the table name consists only of letters and that it is unique in the entire workbook.

```javascript
// good. 
const tablenames = Array.from('abcdefghijklmnopqrstuvwxyz').map(l => 'a' + l)
 
worksheet.addTable({
                name: tablesnames[0], // all letters and unique in workbook
                ref: 'A6',
                headerRow: true,
                style: {
                    theme: 'TableStyleLight11',
                    showRowStripes: true,
                },
                columns: reportColumns(),
                rows: await reportRows(invoices)
            })
```

[skareer95]

hello everyone!

I am using tables in excel worksheet. I have created table columns by using dates and all dates are unique string. but when the excel file creates it excel appends some random number at the end and gives error warning that some content has been repaired while opening.
I am attaching image for your reference. In the screenshot, After tue/28, it should be wed/1, thu/2 and so on, but it is appending some random number at the end like 2, 4. While consoling the same columns data, it shows correct data without random number. WHY THIS THING IS THERE?
For other date ranges, it works fine. Only for Jan to march, I am facing issue.

Sample code:
datearray = await this.getDatesInRange(new Date("1/31/2023"), new Date("3/8/2023"))

sheetCols = [];
data = [...new Array(this.datearray.length + 1).fill("1")];

async getDatesInRange(startDate, endDate) {
const date = new Date(startDate.getTime());
const dates = [];
while (date <= endDate) {
let dt = new Date(date);
let day = this.daysInWeek[dt.getDay()];
let month = dt.getMonth() + 1;
let dte = dt.getDate();
let year = dt.getFullYear();
dates.push({
day,
'date': ${month}/${dte},
'month': this.monthArrFullName[month - 1],
'year': year,
'dateOnly': dte,
'fullDate': ${month}/${dte}/${year},
});
date.setDate(date.getDate() + 1);
}
return dates;
}

for (var j = 0; j < datearray.length; j++) {
sheetCols.push({ name: datearray[j].day + '/' + datearray[j].dateOnly })
}
sheetCols.push({ name: 'Total' })

worksheet.addTable({
name: tableName,
ref: 'A' + (++lastRowTable),
headerRow: true,
style: {
showFirstColumn: true,
// showRowStripes: true
},
columns: sheetCols,
rows: data, // it includes the data of the length of sheetCols
});

[rsgilbert]

Hi @skareer95 , can you first try running it without the "/" symbol in the column header.

Also, can you provide a reproducible code sample that I can run on my side.

The error about excel doing repairs, I've been mostly getting it when providing a name for the table. Here:

worksheet.addTable({
name: tableName, ...

[skareer95]

Hi @rsgilbert

The tableName is not the issue. I have checked it properly. The issue is only coming when I pick the dates for Jan to March. Otherwise, it works fine even for the five months data except the first three months of the year. This issue is strange.
For the sample code, I have edited the previous comment's code. Just use it in the typescript file to regenerate it at your end. I am using 4.3.0 of exceljs npm.
Also, using this '/' in the header is not the issue.

[rsgilbert]

Hi @skareer95 , can you share a full code snippet I can copy/paste into a javascript file on my side and run to see the error.
I've seen the updated sample code but its not reproducible. For example the first line says:

datearray = await this.getDatesInRange(new Date("1/31/2023"), new Date("3/8/2023"))

datearray is not defined, there is a top-level await, you're using the this keyword and I dont what this is etc

[skareer95]

Hi @rsgilbert

i have shared almost the required code. I can't shared more due to some security reasons. It will work fine with little bit changes in JS like setting variable name and so on.
Can you please try on it?

[rsgilbert]

@skareer95 , the code you shared is not reproducible. See my updated comment above. Here it is again:
I've seen the updated sample code but its not reproducible. For example the first line says:

datearray = await this.getDatesInRange(new Date("1/31/2023"), new Date("3/8/2023"))

datearray is not defined, there is a top-level await, you're using the this keyword and I dont what this is etc

[skareer95]

you can remove this from this.getDatesInRange to call function in js.

If you run this in angular, it will work fine.

[rsgilbert]

@skareer95 , thats the point. I am not using angular. The sample code you provided is making many assumptions on the environment developed in so its not reproducible. I should be able to run it with the only dependency being exceljs. Thats why you should first trim it to only the essentials I need to see the excel error message. That means creating a file eg index.js and putting in the code I need to run it including any imports necessary.

[skareer95]

okay. I'll provide you the js code.

[skareer95]

Hi @rsgilbert
Here you go with JS code

let sheetCols = [];
let daysInWeek = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'];
function getDatesInRange(startDate, endDate) {
const date = new Date(startDate.getTime());
const dates = [];
while (date <= endDate) {
let dt = new Date(date);
let day = daysInWeek[dt.getDay()];
let month = dt.getMonth() + 1;
let dte = dt.getDate();
let year = dt.getFullYear();
dates.push({
day,
'date': month/dte,
'year': year,
'dateOnly': dte,
});
date.setDate(date.getDate() + 1);
}
return dates;
}

let datearray = getDatesInRange(new Date("1/31/2023"), new Date("3/8/2023"))
let data = [...new Array(datearray.length + 1).fill("1")];

for (var j = 0; j < datearray.length; j++) {
sheetCols.push({ name: datearray[j].day + '/' + datearray[j].dateOnly })
}
sheetCols.push({ name: 'Total' })

worksheet.addTable({
name: tableName,
ref: 'A' + (++lastRowTable),
headerRow: true,
style: {
showFirstColumn: true,
// showRowStripes: true
},
columns: sheetCols,
rows: data, // it includes the data of the length of sheetCols
});

[skareer95]

Hi @rsgilbert

is it working for you now?
Can you please confirm here?

[rsgilbert]

Hi @skareer95 , I've made a few changes and I've been able to reproduce the warning you're getting.
Excel gives the warning: Repaired Records: Table from /xl/tables/table1.xml part (Table)
Below is the full script I am running

const ExcelJS = require('exceljs')

let sheetCols = [];
let daysInWeek = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'];
function getDatesInRange(startDate, endDate) {
    const date = new Date(startDate.getTime());
    const dates = [];
    while (date <= endDate) {
        let dt = new Date(date);
        let day = daysInWeek[dt.getDay()];
        let month = dt.getMonth() + 1;
        let dte = dt.getDate();
        let year = dt.getFullYear();
        dates.push({
            day,
            'date': month / dte,
            'year': year,
            'dateOnly': dte,
        });
        date.setDate(date.getDate() + 1);
    }
    return dates;
}

let datearray = getDatesInRange(new Date("1/31/2023"), new Date("3/8/2023"))
let data = [[...new Array(datearray.length + 1).fill("1")]];

for (var j = 0; j < datearray.length; j++) {
    sheetCols.push({ name: datearray[j].day + '/' + datearray[j].dateOnly })
}
sheetCols.push({ name: 'Total' })

const workbook = new ExcelJS.Workbook()
const worksheet = workbook.addWorksheet('try', {});
   
worksheet.addTable({
    name: 'abc',
    ref: 'A' + (1+worksheet.rowCount),
    headerRow: true,
    style: {
        showFirstColumn: true,
        // showRowStripes: true
    },
    columns: sheetCols,
    rows: data, // it includes the data of the length of sheetCols
});
workbook.xlsx.writeFile('./try.xlsx')

I am looking into it.

[skareer95]

@rsgilbert

please share the header screenshot of excel for the last few columns. so i can look if the error is same for you.

[rsgilbert]

@skareer95 Hi! Good news.
The error is coming because your function getDatesInRange is giving incorrect days. In the month of Feb, it is including 29th and 30th Feb eventhough Feb ends on 28th.

Here use below function to calculate the datearray:

const { eachDayOfInterval } = require('date-fns');
let datearray = eachDayOfInterval({start: new Date("2023-01-30"), end: new Date("2023-03-30")}).map(d => ({
    dateOnly: d, day: d.getDate()
}))

[skareer95]

@rsgilbert

I can look here, it is not returning 29 and 30 feb for me on consoling the same array of objects

[rsgilbert]

@skareer95 , in my case it was failing whenever I included 29th feb in the date range.
Try running like below and see if you get the error:

let datearray = getDatesInRange(new Date("1/29/2023"), new Date("2/28/2023"))

Also try the advise I gave earlier and let me know if it works.

[skareer95]

@rsgilbert ,
I have no option of including 29th feb because in the UI I am using calendar to select dates. So user can't select date like 29th feb, as it is disabled for the current year, which is not leap year.

[skareer95]

@rsgilbert
const { eachDayOfInterval } = require('date-fns');
let datearray = eachDayOfInterval({start: new Date("2023-01-30"), end: new Date("2023-03-30")}).map(d => ({
dateOnly: d, day: d.getDate()
}))

It is not working as expected

[rsgilbert]

@skareer95 , I was saying that try a date range that stops on 28th Feb and see if it wont give the warning.

let datearray = getDatesInRange(new Date("1/29/2023"), new Date("2/28/2023"))

About your second comment, what do you mean its not working as expected?

[skareer95]

@rsgilbert

yes, I tried it
if you try from 1 jan to 28th feb, the issue is not there.
if you try from 24 feb to 15 march, in this case also the issue is not there.

so the change in month of february is there in the second case still without the issue.

[rsgilbert]

@skareer95 , you're right.

I dont get the error with 24 feb to 15 march

[rsgilbert]

@skareer95 I think the warning is coming because sometimes the table header names get repeated. Like Fri/24

[skareer95]

@rsgilbert
just check for another months except feb, it works fine.

[rsgilbert]

I've included the month in the column name. It's working fine now.

Here:

const { eachDayOfInterval } = require('date-fns');
const ExcelJS = require('exceljs')

let sheetCols = [];
let daysInWeek = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'];
function getDatesInRange(startDate, endDate) {
    const date = new Date(startDate.getTime());
    const dates = [];
    while (date <= endDate) {
        let dt = new Date(date);
        let day = daysInWeek[dt.getDay()];
        let month = dt.getMonth() + 1;
        let dte = dt.getDate();
        let year = dt.getFullYear();
        dates.push({
            day,
            month,
            'date': month / dte,
            'year': year,
            'dateOnly': dte,
        });
        date.setDate(date.getDate() + 1);
    }
    return dates;
}
let datearray = getDatesInRange(new Date("1/24/2023"), new Date("8/15/2023"))
// let datearray = eachDayOfInterval({start: new Date("2023-01-30"), end: new Date("2023-03-30")}).map(d => ({
//     dateOnly: d.getDate(), day: daysInWeek[d.getDay()]
// }))
let data = [[...new Array(datearray.length + 1).fill("1")]];

console.log({ datearray})
for (var j = 0; j < datearray.length; j++) {
    sheetCols.push({ name: [datearray[j].day, datearray[j].dateOnly, datearray[j].month].join('/') })
}
sheetCols.push({ name: 'Total' })
console.log(sheetCols)

const workbook = new ExcelJS.Workbook()
const worksheet = workbook.addWorksheet('try', {});
   
worksheet.addTable({
    name: 'abc',
    ref: 'A1' ,
    // headerRow: true,
    // style: {
    //     showFirstColumn: true,
    //     // showRowStripes: true
    // },
    columns: sheetCols,
    rows: data, // it includes the data of the length of sheetCols
});
workbook.xlsx.writeFile('./try.xlsx')

[rsgilbert]

@skareer95 maybe it was working fine for the other months because for those months there was no collision of day-of-week and day

[skareer95]

@rsgilbert

I got your point. The repeated name of the month is causing issue.
You are right. Appending some other string in the name of header will work fine.

Thanks for your continuous efforts.
You really helped a lot in this issue and I was not able to think so much about this issue.

Thanks!

[nelsondias3]

I had the same problem reported by anmolnar and the solution was simple for me.
I added blank lines at the end of the worksheet.

while (linha < 50)
{
for (int k = 0; k < dt.Columns.Count; k++)
{
WS.Cells[linha, k] = new Cell(String.Empty);
}
linha++;
}