Solved

VB.Net - Exporting to Excel Variable Number of Columns

Posted on 2013-05-13
8
347 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now