XLSX.readFile returns object with no SheetNames and no data. + patch #191

New Issue

2015-03-06T20:31:09Z

chk- commented

2015-03-06 20:31:09 +00:00

(Migrated from github.com)

Hi!
I tried to read a xlsx file and no data was returned, I'm working with nodejs. I debugged the xslx.js and found some solution for me, I'd like to share;-)

1st Problem was function parse_zip(zip, opts)
Line 5007:
-if(!props.Worksheets) { //props.Worksheets was 1 and props.SheetNames.length was 0
+if(!props.Worksheets || props.SheetNames.length === 0) { //works for me
Without SheetNames, safe_parse_ws(zip, path, relsPath, props.SheetNames[i], sheetRels, sheets, opts) would fail.

2nd Problem was the mergeCell tag in my file. It has a closing tag .
I changed line 2835:
-var mergecregex = /<mergeCell ref="[A-Z0-9:]+"\s_/>/g;
+var mergecregex = /<mergeCell ref="[A-Z0-9:]+"\s_(/|)>/g;
and it worked for me.

With my 2 patches it can read the data correct.
Is this helpfull for you?

Hi! I tried to read a xlsx file and no data was returned, I'm working with nodejs. I debugged the xslx.js and found some solution for me, I'd like to share;-) 1st Problem was function parse_zip(zip, opts) Line 5007: -if(!props.Worksheets) { //props.Worksheets was 1 and props.SheetNames.length was 0 +if(!props.Worksheets || props.SheetNames.length === 0) { //works for me Without SheetNames, safe_parse_ws(zip, path, relsPath, props.SheetNames[i], sheetRels, sheets, opts) would fail. 2nd Problem was the mergeCell tag in my file. It has a closing tag </mergeCell>. I changed line 2835: -var mergecregex = /<mergeCell ref="[A-Z0-9:]+"\s_\/>/g; +var mergecregex = /<mergeCell ref="[A-Z0-9:]+"\s_(\/|)>/g; and it worked for me. With my 2 patches it can read the data correct. Is this helpfull for you?

SheetJSDev commented

2015-03-06 21:50:05 +00:00

(Migrated from github.com)

@chk- do you have a sample file to add to the test suite?

chk- commented

2015-03-11 13:49:08 +00:00

(Migrated from github.com)

I organized a testfile to provide to you, but how do I add it to the test suit?

SheetJSDev commented

2015-03-11 17:29:36 +00:00

(Migrated from github.com)

@chk- easiest way is to just email it to ---------

chk- commented

2015-03-11 17:44:37 +00:00

(Migrated from github.com)

So I did.

imsaquib commented

2015-04-01 14:02:58 +00:00

(Migrated from github.com)

I am facing the same issue with xlsx workbook, After making the changes suggested by @chk- I am able to get the worksheet name but still no data is parsed. Do we have fix for the issue available.

chk- commented

2015-04-01 14:13:11 +00:00

(Migrated from github.com)

@imsaquib
Please notice my change on the regular expression for the var mergecregex. In the original code it needs a closing tag. I changed it to make the closing tag optional. Maybe there are other regex's explizit wanting a closing tag. If so, please try again after making the closing tag optional.
@SheetJSDev I suggest changing all regex's to make the closing tag optional.

@imsaquib Please notice my change on the regular expression for the var mergecregex. In the original code it needs a closing tag. I changed it to make the closing tag optional. Maybe there are other regex's explizit wanting a closing tag. If so, please try again after making the closing tag optional. @SheetJSDev I suggest changing all regex's to make the closing tag optional.

imsaquib commented

2015-04-01 15:25:03 +00:00

(Migrated from github.com)

@chk- Thanks for the reply, Had to make closing tag optional for hlinkregex to parse the file.
But by doing so lost the formatted values ""C21":{"t":"n","v":491341.285099999}" & Hyperlink information.

@chk- Thanks for the reply, Had to make closing tag optional for hlinkregex to parse the file. But by doing so lost the formatted values ""C21":{"t":"n","v":491341.285099999}" & Hyperlink information.

chk- commented

2015-04-01 15:53:52 +00:00

(Migrated from github.com)

@imsaquib Maybe I could create a better regular expression for that, but I would need a sample file from you. How does a hlink tag look like in your file? In my case, the mergecell tag had no closing tag, so no information was lost.

imsaquib commented

2015-04-01 16:26:38 +00:00

(Migrated from github.com)

@chk- The mergecells & hyperlinks inmy sample xlsx looks like:

@chk- The mergecells & hyperlinks inmy sample xlsx looks like: ![image](https://cloud.githubusercontent.com/assets/11755337/6945811/f22c6990-d8b9-11e4-90f6-cd88e3f9e8eb.png)

SheetJSDev commented

2015-04-01 17:20:56 +00:00

(Migrated from github.com)

@chk- @imsaquib Incidentally the same issue was discovered by @oising a while back. We ended up going through and fixing a bunch of them in an unreleased change.

For the hyperlink case, regarding the assumption that the tags are singletons (not open/close), the relevant section of the specification is ECMA-376 18.3.1.47 hyperlink. It points to the type CT_Hyperlink which has no child. This is the snippet from the schema:

<xsd:complexType name="CT_Hyperlink">
  <xsd:attribute name="ref" type="ST_Ref" use="required"/>
  <xsd:attribute ref="r:id" use="optional"/>
  <xsd:attribute name="location" type="s:ST_Xstring" use="optional"/>
  <xsd:attribute name="tooltip" type="s:ST_Xstring" use="optional"/>
  <xsd:attribute name="display" type="s:ST_Xstring" use="optional"/>
</xsd:complexType>

@chk- @imsaquib Incidentally the same issue was discovered by @oising a while back. We ended up going through and fixing a bunch of them in an unreleased change. For the hyperlink case, regarding the assumption that the tags are singletons (not open/close), the relevant section of the specification is ECMA-376 18.3.1.47 hyperlink. It points to the type `CT_Hyperlink` which has no child. This is the snippet from the schema: ``` <xsd:complexType name="CT_Hyperlink"> <xsd:attribute name="ref" type="ST_Ref" use="required"/> <xsd:attribute ref="r:id" use="optional"/> <xsd:attribute name="location" type="s:ST_Xstring" use="optional"/> <xsd:attribute name="tooltip" type="s:ST_Xstring" use="optional"/> <xsd:attribute name="display" type="s:ST_Xstring" use="optional"/> </xsd:complexType> ```

SheetJSDev commented

2017-03-20 15:49:55 +00:00

(Migrated from github.com)

Ok so it looks like Excel doesn't actually care. We will change these and a few other tags

Sign in to join this conversation.