Java Library for Microsoft Excel Binary Files

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.

<!-- 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 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 from the existing Microsoft Excel workbook. Reading in a file is very simple, first create a workbook instance from an Excel worksheet and get to the desired sheet. Then Increment the 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.

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

// 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 the 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.

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