Apache POI: Create, read, and edit an Excel file in java

Generally, spreadsheets are used a lot in the fields of finance and accounting to facilitate the calculation and creation of invoices, management of reports, etc. If any part of your application requires such operations: create, read or write, several APIs are available and the most powerful is the API POI of APACHE.

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

Download

You can download the API from the page APACHE POI. After downloading, import the following .jar: in your project:

poi
poi-ooxml
poi-ooxml-schemas
xmlbeans

Authoring and writing to an xls

The two main classes that process excel files are:

HSSFWorkbook: for Microsoft Excel 97 and 2003 files of the extension xls.

XSSFWorkbook: for Microsoft Excel 2007 files with the extension xlsx.

The following code creates an array with 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 Excel {
 public static void main(String[] args) {
 
 //1. Create an empty document
 XSSFWorkbook wb = new XSSFWorkbook(); 
 //2. Create an empty Spreadsheet
 Sheet = wb.createSheet("new sheet"); 
 //3. Create a row and put something in it
 Row row = sheet.createRow((short)0); 
 //4. Create a New Cell
 Cell cell = row.createCell(0); 
 //5. Set the value 
 cell.setCellValue(1.2); 
 
 //Add more cells with different types
 /*int*/row.createCell(1).setCellValue(3); 
 /*char*/row.createCell(2).setCellValue('c'); 
 /*String*/row.createCell(3).setCellValue("string"); 
 /*boolean*/row.createCell(4).setCellValue(false); 

 FileOutputStream fileOut; 
 try {
 fileOut = new FileOutputStream("nouveauFichier.xlsx"); 
 wb.write(fileOut); 
 fileOut.close(); 
 } catch (FileNotFoundException e) {
 e.printStackTrace(); 
 } catch (IOException e) {
 e.printStackTrace(); 
 }
 }
}

We want to insert the current date and time, the Date format is created as follows:

//insert in cell F1
cell = row.createCell((short) 6); 
cell.setCellValue(new Date()); 
XSSFCellStyle cellStyle = wb.createCellStyle(); 
XSSFDataFormat xssfDataFormat = wb.createDataFormat(); 
//create a Date and Time Format
cellStyle.setDataFormat(xssfDataFormat.getFormat("dd/mm/yyyy h:mm")); 
cell.setCellStyle(cellStyle);

Text formatting

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

Example of alignment we'll apply to the cell Date:

//line height
row.setHeightInPoints(20); 
//horizontal alignment
cellStyle.setAlignment(CellStyle.ALIGN_CENTER); 
//vertical alignment
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);

Apache POI Row and Column Alignment Excel

Change font

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

/*creating a new style*/
CellStyle cs = wb.createCellStyle(); 
cs.setFont(font); 
//style cell 3(D1)
row.getCell(3).setCellStyle(cs);

Color and background

/*change background color*/
XSSFCellStyle csColor=wb.createCellStyle(); 
csColor.setFillForegroundColor(new XSSFColor(new Color(194, 154, 250))); 
csColor.setFillPattern(csCouleur.SOLID_FOREGROUND); 
//apply the style to 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);

Merging cells

In this example, we'll merge and center four cells horizontally and vertically: B2, C2, B3, C3 with the addMergedRegion which takes as a parameter the range of cells to be merged.

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 Fusion{

 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("cell merge test"); 

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

Formula Usage

Excel is mainly used in the calculation and use of sometimes complex formulas in the result cells. Apache poi provides very efficient ways to add and test cells with their formulas.

The following code deals with a simple calculation of the average of the 4 semesters. The formula will be (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 Excel_formule {

 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("Quarter1"); 
 row.createCell(1).setCellValue("Quarter2"); 
 row.createCell(2).setCellValue("Quarter3"); 
 row.createCell(3).setCellValue("Quarter4"); 
 row.createCell(4).setCellValue("Average"); 
 
 Row row1 = sheet.createRow((short) 1); 
 row1.createCell(0).setCellValue(2); 
 row1.createCell(1).setCellValue(5); 
 row1.createCell(2).setCellValue(1); 
 row1.createCell(3).setCellValue(7); 
 row1.createCell(4).setCellFormula("(A2+B2+C2+D2)/4"); 

 try {
 FileOutputStream out = new FileOutputStream(new File("formuletest.xlsx")); 
 wb.write(out); 
 out.close(); 
 System.out.println("The excel file has been created successfully"); 
 
 } catch (FileNotFoundException e) {
 e.printStackTrace(); 
 } catch (IOException e) {
 e.printStackTrace(); 
 }
 }
}

Evaluate and browse cells

To display the value of a cell, you need to know its type. Apache poi provides the class FormulaEvaluator.evaluateFormulaCell that checks the checkbox to see if there is a formula. If so, it evaluates it 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 Excel_parcourir {

 public static void main(String[] args) throws IOException {
 FileInputStream file = new FileInputStream(new File("formuletest.xlsx")); 
 //create a workbook instance that references the xlsx file 
 XSSFWorkbook wb = new XSSFWorkbook(file); 
 XSSFSheet sheet = wb.getSheetAt(0); 
 
 FormulaEvaluator formulaEvaluator = 
 wb.getCreationHelper().createFormulaEvaluator(); 
 
 for (Row row: sheet) {//browse rows
 for (Cell cell: row) {//browse columns
 //evaluate cell type
 switch (formulaEvaluator.evaluateInCell(cell).getCellType())
 {
 box Cell.CELL_TYPE_NUMERIC:
 System.out.print(cell.getNumericCellValue() + "\t\t"); 
 estate; 
 box Cell.CELL_TYPE_STRING:
 System.out.print(cell.getStringCellValue() + "\t"); 
 estate; 
 }
 }
 System.out.println(); 
 }  
 }
}

Output

Quarter1 Quarter2 Quarter3 Quarter4 Average 
2.0 5.0 1.0 7.0 3.75

References

http:// poi.apache.org/spreadsheet/quick-guide.html
http://en.wikipedia.org/wiki/Apache_POI