getting empty value from .xls file #1220

New Issue

2018-08-14T23:32:16Z

AhmadZuhdi commented

2018-08-14 23:32:16 +00:00

(Migrated from github.com)

i trying to read a file i got from my friend
it was a .xls and protected file (when i open it with microsoft excel)

this is the code:

import * as fs from 'fs';
import * as xlsx from 'xlsx';

const wb = xlsx.readFile(`${__dirname}/09082018.xls`);

const data = xlsx.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {header:1});

console.log(data);

the result of data is just []

i trying to read a file i got from my friend it was a .xls and protected file (when i open it with microsoft excel) this is the code: ```js import * as fs from 'fs'; import * as xlsx from 'xlsx'; const wb = xlsx.readFile(`${__dirname}/09082018.xls`); const data = xlsx.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {header:1}); console.log(data); ``` the result of `data` is just `[]`

SheetJSDev commented

2018-08-15 00:50:57 +00:00

(Migrated from github.com)

Your friend is using a strange tool to generate files!

This file uses the "BIFF2" standard. However, the header record type indicates the file is BIFF8. Since certain fields like the Dimensions record are shaped differently, that leads to an invalid range and the worksheet is not properly parsed. Fortunately the override is a one-line fix (feel free to submit this as a PR):

--- a/bits/76_xls.js
+++ b/bits/76_xls.js
@@ -351,6 +351,7 @@ function parse_workbook(blob, options/*:ParseOpts*/)/*:Workbook*/ {
                                                /*::[*/0x0002/*::]*/:2,
                                                /*::[*/0x0007/*::]*/:2
                                        }[val.BIFFVer] || 8;
+                                       if(opts.biff == 8 && val.BIFFVer == 0 && val.dt == 16) opts.biff = 2;
                                        if(file_depth++) break;
                                        cell_valid = true;
                                        out = ((options.dense ? [] : {})/*:any*/);

Your friend is using a strange tool to generate files! This file uses the "BIFF2" standard. However, the header record type indicates the file is BIFF8. Since certain fields like the Dimensions record are shaped differently, that leads to an invalid range and the worksheet is not properly parsed. Fortunately the override is a one-line fix (feel free to submit this as a PR): ```diff --- a/bits/76_xls.js +++ b/bits/76_xls.js @@ -351,6 +351,7 @@ function parse_workbook(blob, options/*:ParseOpts*/)/*:Workbook*/ { /*::[*/0x0002/*::]*/:2, /*::[*/0x0007/*::]*/:2 }[val.BIFFVer] || 8; + if(opts.biff == 8 && val.BIFFVer == 0 && val.dt == 16) opts.biff = 2; if(file_depth++) break; cell_valid = true; out = ((options.dense ? [] : {})/*:any*/); ```

AhmadZuhdi commented

2018-08-15 01:36:08 +00:00

(Migrated from github.com)

Ah I see, if I remembered, it was generated from an attendance software, I'll take a look the solution

Sign in to join this conversation.