Apache POI: Read and Write Excel File

Learn to read excel, write excel, evaluate formula cells and apply custom formatting to the generated excel files using Apache POI library with examples.

If we are building software for the HR or Finance domain, there is usually a requirement for generating excel reports across management levels. Apart from reports, we can also expect some input data for the applications coming in the form of excel sheets and the application is expected to support this requirement.

Apache POI is a well-trusted library among many other open-source libraries to handle such usecases involving excel files. Please note that, in addition, we can read and write MS Word and MS PowerPoint files also using the Apache POI library.

This Apache POI tutorial will discuss some everyday excel operations in real-life applications.

1. Maven

If we are working on a maven project, we can include the Apache POI dependencies in pom.xml file using this:

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>5.2.2</version>
</dependency>

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

2. Core Classes in POI Library

• HSSF, XSSF and XSSF classes

  Apache POI main classes usually start with either HSSF, XSSF or SXSSF.

  • HSSF – is the POI Project’s pure Java implementation of the Excel 97(-2007) file format. e.g., HSSFWorkbook, HSSFSheet.
  • XSSF – is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format. e.g., XSSFWorkbook, XSSFSheet.
  • SXSSF (since 3.8-beta3) – is an API-compatible streaming extension of XSSF to be used when huge spreadsheets have to be produced and heap space is limited. e.g., SXSSFWorkbook, SXSSFSheet. SXSSF achieves its low memory footprint by limiting access to the rows within a sliding window, while XSSF gives access to all rows in the document.

• Row and Cell

  Apart from the above classes, Row and Cell interact with a particular row and a particular cell in an excel sheet.

• Styling Related Classes

  A wide range of classes like CellStyle, BuiltinFormats, ComparisonOperator, ConditionalFormattingRule, FontFormatting, IndexedColors, PatternFormatting, SheetConditionalFormatting etc. are used when you have to add formatting to a sheet, primarily based on some rules.

• FormulaEvaluator

  Another helpful class FormulaEvaluator is used to evaluate the formula cells in an excel sheet.

3. Write to an Excel File

I am taking this example first so we can reuse the excel sheet created by this code in further examples.

Writing excel using POI is very simple and involves the following steps:

1. Create a workbook
2. Create a sheet in the workbook
3. Create a row in the sheet
4. Add cells to the sheet
5. Repeat steps 3 and 4 to write more data

It seems very simple, right? Let’s have a look at the code doing these steps.

Java program to write an excel file using Apache POI library.

//Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook(); 

//Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Employee Data");

//Prepare data to be written as an Object[]
Map<String, Object[]> data = new TreeMap<String, Object[]>();
data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});
data.put("2", new Object[] {1, "Amit", "Shukla"});
data.put("3", new Object[] {2, "Lokesh", "Gupta"});
data.put("4", new Object[] {3, "John", "Adwards"});
data.put("5", new Object[] {4, "Brian", "Schultz"});

//Iterate over data and write to sheet
Set<String> keyset = data.keySet();
int rownum = 0;
for (String key : keyset) {

  Row row = sheet.createRow(rownum++);
  Object [] objArr = data.get(key);
  int cellnum = 0;
  for (Object obj : objArr)
  {
     Cell cell = row.createCell(cellnum++);
     if(obj instanceof String)
          cell.setCellValue((String)obj);
      else if(obj instanceof Integer)
          cell.setCellValue((Integer)obj);
  }
}

//Write the workbook in file system
try {
  FileOutputStream out = new FileOutputStream(new File("howtodoinjava_demo.xlsx"));
  workbook.write(out);
  out.close();
  System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
} 
catch (Exception e) {
  e.printStackTrace();
}

See Also: Appending Rows to Excel

4. Reading an Excel File

Reading an excel file using POI is also very simple if we divide this into steps.

1. Create a workbook instance from an excel sheet
2. Get to the desired sheet
3. Increment row number
4. Iterate over all cells in a row
5. Repeat steps 3 and 4 until all data is read

Let’s see all the above steps in the code. I am writing the code to read the excel file created in the above example. It will read all the column names and the values in it – cell by cell.

Java program to read an excel file using Apache POI library.

FileInputStream file = new FileInputStream(new File("howtodoinjava_demo.xlsx"));

//Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);

//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);

//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {

  Row row = rowIterator.next();

  //For each row, iterate through all the columns
  Iterator<Cell> cellIterator = row.cellIterator();
   
  while (cellIterator.hasNext()) {

    Cell cell = cellIterator.next();

    //Check the cell type and format accordingly
    switch (cell.getCellType()) {
      case Cell.CELL_TYPE_NUMERIC:
        System.out.print(cell.getNumericCellValue() + "t");
        break;
      case Cell.CELL_TYPE_STRING:
        System.out.print(cell.getStringCellValue() + "t");
        break;
    }
  }
  System.out.println("");
}
file.close();

Program Output:

ID      NAME        LASTNAME
1.0     Amit        Shukla  
2.0     Lokesh      Gupta   
3.0     John        Adwards 
4.0     Brian       Schultz 

See Also: Apache POI – Read an Excel File using SAX Parser

5. Add and Evaluate Formula Cells

When working on complex excel sheets, we encounter many cells with formulas to calculate their values. These are formula cells. Apache POI also has excellent support for adding formula cells and evaluating already present formula cells.

Let’s see one example of how to add formula cells in excel?

The sheet has four cells in a row and the fourth one in the multiplication of all the previous 3 rows. So the formula will be: A2*B2*C2 (in the second row)

Java program to add formulas in an excel file using Apache POI library.

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Calculate Simple Interest");

Row header = sheet.createRow(0);
header.createCell(0).setCellValue("Pricipal");
header.createCell(1).setCellValue("RoI");
header.createCell(2).setCellValue("T");
header.createCell(3).setCellValue("Interest (P r t)");
  
Row dataRow = sheet.createRow(1);
dataRow.createCell(0).setCellValue(14500d);
dataRow.createCell(1).setCellValue(9.25);
dataRow.createCell(2).setCellValue(3d);
dataRow.createCell(3).setCellFormula("A2*B2*C2");
  
try {

  FileOutputStream out =  new FileOutputStream(new File("formulaDemo.xlsx"));
  workbook.write(out);
  out.close();
  System.out.println("Excel with foumula cells written successfully");    
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}

Similarly, we want to read a file with formula cells and use the following logic to evaluate formula cells.

Java program to evaluate formulas in an excel file using Apache POI library.

FileInputStream file = new FileInputStream(new File("formulaDemo.xlsx"));

//Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);

//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {

  Row row = rowIterator.next();
  //For each row, iterate through all the columns
  Iterator<Cell> cellIterator = row.cellIterator();
   
  while (cellIterator.hasNext()) {

    Cell cell = cellIterator.next();

    //Check the cell type after eveluating formulae
    //If it is formula cell, it will be evaluated otherwise no change will happen
    switch (evaluator.evaluateInCell(cell).getCellType()) {
    
      case Cell.CELL_TYPE_NUMERIC:
          System.out.print(cell.getNumericCellValue() + "tt");
          break;
      case Cell.CELL_TYPE_STRING:
          System.out.print(cell.getStringCellValue() + "tt");
          break;
      case Cell.CELL_TYPE_FORMULA:
          //Not again
          break;
      }
  }
  System.out.println("");
}
file.close();

Program Output:

Pricipal        RoI         T       Interest (P r t)        
14500.0         9.25        3.0     402375.0  

6. Formatting the Cells

So far we have seen examples of reading/writing and excel files using Apache POI. But, when creating a report in an excel file, it is essential to add formatting on cells that fit into any pre-determined criteria.

This formatting can be a different coloring based on a specific value range, expiry date limit etc.

In the below examples, we are taking a couple of such cell formatting examples for various purposes.

6.1. Cell value in a specific range

This code will color any cell in a range whose value is between a configured range. [e.g., between 50 and 70]

static void basedOnValue(Sheet sheet) 
{
    //Creating some random values
    sheet.createRow(0).createCell(0).setCellValue(84);
    sheet.createRow(1).createCell(0).setCellValue(74);
    sheet.createRow(2).createCell(0).setCellValue(50);
    sheet.createRow(3).createCell(0).setCellValue(51);
    sheet.createRow(4).createCell(0).setCellValue(49);
    sheet.createRow(5).createCell(0).setCellValue(41);
 
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
 
    //Condition 1: Cell Value Is   greater than  70   (Blue Fill)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
 
    //Condition 2: Cell Value Is  less than      50   (Green Fill)
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.GREEN.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
 
    CellRangeAddress[] regions = {
            CellRangeAddress.valueOf("A1:A6")
    };
 
    sheetCF.addConditionalFormatting(regions, rule1, rule2);
}

6.2. Highlight Duplicate Values

Highlight all cells which have duplicate values in observed cells.

static void formatDuplicates(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Code");
    sheet.createRow(1).createCell(0).setCellValue(4);
    sheet.createRow(2).createCell(0).setCellValue(3);
    sheet.createRow(3).createCell(0).setCellValue(6);
    sheet.createRow(4).createCell(0).setCellValue(3);
    sheet.createRow(5).createCell(0).setCellValue(5);
    sheet.createRow(6).createCell(0).setCellValue(8);
    sheet.createRow(7).createCell(0).setCellValue(0);
    sheet.createRow(8).createCell(0).setCellValue(2);
    sheet.createRow(9).createCell(0).setCellValue(8);
    sheet.createRow(10).createCell(0).setCellValue(6);
 
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
 
    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);
 
    CellRangeAddress[] regions = {
            CellRangeAddress.valueOf("A2:A11")
    };
 
    sheetCF.addConditionalFormatting(regions, rule1);
 
    sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted.  " +
            "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1   (Blue Font)");
}

6.3. Alternate Color Rows in Different Colors

A simple code to color each alternate row in a different color.

static void shadeAlt(Sheet sheet) {
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
 
    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
 
    CellRangeAddress[] regions = {
            CellRangeAddress.valueOf("A1:Z100")
    };
 
    sheetCF.addConditionalFormatting(regions, rule1);
 
    sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows");
    sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is  =MOD(ROW(),2)   (Light Green Fill)");
}

6.4. Color amounts that are going to expire in the next 30 days

A handy code for financial projects which keeps track of deadlines.

static void expiryInNext30Days(Sheet sheet) 
{
    CellStyle style = sheet.getWorkbook().createCellStyle();
    style.setDataFormat((short)BuiltinFormats.getBuiltinFormat("d-mmm"));
 
    sheet.createRow(0).createCell(0).setCellValue("Date");
    sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
    sheet.createRow(2).createCell(0).setCellFormula("A2+1");
    sheet.createRow(3).createCell(0).setCellFormula("A3+1");
 
    for(int rownum = 1; rownum <= 3; rownum++) sheet.getRow(rownum).getCell(0).setCellStyle(style);
 
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
 
    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);
 
    CellRangeAddress[] regions = {
            CellRangeAddress.valueOf("A2:A4")
    };
 
    sheetCF.addConditionalFormatting(regions, rule1);
 
    sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted");
}

I am ending this apache poi tutorial here to keep the post within a limit.

7. Conclusion

In this tutorial, we learned to read excel, write excel, set and evaluate formula cells, and format the cells with color codings using the Apache POI library.

Happy Learning !!

Source Code on Github