centering text in file

[DanNatCorning]

I am using a Write to Spreadsheet File vi., inputting a 2d array of strings.  What I'd like to do is be able to format the file (created as an excel file (.xls) so that all of the cells are cnter aligned. I can't seem to get this to work programmatically.  Do I have to go out and open the file manually, and then autosize and center all of the columns, or is there a way to do it from within LabVIEW?

[falkpl]

Are you using the office report generation toolkit?  There is a set cell allignment vi.  Otherwise you will have to use activeX interface to excel.

[DanNatCorning]

I have the report generation toolkit, but I don't see how I can leverage any of those VIs...below is a screen capture of this VERY small part of my app. I just want to check to see if the file exists.  If not, create it and write the 2D array of strings to it.  Then I'd like to autofit the columns and center the text in all columns, so the user does not have to do this.  Seems simple, but...

[Dennis_Knutson]

You do realize that you are not writing an Excel file, don't you? You are writing a text file with an xls extension. You can't center the text until someone imports that text file into Excel. If you want it centered, use the toolkit and write directly to Excel.

[DanNatCorning]

Yes, I realize that Dennis.  I have not used the toolkit before.  My application actually makes extensive use of a couple of activeX containers containing Excel Spreadsheets in various areas, but when I'm all done, I just capture a range of cells from the ActiveX excel spreadsheet on my main front panel tab, and copy them to a text file, which is appended each time the vi is run.  It seemed like the Write to Spreadsheet File was the most straightforward approach.  I'm looking at the Easy text report VI, but that does not seem to provide what I need.  Can you explain more?  I know my questions are basic, but I'm doing my best...

[Dennis_Knutson]

No, the Easy Report is too high level, you'll have to call some low level functions. While I have the report toolkit, I've never really used it. I see a function called Excel Set Cell Format that might do what you want.

[DanNatCorning]

Dennis, attached is a weord doc showing where I've been and what I've run into...can you take a look and offer advice?

[lars]

If you are appending to a file, your simplest solution may be to create a macro in Excel that formats the spreadsheet when it's opened. As stated above, the next choice is to use the Excel API which isn't too difficult to do, but will take some effort.

[DanNatCorning]

I'm hoping to find SOMEONE on here who can get me going in the right direction.  It seems like there HAS to be a way to apend to an existing spreadsheet file in a relatively straighforward way.  I've done all the work to format the file, now I just need to add some data to it...I tried the Append Table to Report, but that keeps throwing an error in the Excel_Get_Range/Excel_Insert_Table/Append Text Table To Report (wrap) /Append Table To Report.  It seems to have a problem with the start (row and column).  As I read the description, it seems that it wants the cell location at which I want to start writing the appended data.  I provided that, but I still get an error...anyone out there know what I'm doing wrong?

[Bakk_Diagram]

Dan,  there is indeed a way to do this.  You will need to open a .NET reference to the cells you are trying to write to in Excel.  The methods to do this are contained in the .NET palette of the Connectivity palette.  You will have to consult Microsoft for documentation on what .NET objects and parameters must be modified to allow this.  This method is very complicated and time consuming, and it is because of this that we created the Microsft Office Report Generation Toolkit.  It is a complete abstraction of all of the .NET references needed to manipulate MS format files, and standard windows text field properties.

Alternatively, there is probably a standard format for spreadsheets that can be read on all computers, just like rich text format.  Im not sure exactly what you would need to modify to obtain the desired result. But windows gets the info from tsomewhere in the file, pehaps some meta data.  Research into exactly what character or piece of data must be written to the file, and where in the file it must be written to would narrow your objectives and make it easier to accomplish in LabVIEW by brute force. However low level file formatting is beyond my expertise.