Since your data range (A1:D28) does not have any specific formatting (e.g. cell shading color, etc.) applied, so it won’t do anything with the existing data range. Please try the suggested sample code segment as it will efficiently accomplish the task. Let us know if you still have any issue.
The data range (A1:D28) currently does not have a specific formatting but will have the formatting going forward, and we want to make sure only the current data range maintains the formatting it holds and remove all the formatting outside of the range. Does that help?
Could you please provide a sample Excel file (containing specific formatting for data range). This might help to devise the code segment precisely.
@Amjad_Sahi will not be able to share the exact workbook I will be working on, but the best I can do is this.
OldTest.zip (352.9 KB)
Basically want to get the formatting cleared from all the cells except the range (A1:D28)
@Joel_Peter,
In your template file, the styles are applied on both the whole Row and Column. To remove them entirely, please try code:
Style empty = wb.CreateStyle();
sheet.Cells.Style = empty;
for(int i=0; i<28; i++)
{ //this code can keep the formats for those cells in range A1:D28 if their styles inherite from the Row/Column. If those cells has their own style which is different from corresponding Row/Column, this code is needless
for(int j=0;j<4;j++)
{
Cell c = sheet.Cells[i, j];
c.SetStyle(c.GetStyle());
}
}
StyleFlag sf = new StyleFlag();
sf.All = true;
IEnumerator en = sheet.Cells.Columns.GetEnumerator();
while (en.MoveNext())
{
Column col = (Column)en.Current;
col.ApplyStyle(empty, sf);
}
en = sheet.Cells.Rows.GetEnumerator();
while (en.MoveNext())
{
Row row = (Row)en.Current;
row.ApplyStyle(empty, sf);
}
@johnson.shi, Thank you for your reply.
But sheet.Cells.Rows.GetEnumerator(), Does not go to all the rows on excel i.e it does not go until row number 1048576.
Can you let me know if I am missing something
@Joel_Peter,
For the enumerator of Row/Column/Cell, it only enumerates those objects which have been instantiated. For your situation, most of row objects in range 1:1048576 have not been instantiated(and it is sure your template file’s size will be increased significantly if all those objects are instantiated). For those rows, they have no custom formatting, so you do not need to change/clear settings for them.
But the requirement that I am trying to implement is to remove all the formats outside of the range of actual data cells, so that it does not look odd to the person receiving the excel and viewing it. Does that help?
As we told you, to apply empty formatting/style to those unnecessary cells, they need to be initialized which might increase your final file size to certain extent. The process to apply all cells (initialized and un-initialized) might take more time too. Anyways, you may try to loop though the whole matrix of cells.
By the way, one other approach to cope with it can be: Add a new sheet to your workbook. Copy your data range (e.g., A1:D28) from “Model” sheet to it via Range.Copy() method. Now remove the sheet “Model” via WorksheetCollection.RemoveAt() method. Finally rename your new sheet as “Model”. This might help you to accomplish your task efficiently.
@Joel_Peter,
And with the code I post here ClearFormats does not work on all cells - #9 by johnson.shi, can you get the expected result or not? We think it should be the simplest way for your requirement. If your case is more complicated than the one shown in the provided template file, please send us the new files(template file and the file to show the expected result) so we can check whether we can help more.
Please find the template, final template (after executing the code), and the ExpectedFinalTemplate.xlsx is what we want the code to do
This is not an excel way of doing things right?, So I am a bit hesitant to do this approach. Any idea why ClearFormats is not doing performing as desired on the appropriate range.
@johnson.shi, Also noticed that column enumerator runs from 0 to 18 and row enumerator runs from 0 to 675, Not sure how this code would remove styles for all the rows and columns.
Yes, you are right this is a kind of workaround.
As we told you, in your case, the formatting is applied to whole rows/cols, therefore using ClearFormats to clear the range(s) cannot remove the formatting of the whole rows.
Probably, you forgot to attach the attachments. Please zip the files and attach the zipped archive. We will check it soon.
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.