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            
          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: I am able to insert the data by implementing 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.

This is the given template.

The data should be polpulated as shown here

but the problem is that 1st row is being overwritten by column names; also, data is being populated after the coloured lines

Now, I have some questions:

1. What should I do so that the datatable can be populated from the 3rd row of the excel file without overwriting it?
2. I read about OPENROWSET from [here][5]
   it can export data from the desired row but I did not understand how to use it in my situation? [5]: https://social.msdn.microsoft.com/Forums/en-US/99c7d06f-6c5d-4ab3-96e4-e07698394a89/import-data-from-sql-to-excel-sheets-named-region?forum=sqlintegrationservices

Answer 1

Hi RashmiGupta-8587,
As vb2ae said, OleDB will only allow you to interact with the data.
If you wan t to load custom excel template(.xltx) in new worksheet, you need to use interop.
Here are some code examples to achieve it you can refer to.
C# How to write data to excel template
How to Load custom excel template(.xltx) in new worksheet using c# code.
Considering performance and experience, I suggest you do a test first.
Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.