Click here to Skip to main content
14,877,943 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hi everyone,
I have exported MS Access file's data to a datagridview and I am trying to export the datagridview's datatable to an excel file which has a template like this 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 just like this.
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 would really appreciate if any one can help me.

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

What I have tried:

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.
Posted
Updated 25-Mar-21 7:18am
v2
Comments
Richard MacCutchan 25-Mar-21 4:48am
   
How many times do you need to be told how to do this? You must read both sets of data from their sources and merge them internally before you export them to the Excel sheet.
Rash24Agg 25-Mar-21 5:17am
   
My boss told me that your solution is not what we want. That is why I posted it...
See! I am not a fool.......
Richard MacCutchan 25-Mar-21 5:36am
   
Yes, and I have repeatedly told you what you need to do to get the correct result. But hey, it's your time that you are wasting.
Gerry Schmitz 25-Mar-21 13:21pm
   
The "template" is a "dumb idea"; it buys you nothing. Export to a csv.
Rash24Agg 26-Mar-21 3:54am
   
Sorry,My boss denied to use csv.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900