search

exceljs / exceljs

Excel Workbook Manager
MIT License
13.62k stars 1.74k forks source link

TypeError: Cannot read property 'sheets' of undefined #962

Open Tri-Vi opened 5 years ago

Tri-Vi commented 5 years ago

I got this error when I tested the parsing process from an excel file. Hope someone can help me with this

TypeError: Cannot read property 'sheets' of undefined
    at _callee10$ (/Users/tvi/Documents/BCM projects/cvd-genomics/node_modules/exceljs/dist/es5/xlsx/xlsx.js:553:43)
    at tryCatch (/Users/tvi/Documents/BCM projects/cvd-genomics/node_modules/regenerator-runtime/runtime.js:65:40)
    at Generator.invoke [as _invoke] (/Users/tvi/Documents/BCM projects/cvd-genomics/node_modules/regenerator-runtime/runtime.js:303:22)
    at Generator.prototype.(anonymous function) [as next] (/Users/tvi/Documents/BCM projects/cvd-genomics/node_modules/regenerator-runtime/runtime.js:117:21)
    at asyncGeneratorStep (/Users/tvi/Documents/BCM projects/cvd-genomics/node_modules/exceljs/dist/es5/xlsx/xlsx.js:3:103)
    at _next (/Users/tvi/Documents/BCM projects/cvd-genomics/node_modules/exceljs/dist/es5/xlsx/xlsx.js:5:194)
    at <anonymous>

Excel File: TEST.xlsx

exceljs version: 2.0.1

amit-coditas commented 5 years ago

were you able to find the root cause?

dirasangtani commented 5 years ago

@Tri-Vi Hi, I am getting similar issue with my excel file, attached. Bulk.xlsx

However, the code works well while parsing other newly created excel file. There is some problem with properties of TEST.xlsx and Bulk.xlsx. Please share in case you find the root cause.

Read Excel Code:

async writeData(filepath, values, prof_name){
        workbook.xlsx.readFile(filepath)
        .catch(function(error){
            console.log(error)
        })
        .then(function(){
            var worksheet = workbook.getWorksheet(1);
                var row = worksheet.getRow(worksheet.actualRowCount+1);
                var j=values.length;
                for(var i=1;  i<=j; i++){
                    row.getCell(i).value = values[i-1];
                }
                row.commit();
                workbook.xlsx.writeFile(downloadsFolder()+'/' +prof_name+'.xlsx'); 
        })

    }
amit-coditas commented 5 years ago

Not sure of the exact root cause, but opening the file in Excel and saving it again works.

dirasangtani commented 5 years ago

@amit-coditas @Tri-Vi I have had similar issue with apache poi(java), it would not be able to write into this same excel when downloaded. If it is opened and saved manually, it worked. How do we automate in such case? Any thoughts?

Tri-Vi commented 5 years ago

@dirasangtani , @amit-coditas : I haven't found the root cause yet, but there is something wrong with the worksheet. It seems like the worksheet can't be created for those excel files. I switch over to this package https://www.npmjs.com/package/xlsx and the problem is solved.

dirasangtani commented 4 years ago

I am referring the docs for xlsx module. https://docs.sheetjs.com/#sheetjs-js-xlsx However, I do not see how do I create cells and write.

I am getting error like this, TypeError: Cannot read property 'v' of undefined when I use - bulkSheet['A4'].v = 'My Value'; Any help?

Tri-Vi commented 4 years ago

I am referring the docs for xlsx module. https://docs.sheetjs.com/#sheetjs-js-xlsx However, I do not see how do I create cells and write.

I am getting error like this, TypeError: Cannot read property 'v' of undefined when I use - bulkSheet['A4'].v = 'My Value'; Any help?

Can you include your code here ?

dirasangtani commented 4 years ago

I am referring the docs for xlsx module. https://docs.sheetjs.com/#sheetjs-js-xlsx However, I do not see how do I create cells and write. I am getting error like this, TypeError: Cannot read property 'v' of undefined when I use - bulkSheet['A4'].v = 'My Value'; Any help?

Can you include your code here ?

Hi,

Below is the code, 

const xlsx = require('xlsx');

function writeDataBulkEmployee(filepath, values){
    //read from xlsx file
    let workbook = xlsx.readFile(filepath);
    // get first sheet
    var first_sheet = workbook.SheetNames[0];
    console.log(first_sheet)
    var bulkSheet = workbook.Sheets[first_sheet]
    //This below loop writes just one row, expected 8
    for(let i=1; i<=8; i++){
        var str = 'A'+i;
        workbook.Sheets[first_sheet][str] = {v:'232388270', t: 's'};
    }
    //This below did not work, array of arrays cannot be written
    xlsx.utils.sheet_add_aoa(bulkSheet, [[4,5,6,7,8,9,0]], {origin: "-1"});

    //Below is able to write only on first row
    workbook.Sheets[first_sheet]['A1'] = {v:'232388270', t: 's'};
    workbook.Sheets[first_sheet]['A2'] = {v:'232388270', t: 's'};
    workbook.Sheets[first_sheet]['A3'] = {v:'232388270', t: 's'};
    workbook.Sheets[first_sheet]['A4'] = {v:'232388270', t: 's'};
    workbook.Sheets[first_sheet]['A5'] = {v:'232388270', t: 's'};
    workbook.Sheets[first_sheet]['A6'] = {v:'232388270', t: 's'};

    xlsx.writeFile(workbook, 'C:\\Users\\dira.sangtani\\Downloads\\SampleOutput.xls')
}
writeDataBulkEmployee('C:\\Users\\dira.sangtani\\Downloads\\Sample.xlsx')
dirasangtani commented 4 years ago

@Tri-Vi did you get chance to look into this?

Tri-Vi commented 4 years ago

@Tri-Vi did you get chance to look into this?

Try this :

const XLSX = require('xlsx');
var book1 = __dirname + '/files/Book1.xlsx'; 

var data = [
  {"name":"A"},
  {"name":"B"},
  {"name":"C"},
]

function testWriteFile(filePath, data){

  /* make the worksheet */
  var ws = XLSX.utils.json_to_sheet(data);

  /* add to workbook */
  var wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "Test");

  console.log(wb);

  /* generate an XLSX file */
  XLSX.writeFile(wb, filePath);

}

testWriteFile(book1, data);
Siemienik commented 4 years ago

that situation is really often when some worksheets were been removed.

for example when workbook has sheets:

index name id
0 Sheet 1 1
1 Sheet 2 2
2 Sheet 3 4
3 SomeSheet 3

then you remove Sheet 1:

index name id
0 Sheet 2 2
1 Sheet 3 3
2 SomeSheet 4

then you remove Sheet 3:

Index name id
0 Sheet 2 2
1 SomeSheet 4

In this case, You can:

const first = wb.getWorksheet() // to get first worksheet (Sheet 2)
const withId2 = wb.getWorksheet(2) //to get worksheet with id === 2 (Sheet 2)
const byName = wb.getWorksheet("Sheet 2") //to get worksheet with specific name (Sheet 2)
const byIndex= wb.worksheets[0] //to get directly from worksheet array

and also you can't do that:

const byIndex = wb.getWorksheet(0) // becouse it try to get worksheet by ID not by Index in worksheet array

Resuming

so, if you want to iterate through the worksheets, you should use wb.worksheets array or wb.eachSheet function

Sources

I encourage you to study sources- they are like a tutorial :)

https://github.com/exceljs/exceljs/blob/bbc4e43d60fa7e57b5296b1ea1225a0e3ec27eed/lib/doc/workbook.js#L102 https://github.com/exceljs/exceljs/blob/bbc4e43d60fa7e57b5296b1ea1225a0e3ec27eed/lib/doc/workbook.js#L115 https://github.com/exceljs/exceljs/blob/bbc4e43d60fa7e57b5296b1ea1225a0e3ec27eed/lib/doc/workbook.js#L123

phsantiago commented 4 years ago

@Siemienik thanks for your reply, but the error happens before we can get the worksheet. It's more related to workbook than worksheet.

    //        place where the error happens
    //                       v
    return this.workbook.xlsx.read(sheet).then(() => {
      if (sheetNameOrIndex) this.worksheet = this.workbook.getWorksheet(sheetNameOrIndex);

Lib code that throws the error:

// exceljs/lib/xlsx/xlsx.js:269
              case 'xl/workbook.xml': {
                const workbook = await this.parseWorkbook(entry);
                model.sheets = workbook.sheets;
phsantiago commented 4 years ago

working xl/workbook.xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
          xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
          xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x15 xr xr6 xr10 xr2"
          xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"
          xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision"
          xmlns:xr6="http://schemas.microsoft.com/office/spreadsheetml/2016/revision6"
          xmlns:xr10="http://schemas.microsoft.com/office/spreadsheetml/2016/revision10"
          xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2">
    <fileVersion appName="xl" lastEdited="7" lowestEdited="7" rupBuild="22730"/>
    <workbookPr/>
    <mc:AlternateContent xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006">
        <mc:Choice Requires="x15">
            <x15ac:absPath url="C:\Users\gabri\Desktop\"
                           xmlns:x15ac="http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac"/>
        </mc:Choice>
    </mc:AlternateContent>
    <xr:revisionPtr revIDLastSave="0" documentId="13_ncr:1_{E472DD84-332E-4E17-AA84-0D21183461C7}"
                    xr6:coauthVersionLast="45" xr6:coauthVersionMax="45"
                    xr10:uidLastSave="{00000000-0000-0000-0000-000000000000}"/>
    <bookViews>
        <workbookView xWindow="-120" yWindow="-120" windowWidth="29040" windowHeight="15840"
                      xr2:uid="{00000000-000D-0000-FFFF-FFFF00000000}"/>
    </bookViews>
    <sheets>
        <sheet name="Sheet1" sheetId="1" r:id="rId1"/>
    </sheets>
    <calcPr calcId="0"/>
</workbook>

broken xl/workbook.xml:

<?xml version="1.0" encoding="utf-8"?>
<x:workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
            xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <x:fileVersion appName="SpreadsheetLight"/>
    <x:bookViews>
        <x:workbookView/>
    </x:bookViews>
    <x:sheets>
        <x:sheet name="Sheet1" sheetId="1" r:id="R72a4c9e3da964db8"/>
    </x:sheets>
</x:workbook>
Siemienik commented 4 years ago

how this file is generated?

rcrogers commented 3 years ago

I'm also getting this error on (new Excel.Workbook()).xlsx.readFile(fileName).

how this file is generated?

Users like me typically won't know how the file is generated (because it came from an external source), so that doesn't seem like a useful line of inquiry. I can corroborate that this situation occurs in the wild. It would be cool if this lib was capable of dealing with it.

yocontra commented 3 years ago

Another reproduction case: file.xlsx

Seeing this on latest (4.2.0).

With some basic debugging, it seems like the order of files in the zip is the issue. The sheet parser is being called before the workbook has been parsed, so this.model is still undefined. Opening the file in excel and resaving it makes it work, assuming that it reorders the files inside the xlsx zip.

Same file after it has been opened and resaved: file.xlsx

Privatecoder commented 3 years ago

here is an attempted fix for this which seemed to be sufficient in my case

EDIT: I deleted my fork/fix in favor of @contra's PR #1570 which fixes this and a lot more

yocontra commented 3 years ago

I've fixed this as a part of this PR: https://github.com/exceljs/exceljs/pull/1570

Privatecoder commented 3 years ago

@contra & @Siemienik

I now have a very similar issue with worksheet names, which default to "SheetN" if workbook.xml.rels is not parsed via _parseRels(entry) before

    const matchingRel = (this.workbookRels || []).find(
      rel => rel.Target === `worksheets/sheet${sheetNo}.xml`
    );
    const matchingSheet =
      matchingRel && (this.model.sheets || []).find(sheet => sheet.rId === matchingRel.Id);
    if (matchingSheet) {
      worksheetReader.id = matchingSheet.id;
      worksheetReader.name = matchingSheet.name;
      worksheetReader.state = matchingSheet.state;
    }

is called.

maybe mandatory files such as workbook.xml.rels and workbook.xml should be searched for and parsed before everything else to avoid race conditions like this

yocontra commented 3 years ago

@Privatecoder Does that issue exist on my fork or only in the regular version?

Privatecoder commented 3 years ago

@contra both, latest stable + your fork.

Title is "Sheet1" but should be "my fancy title".

I added a console.log(entry.path) to the for await (const entry of zip) statement to find that some files are just not showing up.

Privatecoder commented 3 years ago

@contra & @Siemienik

there seems to be an issue with unzipper using async iterators. any await (sample: 1000ms) results in any of the succeeding contents of the zip (here xlsx) to not be processed:

const fs = require('fs');
const unzipper = require('unzipper');

const test = async () => {
    const stream = fs.createReadStream('test.xlsx');
    const zip = stream.pipe(unzipper.Parse({forceStream: true}));

    for await (const entry of zip) {
        console.log(entry.path);
        await new Promise(resolve => setTimeout(resolve, 1000));
    }
}

test();

output:

docProps/core.xml

vs.

const fs = require('fs');
const unzipper = require('unzipper');

const test = () => {
    const stream = fs.createReadStream('test.xlsx');
    const zip = stream.pipe(unzipper.Parse());

    zip.on('entry', entry => {
        console.log(entry.path);
    })
}

test();

output:

docProps/core.xml
docProps/app.xml
xl/worksheets/sheet1.xml
xl/workbook.xml
xl/theme/theme1.xml
xl/styles.xml
[Content_Types].xml
_rels/.rels
xl/_rels/workbook.xml.rels

test.xlsx

yocontra commented 3 years ago 
resolutions": {
  "unzipper": "github:kinolaev/node-unzipper#patch-1"
}

Try this in your package.json and let me know if it helps

Privatecoder commented 3 years ago

it doesn't – you can check the sample along with the attached test.xlsx

=> https://github.com/ZJONSSON/node-unzipper/issues/234

yocontra commented 3 years ago

@Privatecoder What node version are you on?

Privatecoder commented 3 years ago

@contra v12.18.0/macOS

EDIT: upgrading node to v14.16.0 fixes the issue.

yocontra commented 3 years ago

@Privatecoder Thought that would be the case - I saw the same issue on 12 and 13, I make sure to just use 15 because streams had a few odd bugs in those versions. Even 14 had some weird issues.

zhukovsv commented 3 years ago

Hi Everyone,

I reproduced this issue: Failed: Cannot read property 'sheets' of undefined

Configuration settings:

  1. node - v: 14.16.0

  2. "exceljs": "^4.2.1"

  3. test File: testFile.xlsx

  4. Code:

    const ExcelJS = require('exceljs');
const pageComparisonXLSXFilePath = './testFile.xlsx';
const workbook = new ExcelJS.Workbook();
await workbook.xlsx.readFile(pageComparisonXLSXFilePath); 
const worksheet = workbook.getWorksheet(1);
const rowC = worksheet.rowCount;
for (const row of worksheet.getRows(1, rowC)) {
console.log(row.values);
}

    Error raises during next line execution: await workbook.xlsx.readFile(pageComparisonXLSXFilePath);

  5. Steps to reproduce:

    1. Download file -> exec code -> exception: Failed: Cannot read property 'sheets' of undefined. but
    2. If I open this file by MSExcel and then simply save this file and close it -> code executes correctly.

Could you help me?

Regards, Sergei.

vinurs commented 3 years ago

is there any work aroud for this?

resourge commented 2 years ago

I was having the same error "Failed: Cannot read property 'sheets' of undefined", looking at the code I realise the problem was that Exceljs is prepared to deal with tags that do not start with "<x:.....".

Working excel:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook
    xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
    xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x15 xr xr6 xr10 xr2"
    xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"
    xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision"
    xmlns:xr6="http://schemas.microsoft.com/office/spreadsheetml/2016/revision6"
    xmlns:xr10="http://schemas.microsoft.com/office/spreadsheetml/2016/revision10"
    xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2">
    .....
</workbook>

Not working excel:

<?xml version="1.0" encoding="utf-8"?>
<x:workbook
    xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
    xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <x:workbookPr codeName="ThisWorkbook" />
    <x:bookViews>
        <x:workbookView firstSheet="0" activeTab="0" />
    </x:bookViews>
    <x:sheets>
        <x:sheet name="Data" sheetId="2" r:id="rId2" />
    </x:sheets>
    <x:definedNames />
    <x:calcPr calcId="125725" />
</x:workbook>

The solution for my problem(it's probably not the best or correct solution but so far is working, so well...) was to replace all "<x:..." for "<..." and "</x:" for "</" before writing into the stream.

File: xlsx.js. Function: load Line: 301

  // use object mode to avoid buffer-string convention
  stream = new PassThrough({
      writableObjectMode: true,
      readableObjectMode: true
  });
  let content;
  // https://www.npmjs.com/package/process
  if (process.browser) {
      // running in browser, use TextDecoder if possible
      content = bufferToString(await entry.async('nodebuffer'));
  }
  else {
      // running in node.js
      content = await entry.async('string');
  }

content = content.replace(/<\w:/g, '<') // <--- Here
content = content.replace(/<\/\w:/g, '</') // <--- Here

  const chunkSize = 16 * 1024;
  for (let i = 0; i < content.length; i += chunkSize) {
      stream.write(content.substring(i, i + chunkSize));
  }

PS: xlsx.js is also striping the "x:" from the tags

Siemienik commented 2 years ago

@yocontra

is there any work aroud for this?

I have got limited time for volunteering in OSS. (This is a common problem for OSS projects I guess). If any issue is urgent please get in touch with me via mail consulting@siemienik.com .

About this issue, As you did https://github.com/exceljs/exceljs/pull/1570/ I'm going to review it again if my time passes.

artemrudenko commented 2 years ago

Hi @Siemienik any news on this?

ilovepixelart commented 1 year ago

Having similar issue with "exceljs": "4.3.0", this issue is very annoying looks like library can't initialize the sheets Cannot read properties of undefined (reading 'sheets')

reallomate commented 12 months ago

hi @Siemienik , any ETA on this?

oudi commented 3 months ago

It's 5 years since 2019, any update? face same issue as @resourge https://github.com/exceljs/exceljs/issues/962#issuecomment-957299936 mentioned.