Apply Shading to Alternate Rows and Columns with Conditional Formatting
Apply Shading to Alternate Rows & Columns using Conditional Formatting
This article makes use of Excel’s built-in functions such as ROW, COLUMN & MOD. Here are little details of these functions for a better understanding of the code snippet provided ahead.
- ROW() function returns the row number of a cell reference. If the reference is omitted, it assumes that the reference is the cell address in which the ROW function has been entered in.
- COLUMN() function returns the column number of a cell reference. If the reference is omitted, it assumes that the reference is the cell address in which the COLUMN function has been entered in.
- MOD() function returns the remainder after a number is divided by a divisor, where the first parameter to the function is the numeric value whose remainder you wish to find and the second parameter is the number used to divide into the number parameter. If the divisor is 0, then it will return the #DIV/0! error.
Let us start writing some code to accomplish the goal with the help of Aspose.Cells for Java API.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
String dataDir = Utils.getDataDir(ApplyShadingToAlternateRowsAndColumns.class); | |
/* | |
* Create an instance of Workbook Optionally load an existing spreadsheet by passing its stream or path to Workbook | |
* constructor | |
*/ | |
Workbook book = new Workbook(); | |
// Access the Worksheet on which desired rule has to be applied | |
Worksheet sheet = book.getWorksheets().get(0); | |
// Add FormatConditions to the instance of Worksheet | |
int index = sheet.getConditionalFormattings().add(); | |
// Access the newly added FormatConditions via its index | |
FormatConditionCollection conditionCollection = sheet.getConditionalFormattings().get(index); | |
// Define a CellsArea on which conditional formatting will be applicable | |
CellArea area = CellArea.createCellArea("A1", "I20"); | |
// Add area to the instance of FormatConditions | |
conditionCollection.addArea(area); | |
// Add a condition to the instance of FormatConditions. For this case, the condition type is expression, which is based on | |
// some formula | |
index = conditionCollection.addCondition(FormatConditionType.EXPRESSION); | |
// Access the newly added FormatCondition via its index | |
FormatCondition formatCondirion = conditionCollection.get(index); | |
// Set the formula for the FormatCondition. Formula uses the Excel's built-in functions as discussed earlier in this | |
// article | |
formatCondirion.setFormula1("=MOD(ROW(),2)=0"); | |
// Set the background color and patter for the FormatCondition's Style | |
formatCondirion.getStyle().setBackgroundColor(Color.getBlue()); | |
formatCondirion.getStyle().setPattern(BackgroundType.SOLID); | |
// Save the result on disk | |
book.save(dataDir + "output.xlsx"); |
The following snapshot shows the resultant spreadsheet loaded in Excel application.
In order to apply the shading to alternative columns, all you have to do is to change the formula =MOD(ROW(),2)=0 as =MOD(COLUMN(),2)=0, that is; instead of getting the row index, modify the formula to retrieve the column index. The resultant spreadsheet, in this case, will look like the following image.