Nonagon - 1 year ago 66

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

Answer

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

Source (Stackoverflow)