Apache POI

Apache POI HSSF

 
 

Java Library for Microsoft Excel Binary Files

Open Source Java API allows to Read, Write & Convert Excel XLS spreadsheets.

 

Apache POI HSSF is a pure Java implementation of the Excel '97(-2007) file format (BIFF8). The free open source API provides features for creating, reading, modify and writing Excel XLS spreadsheets. Developers interested in just reading spreadsheet data can use the event model API to fulfill the needs. In order to modify a spreadsheet data, the user model API can be used. It is important to know that the user model system has a higher memory footprint than the low-level event user model, but has the major advantage of being much simpler to work with.

Apache POI HSSF 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 HSSF

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-scratchpad</artifactId>
    <version>4.0.0</version>
</dependency>

Create Excel Workbook & Add Sheets via Java APIs

Apache POI HSSF open-source library enables software developers to create a new Microsoft Excel workbook in XLS file format. Developers can easily add new Worksheets to the collection from an existing instance of Workbook. 

Create a new XLS file

// create a new workbook
Workbook workbook = new HSSFWorkbook();
OutputStream outputStream = new FileOutputStream("CreateXls.xls");
// create a new sheet
Sheet sheet = workbook.createSheet("Apache POI XSSF");
// create a new row
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);

Java API to Read & Write to Existing Excel File 

Apache POI HSSF enables Software developers to access and read data of the existing Microsoft Excel workbook. Reading in a file is very simple, first create workbook instance from an Excel worksheet and get to the desired sheet. Then Increment row number and iterate over all cells in a row. Repeat these steps until all data are read. Apache POI HSSF also provides features for modifying an existing Excel file.

Read Cell Content

// open xls file
InputStream inputStream = new FileInputStream("document.xls");
Workbook workbook = WorkbookFactory.create(inputStream);
// get sheet
Sheet sheet = workbook.getSheetAt(0);
// get row
Row row = sheet.getRow(1);
// get cell
Cell cell = row.getCell(1);
// display data
System.out.println(cell);

Draw Shapes & Add Images to Excel Spreadsheet

Apache POI-HSSF provides features for drawing shapes in Excel Spreadsheet. It supports drawing shapes using Microsoft Office drawing tools. Draw various shapes such as the oval, line, rectangle and set any other shape styles. In Apache POI, images are part of the drawing, supporting PNG, JPG & DIB types of images at the moment.

Draw Rectangle in XLS

// create a new workbook
Workbook workbook = new HSSFWorkbook();
OutputStream outputStream = new FileOutputStream("DrawShape.xls");
// create a new sheet
Sheet sheet = workbook.createSheet("Apache POI XSSF");
// create a new row
Row row = sheet.createRow(1);
// create a new cell
Cell cell = row.createCell(1);
// create rectangle shape
HSSFPatriarch patriarch = (HSSFPatriarch) sheet.createDrawingPatriarch();
HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 254, (short) 1, 0, (short) 1, 0);
HSSFSimpleShape shape = patriarch.createSimpleShape(a);
shape.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
// save file
workbook.write(outputStream);

Fonts Handling & Merging Cells of XLS Spreadsheets

Apache POI-HSSF provides methods that enable Java programmers to handle font in Excel spreadsheets. We can create the font, set the color, set size, etc. The Font is an interface that provides methods to handle the font. Apache POI-HSSF also allows developers to merge cells into a single cell. To do so, it provides methods that take cell indexes as an argument and merge the cells into a single large cell.

Set Font

// create a new XLS file
OutputStream outfile = new FileOutputStream("SetFont.xls");
Workbook wb = new HSSFWorkbook();
// create a new sheet
Sheet sheet = wb.createSheet("Apache POI XSSF");
// create a new row
Row row = sheet.createRow(1);
// create a new cell
Cell cell = row.createCell(1);
// set style
CellStyle style = wb.createCellStyle();
// set text
cell.setCellValue("File Format Developer Guide");
// set font settings
Font font = wb.createFont();
font.setFontHeightInPoints((short) 14);
font.setFontName("Arial");
font.setItalic(true);
font.setBold(true);
// apply font
style.setFont(font);
cell.setCellStyle(style);
// save
wb.write(outfile);