Excel Report Generation

[Twilso37]

Hello,

I am trying to streamline an application developed by my predecessor. I have generated a .vi that upon clicking a mechanical switch, will search a directory for an excel file, open it if it exists, create it if it doesn't using a pre-written excel template, and begin plotting data into a specific worksheet depending on the value of a combo box on the front panel. I would like the program to save, and close the excel file when deselecting the "record permeation data" button. My issue is that if I insert the "Save Report to File" and "Dispose Report" nodes into my case structure hierarchy, the program saves the file and closes it on every iteration of the while loop. I have been unable to find a way to move these nodes outside the while loop while still managing to write data to the excel file. 

I have attached a .vi with the serial inputs replaced with a generic sine function so that I can work on this remotely. I have also attached the Excel template I am writing the data to. Hopefully I haven't left out any critical information. 

Disclosure: I am very new to LabView, and my code is probably the least elegant you've ever seen. I'm also a Chemist by training so this is my first foray into any sort of programming. Please forgive my ignorance. 

Thank you!

[Michael_Munroe]

There are two options.  If you will always write data at some point then just open the Excel file before the loop and save it after the loop.  If you prefer to run the program with or without writing the data, then you should create a simple state machine inside the loop.  When the button is pushed then combine the copy template with the file open, selected worksheet and find starting cell functions.  The next state will append the data.  When the button is turned off then write the file and close Excel.

Issues with your code:

1. Get last row in Excel will not work correctly if there are other cells used in the sheet

2. Your waveform calculates the dt value incorrectly.  This value should be consistent.

[Bob_Schor]

I recommend you write a one-page document describing what you want to do.  In the case of this particular Report-from-a-Template, describe what you expect (in broad strokes) a typical Data Run to look like.

• Where do the data being put into the Template come from?
• How are they formatted?
• Do you have multiple files for multiple gasses?  Are they all Time, Downstream, and Upstream?  Are the number of points the same for each data file?

It looks (to me) like this may be an interesting two-pass process, involving both LabVIEW and Excel in creative ways.  I'm guessing that your Document will say something like this:

• I'm not sure what the data really look like, but I envision a bunch of files with measurements of gasses, organized by Gas and containing triples of values (no idea of the format of these files, but for this high level, it doesn't matter).
• Step 1 -- Open (empty, blank) Template.
• Step 2 -- Populate the "Gas" Sheets by adding the triplet data from the Gas files into Columns A-C, Rows 1 to whatever.
• Step 3 -- When all the data are in place, make sure that the Graphs are populated and the Formulas are evaluated.
• Step 4 -- Populate Sheet 1 from the other Sheets (not sure if you will have pre-loaded the cells with appropriate formulae from the Gas Sheets, or whether you'll want to populate cells L20-L35 "by hand" (read cell/write cell), but you'll put that into the Document.

We've now broken a Big Complicated Problem into a sequence of four simpler Steps, each of which is either a sub-VI (which you string in a simple sequence) or a "State" of a State Machine model.

You should be able to do all of these things in a fairly straight-forward manner with the Report Generation Toolkit.  There are some interesting examples out there (including one I especially like, right here in the Forums, search for Revised Example.

Bob Schor

[Twilso37]

Thanks for the thoughtful reply, Bob!

I work on gas separation membranes and our lab utilizes some rather "homegrown" instrumentation. A lot of work has been done building and validating the instruments, but our data acquisition is not as streamlined as I would like to see. My predecessors essentially adopted a hand-me-down LabVIEW program from another lab, modified the VISA serial resources to fit our instruments and called it a day.

To answer your questions:

1. We currently have an operational program that will acquire the data, and for all intents and purposes it works. I would just like to automate the data reporting and work-up as much as I can. That's where plotting the data directly into a spreadsheet template comes into play
2. Every single file will ideally involve the testing of all 5 gasses on a single membrane material. The sample name identifies the material. 
3. All experiments will involve the same three parameters (upstream pressure, downstream pressure, and time), but the number of data points can change drastically from run to run depending on how permeable the material is. 

To outline a given run:

• For an experiment I will load a membrane sample into a permeation cell, and apply a fixed upstream pressure (above the membrane), apply static vacuum downstream (below the membrane), and begin running the lab-view program.
• The nature of gaseous diffusion through a membrane is not instantaneous and we must wait to reach "steady state permeation" before recording data. So it is important that I can monitor the process real time before recording any data to the excel sheet. 
• Once steady state has been achieved, the record button is pressed and the triplicate values (time, downstream pressure, and upstream pressure) are recorded at regular intervals set by the timed loop. 
• It is important that LabVIEW records the data into the appropriate sheet in the workbook depending on the gas being tested. My current solution is to reference the combo box on the front panel into the report generation toolkit nodes. This seems to work well.
• Once sufficient data has been recorded (approximately 5-10 minutes) the record button is pressed again, and the excel file is saved and closed. 
• The excel template that I attached is designed to automatically calculate the flux of gas through the membrane and into the downstream (torr/sec) the thickness and surface area of the membrane are normalized out to yield a permeability of a given gas. 

The old process and subsequent data work-up involved generating a spreadsheet for each individual gas (often 5 gasses for a single membrane), generating a scatter plot of downstream pressure vs. time, fitting a regression curve to the data, changing the equation to scientific notation w/ 5 sig figs, directly copying the slope from the graph equation and pasting that into a master spreadsheet. I work with guys who worked the data up this way their entire graduate school careers, and I am just trying to make my life a bit easier. 

Based on an earlier suggestion, I have started looking into building a state machine. It seems like it will work much better than my original code, but I am finding it difficult to get right. Limited programming experience makes this process more difficult than it should be. Hopefully I can get it figured out! 

Thanks again for your reply!