How to get data from an existing Excel file using report generation toolkit

[aputman]

Have you tried using the "Excel Get Worksheet.vi" or the "Excel Get Data.vi"?  "Excel Get Worksheet" just needs a sheet number or name.  Right click on "Excel Get Data" and select the type of data you want to return (single cell or a range of cells) and then give the range start and range stop as a cluster (row, column).  The best way to learn to use these functions is to try them out, not wait for someone to write your code for you.  

[Michael.Koppelgaard]

About Excel Get Data.vi
I need to tell it where to look for data. How 🙂

[Michael.Koppelgaard]

Another thing:

If I manages to load all data from a sheet and want to write it back, will I then loos all the formatting and formulas?

[aputman]

[Michael.Koppelgaard]

Okay great 🙂

And here is an example how to use "Excel get worksheet" and "Excel get data"

https://forums.ni.com/t5/Example-Programs/Reading-Excel-Data-Programmatically-Using-the-Report-Gener...

[Bob_Schor]

The Report Generation Toolkit was designed to write Reports, one of which could be an Excel Workbook and Worksheets.  It was not designed to (easily) let you manipulate every last thing that Excel has to offer.  In particular, it basically wants to use Dbl or String data in the cells, and if you give it Dbl, it might diddle with the formatting/precision.

Note that when you write to Excel, you have the ability to format cells in a variety of ways.  For example, I use Excel as a "Control Document" for behavioral Experiments, where each row is a separate Trial and the 100-or-so columns are the parameters for that Trial.  While the Experiment is running, I change the color of the "Active Row" to Red (to help the Experimenter know what Trial is running in the other (darkened) room) and change it back to Black when I move to the next Trial (= Excel Row).

I believe that if you have a cell that has formatting associated with it and you simply write a new value into that cell, the formatting won't change.  I've not used formulas, myself, but I did answer a question on the Forums about a year ago where lots of Formulas were involved (I think I recommended doing the computations in LabVIEW, instead).

Bob Schor

[Michael.Koppelgaard]

Okay 🙂

I tried to see if the formatting changes when reading - changing data - and writing back, and it seems not to change 🙂

One ting does though 😞  When I use “Excel easy table” I get frames around every cell.

Do you know how to avoid this ?

[aputman]

A table in Excel is more than just text.  Try to insert a table in Excel and see what you get.  If you are only concerned with text, use the Excel Easy Text VI.

[Michael.Koppelgaard]

I tried:

And here  is the result

I changed the color of 5 and 6, before running the program and this is unchanged.

BUT the cellcolor is changed from nothing to white and while running the program.
That is not what I wanted 😞

[aputman]

If you open up the block diagram for Excel Easy Text, there is a VI in there called Excel Set Cell Color and Border.  This is found in the Excel Format palette.  I don't know why NI chose to add this formatting and not expose the settings as an input but they did.  The choices you have are:

• add this Excel Set Cell Color and Border into your program to change the colors back to default.
• modify the Excel Easy Text to remove the cell format VI.  Keep in mind that if you choose this option, there is the possibility that it will revert back at some point if you install an update or repair an installation.
• a third option would be to create a copy of the Excel Easy Text, give it a new name, and remove the format VI.