Read and Write Excel with Java using PoI

In this tutorial we will learn how to Create and / or read Excel file with Java.  To perform this action we will use POI project from apache.

Download POI jar from apache website.

package com.howto;

/*
 * Here we will learn how to write to excel file
 */

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.StringTokenizer;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class WriteExcelFile {

	int rownum = 0;
	HSSFSheet firstSheet;
	Collection<File> files;
	HSSFWorkbook workbook;
	File exactFile;

	/**
	 * This is anonymous block it will be executed every time new object of the
	 * class created This is opposite to static block which will be executed
	 * only once while loaded.
	 * */

	{
		workbook = new HSSFWorkbook();
		firstSheet = workbook.createSheet("FIRST SHEET");
		Row headerRow = firstSheet.createRow(rownum);
		headerRow.setHeightInPoints(40);
		Cell headerCell;

	}

	public static void main(String args[]) {
		WriteExcelFile class1 = new WriteExcelFile();
		class1.readfile();
	}

	void readfile() {
		try {
			FileInputStream fstream = new FileInputStream("D:\testing.csv");
			DataInputStream in = new DataInputStream(fstream);
			BufferedReader br = new BufferedReader(new InputStreamReader(in));

			String strLine;
			int i = 1;
			int seqno = 1;
			while ((strLine = br.readLine()) != null) {
				StringTokenizer st1 = new StringTokenizer(strLine, ",");

				List<String> l1 = new ArrayList<String>();
				while (st1.hasMoreTokens()) {
					l1.add(st1.nextToken());
				}

				try {
					boolean retu = writenameinsheet(l1);
				} catch (Exception e) {
					e.printStackTrace();
				}

				seqno++;
				i = 1;
			}
			FileOutputStream fos = null;
			try {
				fos = new FileOutputStream(new File("ExcelSheet.xls"));
				workbook.write(fos);
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				if (fos != null) {
					try {
						fos.flush();
						fos.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}
			in.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	boolean writenameinsheet(List<String> l1) throws Exception {

		try {
			Row row = firstSheet.createRow(rownum);

			for (int j = 0; j < l1.size(); j++) {
				Cell cell = row.createCell(j);
				cell.setCellValue(l1.get(j));
			}
			rownum++;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
		}
		return true;
	}
}

CSV File which we are trying to read in this application will have data like below and will reside in C drive.

testing.csv

Employee Number,Employee Name,DOB
11111,Nicole,17-Jun-84
22222,Aehmad,02-Jun-84
33333,Simon,11-Apr-85
44444,Neha,05-Jul-85
55555,Rajesh,28-Oct-85
66666,Alekh,12-Jan-86

 

 

21 Comments

  2. Hi! Could you provide a similar program to read from an excel file using certain concepts viz. Collection, Concurrecy,   Functional  / Lamda Exp and Streams ?

  3. Hi,
    Could you please provide the code that compare excel sheet and database table data and extract data from oracle database and display in Excel File .

  6. how to develop a web application which will read an excel sheet and UI will be created as per excel sheet column name… plz guide me im new to java..

  7. I have some question? For example I need a one excel sheet in that excel sheet having 10 testcases ok .. My question is in that testcase how i can pick any one testcase starting number to ending number and execute that testcase only… pls help me any one…. this is my interview question(using data driven testing in Hybrid frame work)

  8. HI How to export data from a table to excel sheet in jsp. Iam using spring mvc please give the process
    Thanks

  10. Try using Aspose.Cells for Java Library for reading and writing excel file in java . It also provides many other features also which you can use in your java application. So ty it.

  11. Hi,
    I am new to java, i want to read the “ExcelSheet.xls” from desktop and create “testing.csv file @ the location of java file and the values should be saved in my entity cass(db).Could u please send me the entire java code for this .It will be really helpfull.

    Thanks

  14. Hi

    I want to use Apache POI (Excel API)
    Apache FOP (PDF API) in eclipse rcp to write xl and pdf files. i want a sample application using eclipse rcp.
    thanks.

  15. hi,
    i want to read a particular column in a row in excel sheet and add some missing values to same excel sheet .How can i do it?

  16. Please can any one send me e-books on how to read and write to file in excel format using provided standard api in java.

  17. I am very much a beginner at OOP, so please forgive any “bad” questions.
    I have worked my way through most of the Java in 21 Days course book, and have some concepts of the programming (I am a mainframe programmer from WAY back, and have done some GUI, but not OOP).
    I am trying to load and read an Excel file into my Java code (object, class?), using the example provided on this site. I seem to be having trouble getting my code to “point” to where I have downloaded the POI…..

    I’m using NetBeans 7.2.1
    My NetBeans is stored in C:ProgramFilesNetBeans 7.2.1

    I downloaded (I think) POI to C:ProgramFilesPOI-3.8 (I never saw a wizard to talk me through the download, but I have a bunch of files in subfolders docs, lib and ooxml-lib… plus some examples

    I’ve also downloaded Apache Ant – not sure if I need it – but it is in my Downloads folder C:Users…me…Downloadsapache-ant-1.8.4-bin.zip.part

    My course “stuff” is in C:Users..me..DocumentsJava21

    Everything in the course works fine, including the imports of java, java.io, javax.swing

    So on my own, I’m trying to do an Excel file reader
    I took these imports from a website tutorial on this website….

    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.*;
    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;

    I get a package org.apache…. does not exist when I add these lines to my code
    When I try to run it, I get Uncompilable Source Code – package does not exist

    Methinks I am not telling my code where to look for the download path to POI…but at that point I’m stuck….I don’t know the NetBeans package well enough to know where to set these things up….

    Am I on the right path? Help, please….

    • Please let us know what problem you are facing while running this application.
      Hope you have downloaded the POI jar file from Apache site.
      This Application will read the “testing.csv” from ‘D’ drive and create the “ExcelSheet.xls” file @ the location of java file.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.