LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

How to replace specific Excel values with Report Generation Toolkit

Solved!
Go to solution

Hello LabVIEW Champions. I am attempting to automate a manual process performed in Excel that updates Sensor offset values based on ambient psi. In the attached spreadsheet, the user enters the present measured ambient psi, the copies the New Offset value to the Old Offset, which then results in the New->New Offset.

The attached .vi performs the following functions:
1. Imports spreadsheet Test Ambi.xlsx with previously entered ambient psi values

2. Indexes Offset New column
3. Places Offset New values into Offset Old column and writes back to Test Ambi.xlsm

4. Displays Updated Test Ambi.xlsx with New Offset Calibration values

The problem is that I don't need to re-calibrate all sensors. I'm trying to be able to select specific sensors and only replace those values. I've added the Listbox example into this .vi and it gives the basic functionality of choosing and assigning an index #, but I don't know how to individually replace those values into a specific cell. The Easy Table.vi only allows a starting point then imposes the rest of the column entirely. It seems like if the Easy Table.vi has the functionality to index both row and column (for a single cell value) then I could use those inputs in a For loop to replace one each iteration. But it only has a start point. 

Not sure if there is another method to programmatically replace just one cell. Or maybe there's a better way to do this altogether. Thank you.

Download All
0 Kudos
Message 1 of 3
(938 Views)
Solution
Accepted by topic author jyingling27

As it happens, I'm basically working on some VIs to more-or-less do exactly what you are talking about.  But it can get a little complicated, as Excel uses (possibly multiple) 2D Arrays of mixed type (at a minimum, Numeric, String, and Boolean) with an "origin" at (1, 1) (Cell A1 is at the upper left) whereas LabVIEW deals with Arrays of a single fixed type (unless you want to count Variants, but I didn't go there) and has an origin at (0, 0).

 

My first Foray into LabVIEW/Excel was more complicated, as the LabVIEW data that was going into the Excel WorkSheet was also of Mixed Type (Integer, Floats, String, and possibly Boolean -- that system hasn't been running for about a decade).  So how do you handle a 2D Data structure where the rows have identical formats (as they are simply "Trials"), but the columns do not (numbers, strings, blank cells, etc.)?  Simple -- the row becomes a Cluster, and the WorkSheet becomes an Array of Cluster.

 

How do you populate your WorkSheet, particularly one that might have a Header Row?  And why is this Row different from all other rows?  Because it is (a) pre-determined, and (b) it is all Text.

 

The secret is you build a WorkSheet that serves as a "Template", to be filled in when you run your program and stuff values into the Cells in the WorkSheet that are not part of the Header Row.  [There could, of course, also be a Header Column, but let's not get too carried away].

 

Look at the Create Report VI.  Right under where you select "Excel" as the type of Report, there's an input for "Template".  This is where you wire a (mostly-blank) "Template" that "fills in Cells and WorkSheets" that you want in the final Report.  All you now have to do is "fill in the blank spaces with your data", and save the result under a different name (to avoid obliterating the Template, which you probably want to reuse over and over).

 

This does make things a little more difficult.  Let's assume that your data are all one type, so that you can create a 2D Array of <something> (maybe you want to use a string, which can represent integers, decimals, TRUE/FALSE for Boolean, or, easier yet, all of your data are Floats).  Now all you have to do is learn to use the Start (0, 0) input to position your data so that it doesn't overwrite the Header (you'd use (1, 0) if you had a 1-line header, for example).

 

But what if it was an Array of Cluster?  A little harder, but still doable.  Write one column at a time (it will be a 1D Array of String or Number, so you'll have to "fake" Boolean by doing something like writing "TRUE" or "FALSE" and saving it as a String).  Since "Easy Table" takes only scalars or 2D Arrays, how do you get a column?  Simple, take your 1D Array and run it through "Build Array".  It is now a "horizontal" (or "Row") Array.  Transpose it, now it's a Column Array.  Write it, starting at (1, 0) (assuming you had a 1-row Header).  Now do the next Column, writing it in (1, 1), then (1, 2), etc.

 

Don't worry, it can easily get more complicated.  But if you take it step by step, and write a lot of small VIs that encapsulate (that's a fancy word that means "hides the messy details") what your are trying to do, this can actually work.

 

Way back in 2014, someone posted a topic on the forums called "Revised "Generate Excel Report" Example" (the outer set of parenthesis is my "naming" the Post -- the inner set are part of the name, go search for it).  It doesn't discuss Templates, nor does it use sub-VIs (because it is trying to show how to use Easy Table to generate various elements of a Report, including even a graph of the data).

 

OK, now that I've rambled on a bit, let me address your question (in light of what I just said, above).

 

You want to modify an existing Report.  Do a Create Report, using your existing Report as the Template.  It will be read in.  Do you want to change a single cell?  Locate its coordinates and use Excel Easy Table.  If it is a single entry, you may have to Build Array twice to make a 2D structure, but LabVIEW will see it is a 1 x 1 "Array" and should only write one entry.  Now set the "Start" cluster to select the correct cell, remembering that LabVIEW puts the first cell at (0, 0), while Excel calls it (1, 1) [you need to use (0, 0)].

 

If you want to maintain fancy formatting, you can (probably) do that, too, but it will be more work.

 

Bob Schor

 

 

Message 2 of 3
(912 Views)
Spoiler

Thank you for the detailed response. I'm not sure if I implemented what you were saying at the end but I gave it my best shot. It does work, although the way I did it seems pretty crude. The largest challenge was figuring out how to change the start coordinates for the Easy Table every iteration. Which I managed, but maybe there is a better way. Here's how this simple VI works:

1. Run and import the spreadsheet to make the Listbox channels visible

2. Select the desired channels from the Listbox + press the "Replace OFFSET" button, which will essentially move column 6 value to column 5 (only the channels that were selected in the Listbox). 

This is actually just the first step to what I need this to do, but if I can place a single entry into a "1 x 1 array", I can pretty much just keep using the same method for the other functions. 

Download All
0 Kudos
Message 3 of 3
(870 Views)