adding specific database data to an excel file using VB.NET

Question

I want to add data to an Excel file, i have established a connection the database and pulled the information i need into a stored procedure using SSMS.

I have added the columns based on the information i need, some of the rows will be static data here is my code:

Private Sub ExcelOutput_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExcelOutput.Click


Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim xlRange As Excel.Range
Dim misValue As Object = System.Reflection.Missing.Value
Dim rNum As Random = New Random



xlApp = New Excel.Application
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
xlRange = xlWorkSheet.UsedRange


With xlWorkSheet
    .Range("A1").Value = "Col1"
    .Range("B1").Value = "Col2"
    .Range("C1").Value = "Col3"
    .Range("D1").Value = "Col4"
    .Range("E1").Value = "Col5"
    .Range("F1").Value = "Col6"
    .Range("G1").Value = "Col7"
    .Range("H1").Value = "Col8"
    .Range("I1").Value = "Col9"
    .Range("J1").Value = "Col10"
    .Range("K1").Value = "Col11"
    .Range("L1").Value = "Col12"



    xlWorkSheet.Range("E2:E10").Value = DateTime.Now.ToString("dd-MMM-yy")
        xlWorkSheet.Range("F2:F10").Value = "Upload"
        xlWorkSheet.Range("G2:G10").Value = rNum.Next()
        xlWorkSheet.Range("I2:I10").Value = "INCLUDED"

End With

What i want to know is how i would be able to loop through a database table and populate each cell in the Excel file based on the information in each column, for example col1 will have account customer ID's that are pulled from a stored procedure. I want the use a loop to put it into the Excel file and include the information.

I am new to this so would appreciate some guidance

HaPhan · Accepted Answer · 2016-06-28 14:43:46Z

1

Let's say i have pulled the second query into a datatable. I will populate it into the excel file with that order: A1 being 1660, B1 being HT5-088, A2 being 5882, etc....

Here's the code:

Dim pos As String = ""
    For i As Integer = 0 To datatable.Rows.Count - 1 'rows
        For j As Integer = 0 To datatable.Columns.Count - 1 'columns
            pos = Chr(Asc("A") + (j)) & i 'calculate the column according to j
            'after this, pos will have the right excel cell.
            xlWorkSheet.Range(pos).Value = datatable.Rows(i)(j)
        Next
    Next

The gist here is, you can use a correct string to represent the excel range...

answered Jun 28, 2016 at 14:43

HaPhan

3191 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nonagon Over a year ago

Thats is Genius! Thank you!

Collectives™ on Stack Overflow

adding specific database data to an excel file using VB.NET

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related