.net data being formatted in excel

[douglas]

I am writing a Visual Basic.Net 2005 desktop application that exports data to
individual excel 2003 spreadsheets in separate workbooks. (The data is
written to separate workbooks since the workbooks are emailed to different
customers.)

I am exporting all the data I want to the spreadsheets now. However, I
would like to know how to do any of the following:
1. Put the column headers on to the top of each data column.
2. How to put report headers on the first 3 rows of each spreadsheet,
3. How to set the wraparound property one column in particular. I have one
column that is varchar(500) and I want this column to wraparound.

Thanks!

 

[Jacob Skaria]

'1. Column headers use a loop to update the header columns. Write this as a
separate procedure and call it for each workbook
For lngCol = 1 to Ubound(arrHeader)
Workbook.Sheets("Sheet1").Cells(1,lngCol) = arrHeader(lngCol)
Next
'2. Wrap text. Select the column you need
Workbook.Sheets("Sheet1").Columns("E:E").Select
Selection.WrapText = True


If this post helps click Yes

 

[douglas]

"Jacob Skaria":

Your reply answers part of my question, thanks!
1. I do not understand your reply for column headers. can you tell me how
the
following code actually accesses the column headers:
For lngCol = 1 to Ubound(arrHeader)
Workbook.Sheets("Sheet1").Cells(1,lngCol) = arrHeader(lngCol)
Next
2. Can you tell me how to put page headers in rows 1 to 3 for each new
worksheet that is created?

Thanks!

 

[Jacob Skaria]

I understand you want to populate Column header and Page header in the top
rows. You can have these information populated into the cells and then use
the below code to repeat this section in all pages.

ActiveSheet.PageSetup.PrintTitleRows = "$1:$3"

If this post helps click Yes

 

[douglas]

"Jacob Skaria":

Thank you for your answer but I still have the following questions:

1. Doesn't the code 'ActiveSheet.PageSetup.PrintTitleRows = "$1:$3"' copy
the header information into more worksheets in the same workbook? i am asking
that question since this code referst to the active sheet.
Does this work for copying the header lines into different workbooks? If
not, how do you copy the header information into individual worksheets in
separate workbook? (I am going to generate one worksheet per workbook since
the reports go to different customers.
2. How you you populate the column and page headers where there is one
worksheet per workbook?

 

[douglas]

Jacob Skaria":

Thank you for your answer but I still have the following questions:

1. Doesn't the code 'ActiveSheet.PageSetup.PrintTitleRows = "$1:$3"' copy
the header information into more worksheets in the same workbook? i am asking
that question since this code referst to the active sheet.
Does this work for copying the header lines into different workbooks? If
not, how do you copy the header information into individual worksheets in
separate workbook? (I am going to generate one worksheet per workbook since
the reports go to different customers.
2. How you you populate the column and page headers where there is one
worksheet per workbook?

:

 

[Jacob Skaria]

1. You will have to apply this for each workbook. The process would be
something like this..Open new workbook..Populate page header information in
cells/ then the column header information.........and then populate the
data...Save As....If the data is same you can make changes to the Workbook
and change the fields...to suit the next and again SaveAs....until close...I
know this is only on a highlevel...Please let me know about the
information....Is that going to be same in all workbooks with header info
changes...or ???

If you set the title rows; that can be viewed in excel only once.. but if
the user print or print preview the title rows will be populated in all rows..

2. Column and header info has to be populated into the cells just below the
page header cells...Unlike other reporting tools we dont have cells i mean
rows available in excel to populate the page header or column header....So we
will use the first 3 rows as the page header ..and the corresponding row as
the column header....If the column header remains the same then it is better
to include column header along with page header with a different color tone
or font size.. Again this depends on your requirement. Do you have sub
headers or sub totals in your data....


In case of any queries please post back. I will be checking this...Thanks

If this post helps click Yes

 

[douglas]

"Jacob Skaria":

This is to answer your question about the detail on the excel spreadsheets:

1. The title of the report will be the same between all the reports.
2. The subcategory header will be the same format, however the data will
change
based upon the customer information.
3. The column titles will be different depending upon what rows of the
joined tables the particular customer wants to see.
4. There is no summary or total information.

Thanks for showing me how you would code this between vn.net 2005 desktop
application exporting the sql server data to one worksheet in a workbook.
(Each workbook is for individual customers.)

 

[Jacob Skaria]

Few queries...Why have you specified that the title should be 3 rows. If you
have more info then fine; or other wise you can increase the row size of the
title...Subcategory also can be included in subsequent columns.

Fine. The best thing to do is design an excel worksheet manually in MSExcel
with the title,column and all formatting. View print preview and make
adjustments. Save the excel file. Rename the file to another extension..This
file will be used as a template for report creation.

At runtime copy the file to a temporary location with .xls extension. Open
the file and populate the data fields and column header info and Save
As...1st copy...remove the data and populate the data for the 2nd copy
....Save As...


If this post helps click Yes

 

[douglas]

Jacob Skaria:

A couple of items whcih include:

1. You are saying to Open the file first and save the header information
and formatting in a 'template' file in advance. Then at run run time to
actually populate the data and ciolumn headers, correct?
Can you show me an example of the code that can be using as a template
for the
excel spreadsheet?
2. I just found out that visual basic.net 2005 desktop application
version has a report viewer control that can export data to excel. Are you
familar with this control at all?

Thanks!