topic Re: How do I input column headers in an Excel report with variable width columns? in LabVIEW

How do I input column headers in an Excel report with variable width columns?

SciManStev — Mon, 13 Sep 2010 15:43:24 GMT

I am trying to create an Excel report with variable column widths so that the column headers have a blue background, and show up at the top of each page in the report. The end report will use text wrapping. How can I do this?

Re: How do I input column headers in an Excel report with variable width columns?

systemcrash — Mon, 13 Sep 2010 16:19:18 GMT

Report generation toolkit can help you in this matter, Go to Excel format API and you can use all the different API to set your cell border, alignment, colors etc etc

Re: How do I input column headers in an Excel report with variable width columns?

SciManStev — Mon, 13 Sep 2010 16:41:08 GMT

I am able to create the report. Other than the headers, each line is a record from a database. I can set the report so that the headers are blue, and the text wraps around after the report is finished. That is not the problem. The problem comes in trying to keep the headers at the top of each new page. The vi's that actually insert column headers will do it, but force the column widths to one value, even when using a template. Typically what I have done is to lay out the column headers and widths, and color if needed, then filll the report row by row with different database records. What I need to do is reinsert the column headers at the top of the next page. Can I poll the report and see when a new page is started? That way I could reinsert the headers as a row in the report. Or is there some other method for doing this?

Thank you!

Re: How do I input column headers in an Excel report with variable width columns?

systemcrash — Mon, 13 Sep 2010 16:57:17 GMT

How about instead of using column headers you update each top cell induvidually and then format this accordingly according to your requirement, but I doubt the cell width might alter when you format the columns below, hope you are getting what I mean.

Can I poll the report and see when a new page is started? That way I could reinsert the headers as a row in the report.

I believe it is up to you when do you want to stop updating the page 1 in excel and move on to page 2 or wait until all 65536 rows are filled and then move 2 next page.

Re: How do I input column headers in an Excel report with variable width columns?

SciManStev — Mon, 13 Sep 2010 17:10:44 GMT

This is what I have been doing up to this point. The only thing I can't do is to reinsert the column headers at the top of the next page because I don't know when the next page starts. The database records vary in size so that because of the wrap around` text at the end, each row can and does vary considerably in height. That means that there could be 1 to 25 rows per page. If I knew when the next page was to start, I could reinsert the headers and color easily. Without knowing how many rows are going to be for each page, or not knowing when a page break is created makes this impossible. I can easily create the entire report the way I want it except that the column headers are only displayed once, at the very top. The example Excel sheet my boss handed me that I am supposed to duplicate has the column headers at the top of each page. There could be between 1 and 200 pages, so only having the column headers displayed once would make the report hard to read.

Thank you!

Re: How do I input column headers in an Excel report with variable width columns?

systemcrash — Mon, 13 Sep 2010 17:26:05 GMT

How about using the EXCEL GET WORKSHEET function to track for the page number, keep monitoring the page number when there is change in number you can insert the column names in new page and allow the excel page to be filled up again. Did you try this?

Re: How do I input column headers in an Excel report with variable width columns?

SciManStev — Mon, 13 Sep 2010 17:28:39 GMT

That might work. I will work out the code and post back when I get it squared away. Excellent idea! Thank you very much!

Re: How do I input column headers in an Excel report with variable width columns?

systemcrash — Mon, 13 Sep 2010 17:28:40 GMT

Well my question would be how the control goes to next page when you are not directing it to go to next page.

Re: How do I input column headers in an Excel report with variable width columns?

SciManStev — Mon, 13 Sep 2010 17:33:38 GMT

The rows increase one at a time. In Excel itself, when you specify from the page setup "down then over or over then down, Excel shows a dotted line where the page boundries are. That dotted line changes as you manually increase or decrease the row height, but gives the same size for a page. My report just keeps extending downward until it finishes. I will try your method and see what happens.

Re: How do I input column headers in an Excel report with variable width columns?

SciManStev — Mon, 13 Sep 2010 18:06:16 GMT

Well, that didn't work. I am not changing worksheets, just adding row after row. The concept seems valid, so I will keep searching for a vi that says a page break was just added. I think I may be out of luck.

Re: How do I input column headers in an Excel report with variable width columns?

systemcrash — Mon, 13 Sep 2010 18:10:29 GMT

Well, you always have control to write the data into excel file, you can write 500 rows of data in page 1 and then move to page 2 and similarly page 100 if you want to.

As far as I see you read the data from from database and dump the same data to the excel file,. you can write how much ever data you want to correct ? Tryout programming in this fashion.

Re: How do I input column headers in an Excel report with variable width columns?

SciManStev — Mon, 13 Sep 2010 18:25:57 GMT

Here is the vi I am working with. It is saved to the frame where the column headers are created. The next frame is a for loop which first gets the next database record, then applies it to the report.

Re: How do I input column headers in an Excel report with variable width columns?

SciManStev — Mon, 13 Sep 2010 18:54:47 GMT

This is a fictional worksheet generated by the vi to show what it will look like.

Re: How do I input column headers in an Excel report with variable width columns?

systemcrash — Mon, 13 Sep 2010 19:01:02 GMT

Alright, Instead of using Increment Column VI use the "EXCEL EASY TEXT.VI " to update your column name and make use of start and end index to control your column index. This will avoid the use of basic report generation API.

Looks like you have lots of column to update, but I can see that you have sequence inside sequence inside sequence, you can really modify the logic and come up with better ones.

Well in my earlier reply I meant use the EXCEL EASY TEXT VI to update the column names, and since you get the data from the database you can write data in a loop at once instead of writing into variable and then writing into excel sheet.Use "EXCEL EASY TABLE" to write the data into excel file at once, remember to properly program the start and end inputs.

Attaching the excel sheet generated from my program and see the wonders what we can do from NI report generation toolkit

Have attached some programs of my project

Re: How do I input column headers in an Excel report with variable width columns?

SciManStev — Mon, 13 Sep 2010 18:59:31 GMT

Awesome! I think you have put me on the right track. I will try your suggestions and post back.

Thank you!

Re: How do I input column headers in an Excel report with variable width columns?

Wayne.C — Mon, 13 Sep 2010 19:46:09 GMT

SciManStev,

Do the headers just need to show up when the report is printed?

Re: How do I input column headers in an Excel report with variable width columns?

SciManStev — Mon, 13 Sep 2010 19:48:59 GMT

Yes. Only when the report is printed. Several copies of the report are passed out at meetings for review. On the monitor itself, I don't care.

Re: How do I input column headers in an Excel report with variable width columns?

Wayne.C — Mon, 13 Sep 2010 19:56:56 GMT

SciManStev,

Very easy way to do this. In Excel its called 'Rows to repeat at top:' You can access the setting using ActiveX. I've attached a simple example that sets Row1 of worksheet to be repeated at top of each printed page.

Re: How do I input column headers in an Excel report with variable width columns?

SciManStev — Mon, 13 Sep 2010 20:01:28 GMT

I think that is just perfect! I was hoping it would be that easy! Thank you both very, very much!

Re: How do I input column headers in an Excel report with variable width columns?

mubin — Tue, 26 Feb 2013 09:05:24 GMT

I have exported the table with "excel specific vi's" with column headers data at the top of the 1 st page. but, I want to add column headers to each page of the excel report programmatically.

Can anyone give me the answer.