Formatting whole column

Hi

I call CreateRange with “H:H” and set the style of this new range. After that, the MaxDisplayRange is A1:I1048576, which results in an out of memory when saving to pdf.

How can I change the style of the whole column?

Thank you
Marco

Hi Marco,


Thank you for contacting Aspose support.

Please note, if you creating the range as “H:H” the range should refer to H1:H1058576. Anyway, I tried your presented scenario with following piece of code against Aspose.Cells for .NET 8.7.1.1 (attached), however, I am not able to see any exception. Moreover, you can format a column using the Column.ApplyStyle as demonstrated below. Please also review the article on this subject.

C#

var book = new Workbook();
var style = book.CreateStyle();
style.Font.Name = “Arial”;
style.Font.Size = 24;
//Test code to replicate OutOfMemoryException by applying style to range
var range = book.Worksheets[0].Cells.CreateRange(“H:H”);
Console.WriteLine(range.RefersTo);
range.ApplyStyle(style, new StyleFlag() { All = true });

//Format a column using Column.ApplyStyle
book.Worksheets[0].Cells.Columns[CellsHelper.ColumnNameToIndex(“H”)].ApplyStyle(style, new StyleFlag() { All = true });
book.Save(dir + “output.xlsx”);

Your sample works, but when I want to add gridlines, I have the memory problem.

var book = new Workbook();

var style = book.CreateStyle();

style.Font.Name = "Arial";

style.Font.Size = 24;

//Test code to replicate OutOfMemoryException by applying style to range

var range = book.Worksheets[0].Cells.CreateRange("H:H");

Console.WriteLine(range.RefersTo);

range.ApplyStyle(style, new StyleFlag() { All = true });


// Gridlines

style = book.CreateStyle();

style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Hair;

style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Hair;

style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Hair;

style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Hair;

book.Worksheets[0].Cells.MaxDisplayRange.ApplyStyle(style, new StyleFlag { Borders = true });


//Format a column using Column.ApplyStyle

book.Worksheets[0].Cells.Columns[CellsHelper.ColumnNameToIndex("H")].ApplyStyle(style, new StyleFlag() { All = true });

book.Save(@"C:\Temp\output.xlsx");

Hi Marco,


I have executed your exact code against the latest revision of Aspose.Cells for .NET 8.7.1.1 (assembly shared in my previous response) and I am not able to see the OutOfMemory error. However, I have noticed that the memory spikes up to 800+ MB which is justified because your code is initializing a very large range of cells (A1:H1058576) which needs a lot of memory to process and dump the result on disc. If you are not already using the latest release, please give it a try on your side. Moreover, you may set the Memory Preferences using the Cells.MemorySetting property before creating any range in order to lower down the memory consumption to some extent.

That said, I am bit confused regarding your exact requirements. As per the title of this thread, you need to format a column, that can be accomplished using the Column.ApplyStyle. However, if you wish to apply the borders too then you should not be using the aforementioned method, instead Range.ApplyStyle suits the requirement. Please also note that MaxDisplayRange always starts from A1 (first cell in the worksheet) therefore if you have previously initialized all cells in a column then MaxDisplayRange will give you a huge range of cells starting from A1 to last row of specified column. I suggest you to use the Cells.CreateRange method by specifying the MaxDataRow & MaxDataColumns as parameters in order to limit the range size to cells which actually contain any data otherwise initializing empty cells will be waste of memory and processing time.

ok, that helps. Thank you.

Hi Marco,


You are most welcome. Please do not hesitate to contact us back in case you face any difficulty or have question for us. We will be glad to help you out.