Author: Nosa Lee
Original Address: https://www.seeksunslowly.com/operation-excel-xlsx-slowly
To reprint this article, please indicate the source, thank you.
_____________________________________

Recently, I tried to import data from Excel to a ListView with Detail view in .NET, there are about 3,000 records, I found that is is very slowly (took about 10 minutes). I think users cannot endure it, and I cannot accept it also, but this is a necessary function and cannot be removed, so I must find a solution.

Among them, the way to read the data form Excel document is: line by line and cell by cell. The key code as below:

[cc lang=”vbnet”]
……
For r% = 1 To rows ‘ The outer circle for traversing rows, r = row.
For c% = 0 To 9 ‘ The inner circle for traversing columns, c = column.
fields(c) = sheet.Cells(r, c + 1).Text
Next
Next
……
[/cc]

After searched the Internet, I found that most people say “use Excel file as a data source, and then read its data by ADO.NET, will be much faster”.
I had not tested this method due to I cannot accept this solution also: I have never used any database and related components in whole software, adding extra components for such a import function is not acceptable, I must use Excel related objects to solve it.

Now, I can only solve this problem by myself. Continued to record the macros in Excel and analyze the macro code, I found a way to get all data from Excel file without reading cell by cell.

I think this method should be able to improve importing efficiency substantially, because except access the Excel application and workbook, I only need to access a sheet once to get all data in it.
After testing, the method is feasible, efficiency is very good.

The key is using sheet.Range(“A1:X#”).Value.
The Range method of sheet object will return all data in the specified scope: a two-dimensional array as Object type, first dimension is rows, second is columns, the lower bounds of each dimension is 1 (specified by Excel, is not similar to most other programming languages).
With this method, everything becomes so simple, now I provide you with the complete and usable VB 2008 code, please adjust the detail (such as columns count) when use it.
Note: this solution is not only for VB 2008, but also appropriate for most other programming languages, such as C#, Delphi, Java, VB, VC, PB, and so on, because you can operate Excel by Object in them.

[cc lang=”vbnet”]
Private Sub ImportFromExcel(ByVal fn$)

‘ Read data from Excel file, fn – Excel file name.

On Error Resume Next

Dim excel As Object ‘ Excel application.
Dim wb As Object ‘ Excel workbook.
Dim sheet As Object ‘ Workbook sheet.
Dim rows% ‘ The valid rows of current sheet.
‘ Use to retrieve Excel data.
‘ Note: must be a two-dimensional array as Object type (the lower bounds of each dimension is 1).
Dim eData(,) As Object

excel = CreateObject(“Excel.Application”)
If excel Is Nothing Then
MsgBox(“Failed to open Excel application, please make sure you have installed it properly.”, _
MsgBoxStyle.Exclamation)
Return
End If

wb = excel.Workbooks.Open(fn, , True) ‘ Open in read-only mode.

‘ User canceled or verified failed (maybe need you to input password or show other dialog box).
If wb Is Nothing Then GoTo CLS

sheet = wb.Worksheets(1)
rows = sheet.UsedRange.Rows.Count ‘ Get the valid rows of current sheet.

‘ Read the data of whole sheet in one time by using Range method of sheet object.
eData = sheet.Range(“A1:C” & rows.ToString).Value

” Simply print the read data. You should convert them to the specific business code in your project.
For i% = 1 To rows
For j% = 1 To 3
Debug.Print(eData(i, j)
Next
Next
”

CLS:
wb.Close() ‘ Close workbook.
excel.Quit() ‘ Quit Excel.

End Sub
[/cc]

At last, one more thing, the Range method is suitable for writing data to Excel file.
Now I do not give you the complete code, the core part as below:
[cc lang=”vbnet”]
‘ Use to write data to Excel file.
‘ Note: must be a two-dimensional array as Object type (the lower bounds of each dimension is 1).
Dim eData(1 To 100, 1 To 3) As Object
…… ‘ Prepare eData.
sheet.Range(“A1:C100”).Value = eData
…… ‘ The finishing work.
[/cc]

【赞赏 / Reward】