I've been working on making my OpenSUSE 10.2 box my permanent home computing environment for a few months now, I've got it set up for web browsing, development, media catching. I tried recently to open a rather large personal finance-related spreadsheet in OpenOffice Calc and was appalled.

The Excel workbook has roughly ten worksheets, lots of data, many graphs, many cells that are dependent upon other cells through formulae, many styled cells and no macros. Upon opening in Calc, the window froze for roughly five minutes (refusing to repaint). After this, Calc came up. The overall look/functionality of the workbook was intact, but many of the cells now had invalid values in them. I traced the chain of invalid values back to a column of Date cells that had formula like:

=DATE(103, 10, 1)

I remembered that Excel uses 1900 as the basis for dates, so the formula gets calculated in Excel to be Oct 1st, 2003. However, Calc expected the full 4-digit date ("2003"). I wonder why Calc didn't interpret the cell as Oct 1st, in the year 103 AD? Anyway, after fixing the top cell the window to:

=DATE(2003, ROW()+10, 10)

the Calc window froze again for about 1 minute. When it came back, the cell's contents were now valid. I fixed the rest of the column formula problems by copying and pasting into the workbook and had to wait for another minute or so while Calc churned away on that.

Obviously Calc was struggling to auto-calculate with each change in the cell contents, so I did a quick web search and learned how to turn off Auto-Calculate (Tools > Cell Contents > Auto-Calculate should be unchecked). Problem with that is that now when I've made my changes, I still need to hit F9 (or is it Ctrl+Alt+F9) to force a recalculate and I have to wait a minute or two with the window frozen. This is simply unacceptable behavior when the competing product, Microsoft Excel, does this in the background at near-instant speed.

I've read in some forums that this might have to do with cells that have custom styles on it. At the moment, I simply do not have time nor patience to re-build my workbook from the ground up, piece-by-piece to see what the offending style/graphic is. So for now, OpenOffice Calc has lost a user.

§345 · March 13, 2007 · Linux, Software, Technology · · [Print]

6 Comments to “OpenOffice Calc: No Thanks For Now”

  1. Jason says:

    This is the same type of reasons why I haven’t been able to switch over to OpenOffice either. I can’t completely blame OpenOffice either. I’m sure that Microsoft does some pretty messed up things with their format that is very hard to deal with. (Maybe not your example) Just think of all the legacy garbage they carry forward. Even if OpenOffice does a good job and can get it right 95% of the time, it doesn’t do me any good unless it’s 100%. There will always be some document it can’t read that MS Office does.

    I had a Word document that basically had a grid of embedded pictures, fairly simple. I tried opening it in Writer, and the positioning of the images was all off. It would have taken a lot of time to reposition everything the way it was.

  2. Yeah, I wasn’t so upset with the DATE() formula problem, it was something I was able to figure out, at least – and heck, OO.o’s implementation is more reasonable to my eyes. But what makes Calc a non-starter for me is the performance issues – waiting any amount of time for a spreadsheet to update its caculations is a step far backwards in time, since Excel’s performance is so excellent.

  3. Mauriat says:

    Well, I have to join the chorus. So I took 2 years of random receipts I wanted to throw away and put them in Excel. I derived 2 cells with =YEAR() and =MONTH() from the main date cell and did a quick “AutoFilter” so I could view by month and year. I’m having update problems and the autofilter sometimes just won’t work. If I go back and forth between Excel and OO it seems to fix it, but then, I guess, I might as well just use Excel. I will admit I have not taken the time to troubleshoot this.

  4. Anders says:

    OpenOffice calc is a shame and a big disappointment. It takes over 14 minutes(!!) to load my biggish workbook of about 6 Mb (Win XP/AMD64 3500/2 Gb RAM), while Gnumeric needs about 20 SECONDS and Excel all of 4… I am sure Microsoft feels extremely threatened by Sun’s spreadsheet efforts. Now, why doesn’t Sun fix a product which loads 198 times slower than the competition?

    This said, I much prefer OO calc’s interface to other spreadsheet products I have tried. But I can’t spend most of my working time waiting for documents to load.

    Too bad. Too bad…

  5. In fairness, I did open up another .XLS file in OOo.Calc recently and it opened just fine (albeit there was no heavy styling or chained equations in this one).

  6. sonny says:

    It takes that much for 2 things, the first is that Openoffice is slower than Excel when recalculating formulas in big datasets, and the other is that the Excel format makes it slower, you should open the Excel file and inmediatly saved it as the standar openoffice fileformat (*.ods) then you’ll get a considerable time improvement.