Applying style to all cells except first cell takes lots of time and throws exception

[aspose.kishansojitra]

When I try to apply black color to all cells except first cell my application throws an error of System.OutOfMemoryException,

It creates a huge file.

Here is my code:

//Instantiate a new Workbook.

Workbook workbook = new Workbook();

//Get the first worksheet in the book.

Worksheet worksheet = workbook.Worksheets[0];

//This works good-Creates file with size of 7kb

//worksheet.Cells.ApplyStyle(new Style() { BackgroundColor = Color.Black, Pattern = BackgroundType.Solid, ForegroundColor = Color.Black }, new StyleFlag() { All = true });

//This takes time and throws an exception - SystemOutOfMemory

Range r = worksheet.Cells.CreateRange(1, 1, 1048575, 16383);

r.ApplyStyle(new Style() { BackgroundColor = Color.Black, Pattern = BackgroundType.Solid, ForegroundColor = Color.Black }, new StyleFlag() { All = true });

//Save the excel file.

workbook.Save("D:\\output.xlsx");

Is there any other fast and good way to do this?

[Amjad_Sahi]

Hi,

Thanks for providing us details and sample code segment.

Using the code segment:

//This takes time and throws an exception - SystemOutOfMemory

Range r = worksheet.Cells.CreateRange(1, 1, 1048575, 16383);

r.ApplyStyle(new Style() { BackgroundColor = Color.Black, Pattern = BackgroundType.Solid, ForegroundColor = Color.Black }, new StyleFlag() { All = true });

Well, surely, it will consume more memory as you are formatting such a huge range (which uses all the worksheet range/area). It is better you apply formatting to the data or initialized cells only. Also, you should apply style/formatting at the end after you have inserted/imported data or other objects into the cells. See the following code segment for your reference:

e.g

Sample code:

…

Range r = worksheet.Cells.MaxDisplayRange;

r.ApplyStyle(new Style() { Pattern = BackgroundType.Solid, ForegroundColor = Color.Black }, new StyleFlag() { All = true });

//Since you are applying solid cell shading color, so BackgroundColor = Color.Black would be useless.

Let us know if you still have any issue or query.

Thank you.

[aspose.kishansojitra]

Thanks for your quick response.

Actually, When I apply same thing in excel, It creates file of 7kb only. And when open it and save it using Aspose, It creates same 7kb file.

I have attached file here.

Is there any other method to complete this task as fast as possible?

Why Aspose.Cells is creating a huge file while Excel is creating a small file with same formatting and styles?

Thanks,

Kishan

[Amjad_Sahi]

Hi,

Thanks for the template file.

Please see the following sample code that would accomplish your task efficiently. The sample code is processed instantly and the output file’s size is also minimized:

e.g

Sample code:

//Instantiate a new Workbook.

Workbook workbook = new Workbook();

//Get the first worksheet in the book.

Worksheet worksheet = workbook.Worksheets[0];

//First apply style to the whole sheet.

worksheet.Cells.ApplyStyle(new Style() { BackgroundColor = Color.Black, Pattern = BackgroundType.Solid, ForegroundColor = Color.Black }, new StyleFlag() { All = true });

//Now apply formatting to the first column (A) and first row (1) with normal style as per your needs.

worksheet.Cells.ApplyColumnStyle(0, new Style() { BackgroundColor = Color.Empty, Pattern = BackgroundType.None, ForegroundColor = Color.Empty }, new StyleFlag() { All = true });

worksheet.Cells.ApplyRowStyle(0,new Style() { BackgroundColor = Color.Empty, Pattern = BackgroundType.None, ForegroundColor = Color.Empty }, new StyleFlag() { All = true });

workbook.Save(“e:\test2\out1.xlsx”);

Hope, this helps a bit.

Thank you.