LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

Please suggest how to speed up data write into Excel report from template

Hello everyone,

I've been struggling to speed up a part of my code which is supposed to insert data in my Excel report template I prepared beforehand.

My complaint is that it takes way too long to insert only 20 string values.

What i tried:

1) appending data by means of Excel Easy Text VI. This one is the slowest and it seams to be quite reasonable to me.

2) appending data by means of Excel Easy Table VI. This one is faster but it still takes a relatively long time.

3) As I have really positive experience (in terms of data appending speed) with MS Office report express VI, I dived into it and fished out ex_rgt_append anything to report.vi that, I hoped, would help me speed things up. And it did actually, but I still think that it is not fast enough. I would continue to use Express VI because it took only 1 to 2 seconds to fill in the data for my previous tasks but now my requirements are beyond Express VI capabilities so I have to look for a decent solution among RGT options.

 

First of all I'd be happy if anyone could try to run an example VI I prepared along with report template and measure aprox time it takes to run and fill in the data. In my case it's 10 sec aprox starting from the moment I push RUN button that I consider as extra-long.

 

And I know that I can optimise the process by appending data in groups (arrays, tables, so on) but, the way I see it, this would require to rearrange my report template and I'd preffer to keep it as it is.

What I can't really understand is why it's so fast with Express VI. I suspect that one of the reasons is that Express VI doesn't mess up with cells formatting. It just inserts data in template cells I pre-formatted according to my requirements. Every VI I tried from RGT is trying to change the font or something else. I hate this.

 

As for what I'm trting to do, it's a trivial task, I believe. It's a relatevely simple process of automated calibration procedure - for instance, calibration of DMM with report to be saved as Excel file from existing template. This created report is a subject for further processing by a different piece of software that reads data from 'named cells'. So the same 'cell names' are used in my code and in a code of that different soft.

So this code I attach is only a part of the VI intended for the whole calibration process. At the same time it's the only part that slows the cacibration process in general. I'm totally satisfied with the speed of the rest of the code.

 

I have LV 2020, MS Office 2016, Windows 10.

I attach my test VI and template. Any help is appreciated. Thanks!

Download All
0 Kudos
Message 1 of 21
(466 Views)

Hello everyone,

I've been struggling to speed up a part of my code which is supposed to insert data in my Excel report template I prepared beforehand. Ended up here.

My complaint is that it takes way too long to insert 20 string values.

What i tried:

1) appending data by means of Excel Easy Text VI. This one is the slowest and it seams to be quite reasonable to me.

2) appending data by means of Excel Easy Table VI. This one is faster but it still takes a relatively long time.

3) As I have really positive experience (in terms of data appending speed) with MS Office report express VI, I dived into it and fished out ex_rgt_append anything to report.vi that, I hoped, would help me speed things up. And it did actually, but I still think that it is not fast enough. I would continue to use Express VI because it took only 1 to 2 seconds to fill in the data for my previous tasks but now my requirements are beyond Express VI capabilities so I have to look for a decent solution among RGT options.

 

First of all I'd be happy if anyone could try to run an example VI I prepared along with report template and measure aprox time it takes to run and fill in the data. In my case it's 10 sec aprox starting from the moment I push RUN button that I consider as extra-long.

 

And I know that I can optimise the process by appending data in groups (arrays, tables, so on) but, the way I see it, this would require to rearrange my report template and I'd preffer to keep it as it is.

What I can't really understand is why it's so fast with Express VI. I suspect that one of the reasons is that Express VI doesn't mess up with cells formatting. It just inserts data in template cells I pre-formatted according to my requirements. Every VI I tried from RGT is trying to change the font or something else. I hate this.

I have LV 2020, MS Office 2016, Windows 10.

So I attach my test VI and template. Any help is appreciated. Thanks!

Download All
0 Kudos
Message 2 of 21
(499 Views)

Hello, Alexander.

 

     It would be much more helpful to other members of this Forum (and, ultimately, to you) if you didn't attach your "Me, too!" post to a thread that started in 2007!

 

     When you do start a new thread, I would suggest a title something like "Please suggest how to speed up producing an Excel "Report" from a Template".

 

     I looked at your example, and cannot figure out what you want to do.  I ran your code, and it did not make a "sensible" report.  About two decades ago, I posted in this Forum a revised Excel example that generated a report, with tables and data, without using a Template (the Report sort of "generated the template" at the same time).  But it would be trivial (I think) to modify this example to use a Template file.

 

     Think about what you want to do (and, a little bit, about "why", as in "Why Excel?").  You may want to also consider multi-"page" Reports, where each "page" is a separate WorkSheet summarizing the data from individual Units, with a summary WorkSheet at the end expressing, say, the average across the individual data.

 

     If you do "submit your own request", I'll try to make helpful suggestions.  I have nothing to add, however, to a Post from 2007.

 

Bob Schor

0 Kudos
Message 3 of 21
(479 Views)

@AlexanderU wrote:

Hello everyone,

I've been struggling to speed up a part of my code which is supposed to insert data in my Excel report template


One of the fastest way to insert data into template is "direct write" into OpenXML. In general your *.xlsx file is just zip file contains some *.xml files inside. So, if you will rename it to zip, then you will see whole internal structure and you will locate your placeholders, and all what you needed is just replace portions of the data, then pack it back to zip container and rename to xlsx. Well, it is amount of work, but the advantage is that you will not touch Excel (moreover, you don't need to have Excel installed on the target machine at all).

Attached a very simple example how to drop 2D table into Excel file directly, may be you can follow this way:

excel_demo.png

Message 4 of 21
(383 Views)

If you don't want to go down the XML route, all you have to do (likely) is just turn off screen updating in Excel. It'll be under the Application Class within that Excel Ref you have. Just turn it back on when you are done with a True constant.

 

Below is an example with using ActiveX Automation Open & the Get Excel ActiveX References from the Report Generation - Excel Specific - Excel Advanced - Get Excel ActiveX References. Both will work and will likely speed up entering data into your report.

 

Eric1977_1-1708973290821.png

 

 

 

 

Message 5 of 21
(371 Views)

Hello Andrey,

thank you for your reply. It seems to be an interestiong approach (with .xlsx to .zip to .xml) but the thing is I'm not a pro and I just started to make my first steps in LabView. I'm afraid so far it's a little compicated way for me to follow 😔

But I saved your example so this option is available for implementation in the projects to come.

 

Right now I'm trying to apply standard LabView functions to get the results I need.

Appreciate your involvement though.

0 Kudos
Message 6 of 21
(312 Views)

Hello Eric,

I think your suggestion about Excel screen updating slowing the process down might be right.

 

I tried to create the code from your sketch and I couldn't make it. That's what I get:

 

AlexanderU_0-1709059057044.png

When Excel._Application output is connected to Property node ref.num the class goes blank.

Now I suspect that there's something missing in my MS Office package installed on my PC.

0 Kudos
Message 7 of 21
(308 Views)

Hello, Alexander.

 

I think I may have a partial "solution" to your problem.  However, you'll need to do a little work with Excel to create a Workbook (with, I presume, a single Worksheet that, when printed, produces the report you want in the format you desire).  Let me take the steps one at a time.

 

The first thing I did was to print out your "Template".  It wasn't clear to me if you wanted a three-page "Landscape" report or a two-page "Portrait" report.  I chose the latter as it could fit on a single double-sided page, and seemed to "space out" nicely.

 

Excel works well when all the columns have the same spacing.  Your "Template" does not have that property.  Getting Excel to change column widths after, say, 92 rows to "fit a table" is painful.  Try to design your report so that all the column widths (including the table from Rows 94 to 100 are used for all the rows of the Report, as it will greatly simplify the Report "design".

 

Once you have the Template designed, I'm going to suggest you write your report without using a Template (don't panic, I'm about to show you how to do this).

 

Ten years ago (well, 10 years and one month ago), I posted a "Revised "Generate Excel Report" Example" in the Forums.  [You can find it by going to Google and starting to type "Revised "Generate Excel ... and it should pop up -- if it doesn't, try using the Search Bar in the Forums].  I'm about to post a very-slightly revised version here, so you won't even have to look for it -- the only change is that the RGT now uses "Create Report" instead of "New Report" used in 2014.

 

When you look at the Front Panel of this Report, you see a place to put certain entries.  Within the Report, I generate some fake data and list it in a table, and include it in an Excel Graph (you don't have to be this fancy).  I also show how to do some formatting, and to put data in the rows and columns where you want them.  Finally, I close the Report, having given it a name.  It took 4 seconds from start to finish, including opening and closing Excel.

 

I'm going to provide it as a LabVIEW 2021 Snippet, and will also the LabVIEW 2021 version of the VI.  You should be able to program this up yourself in almost any version of LabVIEW, as everything is visible and commented.

Revised Excel Demo 2021.png

 

Bob Schor

 

Message 8 of 21
(296 Views)

Curiously, I think I found a bug while playing with my old demo.  To try to tweak a little more speed out of it, I tried running Excel "minimized".  Everything went well until time to plot the Graph, then it threw a what-I-think-is a "random" error, and quit!  I may try to track this down, and maybe file a Bug Report ...

 

Bob Schor

Message 9 of 21
(292 Views)

@AlexanderU wrote:

Hello Eric,

I think your suggestion about Excel screen updating slowing the process down might be right.

 

I tried to create the code from your sketch and I couldn't make it. That's what I get:

 

AlexanderU_0-1709059057044.png

When Excel._Application output is connected to Property node ref.num the class goes blank.

Now I suspect that there's something missing in my MS Office package installed on my PC.



I got that as well. Back when I observed this, I don't recall placing a CAR for it unless they resolved it in a later version (I'm running 2021 32-bit). I think it's the way LabVIEW tries to find the Excel EXE. You'll have to manually search for it and save it. Start at C:\Program Files\Microsoft Office.

 

Take Bob's advice when you can. He one of the many brilliant minds here and has helped me with my projects...even non-LabVIEW ones I have ported over into other languages.

 

0 Kudos
Message 10 of 21
(289 Views)