apache-poi

Damaged file handling

by

You could try using HSSFWorkbook to open .xls files. You could use the following code to check how POI respond determining xls format. private boolean isExcel(InputStream i) throws IOException { return (POIFSFileSystem.hasPOIFSHeader(i) || POIXMLDocument.hasOOXMLHeader(i)); } I would use : InputStream input = new FileInputStream(fileName); Instead of : File file = new File(“Z:\\Path\\To\\File_causing_the_trouble.xls”); Did you check … Read more

How to Cache InputStream for Multiple Use

by

you can decorate InputStream being passed to POIFSFileSystem with a version that when close() is called it respond with reset(): class ResetOnCloseInputStream extends InputStream { private final InputStream decorated; public ResetOnCloseInputStream(InputStream anInputStream) { if (!anInputStream.markSupported()) { throw new IllegalArgumentException(“marking not supported”); } anInputStream.mark( 1 << 24); // magic constant: BEWARE decorated = anInputStream; } @Override … Read more

Basic Excel currency format with Apache POI

by

After digging through the documentation a bit more, I found the answer: http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFDataFormat.html Just need to find an appropriate pre-set format and supply the code. styleCurrencyFormat.setDataFormat((short)8); //8 = “($#,##0.00_);[Red]($#,##0.00)” Here are more examples: http://www.roseindia.net/java/poi/setDataFormat.shtml

How can I create a simple docx file with Apache POI?

by

Here is how you can create a simple docx file with POI : XWPFDocument document = new XWPFDocument(); XWPFParagraph tmpParagraph = document.createParagraph(); XWPFRun tmpRun = tmpParagraph.createRun(); tmpRun.setText(“LALALALAALALAAAA”); tmpRun.setFontSize(18); document.write(new FileOutputStream(new File(“yourpathhere”))); document.close();

Multiline text in Excel cells

by

You need to set the row height to accomodate two lines of text. row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints())); You need to set the wrap text = true to get the new line. Try this : Here wb is the Workbook. CellStyle cs = wb.createCellStyle(); cs.setWrapText(true); cell.setCellStyle(cs);

Required maven dependencies for Apache POI to work

by

No, you don’t have to include all of POI‘s dependencies. Maven’s transitive dependency mechanism will take care of that. As noted you just have to express a dependency on the appropriate POI artifact. For example: <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.8-beta4</version> </dependency> Edit(UPDATE): I don’t know about previous versions but to resolve imports to XSSFWorkbook and other … Read more

POI setting Cell Background to a Custom Color

by

You get this error because pallete is full. What you need to do is override preset color. Here is an example of function I’m using: public HSSFColor setColor(HSSFWorkbook workbook, byte r,byte g, byte b){ HSSFPalette palette = workbook.getCustomPalette(); HSSFColor hssfColor = null; try { hssfColor= palette.findColor(r, g, b); if (hssfColor == null ){ palette.setColorAtIndex(HSSFColor.LAVENDER.index, r, … Read more

Using Apache POI HSSF, how can I refresh all formula cells at once?

by

Sure. Refreshing all the formulas in a workbook is possibly the more typical use case anyway. If you’re using HSSF, call evaluatorAllFormulaCells: HSSFFormulaEvaluator.evaluateAllFormulaCells(hssfWorkbook) If you’re using XSSF, call evaluatorAllFormulaCells: XSSFFormulaEvaluator.evaluateAllFormulaCells(xssfWorkbook) More details are available on the poi website

tech