Darren's Occasional Nugget 09/26/2022

[dumi0501]

here is an attached xlsx that doesnt seem to work.  it is strange though because when i open it and re-save it, it works fine.

[Darren]

That .xlsx file contains tags that I haven't seen before... more specifically, they are formatted as <x:c> instead of <c>. The file must have been saved with an older version of the Open Document standard than any of the test files I used when I originally wrote the VI.

Anyway, here's an updated version, it appears to read your old file fine. Let me know if you have any other issues.

[dumi0501]

thank you very much for this.  i also saw you added the "workbook2.xml" fix which i was also running into occasionally.

[X.]

I ran into issues opening a fairly basic xlxs file using the GitHub repo version, while @Ettep's version worked fine.

What I noted though, using a more advanced file, is that @Darren & @Ettep's codes don't treat cells spanning several columns and/or rows as expected, in the sense that the 2-D string array output does contain empty values where the larger cells value would be expected to be repeated.

The current codes only output the cell's content in the first "elementary" cell of a multi-row/column cell.

[Ettepet]

---

@X. wrote:

I ran into issues opening a fairly basic xlxs file using the GitHub repo version, while @Ettep's version worked fine.

What I noted though, using a more advanced file, is that @Darren & @Ettep's codes don't treat cells spanning several columns and/or rows as expected, in the sense that the 2-D string array output does contain empty values where the larger cells value would be expected to be repeated.

The current codes only output the cell's content in the first "elementary" cell of a multi-row/column cell.

---

I would need an example xlsx-file to implement the needed changes.

It was fun to see how these xlsx-files work.  Some of it reminded me of the "Reveal Codes" feature used by Wordperfect.

[wiebe@CARYA]

If you're dealing with OpenXML, have some fun with the  Open XML SDK 2.5 Productivity Tool · (GitHub).

It's an impressive (free and portable) tool.

It validates OpenXML files and sometimes gives details about what is wrong.

A great feature for a developer is that it can "diff" 2 OpenXML documents and generate C# code (using the OpenXML library) to go from one document to the other.

[X.]

I am attaching a modified version that offers the option to fill in the multicell-spanning cells with their common values.

I threw in options to remove empty columns and rows for good measure (I needed this).

Quick work so user beware.

[TiTou]

your improvements are interesting and they do solve some issues I have but you version does not handle references (see latest pull request on github).

would you be have to contribute your changes on the github repo please?

[TiTou]

on my side the biggest issue is that the current VI in the main branch on github works just as I expect on Ubuntu 22, but when I put it in an Ubuntu 22 docker container cells that contain any accented char come back empty.

it's not easy to debug 😞

[TiTou]

I found a much easier solution, I wrote a python script that uses pandas to convert each sheet of my xlsx file to a csv file.

works great!