Read / Write Excel file in Java using Apache POI

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

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

Let’s get started.

Tools & Technologies:

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

1. Add Apache POI dependency

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

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

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

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

2. Read Excel File

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

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

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

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

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

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

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

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

Working with .xlsx files

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

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

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

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

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

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

Consider a sample excel file:

test.xls
java read excel file

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

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

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

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

Output:

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

3. Create New Excel File

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

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

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

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

Output: new.xls
java-write-excel file

4. Update Existing Excel File

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

update.xls
java-update-excel-before

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

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

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

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

Steps to update excel file will be:

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

Output: update.xls
java-update-excel-after

5. Adding Formulas

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

cell.setCellFormula("someformula")

For example:

cell.setCellFormula("A2*B2*C5")
//or
cell.setCellFormula("SUM(A1:A7)")

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

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

The above code will throw:

org.apache.poi.ss.formula.FormulaParseException: 
The specified formula '=A2*B2*C5' starts with an equals sign which is not allowed.

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

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

		Row header = sheet.createRow(0);
		header.createCell(0).setCellValue("Pricipal Amount (P)");
		header.createCell(1).setCellValue("Rate of Interest (r)");
		header.createCell(2).setCellValue("Tenure (t)");
		header.createCell(3).setCellValue("Interest (P r t)");
		
		Row dataRow = sheet.createRow(1);
		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("C:\\formula.xls"));
			workbook.write(out);
			out.close();
			System.out.println("Excel written successfully..");
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

Output: formula.xls
java-excel-add-formula

Triggering Existing Excel Formulas

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

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

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

6. Adding Styles to Cell

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

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

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

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

Output: style.xls
java-excel-cell-style

6.1 Add Background Color to the cell

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

cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

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

I hope this article is useful.

Get our Articles via Email. Enter your email address.

You may also like...

219 Comments

  1. Mayur Chavda says:

    Hi Viral,
    I want to read only BOLD text from excel file and save it to another new excel file
    Any suggestion appreciated.

    Thanks

    • Viral Patel says:

      You can check the style for given cell and see if it is bold or not. Something like following:

      HSSFCell cell = ... //get cell
      short boldness = cell.getCellStyle().getFont(workbook).getBoldweight();
      if(Font.BOLDWEIGHT_BOLD == boldness) {
          //the cell is having bold font
          ///do something...
      }
      

      Hope that helps.

      • Satheesh says:

        Hi Viral,

        could you please share an example for .xlsx file.

        Thanks in Advance,
        Satheesh

        • Prasad says:

          Hey Satheesh,

          Change HSSF to XSSF, it should work.

          • Prasad says:

            I created code for reading xlsx file using the example given by Viral Patel and it works fine for me….Sateesh, for your reference I am pasting the code…

            try{	
            			
            		
            		FileInputStream file = new FileInputStream(new File("< path of excel file.....xlsx"));
            		
            		XSSFWorkbook wb = new XSSFWorkbook(file);
            		
            		XSSFSheet sheet = wb.getSheetAt(0);
            		
            		//iterate through each row from first sheet
            		Iterator<Row> rowIterator = sheet.iterator();
            		while(rowIterator.hasNext()){
            			Row row = rowIterator.next();
            			
            			//Fore each row iterate through each column
            			Iterator<Cell> cellIterator = row.cellIterator();
            			while(cellIterator.hasNext()){
            				Cell cell = cellIterator.next();
            				
            				
            				switch (cell.getCellType()){
            					case Cell.CELL_TYPE_BOOLEAN:
            						System.out.print(cell.getBooleanCellValue() + "\t\t");
            						break;
            						
            					case Cell.CELL_TYPE_NUMERIC:
            						System.out.print(cell.getNumericCellValue() + "\t\t");
            						break;
            						
            					case Cell.CELL_TYPE_STRING:
            						System.out.print(cell.getStringCellValue() + "\t\t");
            						break; 
            						
            				}
            				
            			}
            			
            			System.out.println("");
            		}
            		
            		file.close();
            		FileOutputStream out = new FileOutputStream (new File("< path of excel file.....xlsx"));
            		wb.write(out);
            		out.close();
            				
            				} catch(FileNotFoundException e){
            					e.printStackTrace();
            				} catch (IOException e){
            					e.printStackTrace();
            				}
            
    • Mladen says:

      boolean isbold = font.getBoldweight() > HSSFFont.BOLDWEIGHT_NORMAL;

      Check SVTableUtils from poi-examples.jar

  2. Vinayak Pahalwan says:

    I want to count the number of columns in the sheet and print the number of rows in a particular coulm. How i can do that? Like manually i have to iterate ? can you provide the code or describe how to do it

  3. Priyank Vyas says:

    i want enter null or empty string in DB is it possible if possible then how to do it
    if any cell is empty it throws the null pointer exception

  4. uday says:

    When i am trying to read the excel file from the above code it showing error at the following :
    Iterator rowIterator = sheet.iterator();
    //Get iterator to all cells of current row
    Iterator cellIterator = row.cellIterator();

    can u please help me to resolve it

  5. Anto says:

    i want to convert odp document to xls documnet please help

  6. [email protected] says:

    can i send the contents in the ppt to xls automatically by clicking a button

  7. Anto says:

    Hi Sir,
    Getting red line on the following words when tried create a new excel file and write data in it as given above…
    workbook, Row, Cell, sheet, Map, HashMap, Set, Date

  8. siddu says:

    how to apply conditions to the data while retrieving from excel. For suppose, I dont want total data of SALARY column. I need only the data who are earning salary morethan 10000/-

  9. patel says:

    i want to copy array contents to excel file how can i do that.

  10. Timy says:

    Please Check that below mentioned example does not work as it is expected to be .

    3. Create new Excel File : Below is the complete code that writes a new excel with dummy data: 
  11. Anto says:

    Hi,
    i want to extract the text data from ppt and store in excel…i used apache poi to extract text from PPt and it’s sucessfully working and im also able to store some data in excel using apachi poi.. but the couldnt make the connection between them that is donno whether ther’s a way to store the extracted data in excel using Apache POI.

    Please kindly help..

  12. Hayk says:

    Hi, there, please help me,. I am trying to merge excel files.
    I read above code.

    switch(cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.print(cell.getBooleanCellValue() + "\t\t");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t\t");
                        break;
    

    but my cell type is BLANK
    and I added a new switch case like this:

    case Cell.CELL_TYPE_BLANK:
    			                    System.out.print(cell.getStringCellValue() + "\t\t");
    			                    break;
    
     

    but it doesn’t display me value. how to get value of cell with the type Blank. ??????????
    I tryied to use cell.getRichStringValue(); but unfortunalely it doesn’t help me.
    please help. that is urgent. thanks in advance.

  13. Kyle says:

    Hey,
    Thanks for the tutorial but how would I go about using this to create a .xlsx from a .xls?

    • Satheesh says:

      hi Kyle,

      are u able to read the .xlsx file, if yes, please share the code. .

      Thanks in Advance,
      Satheesh

  14. bobby says:

    i am trying to do export to excel with embed files. But not happening.

    Can any one help the same

  15. Parin says:

    Thanks for this post Viral! Was looking for a quick example for this – thanks!

  16. saks says:

    Hi Viral Patel,
    The examples you have provided are very helpful.
    What are the import files required in a javascript to import/export data from/to excel from the javascript? I’m a tester using javascript in Selenium.
    Thanks in advance
    Saks

  17. Pushpa Kalubarme says:

    Hi Viral,
    I using your code to read the data of excel(xlsx) file . So only made change from HSSF to XSSF. But I am not able to read the data of excel file. And this code is giving error like “cannot be resolved to a type” for below lines of code
    1. Iterator rowIterator = sheet.iterator();
    2. Row row = rowIterator.next();
    3. Iterator cellIterator = row.cellIterator();
    4. Cell cell = cellIterator.next();
    5. case Cell.CELL_TYPE_BOOLEAN:
    6. case Cell.CELL_TYPE_NUMERIC:

    Please refer below code :

    try {
    
     FileInputStream fin = null;
    
    
      String file_path=request.getParameter("file_name");
       
        file_path=file_path.replaceAll(Matcher.quoteReplacement("\\"), "//");
        
        
        FileInputStream file1 = new FileInputStream(new File(file_path)); 
        
       
          
        //Get the workbook instance for XLS file  
        XSSFWorkbook workbook = new XSSFWorkbook(file1); 
     
        //Get first sheet from the workbook 
        XSSFSheet sheet = workbook.getSheetAt(0); 
          
           //Iterate through each rows from first sheet 
        Iterator rowIterator = sheet.iterator(); 
        while(rowIterator.hasNext()) { 
            Row row = rowIterator.next(); 
              
            //For each row, iterate through each columns 
            Iterator cellIterator = row.cellIterator(); 
            while(cellIterator.hasNext()) { 
                  
                Cell cell = cellIterator.next(); 
                  
                switch(cell.getCellType()) { 
                    case Cell.CELL_TYPE_BOOLEAN: 
                       out.print(cell.getBooleanCellValue() + "\t\t"); 
                        break; 
                    case Cell.CELL_TYPE_NUMERIC: 
                       out.print(cell.getNumericCellValue() + "\t\t"); 
                        break; 
                    case Cell.CELL_TYPE_STRING: 
                        print(cell.getStringCellValue() + "\t\t"); 
                        break; 
                } 
            } 
            
        } 
        file.close(); 
       // FileOutputStream out =  
           // new FileOutputStream(new File("C:\\test.xls")); 
       // workbook.write(out); 
      //  out.close(); 
    
    
          
    
    		} catch (Exception e){ out.println(e);}
    
    • Satheesh says:

      Hi Pushpa,

      Are u able to read the .xslx file? if yes please share the code.

      Thanks in advance,
      Satheesh

  18. Satheesh says:

    Hi All,

    is anyone have solution for .Xlsx file format??
    Im facing issues with this format, plz help me by sharing the code.

    Thanks in Advance,
    Satheesh

    • Gautham says:

      Hi Satheesh,

      I assume that you have downloaded the requirement from http://poi.apache.org/download.html.
      I guess you would have added only jar named : poi-3.9-20121203.jar which does not have XSSF package. Import even rest of the jars into your project, especially poi-ooxml-schemas-3.9-20121203.jar. Then the fix given by Prasad will work. Hope this was helpful.

      • Roberto says:

        I included poi-3.9-20121203-jar, poi-scratchpad…, poi-ooxml-schemas…,xmlbeans…, dom4j..and stax-api… and the code worked flawlessly.
        With this jar files it is possible to read xls and xlsx Excel files.
        Regards
        Roberto

  19. Pushpa says:

    hi vital,
    how to read more than one xml file at a time.

  20. prachi Joshi says:

    Hi I want to ask is i downloaded the library in zip format where should I extract it on m y computer

  21. Thyagu says:

    I am getting Null pointer Exception in the write excel…

    java.lang.NullPointerException
    	at ExcelWrite.main(ExcelWrite.java:32)
    

    can any one help me out ???

    try {
        FileInputStream file = new FileInputStream(new File("C:\\update.xls"));
     
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet sheet = workbook.getSheetAt(0);
        Cell cell = null;
     
        //Update the value of cell
        cell = sheet.getRow(1).getCell(2);
        cell.setCellValue(cell.getNumericCellValue() * 2);
        cell = sheet.getRow(2).getCell(2);
        cell.setCellValue(cell.getNumericCellValue() * 2);
        cell = sheet.getRow(3).getCell(2);
        cell.setCellValue(cell.getNumericCellValue() * 2);
         
        file.close();
         
        FileOutputStream outFile =new FileOutputStream(new File("C:\\update.xls"));
        workbook.write(outFile);
        outFile.close();
         
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    
  22. aakash chandwani says:

    i m not getting where to unzip this apache poi or how to use this poi. when i am using above code it is saying cannot recognize symbol HSSFWORKSHEET AND ALL.PLEASE HELP VIRAL I DESPARATELY NEED THIS.PLZ.. THANKS IN ADVANCE

  23. midhun says:

    viral i need to convert xls to xlsx using java how can i do that plz help

  24. Rishinder says:

    Hi Viral,

    I’m looking into more specific details about Apache POI. Do you have any idea of memory/heap usage. Sometimes we have thousands of records to read and write and I’ve read somewhere that it needs more memory to do so or it will crash!

    Thanks.

  25. balakrishna gajam says:

    Hi.. I am reading cells data dynamically.
    When I am reading date, numeric values, I do not find proper type checking for both date and numeric types. How to solve the problem.

  26. david drennan says:

    Hi Viral,
    Thanks for the tutorial. I want to read the results of a in a cell that is continually changing. The cells are actually addresses on plc. How do I get the results i.e. that which the user can see and not the formula?

  27. Jegar D. Apiag says:

    Please help me inserting a column to an excel file..Thanks in advance!

  28. rajeswari says:

    hi sir.. thanks for the tutorial…. iam reading excel file.. but i also want the data to update into database… please help me any one…

  29. Yogesh says:

    in xlsx file there is a date 10-10-2012 with dateformat. Hoew to read ths date format in java?

  30. Andy says:

    Hi Viral,

    I need a code for the following problem.
    I am working on a java application in which i fetch data from multiple tables in the database. The consolidated data is presented in the form of a table in which each row is having an individual checkbox in front. There is a checkbox at the top also. If user clicks on the topmost checkbox, then all rows get selected. Now i want to export the selected rows(which have data from multiple tables) to an existing excel. The excel has a sheet for each table in the database with columns mapped to the fields in table. The data of the corresponding table should get exported to the corresponding sheets. In addition, each sheet has some conditional formatting and if the data from the databse doesn’t satisfy that condition then that particular cell should turn red. for eg. a cell has a dropdown list of 3 values. Suppose the data fetched from the database is some other value then that particular cell should turn red.
    Please provide me code for that.

  31. Jayant Kumar says:

    Hi All,
    How to Sort data in alphabetical order based on 1 column in excel ?

  32. sunshine says:

    Hi Viral
    I wanted to read data from a cell of GeneralType. Can you please tell me how to do it?

  33. mula says:

    Hi All,
    I have been trying to edit an existing .xlsx file with XSSFWorkbook, getting stuck with this exception when it’s trying to open it.

    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(“existing.xlsx”)));

    It’s throwing the following exception:
    java.lang.NumberFormatException: For input string: “A$1]”

    Seems to be that the XSSFWorkbook is complaining about some kind of formatting, or formula from the existing file!?

    Any pointers would be greatly appreciated!

    Thanks!

  34. shri says:

    Hi,

    I have an excel sheet which has a Date column whose cell type is custom d-mmm-yy.
    When i read this excel using apache POI then this column gets read as a numeric cell and returns the big decimal value.

    Is there any way of identifying the cell type to be Date for this custom format?

  35. raj says:

    hi viral
    i paste your readexcel java code its not showing any errors but at the time running its showing the file not specified in your path but i placed excel file correct

  36. rand says:

    I am facing a problem while applying styles to the XWPFTable present in apache poi. I have drawn a table in a word file using the above class, but it need to autofit table to window… i.e i am making the table of increasing row to be fit to center of the word document page even when the table’s column increases.

  37. Razz says:

    how can i read empty cells or null values from excel

  38. Rahul Munjal says:

    I want to convert my excel sheet to html file using java…..
    can u help ????????????????????????
    or if u can give me a code in which we can convert my excel sheet into image…
    m waiting…………
    thnx in advance

  39. Sankar says:

    Hi Viral,

    I’m developing chrome extension to read and write excel from local machine.
    Is it possible to implement something similar to Apache POI in Java script.

    Thanks

  40. Arpit Thakkar says:

    Hi Viral,
    i have lots of data more than 65000 and i have to add all data in to excel sheet and when data will more than 65000 then crate new excel sheet and store data after 65000 in new sheet so what should i do? will u please help me what should i do?

  41. NewBrain says:

    Hi Viral
    i exactly do the same as u mention in Read Excel File to read the excel data
    but at the end when i compile it in eclipse console insteed of showing the excel data it show a message i.e Usage: AddDimensionedImage imageFile outputFile. i am not getting what was reasion behind this message please let me not where is problem. my code is —

    import java.io.*;
    import java.util.*;
    
    //import org.apache.poi.hssf.*;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.*;
    
    
    
    public class ExcelDataRead {
    
    	public static void main(){
    		
    		try{
    			FileInputStream file = new FileInputStream(new File("E:\\Emp.xls"));
    			
    			//Get the workbook instance for XLS file
    		    HSSFWorkbook workbook = new HSSFWorkbook(file);
    		 
    		    //Get first sheet from the workbook
    		    HSSFSheet sheet = workbook.getSheetAt(0);
    		     
    		    //Iterate through each rows from first sheet
    		    Iterator<Row> rowIterator = sheet.iterator();
    		    while(rowIterator.hasNext()) {
    		        Row row = rowIterator.next();
    		         
    		        //For each row, iterate through each columns
    		        Iterator<Cell> cellIterator = row.cellIterator();
    		        while(cellIterator.hasNext()) {
    		             
    		            Cell cell = cellIterator.next();
    		             
    		            switch(cell.getCellType()) {
    		                case Cell.CELL_TYPE_BOOLEAN:
    		                    System.out.print(cell.getBooleanCellValue() + "\t\t");
    		                    break;
    		                case Cell.CELL_TYPE_NUMERIC:
    		                    System.out.print(cell.getNumericCellValue() + "\t\t");
    		                    break;
    		                case Cell.CELL_TYPE_STRING:
    		                    System.out.print(cell.getStringCellValue() + "\t\t");
    		                    break;
    		            }
    		        }
    		        System.out.println("");
    		    }
    		    file.close();
    		    FileOutputStream out =
    		        new FileOutputStream(new File("C:\\test.xls"));
    		    workbook.write(out);
    		    out.close();
    		     
    		} catch (FileNotFoundException e) {
    		    e.printStackTrace();
    		} catch (IOException e) {
    		    e.printStackTrace();
    		}
    	}
    	
    }
       
  42. Ashik says:

    hi….
    Is there any jar files required to use worksheets in soapui.

  43. vivek says:

    Hi Patel,

    Thanks for sharing the code.
    Can you please tell me if there is any method to download and save the generated file by renaming it at any desired drive on the system.

  44. santosh says:

    hello all,
    anyone has idea on reading excel and storing them to hierarchical JAVA objects using XML based approach intead of POI.
    for example: i have excel which has different phone numbers along with other values in a row and each phone number should store as object and create a ArrayList of objects.
    sample data in excel file
    home_phone work_phone cell_phone occupation
    111-111-1111 444-444-4444 777-777-7777
    222-222-2222 555-555-5555 888-888-8888
    333-333-3333 666-666-6666 999-999-9999

    main Object:

    public class ReadObject
    {
       private String occupation;
       private List phoneNumberList = new ArrayList();
      ....
     setters/getters
    .....
    }
    
    public class PhoneNbrObj
    {
        private String number;
        private Type type;
        private String extension;
    
        public String getNumber() {
            return number;
        }
    
        public void setNumber(String number) {
            this.number = number;
        }
    
        public Type getType() {
            return type;
        }
    
        public void setType(Type type) {
            this.type = type;
        }
        public String getExtension() {
            return extension;
        }
    
        public void setExtension(String extension) {
            this.extension = extension;
        }
        public static enum Type {
            HOME_PHONE("Home Phone"), OFFICE_PHONE("Office Phone"), FAX_NUMBER(
                    "Fax Number"), CELL_PHONE("Cell Phone");
            private final String value;
    
            private Type(String value) {
                this.value = value;
            }
    
            public String xmlValue() {
                return value;
            }
    
            public static Type convert(String value) {
                for (Type inst : values()) {
                    if (inst.xmlValue().equals(value)) {
                        return inst;
                    }
                }
                return null;
            }
        }
    }
    
  45. Bert says:

    Hi!

    Everything works fine, but when extracting numbers, the result is rounded to one decimal only, ie 9.39 becoming 9.4.
    Is there any way to receive the numbers with all decimals?

    • Bert says:

      I found out that the formatting in an excel file can influence the results of POI’s stream. It is necessary to copy the data from one excel file into another, this time without any text-formatting, colored rows or the likes, just clear numbers, so Java can read the excel stream crystal clearly.

  46. sabyasachi says:

    Hi Viral,

    Thanks for gr8 effort

  47. class A says:
    class A{ public static void main String[]  ar{
    System.out.println("A');
    }}
  48. Jugal Thakkar says:

    Hello Viral,
    Nice Tutorial and Keep it up but You Haven’t Attached Source Code For This Tutorial

  49. Raimund says:

    Thanks for the nice examples how to use POI.

    But I suggest not to create a Workbook for an existing file per new. Instead you should use WorkbookFactory.

    The important difference is that this approach works with all supported file types – WorkbookFactory creates the appropriate Workbook-Object, either HFFSWorkbook or XSSFWorkbook.

    Also never use the “HFFS*”-classnames, just use the interface names (without the HFFS-Prefix).

    // Get the matching workbook instance for any supported spreadsheet format
    Workbook workbook = WorkbookFactory.create(file); 
    
    // Get first sheet from the workbook
    Sheet sheet = workbook.getSheetAt(0);
    

    Best regards,
    Raimund

  50. vivek says:

    Hi Viru,

    i am getting this error.Exception in thread “main” java.lang.ClassCastException: org.apache.poi.hssf.usermodel.HSSFRow cannot be cast to org.apache.poi.ss.usermodel.Row
    at com.infy.excel.Excelbook.main(Excelbook.java:37)
    Please help me

  51. Santhanam says:

    hi patel i just copied your Update Existing Excel File and run it in netbeans ide.when i run it shows the following error,
    Exception in thread “main” java.lang.IllegalArgumentException: Sheet index (0) is out of range (0..-1)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.validateSheetIndex(HSSFWorkbook.java:429)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetAt(HSSFWorkbook.java:825)
    at com.infomindz.io.ReadXlFile.updateExistingFile(ReadXlFile.java:277)
    at com.infomindz.io.ReadXlFile.main(ReadXlFile.java:308)

    and the code is

    String path = separator + "home" + separator + "santhanam" + separator+ "Contact list.xls";        FileInputStream inStream = new FileInputStream(path);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.getSheetAt(0);
            Cell cell = null;
    
            //going to update existing excel file
            cell = sheet.getRow(1).getCell(2);
            cell.setCellValue(cell.getNumericCellValue() * 2);
            
            cell = sheet.getRow(2).getCell(2);
            cell.setCellValue(cell.getNumericCellValue() * 2);
            
            cell = sheet.getRow(3).getCell(2);
            cell.setCellValue(cell.getNumericCellValue() * 2);
            
            inStream.close();
            
            FileOutputStream out = new FileOutputStream(path);
            workbook.write(out);
            System.out.println("Updating Excell file successful");       
    
    

    and also i checked xl file values.it is contain same value as what you gave.

    if i did anything wrong pls advice me.
    thanks in advance

    • rikin says:

      i have an error in netbeans 7.3…
      row variable doent have cell Iterator method..
      Iterator cellIterator = row.cellIterator();

  52. Abcd says:

    Sir ,where i add my downloaded poi in project .can u plz tell the complete structure of the project after adding the POI……Thanks

    • Amit Jain says:

      In eclipse u can download and add in build path
      like Go to Project > Properties > Java Build Path
      Click and browse external jar where it is saved

      If u have created a shared path for all external jars with environment variable M2_REPO then you can browse that path
      C:\Users\<>\.m2\repository\

  53. anand says:

    nice, u helped me. u explained in a better way.
    thank u.

  54. One should use TreeMap instead of HashMap. As there is no ordering, one can get the heading row not as the top row always.

  55. sree harsha says:

    excellent information.
    I will download this api now and work on it. I wasted lot of time searching online about other alternatives but this api is awesome and easy to use.
    thanks for sharing this tutorial with all of us..

  56. swati saxena says:

    Solve this error ::

    The supplied spreadsheet seems to be Excel 5.0/7.0 (BIFF5) format. POI only supports BIFF8 format (from Excel versions 97/2000/XP/2003)

  57. Osano Kevin says:

    Hi Patel, am in a serious problem, i have a folder receiving data in excel format every day in every second. i need to read these excel files and parse to MySQL database(Apache POI am using). My program needs to go back to the same data source to update only new data in those existing excel files. I also need to generate real time graphs from this data fro the database.
    Please help me.

  58. Amol Patil says:

    Hi Patel,
    It might be helpful if you can explain how to setup this library to some beginners :)
    let me help you here.

    You need to follow following steps to get started with above examples

    1. you need to download poi-2.5.1.jar(or in this case 3.8) jar file
    2. You need to include this file in your projects class path
    3. Create new java project in eclipse with auto generated main function
    4. just copy and paste above code or and press ctrl+Shift+o and your ready to go
    Hope this helps :)

    Amol Patil
    findnetinfo.com

    • Viral Patel says:

      @Amol: I am using Maven in this tutorial as specified in Step 1. That should add required dependencies. But thanks for your suggestion. I have added these steps to above tutorial so that users who do not wish to use Maven can run the examples without getting into classpath errors. Thanks :)

  59. Amol Patil says:

    and yes this is most helpful resource I found on internet about using these libraries thanks a ton..

    for my previous comment , I was a complete newbie in reading xls and I faced problems while setting up all of it thats why explained this stuff but I found most of your readers expert here . :D

  60. jarkema says:

    Your example: Read Excel File using Eclipse worked fine on windows 7 for me. The only difficulty that I’m having is getting the docs to be accessed by Eclipse. I get “Note: This element neither has attached source nor attached Javadoc and hence no Javadoc could be found”. I added the source docs in as part of the Referenced Libraries along with the class .jar file. I can access the docs separately just fine, but haven’t been able to use them in code assist.

  61. radha says:

    hi your examples are simply good…
    my requirement is i want to read data from one excel and i want to append it in another excel.
    can you please guide me.

  62. Ronnie says:

    Im getting the following error:

    org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)

    How do i resolve this?

    • Ronnie says:

      The file was in .xlsx format. Converted it to .xls and now its working fine. Thank you.

  63. Vaigha says:

    How to add an image(logo) into an excel file using poi api?

  64. prasad says:

    Hi, I am reading table from db and storing it in excel I have more than one million records in the table. I want to store the records 1 lakh each in different excel file tabs not in the single excel file. Please let me know the procedure to get it stored in the excel with best efficient way.

  65. Ronnie says:

    Hi,
    Doesn’t Apache POI read .xlsx files?

  66. Ronnie says:

    This is the error that im getting when i try to read an .xlsx file:

    org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)

  67. hitish says:

    In creating new excel file you must use treemap instead of hashmap because hashmap does not gurantee any order and elements are being printed randomly.

  68. Fahad Shafeeq says:

    I have created a Batch report job Successively using Apache POI. But now im stuck! I need to add a new sheet to my current workbook which I did by using HSSFSheet sheet1 = excel.createSheet("Parcel Return");However both sheets have different data for headers and columns how do I write that within my current java class?

    public String[] getColumnNames() {
    		String[] columnNames = {"ISC\nCode", "Total\nParcels", "Total\nParcel Hit\n Count",
    				"Filter Hit\n%", "Unanalyzed\nCount", "Unanalyzed\n%",
    				"Name\nMatch\nCount", "Name\nMatch\n%", "Pended\nCount",
    				"Pended\n%", "E 1 Sanction\nCountries\nCount", "E 1 Sanction\nCountries\n%", "Greater\nthat\n$2500\nCount", "Greater\nthat\n$2500\n%",
    				"YTD\nTotal Hit\nCount", "YTD\nLong Term\nPending", "YTD\nLong Term\n%"};
    		return columnNames;
    	} 
    

    This is my getColumnNames for first sheet

  69. Fahad says:

    Hi Viral, Can you show an example of workbook with multiple sheets both having different data i.e headers, footers and style?

  70. milan says:

    I am Getting error on iterator that it can not be resolved as type…..so please give a solution if u can

  71. Mano says:

    Hi viral,

    How to stored the excel values to a oracle database. Please give any suggestions or examples for that. Thanks in advance.

  72. milan says:

    Hi, Viral does it works for .xlsx file?????? please reply fast as u can its critically impotant
    thanx…… :)

  73. Naveen kumar says:

    I need to change the color of the font in the excel, kindly help me…

  74. Vishal says:

    Hi Viral,

    I am trying to read the value of a cell with the help of below code snippet but getting errors:

    case Cell.CELL_TYPE_FORMULA: 
    // System.out.print(cell.getNumericCellValue() + "\t\t"); 
    // System.out.println(cell.getStringCellValue());
    cell.setCellType(Cell.CELL_TYPE_STRING);
    String cellValue=cell.getStringCellValue();
    System.out.println(cell.getStringCellValue());
    

    Please help me to fetch the value of CELL_TYPE_FORMULA cellType.

  75. Rohit More says:

    Hello Viral Sir,
    problem is that if the cell contains integer no like 1
    on the time of reading cell value it is displaying 1.0 , i mean Floating value.
    What to do now if i want to get original data.

  76. ssp says:

    Instead of hard-coding the path of the excel to be saved, I want to ask for the OPEN & SAVE option…
    The following code gives me these options, but the data isn’t been saved in this excel:

    response.setContentType("application/vnd.ms-excel");
    			response.setHeader("Content-Disposition", "inline; filename="
    					+ "excel.xls"); 
  77. Nidhi Sharma says:

    how to print the output in XML after reading it through XLS using apache poi

  78. sandy says:

    can any body help me in converting the following xml to xls

    <![CDATA[This procedure must be done once a week, with this safety device disabled:
    X45HHYY89-000-JI]]>
    
    <![CDATA[This procedure must be done once a week, with this safety device disabled tr:
    X45HHYY89-000-JI]]>
    
    

    i am unable to get the name of testsuite and testcase tags.

  79. abhishek says:

    very nice article but I have one problem

    I am trying to read the .xlsx file using the XSSF but when i execute my program
    the program excution stuck on line

    XSSFWorkbook workbook = new XSSFWorkbook(file);

    where,
    file is InputStream Object

    Please help me

    Thanks in advance

  80. ssp says:

    Hi Viral,

    I have to export data to excel from a List which will be dynamically generated on every execution and get saved in
    List expColName = new ArrayList(); ,
    from a List , I have to obtain individual values into every column,

    The following code gives me the last column value only

    for(int i=0; i<expColName.size(); i++){
    			 data.put("1",new Object[] {					
    					 expColName.get(i)					
    			 });
    		}
    
    
  81. pretty says:

    Hi,
    I want to add a sheet to existing excel file. How can I do that? I am working on one selenium project and I want to add all of my automation result sheets into single excel file. Right now I am able to create new excel file for each sheet.

  82. Aanchal says:

    hi,
    i have created a password generator. I want to add the name, password and date respectively in a spreadsheet and save it. How can i add text from labels and textfields into the spreadsheet.
    plzz help

  83. NASREEN HUSSAIN says:

    Can Any one help me to read the contents from multiple excel sheets and store the contents in bean
    Here is my sample code

    public class UtilExcel {
     
     
                    private List readExcelFile(String fileName) throws Exception
                    {
                                    List cellDataList = new ArrayList();
                                    FileInputStream fileInputStream = new FileInputStream(fileName);
                                    System.out.println("File Name is "+fileName);
                                    POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
                                    HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
                                    
                                    List inputDataList=new ArrayList();
                                   // List inputDataList1=new ArrayList();
    
                                    HSSFSheet hssfSheet = workBook.getSheetAt(0);
                                    Iterator rowIterator = hssfSheet.rowIterator();                                
                                    while (rowIterator.hasNext())
                                    {
                                                    ExcelBean excelbean = new ExcelBean();
                                                    BeanExcel beanexcel = new BeanExcel();
                                                    HSSFRow hssfRow = (HSSFRow) rowIterator.next();
                                                    Iterator iterator = hssfRow.cellIterator();                                                             
                                                    while (iterator.hasNext())
                                                    {    
                                                    	if((hssfSheet.getSheetName()).compareTo("EIA Data") == 0)
                                                    	{
                                                        HSSFCell hssfCell = (HSSFCell) iterator.next();                                                                    
                                                        excelbean.setClusterName(hssfCell.toString());
                                                        
                                                        hssfCell = (HSSFCell) iterator.next(); 
                                                        excelbean.setItemNumber(hssfCell.toString()); 
                                                        
                                                        hssfCell = (HSSFCell) iterator.next(); 
                                                        excelbean.setItemDescription(hssfCell.toString());
                                                        
                                                        hssfCell = (HSSFCell) iterator.next(); 
                                                        excelbean.setVBUNumber(hssfCell.toString());
                                                        
                                                        hssfCell = (HSSFCell) iterator.next(); 
                                                        excelbean.setVBUNumberDescription(hssfCell.toString());
                                                        
                                                        hssfCell = (HSSFCell) iterator.next(); 
                                                        excelbean.setSalesUnits(hssfCell.toString());
                                                        
                                                        hssfCell = (HSSFCell) iterator.next(); 
                                                        excelbean.setStockedStores(hssfCell.toString());
                                                        
                                                        hssfCell = (HSSFCell) iterator.next(); 
                                                        excelbean.setCoreOptDelete(hssfCell.toString());
                                                    	}
                                                    }
                                                    inputDataList.add(excelbean);
                                                    
                                                    }
                                   
                                    System.out.println(" input Data size of EIA Data"+inputDataList.size());
                                 
                                    return inputDataList;
     
                    } 

    i created one bean for each sheet.
    Please help me out with the storing multiple sheet content to bean.

  84. Ehsan says:

    Just wanted to thank you so much for providing this tutorial. Much appreciated.

  85. Amit says:

    i want upload two excel file and compare their certain column……………………plz help me

  86. shaik says:

    i want upload two excel file and compare their certain column……………………plz help me

  87. pankaj says:

    Good article. Helped me instantly when i was stuck. thanks :)

  88. saran says:

    How to read xlsx (or) xls cell dropdown(list) value ?

  89. Arunbabu Kongara says:

    Hi

    Can any one tel me how to retrieve data from excel with out using any API. is any drivers are available.

    if possible please share Example code

    Thanks in advance

    Thanks and regards
    Arun

  90. Saran kumar says:

    Since I am new to Apache POI, screen shoots may root me to finish this task Successfully..! Thanks in advance

  91. ANSON VATTOLY says:

    Thank u so much…….:)

  92. arpita says:

    I am not able to read the excel on mac environment. It is giving null pointer exception
    java.lang.NullPointerException
    at RFLiveData2.cellToString(RFLiveData2.java:181)
    at RFLiveData2.readXL(RFLiveData2.java:151)
    at RFLiveData2.setUp(RFLiveData2.java:52)

  93. Karthik says:

    Hi Viral,I have an output stream of .xls file having multiple sheets (multiple sheets will be created since per sheet max rows 65530).As soon as new sheet is created , i am trying to write in temp file i.e .xls by creating a new workbook instance. But it gives an error like out of memory error.Any suggestions

  94. Paul says:

    Hi Viral, I need to split a single excel in to multiple excels based on unique values in a single column.Can you just help me to do that .

  95. Mahesh says:

    Patel,

    Can you please let me know how do i print the Test results in excel file with Test Scenario number and Pass/Fail. where i am not taking any input from Excel file. But i want to create the excel file and print the results to it for my selenium project

  96. Elaya says:

    Paul,

    public void createSheet(Cell cell){
      List<String> arrStr=new ArrayList<String>();
      ++colCount;
      if(colCount==1)
      {
    	matchFound=false;
        str=cell.getStringCellValue();  
    	for (int i = 0; i < arrStr.size(); i++) {
    		if(arrStr.get(i).equals(str))
    		{
    			matchFound=true;
    		}
    		if(matchFound)
    			break;
    	}	
    	if(!matchFound){
    		File f1=new File("/test/test"+fileCount+".xls");
    		//createSheet();
    		//getsheet and set the values
    	}
    	
      }
      }
    
  97. Suvi says:

    hiiii viral,
    I have download apache-poi-src-3.9-2012203.tar.gz . then copied ur code & compile bt i got errors that Package org.apache.poi.hssf.usermodel does not exist. Import org.apache.poi.hssf.usermodel.HSSFCell.

    I need ur help.

    thanks.

  98. NN says:

    Hi,

    I am using poi in android. I have following code
    File file = new File(m.getInstrumentation().getContext().getExternalFilesDir(null), “abc.xlsx”);
    FileInputStream fis = new FileInputStream(file);
    file.exists(); // returning true
    try{
    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    }Catch(){
    }
    I am facing java.lang.NoClassDefFoundError: org.apache.poi.xssf.usermodel.XSSFWorkbook during runtime. Could you please help me out. I have included following jar poi-scratchpad-3.9-20121203, poi-ooxml-schemas-3.9-20121203, poi-ooxml-3.9-20121203, poi-ooxml-3.9, poi-excelant-3.9-20121203, poi-examples-3.9-20121203, poi-3.9-20121203,dom4j-1.1, xmlbeans-2.3.0.

  99. NN says:

    Hi,

    I am using poi in android. I have following code

    File file = new File(m.getInstrumentation().getContext().getExternalFilesDir(null), "abc.xlsx");
    			FileInputStream fis = new FileInputStream(file);
    			file.exists();   // returning true
    			try{
    			XSSFWorkbook    workbook = new XSSFWorkbook(fis);
                             }Catch(){
                             }		
    

    I am facing java.lang.NoClassDefFoundError: org.apache.poi.xssf.usermodel.XSSFWorkbook during runtime. Could you please help me out. I have included following jar poi-scratchpad-3.9-20121203, poi-ooxml-schemas-3.9-20121203, poi-ooxml-3.9-20121203, poi-ooxml-3.9, poi-excelant-3.9-20121203, poi-examples-3.9-20121203, poi-3.9-20121203,dom4j-1.1, xmlbeans-2.3.0.

  100. Amit says:

    Hi,

    I am using poi in android. I have following code

    File file = new File(m.getInstrumentation().getContext().getExternalFilesDir(null), "abc.xlsx");
    			FileInputStream fis = new FileInputStream(file);
    			file.exists();   // returning true
    			try{
    			XSSFWorkbook    workbook = new XSSFWorkbook(fis);
                             }Catch(){
                             }			
    

    I am facing java.lang.NoClassDefFoundError: org.apache.poi.xssf.usermodel.XSSFWorkbook during runtime. Could you please help me out. I have included following jar poi-scratchpad-3.9-20121203, poi-ooxml-schemas-3.9-20121203, poi-ooxml-3.9-20121203, poi-ooxml-3.9, poi-excelant-3.9-20121203, poi-examples-3.9-20121203, poi-3.9-20121203

    Thanks.

  101. Revathy says:

    Hello :) I wanted to know how to compare 2 excel sheets??? pls help me ..ASAP !!!

  102. imrsn says:

    Nice article, just one thing. Use LinkedHashMap instead of HashMap. Otherwise, when creating an excel file, users will get random rows. LinkedHashMap will take care of that.

    • Setu says:

      Thank you!

  103. vinh says:

    very good! thanks u!!

  104. Onu says:

    Hi Patel
    Thanks for a wonderful article, quick question – how I can ignore first row & print everything (since first row is the column title

  105. Yaakov says:

    Really awesome! Exactly what I needed and very well explained. Just the basic information that I need to use the API :)

  106. Patrick Meppe says:

    This is a very helpful tutorial.
    Just one thing tho, you should mention where you get the “Cell” and “Row” objects from.

    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    //...
    
  107. Sujith Sundaran says:

    Hey I have to create a list which will be dynamically read from the Java side and select the one item in the list. Can any one please tell me how can we do that ?

  108. Justin Grant says:

    Also, as a little note

    Great stuff – Thanks!

    As a little note to others, if you would like to use the xml-based stuff, such as xlsx, you need to adjust your maven coordinates as follows:

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.8</version>
    </dependency>
    
  109. 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!

    • DeenuBabu says:

      Hi Sunil kumar by using java2word we can create word documents. I have worked with this and it is working fine. Visit the following URLyou will get information and example to how to do it.

      “http://java2word.blogspot.in/”

    • DeenuBabu says:

      By using itextpdf jar we can create pdf file dynamically. this jar file is open source. if u need i will share u the code to generate pdf file which i wrote to generate pdf file.

  110. Praveen says:

    hi,
    your tutorial is nice and very useful.
    i want to read Excel data and send that data to database as POJO .
    can u please help me.
    thank you.

  111. Jil says:

    Hi,
    I did everything the tutorial said, but I am getting the following errors:

    Row cannot be resolved to a type
    Iterator cannot be resolved to a type
    Cell cannot be resolved to a type
    File cannot be resolved to a type

    How can I fix this?

    • Priyanka says:

      I think, you need to import the proper classes. And before that add poi jar to your project.

  112. Rohit says:

    Hi,
    A good tutorial to start Apache POI.
    I have a specifiv use case, I have to read an image from excel (.xlsx) which is at particular cell. Please help to read image from a cell, as currently the solution i have is to read all the pictures from a workbook.

    Thanks in advance.!!!

  113. doki says:

    Hi,

    please send me the code

  114. Kalaiyappan V says:

    Hi Thanks for the tutorial, would like to know if there are possibilties to find out the version of Excel Sheet we are working with these classes?

    I would like to whether the Excel is MSExcel is 2003, 2007, 2010, xp, 97 formats? how is it possible, it would be great if we could get this through code.

  115. taruna says:

    i need help…
    i am getting error on line* Iterator cellIterator = row.cellIterator();* showing there is no method like *cellIterator()*

  116. SANDOKAN says:

    Hi
    I am trying to make an app to read my excels..but every time I found an error and formulas don’t update.
    Is it possible problem is update poi?
    Can you upload full example with all folders and librarys?

  117. nelson says:

    Regards to things:

    1) whit the iterator, Netbeans obligates me to define it this
    Iterator iteradorDeFila = sheet.iterator();

    2)RowIterator method: celliTerator(); is note recognized: “cannot find symbol”

    • nelson says:

      Solved. Nice Tutorial. Thanks.

  118. Chandu says:

    When I run the above codes I am getting an runtime exception as follows
    Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException
    at jdbcwithexcel.GetData.main(GetData.java:25)
    Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    … 1 more

  119. aamir says:

    This article really worth to me.
    I learned the things in a simply manner.
    The way they are placed sequentially is
    So good.
    Thanks for helping the newcomers.

  120. Supriyo says:

    Very nice blog. very efective.

  121. Balaji says:

    Hi,

    Nice Tut.. Is this possible to do “Keyword Contain Search” action by this..???

  122. Vinh says:

    Thanks you!

  123. Umasri says:

    Hi,
    Very good information.But i would like to know how to work with multiple sheets in a Workbook.
    For this again we have to write the total code.I am unable to work with multiple sheets.I am able to read the data by copying the same code and changing the sheet number.But it is showing duplicate code(More lines of code). Can you please suggest me how to do?

    //Get first sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(0);
     
    //Get iterator to all the rows in current sheet
    Iterator<Row> rowIterator = sheet.iterator();
     
    //Get iterator to all cells of current row
    Iterator<Cell> cellIterator = row.cellIterator();
  124. JOSE says:

    I see this order:
    2 Sam 800000
    1 John 1500000
    Emp No. Name Salary
    3 Dean 700000

    and you:

    Emp No. Name Salary
    1 John 1500000
    2 Sam 800000
    3 Dean 700000

    Why?

    • Josser Ramirez says:

      Resolve this with.
      after
      Object [] objArr = data.get(key);
      before
      Object [] objArr = data.get(String.valueOf(rownum));

  125. siva says:

    Hi all,,

    Can any one tell the code for importing excel file with .xlsm extension and with implementing formulas in it. please urgent for me….

  126. Soniya says:

    Hey ,

    Please Help me . I need code for read data from one excel sheet and append this data on other excel Sheet

  127. kirtana says:

    copy pasted the same code.but when compiling in eclipse it shows message “Usage: AddDimensionedImage imageFile outputFile”.plz help

  128. Mayank says:

    hello viral
    I have a problem in printing excel sheet means i have some data in database on server and i have to print that data in excel sheet on client side. And also there should be a pop up window come first in which user can give the path to save that excel sheet and also can give a name whatever they want at time of downloading data in the form of excel sheet, can u help me please

  129. suv says:

    How to read cell value in excel using cell number as a key in JAVA

  130. Balendu says:

    I want to write a javacode where i read an XML from Excel sheet and i want to get response using httpclient and want to print the response in the second sheet. Please help me on this.

  131. Dipeen says:

    Hi
    How can I sort an excel sheet by a column using apache POI ?

  132. Sandy says:

    Hi Viral,

    How to set a password for xls file. ? Without password it should not even allow to read the file

    Thanks in advance

  133. sandeep says:

    Hi Viral,
    How to navigate to a different worksheet in the same workbook . By just clicking on a hyperlink of a cell it should happen. Could you please help me out..

    Thanks in Advance:
    Sandeep

  134. Anil Sanwal says:

    Hi Viral,
    I want to take the default value from the cell without applying any formula.
    So,Can you please tell me how I can ignore the default formulas present in excel sheet.
    Please help me.

  135. KC says:

    wow…. your knowledge sharing is awesome… it was help me to understand a lot about POI…, thank you so much

  136. Div says:

    Thanks for sharing the information. I have a question here. Can you please tell me how to open the newly created excel file and write results to that excel file. I want to view the excel while writing. This is my requirement. and also when ever i run the script new excel will be created.

  137. Manjunath says:

    Hi,Thanks for the tutorial.
    Kindly tell me how to check for not null fields while importing data from an excel into a table ?.
    I mean to say,how should we check if user misses out on entering primary key value while entering data ?

  138. KYo says:

    Impressive

  139. Ganesh says:

    Hello Guys,,

    Need help in fetching date values from excel sheet from particaula cell which are in this format MMDDYYYY

    thanks in advance.

  140. Shivany says:

    Hey Viral am tring to read data from my excel sheet which has been created by java code and even the data has been feeded.M not able to read data + make changes in the alignment and styling.How do I do that.Please Help out…..

    import java.io.File;
    import java.io.FileInputStream;
    import java.util.Iterator;
    
    import org.apache.poi.hssf.record.formula.functions.Cell;
    import org.apache.poi.hssf.record.formula.functions.Row;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    public class HssfRead
    {
    	    @SuppressWarnings("unchecked")
    		public static void main(String[] args) 
    	    {
    	        try
    	        {
    	            FileInputStream file = new FileInputStream(new File("C:\\virclipse\\random.xls"));
    	 
    	            //Create Workbook instance holding reference to .xlsx file
    	            HSSFWorkbook workbook = new HSSFWorkbook(file);
    	 
    	            //Get first/desired sheet from the workbook
    	            HSSFSheet sheet = workbook.getSheetAt(0);
    	 
    	            //Iterate through each rows one by one
    	            Iterator<Row> rowIterator = sheet.rowIterator();
    	            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();
    	        }
    	    }
    	}
    
    • rafael says:

      i m trying to read a excel like this example and put each column in a table using fileupload primefaces and having a problem with a name of the file :

      @Named
      @RequestScoped
      public class FileBean implements Serializable {
      
      	private static final long serialVersionUID = 1L;
      
      	private String diretorioDestino = "C:\\Users\\Rafael.Moreira\\Desktop\\Confiabilidade\\";
      	private String tipo;
      	private String nome;
      
      	private Item item = new Item();
      
      	@Inject
      	private Items items;
      
      	
      
      	public void handleFileUpload(FileUploadEvent event) throws FileNotFoundException {
      
      		UploadedFile uploadedFile = event.getFile();
      		String fileNameUploaded = uploadedFile.getFileName().substring(
      				uploadedFile.getFileName().lastIndexOf("\\") + 1);;
      
      		FacesContext facesContext = FacesContext.getCurrentInstance();
      		facesContext.addMessage(null, new FacesMessage("Arquivo : " +fileNameUploaded + " salvo com sucesso!",
      				null	));
      		
      System.out.println(fileNameUploaded );
      System.out.println(event.getFile().getFileName());
      		// Do what you want with the file
      
      		try {
      
      			FileInputStream file = new FileInputStream(new File(fileNameUploaded));
      							
      			// Get the workbook instance for XLS file
      			HSSFWorkbook workbook = new HSSFWorkbook(file);
      
      			// Get first sheet from the workbook
      			HSSFSheet sheet = workbook.getSheetAt(0);
      
      			// Iterate through each rows from first sheet
      			Iterator<Row> rowIterator = sheet.iterator();
      			while (rowIterator.hasNext()) {
      				Row row = rowIterator.next();
      
      				// For each row, iterate through each columns
      				Iterator<Cell> cellIterator = row.cellIterator();
      				while (cellIterator.hasNext()) {
      
      					Cell cell = cellIterator.next();
      
      					switch (cell.getCellType()) {
      					case Cell.CELL_TYPE_BOOLEAN:
      						System.out.print(cell.getBooleanCellValue() + "\t\t");
      						break;
      					case Cell.CELL_TYPE_NUMERIC:
      						System.out.print(cell.getNumericCellValue() + "\t\t");
      						break;
      					case Cell.CELL_TYPE_STRING:
      						System.out.print(cell.getStringCellValue() + "\t\t");
      						item.setCodigoItem(cell.getStringCellValue());
      						break;
      					}
      				}
      				System.out.println("");
      			}
      			items.guardar(item);
      
      		} catch (FileNotFoundException e) {
      			e.printStackTrace();
      		} catch (IOException e) {
      			e.printStackTrace();
      		}
      
      	}
      
      

      The following message:

      nformações: Reloading Context with name [/Confiabilidade] is completed
      ConfiabilidadeLista_usuario_B_SM.xls
      ConfiabilidadeLista_usuario_B_SM.xls
      java.io.FileNotFoundException: ConfiabilidadeLista_usuario_B_SM.xls (O sistema não pode encontrar o arquivo especificado)
      	at java.io.FileInputStream.open(Native Method)
      	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
      
  141. NGowda says:

    i post the my code ,, please can you help me, how to print the get text data into 2 nd column of existing excel sheet..

    here my code

    package shamrock;
    
    import org.testng.annotations.Test;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    //import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.Iterator;
    import java.util.concurrent.TimeUnit;
    
    //import jxl.Workbook;
    
    import org.apache.commons.io.FileUtils;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    //import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.openqa.selenium.By;
    import org.openqa.selenium.OutputType;
    import org.openqa.selenium.TakesScreenshot;
    import org.openqa.selenium.WebDriver;
    import org.openqa.selenium.firefox.FirefoxDriver;
    
    @Test
    public class Searchengine {
    
    
    	public void run() throws IOException {
    		FileInputStream file = null;
    		FileOutputStream fos=null;
    		HSSFWorkbook workbook;
    		try {
    			
    			
    			
    			file = new FileInputStream(new File("D:\\test.xls"));
    			ArrayList partNumberList = new ArrayList();
    			ArrayList result = new ArrayList();
    			workbook = new HSSFWorkbook(file);
    			HSSFSheet sheet = workbook.getSheetAt(0);
    			Iterator rowIterator = sheet.iterator();
    			while(rowIterator.hasNext()) {
    				Row row = rowIterator.next();
    				Iterator cellIterator = row.cellIterator();
    				while(cellIterator.hasNext()) {
    
    					Cell cell = cellIterator.next();
    					System.out.println(cell.getStringCellValue());
    					String partNum = cell.getStringCellValue()+"";
    					partNumberList.add(partNum);
    
    				}
    				System.out.println("");
    			}
    			WebDriver s1 = new FirefoxDriver();
    			s1.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
    			s1.get("http://www.shamrocksupply.com/userHome.action");
    			s1.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
    			s1.manage().window().maximize();
    			if(partNumberList != null){
    				fos=new FileOutputStream ("D:\\test1.xls");
    				HSSFWorkbook workBook = new HSSFWorkbook();
    				HSSFSheet spreadSheet = workBook.createSheet("sheet1");
    				HSSFRow row;
    				HSSFCell cell;
    				for (int i = 0; i&lt;partNumberList.size(); i++ ){
    					s1.findElement(By.id(&quot;txtSearch&quot;)).sendKeys(partNumberList.get(i));
    					s1.findElement(By.id(&quot;performSearchBtn&quot;)).click();
    					//Thread.sleep(1000);
    					String a = s1.findElement(By.xpath(&quot;//h2[@style=&#039;margin-left:2px; &#039;]&quot;)).getText();
    					System.out.println(a) ;
    					result.add(a);
    					File scrFile4 = ((TakesScreenshot)s1).getScreenshotAs(OutputType.FILE);
    					FileUtils.copyFile(scrFile4, new File(&quot;D:\\tmp\\image_&quot;+new Date().getTime()+&quot; .png&quot;));
    					s1.findElement(By.xpath(&quot;//div/a[@href=&#039;/userHome.action&#039;]&quot;)).click();
    					
    					
    					HSSFSheet sheet1 = workbook.getSheetAt(0);
    
    				    Cell cell1 = null;
    
    
    				    //Update the value of cell
                      cell1 = sheet1.getRow(1).getCell(2);
    				   cell1.setCellValue(a);
    
    
    				    cell1 = sheet1.getRow(2).getCell(2);
    
    				    cell1.setCellValue(a);
    
    				    cell1 = sheet1.getRow(3).getCell(2);
    
    				    cell1.setCellValue(a);
    
    				     
    
    				    file.close();
    
    				     
    
    				  
    				    FileOutputStream outFile =new FileOutputStream(new File(&quot;D:\\test1.xls&quot;));  	
    				}
    				
    
    				    workbook.write(fos);
    
    				 
    				
    			}		
    			
    			
    			System.out.println(&quot;Done&quot;);
    			
    			partNumberList.clear();
    			System.out.println(&quot;ReadIng From Excel Sheet&quot;);
    
    
    		} catch (FileNotFoundException e) {
    			e.printStackTrace();
    		} catch (IOException e) {
    			e.printStackTrace();
    		}finally{
    			try{
    				if(file != null){
    					file.close();
    				}else if(fos!=null){
    					fos.close();
    				}
    			}catch (Exception e) {
    				e.printStackTrace();
    			}
    		}
    	}
    
    
    }
    
    • Narendar says:

      Hi NGowda,

      I will try to help you send me sample files. how test.xls and test1.xls should look like. Explain me the problem clearly.

      Thanks,
      Narendar.g

  142. kevin flynn says:

    How do you update a powerpoint with an embedded excel sheet? I can open the ppt, get the xls, update it, but I cannot get it to save back to the ppt. I cannot find this answer anywhere.

  143. Gangadhar says:

    How to read the excel file using java only with out using any APIs

  144. Pavana says:

    How to extract data from excel using java and how to write code for the same using netbeans.

  145. Richard PSilva says:

    This info is excelent,…

    From this article, I have the complete code that writes a new excel with dummy data,.. what I need to do in order to work with the same stuff within a JSP web Page ? I need to have a link somewhere which points to the JSP that generates the excel file,… so the excel file will be generated on the fly, what I need to do ? I need a popUp message saying if you want to save or open the excel file, I do not need and I do not want to have a local path like C:

    Could you please help me?
    Thanks in advance.

    • Kumar says:

      Hi Richard,

      Please share the solution if you get it.

  146. Tester says:

    Hi,

    I would like to append data in existing .xlsx file. Can anyone show me how should I achieve it.
    Actually I have created an .xlsx file now I want to append the data on the same.

    • Tester says:

      Here is my code

      package com.filestuffs;
      
      import java.io.File;
      import java.io.FileInputStream;
      import java.io.FileNotFoundException;
      import java.io.FileOutputStream;
      import java.io.IOException;
      import java.io.InputStream;
      import java.util.Set;
      import java.util.TreeMap;
      
      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;
      
      public class AppendDataToExcelFile {
      	
      	public static void writeToExcelFile() {
      		//Blank workbook
      		XSSFWorkbook workbook = new XSSFWorkbook();
      		//Create a blank sheet
      		XSSFSheet sheet = workbook.createSheet("Sample sheet");
      
      		//This data needs to be written (Object[])
              TreeMap<String, Object[]> data = new TreeMap<String, Object[]>();
              
              int rownum = 0;
              Set<String> keyset;
              FileOutputStream out = null;
      		
              try {
      			if (!(new File("D:\\BackLinksSiteAudit2013\\new_testoutput_3.xlsx")).exists()) {
      				data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});			
      				data.put("2", new Object[] {1, "Amit", "Singh"});
      				keyset = data.keySet();
      		        for (String key : keyset)
      		        {
      		        	//System.out.println("Key - "+key);
      		            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);
      		            }
      		        }
      				// Write the workbook in file system
      				out = new FileOutputStream(new File("D:\\BackLinksSiteAudit2013\\new_testoutput_3.xlsx"), true);
      				workbook.write(out);
      				//out.close();
      			}
      			else
      			{
      				InputStream inp = new FileInputStream("D:\\BackLinksSiteAudit2013\\new_testoutput_3.xlsx");
      				data.put("3", new Object[] {2, "Tanuj", "Kumar"});
      				keyset = data.keySet();
      				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);
      		            }
      		        }
      				//out = new FileOutputStream(new File("D:\\BackLinksSiteAudit2013\\new_testoutput_3.xlsx"));
      				workbook.write(out);
      				inp.close();
      			}
      			
      			System.out.println("Excel written successfully..");
      
      		} catch (FileNotFoundException e) {
      			e.printStackTrace();
      		} catch (IOException e1) {
      			e1.printStackTrace();
      		} 
      	}
      	
      	public static void main(String [] args)
      	{
      		AppendDataToExcelFile.writeToExcelFile();
      	}
      }
      
  147. Tukaram Bhosale says:

    Hello Viral,

    When I tried this code for reading XLSX with more than one column then this code is not working.

    Could you please help me to resolve this?

    regards,

  148. venkat says:

    I want to insert a checkbox in excel using the poi jar. Is it possible?? Anyone one tell..how to solve this problem.?

  149. Sachin says:

    Hi Viral,
    Thank you very much for the very well explanation on Apache POI. got to learn so many basic things on excel are.
    I am executing the code what you have provide above(Reading excel), but getting below error. Tried a lot to fix the issue, but couldn’t find it my self. Can you please help me in fixing this issue.

    “The type HTMLDocument.Iterator is not generic; it cannot be parameterized with
    arguments ”
    “The type HTMLDocument.Iterator is not generic; it cannot be parameterized with arguments ”

    The error observed in the below two lines:

    Iterator<Row> rowIterator = sheet.iterator();   
    Iterator<Cell> cellIterator = row.cellIterator(); 
  150. Subrata Maiti says:

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

    • Shivani says:

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

  151. arpit says:

    Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/juli/logging/LogFactory
    at org.apache.catalina.util.LifecycleBase.(LifecycleBase.java:37)
    Caused by: java.lang.ClassNotFoundException: org.apache.juli.logging.LogFactory
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)

    This is the error i am getting

    • thatskriptkid says:

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

  152. Vinesh says:

    Good explanation.. helped a lot.
    THanks

  153. Jess A says:

    Thanks for this – very helpful.

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

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

    Thanks!

  154. dev says:

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

  155. Divya says:

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

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

    Please provide me some information for this requirement.

  156. Reetesh says:

    Thanks for explanation, it solved one of my problem.

  157. betty says:

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

    • Raghavendra Samant says:

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

  158. Divya says:

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

    Please give me the solution for this issue.

  159. Justin says:

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

    cell.getStringCellValue().matches(regex) 

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

  160. mohankumar says:

    thank u so much…

  161. Kumar says:

    Hi Patel,

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

    Thanks

  162. Jags says:
    Hi Patel,
    I’m getting the column name and corresponding values dynamically. I don’t want to hard code both column name and values.
    Below method will get the list of users which will have column names and values.
    List mystring = getIndiviualRecordsList(getLdapDirContext());
    I’m wondering how to use it here. Please share your thoughts.
    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”, “Kumar”});
    data.put(“3″, new Object[] {2, “Lokesh”, “Gupta”});
    data.put(“4″, new Object[] {3, “John”, “Adwards”});
    data.put(“5″, new Object[] {4, “Brian”, “Lenny”}); 
  163. chandra says:

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

    • inl1ner says:

      Hi chandra,
      maybe this helps:

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

      regards
      inl1ner

  164. Sagar says:

    Problem statement:

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

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

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

    Thanks & regards

  165. Gulamjelani Qureshi says:

    what about row type in iterator

  166. amitabh says:

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

  167. Sachin says:

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

  168. Sagar says:

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

  169. Candis says:

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

  170. Kirankumar Oggu says:

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

  171. Jitendra Arethiya says:

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

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

  172. Anjana says:

    Hi,

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

    Thanks,
    Anjana

  173. Leo says:

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

    Thank you for your answers.

  174. prasanthi says:

    Hi

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

  175. Sundar says:

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

  176. raj says:

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

  177. Srikanth says:

    Nice info,

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

  178. Aj says:

    Thanks for this article! It was useful.

  179. shweta says:

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

  180. satya says:

    Please update me.

    set Password Protected Excel Sheets Using java

  181. Gundu says:

    Hi Viru,
    I want to only read to a MS word document file in android.
    that word files choose from external storage from device.
    If reading file with curl effect like a book that will very good.
    Thank You.

  182. Eduard says:

    Hello!
    Thanx a lot for this: “One thing we need to note here is that we can update the excel file only when we close it first”!!!
    You’ve saved my holiday!!!

  183. Handsome says:

    Hello,

    How can I find user selected cells or active cells?

    Thank u

  184. priya says:

    I want to fetch the data from data base and get into the excel sheet for that what I have to do??…
    please give me solution . thanks in advance.

  185. Swapnali says:

    Hi Viral,

    Could you please help how to find row count ofsheet?

    Thanks

  186. Sanjay says:

    Could tell me how show pptx file in android using apachi poi…

  187. Ma says:

    I want to write the output of my coded algorithm into an excel?
    How can do it?

  188. faz says:

    someone help me to solve this problem.
    i want to read data from excel.. but the problem is my coded can read excel 2003 only.
    i want it read all excel version.

    if(filepath != null){
                    
                    attachment = filepath.getFileName();
                    mid = attachment.lastIndexOf(".");
                    filesize = filepath.getSize();
                    extension = attachment.substring(mid + 1, attachment.length());
                    filename = attachment+"."+extension;
                    
                    log.info("Filename : " + filename + " ["+filesize+"B]");
    
                    if (filesize != 0){
                        
                        File attachmentFile = new File(filename);
                        InputStream inputStream = filepath.getInputStream();
                        OutputStream outpuStream = new FileOutputStream(attachmentFile);
                        
                        while((len=inputStream.read(buf))>0)
                            outpuStream.write(buf,0,len);
                        outpuStream.close();
                        inputStream.close();
                        try {
                            //ALERT : compatible with 2003 excel 
                            //----------Suggested file format-----------------------------------------------------------
                            FileInputStream f = new FileInputStream(filename);
                            //TODO -- Add checking for excel format 2003 only!!
                            
                            Workbook wb = Workbook.getWorkbook(f);
                            
                            String [] sheetNames = wb.getSheetNames();
                            List uploadContents = new ArrayList();
                            //iterate through sheet
                            
                            
                            Properties prop = CommonUtils.getWSProperties(context);
                            //readsheet -- read from websuite.properties 
                           
                            String sheetToRead = prop.getProperty("readSheet");
                            
                            
                            //TODO -- add if condition
                            //if readsheet is not null then read sheet direct
                            //else
                            //read all sheet
                            
                            for (int i = 0 ; i < sheetNames.length ; i ++ ) {
                                log.info("Sheet : " + sheetNames[i].toString());
                                //to read specific sheet
                                //if want to read all sheet, remove if condition
                                if(sheetNames[i].toString().equals(sheetToRead)){
                                    List listSheet = new ArrayList();
                                    Sheet sh = wb.getSheet(sheetNames[i].toString());// to get the access to the sheet
                                    int totalNoOfRows = sh.getRows();// get the number of rows in sheet
                                    int totalNoOfCols = sh.getColumns();// to get the number of columns present in sheet
                                    
                                    for (int row = 0; row < totalNoOfRows; row++) {
                                        String lineAppend = "";
                                        String delimeter = "|";
                                        
                                        //read all data in a row, put in List
                                        List listRow = new ArrayList();
                                        if(row > 4){
                                            for (int col = 0; col < totalNoOfCols; col++) {
                                                listRow.add(sh.getCell(col, row).getContents());
                                            }
                                        }
                                        
                                        //append list, put with delimeter
                                        for(int t = 0; t < listRow.size(); t++){
                                            lineAppend = lineAppend.concat(listRow.get(t));
                                            if(t 0){
                                                    s = s.replaceAll("\r", " ").trim();
                                                }
                                                listSheet.add(s);                                   
                                            }
                                        }
                                    }
                                    uploadContents.addAll(listSheet);
                                }
                            }
    
  189. shishir says:

    I am getting error

    The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)

  190. yellaiah says:

    Hi,

    How can i read local file data based on column contains path in xlsx sheet. How can i do it. pls give me suggestions.

    Thanks

Leave a Reply

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