Read / Write Excel file in Java using Apache POI

Apache POI is a powerful Java library to work with different Microsoft Office file formats such as Excel, Power point, Visio, MS Word etc. The name POI was originally an acronym for Poor Obfuscation Implementation, referring humorously to the fact that the file formats seemed to be deliberately obfuscated, but poorly, since they were successfully reverse-engineered.

In this tutorial we will use Apache POI library to perform different functions on Microsoft Excel spreadsheet.

Let’s get started.

Tools & Technologies:

  1. Java JDK 1.5 or above
  2. Apache POI library v3.8 or above (download)
  3. Eclipse 3.2 above (optional)

1. Add Apache POI dependency

Make sure to include apache poi jar file to your project. If your project uses Maven as dependency management, add following in your Pom.xml file.


If you are not using Maven then you can directly add required JAR files in your classpath.

  1. Download poi-2.5.1.jar(or in this case 3.8) jar file.
  2. Include this file in your projects class path.
  3. Create new java project in eclipse with auto generated main function.

2. Read Excel File

To read an excel file, Apache POI provides certain easy-to-use APIs. In below sample code we use different classes from POI library to read content of cell from excel file. This is for quick reference.

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
FileInputStream file = new FileInputStream(new File("C:\\test.xls"));
//Get the workbook instance for XLS file 
HSSFWorkbook workbook = new HSSFWorkbook(file);

//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);

//Get iterator to all the rows in current sheet
Iterator<Row> rowIterator = sheet.iterator();

//Get iterator to all cells of current row
Iterator<Cell> cellIterator = row.cellIterator();

Notice how each class in POI library starts with HSSF prefix! e.g. HSSFWorkbook, HSSFSheet etc. HSSF stands for Horrible SpreadSheet Format! I’m not kidding.. It really is.

Similar to HSSF, POI has different prefix for other file formats too:

  1. HSSF (Horrible SpreadSheet Format) – reads and writes Microsoft Excel (XLS) format files.
  2. XSSF (XML SpreadSheet Format) – reads and writes Office Open XML (XLSX) format files.
  3. HPSF (Horrible Property Set Format) – reads “Document Summary” information from Microsoft Office files.
  4. HWPF (Horrible Word Processor Format) – aims to read and write Microsoft Word 97 (DOC) format files.
  5. HSLF (Horrible Slide Layout Format) – a pure Java implementation for Microsoft PowerPoint files.
  6. HDGF (Horrible DiaGram Format) – an initial pure Java implementation for Microsoft Visio binary files.
  7. HPBF (Horrible PuBlisher Format) – a pure Java implementation for Microsoft Publisher files.
  8. HSMF (Horrible Stupid Mail Format) – a pure Java implementation for Microsoft Outlook MSG files
  9. DDF (Dreadful Drawing Format) – a package for decoding the Microsoft Office Drawing format.

Working with .xlsx files

The classes we used in above code snippet, HSSFWorkbook and HSSFSheet works for .xls format. In order to work with newer xls format viz .xlsx, you need to see newer POI classes like:

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
FileInputStream file = new FileInputStream(new File("C:\\test.xlsx"));
//Get the workbook instance for XLS file 
XSSFWorkbook workbook = new XSSFWorkbook (file);

//Get first sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);

//Get iterator to all the rows in current sheet
Iterator<Row> rowIterator = sheet.iterator();

//Get iterator to all cells of current row
Iterator<Cell> cellIterator = row.cellIterator();

Use XSSFWorkbook and XSSFSheet class in all of the below examples in order to make them work with .xlsx files.

Consider a sample excel file:

java read excel file

We will read above xls file using Apache POI and prints the data.

		try {
			FileInputStream file = new FileInputStream(new File("C:\\test.xls"));
			//Get the workbook instance for XLS file 
			HSSFWorkbook workbook = new HSSFWorkbook(file);

			//Get first sheet from the workbook
			HSSFSheet sheet = workbook.getSheetAt(0);
			//Iterate through each rows from first sheet
			Iterator<Row> rowIterator = sheet.iterator();
			while(rowIterator.hasNext()) {
				Row row =;
				//For each row, iterate through each columns
				Iterator<Cell> cellIterator = row.cellIterator();
				while(cellIterator.hasNext()) {
					Cell cell =;
					switch(cell.getCellType()) {
						case Cell.CELL_TYPE_BOOLEAN:
							System.out.print(cell.getBooleanCellValue() + "\t\t");
						case Cell.CELL_TYPE_NUMERIC:
							System.out.print(cell.getNumericCellValue() + "\t\t");
						case Cell.CELL_TYPE_STRING:
							System.out.print(cell.getStringCellValue() + "\t\t");
			FileOutputStream out = 
				new FileOutputStream(new File("C:\\test.xls"));
		} catch (FileNotFoundException e) {
		} catch (IOException e) {

The above code is self explanatory. It read the sheet from workbook and iterate through each row and cell to print its values. Just note how we use different methods like getBooleanCellValue, getNumericCellValue etc to read cell value. Before reading a cell content, we need to first determine its type using method cell.getCellType() and then call appropriate method to read content.


Emp Id		Name		Salary		
1.0		John		2000000.0		
2.0		Dean		420000.0		
3.0		Sam		280000.0		
4.0		Cass		6000000.0	

3. Create New Excel File

Let us create a new excel file and write data in it. Following is the API which we will use for this purpose.

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Sample sheet");
//Create a new row in current sheet
Row row = sheet.createRow(0);
//Create a new cell in current row
Cell cell = row.createCell(0);
//Set value to new value

Below is the complete code that writes a new excel with dummy data:

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("Sample sheet");
		Map<String, Object[]> data = new HashMap<String, Object[]>();
		data.put("1", new Object[] {"Emp No.", "Name", "Salary"});
		data.put("2", new Object[] {1d, "John", 1500000d});
		data.put("3", new Object[] {2d, "Sam", 800000d});
		data.put("4", new Object[] {3d, "Dean", 700000d});
		Set<String> keyset = data.keySet();
		int rownum = 0;
		for (String key : keyset) {
			Row row = sheet.createRow(rownum++);
			Object [] objArr = data.get(key);
			int cellnum = 0;
			for (Object obj : objArr) {
				Cell cell = row.createCell(cellnum++);
				if(obj instanceof Date) 
				else if(obj instanceof Boolean)
				else if(obj instanceof String)
				else if(obj instanceof Double)
		try {
			FileOutputStream out = 
					new FileOutputStream(new File("C:\\new.xls"));
			System.out.println("Excel written successfully..");
		} catch (FileNotFoundException e) {
		} catch (IOException e) {

Output: new.xls
java-write-excel file

4. Update Existing Excel File

Updating an existing excel file is straight forward. Open the excel using different API that we discussed above and set the cell’s value. One thing we need to note here is that we can update the excel file only when we close it first.


Following Java code read the above excel file and doubles the salary of each employee:

		try {
			FileInputStream file = new FileInputStream(new File("C:\\update.xls"));

			HSSFWorkbook workbook = new HSSFWorkbook(file);
			HSSFSheet sheet = workbook.getSheetAt(0);
			Cell cell = null;

			//Update the value of cell
			cell = sheet.getRow(1).getCell(2);
			cell.setCellValue(cell.getNumericCellValue() * 2);
			cell = sheet.getRow(2).getCell(2);
			cell.setCellValue(cell.getNumericCellValue() * 2);
			cell = sheet.getRow(3).getCell(2);
			cell.setCellValue(cell.getNumericCellValue() * 2);
			FileOutputStream outFile =new FileOutputStream(new File("C:\\update.xls"));
		} catch (FileNotFoundException e) {
		} catch (IOException e) {

Steps to update excel file will be:

  1. Open excel file in input mode (inputstream)
  2. Use POI API and read the excel content
  3. Update cell’s value using different setCellValue methods.
  4. Close the excel input file (inputstream)
  5. Open same excel file in output mode (outputstream)
  6. Write content of updated workbook in output file
  7. Close output excel file

Output: update.xls

5. Adding Formulas

Apache POI provides API to add excel formulas to cell programmatically. Following method that comes handy for this:


For example:


Note: Formula string should not start with equal sign (=)
Thus, following is incorrect way of adding formula:

cell.setCellFormula("=A2*B2*C5") //Ops! Won't work

The above code will throw: 
The specified formula '=A2*B2*C5' starts with an equals sign which is not allowed.

Following Java code creates a new excel sheet which calculates Simple Interest. It defines Principal amount, Rate of Interest and Tenure. We add an excel formula to calculate interest.

	HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("Calculate Simple Interest");

		Row header = sheet.createRow(0);
		header.createCell(0).setCellValue("Pricipal Amount (P)");
		header.createCell(1).setCellValue("Rate of Interest (r)");
		header.createCell(2).setCellValue("Tenure (t)");
		header.createCell(3).setCellValue("Interest (P r t)");
		Row dataRow = sheet.createRow(1);
		try {
			FileOutputStream out = 
					new FileOutputStream(new File("C:\\formula.xls"));
			System.out.println("Excel written successfully..");
		} catch (FileNotFoundException e) {
		} catch (IOException e) {

Output: formula.xls

Triggering Existing Excel Formulas

In certain cases your excel file might have formula defined and you may want to trigger those formulas since you updated it using POI. Following code snippet will do the trick.

FileInputStream fis = new FileInputStream("/somepath/test.xls");
Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("C:\\test.xls")
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
    Sheet sheet = wb.getSheetAt(sheetNum);
    for(Row r : sheet) {
        for(Cell c : r) {
            if(c.getCellType() == Cell.CELL_TYPE_FORMULA) {

We use FormulaEvaluator class to evaluate formula defined in each of the cell.

6. Adding Styles to Cell

Adding style to a cell is also piece of cake. Check following example which creates two new cell one with bold font and another with italic and add text to it.

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("Style example");

		HSSFFont font = workbook.createFont();
		HSSFCellStyle style = workbook.createCellStyle();
		Row row = sheet.createRow(0);
		Cell cell = row.createCell(0);
		cell.setCellValue("This is bold");

		font = workbook.createFont();
		style = workbook.createCellStyle();
		row = sheet.createRow(1);
		cell = row.createCell(0);
		cell.setCellValue("This is italic");
		try {
			FileOutputStream out = new FileOutputStream(new File("C:\\style.xls"));
			System.out.println("Excel written successfully..");
		} catch (FileNotFoundException e) {
		} catch (IOException e) {

Output: style.xls

6.1 Add Background Color to the cell

Changing background color of the cell is a bit tricky. Ideally we assume setting background color will have some API like setFillBackgroundColor, but surprisingly to set background color of a cell, we have to set the foreground color :D. See below API.


So use setFillForegroundColor method to set the background color of given cell.

I hope this article is useful.

Get our Articles via Email. Enter your email address.

You may also like...


  1. Subrata Maiti says:

    How I Can Find the cell data source name for a particular dropdown box; like for a dropbox there a days of a week but the data source is in another worksheet, how to find the name of that data source name by JAVA ???
    plzz replyy !!

    • Shivani says:

      i want code for converting excel sheet into .csv file in eclipse i am using apache POI.

  2. arpit says:

    Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/juli/logging/LogFactory
    at org.apache.catalina.util.LifecycleBase.(
    Caused by: java.lang.ClassNotFoundException: org.apache.juli.logging.LogFactory
    at$ Source)
    at$ Source)
    at Method)

    This is the error i am getting

    • thatskriptkid says:

      You should add external JAR – org.apache.juli.logging.LogFactory.

  3. Vinesh says:

    Good explanation.. helped a lot.

  4. Jess A says:

    Thanks for this – very helpful.

    I have a questions. In your code to trigger existing Excel formula calculation, you just walk every formula and evaluate. Will this work? Order should matter, correct? If a formula on sheet 1 needed value of a formula on sheet 2, sheet 2 formula should be evaluated first?

    Is there a calculate workbook method? Or do you need to do as you said above and save/open it in order to get all formula/cells to update?


  5. dev says:

    how I can add formula to every row if I have more than 500 rows?

  6. Divya says:

    Hi Virat,
    I just want to know how to verify the excel data and pick data from excel.

    My requirement is, i want to verify the all rows in column1, if my text is available in some nth row, i have to pick the nth row column2.

    Please provide me some information for this requirement.

  7. Reetesh says:

    Thanks for explanation, it solved one of my problem.

  8. betty says:

    How to read content from xlsm? can we use the same jar and if yes the what type of format i need to use

    • Raghavendra Samant says:

      you can use POI 3.11 versions XSSFWorkbook and XSSFSheet classes for xlsm

  9. Divya says:

    Hi Virat,
    While executing my excel reading code i am getting error as ‘ java.lang.NoSuchMethodError: org.apache.poi.poifs.filesystem.POIFSFileSystem.hasPOIFSHeader(Ljava/io/InputStream;)Z’. But in my jar ‘poi-ooxml-3.9-20121203.jar’ above ‘org.apache.poi.poifs.filesystem.POIFSFileSystem.hasPOIFSHeader(‘ is available. Not able to get the solution for this. It worked before. What the change i have done is once i removed jars from lib and placed again thats it.

    Please give me the solution for this issue.

  10. Justin says:

    This tutorial is really helpful. I have a doubt though
    I want to check a excel cell value falls within a range (say, between greater than 1 and less than 32)
    How can I do that. I have seen


    But I’m not sure how to implement it. Any suggestion ?

  11. mohankumar says:

    thank u so much…

  12. Kumar says:

    Hi Patel,

    The above post is very useful and it helped me a lot for my project but I am stuck at a point – where I have to compare any two columns by getting the column’s header name.


  13. Jags says:
     Hi Patel,
    I’m getting the column name and corresponding values dynamically. I don’t want to hard code both column name and values.
    Below method will get the list of users which will have column names and values.
    List mystring = getIndiviualRecordsList(getLdapDirContext());
    I’m wondering how to use it here. Please share your thoughts.
    //This data needs to be written (Object[])
    Map data = new TreeMap();
    data.put(“1″, new Object[] {“ID”, “NAME”, “LASTNAME”});
    data.put(“2″, new Object[] {1, “Amit”, “Kumar”});
    data.put(“3″, new Object[] {2, “Lokesh”, “Gupta”});
    data.put(“4″, new Object[] {3, “John”, “Adwards”});
    data.put(“5″, new Object[] {4, “Brian”, “Lenny”}); 
  14. chandra says:

    how to copy the content to an text file instead of an excel file

    • inl1ner says:

      Hi chandra,
      maybe this helps:

      String xlCont = “some xls content”;
      String writePath = “C:\\ListNew.txt”;
      File myWf = new File(writePath);
      Writer filWr = new FileWriter(myWf);
      BufferedWriter bufWrite = new BufferedWriter(filWr);


  15. Sagar says:

    Problem statement:

    I want to fetch data from two different websites , and write this data into workbook .
    Sheet 1 and sheet 2
    am looking for solution to perform excel comparison, looking for ur expert guidance .

    Sheet 1 is as followed
    Ticker— Out standing Shares– Value Of Holdings–Increased Positions–Decreased–Positions—New Positions—-Sold Out Positions
    XMN 608,911,000—–3,741—–362 —-312—-76—–70
    DWD 608,911,000—–3,741—–362——312—-76—–70

    Sheet 2 is as followed
    Ticker— Out standing Shares– Value Of Holdings–Increased Positions–Decreased–Positions—New Positions—-Sold Out Positions
    XMN 608,911,000—–3,741—–362 —-312—-76—–70
    DWD 608,911,000—–3,741—–362——312—-76—–70

    Thanks & regards

  16. Gulamjelani Qureshi says:

    what about row type in iterator

  17. amitabh says:

    Very good explanation and way to do it. Thank you.

  18. Sachin says:

    Referred the above java code and converted to Scala,Working fine!!
    Thanks for the clean code.

  19. Sagar says:

    How do I populate data into another tab when the user clicks on the link in the first tab.

  20. Candis says:

    Can we write a JSONObject to the excel file….??

  21. Kirankumar Oggu says:

    Your page has helped me a lot to understand how to read and write files in java using an excel. Could you please let me know how can i compare two columns in an excel file while reading the data.

  22. Jitendra Arethiya says:

    I wanted to set password to existing workbook. I am able to do that with workbook.writeProtectWorkbook(“12”, “1”);, but the problem is user is able to view the excel as read only mode, which i dont want to allow. When you try to open the excel, it shows two options, read only mode or enter password. I dont want the first option.

    User should able to open the document only if S/he knows password. can you please help me?

  23. Anjana says:


    When there are more than 10 values, the header values do not remain at the top. The 11th value takes the first cell position and the header value goes under it. Anybody has faced this issue or have a solution to this?


  24. Leo says:

    Can we select particular row from excel sheet amid 100 of rows using Java and POI?
    For say Parametrization, I want to select Row numbered 1, 5 and 11 from given list of 50 rows. How can we achieve it using java coding?

    Thank you for your answers.

  25. prasanthi says:


    I tried updating existing excel sheet. when i tried updating one cell and print that cell value its giving me updated value. But if i open the excel file from windows explorer and see the file doesn’t contain any updated values. I am confused why it is happening. Please assist

  26. Sundar says:

    Can any one say me how to set the page layout view using XSSF poi ?thanks in advance

  27. raj says:

    suppose is have 2 columns in excel : Firstname, lastname. I want to read the excel file using poi and then add one more column Age and then write to a file. How would i do that?

  28. Srikanth says:

    Nice info,

    My doubt is if i want to download the written excel immediately without storing it in any output path

  29. Aj says:

    Thanks for this article! It was useful.

  30. shweta says:

    the excel sheet created is not opening in few gives unsupported file toast message

Leave a Reply

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