Excel easy text VI takes too long to write into one cell

[Akarsh_Dugg]

Excel easy text VI takes too long to write into specified cell. It has to open existing test report and fill into specified cells. I am using Labview 2019 64 bit and 2013 office 86x. Please help me.

[altenbach]

Please attach your VI. We can't really debug truncated tiny stamp sized images.

How long is "long"? (milliseconds? seconds? minutes? etc.). How big is the file? What are your timing requirements? How often do you need to update a certain cell? Do you really need to close and open the report with every operation or could you keep it open?

[Akarsh_Dugg]

To make sure if my program was slower or not, i created another small program attached to see how it runs. This takes one minute to complete. All VIs related to report generation toolkit are slower, right from creating the file. Recently I have upgraded to 2019 LV from 2017 LV, and then installed Office 2013. Please help me know why it is taking longer.

[Bob_Schor]

I have no idea why your program runs so slowly.  Of course, you refer to a few Excel files that you didn't attach, and your code is a little redundant, but if I concentrate on the task of opening Excel and doing the four writes you specify, it takes about 2 seconds, which I don't consider so bad.  Here is my version of your code (as a Snippet):

Bob Schor

[Akarsh_Dugg]

You were right. I ran your code. It took 5 seconds in my computer. I found out that my excel file that I was using caused whole program to slow down. I have attached my excel file. Please look at it and let me know why it is slowing down process.

[Akarsh_Dugg]

You were right. I ran your code. It took 5 seconds in my computer. I found out that my excel file that I was using caused whole program to slow down. I have attached my excel file. Please look at it and let me know why it is slowing down process.

[Bob_Schor]

Thanks for including the data.  I don't have a direct answer for you, but can make some "educated guesses" that are probably "more right than wrong".

Here are some facts that explain some of the "speed issues" you are seeing:

• Excel is a Spreadsheet program made by Microsoft.  It saves its data in a proprietary file format (with the extension .xlsx).
• In order for LabVIEW to write an Excel file, it needs to take data (often 2D arrays) and "translate" them into the Excel File Format so that Excel can read them.
• On the other hand, if LabVIEW is starting from an Excel Template, it needs to, first, read the Excel file, preserving any specific formatting associated with Excel cells, then use this information as the basis for adding the LabVIEW Data into this Template, and then writing out the result.
• The Template you are using is a multi-Sheet template.  It is Good Fortune that you choose to use WorkSheet 1 (the default).  I'm not certain how LabVIEW handles the juggling of multi-Sheet Template, but it clearly slows things down.  My simple example took 46 seconds.
• One other (minor?) complication:  When you created the Template, you created was is essentially a single Cell at location G4, where the first "Hello" goes.  You've already "done the hard part" of creating and formatting the cell, so you don't have to use Excel Easy Text, but can use Excel Easy Table (which, I assume, is what you are using for most of the Report) and just write the Text in each cell.  One "gotcha" is that Excel Easy Table requires that entries be a 2D Array, so take your "Hello" string and pass it through two Build Array Functions to make it a 2D "Hello" Array with a single entry.  The Formatting that you put into the Template will still work (the third Hello is bold, for example).  I thought this would be much faster, but it isn't.  It may even be slower (I forgot to time the Easy Text version, but it was slow ...

Bottom line -- NI provides a nice Tool that understands and can provide useful links into Excel.  This works well when doing "simple" things, but for more complicated LabVIEW/Excel interactions, it may be slow.

One other thing I ran into a few years ago, but can't find a reference for it (and may be "mis-remembering") -- an Excel Worksheet really has a whole lot of Rows and Columns (something like 2**20 rows and 2**14 columns).  I vaguely remember doing something to help LabVIEW and Excel "know" how many columns there are (LabVIEW can figure out the number of rows easily) -- maybe when using your Template, it sees it as "full width".  This might be totally wrong ...

Bob Schor

[altenbach]

---

@Bob_Schor wrote:

Bob Schor

---

Or to simplify code and remove all that code duplication (actually quadruplication :)), just place the subVI in a FOR loop and autoindex on arrays of clusters. Some values can even be derived from [i]. 😄

[Akarsh_Dugg]

Bob,

Thanks for your time. You were really helpful.