Apache POI – Questions and Answers

What does Apache POI stand for?

Apache POI stands For “Poor Obfuscation Implementation“. Apache POI is an API provided by Apache Software Foundation which is a collection of different java libraries.

What is Apache POI?

Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs. It is an open-source library developed and distributed by Apache Software Foundation to design or modify Microsoft Office files using Java programs.

Know more about Apache POI.

Is Apache POI free to use?

The POI project is OpenSource and developed/distributed under the Apache Software License v2. Unlike some other licenses, the Apache license allows free open source development.

What are the commonly used components of Apache POI?

The following tables show the commonly used components of Apache POI and their basic description:

ComponentDescription
POIFS (Poor Obfuscation Implementation File System) It is the oldest component of Apache POI. It is a pure Java implementation of the OLE 2 Compound Document Format.
HSSF (Horrible SpreadSheet Format) It is used to read and write MS-Excel 97 (-2003) file format (.xls)
XSSF (XML SpreadSheet Format)It is used to read and write MS-Excel XML (2007+) file format (.xlsx)
HSLF (Horrible Slide Layout Format)It is used to read and write MS-PowerPoint 97 (-2003) file format (.ppt)
XSLF (XML Slide Layout Format)It is used to read and write MS-PowerPoint (2007+) file format (.pptx)
HWPF (Horrible Word Processor Format)It is used to read and write MS-Word 97 (-2003) file format (.doc)
XWPF (XML Word Processor Format)It is used to read and write MS-Word (2007+) file format (.docx)
Common SSIt is a common Java API to read and write both MS-Excel files format .xls and .xlsx
Common SLIt is also a common Java API to read and write both MS-PowerPoint files format .ppt and .pptx

Know more about Apache POI – Components

What are the features of the Apache POI?

Apache POI Features

  1. Apache POI provides stream-based processing which is useful for large files and takes less memory.
  2. Apache POI provides the freatures to create, read and write Microsoft document files (Excel, PowerPoint, Word).
  3. It is helpful to handle both XLS and XLSX formats of spreadsheets.
  4. It also provides SXSSF that is an extension of XSSF to work with very large excel sheets.

How do I install Apache POI?

There are two ways for installing Apache POI in Eclipse, based on your project type:

  1. Maven Project
  2. Stand alone Java Project

See the article here Apache POI – How to Install?

What are HSSF and XSSF?

HSSF and XSSF are the popular components of the Apache POI library. HSSF is the POI Project’s pure Java implementation of the Excel 97(-2007) (.xls) file format. XSSF is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

What does HSSF stand for?

The HSSF stands for “Horrible Spreadsheet Format“. It is one of the components of Apache POI used for reading and writing the Microsoft Excel (.XLS) file formats.

What does XSSF stand for?

The XSSF stands for “XML Spreadsheet Format“. It is a component of Apache POI used for reading and writing the Microsoft Excel (.XLSX) file formats.

What is XSSF in Java?

XSSF is prefixed before the class name to indicate operations related to a Microsoft Excel 2007 file or later. XSSFWorkbook and HSSFWorkbook are classes that act as an Excel Workbook. HSSFSheet and XSSFSheet are classes that act as an Excel Worksheet.

What is the difference between an XLS and XLSX file?

An XLS and XLSX are two different file extensions of Microsoft Excel. The main difference between XLS and XLSX is that XLS files use a Binary format whereas, XLSX uses a newer file format known as Open XML. The XLS extension is used by Microsoft Excel 2003 and earlier whereas the XLSX extension is used by Microsoft Excel 2007 and later.

How does Apache POI read data from Excel?

Steps to read excel files using Apache POI:

  1. Create a workbook bypassing the InputStream. eg. HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
  2. Getting a sheet from the workbook. eg. HSSFSheet sheet = workbook.getSheetAt(0);
  3. Iterate each Row of the sheet. eg. Iterator<Row> iterator = sheet.iterator();
  4. Iterate each Cell of a Row. eg. Iterator<Cell> cellIterator = row.cellIterator();
  5. Read the Cell value based on CellType.
  6. Repeat steps 3-5 to read more available data in a sheet.

See the complete implementation here: How to read Excel files in java using Apache POI?

How do I write data using Apache POI in Excel?

Steps to writing data in Excel files using Apache POI:

  1. Create a blank workbook. eg. XSSFWorkbook workbook = new XSSFWorkbook();
  2. Create a sheet and name it. eg. XSSFSheet spreadsheet = workbook.createSheet("Employee Data");
  3. Create a row. eg. Row row = sheet.createRow(rownum++);
  4. Add cells to the sheet.
  5. Repeat Steps 3 and 4 to write the complete data.

See the complete implementation here: How to write Excel files in java using Apache POI?