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.

Ads by Google

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

Ads by Google

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

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

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

    1. You will get good information here: http://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("Specify a file to save");   
       
      int userSelection = fileChooser.showSaveDialog(parentFrame);
       
      if (userSelection == JFileChooser.APPROVE_OPTION) {
          File fileToSave = fileChooser.getSelectedFile();
          System.out.println("Save as file: " + fileToSave.getAbsolutePath());
      }
      
  3. How to save generated xls file at specified location ..means the path should be given by user where he want to save it??????

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

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

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

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

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

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

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

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

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

                    1. Problem is solved. The classloader failed to load resource files. The error message was confusing. Code works fine. Thanks.

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

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

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

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

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

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

    1. CLOB column is for text data. The Excel sheet must be treated as binarya data. Store it in a BLOB column. That should work.

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

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

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

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

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

Note:- In comment box, please put your code inside [java] ... [/java] OR [xml] ... [/xml] tags otherwise it may not appear as intended.

Leave a Reply

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


four + = 6

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>