string array write to measurement file xlsx

[TeamHalli]

Hello,

I would like to add a header on my excel spreadsheet, similar to what a report does. I like what I have accomplished so far, so the title block (operator, comments, etc) basically a 1D array of string is what I am missing.

With the flexibility I have to log every so often and writing to an XLSX, I cannot use the write to spreadsheet file VI.

Thanks for your input.

[Hatef.fouladi]

write to spreadsheet file VI. is subvi and you can open it go through it and change part that relate convert number to string just do not forget save this new subvi with different name (save as) so you can use it also you can use first write to text file for insert header and then use write to spreadsheet file VI.
because this subvi indeed is a code that written with write to text functions in labview

[DGrayEOS]

I might note in passing that streaming data to an XLSX file is extremely inefficient. XLXS files are essentially a ZIP file containing multiple items, including an XML file containing most of the data. To add anything to it, you need to unzip it, add the item, then zip it back up. I would recommend you stream to something else (TDMS, flat binary, ...), then convert to XLSX when done.

[TeamHalli]

I have struggled with trying to create this spreadsheet in XLSX format. Do you have any pointers on converting TDMS or binary to XLSX in labview.

Thanks,

[DGrayEOS]

XLSX is very poorly supported in LabVIEW, only available through the Express VI. If you can, I would write to a standard tab separated text file, then open in Excel and save as XLSX. If you really need XLSX, I would look up the Excel XLSX format and write it myself. You only need a small subset of the format. Doing it yourself means that you only need to zip it back up when you are actually done, so you can be medium fair efficient. As a last option, you can use the .NET interface to call the Excel assemblies and write it using .NET calls. This will require Excel to be present on the system. Unfortunately, there is no easy solution.

Were I to do this, I would probably write to a tab-separated text spreadsheet file, import into Excel, and apply a template for formatting.

[Gregory]

Use the report generation toolkit. You can send it an Excel file as a template (this can be created an Excel to look just how you like, but leave anywhere that data is supposed to go blank).

[TeamHalli]

DGrayStratasys,

My technicians do not like csv or tab separated files. They are requesting XLSX files.

Greg,

I have seen the Microsoft Excel Report VI but it generates and opens the report as soon as the user clicks run, while I want the report to be created at a click of a button, saved to a directory and keep populating with new data and sit there until the user double clicks to open it. Any idea on this?

Thanks

[Gregory]

Well it all depends on how your program works. I often open an Excel report using "no change" so that it doesn't pop up. If you are generating a lot of data, the best thing would be build the data up in LabVIEW and then write all of it once the user finishes the test. If the tests are long or destructive to the unit, then you will probably want to write to a CSV file during the test so that you don't lose data in case of an error.

[TeamHalli]

Ok, i will look into that.

Thank you.

[Bob_Schor]

---

@TeamHalli wrote:

DGrayStratasys,

My technicians do not like csv or tab separated files. They are requesting XLSX files.

Greg,

I have seen the Microsoft Excel Report VI but it generates and opens the report as soon as the user clicks run, while I want the report to be created at a click of a button, saved to a directory and keep populating with new data and sit there until the user double clicks to open it. Any idea on this?

Thanks

---

This is easy to do with the Report Generation Toolkit.  Assume you are creating a "blank" report, a simple Workbook with a single Worksheet that (for the purpose of this discussion) has data arranged in rows, with the first row having Column Headers and the remaining rows having sets of data that you want to write out, one row at a time.  Let us further assume that either (a) all of the data can be represented by a 1D array of Dbl (if all numeric) or a 1D array of String (where you convert mixed type to String).  Note that this condition can be relaxed (I write rows based on a Cluster having multiple Types, but it takes additional code).

• Excel will stay closed and "out of the picture" until you call New Report, which can be just before you want to write the first row.  This can easily be "at the click of a button".  Furthermore, Excel can be set to run "minimized" so you don't "see" it.
• If you use Excel Easy Table, you can "easily" write one row at a time, with Headers in Row 1 and the data rows in Rows 2, 3, 4, ...   To do this, you need to "Build Array" your 1D array to a 2D array.  Use a "Do Once" Action Engine to handle the writing -- you'll want to write the Header on the first Write, but not subsequent writes (so have a "Header Written?" Case statement wired to an uninitialized Boolean Shift Register, default False, that writes the Header and sets it shift Register to True) and updates the Start location (initialized to (0, 0), also in a Shift Register.
• This doesn't actually save (meaning what in Excel would be a Ctrl-S) the file until you do a Save Report (giving it a Filename).  But if there's a Control you want to Save and Reopen (now with the data "visible") the file, just program it.  To open the file and have the Excel data visible, all you need to do is another New Report, wiring the name of the (now saved) file to the Template input, and open Excel in Normal or Maximized (instead of Minimized) mode.

Bob Schor