Native read/write of Excel files (.xls and .csv)

[Tom_Hawkins]

Like it or loathe it, Microsoft Excel is heavily used for storing, recording and analysing lots of data and many LabVIEW programmers must find they need to get data into or out of Excel. However LabVIEW lacks the ability to read and write Excel files directly. It is possible to exchange data with Excel via ActiveX automation, but this:

• requires Excel to be installed on the same computer as the LabVIEW application
• is not cross platform
• is complicated to do
• does not appear to work reliably across different versions of Excel

The Report Generation Toolkit allows creation of Excel files but this costs extra and is not cross platform. 

Many software packages can read data directly from Excel .xls files (e.g. Umetrics SIMCA-P) and presumably it is therefore also straightforward to write data out in Excel .xls format. Native LabVIEW functions for reading and writing .xls files - for example, into string arrays - would be very useful. It would not be necessary to support formatting, charts etc - although some kind of basic formatting support might be useful.

Of course it is possible to exchange data with Excel via delimited files (comma- or tab-separated values) but the .csv format requires characters such as quote marks, commas and carriage returns in a string to be escaped so that they are not incorrectly interpreted as part of the file structure. Tab-separated files suffer less from this problem but on Windows at least, files with a .tsv extension are not automatically associated with Excel whereas .csv files are. It would be helpful if LabVIEW also had native functions for reading and writing .csv files with correct escaping for Excel.

[Intaris]

How can one argue that the report generation toolkit not being cross-platform is a bad idea and then argue that LV should write XLS files?

Isn't that somewhat contradictory?

And exactly which version of xls files should be supported.  There's a new one every couple of years and MS has started cutting support for older versions IIRC.

You can also save a tabs delimited file as ".dat" and associate it with Excel.  Works a treat.

[rolfk]

Presumably easy, yes, but only presumably!

And that many software packages can read xls files directly without an Excel installed on the system is also a strong exageration. Those that do, have all kinds of trouble to deal with the various formats, versions, and Microsoft specialities to do things different than the format documentation would indicate (for as far as it is documented at all).

[Tom_Hawkins]

Intaris, I don't understand your 'contradictory' comment. I believe that many LabVIEW users would like the ability to read and write Excel files and that probably includes users on Mac and even Linux.

I'm not sure that associating all .dat files with Excel is a very good idea (there's no way of doing this on a per-file basis in Windows, is there?). It's been pointed out elsewhere that you can actually save a tab-delimited file with a .xls extension and it will open just like a real Excel file when double-clicked... that is, until the user makes a change, tries to save it, and gets the highly confusing '...may contain features that are not compatible with...' dialogue. Also, if you try to open that file from Excel's Open... dialogue, or from the recent files menu list, you get the Delimited File Import Wizard - again, confusing for users.

Rolf, I'm not saying the problem is trivial and I know that any Microsoft 'format' is a moving target, but let's keep it in perspective: what I would like is not a universal and perfect implementation but just the ability to read numeric and text values out of the cells of common Excel files, without having to go via Excel. The commercial software I've mentioned does that very reliably.

[Intaris]

I'm not aware of a version of Microsoft Office for Linux.... That must be new.

If there's need for a true cross-platform file format, then ODS is the only real candidate.

The new Office will also be able to read them and the OpenOffice package is available on all major OSs (And costs nothing).

[Tom_Hawkins]

There isn't a version of Microsoft Office for Linux, but that doesn't mean that a LabVIEW program running on Linux might not want to read or write Excel files.

By all means let's have read and write of ods files too, the more the merrier. There are still going to be millions of copies of Excel and millions of xls files around for a few years yet though.

[Intaris]

ODS has added advantages that it's not covered by any patents and it's file format is fully documented.

[tst]

> By all means let's have read and write of ods files too, the more the merrier.

There is a limit to NI's engineering resources. Any feature they will work on will come at the expense of something else.

[Jannis]

I probably should post that in a New Idea:

How about using ActiveX? It actually does work well (once you spent some time understanding Excel's object model) - as long as you keep to one version of it! Here's the idea: Get NI to work on a more flexible ActiveX connection. I would love to flip a switch and link to a different ActiveX version without having to re-route all reference wires.
I actually have the same problem with different versions of AutoCAD.

[Tom_Hawkins]

Again, ActiveX requires Excel to be installed on the same computer and is only available on Windows...

[Tom_Hawkins]

There's a Python package called xlrd that seems to do a decent job of reading data out of Excel sheets (BSD-style open source licence):

http://pypi.python.org/pypi/xlrd

The documentation for that package suggests that the OpenOffice.org project "has truly excellent documentation of the Microsoft Excel file formats" at:

http://sc.openoffice.org/

Message Edited by tmh 153 on 03-17-2010 07:52 AM