Darren's Occasional Nugget 09/26/2022

[Darren]

It's fairly common for LabVIEW programmers to need Excel data (.xlsx files) in LabVIEW. There are multiple ways to do this, but most of them have caveats...

1) "You can do it with such-and-such API, but it's an add-on you have to install from VIPM."
2) "You can do it with ActiveX and/or the Report Generation Toolkit, but that requires Excel to be installed."

3) "You can read spreadsheet text files in LabVIEW, but you have to convert from .xlsx first."

Well, with this nugget post, there are no more complications! See the attached VI (saved in LabVIEW 2018). No add-ons required, no Excel required, no extra steps of file conversion. Just pass in the path to your .xlsx file and you'll get all the data from all the sheets in the workbook as 2D arrays of strings. If you need the data in numeric/timestamp/whatever format, you'll have to do those conversions yourself.

Before you look at the diagram and complain... yes, I know there's room for improvement. The comments on the diagram say as much. 🙂 I wanted to get this single-VI solution out there so people could start using it today. If anybody wants to take this as a starting point and make a cleaner API around it and post that to the NI forums, you're welcome to do so.

But for now, enjoy this single-VI solution to getting .xlsx data into LabVIEW!

[pallen]

Considering everything it took in the past to open an Excel file directly, doing it with just one VI is awesome!

It even opens multiple sheets.  Nice.  😎

Thanks for sharing this one! 

[K C]

Thank you Darren for this one !!

Nice to read the Excel without having Excel installed.

Kees

[Defaphe]

Very nice.

Even if it takes a looong time for big array data, it is doing it in one VI and several sheets are read.

I think I could use it for small files / debug but for big ones I will keep doing an ad-hoc function with RGT as it is faster.

[wiebe@CARYA]

We recently started using Open XML SDK 2.5 for Office | Microsoft Learn to read and write .xlsx files.

It's not as easy as AX (but it works in executables, no Excel needed), but a lot easier than DIY XML operations.

[paul_cardinale]

Wonderful.  Here's my 1st whack at cleanup.

[Bob_Schor]

Thank you for this very interesting contribution.  I've been a fan of the RGT for quite some time, and have tried to clarify Forum posts to "Read Excel files" that were really about reading Comma-separated-values files, as well as suggest that the RGT can do a lot of "simple things" with real Excel files (if you have Excel + the RGT).

Of course, the "real" purpose of the RGT lies in the middle initial -- "Generation", i.e. writing an Excel Workbook/Worksheet.  I'm assuming that "putting the pieces back together", as opposed to "taking the thing apart into pieces", is as difficult for an Excel Workbook as it is for, say, a watch or a car engine ... 😀

Bob Schor

[paul_cardinale]

Here's a makeover.

[ooth]

Thank you Darren! I was looking for something that used the zipped files instead of opening Excel.

I did improve the speed significantly by removing the search for "/>" when it iterates through the cells. Then I replaced the case structure with insert an empty string when the cell was 0.

I commented my changes.

[paul_cardinale]

And now this.