Question How to apply template on excel file exported using oledb ?

Rash24agg

New member
Joined
Mar 19, 2021
Messages
3
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

VB.NET:
''' <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
        
        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.
 
Last edited by a moderator:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,457
Location
Sydney, Australia
Programming Experience
10+
I have taken the time to format your code properly. Please post it properly in the first place to avoid that waste of time.

By the way, while it won't hurt, there's no point closing your connection explicitly. The whole point of a Using block is that the object you create gets disposed at the end and disposing a database connection closes it.
 

Rash24agg

New member
Joined
Mar 19, 2021
Messages
3
Programming Experience
Beginner
I have taken the time to format your code properly. Please post it properly in the first place to avoid that waste of time.

By the way, while it won't hurt, there's no point closing your connection explicitly. The whole point of a Using block is that the object you create gets disposed at the end and disposing a database connection closes it.
Thanks for your suggestion, but, this won't help me in my situation . Please tell me how can I fix my problem.
 
Top Bottom