Ask Your Question
0

Why is Calc displaying zeros opening an Excel .xlsx file.

asked 2020-12-05 02:06:47 +0200

mmouer gravatar image

I'm using LibreOffice(7.0.3.1) downloaded & installed 12/3/2020 on a MacBook Pro running macOS Big Sur.

I downloaded a .xlsx file from QuickBooks. All zeros were displayed in the cells. Opening in Numbers and Excel displayed the data. Why is all zeros being displayed in Calc?

edit retag flag offensive close merge delete

Comments

1

You are the only one who can tell until you share the file.

gabix gravatar imagegabix ( 2020-12-05 06:10:37 +0200 )edit

The problem with that is, it is confidential finance information from a nonprofit. As I explained I DL from a QuickBooks report and it works in Excel & Numbers but not in Calc. The problem must be inherent in Calc.

mmouer gravatar imagemmouer ( 2020-12-05 08:55:48 +0200 )edit
1

The problem is not necessarily in Calc. It may be in the file. One common case: third-party software produces fake XLS(X) files that turn out to be, for example, so called HTML output.

Once again, until you share the file, nobody will be able to check.

gabix gravatar imagegabix ( 2020-12-05 09:23:08 +0200 )edit

2 Answers

Sort by » oldest newest most voted
2

answered 2021-04-10 18:03:09 +0200

(Answering to question by @hjt534, posted as "answer"; hoping that it would also answer the original question)

Your document is generated by Apache POI. It has zeroes in place of all calculated values, like this:

<row r="9">
    <c r="A9" t="s" s="3">
        <v>18</v>
    </c>
    <c r="B9" s="4"/>
    <c r="C9" t="n" s="5">
        <f>50.00</f>
        <v>0.0</v> <!-- <========= this -->
    </c>
    <c r="D9" t="n" s="5">
        <f>(C9)-(B9)</f>
        <v>0.0</v> <!-- <========= and this -->
    </c>

LibreOffice does not automatically recalculate cached values in opened XLSX by default - see this setting:

image description

You may change the setting; and you may press Ctrl+Shift+F9 to force recalculation of everything in the file.

edit flag offensive delete link more

Comments

This worked for me. Great stuff! Thanks, Mike!!

hjt534 gravatar imagehjt534 ( 2021-04-10 18:09:43 +0200 )edit

You should tag the answer (). Thanks!

Hagar Delest gravatar imageHagar Delest ( 2021-04-11 18:41:49 +0200 )edit

@Hagar Delest I tried earlier, but I do not think I can. The system doesn't let me tag the answer because I did not create the question, and it does not let me upvote because I'm brand new here, so I don't have sufficient karma. 8-/

hjt534 gravatar imagehjt534 ( 2021-04-12 15:38:20 +0200 )edit

Indeed, I missed that!

Hagar Delest gravatar imageHagar Delest ( 2021-04-12 15:39:40 +0200 )edit
0

answered 2021-04-10 17:40:39 +0200

hjt534 gravatar image

updated 2021-04-10 17:42:35 +0200

I have the same issue and our books are not confidential. Please see the attached file. C:\fakepath\West+Vincent+PTA_Budget+vs+Actuals+2020-2021+Budget+-+FY21+PL+.xlsx

BTW, an inelegant workaround that I've been using is to open the file in Google Sheets and export (File, Download >) it in OpenDocument format.

Thank you in advance for any help!

(edit - Sorry, I'm brand new to this forum. I think this should have been a comment, not an answer(?))

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-12-05 02:06:47 +0200

Seen: 121 times

Last updated: Apr 10