Slashdot Mirror
Gnumeric Now Supports All Excel Worksheet Functions
unmadindu writes "The latest beta release of Gnumeric has been released. According the the developers, it is now ready and stable enough for general use and deployment, and the final 1.2.0 release will be made on September 8th. This release also marks the realization of a major milestone -- all of the worksheet functions in the U.S. version of MS Excel are now supported. I have been using 1.1.19 for quite some time now, and it is incredibly fast, and hugely improved compared to Gnumeric 1.0."
Of course, it doesn't actually support all, or even most, Excel functionality.
It lacks Visual Basic support. And OLE support.
New versions of Excel are allways backwards compatible. In the event that something feature would need to be added to the .xls format, old versions of excel - and Gnumeric - would still be able to read everything else.
Sort of like HTML... if a browser encounters a flag it is not familiar with, it just ignores it.
The unofficial
Actually I use the Open-Office spreadsheet quite a bit at work, and can't see any reason to change to be honest. Part of my job involves perl scripts that generate .xls spreadsheet reports at night for users to view the next day and my tests with OO render them exactly the same as the users see them with Excel.
;-)
BTW, the reason we switched to doing this was due to the old system; where Access was running on PCs, and generating reports was so damned slow! It may seem unbelievable, but changing from Access+MySQL (we replicate from our Oracle server for reports and other stuff) to Perl+MySQL on Linux resulted in a staggering increase in speed. Reports that were taking an hour are now completed in under 2 minutes! The method I use to convert from Access->perl is, firstly take the Pseudo-SQL Access generates, then customise it a bit for MySQL, then use the Spreadsheet::WriteExcel module for perl. It's great!
I've never used Access myself BTW, and don't really understand what the hell it's doing to use all the CPU cycles. We watched it's activity one day - it ran a query on the Linux box, which took 12 seconds (monitored it with "top"), it then pegged the Windows PC - a P4 2.4ghz - running Access at 100% load for a good 20 minutes generating a spreadsheet!! WTF?!
So, to anyone else suffering with slow Access reports, learn some perl
Code, Hardware, stuff like that.
I'm guessing 'localization' functions.
..... ?
for example, in the old days when the UK used pounds,shillings,pence they'd have needed some special functions in the spreadsheet to work with currency values.
And who knows what extra functions may be needed to support Hebrew, Tamil, Farsi
Quidquid Latine dictum sit, altum videtur (anything said in Latin sounds important)
One way to make things go a little faster when using Access to drive Excel is to set an Excel.Range object equal to the upper left corner cell where you want data, and then CopyFromRecordset.
That assumes you've got things the way you want them in your SQL SELECT clause. If you need to tap every Recordset field prior to writing to a cell, one hopes your data are few.
Keeping this remotely on topic, are the various GNUmeric programming interfaces comparable to that beloved language, VBA?
Get thee glass eyes, and, like a scurvy politician, seem to see things thou dost not.--King Lear
There is a vast difference between
=Sum(A1:A10)
And an Excel.Chart object.
The beauty of Open Source is that, if you feel passionate about these features, you can light off CVS add them, and improve the net happiness of the user community.
Get thee glass eyes, and, like a scurvy politician, seem to see things thou dost not.--King Lear
The OASIS format will be very similar to today's Openoffice format, so if they start supporting Openoffice format now, it will be easy to switch to OASIS later.
Its a question of resource allocation. For any time we spend polishing our xls import export we get access to the installed base of MS Office. Whereas time spent on OOo's formats yields a much smaller number. Given more resources we'd spend more time on it, but for now I threw together an importer in a couple of weekends, and have only received one bug report in 6 months. given the documentation for the OOo format, and more importantly the existance of readable code that impliments it, it would be a simple project for someone to improve our support.
Or GEOMEAN. Excel can produce a zero result even though none of the args are zero -- totally bogus. Gnumeric has much better precision.
We certainly do hope for the migration rush, especially from people who care about getting the right result.
In addition to supporting a superset of their worksheet functions, we're also significantly more accurate and stable numericly. With luck I'll have a 3rd party evaluation confirming that within the next few weeks.
It varies. We have a fairly extensive test suite that tries to keep us compatible with MS Excel for the most part. When a discrepancy appears there are 3 possibilities
1) Gnumeric bug which we fix.
2) Better precision in Gnumeric. This is fine. People tend to prefer the right answers when they can be convinced that XL was being silly. eg our VAR, HYPERGEOM, and various financial routines.
3) There is a bug in XL. This is a royal pain in the butt.
We end up with 2 functions. The XL 'FOO' that attempts to be bug compatibile, and a fixed G_FOO. We don't get a choice here. People tend to freak out if their imported spreadsheet starts to produce different results. Hopefully in time they can be convinced to use the G_ variants by default.
However, this is definitely an area we take very seriously. The Gnumeric project has received a grant to produce a test suite for open source spreadsheets. I'll announce more details shortly.
Soon.
This is something I've been working towards for a while now. It will hopefully happen some time early in the 1.3 development cycle. Having a win32 build (and ideally an osx build too) is a very important for the next stage of migration. People migrating to linux will use an app that is compatible, but they're alot more likely to be allowed to use it by central management if it will run on windows too. This is one of the key difference between abiword and Gnumeric. Their community has been bolstered alot by the infusion of windows users and developers.
If anyone is interested in helping with this its largely just a build monkey issue. The underlying libraries are available for win32 (the gtk stack). All we're lacking is someone with the time to patch the last of the build problems, and point out any lingering non portable calls.
I downloaded and Installed it. I decided to stress test it with a large excel spreadsheet (Around 1500x10x3). It imported all the data ok, but it ruined the fonts! I had to change it back into courier when it had changed it to arial.
Its Interface is still a bit confusing and is filled with engrish such as "replace by" in the search and replace dialog, it should be "replace with". I think the "HIG Police" should come after Gnumeric.
The Insert Image dialog does NOT have a preview pane (which IS ESSENTIAL WHEN CHOOSING IMAGES, I don't want to accidently insert the goatse guy!).
They Messed up the zooming dialog, now its harder to use.
GNUmeric is so close to beating excel, but the little issues stop it from beating the "doe test".
Microsoft XML and rest-of-the-world XML are two completely different things. MS uses a proprietary schema. It's no better than the old .doc format.
We support wk1, and wk2. There are some specs available for newer versions but I haven't had time to do more work on it. This sort of development is very parallizable. The i/o plugins are completely modular, so if anyone is interested in a new or esoteric format its not at all difficult to get something in place.
If anyone is interested please contact me.
I've already roughed in a framework to support the XL 2003 xml format. However, we've heard the MS Office / XML chant before. It did not see much common usage, the performance hit with non-trivial files was just too large.
Gnumeric-1.2 will use stock gnome-2.x libraries. It installs painlessly on rh8 or 9. If you have a nasty test case send me a copy (it can be kept confidential). Gnumeric-1.1.20 is significantly faster than 1.0.x but we can't fix a problem if we haven't seen it.
True, this has been a sticking point for our scripting support. We have not wanted to commmit to a scripting interface that would not allow VBA to be used out of the box. There are several issues with that
1) security.
There is absolutely no way in hell that we'll allow vba to run without some sort of sandbox and user intervention to explicitly enable the macros. This will definitely make life more difficult, but perpetuating the nightmare of vba viruses in office docs seems like a terrible idea.
2) Reading and writing the macros. Unlike xls, the vba streams have no public documentation as far as I know. The anti-virus folk appear to have some under various NDAs but I have not seen enough to get a good handle on things. OO and gnumeric can both extract the compressed source code out of the vba streams, but neither of us has a good way of ensuring that will work.
3) In an ideal world we'd be able to extract the p-code rather than the vba source code. That will enable a simple mapping from vba to a more more opensource friendly language like python. The precompiled p-code would remove the need to parse actual vba.
4) If we're actually forced to use VBA, I'm hoping the mono's vb support will be viable as a fall back.
However, even if we find the file format, and we have an interpretter. Supporting it will require a gnumeric scripting api that supports the entire XL api. A large and daunting task. We'll do something smaller and cleaner first, likely based on our experiments in python. To date we've avoided blessing any scripting api because we don't want to offer one api then pull the rug out from under people and change it. An API needs to be stable to be useful. This is high on our list of projects for 1.3.
I made the claim as clear as possible, and belive it is true.
We can accurately reproduce results from every function in the US version of MS Excel.
We'd don't do quite as well comparing evalation techniques, although we're well ahead of OOo in that regard. 1.0.x had support for
- iterative evaluation
- implicit intersection
- and implicit type conversion
In 1.1.x we've added
- dynamic dependencies (eg OFFSET, or INDEX)
- constructed ranges (A1:INDEX(...))
- and support for implicit iteration for function arguments
That last one is the sole remaining issue as far as I know. I need to finish off support for implicit iteration for operators. Hard to say if it will go in for 1.2.0, probably not (although I've got it partially done), so it will likely wait until 1.3
Actually that capability was one of the first things that went into Gnumeric. Its an area we trounce MS Excel on several levels. - Adding a plugin to gnumeric is trivial. Indeed the vast majority of the functions are in plugins that are demand loaded. - plugin functions can be written in C (like an XLL but with a much cleaner interface), python, perl, or guile Docs for python here
We have tests for accuracy. Speed tends to be more of a 'hmm this is too slow for my taste lets see why' sort of operation. Gnumeric-1.2 is damn fast compared to 1.0, and is easily faster than OOo.
However, I'm not clear on what you're looking for in the context of the solver.
1.1.x has goal seek, and several different optimization algorithms curtesy of various other projects (linear, integer, and non-linear). We're less concerned with XL compatility here (they suck badly). Nor is speed of paramount concern. It seems more important to produce accurate and stable results.
Dunno what else to say. Try it out and bugzilla a report if you see a problem.
IIRC, pivot tables are "functionality", not a "function". Functions are those things that look like: =foo(a,b,c)
http://www.dropline.net/gtk/.
GTK in one nice bundle.
Make sure you let developers of GTK applications for windows know that you want them to build against a shared GTK and save you from having to install multiple copies. Pan and Gaim already do this.
An ideal installer for Gnumeric would do something like what the GIMP does and bundle GIMP+GTK into one
(for users who already have GTK for windows then smaller installer without GTK might be made available).
While MS Excel may have an extensive array of features it is somewhat lacking on the accuracy front. At least as far back as Sawitski (1994) various scientific analyses have been critising Excel using phases like "can be judged inadequate" and "it can be deduced that Excel uses an unstable algorithm". However as McCullough & Wilson (1999) note Microsoft has done little to address these concerns. The problems Sawitski found in Excel 4 were still present in Excel 97 and Excel 2000 for that matter. In fact critisism of the accuracy of Excel 2002 and XP in the scientific literature continues e.g. McCullough & Wilson (2002).
To quote the The Gartner Group, "Enterprises should advise their scientists and professional statisticians not to use Microsoft Excel for substantive statistical analysis". Of course if you do not need to do accurate statistical analysis then these problems will not effect you but given that Microsoft knows about and has largely ignored these problems and scientists are the people most likely to check that a given piece of software really does what if claims to do rather than using it blindly, it seems quite possible that similar problems exist in other parts of Excel but have yet to be exposed.
Rather than blindly copying Excel, the Gnumeric team might do better by trying to bring on board some of these scientists who have been testing and critising Excel in order to improve the accuracy of Gnumeric, so that not only does Gnumeric beat Excel on features but also, and far more importantly, on accuracy. See the following links for more info on the problems with Excel, 1, 2, 3, 4.