Start exporting data to an excel file from a particular row of sheet

Hi everyone,

I have excel installed in my system which is used by my project and I am trying to export the data to an excel file which has a template like this:

I want the data to be inserted from the 3rd row without affecting first two rows same as here

But in the actual output first row is being overwritten by column names and data is inserted from where the template ends :

Here is the code by which I was inserting the data :

'''
''' 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
End Using
End Sub

I wanna ask two question:

Why the data is being inseted from the end of the template?
Is there any way I can insert the data from 3rd row without overwriting the first two rows?

I tried to do it with OPENROWSET but I did not understand how to apply it in my situation so I was getting syntax error.

I would really appreciate if anyone can help me.

Note: I cannot use interop or any third party library.

2 Replies

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

Rashmi Gupta5y ago
Hi Mr. Amit !
Thanks for your reply.
Well I used this in place of insert query:

If dataTbl.Rows.Count <> 0 Then
For i = 0 To dataTbl.Rows.Count - 1
Dim oval As String = String.Empty
For j = 0 To dataTbl.Columns.Count - 1
If j <> 0 Then
oval += ",'" & dataTbl.Rows(i)(j).ToString() & "'"
Else
oval += "'" & dataTbl.Rows(i)(j).ToString() & "'"
End If
Next
sql = $"INSERT INTO [info$A{i + 3}:H{i + 3}] Values ({oval});"
cmd.CommandText = sql
Try
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Next

And it solved my problem.
0
Amit Gupta5y ago
It is the default behaviour, the connection string with HDR = yes specifies that excel has header and it then writes from the next to header rows.

I am not sure if it works but have you tried inserting 2 blank rows to your datatable?

Give it a trail and let me know
0