In LabVIEW, how do I format columns in an excel spreadsheet so that the columns are the right length?

[Scott_B]

Hello everybody,

I work for Cooper Crouse-Hinds out of Syracuse, NY.  I am creating a temperature test VI for various lighting fixtures using a Thermocouple card on NIs PXI chasis.  When I have the program take data, it writes it to an excel spreadsheet.  I have two problems that are preventing my test from being perfect.  Three of the strings I write to the spreadsheet are right oriented and all the others are left oriented.  I want them all to be right oriented.  Also when I open the file in excel, I have to manually adjust the column size.  I want the coumns to be the size they need to be.  How do I fix these problems?

Thanks

Scott

[BretD]

Hey Scott,

Would it be possible for you to upload your VI? and maybe a screenshot of how your data comes out looking?

[Matthew_Kelton]

Scott,

Are you actually creating an Excel spreadsheet, or are you creating a text file that is being imported into Excel?  The LabVIEW VIs Write to Spreadsheet File and Array to Spreadsheet String are a bit misleading in that they are simply creating text that is delimited.  Excel can import this data, however it is not an xls file (even if you name it as an xls file).

From what you describe, it sounds as if this is what you are doing.  If this is the case, there is nothing you can do to change the justification or column width.

You have a couple of options.  You can create an actual Excel file using ActiveX or .NET which will allow you edit formatting and you can create an actual xls file.  This code would have to be updated when Excel is updated on your PC, and would require Excel to be installed on the PC.  YOu could save an Excel template to simplify the process.

You can also leave the data file as is, and write an Excel macro which will import the data into a template and have the proper formatting.  I have done this since Excel can be updated without requiring my LabVIEW code to be modified (the macro may require some changes), and Excel doesn't have to be on the PC collecting the data, just ont he PC viewing the data.

[Scott_B]

Yes, it would be possible.  I have attached my sample test template to this reply and an example excel data. when you open the excel data sheet, you can see how time elapsed and lab tech data are left justified.  Plus when you first open the program you can see that the columns are squished together and the date is in ###### til the column is expanded.  Thank you for your help.

Scott

[Scott_B]

And here is the excel file.  It would only let me add 3 attachments and I have two subvis in this VI

Thanks

Scott

[Matthew_Kelton]

Scott,

As I expected, this xls file is not an Excel file, it is a text file.  Excel imports the text file when you open it.

[Scott_B]

I thought .xls is an excel file extension.  What extension should I use?

[Matthew_Kelton]

Scott,

It appears to be a tab delimited text file, so the more proper extension would be txt.  You could also you csv, although it technically is supposed to be comma delimited.  The CSV file should open in Excel automatically by double clicking it, though.  Just because you put an XLS extension doesn't mean it becomes an Excel spreadsheet.  If you use notepad, you can view the file you posted.

The original problems you listed will not go away by simply changing the extension.  Excel is importing the data into a blank spreadsheet, so the problems will continue.  If a test file is acceptable, you can change the extension to TXT and notepad will open it.

[systemcrash]

Use report generation toolkit for MS office and use Excel VI's to save the data in excel. The toolkit has VI's which can set cell width, data alignment and other things. You can do the same with Active X as said by Matthew