TestClearRange.zip (512.4 KB)
I have attached the sample C# code that does not work for ClearFormats, The code also has the sample Test.xlsx file that is used within the code.
Context: We would want to remove all formatting outside of the working range i.e as sample file (A1:D28). We tried all the possible ways of SetStyle, but it was time and memory consuming. Finally ended up using the following code which works partially and does not work for all the cells in the range.
CellArea caRightSideOfTable = CellArea.CreateCellArea("E1", "XFD1048576");
sheet.Cells.ClearFormats(caRightSideOfTable);
CellArea caBottomSideOfTable = CellArea.CreateCellArea("A29", "XFD1048576");
sheet.Cells.ClearFormats(caBottomSideOfTable);
Let me know if there is any method that works, also tried ClearRange method too. Nothing works as expected. Please help
@Joel_Peter,
The formatting you want to clear is applied to whole rows, so clearing the formatting of the range cannot remove the formatting of those rows. To remove row’s formatting, example code:
IEnumerator en = sheet.Cells.Rows.GetEnumerator();
Style empty = wb.CreateStyle();
StyleFlag sf = new StyleFlag();
sf.All = true;
while(en.MoveNext())
{
Row row = (Row)en.Current;
row.ApplyStyle(empty, sf);
}
Hi @johnson.shi,
Thank you for your quick response. As mentioned earlier, I want to apply style on a outside range, I do not want to apply or remove the formatting for (A1:D28), Looks like your code is trying to remove formatting on the entire row sets.
Also, I tried the code that you provided and the rows below 677 never got their formatting removed
@Joel_Peter,
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.
@Joel_Peter,
Yes, we noticed this. We will look into it and get back to you soon.
@Amjad_Sahi
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?
@Joel_Peter,
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?
@Joel_Peter,
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.
@Joel_Peter,
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.
Sorry, Here you go.ClearRange.zip (1021.6 KB)