OpenTBS & SimpleXLSX = invalid cell index > what does OpenTBS change?

Hi there again :)

I ran into another mysterious issue, for which I would need some expertise to solve it.

1. When I have this xlsm (or xlsx) file, I can normally open it with the also great plugin SimpleXLSX.

2. Then I pull the file through OpenTBS. Afterwards I can normally open the file and everything looks GREAT (thanks to your help btw!)

3. When I try to open the file with SimpleXLSX again, exactly in the same way as before, I give an error: "Invalid Cell Index".

What does OpenTBS change on the cell index? How could this error happen?

Do you have some wisdom about this?

S

Hi SanderK,

OpenTBS does perform a transformation of cells but all stays compliant with the OpenXML specification.
All absolute references of cells (such as "B3") are replaced with relative references.
This may not be supported by SimpleXLSX.

I've been awarded that some other tools does not support this syntax neither.
I should me be provide a way to transform back the syntax.

I see. Is that very complicated, or do you think it will be in the next release already?

I could try to look for another tool and rebuild that part of the code, but if you say it will be released soon I'd rather wait of course :)

Is is not so much complicated.
It is just to code the reverse function for MsExcel_ConvertToRelative_Item().

I could make it for a next release, but unfortunately it won't be done before 2 or 3 weeks.

Hi Skrol!

Sounds good, but take your time: I trew out SimpleXLSX entirely and manually unzipped the file and destilled the information from it (I saw the difference between the two files, but the cells in the xml also have the information in them that makes it possible to know what happend to them). That solved the problem :)

Thanks for the information again.

SK

Ok, good.

I was happy too soon :(

TBS removes the some of the <v>value</v> from within the xml sheets in the xlsx files. This makes it very difficult to get the value from these cells with php after tbs.

It happens only with cells that contain formulas like:

"=IF(J11="text";I11;SUM(I11/(1+J11)))"

and

"=IF(ISNUMBER(SEARCH(" 2 ";R11));"Yes";"No")"

and

=IF(VLOOKUP($D12;'(read only)'!$A:$B;2;FALSE)<>0;VLOOKUP($D12;'(read only)'!$A:$B;2;FALSE);" ")

I'm not sure what the thing is that causes it. Maybe the IF?

You said before you were going to look into this. Is that still the plan?

Would be very very awesome!!

SK

Hi,

> I'm not sure what the thing is that causes it. Maybe the IF?

OpenTBS has a feature to delete formula results in order to have them refreshed when the document is opened.
Otherwise cells with formulas keep their values even when cell concerned with the formulas are merged.

The method is named MsExcel_DeleteFormulaResults()

> You said before you were going to look into this. Is that still the plan?

This point cannot be fixed. Only Ms Excel (or other big XSLX interpretors such as Libre Office) can evaluate an Excel formula.

Hi Skrol,

I understand. I found a workaround to avoid this.

Hi SanderK,

There is a fresh beta version of OpenTBS that save Ms Excel sheets with explicit references.
Now workbooks can be opened with LibreOffice.
Can you check if it also work with SimpleXLSX ?

Regards,

http://www.tinybutstrong.com/dl.php?f=tbs_plugin_opentbs_beta.zip&s=2