Images

Images are part of graphics support. To add an image, simply call createPicture() on the drawing patriarch. Currently supported types:

• PNG
• JPG
• DIB

Note that once an image is added to a sheet, any existing graphics may be erased.

//create a new workbook
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
//add picture data to this workbook.
InputStream is = new FileInputStream("image1.jpeg");
byte[] bytes = IOUtils.toByteArray(is);
int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
is.close();
CreationHelper helper = wb.getCreationHelper();
//create sheet
Sheet sheet = wb.createSheet();
// Create the drawing patriarch. This is the top level container for all shapes.
Drawing drawing = sheet.createDrawingPatriarch();
//add picture shape
ClientAnchor anchor = helper.createClientAnchor();
//set the picture's top-left corner,
//subsequent call to Picture#resize() will operate on it
anchor.setCol1(3);
anchor.setRow1(2);
Picture pict = drawing.createPicture(anchor, pictureIdx);
//auto-resize picture relative to its top-left corner
pict.resize();
//save workbook
String file = "picture.xls";
if(wb instanceof XSSFWorkbook) file += "x";
try (OutputStream fileOut = new FileOutputStream(file)) {
    wb.write(fileOut);
}

Warning: Picture.resize() only works for JPEG and PNG. Other formats are not yet supported.

Reading images from workbook:

List lst = workbook.getAllPictures();
for (Iterator it = lst.iterator(); it.hasNext(); ) {
  PictureData pict = (PictureData)it.next();
  String ext = pict.suggestFileExtension();
  byte[] data = pict.getData();
  if (ext.equals("jpeg")){
    try (OutputStream out = new FileOutputStream("pict.jpg")) {
      out.write(data);
    }
  }
}

Named Ranges and Named Cells

Named ranges are a way to reference a group of cells by name. Named cells are a degenerate case of named ranges where the "cell group" contains only one cell. You can create and reference cells by named range in your workbook. When using named ranges, use the org.apache.poi.ss.util.CellReference and org.apache.poi.ss.util.AreaReference classes.

Note: Using relative values like "A1:B1" may cause the name to unexpectedly shift cells when Microsoft Excel processes the workbook. Using absolute references like "$A$1:$B$1" usually avoids this - see this discussion.

Creating Named Ranges/Named Cells

// Setup code
String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet(sname);
sheet.createRow(0).createCell(0).setCellValue(cvalue);
// 1. Create named range for single cell using AreaReference
Name namedCell = wb.createName();
namedCell.setNameName(cname + "1");
String reference = sname+"!$A$1:$A$1"; // area reference
namedCell.setRefersToFormula(reference);
// 2. Create named range for single cell using CellReference
Name namedCel2 = wb.createName();
namedCel2.setNameName(cname + "2");
reference = sname+"!$A$1"; // cell reference
namedCel2.setRefersToFormula(reference);
// 3. Create named range for area using AreaReference
Name namedCel3 = wb.createName();
namedCel3.setNameName(cname + "3");
reference = sname+"!$A$1:$C$5"; // area reference
namedCel3.setRefersToFormula(reference);
// 4. Create named formula
Name namedCel4 = wb.createName();
namedCel4.setNameName("my_sum");
namedCel4.setRefersToFormula("SUM(" + sname + "!$I$2:$I$6)");

Reading from Named Ranges/Named Cells

// setup code
String cname = "TestName";
Workbook wb = getMyWorkbook(); // retrieve workbook
// retrieve named range
int namedCellIdx = wb.getNameIndex(cellName);
Name aNamedCell = wb.getNameAt(namedCellIdx);
// retrieve cells in specified range and test their contents
AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula());
CellReference[] crefs = aref.getAllReferencedCells();
for (int i=0; i<crefs.length; i++) {
    Sheet s = wb.getSheet(crefs[i].getSheetName());
    Row r = sheet.getRow(crefs[i].getRow());
    Cell c = r.getCell(crefs[i].getCol());
    // extract cell contents based on cell type etc.
}

Reading from Non-Contiguous Named Ranges

// Setup code
String cname = "TestName";
Workbook wb = getMyWorkbook(); // retrieve workbook
// retrieve named range
// Will be something like "$C$10,$D$12:$D$14";
int namedCellIdx = wb.getNameIndex(cellName);
Name aNamedCell = wb.getNameAt(namedCellIdx);
// retrieve cells in named range and test their contents
// will return one AreaReference for C10, and
// another for D12 to D14
AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getRefersToFormula());
for (int i=0; i<arefs.length; i++) {
    // Only get the corners of the Area
    // (use arefs[i].getAllReferencedCells() to get all cells)
    CellReference[] crefs = arefs[i].getCells();
    for (int j=0; j<crefs.length; j++) {
        // Check it turns into real stuff
        Sheet s = wb.getSheet(crefs[j].getSheetName());
        Row r = s.getRow(crefs[j].getRow());
        Cell c = r.getCell(crefs[j].getCol());
        // Do something with this corner cell
    }
}

Note that when deleting cells, Excel does not delete attached named ranges. Therefore, workbooks can contain named ranges pointing to cells that no longer exist. You should check the reference validity before constructing AreaReference:

if(name.isDeleted()){
  //named range points to a deleted cell.
} else {
  AreaReference ref = new AreaReference(name.getRefersToFormula());
}

Cell Comments - HSSF and XSSF

Comments are rich text notes attached to and associated with cells, stored separately from cell content and displayed in a graphic object (like a text box) separate from but associated with the cell.

Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
CreationHelper factory = wb.getCreationHelper();
Sheet sheet = wb.createSheet();
Row row   = sheet.createRow(3);
Cell cell = row.createCell(5);
cell.setCellValue("F4");
Drawing drawing = sheet.createDrawingPatriarch();
//when comment box is visible, display it in 1x3 space
ClientAnchor anchor = factory.createClientAnchor();
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex()+1);
anchor.setRow1(row.getRowNum());
anchor.setRow2(row.getRowNum()+3);
// create comment and set text+author
Comment comment = drawing.createCellComment(anchor);
RichTextString str = factory.createRichTextString("Hello, World!");
comment.setString(str);
comment.setAuthor("Apache POI");
// assign comment to cell
cell.setCellComment(comment);
String fname = "comment-xssf.xls";
if(wb instanceof XSSFWorkbook) fname += "x";
try (OutputStream out = new FileOutputStream(fname)) {
    wb.write(out);
}
wb.close();

Reading Cell Comments

Cell cell = sheet.get(3).getColumn(1);
Comment comment = cell.getCellComment();
if (comment != null) {
  RichTextString str = comment.getString();
  String author = comment.getAuthor();
}
// alternatively, retrieve cell comment by (row, column)
comment = sheet.getCellComment(3, 1);

To get all comments on a sheet:

Map<CellAddress, Comment> comments = sheet.getCellComments();
Comment commentA1 = comments.get(new CellAddress(0, 0));
Comment commentB1 = comments.get(new CellAddress(0, 1));
for (Entry<CellAddress, ? extends Comment> e : comments.entrySet()) {
  CellAddress loc = e.getKey();
  Comment comment = e.getValue();
  System.out.println("Comment at " + loc + ": " +
      "[" + comment.getAuthor() + "] " + comment.getString().getString());
}

Adjusting Column Width to Fit Content

Sheet sheet = workbook.getSheetAt(0);
sheet.autoSizeColumn(0); //adjust first column width
sheet.autoSizeColumn(1); //adjust second column width

For SXSSFWorkbooks only, since the "random access" window may exclude most rows needed to calculate optimal column width, columns to auto-size must be tracked before any rows are flushed.

SXSSFWorkbook workbook = new SXSSFWorkbook();
SXSSFSheet sheet = workbook.createSheet();
sheet.trackColumnForAutoSizing(0);
sheet.trackColumnForAutoSizing(1);
// if you have a collection of column indices, see SXSSFSheet#trackColumnForAutoSizing(Collection<Integer>)
// or loop through yourself with for-loop.
// alternatively, use SXSSFSheet#trackAllColumnsForAutoSizing() if columns to auto-size are not
// known in advance or upgrading existing code and trying to minimize changes. Remember
// tracking all columns will require more memory and CPU cycles as best-fit width is calculated
// on all tracked columns for every flushed row.

//create some cells
for (int r=0; r < 10; r++) {
    Row row = sheet.createRow(r);
    for (int c; c < 10; c++) {
        Cell cell = row.createCell(c);
        cell.setCellValue("Cell " + c.getAddress().formatAsString());
    }
}
// auto-size columns.
sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);

Note that Sheet#autoSizeColumn() doesn't calculate formula cells - width is calculated based on cached formula results. If your workbook has many formulas, evaluate them before auto-sizing.

Warning: To calculate column width, Sheet.autoSizeColumn uses Java2D classes that throw exceptions when graphics environment is unavailable. If graphics environment is unavailable, you must tell Java you're running in headless mode by setting: java.awt.headless=true. Also ensure fonts used in your workbook are available to Java.

How to Read Hyperlinks

Sheet sheet = workbook.getSheetAt(0);
Cell cell = sheet.getRow(0).getCell(0);
Hyperlink link = cell.getHyperlink();
if(link != null){
    System.out.println(link.getAddress());
}

How to Create Hyperlinks

Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
//cell style for hyperlinks
//by default hyperlinks are blue and underlined
CellStyle hlink_style = wb.createCellStyle();
Font hlink_font = wb.createFont();
hlink_font.setUnderline(Font.U_SINGLE);
hlink_font.setColor(IndexedColors.BLUE.getIndex());
hlink_style.setFont(hlink_font);
Cell cell;
Sheet sheet = wb.createSheet("Hyperlinks");
//URL
cell = sheet.createRow(0).createCell(0);
cell.setCellValue("URL Link");
Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
link.setAddress("http://poi.apache.org/");
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
//link to a file in the current directory
cell = sheet.createRow(1).createCell(0);
cell.setCellValue("File Link");
link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
link.setAddress("link1.xls");
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
//e-mail link
cell = sheet.createRow(2).createCell(0);
cell.setCellValue("Email Link");
link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
//note, if subject contains white spaces, make sure they are url-encoded
link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
//link to a place in this workbook
//create a target sheet and cell
Sheet sheet2 = wb.createSheet("Target Sheet");
sheet2.createRow(0).createCell(0).setCellValue("Target Cell");
cell = sheet.createRow(3).createCell(0);
cell.setCellValue("Worksheet Link");
Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
link2.setAddress("'Target Sheet'!A1");
cell.setHyperlink(link2);
cell.setCellStyle(hlink_style);
try (OutputStream out = new FileOutputStream("hyperinks.xlsx")) {
    wb.write(out);
}
wb.close();

Data Validation

Starting from version 3.8, POI handles data validation for .xls and .xlsx formats with slightly different syntax.

hssf.usermodel (binary .xls format)

Checking User Input Cell Values Against One or More Predefined Values

The following code limits values users can enter in cell A1 to one of three integer values (10, 20, or 30).

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Data Validation");
CellRangeAddressList addressList = new CellRangeAddressList(
  0, 0, 0, 0);
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
  new String[]{"10", "20", "30"});
DataValidation dataValidation = new HSSFDataValidation
  (addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(true);
sheet.addValidationData(dataValidation);

Dropdown List:

This code does the same but provides users with a dropdown list to select values from.

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Data Validation");
CellRangeAddressList addressList = new CellRangeAddressList(
  0, 0, 0, 0);
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
  new String[]{"10", "20", "30"});
DataValidation dataValidation = new HSSFDataValidation
  (addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);

Error Message on Invalid Input:

Create a message box that displays to users when they enter invalid values.

dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.createErrorBox("Box Title", "Message Text");

Replace "Box Title" with text to display in the message box title bar, and "Message Text" with the error message text.

Prompt:

Create a prompt users see when the cell containing data validation receives focus.

dataValidation.createPromptBox("Title", "Message Text");
dataValidation.setShowPromptBox(true);

Text in the first parameter passed to createPromptBox() will be bolder and displayed as the prompt title, while the second parameter displays as the message text. You can pass createExplicitListConstraint() a string array containing integers, floats, dates, or text values.

Further Data Validation:

To get validation that checks input values, such as integers between 10 and 100, use the DVConstraint.createNumericConstraint(int, int, String, String) factory method.

dvConstraint = DVConstraint.createNumericConstraint(
  DVConstraint.ValidationType.INTEGER,
  DVConstraint.OperatorType.BETWEEN, "10", "100");

See javadoc for other validation and operator types; also note that not all validation types are supported by this method. Values passed to the two string parameters can be formulas; the "=" sign indicates a formula:

dvConstraint = DVConstraint.createNumericConstraint(
  DVConstraint.ValidationType.INTEGER,
  DVConstraint.OperatorType.BETWEEN, "=SUM(A1:A3)", "100");

If calling createNumericConstraint(), you cannot create a dropdown list - the setSuppressDropDownArrow(false) method call will be ignored.

Date and time constraints can be created by calling createDateConstraint(int, String, String, String) or createTimeConstraint(int, String, String). Both are very similar to the above and explained in javadoc.

Creating Data Validation from Spreadsheet Cells

Specific cell contents can be used to provide data validation values, supported by the DVConstraint.createFormulaListConstraint(String) method. To specify values from a contiguous cell range, do either:

dvConstraint = DVConstraint.createFormulaListConstraint("$A$1:$A$3");

Or:

Name namedRange = workbook.createName();
namedRange.setNameName("list1");
namedRange.setRefersToFormula("$A$1:$A$3");
dvConstraint = DVConstraint.createFormulaListConstraint("list1");

In both cases, users can select from a dropdown list containing values from cells A1, A2, and A3.

Data doesn't have to be for data validation. However, to select data from other sheets, you must specify a name when creating that sheet, and use that name in the formula. Assuming a sheet named "Data Sheet" exists, this will work:

Name namedRange = workbook.createName();
namedRange.setNameName("list1");
namedRange.setRefersToFormula("'Data Sheet'!$A$1:$A$3");
dvConstraint = DVConstraint.createFormulaListConstraint("list1");

This will also work:

dvConstraint = DVConstraint.createFormulaListConstraint("'Data Sheet'!$A$1:$A$3");

But this won't:

Name namedRange = workbook.createName();
namedRange.setNameName("list1");
namedRange.setRefersToFormula("'Sheet1'!$A$1:$A$3");
dvConstraint = DVConstraint.createFormulaListConstraint("list1");

Neither will this:

dvConstraint = DVConstraint.createFormulaListConstraint("'Sheet1'!$A$1:$A$3");