Conditional formatting in excel

Microsoft Office Excel allows you to layout your text data in a spreadsheet or workbook format. You can bring information and data from various files and locations to a single destination, for them to be crunched and analyzed on a single file. Define various color shades, bold, italics in the excel worksheet to highlight important data You may select an appropriate coloring scheme for quick analysis. Specified below are common uses of excel files

  • Data entry and storage
  • Collection and Verification of Business Data
  • Administrative and managerial duties
  • Accounting and budgeting
  • Data Analysis
  • Reporting + Visualizations
  • Forecasting

Conditional Formatting

Conditional formatting is a feature in many spreadsheet applications. It allows you to apply specific formatting to cells that meet certain criteria. It is most often used as color-based formatting to highlight, emphasize, or differentiate among data and information stored in a spreadsheet. If the conditions are false, the cell range is not formatted. As an example, you can have numbers below thousand in red color and numbers over thousand in blue.

Given below are popular conditional formatting visualizations are available

  • Background Color Shading (of cells)
  • Foreground Color Shading (of fonts)
  • Data Bars
  • Icons (which have 4 different image types)
  • Values

Specified below topics are elaborated in more details

Get Conditional Formatting of the Worksheet

Aspose.Cells Cloud provides the capabilities to read conditional formatting information from excel worksheets. The GetWorksheetConditionalFormattings method of API enables you to read the formatting details from the excel worksheet. The first step is to generate a JWT token based on your personalized Client ID and Client Secret. For more information, please visit How to Obtain JWT token using a Client ID and Client Secret key

The cURL command is shown below, which helps to read formatting details from an excel worksheet

curl -X GET "https://api.aspose.cloud/v3.0/cells/conditional.xlsx/worksheets/Sheet1/conditionalFormattings" \
-H  "accept: application/json" \
-H  "authorization: Bearer <JWT Token>"

C# .NET

Add Conditional Formatting

Microsoft Excel offers three presets for Conditional Formatting, Data Bars, Color Scales, and Icon Sets, and Aspose.Cells Cloud API supports all these types. Specify different parameters of the condition including the Type, Operator, Style, Cell Area, etc.

cURL command

curl -X PUT "https://api.aspose.cloud/v3.0/cells/conditional.xlsx/worksheets/Sheet1/conditionalFormattings?cellArea=C4%3AC6" \
-H "accept: application/json" \
-H "authorization: Bearer <JWT Token>" \
-H "Content-Type: application/json" \
-d "{ \"link\": { \"Href\": \"string\", \"Rel\": \"string\", \"Title\": \"string\", \"Type\": \"string\" }, \"Priority\": 2, \"Type\": \"CellValue\", \"StopIfTrue\": true, \"AboveAverage\": { \"IsAboveAverage\": true, \"IsEqualAverage\": true, \"StdDev\": 0 }, \"ColorScale\": { \"MaxCfvo\": { \"IsGTE\": true, \"Type\": \"string\" }, \"MaxColor\": { \"A\": 100, \"R\": 200, \"G\": 200, \"B\": 0 }, \"MidCfvo\": { \"IsGTE\": true, \"Type\": \"string\" }, \"MidColor\": { \"A\": 0, \"R\": 0, \"G\": 0, \"B\": 0 }, \"MinCfvo\": { \"IsGTE\": true, \"Type\": \"string\" }, \"MinColor\": { \"A\": 0, \"R\": 0, \"G\": 0, \"B\": 0 } }, \"DataBar\": { \"AxisColor\": { \"A\": 0, \"R\": 0, \"G\": 0, \"B\": 0 }, \"AxisPosition\": \"string\", \"BarBorder\": { \"Color\": { \"A\": 0, \"R\": 0, \"G\": 0, \"B\": 0 }, \"Type\": \"string\" }, \"BarFillType\": \"string\", \"Color\": { \"A\": 0, \"R\": 0, \"G\": 0, \"B\": 0 }, \"Direction\": \"string\", \"MaxCfvo\": { \"IsGTE\": true, \"Type\": \"string\" }, \"MaxLength\": 0, \"MinCfvo\": { \"IsGTE\": true, \"Type\": \"string\" }, \"MinLength\": 0, \"NegativeBarFormat\": { \"BorderColor\": { \"A\": 0, \"R\": 0, \"G\": 0, \"B\": 0 }, \"BorderColorType\": \"string\", \"Color\": { \"A\": 0, \"R\": 0, \"G\": 0, \"B\": 0 }, \"ColorType\": \"string\" }, \"ShowValue\": true }, \"Formula1\": \"string\", \"Formula2\": \"string\", \"IconSet\": { \"CfIcons\": [ { \"ImageData\": \"string\", \"Index\": 0, \"Type\": \"string\" } ], \"Cfvos\": [ { \"IsGTE\": true, \"Type\": \"string\" } ], \"IsCustom\": true, \"Reverse\": true, \"ShowValue\": true, \"IconSetType\": \"string\" }, \"Operator\": \"string\", \"Style\": { \"link\": { \"Href\": \"string\", \"Rel\": \"string\", \"Title\": \"string\", \"Type\": \"string\" }, \"Font\": { \"Color\": { \"A\": 0, \"R\": 0, \"G\": 0, \"B\": 0 }, \"DoubleSize\": 0, \"IsBold\": true, \"IsItalic\": true, \"IsStrikeout\": true, \"IsSubscript\": true, \"IsSuperscript\": true, \"Name\": \"string\", \"Size\": 0, \"Underline\": \"string\" }, \"Name\": \"string\", \"CultureCustom\": \"string\", \"Custom\": \"string\", \"BackgroundColor\": { \"A\": 220, \"R\": 10, \"G\": 120, \"B\": 0 }, \"ForegroundColor\": { \"A\": 0, \"R\": 0, \"G\": 0, \"B\": 0 }, \"IsFormulaHidden\": true, \"IsDateTime\": true, \"IsTextWrapped\": true, \"IsGradient\": true, \"IsLocked\": true, \"IsPercent\": true, \"ShrinkToFit\": true, \"IndentLevel\": 0, \"Number\": 0, \"RotationAngle\": 0, \"Pattern\": \"None\", \"TextDirection\": \"Context\", \"VerticalAlignment\": \"Center\", \"HorizontalAlignment\": \"General\", \"BorderCollection\": [ { \"LineStyle\": \"string\", \"Color\": { \"A\": 120, \"R\": 0, \"G\": 0, \"B\": 0 }, \"BorderType\": \"BottomBorder\", \"ThemeColor\": { \"ColorType\": \"string\", \"Tint\": 0 }, \"ArgbColor\": -16777216 } ], \"BackgroundThemeColor\": { \"ColorType\": \"string\", \"Tint\": 0 }, \"ForegroundThemeColor\": { \"ColorType\": \"string\", \"Tint\": 0 } }, \"Text\": \"string\", \"TimePeriod\": \"string\", \"Top10\": { \"IsBottom\": true, \"IsPercent\": true, \"Rank\": 0 }}"

Given below C# code snippet adds the conditional formatting in the Excel worksheet:

C# .NET

Clear all Conditional Formatting

Aspose.Cells Cloud enables you the capabilities to clear the formatting of conditional formatting. The DeleteWorksheetConditionalFormattings method provides you the leverage to clear the conditional formatting from an excel spreadsheet. The given below cURL command can be used to accomplish this requirement

cURL command

curl -X DELETE "https://api.aspose.cloud/v3.0/cells/conditional.xlsx/worksheets/Sheet1/conditionalFormattings" \
-H "accept: application/json" \
-H "authorization: Bearer <JWT Token>"

C# .NET

The above method removes all the conditional formatting and if you want o to remove any particular formatting, please try using DeleteWorksheetConditionalFormatting method. This method accepts the index number for formatting conditions in an Excel spreadsheet.

The given below command removes conditional formatting at index 1 (where the index value starts from 0).

cURL command

curl -X DELETE "https://api.aspose.cloud/v3.0/cells/conditional.xlsx/worksheets/Sheet1/conditionalFormattings/1" \
-H "accept: application/json" \
-H "authorization: Bearer <JWT Token>"
Formatting removed from worksheet.

Image 1:- Formatting removed from excel worksheet.

Remove Conditional Formatting for certain Area

If you require to remove the conditional formatting from a specific area, please use DeleteWorksheetConditionalFormattingArea method. We need to specify the startRow, startColumn, totalRows, and totalColumns information to accomplish the requirements. Please note that row and column indexes start with 0.

cURL command

curl -X DELETE "https://api.aspose.cloud/v3.0/cells/conditional.xlsx/worksheets/Sheet1/conditionalFormattings/area?startRow=4&startColumn=1&totalRows=3&totalColumns=1" \
-H "accept: application/json" \
-H "authorization: Bearer <JWT Token>"

Conclusion

Aspose.Cells Cloud API is an amazing product and it offers the capabilities to create, manipulate, and transform MS Excel and OpenOffice sheet files to other supported formats. No product installation is required.

For more information, please visit Aspose.Cells Cloud home page. And for information regarding the installation and configuration of programming language-specific SDKs, please visit How to install Aspose.Cloud SDKs.