Spreadsheet report for myDAQ

[Omershah]

Hello all,

I am doing a project to prepare a testbench for NI myDAQ. At the end of checking all the functions i have to make a report. I also programmed a VI for report in word and excel format.

My question is: Is it possible i make the same report using spreadsheet VI? If yes then please guide me through it.

I have attached the excel and word formats and the VI for it.

Please help in this regards.

Regards

Omer

[Bob_Schor]

I'm not sure what you mean by "make the report using Spreadsheet VI".  If you mean write a report using LabVIEW's Write to Spreadsheet File, you could certainly do that, but I can almost guarantee you won't like the results.  These are pure text files, where each "row" is represented by a single line of text (human-readable character strings, separated by <End of Line> characters), with the columns separated by a "separator character", <tab> by default, but <comma> is also widely used, in which case such files are often called "Comma-Separated Variables" (or .csv) files.  In particular, there is no attempt for the columns to be "lined up" (although if you use <tab> separators and have <tab stops> set appropriately, it might look neat).

When you are writing a Report, you have two choices of writing methods -- you can generate all of the data first, then write the report "all-at-once" at the end, or you can write the report as the data are being generated, line-by-line.  There are advantages and disadvantages to both methods, but they have different program structures.

• Write Report after generating all the data:  Generate your data, perhaps using a While or For loop to generate individual data (row) entries.  When all the data have been generated, do New Report, Write Table, Save Report to File, Dispose Report (I'm describing an Excel Report -- Word would be similar).
• Write Report "line by line":  Before entering the loop that generates the Data, New Report (and bring the Report Object in on a Shift Register).  Within the Loop, generate a row (or rows), Write Table, and loop.  On exiting the Loop, Save Report to File, Dispose Report.  Note that Excel Easy Table has one "Start" input to define the upper-left corner of the 2D area the 2D data will occupy, and two "Next" outputs, one if the next Write is supposed to be in the next row (so it aligns with the bottom left area from the just-written region) or next column (so it aligns with the top-right).  Bring the appropriate "Next" cluster to a Shift Register, and wire the corresponding input to the Start input of Easy Table.
• What do you do if you don't want to write a 2D block of data, but Easy Table requires a 2D block?  Suppose you want to write a row, and have a 1D array.  Run it through Build Array, which gives you a 2D N-by-1 block.  A single column?  Build Array + Transpose 2D Array (a 2D 1-by-N block).  A single cell? Build Array + Build Array (a 2D 1-by-1 block).

Your code seems to be operating in "line-by-line" mode, but you are opening and closing the file every loop iteration.  Move those functions outside the loop if you want to do line-by-line.

Bob Schor

[Omershah]

@Bob_Schor

Yes i mean 'make report using spreadsheet VI'. I am not that good in LabVIEW programming and it is difficult for me in understanding your opinion. If you can show me some figures regarding your opinions it would be great. IT would be really helpfull if you can tell where can i make changes and how i will add this table and everything to a .csv file using the spreadsheet VI.  

´Thanks in advance.

Regards

Omer

[Bob_Schor]

OK, Omer.  I took the Excel Report you attached in your original report, opened it in Excel, and saved it as a text file, which is what Write Spreadsheet File would prepare.  Excel uses the Comma-separated-variable format, with row entries separated by commas, and gives the file a .csv extension (entirely appropriate), which it "conveniently" assigns to itself, tricking you into thinking it is an "Excel" file instead of a Text file.

So I saved it as Excel_report.txt, and have attached it here.  Go ahead and open it, and decide if this is what you want to be creating.  Note that if you do change the extention back to .csv, its Icon will change to "look like" it is an Excel file, Excel will interpret the commas as column separators, and you will be able to do Excel-like things with the cells.  If this is what you want, then you can use Write Spreadsheet File ...

Bob Schor

[Omershah]

No definetly not. I created file like this with the help of write text file.

I want to create the file like attached with the help of spreadsheet Vi if possible with appropriate columns and everything.

[Bob_Schor]

I apologize for not making myself clear.  The text file that I attached is precisely the kind of file that Write Spreadsheet File produces.  Do this experiment -- copy the text file, but name the copy Test.csv.  Note that this in no way changes the contents of the file.  Look at this file -- does it have an icon that "looks like" Excel?  Double click it -- did Excel open it?  Does it look like something you'd like to have?

Excel can read .csv files and can manipulate their contents, but the files remain pure text.  For example, if you attempt to enter a Formula into the CSV-opened-in-Excel file and then Export it to CSV, you will get a message that you will lose some functionality if you do this.

To show this, copy Excel_report.txt to Excel_report.csv, open it in Excel, and in Cell H6 put a formula that sums the preceding 6 cells (put non-zero numbers in to convince yourself that it is doing something).  Export it to a .csv file, accept the "loss of functionality" warning.  Now copy it back to a .txt file and open with Notepad.  You'll see two differences -- first, the formula will be gone, but its result will be displayed at the end of the ninth line.  Second, and more subtle, is that there are additional commas added, as the new Report is 8 columns wide, so each line requires 7 "separating" columns (the original only had 6).

If what you want to do is to output a 2D array of values as text, with columns separated by commas (no extra spaces) or some other character of your choosing, and rows separated by End-of-Lines, knowing that such a file, if given the extension .csv, can be opened by Excel and look like an Excel Workbook (although it isn't), then Write Spreadsheet File does a lot of the hard work of inserting commas and EOLs and formatting as text for you.  You can also write this code yourself and produce identical results.

Bob Schor

[Omershah]

Hello Bob,

i tried with Excel_report.txt to Excel_report.csv and there were also commas in excel files and yes it was openend with excel. But this i do not need. I need a report exactle like i made with a table using spread sheet VIs. if you have some information and guide me with the steps or cam forward me some links from where i can get an idea it would be very helpful. Because i searched alot but i can not find any exact idea.

Kind Regards

Omer

[Bob_Schor]

---

@Omershah wrote:

Hello Bob,

i tried with Excel_report.txt to Excel_report.csv and there were also commas in excel files and yes it was openend with excel. But this i do not need. I need a report exactle like i made with a table using spread sheet VIs. if you have some information and guide me with the steps or cam forward me some links from where i can get an idea it would be very helpful. Because i searched alot but i can not find any exact idea.

---

The Excel report that you attached to your original Post is a true Excel (.xlsx) file, which you can create in LabVIEW using the Report Generation Toolkit.  The Write-to-Spreadsheet functions create text files that can be opened by Excel as rows and columns, but do not have the variable formatting and other features that you probably want to have.  There are numerous links on how to use the Report Generation Toolkit to generate Reports such as you seem to want (to see one, open the Forum, do a search on "Revised " (that is, type the word Revised, then a space) and choose the first "hit" -- this will take you to an older post of mine where I illustrate generating a "mixed Report" in Excel using the Report Generation Toolkit.

Of course, if you don't have the RGT, this won't work for you ...

Bob Schor