March 2004: Read And Write Microsoft Excel Spreadsheets Using Jakarta POI

Read And Write Microsoft Excel Spreadsheets Using Jakarta POI

By Santosh Shanbhag, OCI Senior Software Engineer

March 2004


Introduction

The Jakarta POI project consists of APIs for manipulating various file formats based upon Microsoft's OLE 2 Compound Document format using pure Java. OLE 2 Compound Document Format based files include most Microsoft Office files such as XLS and DOC as well as MFC serialization API based file formats. The POI File System POIFS can be used to read a document written in OLE 2 Compound Document Format using Java as well as write such documents. POI provides several APIs to read and write Microsoft Excel (HSSF), Microsoft Word (HWPF), and OLE Property Sets (HPSF). However, to read and write Excel (XLS) files using Java, the HSSF (Horrible SpreadSheet Format!) API is sufficient. HSSF uses POIFS under the covers to perform some of its major tasks. HSSF creates spreadsheets in Microsoft Excel '97 (-2002) format.

This article provides an introduction on how to get started with the HSSF API. No prior knowledge other than an understanding of core Java is assumed.

The POI Way To Do It

The current POI release is at version 2.0. In order to start using HSSF you will need to include the poi-2.0-final-20040126.jar library in your classpath. The HSSF API consists of the following main packages:

PackageDescription
org.apache.poi.hssf.usermodel Provides high level representation of common spreadsheet entities like workbook, sheet, row, cell, font, cell style, color pallete, data format, named ranges, etc.
org.apache.poi.hssf.record Contains class representations for XLS binary strutures.
org.apache.poi.hssf.record.aggregates Contains classes that manipulate collections of records as a single record.
org.apache.poi.hssf.record.formula Contains classes to handle formulas used in a spreadsheet.
org.apache.poi.hssf.eventusermodel Provides an event-based API for reading HSSF files. Intended for intermediate developers who might want to learn low-level API structures.
org.apache.poi.hssf.dev Contains examples for how to use HSSF and tools for developing and validating HSSF.

 

The HSSF API (and also the rest of the POI API) is so simple and easy to use that there is hardly any detailed explanation required. The Busy Developers' Guide To HSSF on the POI website (see references below) provides a ready reference to the most common tasks in reading and writing spreadsheets. At a very basic level, one must know the following relationship :

Creating A Workbook

The starting point for creating a spreadsheet is to create an HSSFWorkbook. An HSSFWorkbook is a high-level container for all the elements of a spreadsheet just like the JFrame is a high-level container for all the gui components of a Swing application.

In order to create a new workbook and associate it with an output stream, use the following code:

  1. import java.io.*;
  2. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  3.  
  4. ...
  5. ...
  6. HSSFWorkbook workbook = new HSSFWorkbook();
  7. ...
  8. ...
  9.  
  10. FileOutputStream fout = new FileOutputStream("MyWorkbook.xls");
  11. // do not have to use FileOutputStream, any OutputStream will do
  12. wb.write(fout);
  13. fout.close();
  14. ...

In order to read an existing spreadsheet into a workbook, use the following code:

  1. import java.io.*;
  2. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  3.  
  4. ...
  5. ...
  6. FileInputStream fis = new FileInputStream("MyWorkbook.xls");
  7. ...
  8. // do not have to use FileInputStream, any InputStream will do
  9. HSSFWorkbook workbook = new HSSFWorkbook(fis);
  10. ...
  11. fis.close();

Creating Sheets In The Workbook

The HSSFWorkbook provides methods to create sheets dynamically and automatically make it a part of the workbook. Duplicates of existing sheets can also be created as shown below:

  1. import org.apache.poi.hssf.usermodel.*;
  2.  
  3. ...
  4. ...
  5. // create a sheet with name "Sheet One"
  6. HSSFSheet sheet1 = workbook.createSheet("Sheet One");
  7.  
  8. // create a sheet with name "Sheet Two"
  9. HSSFSheet sheet2 = workbook.createSheet("Sheet Two");
  10. ...
  11. HSSFSheet sheet3 = workbook.createSheet();
  12.  
  13. // we can change the name the name of the sheet anytime.
  14. workbook.setSheetName(2, "Sheet Three");
  15. ...
  16. ...
  17. // create a sheet in the workbook that contains the same data as the third
  18. // the name of sheet4 will be "Sheet Three (2)"
  19. HSSFSheet sheet4 = workbook.cloneSheet(2);
  20.  
  21. ...
  22. ...

Creating Rows, Freeze Panes, and Split Panes In A Sheet

A sheet provides methods to create rows in the sheet. It also contains methods to create freeze panes and splitpanes.

Rows can be created in the sheet as shown below:

  1. ...
  2. ...
  3. // creates the first row in the sheet
  4. HSSFRow row1 = sheet.createRow((short) 0);
  5.  
  6. // creates the second row in the sheet
  7. HSSFRow row2 = sheet.createRow((short) 1);

A freeze pane can be created as shown below:

  1. ...
  2. ...
  3. // horizontal position of the freeze pane
  4. int colSplit = 0;
  5.  
  6. // vertical position of the freezepane
  7. int rowSplit = 0;
  8.  
  9. // left column visible in right pane
  10. int leftmostColumn = 20;
  11.  
  12. // top row visible in bottom pane
  13. int topRow = 20;
  14.  
  15. // create a freeze pane from colSplit, rowSplit to leftmostColumn, topRow
  16. // colSplit and rowSplit indicate columns and rows you wish to split by
  17. // leftmostColumn and topRow indicate cells that are visible in the bottom right quadrant
  18. sheet.createFreezePane(colSplit, rowSplit, leftmostColumn, topRow);
  19. ...

A split pane can be created as shown below:

When creating a split pane, the split area is divided into four separate work areas. A split is always specified by a pixel value and can be changed by a user by dragging it to a new position.

  1. ...
  2. ...
  3. // x position of the split (1/20th of a point)
  4. int xSplitPos = 2000;
  5.  
  6. // y position of the split (1/20th of a point)
  7. int ySplitPos = 2000;
  8.  
  9. // left column visible in right pane
  10. int leftmostColumn = 10;
  11.  
  12. // top row visible in bottom pane
  13. int topRow = 10;
  14.  
  15. // the pane that currently has the focus
  16. int activePane = HSSFSheet.PANE_LOWER_LEFT;
  17.  
  18. // create a split pane from xSplitPos, ySplitPos to leftmostColumn, topRow
  19. sheet.createSplitPane(xSplitPos, ySplitPos, leftmostColumn, topRow, activePane);
  20. ...

Rows can be shifted up or down in a sheet.

  1. ...
  2. ...
  3. int startRow = 101;
  4. int endRow = 120;
  5. int shiftValue = -10;
  6. // move 20 rows from (101 - 120) up 10 rows
  7. sheet.shiftRows(startRow, endRow, shiftValue);
  8. ...

Sheets can be zoomed in and out.

The zoom factor is specified as a fraction of two numbers. Any two integers can be used as long as they are in the range of (1..65535) inclusive.

  1. ...
  2. ...
  3. // zoom out to 90% (9 / 10)
  4. sheet.setZoom(9, 10);
  5. ...
  6. // zoom in to 125% (125 / 100)
  7. sheet.setZoom(125, 100);
  8. ...

Creating Cells For A Row

It is pretty straight-forward to create cells from an HSSFRow. You can also iterate through the cells of a row.

  1. ...
  2. ...
  3. // create a row
  4. HSSFRow row = sheet.createRow((short) 0);
  5.  
  6. short col = 0;
  7.  
  8. // create the first cell in the row.
  9. HSSFCell cell1 = row.createCell(col++);
  10.  
  11. // create more cells
  12. HSSFCell cell2 = row.createCell(col++);
  13. HSSFCell cell3 = row.createCell(col++);
  14. HSSFCell cell4 = row.createCell(col++);
  15. ...
  16. // now iterate through the cells of a row
  17. Iterator iterator = row.cellIterator();
  18.  
  19. while ( iterator.hasNext() ) {
  20. HSSFCell cell = (HSSFCell) iterator.next();
  21.  
  22. // do something with the cell
  23. ...
  24. }

Setting the Cell Value

A cell value can be set to an int, double, boolean, String, Date, and Calendar.

  1. ...
  2. HSSFRow row = sheet.creatRow((short) 0);
  3.  
  4. HSSFCell cell = row.createCell((short) 0);
  5.  
  6. //**********************************
  7. //* Examples for setting cell value
  8. //**********************************
  9. // cell value is an integer
  10. cell.setCellValue(20);
  11.  
  12. // cell value is a double
  13. cell.setCellValue(20.0);
  14.  
  15. // cell value is a boolean
  16. cell.setCellValue(true);
  17.  
  18. // cell value is a string
  19. cell.setCellValue("Hello");
  20.  
  21. // cell value is a date
  22. cell.setCellValue(new Date());
  23. ...

Formatting A Cell To A Style

A cell can be styled using various parameters. You can set the font, fill color, and border for a cell. Also, you can format the value in a cell using a built-in format. The following code shows how:

  1. //***************************************
  2. //* Example for setting cell data format
  3. //***************************************
  4. // create a cell style
  5. // we should always create a new style from the workbook instead of modifying
  6. // the existing cell style object. Otherwise, it might affect other cells in
  7. // addition to this cell.
  8. HSSFCellStyle style = workbook.createCellStyle();
  9.  
  10. // get one of the built-in formats. Several formats are defined in the HSSFDataFormat class.
  11. short dataFormatIndex = HSSFDataFormat.getBuiltinFormat("m/d/yy"));
  12.  
  13. style.setDataFormat(dataFormatIndex);
  14.  
  15. // now set the cell to the formatted style.
  16. cell.setCellStyle(style);
  17. ...
  18. //**********************************
  19. //* Example for setting cell font
  20. //**********************************
  21. // let's create a new font for the cell. Always use workbook to create a new font
  22. HSSFFont font = workbook.createFont();
  23. font.setFontHeightInPoints((short) 32);
  24. font.setFontName("Times New Roman");
  25. font.setItalic(true);
  26. //single underline
  27. font.setUnderline(HSSFFont.U_SINGLE);
  28. // make it bold
  29. font.setBoldWeight(HSSFFont.BOLDWEIGHT_BOLD);
  30.  
  31. style.setFont(font);
  32. ...
  33. //******************************************************
  34. //* Example for setting cell borders and border colors
  35. //******************************************************
  36. style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  37. style.setBottomBorderColor(HSSFColor.RED.index);
  38.  
  39. style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
  40. style.setLeftBorderColor(HSSFColor.ORANGE.index);
  41.  
  42. style.setBorderRight(HSSFCellStyle.BORDER_DASHED);
  43. style.setRightBorderColor(HSSFColor.YELLOW.index);
  44.  
  45. style.setBorderTop(HSSFCellStyle.BORDER_HAIR);
  46. style.setTopBorderColor(HSSFColor.AQUA.index);
  47. ...
  48. //****************************************************************
  49. //* Example for setting cell fill color using a pre-defined color
  50. //****************************************************************
  51. // choose from several colors defined in the HSSFColor class
  52. style.setFillBackgroundColor(HSSFColor.RED.index);
  53.  
  54. // choose from several fill patterns: SOLID_FOREGROUND, FINE_DOTS,SQUARES
  55. // DIAMONDS, etc, defined in the HSSFCellStyle class
  56. style.setFillPattern(HSSFCellStyle.BRICKS);
  57.  
  58. //****************************************************************
  59. //* Example for setting cell fill color by defining a custom color
  60. //****************************************************************
  61. byte redValue = (byte) 50;
  62. byte greenValue = (byte) 100;
  63. byte blueValue = (byte) 150;
  64.  
  65.  
  66. // choose a seed color index
  67. short colorIndex = HSSFColor.RED.index + 1;
  68.  
  69. // get the workbook custom pallete and set the color at the seed index to
  70. // the custom color
  71. HSSFPalette palette = workbook.getCustomPalette();
  72. pallete.setColorAtIndex(colorIndex, redValue, greenValue, blueValue);
  73.  
  74. // set the cell style to that fill color index
  75. style.setFillForegroundColor(colorIndex);

Setting A Formula Inside A Cell

You can use any built-in function or operator as a formula string. HSSF provides a FormulaParser class (in the package org.jakarta.poi.hssf.model) that parses the formula set in the cell.

  1. ...
  2. cell.setCellFormula("SUM(B10..G10)");
  3. ...

Creating An Excel Spreadsheet From A Swing JTable

A JTable organizes its data in cells just as a spreadsheet does and hence it provides a classic example for migrating data from a JTable to a spreadsheet. Here's the code that shows how to do it:

Exporting a JTable to a spreadsheet

  1. // call this method to export a JTable to an HSSFSheet
  2. public void exportTableToSheet(JTable table, HSSFSheet sheet) {
  3. int rowCount = table.getRowCount();
  4. int colCount = table.getColumnCount();
  5. int currentSheetRow = 0;
  6.  
  7. for (int tableRowIndex = 0; tableRowIndex < rowCount; tableRowIndex++) {
  8.  
  9. for (int tableColIndex = 0; tableColIndex < colCount; tableColIndex++) {
  10.  
  11. // create and format the cell in the spreadsheet
  12. createAndFormatCell(table, tableRowIndex, tableColIndex, sheet, currentSheetRow);
  13.  
  14. }
  15. currentSheetRow++;
  16. }
  17. }
  18.  
  19.  
  20. private void createAndFormatCell(JTable table, int tableRowIndex, int tableColIndex,
  21. HSSFSheet sheet, int currentSheetRow) {
  22.  
  23. // get the cell value from the table
  24. Object cellValue = table.getValueAt(tableRowIndex, tableColIndex);
  25.  
  26. // create the cell
  27. HSSFCell cell = createHSSFCell(sheet, cellValue, currentSheetRow, tableColIndex);
  28.  
  29. // get the renderer component that renders the cell
  30. TableCellRenderer renderer = table.getCellRenderer(tableRowIndex, tableColIndex);
  31. Component rendererComponent = renderer.getTableCellRendererComponent(table,
  32. cellValue,
  33. false,
  34. false,
  35. tableRowIndex,
  36. tableColIndex);
  37.  
  38.  
  39. if (rendererComponent instanceof JLabel) {
  40. // if it is a JLabel, get the label text which is the actual formatted displayed text
  41. // and not the raw cell value
  42. JLabel label = (JLabel) rendererComponent;
  43. cellValue = label.getText();
  44. }
  45.  
  46. formatCell(cell, rendererComponent);
  47. }
  48.  
  49.  
  50. private HSSFCell createHSSFCell(HSSFSheet sheet, Object value, int row, int col) {
  51. // create row if not yet created
  52. HSSFRow hssfRow = sheet.getRow(row);
  53. hssfRow = (hssfRow == null) ? sheet.createRow(row) : hssfRow;
  54.  
  55. // create cell if not yet created
  56. HSSFCell cell = hssfRow.getCell((short) col);
  57. cell = (cell == null) ? hssfRow.createCell((short) col) : cell;
  58.  
  59. // set the cell value
  60. String cellValue = (value == null) ? "" : value.toString();
  61. cell.setCellValue(cellValue);
  62. return cell;
  63. }
  64.  
  65.  
  66. public void formatCell(HSSFCell cell, Component rendererComponent) {
  67.  
  68. // create a style
  69. HSSFCellStyle cellStyle = workbook.createCellStyle();
  70.  
  71. // set the cell color
  72. cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  73. Color color = rendererComponent.getBackground();
  74. HSSFPalette palette = workbook.getCustomPalette();
  75.  
  76. // maintain(increment after each use) unused color index as an instance variable
  77. short someUnusedColorIndex = 10;
  78. palette.setColorAtIndex(someUnusedColorIndex, (byte) color.getRed(),
  79. (byte) color.getGreen(), (byte) color.getBlue());
  80. cellStyle.setFillForegroundColor(someUnusedColorIndex);
  81.  
  82.  
  83. // set the font
  84. Font font = rendererComponent.getFont();
  85. HSSFFont hssfFont = createHSSFFont(font);
  86. cellStyle.setFont(hssfFont);
  87.  
  88. // set the border
  89. cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  90. cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  91. cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  92. cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  93.  
  94.  
  95. // don't forget to set the cell style!
  96. cell.setCellStyle(cellStyle);
  97. }
  98.  
  99.  
  100. private HSSFFont createHSSFFont(Font font) {
  101. HSSFFont hssfFont = workbook.createFont();
  102. hssfFont.setFontName(font.getName());
  103. hssfFont.setItalic(font.isItalic());
  104. hssfFont.setBoldweight(font.isBold() ? HSSFFont.BOLDWEIGHT_BOLD
  105. : HSSFFont.BOLDWEIGHT_NORMAL);
  106. hssfFont.setFontHeightInPoints((short) font.getSize());
  107. hssfFont.setUnderline(HSSFFont.U_NONE);
  108.  
  109. return hssfFont;
  110. }

Figure 1. An example JTable with TimeSheet information

Figure 1 An example JTable with TimeSheet information


Figure 2. Microsoft Excel spreadsheet created from JTable

Figure 2 Microsoft Excel spreadsheet created from JTable

Summary

Jakarta POI's HSSF provides a sophisticated and easy-to-use API to read and write Microsoft Excel files. This article provides a beginner's guide to start using the HSSF API quickly. An example use of HSSF is in acceptance testing, where users are comfortable specifying input data and expected output data in Excel format instead of XML or flat files. HSSF can be used to read the input data, pre-populate the database with the data, run the application against the data and then test the actual results with the expected results specified in the spreadsheet. This gives the users the flexibility to specify the application requirements as well as provide acceptance test data in Excel. The developers can use the spreadsheet to write the tests at the application level. HSSF makes life easier in such test-driven environments. And, of course, HSSF can be used in applications that need to read and write Excel spreadsheets using Java.

References

 

The Software Engineering Tech Trends is a monthly newsletter featuring emerging trends in software engineering.

Subscribe

© Copyright Object Computing, Inc. 1993, 2016. All rights reserved

secret