Solved

VB.Net - Exporting to Excel Variable Number of Columns

Posted on 2013-05-13
8
356 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Check only one toolstripmenu item 12 63
FTP recursive download files 5 28
Background worker passing messagebox yes no 13 26
VB .net 2010 Byte array 2 24
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 …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 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