Excel Reports - Preserve Template Format

[TurboPhil]

Two part question here:

Part 1:

Is there an easy way to use the NI Report Generation Toolkit or any of the Excel subvis in the _Office addons subdirectory to preserve the existing formatting of a cell that you are writing data to? For example, I have a report template with named ranges to which I write data using the Excel Easy Text.vi, but that VI has inputs for cell formatting (font) that, when left blank, default to some font that I don't necessarily want. Is there a way to get the font (and other formatting properties--allignment, merge status, etc.) from the template before writing data to it to avoid overriding the template settings? I have searched through all the NI Report libraries and the Office addons, and I can't seem to find anything that will accomplish this.

Which brings be to Part 2:

Naturally, my reaction to the problem above is simple: roll your own. I figured it would be pretty easy to write a subvi to return the font refnum from a named range; it would probably look something like this:

However, as you can see, I get broken wires on my attempt to unbundle from the private Report class object. Apparently, I can't access properties of this object unless my VI is a part of the library, but I can't add my VI to the library because it is protected. Is there any way around this? I can see NI not wanting me to go in a modify the shipping vi.lib VIs, but it seems like it's not too much to ask for the ability to add user-created VIs to the libraries.

Maybe there's an easier way; I admit I'm pretty new to LVOOP.

EDIT: Oh, I did see that I could use the Excel Get ActiveX References to return refnums from the original report object, but that seems a bit messy for my tastes--too many unneeded references that I would have to close afterwards, and the connector pane leaves much to be desired (couldn't all the references be bundled together?).

[TurboPhil]

EDIT: Dug deeper and found relevant Excel_Get Current Font.vi to solve the formatting issues. However, I would still like to know, for my own edification, if there is any way to add VIs to private libraries such as those in the Report Generation Toolkit, on the off chance that there is some functionality I would like to implement that was not originally included.

[OlderJohn]

One method that I have used is to create tags in a template (my usual format is "[DESCRIPTION]") in the cells that you are inserting numbers into, and then use "Excel Find and Replace" VI to find that tag and insert the data.  This keeps the original text formatting.

I've only used this to get data into reports that are meant to be printed and shipped with products, but I have not used this for any data that is used in formulas on the sheet.

[TurboPhil]

@OlderJohn: Thanks for the tip. Unfortunately, the Excel Find and Replace.vi only accepts numeric inputs, and some of the data fields I am populating are strings. However, this does provide a perfect example of a situation where I would like to be able to add a new VI (i.e. a version of Find and Replace that handles string inputs) to the Excel class library to add functionality/flexibility to the toolkit.

Any ideas on how I could do that without reinventing the wheel?

[OlderJohn]

TurboPhil:  It accepts string inputs as well if I recall correctly.  From the help article online:

Searches the occurrences of searched string and replaces them with a new string or numeric value. The data type you wire to the replacement input determines the polymorphic instance to use. 

[TurboPhil]

<sarcasm>Don't cloud the issue with your "relevant facts"!</sarcasm>

Indeed, it does accept string inputs. Okay, maybe NI did think of everything, and maybe I don't need to make any of my own VIs....

[TurboPhil]

...but I see your String/Numeric data inputs and raise you Array data!

I have some data sets that I store in a 2D string array that I dump to a named range in my Excel template. It works fine if I use the Excel Easy Table.vi, but there is still the aforementioned formatting concern. I tried handling it like this:

But it doesn't seem to preserve all the formatting, for some reason (i.e. correct font type [bold], but incorrect font size). It sure would be nice if I could add a new subvi to the Find and Replace polymorphic VI to handle string/numeric arrays...

[OlderJohn]

Alas, I have no response to the dreaded array.  A non-efficient way would have you using nested For loops and deriving tag names based on iterations (like [Row1Col1], [Row1Col2], [Row2Col1], etc), but there are probably better ways to perform this.  

And I have only worked on Excel reporting for customers with limited reporting requirements, so I have not had the time to play around too much.