Redentoru Redentoru - 3 months ago 70
Vb.net Question

vb.net Export Datagridview to excel template

I want to export my datagridview rows to a existing excel template with headers that will start from cell A10:AA10.

This is the template:

enter image description here

I've tried this

Public Sub exportToexcel()


Dim default_location As String = "D:\Book1.xlsx"

Dim dset As New DataSet

dset.Tables.Add()

For i As Integer = 0 To dgvReports.ColumnCount - 1
dset.Tables(0).Columns.Add(dgvReports.Columns(i).HeaderText)
Next
add rows to the table
Dim dr1 As DataRow
For i As Integer = 0 To dgvReports.RowCount - 1
dr1 = dset.Tables(0).NewRow
For j As Integer = 0 To dgvReports.Columns.Count - 1

dr1(j) = dgvReports.Rows(i).Cells(j).Value


Next
dset.Tables(0).Rows.Add(dr1)
Next

Dim excel As Microsoft.Office.Interop.Excel.Application
excel = New Microsoft.Office.Interop.Excel.Application
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

excel.Visible = True
excel.UserControl = True

wBook = excel.Workbooks.Add(System.Reflection.Missing.Value)
wSheet = wBook.Sheets("Sheet1")
excel.Range("A50:I50").EntireColumn.AutoFit()
With wBook
.Sheets("Sheet1").Select()
.Sheets(1).Name = "Sheet1"

End With

Dim dt As System.Data.DataTable = dset.Tables(0)
' wSheet.Cells(1).value = strFileName
For Each col As DataGridViewColumn In dgvReports.Columns
wSheet.Cells(1, col.Index + 1) = col.HeaderText.ToString
Next


For i = 0 To dgvReports.RowCount - 1
For j = 0 To dgvReports.ColumnCount - 1
wSheet.Columns.NumberFormat = "@"
wSheet.Cells(i + 2, j + 1).value = dgvReports.Rows(i).Cells(j).Value.ToString
Next j
Next i

wSheet.Columns.AutoFit()


Dim blnFileOpen As Boolean = False
Try
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(default_location)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
End Try

If System.IO.File.Exists(default_location) Then
System.IO.File.Delete(default_location)
End If
wBook.SaveAs(default_location)
excel.Workbooks.Open(default_location)
excel.Visible = True
End Sub


This only creates a new excel file. I just need to feel a existing excel file.

Answer

Replace this line:

wBook = excel.Workbooks.Add(System.Reflection.Missing.Value)

that code will add a new Workbook to your newly created Excel.

This will open the file assigned to the default_location variable:

wBook = excel.Workbooks.Open(default_location)
Comments