Thanks for the resource files. We will evaluate and get back to you.
We evaluated your requirements using your template, current output and expected output files. We have logged a ticket with an id “CELLSNET-52496” to support your needs, i.e., change/clear the default style of row/column, but without changing the style settings of existing (data) cells efficiently. Once we support your requested feature, we will let you know.
@Joel_Peter,
We know your situation and requirement now. We are afraid currently there is no simple way to get the expected result because removing/replacing the custom style for Row/Column is required but ApplyStyle() method also will change the style for existing cells. We will add method Row.SetStyle()/Column.SetStyle() for your requirement. The new methods will change the default style for the Row/Column but do not change the style settings of existing cells. With the new methods, the code work for you will be like:
Style empty = wb.CreateStyle();
sheet.Cells.Style = empty;
for (int i = 0; i < 28; i++)
{
for (int j = 0; j < 4; j++)
{
Cell c = sheet.Cells[i, j];
style = c.GetStyle();
c.SetStyle(style);
}
}
IEnumerator en = sheet.Cells.Columns.GetEnumerator();
while (en.MoveNext())
{
Column col = (Column)en.Current;
col.SetStyle(empty);
}
en = sheet.Cells.Rows.GetEnumerator();
while (en.MoveNext())
{
Row row = (Row)en.Current;
row.SetStyle(empty);
}
//separate the range A1:D28 from the whole sheet to preserve its style settings
sheet.Cells.ClearFormats(CellArea.CreateCellArea("E1", "XFD28"));
sheet.Cells.ClearFormats(CellArea.CreateCellArea("A29", "XFD1048576"));
The new apis will be included into our next official version 23.1 which will be released in next month.
Thank you so much for the consideration, Will you be able to create the apis to be generic like the interop please?
Microsoft.Office.Interop.Excel.Range.Interior.ColorIndex = 0;
This is the alternate version on Interop which works perfectly fine, It does not care if it is cell formatting/row formatting/column formatting. It just removes the entire interior color from the range associated
Hi @johnson.shi, Can you explain the code please?, since what I can understand from the code is that sheet.Cells.Style = empty; clears off the formatting from all cells except for certain rows and columns.
and the sheet.Cells.Columns.GetEnumerator() runs only through the data columns and sheet.Cells.Rows.GetEnumerator() runs only through data rows. I understood this by printing Column.Index and Row.Index respectively within the while loop.
Let me know if my understanding is different from yours.
We will evaluate it if we could provide the similar API in Aspose.Cells.
@Joel_Peter,
In fact the function shown by interop is just our API Range.ApplyStyle() with the style flag which specifies only the color will be applied. However, because in your range the whole rows/columns have defined their default style, changing the cell’s style in the range need to instantiate all the cell objects and applied different style from the default one(inherited from default style of corresponding row/column or sheet) for them. And the range you need to change style is so large, so there are too many cell objects will be created. As we have said, such kind of operation will require quite a lot time and finally make the size of generated file increased significantly.
It also explains why we need to clear the default style of cells/rows/columns in the code I provide. After we have removed those default style settings, we can clear the formats for the specified range without instantiating most of cells in the range.
And your understanding is right, that is, sheet.Cells.Style=empty will remove the custom style defined for the sheet, enumeration of the rows/columns will remove the custom style defined for the whole row/column. For those rows/columns that are not in the enumeration, they have not been instantiated in the cells model, so they have no custom style settings and need no special operation.
The issues you have found earlier (filed as CELLSNET-52496) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi