org.apache.poi.ss.usermodel.Sheet.getSheetConditionalFormatting()方法的使用及代码示例

x33g5p2x  于2022-01-29 转载在 其他  
字(14.2k)|赞(0)|评价(0)|浏览(126)

本文整理了Java中org.apache.poi.ss.usermodel.Sheet.getSheetConditionalFormatting()方法的一些代码示例，展示了Sheet.getSheetConditionalFormatting()的具体用法。这些代码示例主要来源于Github/Stackoverflow/Maven等平台，是从一些精选项目中提取出来的代码，具有较强的参考意义，能在一定程度帮忙到你。Sheet.getSheetConditionalFormatting()方法的具体详情如下：
包路径：org.apache.poi.ss.usermodel.Sheet
类名称：Sheet
方法名：getSheetConditionalFormatting

Sheet.getSheetConditionalFormatting介绍

[英]The 'Conditional Formatting' facet for this Sheet
[中]此工作表的“条件格式”方面

代码示例

代码示例来源：origin: org.apache.poi/poi

return Collections.emptyList();
final SheetConditionalFormatting scf = sheet.getSheetConditionalFormatting();
final int count = scf.getNumConditionalFormattings();
rules = new ArrayList<>(count);

代码示例来源：origin: net.sf.jxls/jxls-core

public static void copyConditionalFormatting(Sheet destSheet, Sheet srcSheet) {
  SheetConditionalFormatting cf = srcSheet.getSheetConditionalFormatting();
  int numCF = cf.getNumConditionalFormattings();
  for (int i = 0; i < numCF; i++) {
    destSheet.getSheetConditionalFormatting().addConditionalFormatting(cf.getConditionalFormattingAt(i));
  }
}

代码示例来源：origin: org.apache.poi/poi-examples

row3.createCell(2).setCellValue(2059);
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

代码示例来源：origin: org.apache.poi/poi-examples

/**
 * Use Excel conditional formatting to highlight items that are in a list on the worksheet.
 */
static void inList(Sheet sheet) {
  sheet.createRow(0).createCell(0).setCellValue("Codes");
  sheet.createRow(1).createCell(0).setCellValue("AA");
  sheet.createRow(2).createCell(0).setCellValue("BB");
  sheet.createRow(3).createCell(0).setCellValue("GG");
  sheet.createRow(4).createCell(0).setCellValue("AA");
  sheet.createRow(5).createCell(0).setCellValue("FF");
  sheet.createRow(6).createCell(0).setCellValue("XX");
  sheet.createRow(7).createCell(0).setCellValue("CC");
  sheet.getRow(0).createCell(2).setCellValue("Valid");
  sheet.getRow(1).createCell(2).setCellValue("AA");
  sheet.getRow(2).createCell(2).setCellValue("BB");
  sheet.getRow(3).createCell(2).setCellValue("CC");
  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
  // Condition 1: Formula Is   =A2=A1   (White Font)
  ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($C$2:$C$4,A2)");
  PatternFormatting fill1 = rule1.createPatternFormatting();
  fill1.setFillBackgroundColor(IndexedColors.LIGHT_BLUE.index);
  fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
  CellRangeAddress[] regions = {
      CellRangeAddress.valueOf("A2:A8")
  };
  sheetCF.addConditionalFormatting(regions, rule1);
  sheet.getRow(2).createCell(3).setCellValue("<== Use Excel conditional formatting to highlight items that are in a list on the worksheet");
}

代码示例来源：origin: org.apache.poi/poi-examples

/**
 * Use Excel conditional formatting to hide the duplicate values,
 * and make the list easier to read. In this example, when the table is sorted by Region,
 * the second (and subsequent) occurences of each region name will have white font colour.
 */
static void hideDupplicates(Sheet sheet) {
  sheet.createRow(0).createCell(0).setCellValue("City");
  sheet.createRow(1).createCell(0).setCellValue("Boston");
  sheet.createRow(2).createCell(0).setCellValue("Boston");
  sheet.createRow(3).createCell(0).setCellValue("Chicago");
  sheet.createRow(4).createCell(0).setCellValue("Chicago");
  sheet.createRow(5).createCell(0).setCellValue("New York");
  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
  // Condition 1: Formula Is   =A2=A1   (White Font)
  ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("A2=A1");
  FontFormatting font = rule1.createFontFormatting();
  font.setFontColorIndex(IndexedColors.WHITE.index);
  CellRangeAddress[] regions = {
      CellRangeAddress.valueOf("A2:A6")
  };
  sheetCF.addConditionalFormatting(regions, rule1);
  sheet.getRow(1).createCell(1).setCellValue("<== the second (and subsequent) " +
      "occurences of each region name will have white font colour.  " +
      "Condition: Formula Is   =A2=A1   (White Font)");
}

代码示例来源：origin: org.apache.poi/poi-examples

/**
 *  Use Excel conditional formatting to check for errors,
 *  and change the font colour to match the cell colour.
 *  In this example, if formula result is  #DIV/0! then it will have white font colour.
 */
static void errors(Sheet sheet) {
  sheet.createRow(0).createCell(0).setCellValue(84);
  sheet.createRow(1).createCell(0).setCellValue(0);
  sheet.createRow(2).createCell(0).setCellFormula("ROUND(A1/A2,0)");
  sheet.createRow(3).createCell(0).setCellValue(0);
  sheet.createRow(4).createCell(0).setCellFormula("ROUND(A6/A4,0)");
  sheet.createRow(5).createCell(0).setCellValue(41);
  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
  // Condition 1: Formula Is   =ISERROR(C2)   (White Font)
  ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("ISERROR(A1)");
  FontFormatting font = rule1.createFontFormatting();
  font.setFontColorIndex(IndexedColors.WHITE.index);
  CellRangeAddress[] regions = {
      CellRangeAddress.valueOf("A1:A6")
  };
  sheetCF.addConditionalFormatting(regions, rule1);
  sheet.getRow(2).createCell(1).setCellValue("<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
  sheet.getRow(4).createCell(1).setCellValue("<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
}

代码示例来源：origin: org.apache.servicemix.bundles/org.apache.servicemix.bundles.poi

return Collections.emptyList();
final SheetConditionalFormatting scf = sheet.getSheetConditionalFormatting();
final int count = scf.getNumConditionalFormattings();
rules = new ArrayList<>(count);

代码示例来源：origin: org.apache.poi/poi-examples

/**
 * Use Excel conditional formatting to highlight duplicate entries in a column.
 */
static void formatDuplicates(Sheet sheet) {
  sheet.createRow(0).createCell(0).setCellValue("Code");
  sheet.createRow(1).createCell(0).setCellValue(4);
  sheet.createRow(2).createCell(0).setCellValue(3);
  sheet.createRow(3).createCell(0).setCellValue(6);
  sheet.createRow(4).createCell(0).setCellValue(3);
  sheet.createRow(5).createCell(0).setCellValue(5);
  sheet.createRow(6).createCell(0).setCellValue(8);
  sheet.createRow(7).createCell(0).setCellValue(0);
  sheet.createRow(8).createCell(0).setCellValue(2);
  sheet.createRow(9).createCell(0).setCellValue(8);
  sheet.createRow(10).createCell(0).setCellValue(6);
  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
  // Condition 1: Formula Is   =A2=A1   (White Font)
  ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");
  FontFormatting font = rule1.createFontFormatting();
  font.setFontStyle(false, true);
  font.setFontColorIndex(IndexedColors.BLUE.index);
  CellRangeAddress[] regions = {
      CellRangeAddress.valueOf("A2:A11")
  };
  sheetCF.addConditionalFormatting(regions, rule1);
  sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted.  " +
      "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1   (Blue Font)");
}

代码示例来源：origin: org.apache.poi/poi-examples

/**
 * Highlight cells based on their values
 */
static void sameCell(Sheet sheet) {
  sheet.createRow(0).createCell(0).setCellValue(84);
  sheet.createRow(1).createCell(0).setCellValue(74);
  sheet.createRow(2).createCell(0).setCellValue(50);
  sheet.createRow(3).createCell(0).setCellValue(51);
  sheet.createRow(4).createCell(0).setCellValue(49);
  sheet.createRow(5).createCell(0).setCellValue(41);
  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
  // Condition 1: Cell Value Is   greater than  70   (Blue Fill)
  ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70");
  PatternFormatting fill1 = rule1.createPatternFormatting();
  fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
  fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
  // Condition 2: Cell Value Is  less than      50   (Green Fill)
  ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50");
  PatternFormatting fill2 = rule2.createPatternFormatting();
  fill2.setFillBackgroundColor(IndexedColors.GREEN.index);
  fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
  CellRangeAddress[] regions = {
      CellRangeAddress.valueOf("A1:A6")
  };
  sheetCF.addConditionalFormatting(regions, rule1, rule2);
  sheet.getRow(0).createCell(2).setCellValue("<== Condition 1: Cell Value Is greater than 70 (Blue Fill)");
  sheet.getRow(4).createCell(2).setCellValue("<== Condition 2: Cell Value Is less than 50 (Green Fill)");
}

代码示例来源：origin: org.apache.poi/poi-examples

/**
 * You can use Excel conditional formatting to shade bands of rows on the worksheet.
 * In this example, 3 rows are shaded light grey, and 3 are left with no shading.
 * In the MOD function, the total number of rows in the set of banded rows (6) is entered.
 */
static void shadeBands(Sheet sheet) {
  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
  ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),6)<3");
  PatternFormatting fill1 = rule1.createPatternFormatting();
  fill1.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index);
  fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
  CellRangeAddress[] regions = {
      CellRangeAddress.valueOf("A1:Z100")
  };
  sheetCF.addConditionalFormatting(regions, rule1);
  sheet.createRow(0).createCell(1).setCellValue("Shade Bands of Rows");
  sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is  =MOD(ROW(),6)<2   (Light Grey Fill)");
}

代码示例来源：origin: org.apache.poi/poi-examples

/**
 * Use Excel conditional formatting to shade alternating rows on the worksheet
 */
static void shadeAlt(Sheet sheet) {
  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
  // Condition 1: Formula Is   =A2=A1   (White Font)
  ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)");
  PatternFormatting fill1 = rule1.createPatternFormatting();
  fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
  fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
  CellRangeAddress[] regions = {
      CellRangeAddress.valueOf("A1:Z100")
  };
  sheetCF.addConditionalFormatting(regions, rule1);
  sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows");
  sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is  =MOD(ROW(),2)   (Light Green Fill)");
}

代码示例来源：origin: stackoverflow.com

Workbook wb = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();

SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
ConditionalFormattingRule rule1 =
      sheetCF.createConditionalFormattingColorScaleRule();
ColorScaleFormatting clrFmt = rule1.getColorScaleFormatting();

clrFmt.getThresholds()[0].setRangeType(RangeType.MIN);
clrFmt.getThresholds()[1].setRangeType(RangeType.NUMBER);
clrFmt.getThresholds()[1].setValue(10d);
clrFmt.getThresholds()[2].setRangeType(RangeType.MAX);

CellRangeAddress [] regions = { CellRangeAddress.valueOf("A1:A5") };
sheetCF.addConditionalFormatting(regions, rule1);

代码示例来源：origin: org.apache.poi/poi-examples

/**
 *  Use Excel conditional formatting to highlight payments that are due in the next thirty days.
 *  In this example, Due dates are entered in cells A2:A4.
 */
static void expiry(Sheet sheet) {
  CellStyle style = sheet.getWorkbook().createCellStyle();
  style.setDataFormat((short)BuiltinFormats.getBuiltinFormat("d-mmm"));
  sheet.createRow(0).createCell(0).setCellValue("Date");
  sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
  sheet.createRow(2).createCell(0).setCellFormula("A2+1");
  sheet.createRow(3).createCell(0).setCellFormula("A3+1");
  for(int rownum = 1; rownum <= 3; rownum++) {
    sheet.getRow(rownum).getCell(0).setCellStyle(style);
  }
  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
  // Condition 1: Formula Is   =A2=A1   (White Font)
  ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
  FontFormatting font = rule1.createFontFormatting();
  font.setFontStyle(false, true);
  font.setFontColorIndex(IndexedColors.BLUE.index);
  CellRangeAddress[] regions = {
      CellRangeAddress.valueOf("A2:A4")
  };
  sheetCF.addConditionalFormatting(regions, rule1);
  sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted");
}

代码示例来源：origin: net.sf.jxls/jxls-core

public static void copyConditionalFormat(org.apache.poi.ss.usermodel.Cell oldCell, org.apache.poi.ss.usermodel.Cell newCell) {
  SheetConditionalFormatting cf = oldCell.getSheet().getSheetConditionalFormatting();
  SheetConditionalFormatting ncf = newCell.getSheet().getSheetConditionalFormatting();
  int numCF = cf.getNumConditionalFormattings();
  List<ConditionalFormattingRule> rules = new ArrayList<ConditionalFormattingRule>();
  for (int i = 0; i < numCF; i++) {
    ConditionalFormatting f = cf.getConditionalFormattingAt(i);
    for (CellRangeAddress a : f.getFormattingRanges()) {
      if (a.getNumberOfCells() == 1 && a.isInRange(oldCell.getRowIndex(), oldCell.getColumnIndex())) {
        int numR = f.getNumberOfRules();
        for (int j = 0; j < numR; ++j) {
          try {
            rules.add(f.getRule(j));
          } catch (IndexOutOfBoundsException ex) {
          }
        }
      }
    }
  }
  if (!rules.isEmpty()) {
    ncf.addConditionalFormatting(new CellRangeAddress[]{ new CellRangeAddress(newCell.getRowIndex(), newCell.getRowIndex(), newCell.getColumnIndex(), newCell.getColumnIndex())}, rules.toArray(new ConditionalFormattingRule[0]));
  }
}

代码示例来源：origin: org.apache.poi/poi-examples

sheet.getRow(9).createCell(3).setCellValue("Multiples of 10 are red (beats even)");
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

代码示例来源：origin: org.apache.poi/poi-examples

sheet.setColumnWidth(3, 5000);
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

代码示例来源：origin: org.apache.poi/poi-examples

r.createCell(3).setCellValue(10);
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

代码示例来源：origin: org.bidib.jbidib/jbidibc-experimental

SheetConditionalFormatting sheetCF = sheetAccessory.getSheetConditionalFormatting();
ConditionalFormattingRule rule1 =
  sheetCF.createConditionalFormattingRule("ISBLANK(A" + startRowNumber + ")");

代码示例来源：origin: org.apache.poi/poi-examples

SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

代码示例来源：origin: org.bidib.jbidib/jbidibc-experimental

SheetConditionalFormatting sheetCF = sheetMacro.getSheetConditionalFormatting();
ConditionalFormattingRule rule1 =
  sheetCF.createConditionalFormattingRule("ISBLANK(A" + startRowNumber + ")");

内容来源于网络，如有侵权，请联系作者删除！

sheet

相关文章

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多

    Sheet类方法

    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com