Open Source Java API for Microsoft^® Excel XLSX Files

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.

<!-- 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. You can create a large excel file in java by using the following steps

Create Large Excel File using Java

1. Create a new SXSSFWorkbook and keep 100 rows in memory, exceeding rows will be flushed to disk
2. Create a new worksheet using createSheet() method
3. Insert data in 2000 rows and 2000 cells by using createRow(), createCell() and setCellValue("Your data") inside and a nested loop
4. Save file using FileOutputStream() and pass output file name
5. Write to file using SXSSFWorkbook.Write() method and pass FileOutputStream as a parameter

// create a new SXSSFWorkbook
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 create the workbook, worksheets are created from an existing instance of the Workbook, and the newly created sheet is automatically added in sequence to the workbook.

// 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 the input XLS file using Apache POI XSSF API and then write extracted information into CSV files.

// 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();

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 that 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.