Reading xlsx files

[garyhill01]

Hi

Try as I might I cannot read xlsx files. I can only read the cells which are numbers. Any text formatted cells are blank.

I have re-saved the file in open office in xls format and it opens fine with cfspreadsheet. Is there a flag I need to set when reading xlsx files - I'm pulling my hair out!

Thanks Gary

[Leftbower]

Hey Gary - I cannot duplicate? I just created a brand new .xlsx file with sample data. Numbers in COL_1, text in COL_2 and formatted text in COL_3. It reads fine. Please see images:

If you try to do same, do you still have issue?

[garyhill01]

Hi Andrew

Thanks for the reply.

Attached is the file I'm trying to read in, sorry it's ~370k but I thought I would send it raw/directly as I get it rather than try and fiddle!

I note the first column is text, but even if I add a number column as column A I still get the same result. The ONLY column I get returned is G

Any pointers would be greatly appreciated.

Regards Gary

On 5 March 2018 at 20:01, Andrew Kretzer notifications@github.com wrote:

Hey Gary - I cannot duplicate? I just created a brand new .xlsx file with sample data. Numbers in COL_1, text in COL_2 and formatted text in COL_3. It reads fine. Please see images:

[image: capture1] https://user-images.githubusercontent.com/978180/36996785-8515b08e-206c-11e8-91bc-c233e01561f4.PNG

[image: capture2] https://user-images.githubusercontent.com/978180/36996797-8bcfe94e-206c-11e8-83d0-670fecfde563.PNG

[image: capture3] https://user-images.githubusercontent.com/978180/36996800-8f50a6a8-206c-11e8-8ba5-463651980d59.PNG

If you try to do same, do you still have issue?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/Leftbower/cfspreadsheet-lucee-5/issues/14#issuecomment-370545220, or mute the thread https://github.com/notifications/unsubscribe-auth/AjXcXNHW6ruE6EloZTqBSBdrBSIsH7Irks5tbZmGgaJpZM4Sc_k3 .

[Leftbower]

Hi Gary, I don't see an attachment here on the Github site nor in my email that's linked to it?

[garyhill01]

Strange, I see it in my sent mail.....

Maybe github strips it?

Gary

On 5 Mar 2018 22:45, "Andrew Kretzer" notifications@github.com wrote:

Hi Gary, I don't see an attachment here on the Github site nor in my email that's linked to it?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/Leftbower/cfspreadsheet-lucee-5/issues/14#issuecomment-370594818, or mute the thread https://github.com/notifications/unsubscribe-auth/AjXcXOdlpih5k4VSq9i8ZEEo6kVFac7Qks5tbb_zgaJpZM4Sc_k3 .

[garyhill01]

https://www.dropbox.com/s/5nithc29et7rkif/20180305stocklist.xlsx?dl=0

Dropbox link.....

On 5 Mar 2018 22:47, "Gary Hill" gary@co7.net wrote:

Strange, I see it in my sent mail.....

Maybe github strips it?

Gary

On 5 Mar 2018 22:45, "Andrew Kretzer" notifications@github.com wrote:

Hi Gary, I don't see an attachment here on the Github site nor in my email that's linked to it?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/Leftbower/cfspreadsheet-lucee-5/issues/14#issuecomment-370594818, or mute the thread https://github.com/notifications/unsubscribe-auth/AjXcXOdlpih5k4VSq9i8ZEEo6kVFac7Qks5tbb_zgaJpZM4Sc_k3 .

[Leftbower]

Hey Gary - I checked out your file and had the same issue as you. I then validated it using the OpenXmlSdkTool and it shows that your .xlsx file is not valid:

I then just opened your file in Excel and - without changing a thing - did a SaveAs a different filename and validated the new file and it was valid and read as expected in cfspreadsheet:

So, it appears that whatever/whomever is creating your .xlsx file is somehow doing something out of spec. As to what that might be is a bit beyond my expertise!

For you - or anyone else who is interested - you can freely download the SDK to check for file validation here: https://www.microsoft.com/en-us/download/details.aspx?id=5124

I found it invaluable when testing this extension.

[garyhill01]

That is a great help, I didn't know about the validity of the file, but at least I know what it is.

Thanks again.

Gary

On 5 Mar 2018 23:46, "Andrew Kretzer" notifications@github.com wrote:

Hey Gary - I checked out your file and had the same issue as you. I then validated it using the OpenXmlSdkTool and it shows that your .xlsx file is not valid:

[image: capture] https://user-images.githubusercontent.com/978180/37006059-62e90b68-208b-11e8-8deb-066712f1bec3.PNG

I then just opened your file in Excel and - without changing a thing - did a SaveAs a different filename and validated the new file and it was valid and read as expected in cfspreadsheet:

[image: capture2] https://user-images.githubusercontent.com/978180/37006184-0dd8acae-208c-11e8-8292-ed8e159e9578.PNG

So, it appears that whatever/whomever is creating your .xlsx file is somehow doing something out of spec. As to what that might be is a bit beyond my expertise!

For you - or anyone else who is interested - you can freely download the SDK to check for file validation here: https://www.microsoft.com/en-us/download/details.aspx?id=5124

I found it invaluable when testing this extension.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/Leftbower/cfspreadsheet-lucee-5/issues/14#issuecomment-370608548, or mute the thread https://github.com/notifications/unsubscribe-auth/AjXcXMpb2hYkD7KIyJ-h-5tPYyFKh_Dbks5tbc48gaJpZM4Sc_k3 .

[Leftbower]

Happy to help!