Apache POI – Read and Write Excel File in Java

Learn to read excel, write excel, evaluate formula cells and apply custom formatting to generated excel file using Apache POI library with examples.

If you are building a software for HR or finance domain, there is usually requirement for generating excel reports which are usually across management levels. Apart from reports, you can expect input data for application coming in form of excel sheets and application is expected to support it.

Apache POI is well trusted library among many other open source libraries to handle such usecases involving excel files. Using POI, you can read and write MS Excel files using Java.

Please note that, in addition, you can read and write MS Word and MS PowerPoint files also using POI library.

In this Apache POI tutorial, We will discuss some common excel operations required to do in real life applications.

Table of Contents:

Apache POI Dependencies
POI Classes
Write an excel file
Read an excel file
Using formulas in excel sheet
Formatting the cells
Sourcecode download

Apache POI – Dependencies

If you are working on a maven project, you can include the POI dependency in pom.xml file using this:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

If you are not using maven, then you can download maven jar files from POI download page. Include following jar files to run the sample code given in this tutorial.

dom4j-1.6.1.jar
poi-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-ooxml-schemas-3.9-20121203.jar
xmlbeans-2.3.0.jar

Apache POI – Classes

HSSF, XSSF and XSSF classes
Apache POI main classes usually start with either HSSF, XSSF or SXSSF.
- HSSF – is the POI Project’s pure Java implementation of the Excel ’97(-2007) file format. e.g. HSSFWorkbook, HSSFSheet.
- XSSF – is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format. e.g. XSSFWorkbook, XSSFSheet.
- SXSSF (since 3.8-beta3) – is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited. e.g. SXSSFWorkbook, SXSSFSheet. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document.
Row and Cell
Apart from above classes, Row and Cell are used to interact with a particular row and a particular cell in excel sheet.
Style Classes
A wide range of classes like CellStyle, BuiltinFormats, ComparisonOperator, ConditionalFormattingRule, FontFormatting, IndexedColors, PatternFormatting, SheetConditionalFormatting etc. are used when you have to add formatting in a sheet, mostly based on some rules.
FormulaEvaluator
Another useful class FormulaEvaluator is used to evaluate the formula cells in excel sheet.

Apache POI – Write an excel file

I am taking this example first so that we can reuse the excel sheet created by this code to read back in next example.

Writing excel using POI is very simple and involve following steps:

Create a workbook
Create a sheet in workbook
Create a row in sheet
Add cells in sheet
Repeat step 3 and 4 to write more data

It seems very simple, right? Lets have a look at the code doing these steps.

Java program to write excel file using apache POI library.

package com.howtodoinjava.demo.poi;
//import statements
public class WriteExcelDemo 
{
	public static void main(String[] args) 
	{
		//Blank workbook
		XSSFWorkbook workbook = new XSSFWorkbook(); 
		
		//Create a blank sheet
		XSSFSheet sheet = workbook.createSheet("Employee Data");
		 
		//This data needs to be written (Object[])
		Map<String, Object[]> data = new TreeMap<String, Object[]>();
		data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});
		data.put("2", new Object[] {1, "Amit", "Shukla"});
		data.put("3", new Object[] {2, "Lokesh", "Gupta"});
		data.put("4", new Object[] {3, "John", "Adwards"});
		data.put("5", new Object[] {4, "Brian", "Schultz"});
		 
		//Iterate over data and write to sheet
		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 String)
		            cell.setCellValue((String)obj);
		        else if(obj instanceof Integer)
		            cell.setCellValue((Integer)obj);
		    }
		}
		try 
		{
			//Write the workbook in file system
		    FileOutputStream out = new FileOutputStream(new File("howtodoinjava_demo.xlsx"));
		    workbook.write(out);
		    out.close();
		    System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
		} 
		catch (Exception e) 
		{
		    e.printStackTrace();
		}
	}
}

Apache POI – Read an excel file

Reading an excel file using POI is also very simple if we divide this in steps.

Create workbook instance from excel sheet
Get to the desired sheet
Increment row number
iterate over all cells in a row
repeat step 3 and 4 until all data is read

Lets see all above steps in code. I am writing the code to read the excel file created in above example. It will read all the column names and the values in it – cell by cell.

Java program to read excel file using apache POI library.

package com.howtodoinjava.demo.poi;
//import statements
public class ReadExcelDemo 
{
	public static void main(String[] args) 
	{
		try
		{
			FileInputStream file = new FileInputStream(new File("howtodoinjava_demo.xlsx"));

			//Create Workbook instance holding reference to .xlsx file
			XSSFWorkbook workbook = new XSSFWorkbook(file);

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

			//Iterate through each rows one by one
			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) 
			{
				Row row = rowIterator.next();
				//For each row, iterate through all the columns
				Iterator<Cell> cellIterator = row.cellIterator();
				
				while (cellIterator.hasNext()) 
				{
					Cell cell = cellIterator.next();
					//Check the cell type and format accordingly
					switch (cell.getCellType()) 
					{
						case Cell.CELL_TYPE_NUMERIC:
							System.out.print(cell.getNumericCellValue() + "t");
							break;
						case Cell.CELL_TYPE_STRING:
							System.out.print(cell.getStringCellValue() + "t");
							break;
					}
				}
				System.out.println("");
			}
			file.close();
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
	}
}

Output:

ID		NAME		LASTNAME
1.0		Amit		Shukla	
2.0		Lokesh		Gupta	
3.0		John		Adwards	
4.0		Brian		Schultz

Apache POI – Add and evaluate formula cells

When working on complex excel sheets, we encounter many cells which have formula to calculate their values. These are formula cells. Apache POI has excellent support for adding formula cells and evaluating already present formula cells also.

Les see one example of how to add formula cells in excel?

In this code, there are four cells in a row and fourth one in multiplication of all previous 3 rows. So the formula will be : A2*B2*C2 (in second row)

Java program to add formula in excel file using apache POI library.

public static void main(String[] args) 
{
	XSSFWorkbook workbook = new XSSFWorkbook();
	XSSFSheet sheet = workbook.createSheet("Calculate Simple Interest");
 
	Row header = sheet.createRow(0);
	header.createCell(0).setCellValue("Pricipal");
	header.createCell(1).setCellValue("RoI");
	header.createCell(2).setCellValue("T");
	header.createCell(3).setCellValue("Interest (P r t)");
	 
	Row dataRow = sheet.createRow(1);
	dataRow.createCell(0).setCellValue(14500d);
	dataRow.createCell(1).setCellValue(9.25);
	dataRow.createCell(2).setCellValue(3d);
	dataRow.createCell(3).setCellFormula("A2*B2*C2");
	 
	try {
		FileOutputStream out =  new FileOutputStream(new File("formulaDemo.xlsx"));
		workbook.write(out);
		out.close();
		System.out.println("Excel with foumula cells written successfully");
		 
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	}
}

Similarly, I you want to read a file which have formula cells in it, use following logic to evaluate formula cells.

Java program to eveluate formula in excel file using apache POI library.

public static void readSheetWithFormula()
{
	try
	{
		FileInputStream file = new FileInputStream(new File("formulaDemo.xlsx"));

		//Create Workbook instance holding reference to .xlsx file
		XSSFWorkbook workbook = new XSSFWorkbook(file);

		FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
		
		//Get first/desired sheet from the workbook
		XSSFSheet sheet = workbook.getSheetAt(0);

		//Iterate through each rows one by one
		Iterator<Row> rowIterator = sheet.iterator();
		while (rowIterator.hasNext()) 
		{
			Row row = rowIterator.next();
			//For each row, iterate through all the columns
			Iterator<Cell> cellIterator = row.cellIterator();
			
			while (cellIterator.hasNext()) 
			{
				Cell cell = cellIterator.next();
				//Check the cell type after eveluating formulae
				//If it is formula cell, it will be evaluated otherwise no change will happen
				switch (evaluator.evaluateInCell(cell).getCellType()) 
				{
					case Cell.CELL_TYPE_NUMERIC:
						System.out.print(cell.getNumericCellValue() + "tt");
						break;
					case Cell.CELL_TYPE_STRING:
						System.out.print(cell.getStringCellValue() + "tt");
						break;
					case Cell.CELL_TYPE_FORMULA:
						//Not again
						break;
				}
			}
			System.out.println("");
		}
		file.close();
	} 
	catch (Exception e) 
	{
		e.printStackTrace();
	}
}

Output:

Pricipal		RoI			T		Interest (P r t)		
14500.0			9.25		3.0		402375.0

Apache POI – Formatting the cells

So for we have seen the examples of reading / writing and excel file using apache POI. But, when we are creating a report in excel file and it becomes utmost important to add formatting on cells which fit into any per-determined criteria. This formatting can be a different coloring based on certain value range, based on expiry date limit etc.

In below examples, I am taking couple of such cell formatting examples for various purposes.

1) Cell value is in between a certain range

This piece of code will color any cell in range whose value is between a configured range. [e.g. between 50 and 70]

static void basedOnValue(Sheet sheet) 
{
	//Creating some random values
	sheet.createRow(0).createCell(0).setCellValue(84);
	sheet.createRow(1).createCell(0).setCellValue(74);
	sheet.createRow(2).createCell(0).setCellValue(50);
	sheet.createRow(3).createCell(0).setCellValue(51);
	sheet.createRow(4).createCell(0).setCellValue(49);
	sheet.createRow(5).createCell(0).setCellValue(41);

	SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

	//Condition 1: Cell Value Is   greater than  70   (Blue Fill)
	ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70");
	PatternFormatting fill1 = rule1.createPatternFormatting();
	fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
	fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

	//Condition 2: Cell Value Is  less than      50   (Green Fill)
	ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50");
	PatternFormatting fill2 = rule2.createPatternFormatting();
	fill2.setFillBackgroundColor(IndexedColors.GREEN.index);
	fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

	CellRangeAddress[] regions = {
			CellRangeAddress.valueOf("A1:A6")
	};

	sheetCF.addConditionalFormatting(regions, rule1, rule2);
}

2) Highlight duplicate values

Highlight all cells which have duplicate values in observed cells.

static void formatDuplicates(Sheet sheet) {
	sheet.createRow(0).createCell(0).setCellValue("Code");
	sheet.createRow(1).createCell(0).setCellValue(4);
	sheet.createRow(2).createCell(0).setCellValue(3);
	sheet.createRow(3).createCell(0).setCellValue(6);
	sheet.createRow(4).createCell(0).setCellValue(3);
	sheet.createRow(5).createCell(0).setCellValue(5);
	sheet.createRow(6).createCell(0).setCellValue(8);
	sheet.createRow(7).createCell(0).setCellValue(0);
	sheet.createRow(8).createCell(0).setCellValue(2);
	sheet.createRow(9).createCell(0).setCellValue(8);
	sheet.createRow(10).createCell(0).setCellValue(6);

	SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

	// Condition 1: Formula Is   =A2=A1   (White Font)
	ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");
	FontFormatting font = rule1.createFontFormatting();
	font.setFontStyle(false, true);
	font.setFontColorIndex(IndexedColors.BLUE.index);

	CellRangeAddress[] regions = {
			CellRangeAddress.valueOf("A2:A11")
	};

	sheetCF.addConditionalFormatting(regions, rule1);

	sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted.  " +
			"Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1   (Blue Font)");
}

3) Color alternate rows in different colors

A simple code to color each alternate row in a different color.

static void shadeAlt(Sheet sheet) {
	SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

	// Condition 1: Formula Is   =A2=A1   (White Font)
	ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)");
	PatternFormatting fill1 = rule1.createPatternFormatting();
	fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
	fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

	CellRangeAddress[] regions = {
			CellRangeAddress.valueOf("A1:Z100")
	};

	sheetCF.addConditionalFormatting(regions, rule1);

	sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows");
	sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is  =MOD(ROW(),2)   (Light Green Fill)");
}

4) Color amounts which are going to expire in next 30 days

A very useful code for financial projects which keep track of dead lines.

static void expiryInNext30Days(Sheet sheet) 
{
	CellStyle style = sheet.getWorkbook().createCellStyle();
	style.setDataFormat((short)BuiltinFormats.getBuiltinFormat("d-mmm"));

	sheet.createRow(0).createCell(0).setCellValue("Date");
	sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
	sheet.createRow(2).createCell(0).setCellFormula("A2+1");
	sheet.createRow(3).createCell(0).setCellFormula("A3+1");

	for(int rownum = 1; rownum <= 3; rownum++) sheet.getRow(rownum).getCell(0).setCellStyle(style);

	SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

	// Condition 1: Formula Is   =A2=A1   (White Font)
	ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
	FontFormatting font = rule1.createFontFormatting();
	font.setFontStyle(false, true);
	font.setFontColorIndex(IndexedColors.BLUE.index);

	CellRangeAddress[] regions = {
			CellRangeAddress.valueOf("A2:A4")
	};

	sheetCF.addConditionalFormatting(regions, rule1);

	sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted");
}

I am ending this apache poi tutorial here for keeping the post in limit.

Sourcecode download

Click on below given link to download the source code of above examples.

Download Sourcecode

In this tutorial, we learned to read excel, write excel, set and evaluate formula cells and also to format the cells with color codings using APache POI library.

Happy Learning !!

References:

Apache POI How-to Guide
Apache POI API docs

Was this post helpful?

Let us know if you liked the post. That’s the only way we can improve.

Twitter Facebook LinkedIn Reddit Pocket

About Lokesh Gupta

A family guy with fun loving nature. Love computers, programming and solving everyday problems. Find me on Facebook and Twitter.

Feedback, Discussion and Comments

Yellaiah
September 29, 2016

Hi,
I have excel sheet with columns like this( Asset Name , Asset Type, Asset SubType , Asset Logo Path)… Here I want to read blob data based on Asset Logo Path column(/home/mbytes/yellaiah/asset imgs/pens.png) value to back-end(spring) from front-end extjs grid. I have tested reading file based on asset logo path column value, blob data is reading if client and server both are in same system, but i want to upload excel sheet from system2 by connecting to my server system and read files from local system(based on uploaded system) and insert into db. when i upload excel sheet i am display uploaded data to extjs grid. in grid i have button. when i click on button i need to pass data to back-end.how can i read blob from other system. pls give some suggestions.
Renu C
September 20, 2016

Some of the istorm reports are not working correctly in Excel 365.Which version of POI.jar supports excel 365?Kindly help me.
Vinod
August 22, 2016

Hi Lokesh, it was very helpful. Thanks for your help. I have a question on your second example;
Reading an excel file:
instead of getting all the cell values, what if want to get an input from an user (getText), lets say : ID – 1 and I need those cell values i.e. Amit and Shukla? Please throw some light on this.
hari
April 27, 2016

Hi Mr.Lokesh,
Thanks for the helpful work uploaded.
Also, can u plz let me know can i read & write simultaneously from an excel file using Java, keeping the file open ?
Handsome
March 23, 2016

Hello,
How can I find user selected cells or active cells?
Thank u
sagar m
February 12, 2016

Hi,
I am uploading xl to db using Hibernate and spring i am facing foreign key problem.?please help me.
- Lokesh Gupta
  February 13, 2016
  
  What problem??
  - sagar m
    February 13, 2016
    
    how to pass foreign key to the controller?
Muthu
January 20, 2016

Great Work! That too, you have given the source code downloading option! Highly appreciated! Great work!
JohnyGaddar
January 15, 2016

Hi,
I am abel to write data in excel using above code, but when I open the file I am getting Data like :
3.0 John Adwards
4.0 Brian Schultz
ID NAME LASTNAME
1.0 Amit Shukla
2.0 Lokesh Gupta
any idea?
Manzer
November 11, 2015

Very very helpful post and brilliant tutorial for Excel
Naga Ramesh Pandi
October 27, 2015

Need the suggestion regarding the below..
reading the above created file, howtodoinjava_demo.xlsx.
while parsing by using SAX Parser, the output is differing the output as below.
Employee Data [index=0]:
“ID”,”NAME”,”LASTNAME”,,,
1.0,”Amit”,”Shukla”,,,
2.0,”Lokesh”,”Gupta”,,,
3.0,”John”,”Adwards”,,,
4.0,”Brian”,”Schultz”,,,
Same file just edited in local system and doing the same gives the below output – looks fine.
Employee Data [index=0]:
“ID”,”NAME”,”LASTNAME”,,,
1,”Amit”,”Shukla”,,,
2,”Lokesh”,”Gupta”,,,
3,”John”,”Adwards”,,,
4,”Brian”,”Schultz”,,,
Please advise what can we do to get same results with system generated file?
sumit
June 4, 2015

is Apache POI free if not then what price of this
- Lokesh Gupta
  June 4, 2015
  
  Its free.
Puneet
May 11, 2015

Hi Lokesh,
I am stuck in 1 requirement to upload an read excel and save it in db.
Problem: i have n no. of excel sheets and all have different columns and all sheets would save in different table (having different table structure). And i want to create a common service , which read excel sheets and save accordingly.
- Lokesh Gupta
  May 11, 2015
  
  In that case, you MUST put some restriction for users who are uploading those files. E.g. File names should include a particular word for each different format, OR column names should match exactly what is specified. There MUST be some rule which could be verified after file is received at server side.
  - Puneet
    May 11, 2015
    
    Hi Lokesh, Thanks for the reply. I should elaborate more of my problem, to understand u in clear way:
    For Example I have 3 excel tabs A, B & C. A has 5 columns, B has 10 & C has 15 columns. As of now, i am creating 3 different beans with the same no. of attributes (Setter/Getter) , sets every column and finally save the object in db(using hibernate).
    But using this concept, i need to create 3 different service for 3 different tabs.
    SO, is there any optimize way of doing this.
    - Lokesh Gupta
      May 12, 2015
      
      I will suggest you to stick with 3 services. It is good for future use.
      See, it’s more of coding style question. I believe that code should be easy enough to read AND follow these SOLID principles. I will prefer easy maintainable code, rather than optimized code. Choice is yours.
Majid Arif
April 27, 2015

Thanks for help buddy !
Owl
March 22, 2015

It looks like the source code does not include a pom.xml file. Where can I get one?
Narendra
February 16, 2015

i want to read data from excel sheet using range. suppose we want to read cell range A1:C12. please suggest me how read using cell range.
thanks
Steve Armstrong
February 8, 2015

Hi Lokesh,
The way evaluates return formulas is seriously baffling to me, and it’s disturbing me a little bit! When reading how this is done I didn’t quite believe it would work, here is the code where I’ve used the technique:
private Object getCellValue(Cell cell) {
int cellType = evaluator.evaluateInCell(cell).getCellType();
if (cellType == Cell.CELL_TYPE_STRING ) return cell.getStringCellValue (); else if (cellType == Cell.CELL_TYPE_NUMERIC) return cell.getNumericCellValue(); else if (cellType == Cell.CELL_TYPE_FORMULA) return null ; else if (cellType == Cell.CELL_TYPE_BOOLEAN) return cell.getBooleanCellValue(); else if (cellType == Cell.CELL_TYPE_ERROR ) return cell.getErrorCellValue ();
return ""; }
So despite returning null when I have a formula, the evaluation of the formula is automatically returned? But it was also automatically written to the file in your example above… For example I am grabbing data into arrays to use in an application and the array is automatically populated from the use of this function…
Spooky! Would be interested in knowing how this works :).

Anni

January 15, 2015

 while reading xlsx file i have following exceptions 
org.apache.poi.POIXMLException: java.lang.reflect.InvocationTargetException
	at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:62)
	at org.apache.poi.POIXMLDocumentPart.read(POIXMLDocumentPart.java:403)
	at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:155)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.&lt;init&gt;(XSSFWorkbook.java:207)
	at com.howtodoinjava.demo.poi.ReadExcelDemo.main(ReadExcelDemo.java:23)
Caused by: java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:60)
	... 4 more
Caused by: java.lang.IllegalStateException: Using in a JDK without an SHA implementation
	at org.apache.xmlbeans.impl.common.QNameHelper.hexsafe(QNameHelper.java:170)
	at org.apache.xmlbeans.impl.common.QNameHelper.hexsafedir(QNameHelper.java:178)
	at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderImpl.typeSystemForComponent(SchemaTypeLoaderImpl.java:249)
	at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderImpl.findDocumentTypeRef(SchemaTypeLoaderImpl.java:430)
	at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.findDocumentType(SchemaTypeLoaderBase.java:129)
	at org.apache.xmlbeans.impl.store.Locale.autoTypeDocument(Locale.java:312)
	at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1273)
	at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1257)
	at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
	at org.openxmlformats.schemas.spreadsheetml.x2006.main.SstDocument$Factory.parse(Unknown Source)
	at org.apache.poi.xssf.model.SharedStringsTable.readFrom(SharedStringsTable.java:115)
	at org.apache.poi.xssf.model.SharedStringsTable.&lt;init&gt;(SharedStringsTable.java:102)
	... 9 more
please help me to solve this problem as i m very new to java. thanks in advance

Lokesh Gupta
January 16, 2015

Which JRE (Oracle or IBM) and version you are using?

Neelam Joshi
December 24, 2014

Hi,
I want to delete data from ms excel file using Java. Please help me.
Jag
November 23, 2014

Hi Lokesh,
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.
Ex:
//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”, “Shukla”});
data.put(“3”, new Object[] {2, “Lokesh”, “Gupta”});
data.put(“4”, new Object[] {3, “John”, “Adwards”});
data.put(“5”, new Object[] {4, “Brian”, “Schultz”});
sri
November 23, 2014

Hi Guptha,
Id FirstName LastName City
10 Anjali K Pune
20 Pavani D Delhi
30 Anusha L Mumbai
I have Sample .Excel sheet with the above columns and data.I want to display this data in Jtable.Right now am able to display hardcoded data in the Jtable through Controller.But am not able to display through excel sheet.Can you please tell me how to do that ?
Thanks Advance
- Lokesh Gupta
  November 24, 2014
  
  Read excel data and show it in Jtable. Is there any specific issue you are facing??
  This link may help you.
Kumar
November 4, 2014

Hi Lokesh,
I have a sheet which have few columns , and I have to compare all the cells of the specific column by column’s heading name instead by its index value, can you guide me regarding this.
Thanks
Diego Matos
October 14, 2014

solved the problem when i removed my imports and add the default
there are :
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.primefaces.event.FileUploadEvent;
import org.primefaces.model.UploadedFile;
N Deepak Prasath
September 4, 2014

Hey .. Really useful.. Covers almost everything .. Thanks man !!
rajnish
August 12, 2014

i have genetated a .xls file and i am abls to save it by “FileOutputStream out = new FileOutputStream(new File(“DirName:\FolderName\fileName.xls”)); ” but i want it through GUI..
- Lokesh Gupta
  August 12, 2014
  You will get good information here: https://stackoverflow.com/questions/15703214/save-file-open-file-dialog-box-using-swing-netbeans-gui-editor
  Sample code can be written as below. You will have to write full code as per your need.
```
JFrame parentFrame = new JFrame();
 
JFileChooser fileChooser = new JFileChooser();
fileChooser.setDialogTitle(&amp;quot;Specify a file to save&amp;quot;);   
 
int userSelection = fileChooser.showSaveDialog(parentFrame);
 
if (userSelection == JFileChooser.APPROVE_OPTION) {
    File fileToSave = fileChooser.getSelectedFile();
    System.out.println(&amp;quot;Save as file: &amp;quot; + fileToSave.getAbsolutePath());
}
```
rajnish
August 12, 2014

How to save generated xls file at specified location ..means the path should be given by user where he want to save it??????
- Lokesh Gupta
  August 12, 2014
  
  It is done in different ways in different type of applications i.e. console application, desktop GUI application OR web-application. What you want to know?
bharat
August 7, 2014

hi guys
can any one help me to how to set mime type for downloading excel files.
bharat
August 7, 2014

i have used your example can any one halp me to set download prompt when generating excel files
thanks.
Kartik Purohit
July 28, 2014

Thanx man…. Covered all topics and more importantly code……… good work……… keep it up
Ankit
June 3, 2014

I am using J2EE 5.0 and jboss 5.1.0 and EJB 2.0 I have added follwing jars in lib folder and to the classpath of the project:-
dom4j-1.6.1.jar
poi-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-ooxml-schemas-3.9-20121203.jar
xmlbeans-2.3.0.jar
When i try to deploy and run the application it is showing
java.lang.ClassNotFoundException: org.apache.poi.xssf.usermodel.XSSFWorkbook from BaseClassLoader
kishor
May 20, 2014

Hey buddy.. Finally found the solution by adding the Below dependencies in the pom.xml
org.apache.poi poi 3.9
xml-apis xml-apis 1.4.01
org.apache.poi poi-ooxml 3.9
xml-apis xml-apis
org.apache.poi poi-ooxml-schemas 3.10-FINAL
kishor
May 20, 2014

Hi Lokesh,
Definately you have explained the things very well. But, I am completely struck at the step of handling xssf file. I am using maven and as suggested above, in my pom.xml file, I have added the below dependencies.
org.apache.poi poi 3.10-FINAL
org.apache.poi poi-ooxml 3.10-FINAL
But, when I try to run any of the simple tests after this, it gives me the error as :- java.lang.NoClassDefFoundError: org/w3c/dom/ElementTraversal
However, on removing the second dependency i.e. poi-ooxml, the other test classes work fine, but the excel code file shows error for xssf.
Please suggest some solution on it. I am completely struck on this issue.
My requirement is basically to add data from the second row of a particular sheet of the downloaded .xlsx file and then upload this excel file again to the application. I am referring the above code for updating the downloaded excel file. Also, It would be great if you can provide some existing code for such scenario.
Thanks & Regards,
Kishor
Akhilesh
May 11, 2014

which location excel file will be save? if I use writing the file code?
- Lokesh Gupta
  May 11, 2014
  
  You need to give the path in FileOutputStream(new File("c:/temp/howtodoinjava_demo.xlsx"));
Saurabh
May 2, 2014

i want to retrieve data from database to excelsheet i am able to do that but if that detail in database is having more details then cell size is not getting increase so how to increase the cell size according to field value can you help me sir..?
Jan H Malmberg
April 30, 2014

Hi Lokesh,
I am trying to create an Excel file but when my java class do
XSSFWorkbook workbook = new XSSFWorkbook();
I get the error
java.lang.ClassNotFoundException: Cannot find class ‘org.apache.xmlbeans.impl.regex.message’
java.lang.ClassNotFoundException: Cannot find class ‘org.apache.xmlbeans.impl.regex.message_en’
java.lang.ClassNotFoundException: Cannot find class ‘org.apache.xmlbeans.impl.regex.message_en_US’
However I cant find any message.class in xmlbeans-2.3.0.jar.
Do you have any idea why I get these errors.
/Jan
- Lokesh Gupta
  April 30, 2014
  
  Please make sure you have “xmlbeans-2.3.0.jar” jar in classpath. If still facing issue, paste here you .classpath entries.
  - Jan H Malmberg
    April 30, 2014
    
    My java class is in a jarfile together with the jarfiles containing the referenced classes. The manifest looks like:
    Manifest-Version: 1.0
    Ant-Version: Apache Ant 1.6.5
    Created-By: 1.4.2_15-b02 (Sun Microsystems Inc.)
    Transformer-class: CSendGlobalSuppliersToJcat
    Class-Path: dom4j-1.6.1.jar poi-3.7-20101029.jar
    poi-ooxml-3.7-20101029.jar poi-ooxml-schemas-3.7-20101029.jar
    xmlbeans-2.3.0.jar cjcatalogintegration.jar
    I have also tried with the latest versions of the poi files but with the same error.
    /Jan
    - Lokesh Gupta
      April 30, 2014
      
      Try upgrading the version number. I will try at my END.
      - Jan H Malmberg
        April 30, 2014
        
        What version number?
        
        Lokesh Gupta
        April 30, 2014
        
        version 2.4.0
        
        Jan H Malmberg
        May 5, 2014
        
        Hi Lokesh,
        I have tried with both 2.4.0 and 2.6.0 with the same error.
        The error message is “ClassNotFoundException: cannot find class ‘org.apache.xmlbeans.impl.regex.message'” but I dont think this is the problem. The error message refere to ‘org.apache.xmlbeans.impl.regex.message’ which is not a class.
        Might be some problem in language or territory configuration. I dont know.
        
        Lokesh Gupta
        May 5, 2014
        
        It’s properties file i.e. message.properties file. Can you please unzip the jar and check if it present.
        
        Jan H Malmberg
        May 5, 2014
        
        Yes, there are 4 files present:
        org.apache.xmlbeans.message.properties
        org.apache.xmlbeans.impl.regex..message.properties
        org.apache.xmlbeans.impl.regex..message_fr.properties
        org.apache.xmlbeans.impl.regex..message_ja.properties
        The error message refere to
        org.apache.xmlbeans.impl.regex..message.properties
        org.apache.xmlbeans.impl.regex..message_en.properties
        org.apache.xmlbeans.impl.regex..message_en_US.properties
        Same in all versions.
        
        Jan Malmberg
        May 6, 2014
        
        Problem is solved. The classloader failed to load resource files. The error message was confusing. Code works fine. Thanks.
srinikadiyala
April 28, 2014

Lokesh:
I want to have multiple XLS report files with charts be merged into one XLS file with multiple worksheets.
For example I have file1.xlsx with 3 worksheets with charts, and file2.xlsx with two worksheets having charts. The merged file should be file3.xlsx with 5 sheets having 3 from file1 and 2 from file 2.
Appreciate your inputs and thanks in advance
Srini
srini.kadiyala@gmail.com
Ram
April 17, 2014

Hi Logesh I need a java program to compare to identical excel sheet and bring a mismatches in the form of either pdf or simple HTML report.Please guide me or share sample code. Thank you in Advance
ronnie
April 14, 2014

Hi Lokesh,
Sometimes the data exceeds 30 lakh rows. In such cases, can u suggest me something that split the dat into multiple excel files, zip it and give the user a downloadable zip file option? Im using Rest services by the way
elbin
April 12, 2014

hi,
how to insert data to foreign key set table ………..?
Urien
April 11, 2014

For me, work only with poi-ooxml in maven dependency. Otherwise xssf missing, but still very helpful article, thanks for it.
Subrat Parida
April 6, 2014

Hi Lokesh,
Great article, Really helping a lot!!
I have written the data to the xls (Testing.xlsx), the program runs successfully but where can i get the output? (the xls file?)
- Subrat Parida
  April 7, 2014
  
  Got it, just gave the path in my local system but not able to open the excel file, not sure why, anybody knows, why i am not able to open the file?
  - Lokesh Gupta
    April 7, 2014
    
    Which excel version, you have installed on your system?
    - Subrat Parida
      April 8, 2014
      
      Excel 2007
      - Lokesh Gupta
        April 8, 2014
        
        upgrade it.
vinaykumar
March 29, 2014

Hi Lokesh,
My requirement is to insert the Excel sheet to a column in Oracle DataBase and process the files using a scheduler.
Inserted the Excel to CLOB column.But the way it is inserted i dont feel itis the correct way.
Even while trying to convert the CLOBData to HSSFworkbook , i am facing the below issue.
java.io.IOException: Invalid header signature; read 0x80E21193C59380E2, expected 0xE11AB1A1E011CFD0
Please , help me out.
I have no idea how to resolve this.
- Lokesh Gupta
  March 29, 2014
  
  See if they help. They make sense to me: https://stackoverflow.com/questions/11846398/use-poi-to-parse-excel-but-got-exception-invalid-header-signature and https://stackoverflow.com/questions/13949792/invalid-header-reading-xls-file
- Jan Malmberg
  May 2, 2014
  
  CLOB column is for text data. The Excel sheet must be treated as binarya data. Store it in a BLOB column. That should work.
Srinivas
March 28, 2014

Hi Lokesh,
I’m getting follwing error i’m followed u r code to create .xlsx file can you give any idea why this error occuring
ReadWriteExcelFile.java:7: cannot a
ccess org.apache.poi.hssf.usermodel.HSSFCell
bad class file: ….\java_lib\poi-3.9-20121203.jar(org/apache/poi/hssf
/usermodel/HSSFCell.class)
class file has wrong version 49.0, should be 48.0
Please remove or make sure it appears in the correct subdirectory of the classpa
th.
import org.apache.poi.hssf.usermodel.HSSFCell;
^
1 error
can any one knows the solution for above error can you please update me in below email id:- pv_srinivas2005@yahoo.com
- Lokesh Gupta
  March 29, 2014
  
  49.0 and 48.0 are the version number of the class file format. The error message means that POI-3.9 was compiled to JDK 5 class file format and you tried to use it with JDK 4. Of course, JDK 4 couldn’t support JDK 5 class file format. I think you can try older version POI. Or just use JDK 5.
  Major version number of the class file format being used:
  J2SE 7 = 51 (0x33 hex)
  J2SE 6.0 = 50 (0x32 hex)
  J2SE 5.0 = 49 (0x31 hex)
  JDK 1.4 = 48 (0x30 hex)
  JDK 1.3 = 47 (0x2F hex)
  JDK 1.2 = 46 (0x2E hex)
  JDK 1.1 = 45 (0x2D hex)
Seetesh
March 27, 2014

XLSX supports only 1 lakh data to be populated per sheet. Extra logic needs to be done for checking this and create new sheets depending on the volume of data fetched. this part seems to be missing in the code.
Nitin
March 25, 2014

if i have date column in my excel sheet then how to read that in java program
- Lokesh Gupta
  March 25, 2014
  
  Read the column just like any other text field, and parse it to date object.
sujini reddy
March 25, 2014

Am having the following error with yourcode which i had taken reading of excel sheet . In switch condition am getting error saying that cannot resolved
Hitesh Chikani
March 19, 2014

how can i convert VCF file into EXCEL…??????
- Hitesh Chikani
  March 19, 2014
  
  here is my Contact Vcf File Code.
  —————————————–
  private void getVcardString() throws IOException {
  final String vfile = “contacts.vcf”;
  // TODO Auto-generated method stub
  vCard = new ArrayList();
  cursor = getContentResolver().query(ContactsContract.CommonDataKinds.Phone.CONTENT_URI, null, null, null, null);
  if(cursor!=null&&cursor.getCount()>0)
  {
  int i;
  String storage_path = Environment.getExternalStorageDirectory().toString() + File.separator + vfile;
  FileOutputStream mFileOutputStream = new FileOutputStream(storage_path, false);
  cursor.moveToFirst();
  for(i = 0;i<cursor.getCount();i++)
  {
  get(cursor);
  Log.d("TAG", "Contact "+(i+1)+"VcF String is"+vCard.get(i));
  cursor.moveToNext();
  mFileOutputStream.write(vCard.get(i).toString().getBytes());
  }
  mFileOutputStream.close();
  cursor.close();
  }
  else
  {
  Log.d("TAG", "No Contacts in Your Phone");
  }
  }
  private void get(Cursor cursor2) {
  String lookupKey = cursor.getString(cursor.getColumnIndex(ContactsContract.Contacts.LOOKUP_KEY));
  Uri uri = Uri.withAppendedPath(ContactsContract.Contacts.CONTENT_VCARD_URI, lookupKey);
  AssetFileDescriptor fd;
  try {
  fd = this.getContentResolver().openAssetFileDescriptor(uri, "r");
  FileInputStream fis = fd.createInputStream();
  byte[] buf = new byte[(int) fd.getDeclaredLength()];
  fis.read(buf);
  String vcardstring= new String(buf);
  vCard.add(vcardstring);
  } catch (Exception e1)
  {
  // TODO Auto-generated catch block
  e1.printStackTrace();
  }
  }
Abdul
March 17, 2014

when i am rewriting a in excel sheet at the ending of excel sheet it is deleting all my privious data and showing new data from rownum where i started writing. I can read excel and save in temp file and rewrite it, but i want any alternative way for only wrting from where i want to write
- Lokesh Gupta
  March 17, 2014
  
  It might help you. https://stackoverflow.com/questions/9359913/writing-to-a-existing-xls-file-using-poi
sree lakshmi
March 7, 2014

HI,
when i included all the jar files and then tried to read excel sheet,when i tried to run the application i am getting error like :
trouble writing output: Too many methods: 66024; max is 65536. By package:
13 java.lang
1 java.lang.reflect
5 java.util
1 javax.xml.namespace
66 org.apache.xmlbeans………………………………………..[2014-03-07 11:11:13 – exceldemo] Conversion to Dalvik format failed with error 2
please any one help me how to clear this error.
- Lokesh Gupta
  March 7, 2014
  
  May be the size of worksheet is too long. Can you please re-test it by breaking into two.
  - sree lakshmi
    March 7, 2014
    
    Thanks for the reply.But no change i am getting the same error..I tried even by deleting some extra jar file but i did not find it help full
    .
dovu
March 5, 2014

thank you very much
udaykiranpulipati
March 5, 2014

Hi, Lokesh
I got below exception when I run a file.
Link of the file: ISOCountryCodes081507.xls
https://www.iso.org/iso-3166-country-codes.html
org.apache.poi.POIXMLException: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]
at org.apache.poi.util.PackageHelper.open(PackageHelper.java:41)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:204)
at com.howtodoinjava.demo.poi.ReadExcelDemo.main(ReadExcelDemo.java:22)
Caused by: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]
at org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl(ZipPackage.java:178)
at org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:662)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:269)
at org.apache.poi.util.PackageHelper.open(PackageHelper.java:39)
… 2 more
VKS
March 1, 2014

Hi All, I was trying to merge multiple xml files and generate a excel out of it. Any help?
snippetjournal
January 28, 2014

great tutorial, its very helpful, thank a lot
- Salsa
  February 6, 2014
  
  Hi Lokesh,
  I’m trying your code to experiment and getting an error at the line below:
  data.put(“1”, new Object[] {“ID”, “NAME”, “LASTNAME”}); <—Syntax error on tocken(s), misplaced construct(s)
  Regards,
  - Salsa
    February 7, 2014
    
    Issue resolved. I created a new project in Eclipse, added the POI (jar) to the libraries and the syntax is no longer displayed. Regards,
syam
January 24, 2014

Hi Lokesh,
when am trying to do the export excel sheet, i have to show the 5 digit zip code in xl, in java if i read it as integer for zipcode in excel sheet it is just showing it as 4 digit ex for 01201 ->1201 in xl. so if i change it as string in code, in excel sheet it is showing the error. any suggetions plz…
Samar
January 2, 2014

Hi Lokesh
I was trying
FileInputStream file = new FileInputStream(new File(xlsPath));
XSSFWorkbook workBook = new XSSFWorkbook(file);
but in second line it is going in to hung state>
Can you help me why it is going in to hung state
Thanks
Samar
- Lokesh Gupta
  January 2, 2014
  
  File seems to be too big in size. First try with small file.
Aby Jose
December 11, 2013

Nice. Covered almost everything about excel… Tried reading data from excel file using POI. Smaller files works fine. But when I tried opening an excel file having 25000 rows(28 columns), out of memory exception occurred. Tired allocating maximum heap memory, still exception happens. Could you please tell me if any alternatives are available?
Manju
December 11, 2013

I am trying parse values thru excel sheet to java thru readexcel, once the read file ,i want write some column in same readexcel
how to do that
- Lokesh Gupta
  December 11, 2013
  
  Locate a desired cell in readexcel and use .setCellValue() to write your content.
Tasaytaka
December 10, 2013

Hi Lokesh, my case study is how to get data from the excel file into values of the attributes of java objects (the goal is making many objects that get values from the excel file). How can we save the values and then assign its to the attributes of objects? For instance in the excel file, we have “Name” column with values “ABC”, “DEF”,”XYZ” ; and “Age” column with values “18”, “17”, “19” corresponding; the job is making 3 objects with name and age corresponding.
Could you show me the steps to solve it!
Thank so much!
- Lokesh Gupta
  December 10, 2013
  
  I am understanding it correctly, you want to read all columns in a row and set as attribute in a object, right?? Have you tried it doing yourself first?
  - Tasaytaka
    December 10, 2013
    
    Yes, I used jexcelapi library to get data from excel file, but I dont know how to assign the values to objects. I think I have to use ArrayList to store them. just research at that. Thanks for reply!
asif aftab
December 3, 2013

Thanks
Really very help full document.
asif aftab
Manjunath
November 30, 2013

Hi,Thanks for the tutorial.
Kindly tell me how to check for not null fields while importing data from an excel into a table ?.
Thushara
November 24, 2013

Great job.
Thank you very much.
anon
November 21, 2013

hi, my data displayed from the excel arent showing in their proper columns. its either out of the columns or goes into another column. for example, the headings for the columns are ‘no. of sensor for chn1’ & multiplexer. the data in those columns arent displaying properly. any suggestions on how to display them in place ? =]
- Lokesh Gupta
  November 21, 2013
  
  Code please?
Rich
November 20, 2013

Hi Lokesh, with your first tutorial in writing data into excel, it returns an error:
Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject
at com.howtodoinjava.demo.poi.WriteExcelDemo.main(WriteExcelDemo.java:19)
Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlObject
at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
at java.lang.ClassLoader.loadClass(ClassLoader.java:423)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
at java.lang.ClassLoader.loadClass(ClassLoader.java:356)
… 1 more
I’ve imported all the needed jars. Please help me with this. Thank you so much! 🙂
- Lokesh Gupta
  November 20, 2013
  
  The class is inside “xmlbeans-2.3.0.jar”. Please make sure you have this jar in classpath.
  If still facing issue, paste here you .classpath entries.
  - Rich
    November 25, 2013
    
    Thank you.. 🙂
George
November 12, 2013

Hi Lokesh,
I have a simple problem i guess. I am within 2 stage “For loop”. First loop is for col and second loop is for row, such that program will write “Hello” in 3 columns consecutively each with 10 rows in excel
Can you please help me using poi packages.
for(int i=0; i<=2; i++){ //For Col for(int j=0; j<=9; j++){ //For Rows System.out.println("Hello"); } }Thanks
- Lokesh Gupta
  November 12, 2013
  
  Please try first. Let me know where you are struck. I really do not have time to write whole program from beginning.
lukasz
November 8, 2013

You colud write what import to files or give more visible information that ur example has source code 😀
thx a lot
Haris
November 8, 2013

Hi,
I am getting errors at Iterator cellIterator = row.cellIterator(); and
Iterator rowIterator = sheet.iterator();
The error says Type Mismatch:cannot convert from Iterator to Iterator. What might be the problem?
huda
November 7, 2013

hey, i have these sets of data i am streaming wirelessly and i have followed your steps to create a database. i was wondering, is it possible for the data to keep refreshing itself as new data are being fetched ?
- Lokesh Gupta
  November 7, 2013
  
  I really doubt that any database would be capable of doing that. At least I am not aware of any such capability. You can write another thread which keep reading data from source and write it to database.
  - huda
    November 8, 2013
    
    errmm.. how do i do that exactly ? im not really good in java programming. a tutorial maybe ?
Aan
November 6, 2013

hi, what if i want to read from an csv file ? isit possible ? and how do make it read row by row ? are delays possible just like c programming ? any help would be greatly appreciated. =]
- Lokesh Gupta
  November 6, 2013
  
  Use below post code to read data into String. https://howtodoinjava.com/apache-commons/how-to-read-data-from-inputstream-into-string-in-java/
  You can use readLine() method to read line by line. https://stackoverflow.com/questions/5868369/how-to-read-a-large-text-file-line-by-line-in-java
  Using delays : NO idea. I will explore this.
  - Aan
    November 7, 2013
    
    thanks for the help ! i will try to look into the suggestions above. will ask again if ive got anything to clarify. =]
  - Aan
    November 7, 2013
    
    i still cant find how to display out row by row. for example, i have these sets of data, and i want it to print row by row. like a 1-2s interval between each data. isit possible ?
    - Lokesh Gupta
      November 7, 2013
      
      Nothing is impossible. Are you able to read data one line at a time? If not then solve this problem first. If you are getting data from stream then there must be some “end of line” token/separator or number of words/ separators present into the stream. Look for them and use them.
      Displaying in 1-2s is not a big problem. You can use Thread.sleep() for this.
      - Aan
        November 7, 2013
        
        i am able to display the whole data in the csv file but not one line at a time. im trying the Thread.sleep() now. thanks again =]
      - Aan
        November 7, 2013
        
        ITS WORKING ! THANKS FOR THE HELP ! =] one more question. how do i put my data into a real time graph ? any ideas ?
        
        Lokesh Gupta
        November 7, 2013
        
        It depends on your UI layer. Is it browser based web application OR windows executable?
      - huda
        November 8, 2013
        
        its a windows executable file. so how do i go about doing it ? =]
Aan
November 6, 2013

hi. i was wondering if you could tell me how to display single lines at a time from an excel file. lets say im streaming data and i want the data to appear one at a time and not everything at one go. is there any delay function like C programming ? help will be much appreciated ! =]
- Lokesh Gupta
  November 6, 2013
  
  I am not aware of any delay like feature in java. The most closely related feature is asynchronous operations.
  Regarding your problem, I do not fully understand your problem. But, from what I understood, you can use a queue, where you push bytes into the queue and a running thread pulls desired number of bytes( or available bytes if less bytes are present in queue at moment) and display them on screen.
Diego Rodriguez
November 1, 2013

Hi Lokesh,
Using HSSF, how to modify the size and font of a text in a cell
David Zondray
November 1, 2013

While searching for my problem I came across your post. I know it’s a bit late but I am sure it will be useful for the other guys…. I came across great Java Excel component by the name of Aspose.Cells for Java. It is not open source, but contains very powerful features. It allows Java developers to embed the ability to read, write and manipulate Excel® spreadsheets (XLS, XLSX, XLSM, SpreadsheetML, CSV) EML, HTML, PDF and image file formats into their own Java applications. Some Examples with code are:
https://docs.aspose.com/display/cellsjava/Opening+Files
https://docs.aspose.com/display/cellsjava/Copying+and+Moving+Worksheets
https://docs.aspose.com/display/cellsjava/Save+Entire+Workbook+into+Text+or+CSV+Format
I am sure it can help
vijay(masterminds)
October 28, 2013

How to set the width and height to cell…?? I am using HSSF .Please reply..?
- Lokesh Gupta
  October 28, 2013
  
  http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Sheet.html#setColumnWidth%28int,%20int%29
  OR
  http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#autoSizeColumn%28int%29
  e.g.
  sheet.autoSizeColumn(5);
  - vijay(masterminds)
    October 30, 2013
    
    Thanks sir…I was searching at wrong place…thanks a lot… 🙂
sriram
October 28, 2013

Hi sir,I want to read an excel file having checkboxes and combo boxes,is it possible to do it in java sir.plz help me sir.Thanks
Venkata Sriram
- Lokesh Gupta
  October 28, 2013
  
  Hi Sri, I am really unaware of any such capability.
- vijay(masterminds)
  October 30, 2013
  
  Try if useful…..
  http://poi.apache.org/apidocs/org/apache/poi/hssf/record/CommonObjectDataSubRecord.html
Anjan Baradwaj
October 28, 2013

A good tutorial for beginners to understand and grasp the concepts quickly.
I have a doubt, more like a requirement and I’d appreciate if you could just suggest me how to go about doing it.
I need to dynamically retrieve data from MySql database and then write it to an Excel file. How do I go about achieving this? Thanks in advance.
Ahmed
October 27, 2013

How can i use this API load data from mysql to excel knowing that i use struts2 and hibernate
Zeryoun
October 24, 2013

I use Aspose.Cells for Java Library for creating, writing , modifying and even converting excel files to many formats and this library offers many features that java developers can use in their application.
- Lokesh Gupta
  October 25, 2013
  
  Thanks for the link
Donavan
October 23, 2013

The list of Jar that mentioned that need to include are total 10.8MB. That is really to huge if want to allow user to download application via internet.
You also having the same issue? Any other suggestion?
- Lokesh Gupta
  October 23, 2013
  
  Yes, I have worked on such project which was basically a desktop application. Total project size with jars was 17.8 MB. But after creating exe for that project, size reduced to 7.5 MB which is quite “OK” to me.
Rashmi
October 22, 2013

Hi Lokesh,
I have huge number of records in one excel as repository.
i need to search particular record.If i go with cell by cell it takes lot of time .
EX:if 1000 lines are there in excel if record is in 1000 line then it has to go 999 cells .
I want to know is there any smart search is there in POI instead of every cell it should search that cell directly (as we search like from CNTRL+F)
- Lokesh Gupta
  October 22, 2013
  
  In my knowledge, POI do not have this functionality or any other simple search functionality. You have to iterate through cells and observe the content for match.
vijay(masterminds)
October 17, 2013

How can I create password protected excel sheets….? I want contents also to be encrypted?
- Lokesh Gupta
  October 17, 2013
  
  Try using: http://poi.apache.org/apidocs/org/apache/poi/hssf/record/crypto/Biff8EncryptionKey.html
  - vijay(masterminds)
    October 18, 2013
    
    thanks Lokesh…I will go through it…
  - sachin
    December 2, 2015
    
    how can you read the password protected excel file ?
    - Lokesh Gupta
      December 2, 2015
      
      Hi Sachin, first find any tool to crack the password. Then read it. I am not aware of any other way.
vijay(masterminds)
October 16, 2013

Really very very thankful…I have just 1+ yrs of experience in java…this blog has everything which I need….i mean where there are chances of stuck….
Pracheer
October 14, 2013

hi Lokesh,
I have a issue with excel. I have column with a date values which are taken as decimal and through dbunit the values are saving into db. While entering the values in the template if the green triangle is not coming it doesnt read those values as text i think that is feature of excel. My issue is if suppose i enter 20130630 in date column and without that triangle apperance in excel than in the db it is getting stored in exponential format like 2.01306E7 which i dont want as there are procedures written which validate the date values. Now if i do something like setting first cell type as string and then setting string value then it is throwing illegal state exception. Please suggest what needs to be done in such case.
- Lokesh Gupta
  October 14, 2013
  
  Will it help? https://stackoverflow.com/questions/5794659/poi-how-do-i-set-cell-value-to-date-and-apply-default-excel-date-format
  - Pracheer
    October 14, 2013
    
    The problem is i am checking for condition if cell type is numeric then inside that condition i am checking if isCellDateFormatted(Cell cell) and if it is then i am getting date value of cell and setting it to the out cell. But if it returns false then i am setting outCell.setValue(inCell.getNumericValue()).In my case the the second condition outCell.setValue(inCell.getNumericValue()) is getting executed which sets it to exponential form.
    - Pracheer
      October 14, 2013
      
      Lokesh thanks for your quick reply i somehow found out this String text = NumberToTextConverter.toText(cell.getNumericCellValue());
      http://poi.apache.org/apidocs/org/apache/poi/ss/util/NumberToTextConverter.html
      I think i should try this it may help.
      - Junaid
        December 19, 2013
        
        thanks dud… ek number its working….. 😛
    - Lokesh Gupta
      October 14, 2013
      
      Try changing width of cell in excel. Sometimes number fields show up in exponential form when there is not enough width to show them.
kannan
October 12, 2013

Hi Gupta,
I have a array of formula in excel, which works fine in excel but the same thing is not working in APACHE POI…
Could you please let us know, is there any work around to solve the issue.
Thanks
Kannan
- Lokesh Gupta
  October 12, 2013
  
  Common issue. Check for excel version compatibility.
  - kannan
    October 12, 2013
    
    Thanks for the quick reply, am using Xlsx verison and poi 3.8 version and here is the formula, which we are trying to execute from poi. but it’s not working..i looked at the apache poi site where they are saying array of formula not supported by poi.. is there any alternative way to modify the formula to work with POI.
    Please through the light.
    {=IF($F4=0,0,SUM(($F$28:$F$427*($D$28:$D$427=$D4)/$F4)*$K$28:$K$427))}
    - Lokesh Gupta
      October 12, 2013
      
      I am sorry buddy, but I am no good in excel formulas.
      Hey knowledge eaters, would someone like to help kannan???
Irfan
October 11, 2013

i am having one query. In excel if i type 8 then its reading as 8.0, but i dont want to read as 8.0 so i did this
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
cell.setCellType(Cell.CELL_TYPE_STRING);
//value = (cell.getNumericCellValue() + “”).trim();
value = (cell.getStringCellValue() + “”).trim();
System.out.println(“Value=”+value);}
now its coming as 8, after that code. But if i insert 8.0 in excel i want output as 8.0 but i am getting as 8. How to solve this problem?
- Lokesh Gupta
  October 11, 2013
  
  You should really do not need to make this change. If you assign this numeric value (8.0) to int, it will automatically convert to 8.
  - Irfan
    October 18, 2013
    
    But what i want is, if i write 8.0 in excel then i want output as 8.0 in java, if i write 8 in excel then i want output as 8
    - Lokesh Gupta
      October 18, 2013
      
      Then consider this as string only, and no numbers.
David Vega
October 3, 2013

Hi, thks for the tutorial,
Got a question, when I’m about to write 40 thousand lines in the Map, by the way Im using a LinkedHashMap; Java return an Exception in thread “main” java.lang.OutOfMemoryError: GC overhead limit exceeded;
Could you hep me with this issue, what can I do??
Its supossed that i should write around 150 thousand lines.. it work very well under the 40 thousand lines…. the it crush.. please help me..
- Lokesh Gupta
  October 4, 2013
  
  You need to write the lines in bunch. Say in bunch of 5000 lines. Once you write 5000 lines, flush in file system. Do it in loop. POI is not memory effective. I also faced such problem once. I ended up doing what i suggested you.
  - David Vega
    October 25, 2013
    
    Hello again.. Ive already use the flush o 5000 lines and still got an error of memory, this time is “main” java.lang.OutOfMemoryError: Java heap space….. a this time it only write till 20000 lines in the file, Could you help me with this?
    Im using the follow code:
    int rownum = 0;
    while (rownum < f) { if(cp==5000){System.gc(); out.flush(); Runtime.getRuntime().gc(); Thread.sleep(100000); cp = 0; }while(ct<100){ Row row = sheet.createRow(rownum++); fT++; ct++; cp++; System.out.println(rownum); Object [] objArr = data.get(String.valueOf(rownum)); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++);System.out.println(cellnum); if(obj instanceof Date) cell.setCellValue((Date)obj); else if(obj instanceof Boolean) cell.setCellValue((Boolean)obj); else if(obj instanceof String) cell.setCellValue((String)obj); else if(obj instanceof Double) cell.setCellValue((Double)obj); } } workbook.write(out); System.out.println("lineas= "+rownum); System.out.println("Excel escrito con exito.."); ct=0; System.gc(); out.flush(); Runtime.getRuntime().gc(); } System.out.println("lineas= "+fT); System.out.println("Excel escrito con exito.."); //workbook.write(out); out.close();} catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }by the way f = 140000Waiting for your answer thks
  - Aby Jose
    November 6, 2013
    
    Could you please give a sample code on how flushing can be done?
    - Lokesh Gupta
      November 6, 2013
      
      I will post another tutorial on this on next week. currently, I am on holidays.
      - Aby Jose
        December 11, 2013
        
        Sir, have u posted the tutorial on creating large excel files?
        
        Lokesh Gupta
        December 11, 2013
        
        I will soon
prabhakar
October 3, 2013

hi sir i need this to be done in dowloadable format.. means open or save dialog box should appear
- Lokesh Gupta
  October 3, 2013
  
  Didn’t got, what you want to do?
- vijay(masterminds)
  October 28, 2013
  
  If I am not wrong you want to use this API in web application….
- Tony
  November 23, 2013
  
  Hi Prabhakar
  I hope you have already did this.But these things you can’t handle with java.You have to play with the front end stuffs like jquery/java script or adobe flex(mxml or action scrpt file).It’s depends on your project arch.. on which you are working
Vinh Vietnam
October 1, 2013

Thanks you!
mv
September 23, 2013

Hello, I’m trying to write some data to existing workbook using following code:
OPCPackage pkg = OPCPackage.open(file);
XSSFWorkbook workbook = new XSSFWorkbook(pkg);
// adding rows and cells here
pkg.flush();
pkg.close();
No exceptions are thrown but target file always stays untouched. “Last modified” date of file changes correctly though. I wonder if I’m doing something wrong.
- Lokesh Gupta
  September 23, 2013
  
  Use something like this: https://stackoverflow.com/questions/12261014/close-filehandle-for-workbook-apache-poi
chinmay narwane
September 16, 2013

hello sir , i am chinmay narwane ,i have a project in which i have to take data from one excel sheet to another and again i have to populate the excel sheets on runtime ,i mean for manual data enrty also the user shoul see a excell sheet how to do that
- Lokesh Gupta
  September 16, 2013
  
  What are the challenges? I see none.
Venkata Sriram
September 2, 2013

Hi sir,
Iam using POI for Generating excel,but the data comming from Oracle DB,how many rows are there that many times hashmap is filling,sometimes iam getting huge data from DB,it wont effect any performance of the Application na sir?because new Object[] creating = size of resultset. or we have to explicitly derefer the Map.can you please help me out sir.Please clear that object array creation is a good practice or not sir.?please correct me if iam wrong
Thanks
Sriram
- Lokesh Gupta
  September 2, 2013
  
  Hi Sri, I have faced such problem in past and yes , memory problems occurs frequently when working with POI. Memory problems will not be on HashMap or java objects side, but on POI sheet and cell sides.
  So, to avoid such memory issues, initialize the program with bigger JVM memory. Second thing is to write data periodically (flush) in filesystem, rather than populating whole sheet and then write it to filesystem.
  That’s all.
Sunil Kumar
August 31, 2013

Hi, I need one help.
I need to generate a word document dynamically in JAVA. Based on the input from user in the UI, the values will come from database and then, i need to generate the values in word doc with proper formatting like bold, italic and font-size. The word document will contain proper header & footer, page numbers with some images(if possible). This same thing i need to generate a PDF also.
Thanks in advance!
- Lokesh Gupta
  August 31, 2013
  
  I am afraid I might not able to spend time on this as of now, due to my busy schedule for next couple of months.
- R
  September 11, 2013
  
  Use itext-1.4.6.jar (latest one) to create pdf..You will get a lot pf help on net for this..
Ashutosh Singh
August 24, 2013

sir, i have implemented downloaded code but found error :
Exception in thread “main” java.lang.ExceptionInInitializerError
at org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook$Factory.newInstance(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.onWorkbookCreate(XSSFWorkbook.java:307)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:167)
at com.howtodoinjava.demo.poi.WriteExcelDemo.main(WriteExcelDemo.java:20)
Caused by: java.lang.NullPointerException
at org.apache.xmlbeans.XmlBeans.typeSystemForClassLoader(XmlBeans.java:769)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook.(Unknown Source)
… 4 more
Kindly solve it..i will be very thankful to you…
- Lokesh Gupta
  August 24, 2013
  
  Hey Ashu, can you please confirm what all jars u got in your classpath. Please mention their version numbers.
  - Ashutosh Singh
    August 24, 2013
    
    dom4j-1.6.1.jar
    poi-3.9-20121203.jar
    poi-ooxml-3.9-20121203.jar
    poi-ooxml-schemas-3.9-20121203.jar
    xmlbeans-2.3.0.jar
    - Lokesh Gupta
      August 24, 2013
      
      Its really strange cause I am facing no issue with similar set of jars. Can you please try removing “xmlbeans-2.3.0.jar” from classpath and see if it helps. Its definitely some jar dependency issue in your system. I really can’t help much as I am not able to reproduce this error.
      - Ashutosh Singh
        August 24, 2013
        
        sir, i am using jdk 1.6 and after removing “xmlbeans-2.3.0.jar” after that the error comes :
        Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlOptions
        at org.apache.poi.POIXMLDocumentPart.(POIXMLDocumentPart.java:53)
        at com.howtodoinjava.demo.poi.WriteExcelDemo.main(WriteExcelDemo.java:20)
Ramesh J
August 23, 2013

Splendid work ….
Saravanan
August 19, 2013

Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject
at Excel_Final.main(Excel_Final.java:49)
Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlObject
Now I am facing this error after clearing the Iterator Issues.
Do I need to import any necessary packages? Please help me.
Saravanan
August 19, 2013

Iterator rowIterator = sheet.iterator();
while(rowIterator.hasNext()) {
Row row = rowIterator.next();
while attempting to Iterate in Cells, I am facing “The Type HTML Document.Iterator is not generic. It can not be parameterized with Arguments ”
I dont have any clue why i am facing this prob. Can you help me to sort out this…?
Venkata Sriram
August 2, 2013

Thanks sir,i have one doubt sir,How to make only Headers bold by adding directly to map and iterate sir.
Marc
July 18, 2013

Excellent post. Many Thanks
Krishna
June 25, 2013

hi,
Using XSSF, how to set the color for particular line in a cell.
- Lokesh Gupta
  June 25, 2013
  
  Try this:
  XSSFCellStyle style = (XSSFCellStyle)cell.getCellStyle();
  XSSFColor myColor = new XSSFColor(Color.RED);
  style.setFillForegroundColor(myColor);
  https://stackoverflow.com/questions/10912578/apache-poi-xssfcolor-from-hex-code/10924483#10924483
Vicky Thakor
June 19, 2013

Nice. Covered almost everything about excel…

Comments are closed on this article!