Click here to Skip to main content
15,742,011 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
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)
       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
               tableName = If(Not String.IsNullOrWhiteSpace(dataTable.TableName), 
                           dataTable.TableName, Guid.NewGuid().ToString())
               command.CommandText = $"CREATE TABLE [{tableName}] ({String.Join(",", 
                                     columnNames.[Select](Function(c) $"[{c}] 
               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(), 
                     command.CommandText = $"INSERT INTO [{tableName}]({String.Join(",", 
                                           columnNames.[Select](Function(c) $"[{c}]"))}) 
                                           VALUES ({String.Join(",",rowValues.[Select] 
                                           (Function(r) $"'{r}'").ToArray())});"
                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

but failed.
Updated 23-Mar-21 17:25pm
Richard MacCutchan 19-Mar-21 5:46am    
What do you mean by "apply the template in the excel file"?
Rash24Agg 19-Mar-21 6:14am    
I mean I am given an xltx file which contains the template having name of my company and I have to set the template of the excel file(<- in which data is to be populated) same as template of the given file.
Richard MacCutchan 19-Mar-21 6:17am    
The logical think to my mind, would be to read the different fields from the template file first, and add the relevant information to the appropriate parts of your datatable. You can then populate the remaining parts with the variable data before exporting the entire table.
Rash24Agg 23-Mar-21 9:21am    
Thanks for replying.
I have update my question. Please take a look.
Richard MacCutchan 23-Mar-21 9:37am    
Sorry, I ma not sure what you have changed. You need to mark the updates clearly, as it is unlikely we are going to remember what the original question was. Did you actually try my suggestion, and if so what was the result?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900