How to apply template on excel file exported using oledb ?

Rashmi Gupta 81 Reputation points
2021-03-19T09:27:43.64+00:00

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
Windows Forms
Windows Forms
A set of .NET Framework managed libraries for developing graphical user interfaces.
1,869 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,627 questions
Office Management
Office Management
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Management: The act or process of organizing, handling, directing or controlling something.
2,062 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,689 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Daniel Zhang-MSFT 9,621 Reputation points
    2021-03-22T02:37:01.24+00:00

    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.