使用Aspose.Cells库在C#中应用条件格式阴影以交替显示行和列。通过调整这些条件,您可以更好地控制单元格的外观。
Contents
[
Hide
]
Aspose.Cells API 提供了为工作表对象添加和操作条件格式规则的方法。这些规则可以根据条件或规则以多种方式来设计,从而得到所需的格式。本文将演示使用 Aspose.Cells for Java API 来应用条件格式规则和 Excel 内置函数来为交替行和列添加底纹。
使用条件格式为交替行和列应用着色
本文使用Excel的内置函数,如ROW、COLUMN和MOD。以下是关于这些函数的一些细节,以更好地理解提供的代码段。
- **ROW()**函数返回单元格引用的行号。如果省略引用,则假定引用是输入ROW函数的单元格地址。
- **COLUMN()**函数返回单元格引用的列号。如果省略引用,则假定引用是输入COLUMN函数的单元格地址。
- **MOD()**函数返回一个数字被除数除后的余数,函数的第一个参数是要查找余数的数值,第二个参数是用来除以数值参数的数。如果除数为0,则会返回#DIV/0!错误。
让我们开始编写一些代码,以实现使用 Aspose.Cells for Java API 的目标。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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"); |
以下快照显示了加载到Excel应用程序中的结果电子表格。
为了将底纹应用于交替列,您只需将公式**=MOD(ROW(),2)=0**更改为**=MOD(COLUMN(),2)=0**,即不再获取行索引,而是修改公式以检索列索引。 在这种情况下,生成的电子表格将如下图所示。