Slow ImportArray performance

Aspose.Cells Product Family
1

Hi



I have a problem with poor performance when using ImportArray or PutValue in large spreadsheets. Some sample code is below:



int [] taker = new int [60];

int [] questions = new int [60];

int [] answers = new int [60];


    foreach (int takerID in

surveyCol.Keys)

{

        if (curRow % 2

== 0)

{

cells.Rows [curRow].Style.ForegroundColor = colours

[4];

}


        SurveyTaker

takerTmp = (SurveyTaker)surveyCol [takerID];

        if

(takerTmp.Demographics.ContainsValue (Convert.ToInt32 (dr [“id”])))

{

for (int i = 0; i < 60; i++)<br>
       
       
{<br>
       
       
    taker <img src="/community/emoticons/emotion-55.gif" alt="Idea [I]" /> = takerID;<br>
       
       
    questions <img src="/community/emoticons/emotion-55.gif" alt="Idea [I]" /> = i + 1;<br>
       
       
}<br>


foreach (int stockQuestionID in

takerTmp.StockAnswers.Keys)

{<br>
       
       
    answers [stockQuestionID - 1] =

(int)takerTmp.StockAnswers [stockQuestionID];

}<br>


cells.ImportArray (taker, curRow, curColumn, true);<br>
       
       
cells.ImportArray (questions, curRow,

(byte)(curColumn + 1), true);

cells.ImportArray (answers, curRow, (byte)(curColumn

  • 2), true);


    curRow += 60;

    }

    }



    If I comment out the curRow += 60 line, just after the three
    ImportArray calls, then the performance is blazingly quick, which leads
    me to believe that it is the allocating of space for all the cells that
    is slowing everything down. But it seems to slow down exponentially as
    the number of cells go up. I have 512 mb of ram in this machine. Would
    that be a problem? The spreadsheet in question has about 30000 rows,
    and about 70 columns, and takes about 15 minutes to generate. Is this
    normal for a spreadsheet of this size? I’m using the latest version of
    Aspose.Excel.



    Thanks

    Andrew

2

Hi Andrew,

15 minutes for 30000 rows and 70 columns is too slow. But could you use PutValue method when processing large amount of data? Or you can try to merge your data in a single array. Please put data first by row then by column, that will be much faster. For example, please put data in A1, B1, C1, A2, B2, C2 order, not A1, A2,B1,B2,C1,C2.

3

Hi



I’ve tried PutValue, which appears to give me much the same
performance. I can try refactoring a bit to fill in a full row first,
but this is a little awkward because of the way things are setup at the
moment, so is this gauranteed to increase performace? Also, I’m curious
as to why ImportArray would be slower than PutValue. With the array
method I can cut out about 2 million function calls to PutValue, in a
spreadsheet of this size. What is ImportArray doing internally that it
is slower than that?



Thanks

Andrew

4

Hi Andrew,

ImportArray internally calls Cell.PutValue method. All cells in a worksheet is kept in a list internally order by row first and by column second. So when you put value to cell, please strictly put value in this order.

Following sample code show the difference:

First method takes about 10 seconds and second method takes about 53 seconds in my machine.

static void CreateLargeData()
{
Excel excel = new Excel();

Cells cells = excel.Worksheets[0].Cells;

DateTime start = DateTime.Now;

for(int i = 0; i < 30000; i ++)
{
for(int j = 0; j < 70; j ++)
cells[i, j].PutValue(0);
}

DateTime end = DateTime.Now;

TimeSpan span = end -start;

Console.WriteLine(span.TotalSeconds);
}

static void CreateLargeData2()
{
Excel excel = new Excel();

Cells cells = excel.Worksheets[0].Cells;

int[] a = new int[600];

DateTime start = DateTime.Now;

for(int i = 0; i < 50; i ++)
{
for(int j = 0; j < 70; j ++)
{
cells.ImportArray(a, i * 600, (byte)j, true);

}
}

DateTime end = DateTime.Now;

TimeSpan span = end -start;

Console.WriteLine(span.TotalSeconds);
}

5

Hi Laurence



Thats great. Thanks for the examples. I’ll give it a go and see what happens.



Cheers

Andrew