Is ignoring a formula and instead returning a 0.0 value a bug?

[mikeschinkel]

I have a use-case where I am exporting from QuickBooks as a .zip file containing multiple .xlsx files, unzipping it, and then processing each .xlsx with a Go program I have written in excelize; great package, btw, thank you.

The first known problematic file I have come across is Balance_Sheet.xlsx. It is essentially in a two-column spreadsheet with account on column A and money values in column B.

When I run my Go program that uses execlize to read that file, I get all zeros (0.0) in the money columns rather than the values that appear if I load Balance_Sheet.xlsx into a desktop app (I am using Apple's Numbers since I don't have MS Excel installed.)

I have forked the repo and used the Delve debugger to trace through and identify where the zero value is returned — in (c xlsxC.getValueFrom() where it goes straight to checking for a numeric in c.V and ignores c.F.Content — which has a value of either a numeric like 62694.66 or a formula like ((B10)+(B13))+(B16) — even though c.V==0.

I was going to hack it and possibly provide a PR to substitute in the value from C.F.Content when c.V==0, but then realized that would not work for ((B10)+(B13))+(B16). Try as I might I could not figure out how to resolve that expression with excelize.

Does excelize even have expression resolution, or does it just rely on the value in <v> and ignore the contents of <f>?

I may have found my answer that tells me possibly that instead of calculating expressions excelize's approach is to delegate that to the user using Excel given your header comment for File.UpdateLinkedValue() and its link to this page on Microsoft's Learn website. But I am desperately hoping that is not the case because my use-case needs to be fully automated.

BTW, this is what a couple of the problematic rows look like from inspecting the value of rows.decoder.r.s using Delve:

<row r="9">
    <c r="A9" t="s" s="3">
        <v>4</v>
    </c>
    <c r="B9" t="n" s="5">
        <f>62694.66</f>
        <v>0.0</v>
    </c>
</row>

and

<row r="17">
    <c r="A17" t="s" s="3">
        <v>12</v>
    </c>
    <c r="B17" t="n" s="6">
        <f>((B10)+(B13))+(B16)</f>
        <v>0.0</v>
    </c>
</row>

I am thinking that whoever wrote the code to generate the Excel files for Intuit didn't think to put actual non-zero values in the <v> element even though it is likely that Excel would always do so?

[mikeschinkel]

And after literally hours of trying to figure this out to the point that the next day I write up the problem, I find that within minutes I find part of my answer, that yes, excelize can evaluate expressions with the File.CalcCellValue() function.

That resolved my question is now; should excelize run that function if it finds that c.V=="0" and that c.F.Contents!="" within xlsxC.getValueFrom()?

[mikeschinkel]

I was able to address the needs of my use-case.

So I submitted as a draft PR for review and discussion: #1823.