Rashmi Gupta

Rashmi Gupta

  • 2k
  • 14
  • 253

How to apply template on excel file exported using oledb ?

Mar 22 2021 4:18 AM
Hi,
 
I have exported data into excel file using oledb since I cannot use interop or any third party library. Here is my code
  1. ''' <summary>  
  2. ''' Export datagridview's data contained in an data table to excel file  
  3. ''' </summary>  
  4. ''' <param name="dataTable">DataGridView's datatable</param>  
  5. ''' <param name="XLPath"> Excel File Path with xlsx extension</param>  
  6. Private Shared Sub ExportToExcel(ByVal dataTable As DataTable, ByVal XLPath As String)  
  7.   
  8. Dim connStr = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" + XLPath + ";Extended Properties='Excel 8.0;HDR = YES';"  
  9.   
  10. Using connection As OleDbConnection = New OleDbConnection(connStr)  
  11. connection.Open()  
  12. Using command As OleDbCommand = New OleDbCommand()  
  13. command.Connection = connection  
  14. Dim columnNames As New List(Of String)  
  15. Dim tableName As String = dataTable.TableName  
  16.   
  17. If dataTable.Columns.Count <> 0 Then  
  18. For Each dataColumn As DataColumn In dataTable.Columns  
  19. columnNames.Add(dataColumn.ColumnName)  
  20. Next  
  21. Else  
  22. tableName = If(Not String.IsNullOrWhiteSpace(dataTable.TableName), dataTable.TableName, Guid.NewGuid().ToString())  
  23. command.CommandText = $"CREATE TABLE [{tableName}] ({String.Join(",", columnNames.[Select](Function(c) $"[{c}] VARCHAR").ToArray())});"  
  24. command.ExecuteNonQuery()  
  25. End If  
  26. If dataTable.Rows.Count <> 0 Then  
  27. For Each row As DataRow In dataTable.Rows  
  28. Dim rowValues As List(Of String) = New List(Of String)()  
  29. For Each column As DataColumn In dataTable.Columns  
  30. rowValues.Add(If((row(column) IsNot Nothing AndAlso Not row(column).Equals(DBNull.Value)), row(column).ToString(), String.Empty))  
  31. Next  
  32. command.CommandText = $"INSERT INTO [{tableName}]({String.Join(",", columnNames.[Select](Function(c) $"[{c}]"))}) VALUES ({String.Join(",",  
  33. rowValues.[Select](Function(r) $"'{r}'").ToArray())});"  
  34. command.ExecuteNonQuery()  
  35. Next  
  36. End If  
  37. End Using  
  38. connection.Close()  
  39. End Using  
  40. 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.

Answers (3)