How to remove ff from cell e.g. local format- conditional format- comments- contents etc

Aspose.Cells Product Family
1

Hi,

How to remove everything from the cell and cell area without deleting the cell.

For example using following method, I can remove validations only;

CellArea ca = new CellArea();

ca.StartRow = 10;

ca.EndRow = 20;

ca.StartColumn = 0;

ca.EndColumn = 250;

localSheet.Validations.RemoveArea(ca);

Is there any method which removes local format, conditional format, comments, contents, formula etc from one single cell or group of cells (range) without deleting and shifting the cells from the worksheet.

Thanks in advance.

~/ Vinay

2

Hi Vinay,

You may use following methods to removes formatting, conditional format, comments, contents, formula etc from one single cell or a range to cells.

WorkSheet.Cells.Clear() //Clear all cell objects
WorkSheet.Cells.ClearContents() //Clears contents of a range
WorkSheet.Cells.ClearFormats() //Clears formatting of a range
WorkSheet.Cells.ClearRange() //Clears contents and formatting of a range
worksheet.Comments.RemoveAt() //Remove a cell comment

For more details, please follow the links below.

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/aspose.cells.namecollectionmethods.html

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/aspose.cells.cellsmethods.html


Thanks,

3

I tried to use ClearFormats, but it doesn’t seem to remove conditional formats from the cell Range?


Is there any thing I am missing here?

Primarily I need to remove all of the above mentioned contents from the cell or range of cell to decrease the file size.

If possible, please share a code snippet which demonstrates removing Conditional formatting from cell range.
4

Hi,

You may use Worksheet.ConditionalFormattings.RemoveArea() method to remove all conditional formatting of a given range.

e.g
worksheet.ConditionalFormattings.RemoveArea(0,0,10,10);

Thank you.

5

I don't see this available in aspose version (5.1.0.0) we are using. Refer screen shot attached.

Is there any other alternative solution other than getting your latest version?

6

Hi,

Well, I am sorry there is no way but to use latest versions e.g v5.1.2 as we incorporated this API recently.

Thank you.


7

If not from cell area, is it possible to remove it from individual cells using the current API?, if yes, please provide a sample.

8

Hi Vinay,

You can use following line of codes:

Workbook objWB1 = new Workbook("C:\\test_ConditionalFormat.xlsx");
Worksheet objWS1 = objWB1.Worksheets[0];
FormatConditionCollection fcs = objWS1.ConditionalFormattings[0];
fcs.RemoveArea(0);
objWB1.Save("C:\\output.xlsx", SaveFormat.Xlsx);

Following line of code will also work:
//objWB1.Worksheets[0].ConditionalFormattings.RemoveAt(0);

For further details please follow the links below:

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/aspose.cells.conditionalformattingcollection.html

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/aspose.cells.conditionalformattingcollectionmethods.html

Thanks,

9

Thanks for the reply Salman...

FormatConditionCollection fcs = objWS1.ConditionalFormattings[0];
fcs.RemoveArea(0);

Or

//objWB1.Worksheets[0].ConditionalFormattings.RemoveAt(0);

What does 0 refer to in this context? For example if there are Cell A1, A2 and A3 on the sheet and if I wish to remove Conditional Formatting from the sheet for Cell A3?

10

Hi Vinay,

0 is the index of the ConditionalFormattings Collection. From this collection you can get FormatCondition object.

Thanks,

11

If that is so, how would I identify the conditional formatting applied to a perticular cell And how does this method helps me remove the formatting from a single cell of my choice?

May be I bit confused here...

while specifying 0 I can understand that you are using a collection object... What I am not clear about is...

If I were to remove conditional formatting from a perticular cell, then how do I do that. Please see the inline comments in the example you provided me... Please clarify the comments in Red.

//getting the workbook instance form the excel sheet...
Workbook objWB1 = new Workbook("C:\\test_ConditionalFormat.xlsx");
//getting the worksheet instance form the loaded workbook...
Worksheet objWS1 = objWB1.Worksheets[0];
//Getting the format condition collections from the worksheet object
//How does following code specify that I am accessing the format conditions in cell say "A10"... Element at index 0 could be any cell at Nth row and Nth column as per my understanding? Please clarify!
FormatConditionCollection fcs = objWS1.ConditionalFormattings[0];
fcs.RemoveArea(0);

12

Hi Vinay,

Please use the updated version of Aspose.Cells.dll V5.1.2.2. Using this version you will be able to find the RemoveArea function. Code snippet is given below:

objWB1.Worksheets[0].ConditionalFormattings.RemoveArea

I am afraid that using DLL version V5.1.0.0 it is very much difficult to get and remove a particular cell.

Thanks,

13

hmmm... okay...

Thanks, Vinay