question

RashmiGupta avatar image
0 Votes"
RashmiGupta asked RashmiGupta-7321 commented

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
''' <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


dotnet-csharpwindows-formsoffice-excel-itprooffice-itpro
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe use a copy of the given xlsx file (maybe adjusted) instead of your current Excel file?

1 Vote 1 ·

Sorry, I made a mistake. The given template file is in xltx form, not in xlsx. Please change your suggestion as according to this.

0 Votes 0 ·

Sometimes you can open the template in Excel then save it as a regular .xlsx file. You can also define the target table and columns, if not yet present. (‘CREATE TABLE’ will not be needed in this case). Then export the data to this file programmatically.

1 Vote 1 ·
Show more comments

You will have to automate excel or use something like the open xml sdk if you want to apply a template. OleDB will only allow you to interact with the data

0 Votes 0 ·

I cannot depend upon third party library since it's possible that after our product's release the owner's of third party library may introduce some changes that may affect the application's performance and ruin user experience.

0 Votes 0 ·

In regards to

may introduce some changes that may affect the application's performance and ruin user experience.

That is only if you change versions of said library, find a library that suits your needs and don't change the version. FWIW the same can happen with using native Excel automation which happens all the time to developers. In regards to OpenXML, this will introduce a lot of external DLL libraries and is prone to the same problems as Excel automation. The benefit for OpenXML is performance and finer control yet with these benefits comes complexity which you will need to learn and that is not done over night.

And with no disrespect, a developer is not beyond coding something that may ruin user experience. Find a mature third party library which most experienced developers do after learning the pitfalls of Excel automation and not taking time to understand the limitations of OleDb path.

0 Votes 0 ·

Hi @vb2ae ,
Thanks for your suggestion.
Now I am moving to Open Xml. Can you please share any references which show how to apply a template on an excel file while exporting data with Open Xml?

0 Votes 0 ·

1 Answer

DanielZhang-MSFT avatar image
0 Votes"
DanielZhang-MSFT answered DanielZhang-MSFT edited

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.


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,
Thanks for paying attention.
I was formerly trying to use the interop library but it was giving exception as stated here:

[1]: https://docs.microsoft.com/en-us/answers/questions/311849/how-to-run-microsoft-office-version-182008127110-w.html


Unfortunately I didn't get any solution. Can please suggest something which is according to specifications?







0 Votes 0 ·

Hi @RashmiGupta-8587,
Regarding this problem, I tested it and it can be successfully used in my Excle 2016 and Access 2016.
So the problem lies in your Office and you need to fix it.
And then try the code example I provided above.
Best Regards,
Daniel Zhang

0 Votes 0 ·

Hi @DanielZhang-MSFT !
Did you try it with MS Office 365 instead of excel 2016?
The problem occurs when I try to export data in excel with office 365,using access 2016 .

0 Votes 0 ·