append table to excel report without changing cell format

[kaero]

I need to write values into the existing excel spreadsheet. The spreadsheet is formatted to our needs, i.e. changed fonts, font sizes, text color, cell background color, number display format etc. I tried to use "Append Table to Report.vi or Excel Easy Table.vi" from the Report Generation Toolkit. However, these vis changes some aspect of the cell formatting.

How to reproduce:

Create empty excel spreadsheet, change font and font size of cells, change width of columns and height of rows. Create vi to write simple table of numbers into the existing excel spreadsheet. The format of cells of resulted spreadsheet is different from the original.

[Daniel_Fulop]

Hi kaero,

I spent some time testing the behavior of the VIs you mentioned and found the following.

Format settings unchanged by the VIs:

• font, bold/italic/underlined,
• text and background color,
• numeric format,
• text alignment.

Format settings changed by the VIs:

• font size,
• cell dimensions.

I suggest the following workarounds. To retain font sizes set in your template create a cluster constant at the Font terminal of the Easy Excel Table VI, and change the value of the DBL (font size) included in the cluster to -1. To set cell dimensions use the Excel Set Cell Dimension VI after generating the table.

Best regards,

Daniel Fulop

[kaero]

Thanks for reply.

Setting font size to -1 works, however the cell size is problem. I use various templates designed for printing, and I do not know cell width/row height before writing to the excel.So I changed the Append Table to Report.vi to my needs (in attachement). I do not fully understand the logic in this .vi however It seems to work. It is strange National Instruments does include a .vi to the Report Generation Toolkit which automatically changes cell dimensions.

[William1225]

Hi,

Though this is an old article, I have the same question and need some help...

I have a table with mixed format settings, like the following image,

I'd like to modify the character only without changing the format.

There're two method, one is "Append Table to Report.vi", the other is "Excel Easy Text.vi".

However, the result I got is a little different from the previous article.

If I use the Append Table to Report.vi,

the changed formats are :

the font size

the column dimension

If I use the Excel Easy Text.vi,

the changed formats are :

the font size

the grid lines

the background color

the vertical/horizontal alignment

As DanielFulop mentioned, create a constant to the font terminal of the Excel Easy Text.vi and set the font size in the font cluster constant to -1, it indeed keeps the font size unchanged, but the background color, alignment, and the grid line are changed.(I tried wire a true constant to the "border ? terminal" of the Excel Easy Text.vi, but the outer thick lines in the original Excel are changed to the thin lines)I'm not sure what's wrong in my program so that my conclusion is not the same as the Daniel Fulop's.

I turned to try the "Append Table to Report.vi", I found if I wire a constant of -1 to the column width, the column dimension keeps the same as the original Excel, the only changed format left is the font size, it troubled me a lot. At last, I tried to get the cell font size by the activeX and set it back after the Append Table to Report.vi. It seemed the same format as the original Excel after modifying the content.

I'm still wondering if there's a better solution to do it, keep all the format setting the same, just modify the content in the cell.

Could someone give me some ideas or suggestions? Thanks a lot!!!

[ben64]

You can create a simple vi based on the one from the Report Generation Toolkit to set the Font size to -1. The attached vi will only work for an Excel type report. Just use it after the New Report vi and the font settings won't be changed unless you use another vi that modify them.

Ben64

[Bob_Schor]

Very interesting, Ben.  I would have thought you could do the same thing with the Excel Set Format function, since it will also allow you to set the Font Size to -1.  While this function works fine to set everything to Bold or other Font attributes, setting the size doesn't seem to work.  Curious. I'll have to file your trick away somewhere so I don't forget it ...

Bob Schor

[ben64]

The Set Report Font.vi is probably the best way to go. I had some doubt using an empty string as the Font name but it seems that that it is taken as a "no change" value (that was the reason I created the SetExcelFontToUnchanged.vi) . Using Font Settings of -1,-1," ", -1 will keep the Excel font settings unchanged.

Ben64

[Bob_Schor]

Figured out the "simpler" way.  I forgot that I don't (usually) use Append Text Table, but use the Excel Easy Table function.  This has a Font input, and I simply wired -1 into the font size and it worked the same as your code.  Note that you can "collapse" that big constant by right-double-clicking on the cluster boundary.

Bob Schor

P.S. -- thanks for pointing out this very nice feature!

[William1225]

Hi ben64,

Exactly the Set Report Font.vi can solve my problem, I was usually going through the Excel Specific toolkit fo find what can I implement and missed the useful VI right before...

It seemed it's hard to keep the cell format by the Excel Easy Text.vi, maybe taking use of the Append Table to Report.vi along with Set Report Font.vi is a better way to do it.

Very thanks to your solution:)

[ben64]

---

@Bob_Schor wrote:

Figured out the "simpler" way.  I forgot that I don't (usually) use Append Text Table, but use the Excel Easy Table function.  This has a Font input, and I simply wired -1 into the font size and it worked the same as your code.  Note that you can "collapse" that big constant by right-double-clicking on the cluster boundary.

Bob Schor

---

Hi Bob,

Unfortunately, when you look under the hood of the Excel Easy Table.vi you can see that it uses the Append Table to Report.vi with the column width input unwired using the default value of 1. In this specific case the font settings won't be changed but the cell width might be.

Ben64

EDIT: If necessary Excel Set Cell Alignment.vi can be use to set wrap text to false to avoid cell height change.