Wrong number parsing #909

New Issue

2017-11-30T11:19:16Z

ToujouAya commented

2017-11-30 11:19:16 +00:00

(Migrated from github.com)

I got wrong number parsing result when import from excel.
My result

SSF format

My handle code

`handleFile(e) {
var files = e.target.files, f = files[0];
var reader = new FileReader();
var rABS = true
var _this = this;
reader.onload = function(e) {
var data = e.target.result;
if(!rABS) data = new Uint8Array(data);
var workbook = XLSX.read(data, {type: rABS ? 'binary' : 'array'});

		  /* DO SOMETHING WITH workbook HERE */
		  _this.to_json_first_sheet(workbook)
		  console.log(workbook)
		};
		if(rABS) reader.readAsBinaryString(f); else reader.readAsArrayBuffer(f);
	}

to_json_first_sheet: function(workbook){
var result = {};
var _this = this;
var sheetName = workbook.SheetNames[0];
var roa = _this.X.utils.sheet_to_json(workbook.Sheets[sheetName],{});
console.log(roa)
if(roa.length) result = roa;
return result;
}
`

My excel file ( github doesn't accept xls file so I updated it into google driver)
https://drive.google.com/file/d/1l1TLlycLpYjrhWzpO5fUipp-UCO49rzU/view

I got wrong number parsing result when import from excel. My result ![image](https://user-images.githubusercontent.com/21350606/33428191-799e5416-d5fa-11e7-869f-2eb393f62bb6.png) SSF format ![image](https://user-images.githubusercontent.com/21350606/33428956-827a6b76-d5fd-11e7-8e37-5a15e593fd05.png) My handle code `handleFile(e) { var files = e.target.files, f = files[0]; var reader = new FileReader(); var rABS = true var _this = this; reader.onload = function(e) { var data = e.target.result; if(!rABS) data = new Uint8Array(data); var workbook = XLSX.read(data, {type: rABS ? 'binary' : 'array'}); /* DO SOMETHING WITH workbook HERE */ _this.to_json_first_sheet(workbook) console.log(workbook) }; if(rABS) reader.readAsBinaryString(f); else reader.readAsArrayBuffer(f); } to_json_first_sheet: function(workbook){ var result = {}; var _this = this; var sheetName = workbook.SheetNames[0]; var roa = _this.X.utils.sheet_to_json(workbook.Sheets[sheetName],{}); console.log(roa) if(roa.length) result = roa; return result; } ` My excel file ( github doesn't accept xls file so I updated it into google driver) https://drive.google.com/file/d/1l1TLlycLpYjrhWzpO5fUipp-UCO49rzU/view

SheetJSDev commented

2017-11-30 16:44:21 +00:00

(Migrated from github.com)

Thanks for sharing! This is a BIFF4 file using codepage 932 with FORMAT records that specify 0 index. Need to dig further, but if the index is always zero then replicating the BIFF2/3 logic for BIFF4 would solve the problem:

--- a/bits/76_xls.js
+++ b/bits/76_xls.js
@@ -486,7 +486,12 @@ function parse_workbook(blob, options/*:ParseOpts*/)/*:Workbook*/ {
                                        sst = val;
                                } break;
                                case 'Format': { /* val = [id, fmt] */
-                                       SSF.load(val[1], val[0]);
+                                       if(opts.biff == 4) {
+                                               BIFF2FmtTable[BIFF2Fmt++] = val[1];
+                                               for(var b2idx = 0; b2idx < BIFF2Fmt + 163; ++b2idx) if(SSF._table[b2idx] == val[1]) break;
+                                               if(b2idx >= 163) SSF.load(val[1], BIFF2Fmt + 163);
+                                       }
+                                       else SSF.load(val[1], val[0]);
                                } break;
                                case 'BIFF2FORMAT': {
                                        BIFF2FmtTable[BIFF2Fmt++] = val;

Thanks for sharing! This is a BIFF4 file using codepage 932 with FORMAT records that specify 0 index. Need to dig further, but if the index is always zero then replicating the BIFF2/3 logic for BIFF4 would solve the problem: ```diff --- a/bits/76_xls.js +++ b/bits/76_xls.js @@ -486,7 +486,12 @@ function parse_workbook(blob, options/*:ParseOpts*/)/*:Workbook*/ { sst = val; } break; case 'Format': { /* val = [id, fmt] */ - SSF.load(val[1], val[0]); + if(opts.biff == 4) { + BIFF2FmtTable[BIFF2Fmt++] = val[1]; + for(var b2idx = 0; b2idx < BIFF2Fmt + 163; ++b2idx) if(SSF._table[b2idx] == val[1]) break; + if(b2idx >= 163) SSF.load(val[1], BIFF2Fmt + 163); + } + else SSF.load(val[1], val[0]); } break; case 'BIFF2FORMAT': { BIFF2FmtTable[BIFF2Fmt++] = val; ```

ToujouAya commented

2017-12-01 02:23:28 +00:00

(Migrated from github.com)

mm. well thank you so much for helping me but I still confuse

This is a BIFF4 file using codepage 932 with FORMAT records that specify 0 index

first how could I get this information above, workbook file or work sheet properties ?
second I still don't know how to implement to read data.

mm. well thank you so much for helping me but I still confuse > This is a **BIFF4** file using **codepage 932** with **FORMAT** records that specify **0 index** first how could I get this information above, workbook file or work sheet properties ? second I still don't know how to implement to read data.

ToujouAya commented

2017-12-01 06:20:42 +00:00

(Migrated from github.com)

@SheetJSDev
Could you explain this. Thank you

This is a BIFF4 file using codepage 932 with FORMAT records that specify 0 index
first how could I get this information above, workbook file or work sheet properties ?
second I still don't know how to implement to read data.

@SheetJSDev Could you explain this. Thank you > This is a BIFF4 file using codepage 932 with FORMAT records that specify 0 index > first how could I get this information above, workbook file or work sheet properties ? > second I still don't know how to implement to read data.

SheetJSDev commented

2017-12-01 06:30:01 +00:00

(Migrated from github.com)

@ToujouAya we are releasing 0.11.11 right now with the fix. Give it some time to show up on NPM and CDNs. Your code should work as-is.

👍 1

ToujouAya commented

2017-12-01 06:45:59 +00:00

(Migrated from github.com)

@SheetJSDev
Oh thank you so much. I appreciate

@SheetJSDev Oh thank you so much. I appreciate

Sign in to join this conversation.