Start exporting data to an excel file from a particular row of sheet

Question

Hi everyone,

 

I have excel installed in my system which is used by my project and I am trying to export the data to an excel file which has a template like this:

 

 

I want the data to be inserted from the 3rd row without affecting first two rows same as here

 

 

But in the actual output first row is being overwritten by column names and data is inserted from where the template ends :

 

 

Here is the code by which I was inserting the data :

''' <summary>
''' Export datagridview's data contained in an data table to excel file
''' </summary>
''' <param name="dataTable">DataGridView's datatable</param>
''' <param name="XLPath"> Excel File Path with xlsx extension</param>

 Private Shared Sub ExportToExcel(ByVal dataTable As DataTable, ByVal XLPath As String)
 Dim connStr = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" + XLPath + ";Extended Properties='Excel 8.0;HDR = YES';"
 Using connection As OleDbConnection = New OleDbConnection(connStr)
      connection.Open()
    Using command As OleDbCommand = New OleDbCommand()
       command.Connection = connection
       Dim columnNames As New List(Of String)
       Dim tableName As String = dataTable.TableName
       If dataTable.Columns.Count <> 0 Then
          For Each dataColumn As DataColumn In dataTable.Columns
                      columnNames.Add(dataColumn.ColumnName)
          Next
       Else
          tableName = If(Not String.IsNullOrWhiteSpace(dataTable.TableName), dataTable.TableName, Guid.NewGuid().ToString()) + "$"
          command.CommandText = $"CREATE TABLE [{tableName}] ({String.Join(",", columnNames.[Select](Function(c) $"[{c}]
                               VARCHAR").ToArray())});"
          command.ExecuteNonQuery()
       End If
       If dataTable.Rows.Count <> 0 Then
          For Each row As DataRow In dataTable.Rows
             Dim rowValues As List(Of String) = New List(Of String)()
             For Each column As DataColumn In dataTable.Columns
                rowValues.Add(If((row(column) IsNot Nothing AndAlso Not row(column).Equals(DBNull.Value)),
                                  row(column).ToString(), String.Empty))
             Next
             command.CommandText = $"INSERT INTO [{tableName}]({String.Join(",", columnNames.[Select](Function(c) $"[{c}]"))})
                           VALUES ({String.Join(",", rowValues.[Select](Function(r) $"'{r}'").ToArray())});"
             command.ExecuteNonQuery()
          Next
       End If
    End Using
  End Using
 End Sub

 

I wanna ask two question:

1. Why the data is being inseted from the end of the template?
2. Is there any way I can insert the data from 3rd row without overwriting the first two rows?

I tried to do it with OPENROWSET but I did not understand how to apply it in my situation so I was getting syntax error.

 

I would really appreciate if anyone can help me.

 

Note: I cannot use interop or any third party library.