Excel Report Generation - reduce Rows

[Mrtechie]

Hello all, 

 I have attached  Excel sheet which comprises of Several Rows and columns. In that, The header named S.No, Test File Name, Test time,  Barcode are extended upto 4 rows . Now i need to fix this headers into single row ( 1 - A,  1 - B, 1 - C,   1 - D , And so on  ) . post your views

[Bob_Schor]

The Simplest Answer is to do the following:

• Double-click the file "Excel.xlsx".  This will open Excel and show you the file.
• Make the changes you want, combining Rows 1-4 into a single row.  It will be interesting to see what you use for Columns E through AA.
• Save the resulting file using the same or a different name.
• Close Excel.

"Ah", I hear you say, "but I want to do this with LabVIEW".  "Why?", I ask.  "LabVIEW's Report Generation Toolkit was designed to generate Reports, not to run Excel ...".

But you can still "Have Your Cake and Eat It, Too.".  Follow the steps I outlined above, but save only the first (Header) row.  Call the resulting file "Excel Template.xlsx".  Now do the following:

1. Do a New Report, specifying "Excel.xlsx", the Excel file with the 4-line header, as the Template File.
2. Use the RGT to read in the data rows (use (4, 0) as the Start coordinates, that is, the fifth row, first column).
3. Do a Dispose Report to close Excel.
4. Wait at least 100 msec!  This is important, and (as far as I know) undocumented.
5. Do another New Report, specifying "Excel Template.xlsx", the Template file that only has the new 1-line Header you created earlier.
6. Use one of the RGT's Write Table functions to write the data you obtained in Step 2.
7. Do a Save Report to File, specifying the desired output Filename (use something other than "Excel Template.xlsx").
8. Do a Dispose Report to close Excel.

Now, if you been paying attention, you might realize that in Step 6, you could, in fact, write the data along with a single Column-header Row without needing a Template!  But still, the exercise of using Excel to create the Header Row (and figuring out how to "make it look nice" is still a useful exercise.

So how to do it all at once?  I leave that as an Exercise for the Reader.  It can be done by a small modification of the steps in the previous paragraph.  Hmm, turns out it is not as simple as I thought, as the RGT does not include a "Delete" function without diving into the mysteries and arcana of ActiveX.  Sorry.

So my recommendation?  If you are given an existing Excel Report and want to just modify the Header, use Excel.  If you have many Excel Reports and want to use LabVIEW to automate a massive "Replace the Headers", build a Template and use the multi-step procedure I described above.  If you want to "do it all with LabVIEW" (for some reason), dive into ActiveX (but don't say I didn't warn you ...).

Bob Schor

[Mrtechie]

 RGT to read in the data rows ?

To read means which function i have to use ?? i have tried with the Excel get data . 

or shall i use a property and invoke node to delete the rows  or cells? ( attached image  )

(use (4, 0) as the Start coordinates ?

i used  (4, 0 ) as coordinates and  Not get a data.it is not reading as per our requirement.   The default is (-1,-1 ) 

[Bob_Schor]

Sorry, I made the mistake of "doing what I remember doing", and failed to test my own suggestion ("Pride Goeth Before the Fall").  If I had, I would have reminded myself that NI made the (foolish, in my humble opinion) choice that specifying the Start cell without specifying the End cell didn't mean "Start here and take everything else", but "Start here and end here".

There is another anomaly, but this time it appears to be in the way Excel calculates how many rows are in your WorkBook.  It seems to think you have 2000 rows (I count only 39), and 27 columns (the column count, at least, is correct ...).  So my Step 2 should say "Start at (4, 0) and End at (38, 26).  Note that for your Excel Example, the Get Last Row function returns 2000 rows (I'm not sure why this is so -- when I create an Excel Workbook and put 39 rows of data in it, LabVIEW says it has 39 rows, or at least I think it does ...).

Bob Schor

[Bob_Schor]

---

@Bob_Schor wrote:

So my Step 2 should say "Start at (4, 0) and End at (38, 26).  Note that for your Excel Example, the Get Last Row function returns 2000 rows (I'm not sure why this is so -- when I create an Excel Workbook and put 39 rows of data in it, LabVIEW says it has 39 rows, or at least I think it does ...).

Bob Schor

---

Well, I just created an Excel WorkSheet with 39 rows and only 20 columns (I'm running Excel 2013, which might well make a difference -- Microsoft changed some things that "break" the RGT with Office 2016), and the Get Last Row function returns 39.

Bob Schor

[ben64]

---

Bob_Schor a écrit :

---

@Bob_Schor wrote:

So my Step 2 should say "Start at (4, 0) and End at (38, 26).  Note that for your Excel Example, the Get Last Row function returns 2000 rows (I'm not sure why this is so -- when I create an Excel Workbook and put 39 rows of data in it, LabVIEW says it has 39 rows, or at least I think it does ...).

Bob Schor

---

Well, I just created an Excel WorkSheet with 39 rows and only 20 columns (I'm running Excel 2013, which might well make a difference -- Microsoft changed some things that "break" the RGT with Office 2016), and the Get Last Row function returns 39.

Bob Schor

---

Bob, the Get Last Row function returns 2000 rows because these rows were once selected and borders were added. This vi (and also Get Data) are using the _Worksheet.UsedRange property  that factors in such things and formatted cells with no data and other things that can give you unexpected results. If you add a border to cell A50, save the worksheet, remove the border and resave it the next time you use Get Last Row it will return 50.

Ben64

[ben64]

---

bhuvanesh1191 a écrit :

Hello all, 

 I have attached  Excel sheet which comprises of Several Rows and columns. In that, The header named S.No, Test File Name, Test time,  Barcode are extended upto 4 rows . Now i need to fix this headers into single row ( 1 - A,  1 - B, 1 - C,   1 - D , And so on  ) . post your views

---

Can you show us what is your expected results, the header with the first 2-3 rows. What have you tried so far?

Ben64

[Bob_Schor]

---

ben64 wrote: 

Bob, the Get Last Row function returns 2000 rows because these rows were once selected and borders were added. This vi (and also Get Data) are using the _Worksheet.UsedRange property  that factors in such things and formatted cells with no data and other things that can give you unexpected results. If you add a border to cell A50, save the worksheet, remove the border and resave it the next time you use Get Last Row it will return 50.

Ben64

---

Aha!  A "User Data Error".  I used to say "Answer Analysis Reveals that the Question is Wrong", but lately it seems my motto should be "Answer (or Data) Analysis Reveals that the (User's) Data is Wrong".

First rule of Data Analysis -- Make Sure Your Data Are Clean!!

Bob Schor

[ben64]

You can "easily" filter data using the Range.SpecialCells method. In this case the xlCellTypeConstants type works well and the following vi will return only the first 39 rows.

Ben64

[Bob_Schor]

Cool!  I tend to stay away from ActiveX (although I have "descended into its murky depths" for doing some basic things that the RGT doesn't quite support), but hadn't learned of this trick.  I put a suggestion on the Idea Exchange to include a "Get Last Column" function -- maybe you should comment there (or put in your own Idea Exchange suggestion), particularly if your method can handle both Rows and Columns, and won't be "fooled" by whatever caused the OP's Excel file to report 2000 rows when there were only 39.

Bob Schor