Busy Developers' Guide to HSSF and
XSSF Features
1. Busy Developers' Guide to Features
Want to use HSSF and XSSF read and write spreadsheets in a hurry? This guide is for you. If
you're after more in-depth coverage of the HSSF and XSSF user-APIs, please consult the
HOWTO guide as it contains actual descriptions of how to use this stuff.
1.1. Index of Features
•
How to create a new workbook
•
How to create a sheet
•
How to create cells
•
How to create date cells
•
Working with different types of cells
•
Iterate over rows and cells
•
Getting the cell contents
•
Text Extraction
•
Aligning cells
•
Working with borders
•
Fills and color
•
Merging cells
•
Working with fonts
•
Custom colors
•
Reading and writing
•
Use newlines in cells.
•
Create user defined data formats
•
Fit Sheet to One Page
•
Set print area for a sheet
•
Set page numbers on the footer of a sheet
•
Shift rows
•
Set a sheet as selected
•
Set the zoom magnification for a sheet
•
Create split and freeze panes
Page 1
Copyright © 2002-2009 The Apache Software Foundation All rights reserved.
•
Repeating rows and columns
•
Headers and Footers
•
Drawing Shapes
•
Styling Shapes
•
Shapes and Graphics2d
•
Outlining
•
Images
•
Named Ranges and Named Cells
•
How to set cell comments
•
How to adjust column width to fit the contents
•
Hyperlinks
•
Data Validation
•
Embedded Objects
1.2. Features
1.2.1. New Workbook
Workbook wb = new HSSFWorkbook();
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
Workbook wb = new XSSFWorkbook();
FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
wb.write(fileOut);
fileOut.close();
1.2.2. New Sheet
Workbook wb = new HSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
Sheet sheet1 = wb.createSheet("new sheet");
Sheet sheet2 = wb.createSheet("second sheet");
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
1.2.3. Creating Cells
Workbook wb = new HSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");
Busy Developers' Guide to HSSF and XSSF Features
Page 2
Copyright © 2002-2009 The Apache Software Foundation All rights reserved.
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short)0);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue(1);
// Or do it on one line.
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue(
createHelper.createRichTextString("This is a string"));
row.createCell(3).setCellValue(true);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
1.2.4. Creating Date Cells
Workbook wb = new HSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow(0);
// Create a cell and put a date value in it. The first cell is not styled
// as a date.
Cell cell = row.createCell(0);
cell.setCellValue(new Date());
// we style the second cell as a date (and time). It is important to
// create a new cell style from the workbook otherwise you can end up
// modifying the built in style and effecting not only this cell but other cells.
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(
createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);
//you can also set date as java.util.Calendar
cell = row.createCell(2);
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
Busy Developers' Guide to HSSF and XSSF Features
Page 3
Copyright © 2002-2009 The Apache Software Foundation All rights reserved.
1.2.5. Working with different types of cells
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
Row row = sheet.createRow((short)2);
row.createCell(0).setCellValue(1.1);
row.createCell(1).setCellValue(new Date());
row.createCell(2).setCellValue(Calendar.getInstance());
row.createCell(3).setCellValue("a string");
row.createCell(4).setCellValue(true);
row.createCell(5).setCellType(HSSFCell.CELL_TYPE_ERROR);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
1.2.6. Demonstrates various alignment options
public static void main(String[] args) throws Exception {
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow((short) 2);
row.setHeightInPoints(30);
createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM);
createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM);
createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER);
createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER);
createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY);
createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP);
createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx");
wb.write(fileOut);
fileOut.close();
}
/**
* Creates a cell and aligns it a certain way.
*
* @param wb the workbook
* @param row the row to create the cell in
* @param column the column number to create the cell in
* @param halign the horizontal alignment for the cell.
*/
Busy Developers' Guide to HSSF and XSSF Features
Page 4
Copyright © 2002-2009 The Apache Software Foundation All rights reserved.
private static void createCell(Workbook wb, Row row, short column, short halign, short valign) {
Cell cell = row.createCell(column);
cell.setCellValue(new XSSFRichTextString("Align It"));
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(halign);
cellStyle.setVerticalAlignment(valign);
cell.setCellStyle(cellStyle);
}
1.2.7. Working with borders
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow(1);
// Create a cell and put a value in it.
Cell cell = row.createCell(1);
cell.setCellValue(4);
// Style the cell with borders all around.
CellStyle style = wb.createCellStyle();
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLUE.getIndex());
style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
cell.setCellStyle(style);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
1.2.8. Iterate over rows and cells
Sometimes, you'd like to just iterate over all the rows in a sheet, or all the cells in a row. This
is possible with a simple for loop.
Luckily, this is very easy. Row defines a CellIterator inner class to handle iterating over the
cells (get one with a call to row.cellIterator()), and Sheet provides a rowIterator() method to
give an iterator over all the rows.
Alternately, Sheet and Row both implement java.lang.Iterable, so if you're using Java 1.5,
you can simply take advantage of the built in "foreach" support - see below.
Busy Developers' Guide to HSSF and XSSF Features
Page 5
Copyright © 2002-2009 The Apache Software Foundation All rights reserved.
