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.
At A Glance
An overview of Apache POI-HSSF features.
- Create new worksheet
- Add images
- Worksheet to CSV
- Copy sheet
- Re-Order sheets
- Add header & footers
- Add data in cells
- Create workbook
- Set pint area
- Getting cell contents
- Add hyperlinks worksheet
- Create charts worksheet
- Create pivot tables
- Worksheet to HTML
- Auto-fit row/column
- Cell alignment
- Hide/Unhide cells
- Insert cell comments
- Iterate rows & columns
- Merge cells
- Split panes
- Freeze panes
- Create date cell
- Create cell types
- New line in cells
- Work with fonts
- Work with colors
- Work with borders
Apache POI-HSSF only requires Java run-time.
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.
// 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);