Click here to Skip to main content
15,396,239 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?
Rash24Agg 23-Mar-21 12:29pm
I tried your solution with these steps:
1. Save a .xlsx copy of the template file.
2.Importing the data of given excel file to separate datatable, say "exclTbl".
3. Try to add the column's of datagridview's datatable to exclTbl by applying the Alter table command on exclTbl. I think only that way I can export the data.
Step 3 resulted into Exception as "Invalid Operation".
I tried to find out its solution but couldn't.
Richard MacCutchan 23-Mar-21 12:55pm
I am not sure about Alter Table via OLEDB. You need to create your data table from the template file, and then merge the other data into it before writing out to the new file. So the columns in the data table for both sets of data (tamplate and new) must match before you start merging. try drawing a few columns on paper from both sets to see how you need to format it.
Rash24Agg 23-Mar-21 13:25pm
The template has its own columns and datagridview has different columns and I shouldn't remove template's columns. So, to export data appropriately, I was trying to add datagridview's columns to 'exclTbl' by "ALTER TABLE" command.
All these things would be much clear if I could insert the images of desired result and actual output but I don't know how to....
Anyway, How can I merge the data in exclTbl?
Richard MacCutchan 23-Mar-21 13:28pm
We cannot answer this as only you know which columns are needed. At a guess the template columns are going to be the correct ones. But this is a business decision, not a programming one.
Rash24Agg 23-Mar-21 13:40pm
All the columns of template file and datagridview's datatable are needed.
I guess things are not clear here just by texting. May you need more information like images etc. to help me.
Can I contact you?
If so, please tell me how, otherwise it's ok.
Anyway, thanks for your time and efforts.
Richard MacCutchan 23-Mar-21 14:58pm
Sorry, no, and for two reaons:
1. The whole point of an open forum like this is that many people can offer help.
2. I have tried to explain in simple terms what you need to do, and it is unlikely that I will change that advice.

I understand that all columns are needed and I have tried to explain what you need to do. It is a fairly simple process to fill a datatable from two separate sets of data.
Rash24Agg 23-Mar-21 23:17pm
OK.This image shows how I want the data to be inserted.
Hope it will add clarification.
This shows the data is being populated. Please note the header and rows from where data is populated.
Richard MacCutchan 24-Mar-21 4:09am
Well it is fairly obvious that your row and column references are wrong when you insert the data.
Rash24Agg 24-Mar-21 5:18am
I don't see how they are wrong. Before applying the template, I was using the same code and data was exported in desired way.
Now, I just want way so that datagridview's table can be inserted from 3rd row in excel.
Richard MacCutchan 24-Mar-21 5:24am
The only way to find out is to debug your code. you have all the information there, the source code, the data, the expected results, so you need to work from that. Try making a smaller test program to try and find out where the mis-matches are occurring.
Richard Deeming 19-Mar-21 6:24am
Why the artificial restriction on using third-party libraries? Unless you want to completely reimplement the OpenXML SDK in your own code, a free third-party library like ClosedXml or NPOI would make your life much simpler.
Rash24Agg 19-Mar-21 6:48am
My project cannot use third party library since after the release of our application, may its authors introduce some changes that may affect our product's functionality.
Richard Deeming 19-Mar-21 6:50am
Unlikely, but if that's your concern, simply never update the third-party library. Or add unit tests to your code base to ensure that the behaviour doesn't change if you do update the library.

It's not going to magically update itself in your deployed application unless you update it and deploy a new version.
Rash24Agg 23-Mar-21 9:24am
What if they start charging money for their library?
Richard Deeming 23-Mar-21 9:31am
Use an open-source library with a license which allows commercial use. If they change the license for a later version, you can continue using the old version under the license which applied when you obtained it.

EPPlus is a good example. Version 4.x was entirely free, but version 5 requires you to purchase a license to use it in a commercial application. Your commercial application can continue to use v4 for free under the original license; you only need to pay if you want to upgrade to v5.

You could also "fork" an open-source library, so you'd have your own copy even if the original project was completed removed.
Maciej Los 19-Mar-21 8:51am
I'd suggest to:
1) create *.xslx file based on template
2) pass full path to that file as a parameter to the 'ExportToExcel' method.
Rash24Agg 23-Mar-21 9:24am
Thanks for your reply.
I did as you told. I am facing a new difficulty updated in my question. Please help me if you can..

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