Apache poi的ConditionalFormatting工作不正常。

huangapple go评论90阅读模式
英文:

Apache poi ConditionalFormatting not working properly

问题

我在我的Java应用程序中使用Apache POI来创建Excel文件

我的使用案例是当**A1**单元格中的值为`Change it`

单元格**A10****A14**中的样式将会被修改

为此我正在使用POI提供的ConditionalFormatting功能

但是样式只被应用到了**A10**单元格而没有延伸到**A14**

我漏掉了什么

代码

private void addValidations(Sheet sheet) {
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("=A1=\"Change it\"");
    FontFormatting fontFmt = rule1.createFontFormatting();
    fontFmt.setFontStyle(true, false);
    fontFmt.setFontColorIndex(IndexedColors.YELLOW.index);
    BorderFormatting bordFmt = rule1.createBorderFormatting();
    bordFmt.setBorderBottom(BorderStyle.THIN);
    bordFmt.setBorderTop(BorderStyle.THICK);
    bordFmt.setBorderLeft(BorderStyle.DASHED);
    bordFmt.setBorderRight(BorderStyle.DOTTED);
    ConditionalFormattingRule[] cfRules = {
        rule1
    };
    CellRangeAddress[] regions = {
        CellRangeAddress.valueOf("A10:A14")
    };
    sheetCF.addConditionalFormatting(regions, cfRules);
}
英文:

I'm using apache poi to create excels in my java application.

My use case is when the value in A1 is Change it.

Style in cells A10 to A14 will be changed.

For this I'm following feature of ConditionalFormatting provided by poi.

But the style is only getting applied to cell A10 & not till A14.

What am I missing?

Code:

private void addValidations(Sheet sheet) {
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("=A1=\"Change it\"");
    FontFormatting fontFmt = rule1.createFontFormatting();
    fontFmt.setFontStyle(true, false);
    fontFmt.setFontColorIndex(IndexedColors.YELLOW.index);
    BorderFormatting bordFmt = rule1.createBorderFormatting();
    bordFmt.setBorderBottom(BorderStyle.THIN);
    bordFmt.setBorderTop(BorderStyle.THICK);
    bordFmt.setBorderLeft(BorderStyle.DASHED);
    bordFmt.setBorderRight(BorderStyle.DOTTED);
    ConditionalFormattingRule [] cfRules =
            {
                    rule1
            };
    CellRangeAddress[] regions = {
            CellRangeAddress.valueOf("A10:A14")
    };
    sheetCF.addConditionalFormatting(regions, cfRules);
}

答案1

得分: 1

The code `sheetCF.createConditionalFormattingRule("=A1=\"Change it\"");` cannot work at all. In `HSSF` it throws `org.apache.poi.ss.formula.FormulaParseException: The specified formula 'A1="Change it"' starts with an equals sign which is not allowed.`. In `XSSF` it creates a corrupt `*.xlsx` file. The leading equals sign is not stored in `Excel` formula cells. It only is shown in `Excel`'s `GUI`. So it would must be `sheetCF.createConditionalFormattingRule("A1=\"Change it\"");`.

And the formula `=A1="Change it"` is relative in column letter as well as in row number. So applied to cell `A10` it means `=A1="Change it"`. But applied to cell `A11` it means `=A2="Change it"`. And applied to cell `A12` it means `=A3="Change it"` and so on. So if the need is changing all font colors in `A10:A14` if content of `A1` changes, then the reference in the formula would must be fixed using `$`. So it would must be `sheetCF.createConditionalFormattingRule("A$1=\"Change it\"");`.

Complete Example:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;

public class ConditionalFormatting {

 public static void main(String[] args) throws Exception {
  Workbook workbook = new XSSFWorkbook(); String filePath ="./ConditionalFormatting.xlsx";
  //Workbook workbook = new HSSFWorkbook(); String filePath ="./ConditionalFormatting.xls";

  Sheet sheet = workbook.createSheet();

  for (int r = 9; r < 14; r++) {
   sheet.createRow(r).createCell(0).setCellValue("Text in Cell A" + (r+1));
  }

  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

  ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule("A$1=\"Change it\"");
  FontFormatting fontFormatting = rule.createFontFormatting();
  fontFormatting.setFontStyle(false, true);
  fontFormatting.setFontColorIndex(IndexedColors.YELLOW.index);

  ConditionalFormattingRule[] cfRules = new ConditionalFormattingRule[]{rule};

  CellRangeAddress[] regions = new CellRangeAddress[]{CellRangeAddress.valueOf("A10:A14")};

  sheetCF.addConditionalFormatting(regions, cfRules);

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close();
  workbook.close();

 }
}

This changes font color in A10:A14 to yellow if cell content in A1 is Change it.

英文:

The code sheetCF.createConditionalFormattingRule(&quot;=A1=\&quot;Change it\&quot;&quot;); cannot work at all. In HSSF it throws org.apache.poi.ss.formula.FormulaParseException: The specified formula &#39;=A1=&quot;Change it&quot;&#39; starts with an equals sign which is not allowed.. In XSSF it creates a corrupt *.xlsx file. The leading equals sign is not stored in Excel formula cells. t only is shown in Excel's GUI. So it would must be sheetCF.createConditionalFormattingRule(&quot;A1=\&quot;Change it\&quot;&quot;);.

And the formula =A1=&quot;Change it&quot; is relative in column letter as well as in row number. So applied to cell A10 it means =A1=&quot;Change it&quot;. But applied to cell A11 it means =A2=&quot;Change it&quot;. And applied to cell A12 it means =A3=&quot;Change it&quot; and so on. So if the need is changing all font colors in A10:A14 if content of A1 changes, then the reference in the formula would must be fixed using $. So it would must be sheetCF.createConditionalFormattingRule(&quot;A$1=\&quot;Change it\&quot;&quot;);.

Complete Example:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileOutputStream;
public class ConditionalFormatting {
public static void main(String[] args) throws Exception {
Workbook workbook = new XSSFWorkbook(); String filePath =&quot;./ConditionalFormatting.xlsx&quot;;
//Workbook workbook = new HSSFWorkbook(); String filePath =&quot;./ConditionalFormatting.xls&quot;;
Sheet sheet = workbook.createSheet();
for (int r = 9; r &lt; 14; r++) {
sheet.createRow(r).createCell(0).setCellValue(&quot;Text in Cell A&quot; + (r+1));
}
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(&quot;A$1=\&quot;Change it\&quot;&quot;);
FontFormatting fontFormatting = rule.createFontFormatting();
fontFormatting.setFontStyle(false, true);
fontFormatting.setFontColorIndex(IndexedColors.YELLOW.index);
ConditionalFormattingRule[] cfRules = new ConditionalFormattingRule[]{rule};
CellRangeAddress[] regions = new CellRangeAddress[]{CellRangeAddress.valueOf(&quot;A10:A14&quot;)};
sheetCF.addConditionalFormatting(regions, cfRules);
FileOutputStream out = new FileOutputStream(filePath);
workbook.write(out);
out.close();
workbook.close();
}
}

This changes font color in A10:A14 to yellow if cell content in A1 is Change it.

答案2

得分: 0

尝试一下看看是否有效。

CellRangeAddress[] regions = new CellRangeAddress[]{ CellRangeAddress.valueOf("A10:A14") };
英文:

Try this if this works.

CellRangeAddress[] regions = new CellRangeAddress[]{ CellRangeAddress.valueOf(&quot;A10:A14&quot;) };

huangapple
  • 本文由 发表于 2020年7月26日 17:47:53
  • 转载请务必保留本文链接:https://go.coder-hub.com/63098529.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定