Performance when adding cells

I have a 8760 * 52 spreadsheet in a .CSV file. When I calculate values and add them to 12 additional columns, it takes approximately 5 minutes. Here I add the data 1 column at a time. The calculation itself takes very few seconds.
for (int j = 53; j < 65; ++j)
for (int i = 4; i <= workSheet.Cells.MaxRow; ++i)
double db = CalculationOnSomeCells();
workSheet.Cells[i, j].PutValue(db);

I have tried to speed up, by storing the result data in a double [,] array, and then adding data row-wise. And now it takes about 2 minutes.
int dataColumn = 53;
for (int j = 0; j < 12; ++j)

for (int i = 4; i <= workSheet.Cells.MaxRow; ++i)
{

double db = CalculationOnSomeCells();

db_array[i,j] = db;
}


for (int i = 4; i <= workSheet.Cells.MaxRow; ++i)
{
for (int j = 0; j < col; ++j)
{
workSheet.Cells[i, dataColumn + j].PutValue(db_array[i, j]);
}
}



I have tried to speed things up further, by using the Cells.ImportArray(double[,], row, column), and now it still takes approximately 2 minutes:
int dataColumn = 53;

for (int j = 0; j < 12; ++j)

                for (int i = 4; i <= workSheet.Cells.MaxRow; ++i)<br>                        {<br>

                               double db = CalculationOnSomeCells();<br>

                        db_array[i,j] = db;<br>                        }<br>


workSheet.Cells.ImportArray(db_array, 4, dataColumn);


Is there anyway to speed up this process?

Yours
Lars Hammer

Hi Lars,

Thanks for considering Aspose.

Well, 8760 * 52 spreadsheet is a huge volume of spreadsheets. Aspose.Cells is reliable to process large volume of data. Please see the following tips if these can enhance your performance:

Performance mode and Tips

Regards

Amjad Sahi

Aspose Nanjing Team

Thanks for your response

I actually did use your performancetips (my second try)Smile [:)]. Is there any way to make this go faster. Can I somehow preallocate the number of cells I want to use in a worksheet? Or is there a speedy way to copy data into a spreadsheet?

Yours
Lars Hammer

What's the size of your output file? And what's the configuration of you machine?

I run the following test code:

Workbook excel = new Workbook();
Cells cells = excel.Worksheets[0].Cells;
DateTime start = DateTime.Now;

for(int i = 0; i < 8760; i ++)
{
for(int j = 0; j < 52; j ++)
{
cells[i, j].PutValue("abc" + i.ToString() + "," + j.ToString());
}
}

DateTime end = DateTime.Now;

TimeSpan span = end - start;

Console.WriteLine(span.TotalSeconds);

excel.Save("d:\\abc.xls");

end = DateTime.Now;

span = end - start;

Console.WriteLine(span.TotalSeconds);

It creates a file more than 12MB and it only takes 4 seconds to populate data and 15 seconds to create the file. The following is my laptop's configuration:

*WinXP

*PentiumM 1.4G

*512MB RAM

My laptop configuration is:
*WinXP
*PentiumM 1.6G
*1GB RAM

I read in a ā€˜;ā€™ separated file with a size of app. 5 MB. On the first 3 lines is some information, on the fourth line is a description of the data, and then the data. The data is in 52 columns and 8760 rows.

Then I calculate some values and add 13 more columns and it takes minutes. I have made a simple version of this, where I perform 2 steps:

1) Add 52 columns with 8760 rows
2) Add 1 column (instead of 13) with 8760 rows

Step 1 takes app 4 seconds and step 2 takes app 14 seconds. Adding 13 columns will then take minutes. Here is my sample program:

Aspose.Cells.Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;

DateTime start = DateTime.Now;

for (int i = 0; i < 8760; ++i)
{
for (int j = 0; j < 52; ++j)
{
double x = 7.6;
cells[i, j].PutValue(x);
}
}

DateTime inbetween = DateTime.Now;

for (int i = 0; i < 8760; ++i)
{
for (int j = 0; j < 1; ++j)
{
double x = 7.6;
cells[i, j+52].PutValue(x);
}
}
DateTime end = DateTime.Now;

TimeSpan span = end - start;
TimeSpan firstpart = inbetween - start;

string message =
"Total TimeSpan: " + span.TotalSeconds.ToString() + ā€œ\nā€ +
"First timespan: " + firstpart.TotalSeconds.ToString();
MessageBox.Show(message);


1) Add 52 columns with 8760 rows
2) Add 1 column (instead of 13) with 8760 rows

Yes, the second action takes much more time.

Aspose.Cells internally arrange cells sorted by row first and column second. So second action will cause many insertion actions which slow down the program.

To improve the performance, you can try:

1)Create a two dimension array with 8760 * 53 columns

2)Populate data into this array

3)Use ImportTwoDimensionArray method to import data as a whole

It should be much faster.

For optimization I now perform the following steps (and it works)
- I create a workbook with the old data.
- Then I copy the workbook data into a double [][]
- Then I calculate new data into the same double [][]
- Then I create a new workbook wb and import the data using wb.ImportTwoDimensionArray

Yours
Lars Hammer