1

I've been trying to write a program that could read data from an excel file. After few hours of trying, it's finally working. However, I've noticed it to be working very slow especially when it comes to large amount of rows and columns. Tried it on my other PC just to check maybe it's a PC problem but unfortunately it is definitely slow on the others as well.

Is there something that I need to change or maybe remove or add?

Here is my code:

Public Sub LoopExcel()
    Dim colfrom As Integer = Asc(txtColFrom.Text)
    Dim colto As Integer = Asc(txtColTo.Text)

    Dim rowfrom As Integer = Integer.Parse(txtRowFrom.Text)
    Dim rowto As Integer = Integer.Parse(txtRowTo.Text)

    For rowindex = rowfrom To rowto
        For colindex = colfrom To colto
            Dim str As String = OpenExcelGetData(txtFileName.Text, rowindex, Convert.ToChar(colindex)).ToString
            Console.WriteLine(str)
        Next
    Next
End Sub

Public Function OpenExcelGetData(ByVal fileNameAndPath As String, ByVal rowIndex As Integer, ByVal columnIndex As String) As Object
    Dim oExcelApp As New Excel.ApplicationClass
    Dim oExcelBook As Excel.Workbook
    Dim oExcelSheet As Excel.Worksheet
    Dim sheetNumber As Integer = 1 '1-based array

    Dim oData As Object = Nothing

    Try
        oExcelBook = oExcelApp.Workbooks.Open(fileNameAndPath)
        oExcelSheet = CType(oExcelBook.Worksheets(sheetNumber), Excel.Worksheet)

        'Read data

        Dim excelRange As String = columnIndex & rowIndex.ToString()

        oData = oExcelSheet.Range(excelRange).Value

    Catch exp As COMException
        MessageBox.Show(exp.Message)

    Catch exp As Exception
        MessageBox.Show(exp.Message)

    End Try

    Return oData
End Function
James Z
  • 12,209
  • 10
  • 24
  • 44
John Anthony
  • 67
  • 4
  • 1
    Probably you should post your question [here](https://codereview.stackexchange.com/), its a dedicated site for code reviewing. – Mrig Aug 27 '17 at 06:47
  • [Here](https://stackoverflow.com/a/16051/468973) is an alternative to using automation. – Magnus Aug 27 '17 at 09:23

1 Answers1

2

You should take in consideration that COM technology is slow by itself. You need to minimize the number of methods calls, cell-by-cell will be awfully slowly, maybe by range.

You might also think to a different solution that does not involve COM.

alex.pulver
  • 2,107
  • 2
  • 31
  • 31