none
Excel Interop Help

    Question

  • Hey All,

    I am having an issue returning an entire row of data rather then an individual cell. What am I doing wrong?

                For i As Integer = 2 To counta
                    If CStr(xlWorkSheet2.Cells(i, 21).value) = Form3.Label1.Text Then
                        t1 = CStr(xlWorkSheet2.Cells(i, xlWorkSheet2.Range(i).EntireRow).Value)
                        xlWorkSheet2.
                        Form3.DataGridView1.Rows.Add(t1)
                        TextBox2.Text = "Row " & i
                        ProgressBar1.Value = ProgressBar1.Value + 1
                    Else
                        ProgressBar1.Value = ProgressBar1.Value + 1
                    End If
                Next

    Is there a way to return an entire row of data instead of just a cell?

    Thanks!

    Friday, January 11, 2019 4:47 PM

Answers

  • Hello,

    In the following code sample I iterate rows and "used columns" in tangent with a simple extension method.

    Imports System.Runtime.InteropServices
    Imports Excel = Microsoft.Office.Interop.Excel
    Public Class ExcelOperations
        Public Sub OpenExcelRead(fileName As String, sheetName As String)
            Dim proceed As Boolean = False
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing
    
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(fileName)
    
            xlApp.Visible = False
            xlWorkSheets = xlWorkBook.Sheets
    
            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                If xlWorkSheet.Name = sheetName Then
                    proceed = True
                    Exit For
                End If
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
            Next
            If proceed Then
                xlCells = xlWorkSheet.Cells
    
                Dim TempRange As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
                Dim RowsUsed As Integer = TempRange.Row
                Dim ColsUsed As Integer = TempRange.Column
                Console.WriteLine($"Row: {RowsUsed} Col: {ColsUsed}")
    
                For rowIndex As Integer = 1 To ColsUsed
                    xlCells = xlWorkSheet.Range($"A{rowIndex}:{ColsUsed.ExcelColumnName}{rowIndex}")
                    Dim dataArray = xlCells.Value()
                Next
    
                Marshal.FinalReleaseComObject(TempRange)
                TempRange = Nothing
    
                Marshal.FinalReleaseComObject(xlCells)
                xlCells = Nothing
            End If
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            ReleaseExcelObject(xlCells)
            ReleaseExcelObject(xlWorkSheets)
            ReleaseExcelObject(xlWorkSheet)
            ReleaseExcelObject(xlWorkBook)
            ReleaseExcelObject(xlWorkBooks)
            ReleaseExcelObject(xlApp)
        End Sub
        Private Sub ReleaseExcelObject(ByVal excelObject As Object)
            Try
                If excelObject IsNot Nothing Then
                    Marshal.ReleaseComObject(excelObject)
                    excelObject = Nothing
                End If
            Catch ex As Exception
                excelObject = Nothing
            End Try
        End Sub
    End Class
    Module ExcelExtensions1
        <Runtime.CompilerServices.Extension()>
        Public Function ExcelColumnName(Index As Integer) As String
            Dim chars = New Char() _
                    {
                        "A"c, "B"c, "C"c, "D"c, "E"c, "F"c, "G"c, "H"c, "I"c,
                        "J"c, "K"c, "L"c, "M"c, "N"c, "O"c, "P"c, "Q"c, "R"c,
                        "S"c, "T"c, "U"c, "V"c, "W"c, "X"c, "Y"c, "Z"c
                    }
    
    
            If Not Index = 0 Then
                Index -= 1
            End If
            Dim columnName As String
            Dim quotient = Index \ 26
            If quotient > 0 Then
                columnName = ExcelColumnName(quotient) + chars(Index Mod 26)
            Else
                columnName = chars(Index Mod 26).ToString()
            End If
            Return columnName
        End Function
    
    End Module
    

    When the data is read, some values in dataArray will be nothing as per the yellow highlighted cells so you would need to check for them.

    Note the used columns code comes from the following code sample where I also show how to get the last used row too.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by k7s41gx Saturday, January 12, 2019 9:44 PM
    Friday, January 11, 2019 8:22 PM
    Moderator

All replies

  • Hello,

    In the following code sample I iterate rows and "used columns" in tangent with a simple extension method.

    Imports System.Runtime.InteropServices
    Imports Excel = Microsoft.Office.Interop.Excel
    Public Class ExcelOperations
        Public Sub OpenExcelRead(fileName As String, sheetName As String)
            Dim proceed As Boolean = False
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing
    
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(fileName)
    
            xlApp.Visible = False
            xlWorkSheets = xlWorkBook.Sheets
    
            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                If xlWorkSheet.Name = sheetName Then
                    proceed = True
                    Exit For
                End If
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
            Next
            If proceed Then
                xlCells = xlWorkSheet.Cells
    
                Dim TempRange As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
                Dim RowsUsed As Integer = TempRange.Row
                Dim ColsUsed As Integer = TempRange.Column
                Console.WriteLine($"Row: {RowsUsed} Col: {ColsUsed}")
    
                For rowIndex As Integer = 1 To ColsUsed
                    xlCells = xlWorkSheet.Range($"A{rowIndex}:{ColsUsed.ExcelColumnName}{rowIndex}")
                    Dim dataArray = xlCells.Value()
                Next
    
                Marshal.FinalReleaseComObject(TempRange)
                TempRange = Nothing
    
                Marshal.FinalReleaseComObject(xlCells)
                xlCells = Nothing
            End If
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            ReleaseExcelObject(xlCells)
            ReleaseExcelObject(xlWorkSheets)
            ReleaseExcelObject(xlWorkSheet)
            ReleaseExcelObject(xlWorkBook)
            ReleaseExcelObject(xlWorkBooks)
            ReleaseExcelObject(xlApp)
        End Sub
        Private Sub ReleaseExcelObject(ByVal excelObject As Object)
            Try
                If excelObject IsNot Nothing Then
                    Marshal.ReleaseComObject(excelObject)
                    excelObject = Nothing
                End If
            Catch ex As Exception
                excelObject = Nothing
            End Try
        End Sub
    End Class
    Module ExcelExtensions1
        <Runtime.CompilerServices.Extension()>
        Public Function ExcelColumnName(Index As Integer) As String
            Dim chars = New Char() _
                    {
                        "A"c, "B"c, "C"c, "D"c, "E"c, "F"c, "G"c, "H"c, "I"c,
                        "J"c, "K"c, "L"c, "M"c, "N"c, "O"c, "P"c, "Q"c, "R"c,
                        "S"c, "T"c, "U"c, "V"c, "W"c, "X"c, "Y"c, "Z"c
                    }
    
    
            If Not Index = 0 Then
                Index -= 1
            End If
            Dim columnName As String
            Dim quotient = Index \ 26
            If quotient > 0 Then
                columnName = ExcelColumnName(quotient) + chars(Index Mod 26)
            Else
                columnName = chars(Index Mod 26).ToString()
            End If
            Return columnName
        End Function
    
    End Module
    

    When the data is read, some values in dataArray will be nothing as per the yellow highlighted cells so you would need to check for them.

    Note the used columns code comes from the following code sample where I also show how to get the last used row too.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by k7s41gx Saturday, January 12, 2019 9:44 PM
    Friday, January 11, 2019 8:22 PM
    Moderator
  • Thanks for your response Karen,

    I have a working datatable add row, it just taking forever on a spreadsheet with 311k rows. Is there a way to implement a faster approach? I even tried using a filter and special cells within the worksheet but it still is exremely slow. I even experimented with an oledb fill but I can't track the progress in a progress bar that way. Code below.

    DATATABLE INTEROP

                For i As Integer = 2 To counta
                    If CStr(xlWorkSheet2.Cells(i, 21).value) = Form3.Label1.Text Then
                        t1 = CStr(xlWorkSheet2.Cells(i, 1).value)
                        t2 = CStr(xlWorkSheet2.Cells(i, 2).value)
                        t3 = CStr(xlWorkSheet2.Cells(i, 3).value)
                        t4 = CStr(xlWorkSheet2.Cells(i, 4).value)
                        t5 = CStr(xlWorkSheet2.Cells(i, 5).value)
                        t6 = CStr(xlWorkSheet2.Cells(i, 6).value)
                        t7 = CStr(xlWorkSheet2.Cells(i, 7).value)
                        t8 = CStr(xlWorkSheet2.Cells(i, 8).value)
                        t9 = CStr(xlWorkSheet2.Cells(i, 9).value)
                        t10 = CStr(xlWorkSheet2.Cells(i, 10).value)
                        t11 = CStr(xlWorkSheet2.Cells(i, 11).value)
                        t12 = CStr(xlWorkSheet2.Cells(i, 12).value)
                        t13 = CStr(xlWorkSheet2.Cells(i, 13).value)
                        t14 = CStr(xlWorkSheet2.Cells(i, 14).value)
                        t15 = CStr(xlWorkSheet2.Cells(i, 15).value)
                        t16 = CStr(xlWorkSheet2.Cells(i, 16).value)
                        t17 = CStr(xlWorkSheet2.Cells(i, 17).value)
                        t18 = CStr(xlWorkSheet2.Cells(i, 18).value)
                        t19 = CStr(xlWorkSheet2.Cells(i, 19).value)
                        t20 = CStr(xlWorkSheet2.Cells(i, 20).value)
                        t21 = CStr(xlWorkSheet2.Cells(i, 21).value)
                        t22 = CStr(xlWorkSheet2.Cells(i, 22).value)
                        t23 = CStr(xlWorkSheet2.Cells(i, 23).value)
                        t24 = CStr(xlWorkSheet2.Cells(i, 24).value)
                        t25 = CStr(xlWorkSheet2.Cells(i, 25).value)
                        t26 = CStr(xlWorkSheet2.Cells(i, 26).value)
                        t27 = CStr(xlWorkSheet2.Cells(i, 27).value)
                        t28 = CStr(xlWorkSheet2.Cells(i, 28).value)
                        t29 = CStr(xlWorkSheet2.Cells(i, 29).value)
                        t30 = CStr(xlWorkSheet2.Cells(i, 30).value)
                        t31 = CStr(xlWorkSheet2.Cells(i, 31).value)
                        t32 = CStr(xlWorkSheet2.Cells(i, 32).value)
                        t33 = CStr(xlWorkSheet2.Cells(i, 33).value)
                        TextBox2.Text = "Row " & i
                        RAW.Rows.Add(t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15, t16, t17, t18, t19, t20, t21, t22, t23, t24, t25, t26, t27, t28, t29, t30, t31, t32, t33)
                        ProgressBar1.Value = ProgressBar1.Value + 1
                    Else
                        ProgressBar1.Value = ProgressBar1.Value + 1
                    End If
                Next

    OLE FILL

        Public Sub OXER()
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim RAW As New DataTable
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "2018RAW.xlsx") & "';Extended Properties=Excel 12.0; HDR=YES")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [2018RAW$]", MyConnection)
            MyCommand.Fill(RAW)
            MyConnection.Close()
            Form3.DataGridView1.DataSource = RAW
        End Sub

    Is there perhaps a way to track the progress of the fill command to the datatable RAW? I know I can filter the datatable later but it would be simpler IMO to bring the data in pre-filtered so less data has to be imported.

    Excel Worksheet Contains 311,000 Rows and 33 columns per row. So you can imagine how large the file is.


    • Edited by k7s41gx Saturday, January 12, 2019 9:20 AM code
    Saturday, January 12, 2019 8:40 AM
  • Wanted to post an example for size in comparison. If i filter the table by column 21 = "1072" this drops the total row count down to 53,000 rows instead of 311,000 rows. Is there maybe a way to do this with OLE and track the import progress?
    Saturday, January 12, 2019 8:44 AM
  • Hello,

    Now mentioning an additional requirement is not how it's done here. If my reply solution the initial question then mark it as answered and start a new question for working with large Excel data. 

    I'm willing to assist but in a new thread. After closing this thread out start the new thread and in the new thread please provide the following.

    • Number of rows/columns (repeating yourself I know).
    • Indicate you want progress.
    • Are there other operations needed besides reading into a DataGridView e.g. editing etc.
    • Are these .xls, .xlsx or can be both.

    I will address various ideas including OleDb, advantages and disadvantages for different approaches.

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, January 12, 2019 10:41 AM
    Moderator