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

 

 

By | 2012-07-03T15:19:10+00:00 July 3rd, 2012|Code Base|19 Comments

About the Author:

19 Comments

  1. Scott September 6, 2012 at 5:00 pm - Reply

    All I got was gibberish when I ran this. Any ideas on why that would be?

    • admin September 6, 2012 at 5:40 pm - Reply

      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.

  2. Kevin Jaques November 14, 2012 at 7:22 pm - Reply

    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….

    • admin November 15, 2012 at 6:03 am - Reply

      Hi Kevin,

      Reply has been sent on your mail. Please check.

    • Amit February 23, 2013 at 4:24 pm - Reply

      Hi Kevin Jaques/ Admin, even I am getting the same error, can you please share the solution with me as well..

      Thanks

  3. Olatuneday Passionate January 8, 2013 at 7:57 am - Reply

    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.

  4. Divya February 13, 2013 at 12:52 am - Reply

    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?

  5. Bubbler April 29, 2013 at 6:59 am - Reply

    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.

  6. abcd April 30, 2013 at 7:44 am - Reply

    Where i place the downloaded poi in my project..

  7. akama September 15, 2013 at 1:13 pm - Reply

    Hi,
    Am experiencing the problems and errors as Kevin could some one help with a solution on how to crack the error

  8. Dhanya October 5, 2013 at 1:48 pm - Reply

    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

  9. Dawne October 24, 2013 at 7:26 am - Reply

    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.

  10. sumit December 16, 2013 at 3:16 pm - Reply

    Hi,

    Could you please provide the code that can extract data from oracle database and display in Excel File .

  11. sudhir May 24, 2014 at 11:11 am - Reply

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

  12. mahesh December 3, 2015 at 5:58 am - Reply

    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)

  13. Arnav January 4, 2016 at 5:27 pm - Reply

    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..

  14. subham January 19, 2016 at 9:02 am - Reply

    how to get the path .csv

  15. raghav March 1, 2016 at 11:18 am - Reply

    thank you

  16. Sohini Dhar May 6, 2016 at 4:26 am - Reply

    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 .

Leave A Comment