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.



184 Comments

  • Mayur Chavda 5 December, 2012, 12:21

    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 5 December, 2012, 14:07

      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 4 February, 2013, 8:58

        Hi Viral,

        could you please share an example for .xlsx file.

        Thanks in Advance,
        Satheesh

        • Prasad 7 February, 2013, 18:02

          Hey Satheesh,

          Change HSSF to XSSF, it should work.

          • Prasad 7 February, 2013, 18:13

            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 21 March, 2013, 20:04

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

      Check SVTableUtils from poi-examples.jar

  • Vinayak Pahalwan 14 December, 2012, 11:30

    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

  • Priyank Vyas 3 January, 2013, 0:12

    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

  • uday 3 January, 2013, 10:23

    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

  • Anto 15 January, 2013, 15:00

    i want to convert odp document to xls documnet please help

  • s.arun316@gmail.com 16 January, 2013, 14:00

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

  • Anto 16 January, 2013, 14:50

    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

  • siddu 18 January, 2013, 13:19

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

  • patel 20 January, 2013, 11:55

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

  • Timy 23 January, 2013, 12:46

    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:

  • Anto 24 January, 2013, 13:09

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

  • Hayk 24 January, 2013, 17:25

    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.

  • Kyle 26 January, 2013, 22:19

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

    • Satheesh 4 February, 2013, 9:00

      hi Kyle,

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

      Thanks in Advance,
      Satheesh

  • bobby 28 January, 2013, 13:47

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

    Can any one help the same

  • Parin 30 January, 2013, 12:19

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

  • saks 2 February, 2013, 13:57

    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

  • Pushpa Kalubarme 2 February, 2013, 14:47

    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 4 February, 2013, 9:03

      Hi Pushpa,

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

      Thanks in advance,
      Satheesh

  • Satheesh 4 February, 2013, 8:56

    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 26 February, 2013, 17:12

      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 20 March, 2013, 21:32

        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

  • Pushpa 7 February, 2013, 16:14

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

  • prachi Joshi 8 February, 2013, 16:10

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

  • Thyagu 13 February, 2013, 9:49

    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();
    }
    
  • aakash chandwani 13 February, 2013, 11:41

    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

  • midhun 13 February, 2013, 15:41

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

  • Rishinder 15 February, 2013, 2:13

    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.

  • balakrishna gajam 15 February, 2013, 15:42

    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.

  • david drennan 15 February, 2013, 19:38

    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?

  • Jegar D. Apiag 19 February, 2013, 13:32

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

  • rajeswari 21 February, 2013, 9:24

    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…

  • Yogesh 21 February, 2013, 10:51

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

  • Andy 26 February, 2013, 12:06

    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.

  • Jayant Kumar 27 February, 2013, 16:07

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

  • sunshine 27 February, 2013, 17:26

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

  • mula 28 February, 2013, 5:30

    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!

  • shri 28 February, 2013, 17:10

    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?

  • raj 28 February, 2013, 22:17

    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

  • rand 6 March, 2013, 10:58

    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.

  • Razz 7 March, 2013, 11:05

    how can i read empty cells or null values from excel

  • Rahul Munjal 8 March, 2013, 13:00

    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

  • Sankar 9 March, 2013, 18:45

    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

  • Arpit Thakkar 14 March, 2013, 12:00

    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?

  • NewBrain 15 March, 2013, 14:08

    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();
    		}
    	}
    	
    }
       
  • Ashik 20 March, 2013, 10:31

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

  • vivek 27 March, 2013, 13:13

    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.

  • santosh 2 April, 2013, 21:28

    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;
            }
        }
    }
    
  • Bert 4 April, 2013, 0:16

    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 10 April, 2013, 15:40

      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.

  • sabyasachi 15 April, 2013, 23:49

    Hi Viral,

    Thanks for gr8 effort

  • class A 16 April, 2013, 11:15
     class A{ public static void main String[]  ar{
    System.out.println("A');
    }}
  • Jugal Thakkar 18 April, 2013, 11:32

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

  • Raimund 18 April, 2013, 21:13

    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

  • vivek 25 April, 2013, 12:11

    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

Leave a Reply

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

Note

To post source code in comment, use [code language] [/code] tag, for example:

  • [code java] Java source code here [/code]
  • [code html] HTML here [/code]

Current day month ye@r *