example to create a valid (XSSFWorkbook) and a invalid (SXSSFWorkbook) workbook

ArrayFormulaTest.java (text/plain), 2.61 KB, created by torsten on 2017-10-23 08:38:00 UTC

(hide)

Description: example to create a valid (XSSFWorkbook) and a invalid (SXSSFWorkbook) workbook

Filename:

MIME Type:

Creator: torsten

Created: 2017-10-23 08:38:00 UTC

Size: 2.61 KB

patch

obsolete

>package poi; > >import java.io.FileOutputStream; >import java.io.IOException; > >import org.apache.poi.ss.usermodel.Cell; >import org.apache.poi.ss.usermodel.CellType; >import org.apache.poi.ss.usermodel.Row; >import org.apache.poi.ss.usermodel.Sheet; >import org.apache.poi.ss.usermodel.Workbook; >import org.apache.poi.ss.util.CellRangeAddress; >import org.apache.poi.xssf.streaming.SXSSFWorkbook; >import org.apache.poi.xssf.usermodel.XSSFWorkbook; > >public class ArrayFormulaTest { > > public static void main(String[] args) throws IOException { > ArrayFormulaTest poi = new ArrayFormulaTest(); > // works as expected > poi.writeWorkbook(new XSSFWorkbook(), XSSFWorkbook.class.getName() + "_workbook.xlsx"); > // does not work > poi.writeWorkbook(new SXSSFWorkbook(), SXSSFWorkbook.class.getName() + "_workbook.xlsx"); > } > > void writeWorkbook(Workbook wb, String filename) throws IOException { > Sheet sheet = wb.createSheet("array formula test"); > > int rowIndex = 0; > int colIndex = 0; > Row row = sheet.createRow(rowIndex++); > > Cell cell = row.createCell(colIndex++); > cell.setCellType(CellType.STRING); > cell.setCellValue("multiple"); > cell = row.createCell(colIndex++); > cell.setCellType(CellType.STRING); > cell.setCellValue("unique"); > > writeRow(sheet, rowIndex++, 80d, "INDEX(A2:A7, MATCH(FALSE, ISBLANK(A2:A7), 0))"); > writeRow(sheet, rowIndex++, 30d, "IFERROR(INDEX(A2:A7, MATCH(1, (COUNTIF(B2:B2, A2:A7) = 0) * (NOT(ISBLANK(A2:A7))), 0)), \"\")"); > writeRow(sheet, rowIndex++, 30d, "IFERROR(INDEX(A2:A7, MATCH(1, (COUNTIF(B2:B3, A2:A7) = 0) * (NOT(ISBLANK(A2:A7))), 0)), \"\")"); > writeRow(sheet, rowIndex++, 2d, "IFERROR(INDEX(A2:A7, MATCH(1, (COUNTIF(B2:B4, A2:A7) = 0) * (NOT(ISBLANK(A2:A7))), 0)), \"\")"); > writeRow(sheet, rowIndex++, 30d, "IFERROR(INDEX(A2:A7, MATCH(1, (COUNTIF(B2:B5, A2:A7) = 0) * (NOT(ISBLANK(A2:A7))), 0)), \"\")"); > writeRow(sheet, rowIndex++, 2d, "IFERROR(INDEX(A2:A7, MATCH(1, (COUNTIF(B2:B6, A2:A7) = 0) * (NOT(ISBLANK(A2:A7))), 0)), \"\")"); > > FileOutputStream fileOut = new FileOutputStream(filename); > wb.write(fileOut); > wb.close(); > fileOut.close(); > } > > void writeRow(Sheet sheet, int rowIndex, Double col0Value, String col1Value) { > int colIndex = 0; > Row row = sheet.createRow(rowIndex); > > // numeric value cell > Cell cell = row.createCell(colIndex++); > cell.setCellType(CellType.NUMERIC); > cell.setCellValue(col0Value); > > // formula value cell > CellRangeAddress range = new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex); > sheet.setArrayFormula(col1Value, range); > } >} View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]