There are (at least) two different Questions/Answers here. The first is "What file format do you wish to write?". You use both the term "Spreadsheet" (which NI uses to mean a Text File that conforms to the Comma-Separated-Values, .csv, convention, which Excel can also read and which Excel "hijacks" and gives an Excel-like icon) and "Excel", which I think of as a file in the Microsoft proprietary .xls or .xlsx format, designed to be (natively) read by Microsoft Excel.
Spreadsheet Files can be read and written using the Read Delimited Spreadsheet and Write Delimited Spreadsheet functions in the File I/O Palette (the names of these functions changed around LabVIEW 2014 or so, but the new and old names are very similar). To read/write true Excel files, you need the Report Generation Toolkit or Third Party software.
Now let's tackle the other problem, using an existing "spreadsheet" (whether an Excel or .csv format) that has a Header row and making your output "conform" to what is in the header. This, in turn, has two parts:
- Read the Header Row, parse it, and determine the data and formatting of the remaining data rows.
- Using the parsing information from Step 1, write the data rows starting in Row 2 (or the first blank row, as the situation warrants).
So now you have the task of matching your Cluster to the columns of your spreadsheet. This can, indeed, be automated, but it really helps to think about doing this before you start coding. You can, in principle, get the names of the elements of your data cluster, and then attempt to match them to the elements of your spreadsheet header, but in your case, (a) the cluster isn't "flat" (it contains, for example, a sub-cluster) and the cluster element names don't match the spreadsheet header names.
I recently designed a system for behavioral studies that was controlled using an Excel Workbook with three WorkSheets -- a System Worksheet that contained such things as Room Dimensions (that defined the Geometry of the study, and were effectively "constants"), an Experiment Worksheet that contained values that didn't change during the Experiment (such as the Subject's name, the date, etc.) as well as values from the Trials Worksheet that we wanted to fix, and a Trials Worksheet with 60-150 Trial-specific values that defined what happened on each Trial. The "rule" was that if a value appeared in both the Experiment ("constant for the Experiment") and Trial ("Specific for this Trial") Worksheets, the Trial value was used. In addition, if no entry was found in the Workbook for a value in the Excel Cluster, a default value was used. Note that these choices let us reduce the size of the Trials Worksheet to include only those variables that were important and varying in the particular Experiment, making the resulting Excel Workbook much more compact.
The Excel Cluster was built as single Integer, String, Double, or Array elements. Which came first, the Excel Cluster or the Excel Workbook? You would think the logical (and simpler) answer would be the Cluster, but that would be wrong -- in fact, we designed (and re-designed, and then talked about it with the students who would be using it, and designed it a bit more) the Workbook, then wrote a LabVIEW Program to parse the Workbook and create a Cluster (using LabVIEW Scripting) where the Cluster element names came from the appropriate Row (or Column) header cell in the Worksheet and the Type of the Cluster element came from parsing the content of a Data Cell (if it was all numeric without a decimal, it was an integer, if it had a decimal, it was a dbl, if it had a comma, it was an Array, otherwise it was a String). Needless to say, having such an automated system meant that six months later, when one of the students wanted to add "another feature" and more parameters to the system, it just meant adding some rows and columns to the Master Workbook, reparsing to get a new Excel Cluster, and making the (fortunately minor) changes to the main code to accomodate the new functionality. Everything maintained backward compatibility -- the newer code "did more", but it could also "do less", and having the convention that "if a parameter is not present in the Workbook, then use a default value", and we chose the defaults to mean "Don't use this particular feature".
As you might imagine, a lot of work went into this design, and I'm not going to start posting a lot of code here. This (long) response is meant to say that good Design, complete with writing Documentation first and being willing to "start over" several times, can pay off in the long run. It also (I hope) suggests ways to make your LabVIEW code "conform to changing Design specifications" by doing as much of the Design first before starting any code development.
Bob Schor
