Report Generation from excel

[MS06]

I need to generate a 2-page PDF report in LabVIEW using an Excel template.

• Each page must have a different page size

  • Page 1 → A4

  • Page 2 → Letter

• The report data comes from LabVIEW and is written into Excel.

• The final output should be one single PDF file with two pages.

• I am using 32-bit LabVIEW with 64-bit Excel.

• ActiveX is not allowed / preferred.

I am looking for the recommended LabVIEW approach, best practices, or toolkits to implement this reliably.

[zou]

On Windows, you must use ActiveX to write to Excel.

[Kyle97330]

We've had endless troubles with Excel in LabVIEW, especially when trying to do anything very complicated.  We also couldn't find a way other than ActiveX/COM to interact with it.

In the end we no longer attempt to save directly to Excel in any new code, and whenever we rev up old code we try to see if there's a way to remove it.  Alternate options include:

• Saving data as a CSV or other file, then using standard Excel functionality (macros or the "Get Data -> From file" selection on the Data ribbon)
• Creating a 2D string array that is then changed to a single string delimited by tabs, and copying that string to the clipboard programmatically for pasting into Excel (which would have a special sheet for the data to be pasted into that all other sheets would link to)
  • This includes a "re-copy" button in LabVIEW that re-runs the code that populates the clipboard, either from memory or from loading from a file.  This is mostly just in case someone accidentally copies something else into the clipboard (and either doesn't have or doesn't know how to use clipboard history)
• LabVIEW saves to a local database, and Excel pulls from that database.
  • This is the most robust and reliable method of the options, but also the most work to set up.

[Bob_Schor]

My 2014 Revised "Generate Excel Report" Example will help you generate an Excel report using the Report Generation Toolkit which may or may not be present in your version of LabVIEW (it is, I believe, present in the Academic version of LabVIEW, possibly Community Edition, or as a separate licensed Toolkit).

That, of course, only gets it into Excel.  Generating a two page Report where you change the paper size setting between Page 1 and Page 2 is a trick that I've not considered.  I'm also not sure about the PDF formatting (I'm currently away from my Desktop machine with the code and example).

Bob Schor

[Quiztus2]

C# / VB.NET Excel component - GemBox.Spreadsheet for .xlsx->.pdf

pettaa123/Open-Xml-LabVIEW: Read and write Excel's .xlsx files LV->.xlsx 

[RTSLVU]

The XLR8 toolkit can read and write XLXS files without using ActiveX or needing Excel installed on the target machine. 

Exaprom PDF or just using Windows built in "print to PDF" are two ways to make a PDF in LabVIEW

[altenbach]

---

@RTSLVU wrote:

Exaprom PDF or just using Windows built in "print to PDF" are two ways to make a PDF in LabVIEW

---

I am curious how to solve the fact that tow two pages should be different paper size (Letter, vs A4). I doubt that print to pdf allows you to change paper size mid-stream.

[zou]

---

@altenbach wrote:

---

@RTSLVU wrote:

Exaprom PDF or just using Windows built in "print to PDF" are two ways to make a PDF in LabVIEW

---

I am curious how to solve the fact that tow two pages should be different paper size (Letter, vs A4). I doubt that print to pdf allows you to change paper size mid-stream.

---

It's easy to test this manually since OP is using an Excel template.

[RTSLVU]

---

@altenbach wrote:

---

@RTSLVU wrote:

Exaprom PDF or just using Windows built in "print to PDF" are two ways to make a PDF in LabVIEW

---

I am curious how to solve the fact that tow two pages should be different paper size (Letter, vs A4). I doubt that print to pdf allows you to change paper size mid-stream.

---

I honestly don't know. I just mentioned it because Windows built-in print to pdf function is often overlooked.

[Bob_Schor]

---

@altenbach wrote:
I am curious how to solve the fact that tow two pages should be different paper size (Letter, vs A4). I doubt that print to pdf allows you to change paper size mid-stream.

---

I believe it can be done, but it does seem like a "killer" requirement.  Here's my thinking, but I haven't tried it, nor done the "deep dive" into Excel to verify if this is possible.

The first problem is printing the first page on A4 paper, the second on Letter.  Two ways to do that:

• Use a printer with at least two paper trays.  Tell Excel to print Page 1 on the A4 tray, page 2 on the Letter tray.
• Print both pages, one at a time, using the Manual Feed tray (and adjust the width to A4, then Letter).

But how do you get Excel to format the pages differently?  It's been years since I've used Excel for much, but I recall writing documents with Excel inserts (two pages) and vaguely remember I could adjust the format for the two pages to give them different (and specific!) page numbers and different orientations (Landscape vs Portrait).  I didn't have the odd-ball requirement to change the paper size (the instructions for the application weren't that wacky) but I suspect that ability was probably available in Excel.  Then all you have to do is see if the Report Generation Toolkit will allow you to programmatically make these detailed per-page changes.

Bob Schor