Nonagon Nonagon - 1 year ago 72 Question

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

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

Answer Source

Let's use this as an example

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)

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