none
VB.Net Read An Excel File

    Question

  • Dear all,

    I'm using VB.Net's Excel component to read Excel file, but the performance is totally slow, since it has to loop through cells to get values. Reading a file containing one sheet with 1162 rows and 105 columns (which is small size compared to my general requirement) already costs more than 5 minutes.

    1. I want to ask if any of you have suggestion on reading such an Excel file with better performance, except OleDB, cuz most compies here have vesion problem?
    2. Is it normal that it tooks a long time for Excel component to read Excel file, or is it that I implement the code in a wrong way?

    Thanks a lot for your help.
    Friday, July 25, 2008 5:52 AM

Answers

  • Working with Excel is not necessarily the speediest of operations. If the process is hanging up your UI and you would like to avoid that, you can run your Excel functions on a different thread.

    http://msdn.microsoft.com/en-us/library/hybbz6ke.aspx
    Friday, July 25, 2008 7:36 PM
  • I think nmadd's suggestion will definitely help you in improving the performance.

    I would summarize as follows.

    1. Consider using a separate Thread as nmadd suggested.

    2. Consider reading the excel in ranges. Select a block of rows (say 100 rows at a time) and put the blocks into an array and then process. Then take the next 100 rows.
    Saturday, July 26, 2008 2:39 AM
  • Hello

    If all you are doing is a fetch of all rows and you need speed, then export the data into a CSV (Comma delimited) and read the data in through a text stream.  You can automate the export through Excell. The speed difference will astound you.  Office Interop is just plain slooooooooooow.

    Ibrahim
    Saturday, July 26, 2008 8:03 PM

All replies

  • You can use screenupdating and interactive property to get some performance improvement. I assume you are making the excel visible and doing all the operations.

    For more information, visit

    http://msdn.microsoft.com/en-us/library/bb221605.aspx 

    http://msdn.microsoft.com/en-us/library/bb177670.aspx

    Few queries.

    a. Do you have macros / formulaes defined in the excel sheet?
    b. Are you doing just fetching or are you updating the cell values as well?
    Friday, July 25, 2008 12:46 PM
  • Hi Siva,

    What I do is actually read in an excel file to get information and then save the information in a class for further process.
    So, the performance in reading the excel file is too slow. When my application is reading the excel file, it will "hang" there until it finishes the reading process.

    I'd like to know if there's any improvements for reading Excel file. Since my application would include a LOT of excel file upload >__<

    Ans:
    1. My excel doesn't have macros/formulas, only text.
    2. I am just fetching >__<"

    Thanks a lot~~
    Friday, July 25, 2008 6:22 PM
  • Working with Excel is not necessarily the speediest of operations. If the process is hanging up your UI and you would like to avoid that, you can run your Excel functions on a different thread.

    http://msdn.microsoft.com/en-us/library/hybbz6ke.aspx
    Friday, July 25, 2008 7:36 PM
  • I think nmadd's suggestion will definitely help you in improving the performance.

    I would summarize as follows.

    1. Consider using a separate Thread as nmadd suggested.

    2. Consider reading the excel in ranges. Select a block of rows (say 100 rows at a time) and put the blocks into an array and then process. Then take the next 100 rows.
    Saturday, July 26, 2008 2:39 AM
  • nmadd, siva, thanks for the help. I'll try the 2 options on Monday.
    • Edited by yugho Saturday, July 26, 2008 1:02 PM i lost to read the other solution option :)
    Saturday, July 26, 2008 1:01 PM
  • Hello

    If all you are doing is a fetch of all rows and you need speed, then export the data into a CSV (Comma delimited) and read the data in through a text stream.  You can automate the export through Excell. The speed difference will astound you.  Office Interop is just plain slooooooooooow.

    Ibrahim
    Saturday, July 26, 2008 8:03 PM
  • If you need to achieve good performance during reading, but do not want to use OLEDB, you can try .NET managed reader from my web site. It does not require OLEDB providers or Excel. It is developed in .NET and reads files on binary level. That allows component to achieve good performance


    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, October 27, 2010 10:37 AM
  • Hi, in my opinion if you require speed, great performances then I would avoid using excel automation. As suggested, if you can use CSV format then definitely go with it, I don’t believe anything can yield a better results than reading a plain CSV file.

    But if do not have choose and you have to read Excel file in .NET, then you can try using this VB.NET Excel component. It has a fast execution time and I had no issues with reading a large XLSX and XLS files with it.

    Tuesday, November 19, 2013 10:23 AM