Read / Write Excel file in Java using Apache POI

by Viral Patel · November 28, 2012

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:

Java JDK 1.5 or above
Apache POI library v3.8 or above (download)
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>
Code language: HTML, XML (xml)

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

Download poi-2.5.1.jar(or in this case 3.8) jar file.
Include this file in your projects class path.
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();
Code language: Java (java)

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:

HSSF (Horrible SpreadSheet Format) – reads and writes Microsoft Excel (XLS) format files.
XSSF (XML SpreadSheet Format) – reads and writes Office Open XML (XLSX) format files.
HPSF (Horrible Property Set Format) – reads “Document Summary” information from Microsoft Office files.
HWPF (Horrible Word Processor Format) – aims to read and write Microsoft Word 97 (DOC) format files.
HSLF (Horrible Slide Layout Format) – a pure Java implementation for Microsoft PowerPoint files.
HDGF (Horrible DiaGram Format) – an initial pure Java implementation for Microsoft Visio binary files.
HPBF (Horrible PuBlisher Format) – a pure Java implementation for Microsoft Publisher files.
HSMF (Horrible Stupid Mail Format) – a pure Java implementation for Microsoft Outlook MSG files
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();
Code language: Java (java)

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

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();
}
Code language: Java (java)

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	
Code language: CSS (css)

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");
Code language: Java (java)

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[] {
    1 d, "John", 1500000 d
});
data.put("3", new Object[] {
    2 d, "Sam", 800000 d
});
data.put("4", new Object[] {
    3 d, "Dean", 700000 d
});

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();
}
Code language: Java (java)

Output:

new.xls

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

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();
}
Code language: Java (java)

Steps to update excel file will be:

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

Output:

update.xls

5. Adding Formulas

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

cell.setCellFormula("someformula")
Code language: Java (java)

For example:

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

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
Code language: Java (java)

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.
Code language: JavaScript (javascript)

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(14500 d);
dataRow.createCell(1).setCellValue(9.25);
dataRow.createCell(2).setCellValue(3 d);
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();
}
Code language: Java (java)

Output:

formula.xls

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);
            }
        }
    }
}
Code language: Java (java)

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();
}
Code language: Java (java)

Output:

style.xls

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);
Code language: Java (java)

So use setFillForegroundColor method to set the background color of given cell. I hope this article is useful.

Tags: apache apache software foundation excel Java

238 Comments

Mayur Chavda says:

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 says:

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 says:

4 February, 2013, 8:58

Hi Viral,

could you please share an example for .xlsx file.

Thanks in Advance,
Satheesh

Prasad says:

7 February, 2013, 18:02

Hey Satheesh,

Change HSSF to XSSF, it should work.

Prasad says:

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

anirudh says:
13 February, 2017, 22:57
this is giving me an exception “org/apache/poi/UnsupportedFileFormatException”

Mladen says:
21 March, 2013, 20:04
boolean isbold = font.getBoldweight() > HSSFFont.BOLDWEIGHT_NORMAL;
Check SVTableUtils from poi-examples.jar
Reply

Vinayak Pahalwan says:
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
Reply
Priyank Vyas says:
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
Reply
uday says:
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
Reply
Anto says:
15 January, 2013, 15:00
i want to convert odp document to xls documnet please help
Reply
[email protected] says:
16 January, 2013, 14:00
can i send the contents in the ppt to xls automatically by clicking a button
Reply
Anto says:
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
Reply
siddu says:
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/-
Reply
patel says:
20 January, 2013, 11:55
i want to copy array contents to excel file how can i do that.
Reply
Timy says:
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: 
```
Reply
Anto says:
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..
Reply

Hayk says:

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 says:
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?
Reply
- Satheesh says:
  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
  Reply
bobby says:
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
Reply
Parin says:
30 January, 2013, 12:19
Thanks for this post Viral! Was looking for a quick example for this – thanks!
Reply
saks says:
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
Reply

Pushpa Kalubarme says:

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 says:
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
Reply
anonymus says:
7 March, 2017, 16:24
u need to import apache libraries
Reply

Satheesh says:
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
Reply
- Gautham says:
  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.
  Reply
  - Roberto says:
    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
    Reply
Pushpa says:
7 February, 2013, 16:14
hi vital,
how to read more than one xml file at a time.
Reply
prachi Joshi says:
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
Reply

Thyagu says:

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 says:
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
Reply
midhun says:
13 February, 2013, 15:41
viral i need to convert xls to xlsx using java how can i do that plz help
Reply
Rishinder says:
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.
Reply
balakrishna gajam says:
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.
Reply
david drennan says:
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?
Reply
Jegar D. Apiag says:
19 February, 2013, 13:32
Please help me inserting a column to an excel file..Thanks in advance!
Reply
rajeswari says:
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…
Reply
- Carvalho says:
  15 May, 2018, 18:57
  Did you succeed in this work? I need the same project.
  Reply
Yogesh says:
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?
Reply
Andy says:
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.
Reply
Jayant Kumar says:
27 February, 2013, 16:07
Hi All,
How to Sort data in alphabetical order based on 1 column in excel ?
Reply
sunshine says:
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?
Reply
mula says:
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!
Reply
shri says:
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?
Reply
raj says:
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
Reply
rand says:
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.
Reply
Razz says:
7 March, 2013, 11:05
how can i read empty cells or null values from excel
Reply
Rahul Munjal says:
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
Reply
Sankar says:
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
Reply
Arpit Thakkar says:
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?
Reply

NewBrain says:

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 says:
20 March, 2013, 10:31
hi….
Is there any jar files required to use worksheets in soapui.
Reply
vivek says:
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.
Reply

santosh says:

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 says:
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?
Reply
- Bert says:
  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.
  Reply
sabyasachi says:
15 April, 2013, 23:49
Hi Viral,
Thanks for gr8 effort
Reply

class A says:

16 April, 2013, 11:15

class A{ public static void main String[]  ar{
System.out.println("A');
}}

Jugal Thakkar says:
18 April, 2013, 11:32
Hello Viral,
Nice Tutorial and Keep it up but You Haven’t Attached Source Code For This Tutorial
Reply
Raimund says:
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
Reply
vivek says:
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
Reply
Santhanam says:
25 April, 2013, 15:49
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
Reply
- rikin says:
  26 November, 2013, 22:22
  i have an error in netbeans 7.3…
  row variable doent have cell Iterator method..
  Iterator cellIterator = row.cellIterator();
  Reply
Abcd says:
30 April, 2013, 13:27
Sir ,where i add my downloaded poi in project .can u plz tell the complete structure of the project after adding the POI……Thanks
Reply
- Amit Jain says:
  3 May, 2013, 14:44
  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\
  Reply
anand says:
3 May, 2013, 16:15
nice, u helped me. u explained in a better way.
thank u.
Reply
Souvik Sarkar says:
4 May, 2013, 3:02
One should use TreeMap instead of HashMap. As there is no ordering, one can get the heading row not as the top row always.
Reply
sree harsha says:
6 May, 2013, 10:40
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..
Reply
swati saxena says:
6 May, 2013, 17:48
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)
Reply
Osano Kevin says:
8 May, 2013, 23:42
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.
Reply
Amol Patil says:
9 May, 2013, 17:02
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
Reply
- Viral Patel says:
  10 May, 2013, 14:47
  @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 :)
  Reply
Amol Patil says:
9 May, 2013, 17:08
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
Reply
jarkema says:
14 May, 2013, 0:56
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.
Reply
radha says:
14 May, 2013, 14:09
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.
Reply
Ronnie says:
15 May, 2013, 15:33
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?
Reply
- Ronnie says:
  15 May, 2013, 15:43
  The file was in .xlsx format. Converted it to .xls and now its working fine. Thank you.
  Reply
Vaigha says:
22 May, 2013, 10:08
How to add an image(logo) into an excel file using poi api?
Reply
prasad says:
22 May, 2013, 11:59
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.
Reply
Ronnie says:
22 May, 2013, 15:15
Hi,
Doesn’t Apache POI read .xlsx files?
Reply
Ronnie says:
22 May, 2013, 15:24
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)
Reply
hitish says:
23 May, 2013, 15:04
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.
Reply

Fahad Shafeeq says:

24 May, 2013, 3:28

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

Fahad says:
24 May, 2013, 23:08
Hi Viral, Can you show an example of workbook with multiple sheets both having different data i.e headers, footers and style?
Reply
milan says:
28 May, 2013, 18:00
I am Getting error on iterator that it can not be resolved as type…..so please give a solution if u can
Reply
Mano says:
29 May, 2013, 11:08
Hi viral,
How to stored the excel values to a oracle database. Please give any suggestions or examples for that. Thanks in advance.
Reply
milan says:
5 June, 2013, 11:14
Hi, Viral does it works for .xlsx file?????? please reply fast as u can its critically impotant
thanx…… :)
Reply
Naveen kumar says:
6 June, 2013, 13:27
I need to change the color of the font in the excel, kindly help me…
Reply

Vishal says:

6 June, 2013, 18:44

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.

Rohit More says:
10 June, 2013, 16:19
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.
Reply
ssp says:
21 June, 2013, 10:35
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"); 
```
Reply
Nidhi Sharma says:
22 June, 2013, 17:21
how to print the output in XML after reading it through XLS using apache poi
Reply

sandy says:

23 June, 2013, 14:59

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.

abhishek says:
27 June, 2013, 20:21
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
Reply
ssp says:
28 June, 2013, 13:54
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)					
			 });
		}
```
Reply
pretty says:
29 June, 2013, 11:26
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.
Reply
Aanchal says:
30 June, 2013, 18:05
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
Reply

NASREEN HUSSAIN says:

1 July, 2013, 14:13

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.

Ehsan says:
2 July, 2013, 10:52
Just wanted to thank you so much for providing this tutorial. Much appreciated.
Reply
Amit says:
2 July, 2013, 15:53
i want upload two excel file and compare their certain column……………………plz help me
Reply
shaik says:
3 July, 2013, 14:35
i want upload two excel file and compare their certain column……………………plz help me
Reply
pankaj says:
9 July, 2013, 12:57
Good article. Helped me instantly when i was stuck. thanks :)
Reply
saran says:
10 July, 2013, 16:45
How to read xlsx (or) xls cell dropdown(list) value ?
Reply
Arunbabu Kongara says:
12 July, 2013, 12:38
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
Reply
Saran kumar says:
13 July, 2013, 16:45
Since I am new to Apache POI, screen shoots may root me to finish this task Successfully..! Thanks in advance
Reply
ANSON VATTOLY says:
15 July, 2013, 18:58
Thank u so much…….:)
Reply
arpita says:
17 July, 2013, 4:09
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)
Reply
Karthik says:
17 July, 2013, 10:20
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
Reply
Paul says:
22 July, 2013, 20:44
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 .
Reply
Mahesh says:
24 July, 2013, 18:39
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
Reply

Elaya says:

30 July, 2013, 11:32

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

Suvi says:
30 July, 2013, 15:52
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.
Reply
NN says:
2 August, 2013, 22:06
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.
Reply
NN says:
2 August, 2013, 22:07
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.
Reply
Amit says:
2 August, 2013, 23:40
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.
Reply
Revathy says:
5 August, 2013, 20:41
Hello :) I wanted to know how to compare 2 excel sheets??? pls help me ..ASAP !!!
Reply
imrsn says:
13 August, 2013, 19:30
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.
Reply
- Setu says:
  18 September, 2013, 15:21
  Thank you!
  Reply
vinh says:
14 August, 2013, 20:21
very good! thanks u!!
Reply
Onu says:
19 August, 2013, 19:47
Hi Patel
Thanks for a wonderful article, quick question – how I can ignore first row & print everything (since first row is the column title
Reply
Yaakov says:
20 August, 2013, 1:53
Really awesome! Exactly what I needed and very well explained. Just the basic information that I need to use the API :)
Reply
Patrick Meppe says:
21 August, 2013, 0:57
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;
//...
```
Reply
krishna says:
22 August, 2013, 20:04
hai sir…gd evg
i wist to create excel sheet without using Microsoft office….
i.e when i am unistall ms office it will not work so….we have any anotherway to create and open excel sheet…..then send me the mail to……. [email protected]
thank you
Reply
Sujith Sundaran says:
23 August, 2013, 20:53
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 ?
Reply
Justin Grant says:
28 August, 2013, 23:52
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>
```
Reply
Sunil Kumar says:
31 August, 2013, 0:09
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!
Reply
- DeenuBabu says:
  27 November, 2013, 11:05
  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/”
  Reply
- DeenuBabu says:
  27 November, 2013, 11:10
  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.
  Reply
Praveen says:
1 September, 2013, 10:41
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.
Reply
Jil says:
3 September, 2013, 15:19
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?
Reply
- Priyanka says:
  10 October, 2013, 15:42
  I think, you need to import the proper classes. And before that add poi jar to your project.
  Reply
Rohit says:
3 September, 2013, 16:52
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.!!!
Reply
doki says:
4 September, 2013, 10:25
Hi,
please send me the code
Reply
Kalaiyappan V says:
4 September, 2013, 17:32
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.
Reply
taruna says:
5 September, 2013, 19:08
i need help…
i am getting error on line* Iterator cellIterator = row.cellIterator();* showing there is no method like *cellIterator()*
Reply
SANDOKAN says:
6 September, 2013, 15:47
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?
Reply
nelson says:
8 September, 2013, 12:17
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”
Reply
- nelson says:
  8 September, 2013, 12:31
  Solved. Nice Tutorial. Thanks.
  Reply
Chandu says:
8 September, 2013, 16:44
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
Reply
aamir says:
20 September, 2013, 20:21
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.
Reply
Supriyo says:
23 September, 2013, 11:21
Very nice blog. very efective.
Reply
Balaji says:
26 September, 2013, 10:49
Hi,
Nice Tut.. Is this possible to do “Keyword Contain Search” action by this..???
Reply
Vinh says:
1 October, 2013, 21:51
Thanks you!
Reply
Umasri says:
7 October, 2013, 19:54
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();
```
Reply
JOSE says:
8 October, 2013, 17:30
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?
Reply
- Josser Ramirez says:
  26 January, 2014, 6:20
  Resolve this with.
  after
  Object [] objArr = data.get(key);
  before
  Object [] objArr = data.get(String.valueOf(rownum));
  Reply
siva says:
8 October, 2013, 18:02
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….
Reply
Soniya says:
9 October, 2013, 12:56
Hey ,
Please Help me . I need code for read data from one excel sheet and append this data on other excel Sheet
Reply
kirtana says:
10 October, 2013, 23:41
copy pasted the same code.but when compiling in eclipse it shows message “Usage: AddDimensionedImage imageFile outputFile”.plz help
Reply
Mayank says:
15 October, 2013, 12:59
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
Reply
suv says:
25 October, 2013, 15:03
How to read cell value in excel using cell number as a key in JAVA
Reply
Balendu says:
31 October, 2013, 12:19
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.
Reply
Dipeen says:
6 November, 2013, 10:07
Hi
How can I sort an excel sheet by a column using apache POI ?
Reply
Sandy says:
7 November, 2013, 14:32
Hi Viral,
How to set a password for xls file. ? Without password it should not even allow to read the file
Thanks in advance
Reply
sandeep says:
11 November, 2013, 14:37
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
Reply
Anil Sanwal says:
22 November, 2013, 12:03
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.
Reply
KC says:
25 November, 2013, 9:25
wow…. your knowledge sharing is awesome… it was help me to understand a lot about POI…, thank you so much
Reply
Div says:
27 November, 2013, 2:00
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.
Reply
Manjunath says:
30 November, 2013, 20:55
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 ?
Reply
KYo says:
2 December, 2013, 17:34
Impressive
Reply
Ganesh says:
3 December, 2013, 14:41
Hello Guys,,
Need help in fetching date values from excel sheet from particaula cell which are in this format MMDDYYYY
thanks in advance.
Reply

Shivany says:

4 December, 2013, 13:20

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:

4 January, 2014, 1:48

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)

NGowda says:

12 December, 2013, 13:26

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:
13 December, 2013, 16:18
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
Reply

kevin flynn says:
13 December, 2013, 21:59
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.
Reply
Gangadhar says:
18 December, 2013, 22:56
How to read the excel file using java only with out using any APIs
Reply
Pavana says:
2 January, 2014, 20:15
How to extract data from excel using java and how to write code for the same using netbeans.
Reply
Richard PSilva says:
4 January, 2014, 1:41
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.
Reply
- Kumar says:
  20 January, 2014, 23:57
  Hi Richard,
  Please share the solution if you get it.
  Reply

Tester says:

10 January, 2014, 11:07

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:

10 January, 2014, 12:56

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

Tukaram Bhosale says:
22 January, 2014, 19:50
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,
Reply
venkat says:
28 January, 2014, 14:29
I want to insert a checkbox in excel using the poi jar. Is it possible?? Anyone one tell..how to solve this problem.?
Reply
Sachin says:
22 February, 2014, 21:57
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(); 
```
Reply
Subrata Maiti says:
18 March, 2014, 11:23
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 !!
Reply
- Shivani says:
  20 April, 2015, 11:57
  Hello
  i want code for converting excel sheet into .csv file in eclipse i am using apache POI.
  Reply
arpit says:
20 May, 2014, 12:40
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
Reply
- thatskriptkid says:
  21 June, 2014, 15:28
  You should add external JAR – org.apache.juli.logging.LogFactory.
  Reply
Vinesh says:
30 May, 2014, 18:28
Good explanation.. helped a lot.
THanks
Reply
Jess A says:
2 June, 2014, 7:30
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!
Reply
dev says:
3 June, 2014, 16:22
how I can add formula to every row if I have more than 500 rows?
Reply
Divya says:
4 June, 2014, 19:04
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.
Reply
Reetesh says:
6 June, 2014, 13:11
Thanks for explanation, it solved one of my problem.
Reply
betty says:
4 July, 2014, 15:04
How to read content from xlsm? can we use the same jar and if yes the what type of format i need to use
Reply
- Raghavendra Samant says:
  25 April, 2015, 23:38
  you can use POI 3.11 versions XSSFWorkbook and XSSFSheet classes for xlsm
  Reply
Divya says:
11 July, 2014, 10:18
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.
Reply
Justin says:
1 August, 2014, 14:34
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 ?
Reply
mohankumar says:
3 September, 2014, 15:58
thank u so much…
Reply
Kumar says:
2 November, 2014, 11:48
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
Reply

Jags says:

26 November, 2014, 1:05

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

chandra says:
13 December, 2014, 4:55
how to copy the content to an text file instead of an excel file
Reply
- inl1ner says:
  6 February, 2015, 17:14
  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
  Reply
Sagar says:
15 December, 2014, 14:27
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
Reply
Gulamjelani Qureshi says:
29 January, 2015, 21:03
what about row type in iterator
Reply
amitabh says:
7 February, 2015, 10:53
Very good explanation and way to do it. Thank you.
Reply
Sachin says:
10 February, 2015, 19:36
Referred the above java code and converted to Scala,Working fine!!
Thanks for the clean code.
Reply
Sagar says:
27 February, 2015, 3:11
How do I populate data into another tab when the user clicks on the link in the first tab.
Reply
Candis says:
14 March, 2015, 5:38
Can we write a JSONObject to the excel file….??
Thanks
Reply
Kirankumar Oggu says:
23 March, 2015, 22:28
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.
Reply
Jitendra Arethiya says:
22 April, 2015, 12:34
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?
Reply
Anjana says:
28 April, 2015, 13:17
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
Reply
Leo says:
28 May, 2015, 14:31
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.
Reply
prasanthi says:
1 June, 2015, 20:45
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
Reply
Sundar says:
9 June, 2015, 22:10
Can any one say me how to set the page layout view using XSSF poi ?thanks in advance
Reply
raj says:
17 June, 2015, 12:04
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?
Reply
Srikanth says:
24 July, 2015, 6:31
Nice info,
My doubt is if i want to download the written excel immediately without storing it in any output path
Reply
Aj says:
14 August, 2015, 0:44
Thanks for this article! It was useful.
Reply
shweta says:
22 August, 2015, 12:31
the excel sheet created is not opening in few mobiles..it gives unsupported file toast message
Reply
satya says:
18 November, 2015, 15:35
Please update me.
set Password Protected Excel Sheets Using java
Reply
Gundu says:
30 December, 2015, 10:31
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.
Reply
Eduard says:
9 January, 2016, 19:48
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!!!
Reply
Handsome says:
23 March, 2016, 18:16
Hello,
How can I find user selected cells or active cells?
Thank u
Reply
priya says:
20 April, 2016, 16:48
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.
Reply
Swapnali says:
30 April, 2016, 4:28
Hi Viral,
Could you please help how to find row count ofsheet?
Thanks
Reply
Sanjay says:
10 May, 2016, 13:40
Could tell me how show pptx file in android using apachi poi…
Reply
Ma says:
12 May, 2016, 13:57
I want to write the output of my coded algorithm into an excel?
How can do it?
Reply

faz says:

16 June, 2016, 13:00

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

shishir says:
22 July, 2016, 20:41
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)
Reply
yellaiah says:
20 September, 2016, 16:19
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
Reply
Nitin Kumar Jha says:
30 September, 2016, 12:53
Hi, Can I use the inbuilt formulae function of the POI sheet in my external java class , I dont want to read or write excel just want to use the formulaes. Is there any way ?
Reply
Jithin says:
17 October, 2016, 20:17
How to refresh an excel file after writing data?
Reply
vikas bansal says:
15 November, 2016, 17:48
this code in Eclipse is showing some functions are deprecated.
Like:
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;
Can you please update the code. Many thanks…
Reply
mikmik says:
12 December, 2016, 7:38
how can I sort data in excel coming from java netbeans. TIA
Reply
Victor Benso says:
13 January, 2017, 16:54
Hi congrats on the awesome guide.
I’ve tested the first example in android.
Opening the file in libreoffice yields:
3 Dean 700000
Emp No. Name Salary
1 John 1500000
2 Sam 800000
I’ve solved the issue changing:
Set keyset = data.keySet();
to
Set keyset = new TreeMap(data).keySet();
Reply
chetan says:
30 January, 2017, 17:55
Hi Viral,
Thanks for this article. I am new to core java and having one question.
I am aware of filereader/bufferedreader/filewriter/bufferedwriter/printwriter io classes in java, cant we use them for XLS/XLSX read?
Is there any reason we use combination of fileinputstream + POI only?
also is xls/xlsx file treated as text file or binary when we perform IO operations in java?
Reply
DEEP DAFTARY says:
10 April, 2017, 20:46
Hi,
Is it possible to write into excel row after another? OR
Is it possible to put the below piece of code:
FileOutputStream out = new FileOutputStream(new File(“C:\\test.xls”));
workbook.write(out);
out.close();
inside the a loop where we set cells value. i.e.
.setCellValue(String);
Reply
mani says:
28 June, 2017, 21:02
Hi I want to get the Iterator iterator = firstSheet.iterator();
value not from 1 i want iterator from where the filter is started means
suppose i have data of 10 records out of which 6 records are filtered and 4 records are visible
i want to iterate from 6 to 10 insted of 1 row
please guide me if you find any solution
Reply
A. Colmant says:
5 October, 2017, 18:01
Thank you! I was looking all day for a useful example, and yours was efficient and straight to the point! It took me a while to find it, there are so many bad sources out there.
Reply
Pradeep k says:
15 February, 2018, 17:25
How can i make a comparison of drive size details on daily basis in java and storing the values in Excel sheet
Reply
Atul says:
1 June, 2018, 18:37
can anyone tell me how to handle null/blank value in below code
JSP Page
.blueText {
color: blue;
}
<%!
public static boolean isRowEmpty(Row row){
int firstCol = row.getFirstCellNum();
for(int cnt = 0; cnt
= 0))
{
DataInputStream in = new DataInputStream(request.getInputStream());
int formDataLength = request.getContentLength();
byte dataBytes[] = new byte[formDataLength];
int byteRead = 0;
int totalBytesRead = 0;
while (totalBytesRead < formDataLength) {
byteRead = in.read(dataBytes, totalBytesRead,formDataLength);
totalBytesRead += byteRead;
}
String file = new String(dataBytes);
String saveFile = file.substring(file.indexOf("filename=\"") + 10);
saveFile = saveFile.substring(0, saveFile.indexOf("\n"));
saveFile = saveFile.substring(saveFile.lastIndexOf("\\") + 1,saveFile.indexOf("\""));
//out.println(saveFile);
int lastIndex = contentType.lastIndexOf("=");
String boundary = contentType.substring(lastIndex + 1,contentType.length());
int pos;
pos = file.indexOf("filename=\"");
pos = file.indexOf("\n", pos) + 1;
pos = file.indexOf("\n", pos) + 1;
pos = file.indexOf("\n", pos) + 1;
int boundaryLocation = file.indexOf(boundary, pos) – 4;
int startPos = ((file.substring(0, pos)).getBytes()).length;
int endPos = ((file.substring(0, boundaryLocation)).getBytes()).length;
FileOutputStream fileOut = new FileOutputStream(saveFile);
//out.println("”);
// out.println(“alert(‘You are successfully Login at:” + saveFile + “‘)”);
// out.println(“location=’EmployeeLogin.html’;”);
// out.println(“”);
fileOut.write(dataBytes, startPos, (endPos – startPos));
fileOut.flush();
fileOut.close();
%>
0)
{
saveFile=saveFile.substring(0,saveFile.lastIndexOf(“.”));
}
String query=”Select File_name,to_char(FILE_UPLOAD_DATE,’DD-MON-YYYY’) from DYNAMIC_INSERT where File_name='”+saveFile+”‘”;
Statement st1=con.createStatement();
ResultSet rs=st1.executeQuery(query);
if(rs.next())
{
String file_name=rs.getString(1);
String FILE_UPLOAD_DATE=rs.getString(2);
out.println(“”);
out.println(“alert(‘File name ” + file_name + ” already exists File upload Date is “+FILE_UPLOAD_DATE+”‘)”);
out.println(“location=’xlsUpload.html’;”);
out.println(“”);
}
else
{
XSSFWorkbook workbook=new XSSFWorkbook(file_inut);
XSSFSheet firstsheet=workbook.getSheetAt(0);
Iterator iterator=firstsheet.rowIterator();
int count=0;
int search_id=0;
XSSFRow nextrow;
Row row;
//nextrow=firstsheet.createRow((short)1);
//nextrow.createCell(0).setCellValue(“Null”);
// nextrow.createCell(1);
while(iterator.hasNext())
{
nextrow =(XSSFRow)iterator.next();
ArrayList rowarraylist=new ArrayList();
Iterator cellIterator=nextrow.cellIterator();
while(cellIterator.hasNext())
{
XSSFCell cell=(XSSFCell)cellIterator.next();
rowarraylist.add(cell);
}
CellArrayListHolder.add(rowarraylist);
}
//out.println(CellArrayListHolder);
ArrayList rowarraylist=null;
//PreparedStatement st=con.prepareStatement(“insert into DYNAMIC_INSERT values(?,?,?,?,?,?,?,?,?,?,?)”);
PreparedStatement ps=con.prepareStatement(“insert into DYNAMIC_INSERT values(?,?,?,?,?,?,?,?,?,?,?)”);
ps.setString(1, saveFile);
ps.setInt(2, search_id);
ps.setDate(3,new java.sql.Date(System.currentTimeMillis()));
for(int i=1;i0)
{
out.println(“”);
out.println(“alert(‘File uploaded successfully’);”);
out.println(“location=’xlsUpload.html’;”);
out.println(“”);
}
}
}
//}
//catch(Exception ex)
//{
// out.println(“”);
// out.println(“alert(‘Invalid File format/File not choose ‘);”);
// out.println(“location=’xlsUpload.html’;”);
// out.println(“”);
//
//}
%>
Reply
Swaroop says:
20 March, 2019, 3:31
Hi Viral,
Do you know, how to add a multi-select dropdown into Excel using POI. I know POI does not support this feature. But, I am curious to know, if there is a work-around for this. Any help on this very much appreciated.
Thanks,
Swaroop
Reply
Amit Kumar Pandey says:
24 October, 2019, 1:25
I dont wnat to use fileoutputstream i want that on my servlet trigger it must be downloaded as we download from any other usual side from browser it should save in dwoload of chrome or watever ?
For this I use workbook.write(response.outputstream()) and also response.setheader() and setcontenttype() as well..but no luck …
Reply
Purvi says:
8 November, 2019, 2:57
Hi Viral,
Your article is very useful. But still confused specially when writing a console output on Excel.
In my code main loop is getting executed for each row(21 rows), its opening one popup. fetching all values and printing on console in a tabular format. Now how do i write that all 21 outputs on Excel. Please guide me how do i start then will try by my own.
Reply
tayyab says:
27 November, 2019, 17:31
hi
am trying to get excel data and by sendkeys(selenium webdriver) add in xyz form. i am successfull but it iterate each input field 6 times. dont know why. i have just 2 rows and 6 columns like that. it should work for the second row only how i make it possible need help guys
abc efg hij
abc 1 11.0 22.0
/// /my code
public class data2 {
public static FileInputStream fis;
public static FileOutputStream fos;
DataFormatter formatter = new DataFormatter();
Object value = null;
WebDriver driver;
String url= “https://dc.designcollection.in/codecanyon/gsrf-google-sheet-registration-form/registration-from-5.html?ref=designcollection”;
@Test
public void br() {
WebDriverManager.chromedriver().setup();
//WebDriverManager.firefoxdriver().setup();
driver = new ChromeDriver();
driver.manage().window().maximize();
//log.info(“open project url”);
driver.get(url);
driver.findElement(By.xpath(“//*[@id=\”testform\”]/div[1]/div[1]/div/input”));
}
@Test
public void data_driven() throws IOException, InterruptedException {
try {
fis = new FileInputStream(“F:\\tayyab work\\book1.xlsx”);
XSSFWorkbook wk = new XSSFWorkbook(fis);
XSSFSheet sheet = wk.getSheetAt(0);
//XSSFCell cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK) ;
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();
driver.findElement(By.xpath(“//*[@id=\”testform\”]/div[1]/div[1]/div/input”)).sendKeys(formatter.formatCellValue(cell));
driver.findElement(By.xpath(“//*[@id=\”testform\”]/div[1]/div[2]/div/input”)).sendKeys(formatter.formatCellValue(cell));
driver.findElement(By.xpath(“//*[@id=\”testform\”]/div[2]/input”)).sendKeys(formatter.formatCellValue(cell));
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(“”);
}
fis.close();
fos = new FileOutputStream(“F:\\tayyab work\\book1.xlsx”);
wk.write(fos);
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
// write data of some sheet
// fis = new FileInputStream(“E:\\book1.xlsx”);
/*
XSSFRow writerow = sheet.createRow(6);
XSSFCell writecell = writerow.createCell(6);
writecell.setCellValue(“this is test insertion”);
fos = new FileOutputStream(“E:\\book1.xlsx”);
wk.write(fos);
*/
//fos.flush();
}
Reply
prabha says:
29 November, 2019, 11:24
HI,
I want to read the date format in excel. if suppose having any date format want to change DD/MMM/YYYY format. ie., 01-Jan-2019. any one please suggest and help me.
Reply

Read / Write Excel file in Java using Apache POI

1. Add Apache POI dependency

2. Read Excel File

Working with .xlsx files

3. Create New Excel File

4. Update Existing Excel File

5. Adding Formulas

Triggering Existing Excel Formulas

6. Adding Styles to Cell

6.1 Add Background Color to the cell

You may also like...

238 Comments

Leave a Reply Cancel reply

Read / Write Excel file in Java using Apache POI

1. Add Apache POI dependency

2. Read Excel File

Working with .xlsx files

3. Create New Excel File

4. Update Existing Excel File

5. Adding Formulas

Triggering Existing Excel Formulas

6. Adding Styles to Cell

6.1 Add Background Color to the cell

You may also like...

Java: How to Load CSV file into Database

Convert String to Enum Instance in Java

How to take screen shots in Java

Java Garbage Collection Simplified

238 Comments

Leave a Reply Cancel reply