How to apply template on excel file exported using oledb ?

[Rash24agg]

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>

1. Private Shared Sub ExportToExcel(ByVal dataTable As DataTable, ByVal XLPath As String)
2. 
   
3. Dim connStr = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" + XLPath + ";Extended Properties='Excel 8.0;HDR = YES';"
4. 
   
5. Using connection As OleDbConnection = New OleDbConnection(connStr)
6. connection.Open()
7. Using command As OleDbCommand = New OleDbCommand()
8. command.Connection = connection
9. Dim columnNames As New List(Of String)
10. Dim tableName As String = dataTable.TableName
11. 
    
12. If dataTable.Columns.Count <> 0 Then
13. For Each dataColumn As DataColumn In dataTable.Columns
14. columnNames.Add(dataColumn.ColumnName)
15. Next
16. Else
17. tableName = If(Not String.IsNullOrWhiteSpace(dataTable.TableName), dataTable.TableName, Guid.NewGuid().ToString())
18. command.CommandText = $"CREATE TABLE [{tableName}] ({String.Join(",", columnNames.[Select](Function(c) $"[{c}] VARCHAR").ToArray())});"
19. command.ExecuteNonQuery()
20. End If
21. If dataTable.Rows.Count <> 0 Then
22. For Each row As DataRow In dataTable.Rows
23. Dim rowValues As List(Of String) = New List(Of String)()
24. For Each column As DataColumn In dataTable.Columns
25. rowValues.Add(If((row(column) IsNot Nothing AndAlso Not row(column).Equals(DBNull.Value)), row(column).ToString(), String.Empty))
26. Next
27. command.CommandText = $"INSERT INTO [{tableName}]({String.Join(",", columnNames.[Select](Function(c) $"[{c}]"))}) VALUES ({String.Join(",",
28. rowValues.[Select](Function(r) $"'{r}'").ToArray())});"
29. command.ExecuteNonQuery()
30. Next
31. End If
32. End Using
33. connection.Close()
34. End Using
35. 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.

 

[Skydiver]

Considering that your code above seems to be in some flavor of VB, and this is a C# forum, perhaps you should post your question on this sites' sister: VB.NET Developer Community

If you can't use a 3rd party library, then you'll have to use the built in OpenXML implementation that is built into the .NET Framework. It will require a lot of reading documentation, and then banging your head against the table or wall when you fire up the OpenXML document explorer and discover that there is some other "magic" stuff that doesn't seem to be fully documented. Good luck!

Welcome to the Open XML SDK for Office

Documentation and guidance for the strongly-typed classes in the Open XML SDK for Office.

docs.microsoft.com