Saturday, July 18, 2015

Apache POI: How to create, write and read excel file in java

Typically, spreadsheets are widely used in finance domain and accounting to facilitate the calculation and creation of bills, management reports, etc. If part of your required application of such operations: creation, reading or writing, several API are available and most efficient is Apache POI API.

The API also manages POI Word and PowerPoint documents, over time users have had more confidence.

Download Apache POI API

You can download Apache POI. Then, import  the following .jar into your project:
- poi
- poi-OOXML
- poi-OOXML-schemas
- xmlbeans

Create and write

The two main classes which processes the Excel file are:

HSSFWorkbook: for Microsoft Excel 97 and 2003 files with xls extension.
XSSFWorkbook: for Microsoft Excel 2007 with .xlsx file extension.

The following code creates an excel file with the values of different types:

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class writeDemo{
    public static void main(String[] args) {
   
       //create a blanc document
       XSSFWorkbook wb = new XSSFWorkbook();
       //create a black sheet
       Sheet sheet = wb.createSheet("new sheet");
       //create a new row 0
       Row row = sheet.createRow((short)0);
       //create a new cell
       Cell cell = row.createCell(0);
       //insert value in the created cell
       cell.setCellValue(1.4);
   
       //add other cells with different types
       /*int*/row.createCell(1).setCellValue(7);
       /*int*/row.createCell(2).setCellValue(99);
       /*string*/row.createCell(3).setCellValue("string");
       /*boolean*/row.createCell(4).setCellValue(true);

       FileOutputStream fos;
       try {
         fos= new FileOutputStream("newFile.xlsx");
         wb.write(fos);
         fos.close();
       } catch (FileNotFoundException e) {
           e.printStackTrace();
       } catch (IOException e) {
           e.printStackTrace();
       }
    }
}
create excel file with java

The date to be inserted and the current time, the date format is created as follows:

//insert value in cell F1
cell = row.createCell((short) 6);
cell.setCellValue(new Date());
XSSFCellStyle cellStyle = wb.createCellStyle();
XSSFDataFormat xssfDataFormat = wb.createDataFormat();
//create date and time format
cellStyle.setDataFormat(xssfDataFormat.getFormat("dd/mm/yyyy h:mm"));
cell.setCellStyle(cellStyle);
insert date in excel file with java

Text Formatting

Text formatting includes: font, size, Italic / Bold / Underline, color, background and alignment.

Here is an example of alignment that will be applied to the cell Date:

//Row high
row.setHeightInPoints(20);
//horizontal alignement 
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
//vertical alignement
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
text formating excel java

Create new font

/*create a new font*/
Font font = wb.createFont();
//size: 13px
font.setFontHeightInPoints((short)13);
font.setFontName("Courier New");
font.setItalic(true);
font.setBold(true);

/*create a new style*/
CellStyle cs = wb.createCellStyle();
cs.setFont(font);
//apply the style to cell 3(D1)
row.getCell(3).setCellStyle(cs);
change the excel font with java

Background color

/*change the background color*/
XSSFCellStyle csColor = wb.createCellStyle();
csColor.setFillForegroundColor(new XSSFColor(new Color(194, 154, 250)));
csColor.setFillPattern(csColor.SOLID_FOREGROUND);
//apply to the cell 3
row.getCell(2).setCellStyle(csColor);
        
/*change font color*/
Font font = wb.createFont();
font.setColor((short)45);
CellStyle csCF = wb.createCellStyle();
csCF.setFont(font);
//apply style to cell 0
row.getCell(0).setCellStyle(csCF);
change background color excel with java

Merging cells

In this example, we will merge, center horizontally and vertically four cells B2, C2, B3 and C3 with the addMergedRegion method that takes as parameters the range of cells to be fused.

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class mergingCells{

   public static void main(String[] args) throws FileNotFoundException {
       Workbook wb = new HSSFWorkbook();
       Sheet sheet = wb.createSheet("sheet1");

       Row row = sheet.createRow((short) 1);
       Cell cell = row.createCell((short) 1);
       cell.setCellValue("testing merge cells");

       sheet.addMergedRegion(new CellRangeAddress(
             1, //first row B2
             2, //last row B3
             1, //first column C2
             2  //last column C3 
       ));
       /*Center alignment*/
       cell.getCellStyle().setAlignment((short)2);
       cell.getCellStyle().setVerticalAlignment((short)1);
     
       FileOutputStream fs = null;
       try {
         fs = new FileOutputStream("Mergingdemo.xlsx");
         wb.write(fs);
         fs.close();
       } catch (IOException e) {
          e.printStackTrace();
       }
   }
}
merge cells in excel with java

Using Formulas

Excel is primarily used in the calculation and use of sometimes complex formulas in cells result. Apache poi provides a very effective means to add and test cells with their formulas.

The following code handles a simple calculation of the average 4 semesters. The formula is (A2 + B2 + C2 + D2) / 4.

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class FormulaDemo{

  public static void main(String[] args) {
     XSSFWorkbook wb = new XSSFWorkbook();
     XSSFSheet sheet = wb.createSheet("Average");

     Row row = sheet.createRow((short) 0);
     row.createCell(0).setCellValue("January");
     row.createCell(1).setCellValue("February");
     row.createCell(2).setCellValue("March");
     row.createCell(3).setCellValue("April");
     row.createCell(4).setCellValue("Average");
     
     Row row1 = sheet.createRow((short) 1);
     row1.createCell(0).setCellValue(4.5);
     row1.createCell(1).setCellValue(15.4);
     row1.createCell(2).setCellValue(3.4);
     row1.createCell(3).setCellValue(6);
     row1.createCell(4).setCellFormula("(A2+B2+C2+D2)/4");

     try {
         FileOutputStream out = new FileOutputStream(new File("formulademo.xlsx"));
         wb.write(out);
         out.close();
         System.out.println("Le fichier excel a été créé avec succés");
           
     } catch (FileNotFoundException e) {
         e.printStackTrace();
     } catch (IOException e) {
         e.printStackTrace();
     }
  }
}
using formula in excel with java

Evaluate and browse cells

To view the value of a cell, you must know its type. Apache poi provides FormulaEvaluator.evaluateFormulaCell class that checks if the cell contains a formula. If so, it evaluates and returns the type of the formula.

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class IterateDemo{

    public static void main(String[] args) throws IOException {
       FileInputStream fichier = new FileInputStream(new File("formuladomo.xlsx"));
       //create workbook instance that refers to xlsx file
       XSSFWorkbook wb = new XSSFWorkbook(fichier);
       XSSFSheet sheet = wb.getSheetAt(0);
  
       FormulaEvaluator formulaEvaluator = 
                     wb.getCreationHelper().createFormulaEvaluator();
  
       for (Row ligne : sheet) {//iterate rows
         for (Cell cell : ligne) {//iterate columns
           //cell type
           switch (formulaEvaluator.evaluateInCell(cell).getCellType())
           {
                 case Cell.CELL_TYPE_NUMERIC:
                     System.out.print(cell.getNumericCellValue() + "\t\t");
                     break;
                 case Cell.CELL_TYPE_STRING:
                     System.out.print(cell.getStringCellValue() + "\t");
                     break;
           }
         }
         System.out.println();
       }  
    }
}
Execution:

January February March April Average
4.5  15.4  3.4  6.0  7.325 
For more informations read from apache documentation http://poi.apache.org/spreadsheet/quick-guide.html

1 comment:

  1. How to deal with large excle files?
    I need to read large file and add some rows and write it, can you please help me?

    ReplyDelete