How do I read a formatted excel worksheet as a string array

[viScience]

I am using the report toolkit to read a excel worksheet as a LV string array.  This works fine except that it reads the full precision of numeric cells (~10 digits of precision).

In my workbook I have the displayed precision set to 2.  Is there any way to read the table as it is displayed instead of how it is internally stored?  (BTW, I understand how to do this

manually by parsing the string array and limiting the precision myself but would prefer to use excel itself to determine the precision)

[BTreece]

The reason that it is reading the full precision is because that is what is stored in memory as you have pointed out.  Excel is simply limiting the digits of precision that you see on the screen.  You can do the same in LabVIEW by using a property node.  Right click on a numeric control or indicator and select Create>>Property Node>>Display Format>>Precision.  This will set the number of digits of precision that you will see in LabVIEW.

Brandon Treece

Applications Engineer

National Instruments

[Wayne.C]

sachsm,

This post shows how to format cells in Excel.  Instead of writing the format string using 'NumberFormat' you should be able to read it and convert returned variant to a string.

[viScience]

Actually, what I want is sort of the opposite of what you suggest.  I already have my excel worksheet table formatted the way I want.  The problem is that I cannot read it into a string array with that formating preserved.  For example, lets say that a cell displays the formatted numeric value of 3.14, in actuallity the cell contains the number 3.1415926.  What I would like is to be able to read out the value 3.14, but instead I get 3.1415926.

[RavensFan]

Use ActiveX property nodes to determine what the format of the Excel File is, then use that information to set the format of the indicator in LabVIEW.

[Wayne.C]

sachsm,

You didn't read my whole post.  You can easily modify code that formats an Excel cell to return the format of an Excel cell.

[viScience]

Sorry, I did not understand your suggestion at first...

So then I guess I would have to interpret the format string and then use it to cast each cells data into the correct formatted string and then rebuild the whole thing back into an array of strings to

represent the original table.  I guess that will work, but I was hoping for someone to point out a property that reads a cell's actual text.

[Wayne.C · Accepted solution]

sachsm,

This should allow you to get the actual displayed text from an cell or range of cells.  Kind of doing a 'Paste Special' 'Values'.

[viScience]

Yes, that works exactly as advertised.  It would have been nice if the text property would return a range of cell's like the Value2 property does.

Thanks a million!