How to get data from an existing Excel file using report generation toolkit

[Michael.Koppelgaard]

Is there any way to read an xlsx-file into labview - edit data - and save it back to Excel? 

[aputman]

Of course there is a way to retrieve data.  Excel Get Data.vi

Now the question is if this is the best way to go about importing data into Labview.  How is the data originally written to the XLSX file?  Can you use a CSV file instead?

[Michael.Koppelgaard]

I can use CSV and that’s what we do now, but all the advances with Excel formulas , Excel formatting, etc. is lost.

[Michael.Koppelgaard]

This is how to load data.
But how to write them back?

[aputman]

There is an "Excel Easy Text" and "Excel Easy Table" for writing data back into an XLSX file.  Have you looked thru the VIs in the RGT?  I think the VI names are self explanatory.  There should also be examples that ship with Labview that provide explanations for the basic things you can do with the toolkit.  Look thru these examples and if you have questions about them, post you question again but be more specific about what you are trying to do.  

[Bob_Schor]

As you probably know, the New Report function that, by default, just opens Excel so that you can write a "New Report", also has a "Template" input you can use to specify an Excel File that you want to open, read, modify, and write (if you do a read/modify/write sequence, you, of course, need to provide the same Path name to the Save Report function).

Depending on your Excel file, the logically simplest method to do a Read/Modify/Write is to do a New Report, read in all the data with Excel Get Data, modify the data as needed, then write it all back out (overwriting the original Data) using Excel Easy Table.

You can also use the RGT to do more sophisticated things.  For example, you can append more rows to an existing Excel Workbook by using Get Last Row and the ability of Excel Easy Table to start writing from the first blank row.  You can even update individual cells.

The key to using the RGT is to realize that the thing you pass around that looks like a Green Chain is a "Report Object" that more-or-less refers to Excel and the Workbook you open with New Report.  If you open an existing Excel Workbook with New Report, then save it "to itself" with Save Report (without changing anything), it's like opening the Workbook (in Excel) by double-clicking it, looking at it, Saving it (which does nothing, since you haven't changed anything), then closing Excel (= Dispose Report).

Bob Schor

[Michael.Koppelgaard]

Okay - thanks all of you, I look through examples 

[Michael.Koppelgaard]

Can't find any examples how to load data FROM Excel only the other way.
Can anybody help?

[Bob_Schor · Accepted solution]

Three replies ago, I told you (in words) exactly how to do this.  Here, in Shiny LabVIEW Functions (and one of my own, called "Wait 2 seconds" that I leave for you to figure out how to create) is a demo that does the following:

• Creates a new Excel Report with a single line having the number "1" in three columns, waiting two seconds before closing the Excel Report so you can "see" the results.
• It then enters an "Append" loop to append three additional lines using as close to the same code as the "Create 1-line Report" section as possible.  Differences include:
  • Opening the Report with the existing .xlsx file as the Template file.
  • Via the top Shift Register, creating lines "2, 2, 2", then "3, 3, 3", then "4, 4, 4".
  • Via the bottom Shift Register, putting the first "new" row in Row 2 (the index for row 2 is 1, similar to Array indices) and saying that the "next" row will be the bottom-left edge of the existing Sheet.

I hope you consider accepting this Reply as a Solution so that other Forum users with a similar question will be able to find this example easily.

Bob Schor

[Michael.Koppelgaard]

This is a great example thanks.
Still one thing I haven’t understood:
If I want to load data from an existing Excel file into an array or a table, so I can actually see it in labview how can I do that?

Also in the case where the data is in sheet 2 and not sheet 1