Java Read Excel Files Using Apache POI

Apache POI is a Java library for processing Microsoft Office files. Here below are a few limited examples of its operation on Excel files(both in xls and xlsx format).

Maven POM
Add the following dependency into your project pom.xml file

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.12-beta1</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.12-beta1</version>
		</dependency>

Basic
To read the workbook and worksheet, get row numbers and determine if a row or column is hidden, etc.

Workbook workBook = WorkbookFactory.create(new FileInputStream("example.xlsx"));
Sheet sheet = workBook.getSheet("sheet1");
for (int rowNum = 0; rowNum < sheet.getPhysicalNumberOfRows(); rowNum++) {
  Row row = sheet.getRow(rowNum);
  if(!row.getZeroHeight()){
      for (int colNum = row.getFirstCellNum(); colNum < row.getLastCellNum(); colNum++) {
          Cell cell = row.getCell(colNum);
          String cellValue = CellValueUtil.getValue(workBook, cell);//See below
          System.out.println(cellValue + " at row=" + rowNum + " col="+colNum);
      }
  }
}

Parse Cell Value
To parse the cell value, you can use the following code

public class CellValueUtil {
  public static String getValue(Workbook workBook, Cell cell) {
    FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();

    int cellType = cell.getCellType();
    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
      CellValue cellValue = evaluator.evaluate(cell);
      cellType = cellValue.getCellType();
    }

    switch (cellType) {
      case Cell.CELL_TYPE_BOOLEAN:
        return Boolean.toString(cell.getBooleanCellValue());
      case Cell.CELL_TYPE_NUMERIC:
        return Double.valueOf(cell.getNumericCellValue()).doubleValue() + "";
      case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
      case Cell.CELL_TYPE_BLANK:
        return "";
      case Cell.CELL_TYPE_FORMULA:
      default:
        return "Not Expected";
    }
  }
}

Unmerge Cells
You can split the merged cells with the following code. Note that the merged regions are stored at the worksheet level. After removing each merged region, the returned value of sheet.getNumMergedRegions() will decrease by 1.

  public void splitMergedCells() {
    int totalNumMergedRegions = sheet.getNumMergedRegions();
    for (int i = 0; i < totalNumMergedRegions; ++i) {
      CellRangeAddress firstRange = sheet.getMergedRegion(0);// always handle the first range
      int firstRow = firstRange.getFirstRow();
      int lastRow = firstRange.getLastRow();
      int firstColumn = firstRange.getFirstColumn();
      int lastColumn = firstRange.getLastColumn();
      sheet.removeMergedRegion(0);// always remove the first range
      String mergedValue =
          CellValueUtil.getValue(workBook, sheet.getRow(firstRow).getCell(firstColumn));
      for (int rowNum = firstRow; rowNum <= lastRow; rowNum++) {
        Row row = sheet.getRow(rowNum);
        for (int colNum = firstColumn; colNum <= lastColumn; colNum++) {
          Cell cell = row.getCell(colNum);
          cell.setCellType(Cell.CELL_TYPE_STRING);
          cell.setCellValue(mergedValue);
        }
      }
    }
  }
Advertisements
This entry was posted in Excel, Java and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s