Scroll to top

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

 

 

Author avatar
JBT
https://javabeginnerstutorial.com/

21 comments

  1. sudhir

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

  2. mahesh

    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)

  3. Arnav

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

  4. subham

    how to get the path .csv

  5. Sohini Dhar

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

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

  7. Lakshman A

    Hi,

    How to read embedded object (Image from the cell) ?

Post a comment

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.