How to apply template on excel file exported using 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
'''


''' Export datagridview's data contained in an data table to excel file
'''
''' DataGridView's datatable
''' Excel File Path with xlsx extension

 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.

0 answers