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

'''
''' Export datagridview's data contained in an data table to excel file
'''
''' DataGridView's datatable
''' Excel File Path with xlsx extension
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
connection.Close()
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.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta5y ago
Try removing the existing Interop.excel dll reference from your project and nuget folder and re-install the reference with correct version and remove all the runtime access...64/32bit from control panel and do a system restart and install all the fresh one.

Note: Runtime access library must match the architecture of 32/64 bit with the office architecture
Rashmi Gupta5y ago
Hi ,
Thanks for paying attention.
I was formerly trying to use the interop library but it was giving exception as stated here:
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 you please suggest something which is according to specifications?
Amit Gupta5y ago
Oledb is used to read/write excel files only, it can't be used for formatting and templates unless use interop or third party tool like epplus etc