Low performance when reading data from Excel workbook to ArrayList in C#

Question

0.00/5 (No votes)

See more:

Here's a problem description.

CONDITIONS: General idea is to read a lot of real numbers from MS Excel file and put them inro ArrayList for further processing. An excel workbook has only one worksheet. All the numbers are real and they are stored in one column. I read these numbers row by row and put them into ArrayList.

PROBLEM: the process takes too much time. Program spends about 2 minutes to fill an ArrayList with 10000 elements. Here's my code. I need your advise to make it faster. But the structure of the file cannot be modified. It's only possible to modify code. Help me please to make it faster.

C#

// Method GetExcelData opens 1 excel file, reads data row by row and adds
// it into the array of source Data Values (sourceDataValues in our case).
private void GetExcelData(string fullPath, ArrayList arrForValues)
{
    Excel.Application excelapp = new Excel.Application();
    excelapp.Visible = false;
    // to avoid appearing of Excel window on the screen
    Excel.Workbook excelappworkbook = excelapp.Workbooks.Open(
        fullPath,
        Type.Missing, Type.Missing, true, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing);
    Excel.Worksheet excelworksheet = (Excel.Worksheet)excelappworkbook.Worksheets.get_Item(1);
    Excel.Range excelcells = excelworksheet.UsedRange;
    uint rowsNum = 0;
    for (rowsNum = 1; rowsNum != excelcells.Rows.Count; rowsNum++)
    {
        arrForValues.Add((excelcells.Cells[rowsNum, 1] as Excel.Range).Value2);
    }
    excelappworkbook.Close(false, Type.Missing, Type.Missing);
    excelapp.Quit();
}

Posted 27-Oct-11 8:25am

Shalfey89

Add a Solution

Comments

Jimmanuel 27-Oct-11 16:20pm

This probably won't improve the performance much so I'll post as a comment instead of solution.

ArrayLists have been obsolete since .Net 2.0. A List is the preferred method; in addition to the strong typing the generics also perform better on value types which will probably help you out a little.

Also, if you know a rough estimate of how many items are in the spreadsheet you can declare the List with an initial capacity so that hopefully it won't need to be resized from all the Adds.

Sergey Alexandrovich Kryukov 27-Oct-11 22:11pm

More exactly, instead of ArrayList use System.Collections.Generic.List<double>. The type ArrayList would require type cast, which is not good.
--SA

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tim Groven · Accepted Answer · 2011-10-27T08:34:00

Solution 1

C#

object[,] worksheetValuesArray = excelcells.get_Value(Type.Missing);

Try that line to get all the values into an array at once. It is a lot faster. Then convert this to your ArrayList.

Posted 27-Oct-11 8:34am

Tim Groven

v2

Shalfey89 · Accepted Answer · 2011-10-29T02:06:00

Thank you very much. The problem is solved.
Everything is qute simple. First, we read all the range of current worksheet into simple two-dimension array - worksheetValuesArray. After that we put all the values from that array into our container, converting the type of elements to double. Here's the part of corrected solution:

C#

private void GetExcelData(string fullPath, List<double> arrForValues)
        {
            Excel.Application excelapp = new Excel.Application();
            excelapp.Visible = false;
            // to avoid appearing of Excel window on the screen
            Excel.Workbook excelappworkbook = excelapp.Workbooks.Open(
                fullPath,
                Type.Missing, Type.Missing, true, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
            Excel.Worksheet excelworksheet = (Excel.Worksheet)excelappworkbook.Worksheets.get_Item(1);
            Excel.Range excelcells = excelworksheet.UsedRange;
            object[,] worksheetValuesArray = excelcells.get_Value(Type.Missing);
            
            for (int col = 1; col < (worksheetValuesArray.GetLength(1)+1); col++)
            {
                for (int row = 1; row < (worksheetValuesArray.GetLength(0)+1); row++)
                {
                    arrForValues.Add((double) worksheetValuesArray[row, col]);
                }
            }
            excelappworkbook.Close(false, Type.Missing, Type.Missing);
            excelapp.Quit();
        }</double>