CODEWITHSUNDEEP
windowsvb.netexcel
Naveenraja Subramaniam
Naveenraja Subramaniam

Reputation: 365

CPU usage goes to 100% while reading from excel file?

I created a VB.Net application that will read from excel file and put the data into a table. I used an excel sheet which has 3 columns and 65000 rows. Before starts reading the excel my machine's CPU Usage is around 15%, but during reading the CPU Usage jumps upto 95%. I don't know why it is happening? Can someone help me in this issue? The following is the code i'd written:

Private Sub readFromExcel(ByVal fileName As String, ByVal sheetName As String)
    Dim connString As String = "data source=XE; user=test; password=test"
    Dim con As New OracleConnection(connString)
    Dim str1 As String
    Dim str2 As String
    Dim str3 As String
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    xlApp = New Excel.ApplicationClass
    xlWorkBook = xlApp.Workbooks.Open(fileName)
    xlWorkSheet = xlWorkBook.Worksheets(sheetName)
    Dim x As Integer
    Dim y As Integer
    Dim i As Integer
    x = xlWorkSheet.Rows.Count()
    y = xlWorkSheet.Columns.Count()
    Try
        For i = 1 To x - 1
            'MsgBox(xlWorkSheet.Cells(i, 0).value)
            str1 = xlWorkSheet.Cells(i, 1).value
            str2 = xlWorkSheet.Cells(i, 2).value
            str3 = xlWorkSheet.Cells(i, 3).value
            insertData()
        Next
    Catch ex As Exception
        MsgBox(ex.Message())
    Finally
        con.Close()
        xlWorkBook.Close()
    End Try
End Sub

Private Sub insertData()
    Dim str As String
    str = "insert into test_import values('" + str1 + "'," + str2 + "," + str3 + ")"
    Dim cmd As New OracleCommand()
    cmd.CommandText = str
    cmd.Connection = con
    cmd.ExecuteNonQuery()
End Sub

thx in advance.

Upvotes: 1

Views: 2956

Answers (3)

vic
vic

Reputation: 450

Use get_range(cell1,cell2) method to get the cells value.

You can use it to take the cell value by row, by column, or take all cells value in one go.

Keep watch the CPU usage when you adjust the code to read the cell value either by row, by column, or take all cells value in one go.

Upvotes: 1

Charles Williams
Charles Williams

Reputation: 23520

Its much faster to read the 195000 cells into an Object array in one go and then loop the object array. (There is a very high overhead for each .Net call to the Excel object model)

Upvotes: 3

Hans Passant
Hans Passant

Reputation: 941525

This is entirely normal. A program only doesn't burn 100% core when it gets bogged down by I/O. Reading from a disk or network card, that blocks a program while the operating system supplies the data. Your code doesn't bog down like that, you are asking it to do a bunch of work. Getting 195,000 cell values one by one just takes a while. Excel is an out-of-process COM server so every cell read requires two CPU context switches. You can optimize it a bit by using a Range instead. Or by running it on a machine with a two-core CPU so it only shoots up to 50%.

Feature, not a bug.

Upvotes: 4

Related Questions