Solved: read/write excel file too slowly in C#, Java, VB, Delphi, VC and other programming languages

Posted by NosaLee in .NET Programming, Common Programming on 08-04-2012. Tags:

Author: Nosa Lee
Original Address: http://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:

1
2
3
4
5
6
7
......
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
......

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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

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:

1
2
3
4
5
6
' 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.

Post a comment