On March 26, a new release of the Apache POI library - version 3.8 was released.

Apache POI is a Java language library for reading and writing Microsoft Office documents.
such as Excel, PowerPoint and Word. With POI you can programmatically create new
or modify existing documents, index text, process nested
(embedded) objects (documents, pictures, etc.) and much more.

The previous stable version of Apache POI was released a year and a half ago, in October 2010,
therefore, changes have accumulated decently. Fixed more than 200 bugs, added numerous
new features and improved overall performance.
')

What's new in POI 3.8?

SXSSF Spreadsheet API implementation for creating large .xlsx files
(> 100K records) with limited memory.

The standard implementation of the .xlsx file generator (XSSF) builds a model of the entire document in memory,
those. For each record (row) and cell (cell) in the memory sits a model. Because the .xlsx format is based
on XML, for each model there is an XML bean, we get quite spreading structures and with
generating large amounts of data is a chance to get an OutOfMemoryError.

SXSSF solves this problem by storing only the last N entries in each memory.
worksheet, and the rest as they accumulate are dropped into temporary files and when saving
the contents of these temporary files are copied to the main document.

A small example: when running with the -Xmx128M key, the code below crashes with an OutOfMemoryError approximately
after generating 200,000 cells:

Workbook workbook = new XSSFWorkbook(); // keep the whole model in memory Sheet sheet = workbook.createSheet(); // generate a grid of 1M rows x 256 columns for(int i = 0; i < 1000000; i++) { Row row = sheet.createRow(i); for(int j = 0; j < 256; j++){ Cell cell = row.createCell(j); cell.setCellValue(i*j); } } // the code never gets to this point because of OutOfMemoryError FileOutputStream out = new FileOutputStream("workbook.xlsx"); workbook.write(out); out.close(); 

the same, but using SXSSF works without problems even with the –Xmx64M key:

  // keep last 100 rows in memory, flush older rows to disk Workbook workbook = new SXSSFWorkbook(100); Sheet sheet = workbook.createSheet(); // generate a grid of 1M rows x 256 columns for(int i = 0; i < 1000000; i++) { Row row = sheet.createRow(i); for(int j = 0; j < 256; j++){ Cell cell = row.createCell(j); cell.setCellValue(i*j); } } // Voila! FileOutputStream out = new FileOutputStream("workbook.xlsx"); workbook.write(out); out.close(); 

Updates in the Formula Evaluator

New features support:

IRR,NPV,MROUND,VAR,VARP,CLEAN,CHAR,ADDRESS,HOUR,MINUTE,SECOND,RATE,WORKDAY,NETWORKDAYS,SUMIFS,RANK

Total POI supports 140 functions out of about 300 supported in Excel 2010.
Full list of supported features can be found here.

User-Defined Functions

What if the formula contains a function that is not supported?
By default, the formula calculator throws NotImplementedException and this happens in two cases:

• Function defined in Excel, but not implemented in POI
• The function is defined in a VBA macro or in an external add-in library.

POI 3.8 gives you the opportunity to programmatically implement the function you need.
and register it in the calculator. Like that:

  /** * CUBEMEMBER:   OLAP ,    POI-3.8 */ FreeRefFunction CUBEMEMBER = new FreeRefFunction() { public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { if(args.length != 3) { //  CUBEMEMBER  3  return ErrorEval.VALUE_INVALID; } // TODO: implement me //      #NUM! return ErrorEval.NUM_ERROR; } }; WorkbookEvaluator.registerFunction("CUBEMEMBER", CUBEMEMBER); //   NotImplementedException      

Excel Ant tasks

Ant extensions are designed to recalculate and test formulas without writing Java code.
This functionality has an interesting story: it came from a scientific project in which Excel files
Third-party software is created, not POI. Files contain both the data itself and the formulas needed
check their correctness on the server side, while the verifier is not a programmer
in Java and knows only a little Ant syntax.
Using Excel Ant tasks, a task is reduced to writing build files. Target s interacting
with POI look like this:

  <target name="Habratest"> <excelant fileName="habratest.xls"> <test > <evaluate cell="'Habratest'!$B$4" expectedValue="790.7936" precision="1.0e-4" /> </test> </excelant> </target> 

at the output of Ant, we will see the following:

 Habratest: [excelant] Using input file: habratest.xls [test] setting globalPrecision to 0.0010 in the evaluator [evaluate] test precision = 1.0E-4 global precision = 0.0010 [evaluate] Using evaluate precision of 1.0E-4 over the global precision of 0.0010 [excelant] 1/1 tests passed. 

XSLF - Java API for working with .pptx files (PowerPoint 2007-2010)

POI now supports a full-featured User API for working with .pptx files.
You can programmatically create new or edit existing .pptx files,
insert or change slides, text, pictures, tables, and more.

Below is a partial list of supported features:

• Creating new slides, incl. with a predefined design (Title, Title and Text,
  Title and Picture, etc.)
• Inserting pictures
• Inserting text blocks with support for paragraphs and formatting blocks
• Combine slides from multiple presentations
• Tables
• Full support for geometric objects defined in PowerPoint:
  from primitives (rectangle, ellipse) to objects with arbitrary geometry (freeforms)
• PPTX2PNG: Converting Slides to Pics

The last feature, PPTX2PNG, I would like to emphasize: this utility with Java main () interface
converts slides into PNG pictures, but you can take this code as a basis and write a converter
to other graphic formats (SVG, Flash, HTML5 Canvas, etc.). All you need to do is pass
a subclass from java.awt.Graphics2D to the slide.draw method (Graphics2D graphics);

For example, this example demonstrates how to convert .pptx slides to SVG using the SVGGraphics2D driver from Apache Batik

MS Word to .doc, xsl-fo and text converters .doc

This functionality was already mentioned on Habré, therefore I will note only the main thing:

• Word-to-HTML Converter, which converts a Word document to HTML, can even be with pictures;
• Word-to-Text Converter, which is a replacement for the past WordExtractor, correctly processing
  embedded OLE-documents, splitting into paragraphs, field codes (including hyperlinks);
• A Word-to-FO Converter that converts a Word document to an XSL FO file is also possible with pictures.
  Then this file can be transferred to Apache FOP for conversion to convert Word to PDF.

Bugfixes, bugfixes and even more bugfixes ....

Much work has been done to improve the stability of the library as a whole.
- more than 250 changes from the previous version 3.7. First of all it concerns the readability of documents.
Bug-reports of the form "POI does not read my file and crashes c exception" is already a rarity and, I hope, will soon disappear completely :)

Have you found a bug or want to share ideas on how to improve the project? Visit us at http://poi.apache.org/ , subscribe to the mailing lists and send
patches in bagzilu. You are always welcome!