Excel easy table.vi changing font

[Garydp]

I have a string array of data that is generated by a measurement taken from a DMM. I want to populate an excel spreadsheet with data from the array and im using the Excel Easy Table.vi. If the measured data is outside of the limits, before it is entered into the array it is changed so that it has (F) at the end. is it possible to check the array as it is entered into the spreadsheet for any data that has (F) and changing the font for that data to Red and then changing back to black for any data that doesnt have (F)

The other way of doing this that i can think of is searching the spreadsheet after it is populated for (F) and then changing the cell font colour for each cell. I think this is a longer way around though.

Any help would be appreciated.

[IanSh]

You've identified 2 approaches:

1) do it as you go along. This would require modifying the easy table VI somewhat to check each cell value. Looking at it, it wouldn't be straightforward, as currently the report gen functions are fairly high level (appending a whole table rather than cell by cell) so you'd have to dig a few layers and modify something fundamental to report gen toolkit.

2) Change the colours afterwards. I suggest going for this. With your data you can easily get a set of cell indexes of the values out of limits, just put these through a for loop with the "Set cell font" vi, and it's done. Haven't used this before, possibly you'll need to reset the current font afterwards (like what happens in the easy table), but there is also a VI for that.

[Bob_Schor]

Two comments.  We have a pretty large LabVIEW RT Project that involves running behavioral trials asking subjects to localize sound sources in a 1-D or 2-D location in a darkened room.  The Experiment runs using an Excel Workbook with a large Trials table having something like 150 columns to specify the parameters of each trial (the location of the sound, the nature of the sound, whether lights on the speaker or lasers in the room should flash, how many sounds, whether or not the sounds should be generated and played through headphones instead of speakers, etc.), with an Experiment consisting of 50-200 Trials, each Trial represented as a Row of the Trials Worksheet.

During the Experiment, the Worksheet is opened on a second monitor.  As each Trial is being run, I change the color of the Row from Black to Red as a visual aid for the Experimenter -- the Worksheet has the Cell background colored by function -- spatial parameters of the sound are in one color, parameters of lasers and lights are in another, pure timing functions (like when to enable the pushbutton the Subject uses to signal they've decided on the sound location) in a third, etc, so we only change the Text color.  So it is certainly doable to change Font colors using the RGT.

My second comment is to suggest that you not flag values with "F" as the values are generated.  I'm assuming that the data you are saving is numeric, hence is being saved as Dbl.  Adding "F" forces it to become a String, and gets you into the whole conversion to and from Strings.  [Note that the RGT also has a similar behavior -- the data are ultimately saved as Strings, depending on Excel to interpret things correctly.  If I remember correctly, the default format has 3 digits of precision, but I think you can alter this ...].

So when do you need to "flag" values?  Do you need to flag them as they arrive, or can you put this off until you go to write them?  Why do you need to flag them at all?  If the criteria for flagging are fixed, then you can identify Out of Bounds data at any time, say, when you go to use them.  If the criteria vary from Experiment to Experiment, you can put the criteria in the Workbook, either on cells in the WorkSheet or in a separate WorkSheet and let Excel handle flagging.  Finally, if you do want to flag "as you go", you can add a column, maybe called "OOB" (that is both "boo" spelled backwards and Out Of Bounds), and put an "x" in the column if the neighboring value is out of bounds (otherwise leaving the entry blank).  You can also (if you want to keep everything numeric) use -1 as the OOB value, and 0 as OK (probably leaving it blank will cause Excel to value it as 0, but I'm not 100% certain of this ...).

Bob Schor

[Garydp]

Thank you for the replies.

I am taking measurements from a resistor card and I am applying (F) when I check to see if the resistance value is outside of the limits. I am new to labview so that's why I decided to add (F) to show a failure. If there is a way to do this using DBL then I would prefer to do that but I don't have any idea how to at the moment.

The idea being that the measurement table will be the test results for the card being tested.

[Bob_Schor]

Sure

---

@Garydp wrote:

Thank you for the replies.

I am taking measurements from a resistor card and I am applying (F) when I check to see if the resistance value is outside of the limits. I am new to labview so that's why I decided to add (F) to show a failure. If there is a way to do this using DBL then I would prefer to do that but I don't have any idea how to at the moment.

---

You can Have Your Cake and Eat It, Too.  Any OOB value you can save and flag as a Dbl by simply negating it.  Since resistances can't (ordinarily) be negative, this can be an obvious, easily-searchable signal.

For example, if your legal range was 1K to 2K, and you had values of 1K, 1.5K, 2K, 2.5K, and 100, you could save as 1000, 1500, 2000, -2500, and -100.  [You can decide if you want the "limits" to be inclusive or exclusive, i.e. are 1000 and 2000 "in" or "out"].

Bob Schor

[Garydp]

ok so am I right in what you are saying is I can keep my array as DBL and have the following

Channel 1 10, 20, -30, 20, 10 ,5

Channel 2 5, 16, -28, 5, 15

and then use the negative symbol as a way of deciding when I need to change the font colour to red

how can this be done or are you saying to put the minus symbol in front rather than (F)

I have kind of got this working using the Excel Find.vi but the spreadsheet has to be closed to run the search then im opening it changing the font and closing and then repeating. This is taking a long time at present as Ive made a dummy spreadsheet with mostly failures on there. I was hoping that there was an easier way of doing it but with my little knowledge of LabVIEW I don't really know what else will work

[OJwithPulp]

Hey Give this vi a try! 

Your problem got me curious and I decided to go ahead and figure out a work-around. It might not be the cleanest or most efficient solution, but it seems to get the job done.

[Bob_Schor]

Sorry, I've been "otherwise-occupied".  I was not saying that you flag OOB values as negative and then (in a second pass) change the negative cells to Red -- as you note, this is slow and takes a fair amount of time.  I was saying simply write them as negative, and make sure everyone knows that "negative values are Out of Bounds" and won't be used in processing.

I perhaps led you astray by our (somewhat different) scenario, where we use an entire row as "This is the current test".  As a test lasts 10-60 seconds, and all of the Row Values that we are changing from Black to Red and Red to Black are already-written values we are just examining, there is essentially no timing issue (so what if it takes 20 milliseconds to color a row?) for us to worry about.

You are saving data "as you go", and want a "flag" process that is quick and unambiguous.  My suggestion that you prefix the number with the character "hyphen" rather than suffix it with the character "F" has a sneaky "numeric-only" method, namely negate it, which (as long as the number isn't 0) puts the hyphen (which, to LabVIEW, is a minus sign) as a prefix.  [Fact not known to many LabVIEW Programmers -- the hyphen and minus sign are, in fact, different symbols in the World of Type].

Bob Schor

[Garydp]

thanks guys for the reply.

 OJwithPulp that is what i am after but all my measurements are taken and stored in a 2d array. once all measurements are taken i then transfer the data to excel. its when i am send the data from the 2d array to excel that i want to be able to change the font. The vi you posted is for 1d array isnt it?

thanks

Gary

[Bob_Schor]

---

@Garydp wrote:

thanks guys for the reply.

 OJwithPulp that is what i am after but all my measurements are taken and stored in a 2d array. once all measurements are taken i then transfer the data to excel. its when i am send the data from the 2d array to excel that i want to be able to change the font. The vi you posted is for 1d array isnt it?

---

Gary,

     If you take measurements, write everything to Excel (presumably at or near the end of your program), and want to "highlight" certain cells for the purpose of making them "stand out" while a Human is (passively) "looking at the data", might I suggest another approach?

     I haven't done all that much with Excel macros, but I believe it is a fairly simple procedure to write "code in Excel" that will inspect a range (that's an Excel term) of cells and "do something" with cells that meet some criteria.  Thus you could write an Excel macro that would look at your 2D array of points, find those that you have "flagged" (either because you put an "F" after the number, as you originally proposed, or put a hyphen before it, i.e. negated it, as I suggested), and "do something interesting" with its format (change the Cell Background to Black and the Text to White, or change the Cell Background to a medium shade of red, or set the Font to Bold Italics, or whatever appeals to you).  Indeed, you can even use LabVIEW to execute this macro, and (I seem to recall) it is even possible to use LabVIEW to write the macro!  [Note that I don't really advise doing this -- I recall a post on this forum several years ago where someone wrote a bunch of Excel macros to manipulate or otherwise "compute" data stored in an Excel WorkSheet.  The code didn't quite work.  It took me a while to figure out the purpose of the macro, such as "Find the maximum value in this row", and I proposed fixing the code by ignoring the macro, using LabVIEW to read the data, compute the result, and write the answer back into the WorkSheet.]

Bob Schor