Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VB.Net - Exporting to Excel Variable Number of Columns

Posted on 2013-05-13
8
Medium Priority
?
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 83

Accepted Solution

by:
CodeCruiser earned 1800 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

715 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