Question How to start exporting data to an excel file from a particular row of excel sheet?

[Rash24agg]

Hi everyone,
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 :

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

I wanna ask two question:
1. Why the data is being inseted from the end of the template?
2. 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 any one can help me.
.Net Framework 4.8 is used.

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

 

[jmcilhinney]

1. Why the data is being inseted from the end of the template?

You're treating the worksheet like a database table, which circumvents much of the functionality of Excel. When you insert a record into a database table, it gets appended after all the existing records. Every row of your worksheet that is part of the template is basically a record that is already in the database table, so anything you insert will be appended after that.

2. Is there any way I can insert the data from 3rd row without overwriting the first two rows?

I don't work with Excel very much but I suspect that you cannot do what you want using ADO.NET. You would probably have to Excel Automation for XLS or XLSX. That basically means remote-controlling an instance of the Excel application, so you can basically do in code whatever you can do through the GUI. You may also be able to use the OpenXML SDK if it's an XLSX file. That newer format is basically XML data inside a ZIP file and the SDK allows you to work with that XML using an Office-oriented API.

 

[jmcilhinney]

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

Just noticed this. Does OpenXML count as a third-party library, given that it's not part of standard .NET but it is from Microsoft?

 

[jmcilhinney]

Thinking about ADO.NET further, I guess it may be possible for you to query the sheet first and then update the existing rows with new values. In that case, you would use an OleDbDataAdapter to populate a DataTable and then use the same adapter to save changes back again. To be frank, as you're saving multiple records, you should have been using a data adapter and a DataTable already. I'm really not sure whether that would work or not but it's worth a try.

 

[jmcilhinney]

In it's simplest form, using a data adapter would look something like this:

Using connection As New OleDbConnection("connection string here")
    Dim adapter As New OleDbDataAdapter("SQL query here", connection)
    Dim builder As New OleDbCommandBuilder(adapter)
    Dim table As New DataTable

    connection.Open()
    adapter.Fill(table)

    'Edit table here.

    adapter.Update(table)
End Using

I'm not really sure whether a command builder would work in this case. If not, you'd have to create your own InsertCommand with appropriate parameters:

Using connection As New OleDbConnection("connection string here")
    Dim command As New OleDbCommand("SQL INSERT statement here", connection)

    command.Parameters.Add("@ColumnName", OleDbType.VarChar, 50, "ColumnName")

    Dim adapter As New OleDbDataAdapter("SQL query here", connection) With {.InsertCommand = command}
    Dim table As New DataTable

    connection.Open()
    adapter.Fill(table)

    'Edit table here.

    adapter.Update(table)
End Using