How to apply template on excel file exported using oledb ?

See more:

VB

templates

export

OleDb

, +

Hi,
I have exported data into excel file using oledb since I cannot use interop or any third party library. Here is my code :

''' <summary>
''' Export datagridview's data contained in an datatable to excel file
''' </summary>
''' <param name="dataTable">DataGridView's datatable</param>
''' <param name="XLPath"> Excel File Path</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

The excel file is populated successfully but now I have to apply a template on it given to me in an xltx file and I cannot use any third party library here. How can I
apply the template in the excel file?
Any suggestions ?
Thanks in advance.

Edit : New situation arose :-
Now,I am able to insert the data by implemnting these three things :
1. changing the given file to xlsx format
2. keeping the name of the excel sheet same as name of the datatable
3. appending "$" in name of the datatable.

The data should be polpulated from third row of excel file like this without overwriting first 2 row as they contain information which is template specific, but the problem is that 1st row is being overwritten by column names; also, data is being populated after 39th line just like this.
Now, I have some questions:

1.What should I do so that the excel file can be populated from 3rd row without overwriting the excel file?

2.I read about OPENROWSET from here:
it can export data from the desired row but I did not understand how to use it in my situation?

What I have tried:

After adding data to 'rowvalues', instead of using "Insert into ", I tried to go with OPENROWSET as follows:

sql = $"insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database={XLPath};',{tableName}$') select * from {tableName}$"

     command.CommandText = sql
    command.ExecuteNonQuery()

but failed.