SetOutlineBorder works very slowly

Aspose.Cells Product Family
1

For example, I have a code like this :

DateTime dtBegin = DateTime.Now;

Excel excel = new Excel();
Worksheet sheet = excel.Worksheets[0];

for ( int i = 0; i < 65530; i = i + 5 )
{
sheet.Cells[i,0].PutValue( i );
Range range = sheet.Cells.CreateRange(i, (byte)0, 5, 5);

range.SetOutlineBorder( BorderType.TopBorder, CellBorderType.Thick,
Color.Black );

range.SetOutlineBorder( BorderType.BottomBorder, CellBorderType.Thick,
Color.Black );

range.SetOutlineBorder( BorderType.LeftBorder, CellBorderType.Thick,
Color.Black );

range.SetOutlineBorder( BorderType.RightBorder, CellBorderType.Thick,
Color.Black );
}

DateTime dtEnd = DateTime.Now;
TimeSpan tsDiff = dtEnd.Subtract(dtBegin);

label1.Text = string.Format("{0} min {1} sec {2} ms", tsDiff.Minutes, tsDiff.Seconds, tsDiff.Milliseconds);


excel.Save(@"C:\rem1.xls");
excel = null;

dtEnd = DateTime.Now;
tsDiff = dtEnd.Subtract(dtBegin);

label2.Text = string.Format("{0} min {1} sec {2} ms", tsDiff.Minutes, tsDiff.Seconds, tsDiff.Milliseconds);


I have got the following results :

memory usage 150300 kb
time before saving 6 sec 906 ms
time after saving 28 sec 593

Are there any ways to reduce both memory usage and saving time ? Each time for every range I may have a different styles.

2

Following is my code to do the same thing:

DateTime start = DateTime.Now;
Excel excel = new Excel();
Worksheet sheet = excel.Worksheets[0];

Style upleftStyle = excel.Styles[excel.Styles.Add()];
upleftStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;
upleftStyle.Borders[BorderType.TopBorder].Color = Color.Black;
upleftStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thick;
upleftStyle.Borders[BorderType.LeftBorder].Color = Color.Black;


Style uprightStyle = excel.Styles[excel.Styles.Add()];
uprightStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;
uprightStyle.Borders[BorderType.TopBorder].Color = Color.Black;
uprightStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thick;
uprightStyle.Borders[BorderType.RightBorder].Color = Color.Black;

Style topStyle= excel.Styles[excel.Styles.Add()];
topStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;
topStyle.Borders[BorderType.TopBorder].Color = Color.Black;

Style leftStyle= excel.Styles[excel.Styles.Add()];
leftStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thick;
leftStyle.Borders[BorderType.LeftBorder].Color = Color.Black;

Style rightStyle= excel.Styles[excel.Styles.Add()];
rightStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thick;
rightStyle.Borders[BorderType.RightBorder].Color = Color.Black;


for(int i = 0; i < 65530; i ++)
{


for(int j = 0; j < 5; j ++)
{
switch( i % 5)
{
case 0:
switch(j)
{
case 0:
sheet.Cells[i, j].PutValue( i );
sheet.Cells[i, j].Style = upleftStyle;
break;
case 1:
case 2:
case 3:
sheet.Cells[i, j].Style = topStyle;
break;
case 4:
sheet.Cells[i, j].Style = uprightStyle;
break;
}
break;
case 1:
case 2:
case 3:
case 4:
switch(j)
{
case 0:
sheet.Cells[i, j].Style = leftStyle;
break;
case 1:
case 2:
case 3:
break;
case 4:
sheet.Cells[i, j].Style = rightStyle;
break;
}

break;
}
}
}

DateTime end = DateTime.Now;

TimeSpan span = end - start;

Console.WriteLine(span.TotalSeconds);


start = DateTime.Now;

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

end = DateTime.Now;

span = end - start;

Console.WriteLine(span.TotalSeconds);

I got following result with your code:

time before saving :11.84s
time after saving: 26.38s

And result with my code:
time before saving :0.62s
time after saving: 13.61s

I didn't test memory but I am sure my code used much less memory.
The tip is assigning same Style object to as many cells as possible.

Why doesn't SetOutlineBorder assign same style object to as many cells as possible? Because if it does, when you change a style of a cell at the border, many cells' style will be also changed.

3

Ok, your code works faster, but it doesn't help me, beacause I have to use Range objects and I don't know dimensions of it before. Each time I get new cells dimensions ( it maybe one cell or few cells ). How I can make a borders for that conditions ?

4

You can create 11 predefined border styles: UpLeft, LowerRight, Left,Right,Top, Bottom, LeftTopBottom, RightTopBottom, TopLeftRight, BottomLeftRight, Full.

And when you get new cells dimensions, you can dynamically set the borders.

For example, if it's one cell, set the style to Full. If the range is A1 and B1, set A1 to LeftTopBottom and B1 to RightTopBottom, etc.

It's a little complex to work in this way. However, if you have many many cells to set borders, it's faster and uses less memory.

5

Ok, I'll try it, thanks anyway !!!