Inserting rows into existing Excel worksheet with OleDbConnection

Question

I'm building insert statements based on a list of data and a tab name. I have 4 tabs, the last 2 get data inserted successfully, the first 2 do not.

I commented out inserting into all tabs but one. The size of the excel file increases, but the rows are still blank. Any ideas?

Edit: For some reason, the Excel file I was using as a "blank template" had "empty" values in rows of the first 2 sheets. First one had "empty values" in the first 100K rows, seconds had empty values in the first 700-some rows. The data was being inserted after these rows, which explains why the file size was increasing. Now I'm getting "Operation must use an updateable query" when trying to insert.

Public Sub BuildReport(Of T)(tabName As String, dataList As IEnumerable(Of T))
    '// Setup the connectionstring for Excel 2007+ XML format
    '// http://www.connectionstrings.com/ace-oledb-12-0/ 

    If ((tabName.EndsWith("$") = True AndAlso m_TabList.Contains(tabName) = False) OrElse m_TabList.Contains(tabName & "$") = False) Then
        Throw New Exception(String.Format("The specified tab does not exist in the Excel spreadsheet: {0}", tabName))
    End If

    Using excelConn As New OleDbConnection(m_ConnectionString)

        excelConn.Open()

        Dim insertStatementList As IEnumerable(Of String) = BuildInsertStatement(Of T)(tabName, dataList)

        Using excelCommand As New OleDbCommand()
            excelCommand.CommandType = CommandType.Text
            excelCommand.Connection = excelConn

            For Each insertStatement In insertStatementList
                excelCommand.CommandText = insertStatement
                excelCommand.ExecuteNonQuery()
            Next

        End Using

    End Using

End Sub

Private Function BuildInsertStatement(Of T)(tabName As String, dataList As IEnumerable(Of T)) As IEnumerable(Of String)

    Dim insertStatementList As New List(Of String)

    Dim insertStatement As New StringBuilder()

    For Each dataItem As T In dataList
        Dim props As PropertyInfo() = GetType(T).GetProperties()
        insertStatement.Clear()
        insertStatement.AppendFormat("INSERT INTO [{0}$] ", tabName)

        Dim nameValueDictionary As New Dictionary(Of String, String)

        For Each prop As PropertyInfo In props
            Dim excelColumn As ExcelColumnAttribute = CType(prop.GetCustomAttributes(GetType(ExcelColumnAttribute), False).FirstOrDefault(), ExcelColumnAttribute)

            If (excelColumn IsNot Nothing) Then
                Dim value As Object = prop.GetValue(dataItem, Nothing)
                If (value IsNot Nothing AndAlso value.GetType() <> GetType(Integer) _
                    AndAlso value.GetType() <> GetType(Double) _
                    AndAlso value.GetType() <> GetType(Decimal) _
                    AndAlso value.GetType() <> GetType(Boolean)) Then
                    value = String.Format("""{0}""", value)
                ElseIf (value Is Nothing) Then
                    value = "NULL"
                End If
                nameValueDictionary.Add(excelColumn.ColumnName, value)
            End If
        Next

        Dim columList As String = String.Join(",", nameValueDictionary.Keys)
        Dim valueList As String = String.Join(",", nameValueDictionary.Select(Function(x) x.Value))

        insertStatement.AppendFormat("({0}) ", columList)
        insertStatement.AppendFormat("VALUES ({0})", valueList)

        insertStatementList.Add(insertStatement.ToString())

    Next

    Return insertStatementList

End Function

score 0 · Answer 1 · edited May 23 '17 at 12:05

For some reason, the Excel file I was using as a "blank template" had "empty" values in rows of the first 2 sheets. First one had "empty values" in the first 100K rows, second one had empty values in the first 700-some rows. The data was being inserted after these rows, which explains why the file size was increasing. Now I'm getting "Operation must use an updateable query" when trying to insert.

I found the answer to the second problem here: Operation must use an updateable query when updating excel sheet

Just needed to remove the "IMEX=1" from the extended properties of the connection string which I added in trying to troubleshoot the issue.

Inserting rows into existing Excel worksheet with OleDbConnection

1 Answers1