How to apply template on excel file exported using oledb ?

Rash24agg

New member
Joined
Mar 19, 2021
Messages
1
Programming Experience
Beginner
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. Dim connStr = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" + XLPath + ";Extended Properties='Excel 8.0;HDR = YES';"

  3. Using connection As OleDbConnection = New OleDbConnection(connStr)
  4. connection.Open()
  5. Using command As OleDbCommand = New OleDbCommand()
  6. command.Connection = connection
  7. Dim columnNames As New List(Of String)
  8. Dim tableName As String = dataTable.TableName

  9. If dataTable.Columns.Count <> 0 Then
  10. For Each dataColumn As DataColumn In dataTable.Columns
  11. columnNames.Add(dataColumn.ColumnName)
  12. Next
  13. Else
  14. tableName = If(Not String.IsNullOrWhiteSpace(dataTable.TableName), dataTable.TableName, Guid.NewGuid().ToString())
  15. command.CommandText = $"CREATE TABLE [{tableName}] ({String.Join(",", columnNames.[Select](Function(c) $"[{c}] VARCHAR").ToArray())});"
  16. command.ExecuteNonQuery()
  17. End If
  18. If dataTable.Rows.Count <> 0 Then
  19. For Each row As DataRow In dataTable.Rows
  20. Dim rowValues As List(Of String) = New List(Of String)()
  21. For Each column As DataColumn In dataTable.Columns
  22. rowValues.Add(If((row(column) IsNot Nothing AndAlso Not row(column).Equals(DBNull.Value)), row(column).ToString(), String.Empty))
  23. Next
  24. command.CommandText = $"INSERT INTO [{tableName}]({String.Join(",", columnNames.[Select](Function(c) $"[{c}]"))}) VALUES ({String.Join(",",
  25. rowValues.[Select](Function(r) $"'{r}'").ToArray())});"
  26. command.ExecuteNonQuery()
  27. Next
  28. End If
  29. End Using
  30. connection.Close()
  31. End Using
  32. 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

Staff member
Joined
Apr 6, 2019
Messages
3,700
Location
Chesapeake, VA
Programming Experience
10+
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!

 
Top Bottom