I am new to apache poi trying to write the excel file I am facing some issue while setting the formula to cell.
Below is my sample excel:
User | Country | Value |
---|---|---|
Rohit | UK | |
John | IND |
I need to populate the Value column based on the on the User and Country fields. Below is the excel formula which I want to convert to apache poi java
=IF(AND(LEN([@[User]]) > 0, [@Country] = "UK"),1,0)
can anybody help me ?
sample code
try {
InputStream inputStream = this.getClass().getResourceAsStream("/sample.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook (inputStream);
System.out.println("inside the controller");
XSSFSheet sheet = workbook.getSheetAt(0);
Object[][] bookData = {
{"Rohit","UK",null},
{"John","IND",null}
};
int rowCount = sheet.getLastRowNum();
int count=0;
//CellStyle cell1;
for (Object[] aBook : bookData) {
Row row = sheet.createRow(++rowCount);
int columnCount = 0;
Cell cell = row.createCell(columnCount);
// cell.setCellValue(rowCount);
for (Object field : aBook) {
cell = row.createCell(columnCount++);
if(field==null){
cell.setCellFormula("IF(AND(LEN(A1:A3)>0,(B1:B3)=UK),1,0)");
}
else if (field instanceof String) {
cell.setCellValue((String) field);
} else if (field instanceof Integer) {
cell.setCellValue((Integer) field);
}
}
}
java.util.List<XSSFTable> l = sheet.getTables();
l.get(0).getCTTable().setRef("A1:L4");
FileOutputStream outputStream = new FileOutputStream("D://demo/sample_with_values.xlsx");
workbook.write(outputStream);
workbook.close();
outputStream.close();
} catch (IOException | EncryptedDocumentException ex) {
ex.printStackTrace();
}
As @Axel Richter
mentioned using ==
is invalid.
cell.setCellFormula("IF(AND(LEN(A1:A3)>0,(B1:B3)==UK),1,0)");
Mistakes with your formula.
#1. The error...
Parse error near char 25 '=' in specified formula 'IF(AND(LEN(A1:A3)>0,(B1:B3)==UK),1,0)'. Expected cell ref or constant literal`
…implies that you are using an additional =
in the formula.
#2. (B1:B3)==UK
should be (B1:B3)="UK"
. You are comparing a String value so it should be in double quotes.
Code:
cell.setCellFormula("IF(AND(LEN(A1:A3)>0,(B1:B3)=\"UK\"),1,0)");
Output:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With