Apache POI

Apache POI XSSF

 
 

Open Source Java API for Excel XLSX Files

Create, Edit & Convert Excel XLSX Spreadsheets to CSV & HTML file formats via Java Library.

 

Apache POI-XSSF is a pure Java implementation of the Excel 2007 XLSX file format. The API provides procedures for creating, reading, modify and writing Excel XLSX files. It provides low-level structures for those with special needs. It also provides an event model API for efficient read-only access as well as a full user model API for creating, reading and modifying XLSX files. Apache POI-XSSF provides great support for additional excel features such as working with sheets, formulas, creating cell styles by filling colors and borders, fonts, headers and footers, shapes, data validations, images, hyperlinks and many more.

Getting Started with Apache POI XSSF

First of all, you need to have the Java Development Kit (JDK) installed on your system. If you already have it then proceed to the Apache POI's download page to get the latest stable release in an archive. Extract the contents of the ZIP file in any directory from where the required libraries can be linked to your Java program. That is all!

Referencing Apache POI in your Maven-based Java project is even simpler. All you need is to add the following dependency in your pom.xml and let your IDE fetch and reference the Apache POI Jar files.

Apache POI Maven Dependency

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
</dependency>

Create Large Excel Spreadsheets using Java API

Apache POI XSSF offers low-memory footprint via SXSSF API for handling large data writing to MS Excel files.  This allows writing very large files without running out of memory as only a configurable portion of the rows are kept in memory at any one time. POI-SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document. Older rows that are no longer in the window become inaccessible, as they are written to the disk.

Create a Large Excel File

// create a new SXSSFWorkbook and keep 100 rows in memory, exceeding rows will be flushed to disk
SXSSFWorkbook wb = new SXSSFWorkbook(100);
Sheet sh = wb.createSheet();
// insert 2000 rows
for(int rownum = 0; rownum < 2000; rownum++){
    Row row = sh.createRow(rownum);
    // insert data in 20000 cells
    for(int cellnum = 0; cellnum < 10; cellnum++){
        Cell cell = row.createCell(cellnum);
        cell.setCellValue("Row Number: "+ rownum + " Cell Number: "+ cellnum);
    }
}
// save file
FileOutputStream out = new FileOutputStream("LargeDcument.xlsx");
wb.write(out);
out.close();

Java APIs for Excel Workbook Creation & Sheets Addition

Apache POI XSSF enables computer programmers to create a new Excel workbook in XLSX file format. Once developers creates the workbook, worksheets are created from an existing instance of Workbook, and the newly created sheet is automatically added in sequence to the workbook. 

Create Excel Workbook and Add Sheets

// create a new XLSX file
Workbook workbook = new XSSFWorkbook();
OutputStream outputStream = new FileOutputStream("CreateXlsx.xlsx");
// create a new sheet
Sheet sheet = workbook.createSheet("Apache POI XSSF");
// create a new sheet
Row row     = sheet.createRow(1);
// create a new cell
Cell cell   = row.createCell(1);
// set cell value
cell.setCellValue("File Format Developer Guide");
// save file
workbook.write(outputStream);

Convert an Excel Spreadsheet to CSV & Other File Format

Java Developers and programmers can easily convert an Excel spreadsheet into a CSV file format using Apache POI XSSF API. CSV stands for Comma-Separated-Values and is a very common format used for exchanging data between many applications. Firstly, developers need to read input XLS file using Apache POI XSSF API and then write extracted information into CSV files.

Convert XLSX to CSV

// Open and existing XLSX file
FileInputStream fileInStream = new FileInputStream("LargeDocument.xlsx");
XSSFWorkbook workBook = new XSSFWorkbook(fileInStream);
XSSFSheet selSheet = workBook.getSheetAt(0);
// Loop through all the rows
Iterator rowIterator = selSheet.iterator();
while (rowIterator.hasNext()) {
    Row row = rowIterator.next();
    // Loop through all rows and add ","
    Iterator cellIterator = row.cellIterator();
    StringBuffer stringBuffer = new StringBuffer();
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        if (stringBuffer.length() != 0) {
            stringBuffer.append(",");
        }
        stringBuffer.append(cell.getStringCellValue());
    }
    System.out.println(stringBuffer.toString());
}
workBook.close();

XSSF Enhancement for Headers & Footers

Apache POI XSSF has the ability to handle First page headers and footers, as well as Even/Odd headers and footers. Headers and footers are a very important part of an Excel Spreadsheet. It usually contains extra information such as dates, page numbers, author's name and footnotes, which help in keeping longer documents organized and easier to read. All Header/Footer Property flags can be handled in XSSF. The odd header and footer are the default header and footer. It is displayed on all pages that do not display either a first-page header or an even page header.

Merging Cells & Text Extraction inside Excel XLSX Files

Apache POI XSSF provides the capability that allows Java programmers to merge multiple cells into a single cell inside an Excel spreadsheet. It has included methods which take cell indexes as an argument and merge the cells into a single large cell. One of the most demanding features is the ability to extract text from an XLSX file and use it according to your needs.  Apache POI has provided basic text extraction for all the project supported file formats. For advanced text extraction needs, including Rich Text extraction (such as formatting and styling), along with XML and HTML output, Apache POI works closely with Apache Tika to deliver POI-powered Tika Parsers for all the project supported file formats.