英文:
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("=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. t 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
.
答案2
得分: 0
尝试一下看看是否有效。
CellRangeAddress[] regions = new CellRangeAddress[]{ CellRangeAddress.valueOf("A10:A14") };
英文:
Try this if this works.
CellRangeAddress[] regions = new CellRangeAddress[]{ CellRangeAddress.valueOf("A10:A14") };
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论