Reading/writing excel files in java : POI tutorial

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. These are many open source APIs to handle such scenarios.

Apache POI is one of them and is well trusted over time. In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java.

In this post, I am discussing some common activities required to do in real life application.

Sections in this post:

Apache POI runtime dependencies
Some useful common classes
Writing an excel file
Reading an excel file
Using formulas in excel sheet
Formatting the cells
Sourcecode download

Apache POI runtime 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 minimum to run the sample code:

  • 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

Some useful POI 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.

Apart from above classes, Row and Cell are used to interact with a particular row and a particular cell in excel sheet.

Another useful class FormulaEvaluator is used to evaluate the formula cells in excel sheet.

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.

We will see the usage of above classes in coming examples.

Writing 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 a file using POI is very simple and involve following steps:

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

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

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();
		}
	}
}

poi-demo-write-file

Reading an excel file

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

  1. Create workbook instance from excel sheet
  2. Get to the desired sheet
  3. Increment row number
  4. iterate over all cells in a row
  5. 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.

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	

Using formulas in excel sheet

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 set 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)

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 the formula cells.

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	

poi-demo-write-formula

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 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);
}

poi-demo-formatting-1

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)");
}

poi-demo-formatting-2

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)");
}

poi-demo-formatting-3

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");
}

poi-demo-formatting-4

I am ending this post here for keeping the post in limit. I will post some useful code samples in coming posts.

Sourcecode download

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

Happy Learning !!

References

http://poi.apache.org/spreadsheet/quick-guide.html

Lokesh has written 269 articles

I have 7 Years of rich experience in java technology. This has only increased my hunger to learn more. In this blog, i will be writing on different topics occasionally, and would love to engage in some meaningful serious discussions with you folks.

134 thoughts on “Reading/writing excel files in java : POI tutorial

  1. Ram says:

    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

  2. ronnie says:

    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

  3. Subrat Parida says:

    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?)

    1. Subrat Parida says:

      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?

  4. vinaykumar says:

    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.

  5. Srinivas says:

    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

    1. Lokesh says:

      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 (0×33 hex)
      J2SE 6.0 = 50 (0×32 hex)
      J2SE 5.0 = 49 (0×31 hex)
      JDK 1.4 = 48 (0×30 hex)
      JDK 1.3 = 47 (0x2F hex)
      JDK 1.2 = 46 (0x2E hex)
      JDK 1.1 = 45 (0x2D hex)

  6. Seetesh says:

    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.

  7. sujini reddy says:

    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

    1. 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();
      }
      }

  8. Abdul says:

    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

  9. sree lakshmi says:

    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.

      1. sree lakshmi says:

        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
        .

  10. Hi, Lokesh

    I got below exception when I run a file.

    Link of the file: ISOCountryCodes081507.xls

    https://www.gbta.org/Lists/Resource%20Library/ISOCountryCodes081507.xls

    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

    1. Salsa says:

      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,

  11. syam says:

    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…

  12. Samar says:

    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

  13. Aby Jose says:

    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?

  14. Tasaytaka says:

    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!

      1. Tasaytaka says:

        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!

  15. anon says:

    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 ? =]

  16. Rich says:

    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! :)

  17. George says:

    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

  18. Haris says:

    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?

  19. huda says:

    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 ?

  20. Aan says:

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

      1. Aan says:

        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 ?

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

  21. Aan says:

    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 ! =]

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

  22. David Zondray says:

    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:
    http://www.aspose.com/docs/display/cellsjava/Opening+Files
    http://www.aspose.com/docs/display/cellsjava/Copying+and+Moving+Worksheets
    http://www.aspose.com/docs/display/cellsjava/Save+Entire+Workbook+into+Text+or+CSV+Format
    I am sure it can help

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

  24. Donavan says:

    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?

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

  25. Rashmi says:

    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)

  26. vijay(masterminds) says:

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

  27. Pracheer says:

    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.

      1. Pracheer says:

        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.

  28. kannan says:

    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

      1. kannan says:

        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))}

  29. Irfan says:

    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?

  30. David Vega says:

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

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

      1. David Vega says:

        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 = 140000

        Waiting for your answer thks

    1. Tony says:

      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

  31. mv says:

    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.

  32. chinmay narwane says:

    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

  33. Venkata Sriram says:

    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

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

  34. Sunil Kumar says:

    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!

  35. Ashutosh Singh says:

    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…

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

          1. Ashutosh Singh says:

            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)

  36. Saravanan says:

    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.

  37. Saravanan says:

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

Want to ask any question? Or suggest anything?