使用Aspose.Cells库在C#中应用条件格式阴影以交替显示行和列。通过调整这些条件,您可以更好地控制单元格的外观。

使用条件格式为交替行和列应用着色

本文使用Excel的内置函数,如ROW、COLUMN和MOD。以下是关于这些函数的一些细节,以更好地理解提供的代码段。

  • **ROW()**函数返回单元格引用的行号。如果省略引用,则假定引用是输入ROW函数的单元格地址。
  • **COLUMN()**函数返回单元格引用的列号。如果省略引用,则假定引用是输入COLUMN函数的单元格地址。
  • **MOD()**函数返回一个数字被除数除后的余数,函数的第一个参数是要查找余数的数值,第二个参数是用来除以数值参数的数。如果除数为0,则会返回#DIV/0!错误。

让我们开始编写一些代码,以实现使用 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");

以下快照显示了加载到Excel应用程序中的结果电子表格。

todo:image_alt_text

为了将底纹应用于交替列,您只需将公式**=MOD(ROW(),2)=0**更改为**=MOD(COLUMN(),2)=0**,即不再获取行索引,而是修改公式以检索列索引。 在这种情况下,生成的电子表格将如下图所示。

todo:image_alt_text