How to apply template on excel file exported using oledb ?

Question

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 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
connection.Close()
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.