Hi,
I have exported data into excel file using oledb since I cannot use interop or any third party library. Here is my code :
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) $"
command.ExecuteNonQuery()
Next
End If
End Using
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.
Edit : New situation arose :-
Now,I am able to insert the data by implemnting these three things :
1. changing the given file to xlsx format
2. keeping the name of the excel sheet same as name of the datatable
3. appending "$" in name of the datatable.
The data should be polpulated from third row of excel file like
this without overwriting first 2 row as they contain information which is template specific, but the problem is that 1st row is being overwritten by column names; also, data is being populated after 39th line just like
this.
Now, I have some questions:
1.What should I do so that the excel file can be populated from 3rd row without overwriting the excel file?
2.I read about OPENROWSET from
here:
it can export data from the desired row but I did not understand how to use it in my situation?
What I have tried:
After adding data to 'rowvalues', instead of using "Insert into ", I tried to go with OPENROWSET as follows:
sql = $"insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database={XLPath};',{tableName}$') select * from {tableName}$"
command.CommandText = sql
command.ExecuteNonQuery()
but failed.