Read, Write Excel With Java - POI

Read And Write Excel With Java - POI Example explains step by step details of reading and writing excel with Apache POI.

Consider an application where you need to manipulate Microsoft office formats, in that case you can use Apache POI

Apache POI project consists of APIs that is used for manipulating various Microsoft office formats, this includes pure java libraries for writing and reading files in Microsoft Office formats, such as Excel, Word and PowerPoint presentations.

Apache POI uses event based API's, this will reduce the memory foot print drastically.

You can see the below example, which is demonstrating Reading & Writing Excel With Apache POI

pom.xml

You need to have following dependencies

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.1.2</version>
</dependency>
Required Libraries

You need to download

  1. Apache POI

Following jar must be in classpath

  1. poi-4.1.2,jar

Write Excel With Java - POI

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;

public class PoiWriteExcelFile {

    public static void main(String[] args) {
	try {
	    FileOutputStream fileOut = new FileOutputStream("test.xls");
	    HSSFWorkbook workbook = new HSSFWorkbook();
	    HSSFSheet worksheet = workbook.createSheet("Worksheet");

	    HSSFRow row = worksheet.createRow((short) 0);

	    HSSFCell cellA1 = row.createCell(0);
	    cellA1.setCellValue("Hello");
	    HSSFCellStyle styleOfCell = workbook.createCellStyle();
	    styleOfCell.setFillForegroundColor(HSSFColor.HSSFColorPredefined.AQUA.getIndex());
	    styleOfCell.setFillPattern(FillPatternType.BIG_SPOTS);
	    cellA1.setCellStyle(styleOfCell);

	    HSSFCell cellB1 = row.createCell(1);
	    cellB1.setCellValue("World");
	    styleOfCell = workbook.createCellStyle();
	    styleOfCell.setFillForegroundColor(HSSFColor.HSSFColorPredefined.AQUA.getIndex());
	    styleOfCell.setFillPattern(FillPatternType.BIG_SPOTS);
	    cellB1.setCellStyle(styleOfCell);

	    HSSFCell cellC1 = row.createCell(2);
	    cellC1.setCellValue("Happy");
	    styleOfCell = workbook.createCellStyle();
	    styleOfCell.setFillForegroundColor(HSSFColor.HSSFColorPredefined.AQUA.getIndex());
	    styleOfCell.setFillPattern(FillPatternType.BIG_SPOTS);
	    cellC1.setCellStyle(styleOfCell);

	    HSSFCell cellD1 = row.createCell(3);
	    cellD1.setCellValue(new Date());
	    styleOfCell = workbook.createCellStyle();
	    styleOfCell.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
	    styleOfCell.setFillForegroundColor(HSSFColor.HSSFColorPredefined.AQUA.getIndex());
	    styleOfCell.setFillPattern(FillPatternType.BIG_SPOTS);
	    cellD1.setCellStyle(styleOfCell);

	    row = worksheet.createRow(1);
	    row.createCell(0).setCellValue(Calendar.getInstance().getTime().toString());
	    row.createCell(1).setCellValue("a string");
	    row.createCell(2).setCellValue("true");
	    row.createCell(3).setCellType(CellType.ERROR);

	    workbook.write(fileOut);
	    workbook.close();
	    fileOut.flush();
	    fileOut.close();
	} catch (FileNotFoundException e) {
	    e.printStackTrace();
	} catch (IOException e) {
	    e.printStackTrace();
	}

    }

}
Output
Read And Write Excel With Java - POI

Read Excel With Java - POI

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class PoiReadExcelFile {
    public static void main(String[] args) {
	try {
	    FileInputStream iStream = new FileInputStream("test.xls");
	    HSSFWorkbook workbook = new HSSFWorkbook(iStream);
	    HSSFSheet worksheet = workbook.getSheet("Worksheet");
	    // get first row
	    HSSFRow row = worksheet.getRow(0);
	    HSSFCell cellA1 = row.getCell(0);
	    System.out.println("A1 " + cellA1.getStringCellValue());
	    HSSFCell cellB1 = row.getCell(1);
	    System.out.println("B1 " + cellB1.getStringCellValue());
	    HSSFCell cellC1 = row.getCell(2);
	    System.out.println("C1 " + cellC1.getStringCellValue());
	    HSSFCell cellD1 = row.getCell(3);
	    System.out.println("D1 " + cellD1.getDateCellValue());
	    // get next row
	    row = worksheet.getRow(1);
	    HSSFCell cellA2 = row.getCell(0);
	    System.out.println("A2 " + cellA2.getStringCellValue());
	    HSSFCell cellB2 = row.getCell(1);
	    System.out.println("B2 " + cellB2.getStringCellValue());
	    HSSFCell cellC2 = row.getCell(2);
	    System.out.println("C2 " + cellC2.getStringCellValue());
	    HSSFCell cellC3 = row.getCell(3);
	    System.out.println("D2 " + cellC3.getErrorCellValue());
	    workbook.close();
	} catch (FileNotFoundException e) {
	    e.printStackTrace();
	} catch (IOException e) {
	    e.printStackTrace();
	}
    }
}
Output
A1 Hello
B1 World
C1 Happy
D1 Sat Dec 31 08:55:00 IST 2011
A2 Sat Dec 31 08:55:00 IST 2011
B2 a string
C2 true
D2 15

 











2 Responses to "Read, Write Excel With Java - POI"
  1. sheraz khan 2011-12-10 08:18:55.0
  1. Khoa 2011-12-11 08:18:55.0

Your email address will not be published. Required fields are marked *