• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

VB.Net - Exporting to Excel Variable Number of Columns

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
Jimbo99999
Asked:
Jimbo99999
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
CodeCruiserCommented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
Jimbo99999Author Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
CodeCruiserCommented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
Jimbo99999Author Commented:
Ok, I see.  So I will be referencing the Excel column by the column name from the table.
0
 
ElrondCTCommented:
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
 
ElrondCTCommented:
Sorry, CodeCruiser, I didn't notice before posting that you linked to a similar answer.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now