{"id":439,"date":"2013-05-28T15:17:53","date_gmt":"2013-05-28T15:17:53","guid":{"rendered":"http:\/\/a1webdesignteam.com\/blog\/?p=439"},"modified":"2013-05-28T15:17:53","modified_gmt":"2013-05-28T15:17:53","slug":"read-write-excel-file-java-apache-poi","status":"publish","type":"post","link":"https:\/\/a1webdesignteam.com\/blog\/read-write-excel-file-java-apache-poi\/","title":{"rendered":"Read \/ Write Excel File In Java Using Apache POI"},"content":{"rendered":"

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\u00a0Poor Obfuscation Implementation<\/strong>, referring humorously to the fact that the file formats seemed to be deliberately obfuscated, but poorly, since they were successfully reverse-engineered.<\/p>\n

In this tutorial we will use Apache POI library to perform different functions on Microsoft Excel spreadsheet.<\/p>\n

Let\u2019s get started.<\/p>\n

Tools & Technologies:<\/strong><\/p>\n

    \n
  1. Java JDK 1.5 or above<\/li>\n
  2. Apache POI library v3.8 or above (download<\/a>)<\/li>\n
  3. Eclipse 3.2 above (optional)<\/li>\n<\/ol>\n

    1. Add Apache POI dependency<\/h2>\n

    Make sure to include apache poi jar file to your project. If your project uses Maven as dependency management, add following in your Pom.xml file.<\/p>\n

    \n
    \n\n\n\n
    \n
    \n
    <<\/code>dependency<\/code>><\/code><\/div>\n
    \u00a0\u00a0\u00a0\u00a0<\/code><<\/code>groupId<\/code>>org.apache.poi<\/<\/code>groupId<\/code>><\/code><\/div>\n
    \u00a0\u00a0\u00a0\u00a0<\/code><<\/code>artifactId<\/code>>poi<\/<\/code>artifactId<\/code>><\/code><\/div>\n
    \u00a0\u00a0\u00a0\u00a0<\/code><<\/code>version<\/code>>3.8<\/<\/code>version<\/code>><\/code><\/div>\n
    <\/<\/code>dependency<\/code>><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

    If you are not using Maven then you can directly add required JAR files in your classpath.<\/p>\n

      \n
    1. Download\u00a0poi-2.5.1.jar<\/code>(or in this case 3.8) jar file.<\/li>\n
    2. Include this file in your projects class path.<\/li>\n
    3. Create new java project in eclipse with auto generated main function.<\/li>\n<\/ol>\n

      2. Read Excel File<\/h2>\n

      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.<\/p>\n

      \n
      \n\n\n\n
      \n
      \n
      import<\/code> org.apache.poi.hssf.usermodel.HSSFSheet;<\/code><\/div>\n
      import<\/code> org.apache.poi.hssf.usermodel.HSSFWorkbook;<\/code><\/div>\n
      \/\/..<\/code><\/div>\n
      FileInputStream file = <\/code>new<\/code> FileInputStream(<\/code>new<\/code> File(<\/code>\"C:\\\\test.xls\"<\/code>));<\/code><\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
      \/\/Get the workbook instance for XLS file <\/code><\/div>\n
      HSSFWorkbook workbook = <\/code>new<\/code> HSSFWorkbook(file);<\/code><\/div>\n
      <\/div>\n
      \/\/Get first sheet from the workbook<\/code><\/div>\n
      HSSFSheet sheet = workbook.getSheetAt(<\/code>0<\/code>);<\/code><\/div>\n
      <\/div>\n
      \/\/Get iterator to all the rows in current sheet<\/code><\/div>\n
      Iterator<Row> rowIterator = sheet.iterator();<\/code><\/div>\n
      <\/div>\n
      \/\/Get iterator to all cells of current row<\/code><\/div>\n
      Iterator<Cell> cellIterator = row.cellIterator();<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

      Notice how each class in POI library starts with\u00a0HSSF<\/strong>\u00a0prefix! e.g. HSSFWorkbook, HSSFSheet etc. HSSF stands for\u00a0Horrible SpreadSheet Format<\/strong>! I\u2019m not kidding.. It really is.<\/p>\n

      Similar to HSSF, POI has different prefix for other file formats too:<\/p>\n

        \n
      1. HSSF (Horrible SpreadSheet Format) \u2013 reads and writes Microsoft Excel (XLS) format files.<\/li>\n
      2. XSSF (XML SpreadSheet Format) \u2013 reads and writes Office Open XML (XLSX) format files.<\/li>\n
      3. HPSF (Horrible Property Set Format) \u2013 reads \u201cDocument Summary\u201d information from Microsoft Office files.<\/li>\n
      4. HWPF (Horrible Word Processor Format) \u2013 aims to read and write Microsoft Word 97 (DOC) format files.<\/li>\n
      5. HSLF (Horrible Slide Layout Format) \u2013 a pure Java implementation for Microsoft PowerPoint files.<\/li>\n
      6. HDGF (Horrible DiaGram Format) \u2013 an initial pure Java implementation for Microsoft Visio binary files.<\/li>\n
      7. HPBF (Horrible PuBlisher Format) \u2013 a pure Java implementation for Microsoft Publisher files.<\/li>\n
      8. HSMF (Horrible Stupid Mail Format) \u2013 a pure Java implementation for Microsoft Outlook MSG files<\/li>\n
      9. DDF (Dreadful Drawing Format) \u2013 a package for decoding the Microsoft Office Drawing format.<\/li>\n<\/ol>\n

        Consider a sample excel file:<\/p>\n

        test.xls<\/em>
        \n\"java<\/p>\n

        We will read above xls file using Apache POI and prints the data.<\/p>\n

        \n
        \n\n\n\n
        \n
        \n
        try<\/code> {<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>FileInputStream file = <\/code>new<\/code> FileInputStream(<\/code>new<\/code> File(<\/code>\"C:\\\\test.xls\"<\/code>));<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>\/\/Get the workbook instance for XLS file <\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>HSSFWorkbook workbook = <\/code>new<\/code> HSSFWorkbook(file);<\/code><\/div>\n
        <\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>\/\/Get first sheet from the workbook<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>HSSFSheet sheet = workbook.getSheetAt(<\/code>0<\/code>);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>\/\/Iterate through each rows from first sheet<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>Iterator<Row> rowIterator = sheet.iterator();<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>while<\/code>(rowIterator.hasNext()) {<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>Row row = rowIterator.next();<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>\/\/For each row, iterate through each columns<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>Iterator<Cell> cellIterator = row.cellIterator();<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>while<\/code>(cellIterator.hasNext()) {<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>Cell cell = cellIterator.next();<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>switch<\/code>(cell.getCellType()) {<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>case<\/code> Cell.CELL_TYPE_BOOLEAN:<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>System.out.print(cell.getBooleanCellValue() + <\/code>\"\\t\\t\"<\/code>);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>break<\/code>;<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>case<\/code> Cell.CELL_TYPE_NUMERIC:<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>System.out.print(cell.getNumericCellValue() + <\/code>\"\\t\\t\"<\/code>);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>break<\/code>;<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>case<\/code> Cell.CELL_TYPE_STRING:<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>System.out.print(cell.getStringCellValue() + <\/code>\"\\t\\t\"<\/code>);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>break<\/code>;<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>}<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>}<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>System.out.println(<\/code>\"\"<\/code>);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>}<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>file.close();<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>FileOutputStream out = <\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>new<\/code> FileOutputStream(<\/code>new<\/code> File(<\/code>\"C:\\\\test.xls\"<\/code>));<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>workbook.write(out);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>out.close();<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
        } <\/code>catch<\/code> (FileNotFoundException e) {<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>e.printStackTrace();<\/code><\/div>\n
        } <\/code>catch<\/code> (IOException e) {<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>e.printStackTrace();<\/code><\/div>\n
        }<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

        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\u00a0getBooleanCellValue<\/code>,getNumericCellValue<\/code>\u00a0etc to read cell value. Before reading a cell content, we need to first determine its type using method\u00a0cell.getCellType()<\/code>\u00a0and then call appropriate method to read content.<\/p>\n

        Output:<\/strong><\/p>\n

        \n
        \n\n\n\n
        \n
        \n
        Emp Id\u00a0\u00a0\u00a0\u00a0\u00a0 Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Salary\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code><\/div>\n
        1.0\u00a0\u00a0\u00a0\u00a0 John\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2000000.0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code><\/div>\n
        2.0\u00a0\u00a0\u00a0\u00a0 Dean\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 420000.0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code><\/div>\n
        3.0\u00a0\u00a0\u00a0\u00a0 Sam\u00a0\u00a0\u00a0\u00a0 280000.0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code><\/div>\n
        4.0\u00a0\u00a0\u00a0\u00a0 Cass\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6000000.0\u00a0\u00a0 <\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

        3. Create New Excel File<\/h2>\n

        Let us create a new excel file and write data in it. Following is the API which we will use for this purpose.<\/p>\n

        \n
        \n\n\n\n
        \n
        \n
        import<\/code> org.apache.poi.hssf.usermodel.HSSFSheet;<\/code><\/div>\n
        import<\/code> org.apache.poi.hssf.usermodel.HSSFWorkbook;<\/code><\/div>\n
        \/\/..<\/code><\/div>\n
        HSSFWorkbook workbook = <\/code>new<\/code> HSSFWorkbook();<\/code><\/div>\n
        HSSFSheet sheet = workbook.createSheet(<\/code>\"Sample sheet\"<\/code>);<\/code><\/div>\n
        \/\/Create a new row in current sheet<\/code><\/div>\n
        Row row = sheet.createRow(<\/code>0<\/code>);<\/code><\/div>\n
        \/\/Create a new cell in current row<\/code><\/div>\n
        Cell cell = row.createCell(<\/code>0<\/code>);<\/code><\/div>\n
        \/\/Set value to new value<\/code><\/div>\n
        cell.setCellValue(<\/code>\"Blahblah\"<\/code>);<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

        Below is the complete code that writes a new excel with dummy data:<\/p>\n

        \n
        \n\n\n\n
        \n
        \n
        HSSFWorkbook workbook = <\/code>new<\/code> HSSFWorkbook();<\/code><\/div>\n
        HSSFSheet sheet = workbook.createSheet(<\/code>\"Sample sheet\"<\/code>);<\/code><\/div>\n
        <\/div>\n
        Map<String, Object[]> data = <\/code>new<\/code> HashMap<String, Object[]>();<\/code><\/div>\n
        data.put(<\/code>\"1\"<\/code>, <\/code>new<\/code> Object[] {<\/code>\"Emp No.\"<\/code>, <\/code>\"Name\"<\/code>, <\/code>\"Salary\"<\/code>});<\/code><\/div>\n
        data.put(<\/code>\"2\"<\/code>, <\/code>new<\/code> Object[] {1d, <\/code>\"John\"<\/code>, 1500000d});<\/code><\/div>\n
        data.put(<\/code>\"3\"<\/code>, <\/code>new<\/code> Object[] {2d, <\/code>\"Sam\"<\/code>, 800000d});<\/code><\/div>\n
        data.put(<\/code>\"4\"<\/code>, <\/code>new<\/code> Object[] {3d, <\/code>\"Dean\"<\/code>, 700000d});<\/code><\/div>\n
        <\/div>\n
        Set<String> keyset = data.keySet();<\/code><\/div>\n
        int<\/code> rownum = <\/code>0<\/code>;<\/code><\/div>\n
        for<\/code> (String key : keyset) {<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>Row row = sheet.createRow(rownum++);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>Object [] objArr = data.get(key);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>int<\/code> cellnum = <\/code>0<\/code>;<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>for<\/code> (Object obj : objArr) {<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>Cell cell = row.createCell(cellnum++);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>if<\/code>(obj <\/code>instanceof<\/code> Date) <\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>cell.setCellValue((Date)obj);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>else<\/code> if<\/code>(obj <\/code>instanceof<\/code> Boolean)<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>cell.setCellValue((Boolean)obj);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>else<\/code> if<\/code>(obj <\/code>instanceof<\/code> String)<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>cell.setCellValue((String)obj);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>else<\/code> if<\/code>(obj <\/code>instanceof<\/code> Double)<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>cell.setCellValue((Double)obj);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>}<\/code><\/div>\n
        }<\/code><\/div>\n
        <\/div>\n
        try<\/code> {<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>FileOutputStream out = <\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>new<\/code> FileOutputStream(<\/code>new<\/code> File(<\/code>\"C:\\\\new.xls\"<\/code>));<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>workbook.write(out);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>out.close();<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>System.out.println(<\/code>\"Excel written successfully..\"<\/code>);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
        } <\/code>catch<\/code> (FileNotFoundException e) {<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>e.printStackTrace();<\/code><\/div>\n
        } <\/code>catch<\/code> (IOException e) {<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>e.printStackTrace();<\/code><\/div>\n
        }<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

        Output:<\/strong>\u00a0new.xls<\/em>
        \n\"java-write-excel<\/p>\n

        4. Update Existing Excel File<\/h2>\n

        Updating an existing excel file is straight forward. Open the excel using different API that we discussed above and set the cell\u2019s value. One thing we need to note here is that we can update the excel file only when we close it first.<\/p>\n

        update.xls<\/em>
        \n\"java-update-excel-before\"<\/p>\n

        Following Java code read the above excel file and doubles the salary of each employee:<\/p>\n

        \n
        \n\n\n\n
        \n
        \n
        try<\/code> {<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>FileInputStream file = <\/code>new<\/code> FileInputStream(<\/code>new<\/code> File(<\/code>\"C:\\\\update.xls\"<\/code>));<\/code><\/div>\n
        <\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>HSSFWorkbook workbook = <\/code>new<\/code> HSSFWorkbook(file);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>HSSFSheet sheet = workbook.getSheetAt(<\/code>0<\/code>);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>Cell cell = <\/code>null<\/code>;<\/code><\/div>\n
        <\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>\/\/Update the value of cell<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>cell = sheet.getRow(<\/code>1<\/code>).getCell(<\/code>2<\/code>);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>cell.setCellValue(cell.getNumericCellValue() * <\/code>2<\/code>);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>cell = sheet.getRow(<\/code>2<\/code>).getCell(<\/code>2<\/code>);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>cell.setCellValue(cell.getNumericCellValue() * <\/code>2<\/code>);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>cell = sheet.getRow(<\/code>3<\/code>).getCell(<\/code>2<\/code>);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>cell.setCellValue(cell.getNumericCellValue() * <\/code>2<\/code>);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>file.close();<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>FileOutputStream outFile =<\/code>new<\/code> FileOutputStream(<\/code>new<\/code> File(<\/code>\"C:\\\\update.xls\"<\/code>));<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>workbook.write(outFile);<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>outFile.close();<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
        } <\/code>catch<\/code> (FileNotFoundException e) {<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>e.printStackTrace();<\/code><\/div>\n
        } <\/code>catch<\/code> (IOException e) {<\/code><\/div>\n
        \u00a0\u00a0\u00a0\u00a0<\/code>e.printStackTrace();<\/code><\/div>\n
        }<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

        Steps to update excel file will be:<\/p>\n

          \n
        1. Open excel file in input mode (inputstream)<\/li>\n
        2. Use POI API and read the excel content<\/li>\n
        3. Update cell\u2019s value using different\u00a0setCellValue<\/code>\u00a0methods.<\/li>\n
        4. Close the excel input file (inputstream)<\/li>\n
        5. Open same excel file in output mode (outputstream)<\/li>\n
        6. Write content of updated workbook in output file<\/li>\n
        7. Close output excel file<\/li>\n<\/ol>\n

          Output:<\/strong>\u00a0update.xls<\/em>
          \n\"java-update-excel-after\"<\/p>\n

          5. Adding Formulas<\/h2>\n

          Apache POI provides API to add excel formulas to cell programmatically. Following method that comes handy for this:<\/p>\n

          \n
          \n\n\n\n
          \n
          \n
          cell.setCellFormula(<\/code>\"someformula\"<\/code>)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

          For example:<\/p>\n

          \n
          \n\n\n\n
          \n
          \n
          cell.setCellFormula(<\/code>\"A2*B2*C5\"<\/code>)<\/code><\/div>\n
          \/\/or<\/code><\/div>\n
          cell.setCellFormula(<\/code>\"SUM(A1:A7)\"<\/code>)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

          Note:<\/strong>\u00a0Formula string should not start with equal sign (=)
          \nThus, following is incorrect way of adding formula:<\/p>\n

          \n
          \n\n\n\n
          \n
          \n
          cell.setCellFormula(<\/code>\"=A2*B2*C5\"<\/code>) <\/code>\/\/Ops! Won't work<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

          The above code will throw:<\/p>\n

          \n
          \n\n\n\n
          \n
          \n
          org.apache.poi.ss.formula.FormulaParseException: <\/code><\/div>\n
          The specified formula '=A2*B2*C5' starts with an equals sign which is not allowed.<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

          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.<\/p>\n

          \n
          \n\n\n\n
          \n
          \n
          HSSFWorkbook workbook = <\/code>new<\/code> HSSFWorkbook();<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>HSSFSheet sheet = workbook.createSheet(<\/code>\"Calculate Simple Interest\"<\/code>);<\/code><\/div>\n
          <\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>Row header = sheet.createRow(<\/code>0<\/code>);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>header.createCell(<\/code>0<\/code>).setCellValue(<\/code>\"Pricipal Amount (P)\"<\/code>);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>header.createCell(<\/code>1<\/code>).setCellValue(<\/code>\"Rate of Interest (r)\"<\/code>);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>header.createCell(<\/code>2<\/code>).setCellValue(<\/code>\"Tenure (t)\"<\/code>);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>header.createCell(<\/code>3<\/code>).setCellValue(<\/code>\"Interest (P r t)\"<\/code>);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>Row dataRow = sheet.createRow(<\/code>1<\/code>);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>dataRow.createCell(<\/code>0<\/code>).setCellValue(14500d);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>dataRow.createCell(<\/code>1<\/code>).setCellValue(<\/code>9.25<\/code>);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>dataRow.createCell(<\/code>2<\/code>).setCellValue(3d);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>dataRow.createCell(<\/code>3<\/code>).setCellFormula(<\/code>\"A2*B2*C2\"<\/code>);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>try<\/code> {<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>FileOutputStream out = <\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>new<\/code> FileOutputStream(<\/code>new<\/code> File(<\/code>\"C:\\\\formula.xls\"<\/code>));<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>workbook.write(out);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>out.close();<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>System.out.println(<\/code>\"Excel written successfully..\"<\/code>);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>} <\/code>catch<\/code> (FileNotFoundException e) {<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>e.printStackTrace();<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>} <\/code>catch<\/code> (IOException e) {<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>e.printStackTrace();<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>}<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

          Output:<\/strong>\u00a0formula.xls<\/em>
          \n\"java-excel-add-formula\"<\/p>\n

          Triggering Existing Excel Formulas<\/h4>\n

          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.<\/p>\n

          \n
          \n\n\n\n
          \n
          \n
          FileInputStream fis = <\/code>new<\/code> FileInputStream(<\/code>\"\/somepath\/test.xls\"<\/code>);<\/code><\/div>\n
          Workbook wb = <\/code>new<\/code> HSSFWorkbook(fis); <\/code>\/\/or new XSSFWorkbook(\"C:\\\\test.xls\")<\/code><\/div>\n
          FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();<\/code><\/div>\n
          for<\/code>(<\/code>int<\/code> sheetNum = <\/code>0<\/code>; sheetNum < wb.getNumberOfSheets(); sheetNum++) {<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>Sheet sheet = wb.getSheetAt(sheetNum);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>for<\/code>(Row r : sheet) {<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>for<\/code>(Cell c : r) {<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>if<\/code>(c.getCellType() == Cell.CELL_TYPE_FORMULA) {<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>evaluator.evaluateFormulaCell(c);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>}<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>}<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>}<\/code><\/div>\n
          }<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

          We use\u00a0FormulaEvaluator<\/code>\u00a0class to evaluate formula defined in each of the cell.<\/p>\n

          6. Adding Styles to Cell<\/h2>\n

          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.<\/p>\n

          \n
          \n\n\n\n
          \n
          \n
          HSSFWorkbook workbook = <\/code>new<\/code> HSSFWorkbook();<\/code><\/div>\n
          HSSFSheet sheet = workbook.createSheet(<\/code>\"Style example\"<\/code>);<\/code><\/div>\n
          <\/div>\n
          HSSFFont font = workbook.createFont();<\/code><\/div>\n
          font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);<\/code><\/div>\n
          HSSFCellStyle style = workbook.createCellStyle();<\/code><\/div>\n
          style.setFont(font);<\/code><\/div>\n
          <\/div>\n
          Row row = sheet.createRow(<\/code>0<\/code>);<\/code><\/div>\n
          Cell cell = row.createCell(<\/code>0<\/code>);<\/code><\/div>\n
          cell.setCellValue(<\/code>\"This is bold\"<\/code>);<\/code><\/div>\n
          cell.setCellStyle(style);<\/code><\/div>\n
          <\/div>\n
          <\/div>\n
          font = workbook.createFont();<\/code><\/div>\n
          font.setItalic(<\/code>true<\/code>);<\/code><\/div>\n
          style = workbook.createCellStyle();<\/code><\/div>\n
          style.setFont(font);<\/code><\/div>\n
          <\/div>\n
          row = sheet.createRow(<\/code>1<\/code>);<\/code><\/div>\n
          cell = row.createCell(<\/code>0<\/code>);<\/code><\/div>\n
          cell.setCellValue(<\/code>\"This is italic\"<\/code>);<\/code><\/div>\n
          cell.setCellStyle(style);<\/code><\/div>\n
          <\/div>\n
          try<\/code> {<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>FileOutputStream out = <\/code>new<\/code> FileOutputStream(<\/code>new<\/code> File(<\/code>\"C:\\\\style.xls\"<\/code>));<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>workbook.write(out);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>out.close();<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>System.out.println(<\/code>\"Excel written successfully..\"<\/code>);<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
          } <\/code>catch<\/code> (FileNotFoundException e) {<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>e.printStackTrace();<\/code><\/div>\n
          } <\/code>catch<\/code> (IOException e) {<\/code><\/div>\n
          \u00a0\u00a0\u00a0\u00a0<\/code>e.printStackTrace();<\/code><\/div>\n
          }<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

          Output:<\/strong>\u00a0style.xls<\/em>
          \n\"java-excel-cell-style\"<\/p>\n

          I hope this article is useful.<\/p>\n","protected":false},"excerpt":{"rendered":"

          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\u00a0Poor Obfuscation Implementation, referring humorously to the fact that the file formats seemed to be deliberately obfuscated, but poorly, since they were successfully reverse-engineered. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0},"categories":[25],"tags":[],"_links":{"self":[{"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/posts\/439"}],"collection":[{"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/comments?post=439"}],"version-history":[{"count":0,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/posts\/439\/revisions"}],"wp:attachment":[{"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/media?parent=439"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/categories?post=439"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/tags?post=439"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}