Solved

VB.Net - Exporting to Excel Variable Number of Columns

Posted on 2013-05-13
8
353 Views
Last Modified: 2013-05-14
Good Day Experts!

Usually, when I build a report from SQL to Excel in VB.Net, I know the column names and I set them in the code.  

Now, I have a situation where I need to "Select *..." from a table with over 30 columns.  
Aside from setting up an array of Excel column names(A,B,C...AA,AB,AC and so on), is there a way to programataically start with value "A" and keep "retrieving" the next value without getting it from a pre-populated array?

Thanks,
jimbo99999
0
Comment
Question by:Jimbo99999
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39164216
I assume you are exporting from datatable to excel? You can use an inner loop

For i As Integer = 0 to dTable.Rows.Count
   for j As Integer = 0 to dTable.Columns.Count
0
 
LVL 23

Assisted Solution

by:Ioannis Paraskevopoulos
Ioannis Paraskevopoulos earned 50 total points
ID: 39164237
Hi,

You could retrieve the column names by running the following:

SELECT	sc.name
FROM	sysobjects so
		JOIN syscolumns sc
			ON sc.id = so.id
WHERE   so.name = @TableName
ORDER BY colorder

Open in new window


Just provide the tablename as you would for the select * from ....

Giannis
0
 

Author Comment

by:Jimbo99999
ID: 39164388
Good Day, I apologize...I did not articulate my question very good.  Let me try again.

Usually, when I build a report from SQL to Excel in VB.Net, I know the column names and I set them in the code.  Since I know the column names, I set-up my column formatting ahead of time with the following code:

With oSheet.Range("A" + CStr(ExcelRow))
        .Font.Size = 12
        .Font.Name = "Segoe UI"
        .Font.Underline = True
        .Value = "Pro Number"
        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
End With

Now, I want to execute a Select * statement where I won't know ahead of time the amount of columns or the columns names ahead of time to do my above column cormatting.  When I loop through my DataTable after executing the Select * statment, how can I get that Excel column letter reference to use in the above code?

So, each time through the loop I would get the next letter A through Z.  Then I have to keep track of when I get to Z since the next references are AA through AZ.  

I guess the deliverable I need help with is getting the next letter in the alphabet each time through the loop.

Thanks,
jimbo99999
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 83

Accepted Solution

by:
CodeCruiser earned 450 total points
ID: 39164428
With my approach

For i As Integer = 0 to dTable.Rows.Count
   for j As Integer = 0 to dTable.Columns.Count

You can can the columnname using a function supplied at below link. Just pass it j

So this

With oSheet.Range("A" + CStr(ExcelRow))

will become

With oSheet.Range(ColumnName(j) + CStr(ExcelRow))


http://stackoverflow.com/questions/297213/translate-a-column-index-into-an-excel-column-name
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39164441
Hi,

You have a datatable loaded, right?
As CodeCruiser said you may do:
for i As Integer = 0 to dTable.Columns.Count
    With oSheet.Range(Chr(i+65) + CStr(ExcelRow))
        .Font.Size = 12
        .Font.Name = "Segoe UI"
        .Font.Underline = True
        .Value = "Pro Number"
        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
    End With
next

Open in new window


It isn't really pretty but i would do the job. if .value is the column name then you may do the following:

for i As Integer = 0 to dTable.Columns.Count
    With oSheet.Range(Chr(i+65) + CStr(ExcelRow))
        .Font.Size = 12
        .Font.Name = "Segoe UI"
        .Font.Underline = True
        .Value = dTable.Columns(i).ColumnName
        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
    End With
next

Open in new window


Giannis
0
 

Author Comment

by:Jimbo99999
ID: 39164463
Ok, I see.  So I will be referencing the Excel column by the column name from the table.
0
 
LVL 20

Expert Comment

by:ElrondCT
ID: 39164865
I use the following function to get a column letter name from a number:
    Public Function ExcelColumnName(ByVal intCol As Integer) As String
        ' Convert an integer to the Excel column name: 1=A, 26=Z, 27=AA, etc.
        If intCol <= 26 Then
            Return CStr(Chr(intCol + 64))
        Else
            Dim intPrefix As Integer = (intCol - 1) \ 26
            intCol = ((intCol - 1) Mod 26) + 1          ' Force range 1-26, not 0-25
            Return CStr(Chr(intPrefix + 64)) & CStr(Chr(intCol + 64))
        End If
    End Function

Open in new window

You can then loop through numbers for the columns.
0
 
LVL 20

Expert Comment

by:ElrondCT
ID: 39164887
Sorry, CodeCruiser, I didn't notice before posting that you linked to a similar answer.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ASP/VB email question 4 46
vb.net class 3 22
VB.NET String Settings and Temp Folder Question 3 56
Get month and date in a format 4 35
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question