biff option #2907

New Issue

zimbora · 2023-04-04T17:09:52Z

zimbora commented

2023-04-04 17:09:52 +00:00

I have an old xls file, in which I am only capable to open it changing biff option.
Can you enable it on opts arg?

Please check the attachments to a detailed description

I have an old xls file, in which I am only capable to open it changing biff option. Can you enable it on opts arg? Please check the attachments to a detailed description

Screenshot 2023-04-04 at 18.07.47.png

65 KiB

Screenshot 2023-04-04 at 18.08.54.png

14 KiB

sheetjs commented

2023-04-04 17:29:35 +00:00

Can you share a file that is causing issues? We can add an override, but there may be some file-based hint.

The actual BIFF version is determined by looking at the BOF record: BIFF2/3/4 use distinct record types and BIFF5+ store the BIFF version in the first two bytes of the BOF record. Code https://git.sheetjs.com/sheetjs/sheetjs/src/branch/master/bits/76_xls.js#L330-L342

	if(opts.biff === 8) opts.biff = {
		/*::[*/0x0009/*::]*/:2,
		/*::[*/0x0209/*::]*/:3,
		/*::[*/0x0409/*::]*/:4
	}[RecordType] || {
		/*::[*/0x0200/*::]*/:2,
		/*::[*/0x0300/*::]*/:3,
		/*::[*/0x0400/*::]*/:4,
		/*::[*/0x0500/*::]*/:5,
		/*::[*/0x0600/*::]*/:8,
		/*::[*/0x0002/*::]*/:2,
		/*::[*/0x0007/*::]*/:2
	}[val.BIFFVer] || 8;

Note: If you are trying to write a file, you can pass the file type biff2. This is covered in our docs https://docs.sheetjs.com/docs/api/write-options#supported-output-formats

Can you share a file that is causing issues? We can add an override, but there may be some file-based hint. The actual BIFF version is determined by looking at the BOF record: BIFF2/3/4 use distinct record types and BIFF5+ store the BIFF version in the first two bytes of the BOF record. Code https://git.sheetjs.com/sheetjs/sheetjs/src/branch/master/bits/76_xls.js#L330-L342 ```js if(opts.biff === 8) opts.biff = { /*::[*/0x0009/*::]*/:2, /*::[*/0x0209/*::]*/:3, /*::[*/0x0409/*::]*/:4 }[RecordType] || { /*::[*/0x0200/*::]*/:2, /*::[*/0x0300/*::]*/:3, /*::[*/0x0400/*::]*/:4, /*::[*/0x0500/*::]*/:5, /*::[*/0x0600/*::]*/:8, /*::[*/0x0002/*::]*/:2, /*::[*/0x0007/*::]*/:2 }[val.BIFFVer] || 8; ``` Note: If you are trying to *write* a file, you can pass the file type `biff2`. This is covered in our docs https://docs.sheetjs.com/docs/api/write-options#supported-output-formats

zimbora commented

2023-04-05 09:17:51 +00:00

I think the file is somehow corrupted because I can't open it with microsoft excel.
However, I am receiving those files and I need to open it.
Please give a check to the attached file.

Thank you for your support.

I think the file is somehow corrupted because I can't open it with microsoft excel. However, I am receiving those files and I need to open it. Please give a check to the attached file. Thank you for your support.

gps_file.xlsm

26 KiB

sheetjs commented

2023-04-05 17:10:23 +00:00

The file itself is a bit of a mess. After renaming to gps_file.xls, Excel tries to repair the file and shows a warning.

Inspecting the file, the ExternSheet record is incorrect. The file is marked as BIFF8 but is using the legacy format. The most straightforward patch:

--- a/bits/39_xlsbiff.js
+++ b/bits/39_xlsbiff.js
@@ -699,6 +699,8 @@ function parse_Lbl(blob, length, opts) {
 /* [MS-XLS] 2.4.106 TODO: verify filename encoding */
 function parse_ExternSheet(blob, length, opts) {
        if(opts.biff < 8) return parse_BIFF5ExternSheet(blob, length, opts);
+       /* see issue 2907 */
+       if(!(opts.biff > 8) && (length == blob[blob.l] + (blob[blob.l+1] == 0x03 ? 1 : 0) + 1)) return parse_BIFF5ExternSheet(blob, length, opts);
        var o = [], target = blob.l + length, len = blob.read_shift(opts.biff > 8 ? 4 : 2);
        while(len-- !== 0) o.push(parse_XTI(blob, opts.biff > 8 ? 12 : 6, opts));
                // [iSupBook, itabFirst, itabLast];

If you have the ability to generate these files, can you also share a file with a formula?

The file itself is a bit of a mess. After renaming to `gps_file.xls`, Excel tries to repair the file and shows a warning. Inspecting the file, the ExternSheet record is incorrect. The file is marked as BIFF8 but is using the legacy format. The most straightforward patch: ```diff --- a/bits/39_xlsbiff.js +++ b/bits/39_xlsbiff.js @@ -699,6 +699,8 @@ function parse_Lbl(blob, length, opts) { /* [MS-XLS] 2.4.106 TODO: verify filename encoding */ function parse_ExternSheet(blob, length, opts) { if(opts.biff < 8) return parse_BIFF5ExternSheet(blob, length, opts); + /* see issue 2907 */ + if(!(opts.biff > 8) && (length == blob[blob.l] + (blob[blob.l+1] == 0x03 ? 1 : 0) + 1)) return parse_BIFF5ExternSheet(blob, length, opts); var o = [], target = blob.l + length, len = blob.read_shift(opts.biff > 8 ? 4 : 2); while(len-- !== 0) o.push(parse_XTI(blob, opts.biff > 8 ? 12 : 6, opts)); // [iSupBook, itabFirst, itabLast]; ``` If you have the ability to generate these files, can you also share a file with a formula?

zimbora commented

2023-04-05 17:26:35 +00:00

It is a client that is sending those files.. I don't have a way to generate it.

Thanks for your support ;)

It is a client that is sending those files.. I don't have a way to generate it. Thanks for your support ;)

zimbora closed this issue

2023-04-05 17:26:35 +00:00

sheetjs referenced this issue from a commit

2023-04-05 19:35:17 +00:00

XLS ExternSheet record (fixes #2907)

Sign in to join this conversation.