Read / Write/Update Excel file in Java using Apache POI HssfWorkBook

Read / Write/Update Excel file in Java using Apache POI

I found a nice article to use Apache POI ,which I wanted to share here .Hope in your development you can take help from this article.


Apache POI is a powerful Java library to work with different Microsoft Office file formats such as Excel, Power point, Visio, MS Word etc. The name POI was originally an acronym for Poor Obfuscation Implementation, referring humorously to the fact that the file formats seemed to be deliberately obfuscated, but poorly, since they were successfully reverse-engineered.
In this tutorial we will use Apache POI library to perform different functions on Microsoft Excel spreadsheet.
Let’s get started.
Tools & Technologies:
  1. Java JDK 1.5 or above
  2. Apache POI library v3.8 or above (download)
  3. Eclipse 3.2 above (optional)

Read Excel File


To read an excel file, Apache POI provides certain easy-to-use APIs. In below sample code we use different classes from POI library to read content of cell from excel file. This is for quick reference.
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//..
FileInputStream file = new FileInputStream(new File("C:\\test.xls"));
             
//Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
//Get iterator to all the rows in current sheet
Iterator<Row> rowIterator = sheet.iterator();
//Get iterator to all cells of current row
Iterator<Cell> cellIterator = row.cellIterator();

Notice how each class in POI library starts with HSSF prefix! e.g. HSSFWorkbook, HSSFSheet etc. HSSF stands for Horrible SpreadSheet Format! I’m not kidding.. It really is.
Similar to HSSF, POI has different prefix for other file formats too:
  1. HSSF (Horrible SpreadSheet Format) – reads and writes Microsoft Excel (XLS) format files.
  2. XSSF (XML SpreadSheet Format) – reads and writes Office Open XML (XLSX) format files.
  3. HPSF (Horrible Property Set Format) – reads “Document Summary” information from Microsoft Office files.
  4. HWPF (Horrible Word Processor Format) – aims to read and write Microsoft Word 97 (DOC) format files.
  5. HSLF (Horrible Slide Layout Format) – a pure Java implementation for Microsoft PowerPoint files.
  6. HDGF (Horrible DiaGram Format) – an initial pure Java implementation for Microsoft Visio binary files.
  7. HPBF (Horrible PuBlisher Format) – a pure Java implementation for Microsoft Publisher files.
  8. HSMF (Horrible Stupid Mail Format) – a pure Java implementation for Microsoft Outlook MSG files
  9. DDF (Dreadful Drawing Format) – a package for decoding the Microsoft Office Drawing format.
Consider a sample excel file:
test.xls
java read excel file

We will read above xls file using Apache POI and prints the data.
try {
     
    FileInputStream file = new FileInputStream(new File("C:\\test.xls"));
     
    //Get the workbook instance for XLS file
    HSSFWorkbook workbook = new HSSFWorkbook(file);
    //Get first sheet from the workbook
    HSSFSheet sheet = workbook.getSheetAt(0);
     
    //Iterate through each rows from first sheet
    Iterator<Row> rowIterator = sheet.iterator();
    while(rowIterator.hasNext()) {
        Row row = rowIterator.next();
         
        //For each row, iterate through each columns
        Iterator<Cell> cellIterator = row.cellIterator();
        while(cellIterator.hasNext()) {
             
            Cell cell = cellIterator.next();
             
            switch(cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue() + "\t\t");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.print(cell.getNumericCellValue() + "\t\t");
                    break;
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getStringCellValue() + "\t\t");
                    break;
            }
        }
        System.out.println("");
    }
    file.close();
    FileOutputStream out =
        new FileOutputStream(new File("C:\\test.xls"));
    workbook.write(out);
    out.close();
     
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}
The above code is self explanatory. It read the sheet from workbook and iterate through each row and cell to print its values. Just note how we use different methods like getBooleanCellValue,getNumericCellValue etc to read cell value. Before reading a cell content, we need to first determine its type using method cell.getCellType() and then call appropriate method to read content.
Output:
Emp Id      Name        Salary     
1.0     John        2000000.0      
2.0     Dean        420000.0       
3.0     Sam     280000.0       
4.0     Cass        6000000.0  


Create New Excel File


Let us create a new excel file and write data in it. Following is the API which we will use for this purpose.
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//..
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Sample sheet");
//Create a new row in current sheet
Row row = sheet.createRow(0);
//Create a new cell in current row
Cell cell = row.createCell(0);
//Set value to new value
cell.setCellValue("Blahblah");
Below is the complete code that writes a new excel with dummy data:
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Sample sheet");
Map<String, Object[]> data = new HashMap<String, Object[]>();
data.put("1", new Object[] {"Emp No.", "Name", "Salary"});
data.put("2", new Object[] {1d, "John", 1500000d});
data.put("3", new Object[] {2d, "Sam", 800000d});
data.put("4", new Object[] {3d, "Dean", 700000d});
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 Date)
            cell.setCellValue((Date)obj);
        else if(obj instanceof Boolean)
            cell.setCellValue((Boolean)obj);
        else if(obj instanceof String)
            cell.setCellValue((String)obj);
        else if(obj instanceof Double)
            cell.setCellValue((Double)obj);
    }
}
try {
    FileOutputStream out =
            new FileOutputStream(new File("C:\\new.xls"));
    workbook.write(out);
    out.close();
    System.out.println("Excel written successfully..");
     
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}
Output: new.xls
java-write-excel file


Update Existing Excel File

Updating an existing excel file is straight forward. Open the excel using different API that we discussed above and set the cell’s value. One thing we need to note here is that we can update the excel file only when we close it first.
update.xls
java-update-excel-before
Following Java code read the above excel file and doubles the salary of each employee:
try {
    FileInputStream file = new FileInputStream(new File("C:\\update.xls"));
    HSSFWorkbook workbook = new HSSFWorkbook(file);
    HSSFSheet sheet = workbook.getSheetAt(0);
    Cell cell = null;
    //Update the value of cell
    cell = sheet.getRow(1).getCell(2);
    cell.setCellValue(cell.getNumericCellValue() * 2);
    cell = sheet.getRow(2).getCell(2);
    cell.setCellValue(cell.getNumericCellValue() * 2);
    cell = sheet.getRow(3).getCell(2);
    cell.setCellValue(cell.getNumericCellValue() * 2);
     
    file.close();
     
    FileOutputStream outFile =new FileOutputStream(new File("C:\\update.xls"));
    workbook.write(outFile);
    outFile.close();
     
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}
Steps to update excel file will be:
  1. Open excel file in input mode (inputstream)
  2. Use POI API and read the excel content
  3. Update cell’s value using different setCellValue methods.
  4. Close the excel input file (inputstream)
  5. Open same excel file in output mode (outputstream)
  6. Write content of updated workbook in output file
  7. Close output excel file
Output: update.xls
java-update-excel-after


Adding Formulas

Apache POI provides API to add excel formulas to cell programmatically. Following method that comes handy for this:
cell.setCellFormula("someformula")
For example:
cell.setCellFormula("A2*B2*C5")
//or
cell.setCellFormula("SUM(A1:A7)")
Note: Formula string should not start with equal sign (=)
Thus, following is incorrect way of adding formula:
cell.setCellFormula("=A2*B2*C5") //Ops! Won't work
The above code will throw:
org.apache.poi.ss.formula.FormulaParseException:
The specified formula '=A2*B2*C5' starts with an equals sign which is not allowed.
Following Java code creates a new excel sheet which calculates Simple Interest. It defines Principal amount, Rate of Interest and Tenure. We add an excel formula to calculate interest.
HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Calculate Simple Interest");
    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("Pricipal Amount (P)");
    header.createCell(1).setCellValue("Rate of Interest (r)");
    header.createCell(2).setCellValue("Tenure (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("C:\\formula.xls"));
        workbook.write(out);
        out.close();
        System.out.println("Excel written successfully..");
         
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
Output: formula.xls
java-excel-add-formula

Triggering Existing Excel Formulas

In certain cases your excel file might have formula defined and you may want to trigger those formulas since you updated it using POI. Following code snippet will do the trick.
FileInputStream fis = new FileInputStream("/somepath/test.xls");
Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("C:\\test.xls")
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
    Sheet sheet = wb.getSheetAt(sheetNum);
    for(Row r : sheet) {
        for(Cell c : r) {
            if(c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                evaluator.evaluateFormulaCell(c);
            }
        }
    }
}
We use FormulaEvaluator class to evaluate formula defined in each of the cell.

Adding Styles to Cell

Adding style to a cell is also piece of cake. Check following example which creates two new cell one with bold font and another with italic and add text to it.
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Style example");
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("This is bold");
cell.setCellStyle(style);
font = workbook.createFont();
font.setItalic(true);
style = workbook.createCellStyle();
style.setFont(font);
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue("This is italic");
cell.setCellStyle(style);
try {
    FileOutputStream out = new FileOutputStream(new File("C:\\style.xls"));
    workbook.write(out);
    out.close();
    System.out.println("Excel written successfully..");
     
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}
Output: style.xls
java-excel-cell-style
I hope this article is useful.










Comments

  1. This comment has been removed by the author.

    ReplyDelete
  2. You can read, write, update or even convert excel files to many other format with Aspose.Cells for Java Library.

    You can get sample codes from their documentation page to use them in your java application.

    ReplyDelete

Post a Comment