Tutorial

Apache POI Tutorial

Published on August 4, 2022
Apache POI Tutorial

Welcome to Apache POI Tutorial. Sometimes we need to read data from Microsoft Excel Files or we need to generate reports in Excel format, mostly for Business or Finance purposes. Java doesn’t provide built-in support for working with excel files, so we need to look for open source APIs for the job. When I started the hunt for Java APIs for excel, most of the people recommended JExcel or Apache POI. After further research, I found that Apache POI is the way to go for following main reasons. There are some other reasons related to advanced features but let’s not go into that much detail.

  • Backing of Apache foundation.
  • JExcel doesn’t support xlsx format whereas POI supports both xls and xlsx formats.
  • Apache POI provides stream-based processing, that is suitable for large files and requires less memory.

Apache POI

Apache POI provides excellent support for working with Microsoft Excel documents. Apache POI is able to handle both XLS and XLSX formats of spreadsheets. Some important points about Apache POI API are:

  1. Apache POI contains HSSF implementation for Excel '97(-2007) file format i.e XLS.
  2. Apache POI XSSF implementation should be used for Excel 2007 OOXML (.xlsx) file format.
  3. Apache POI HSSF and XSSF API provides mechanisms to read, write or modify excel spreadsheets.
  4. Apache POI also provides SXSSF API that is an extension of XSSF to work with very large excel sheets. SXSSF API requires less memory and is suitable when working with very large spreadsheets and heap memory is limited.
  5. There are two models to choose from - event model and user model. Event model requires less memory because the excel file is read in tokens and requires processing them. User model is more object oriented and easy to use and we will use this in our examples.
  6. Apache POI provides excellent support for additional excel features such as working with Formulas, creating cell styles by filling colors and borders, fonts, headers and footers, data validations, images, hyperlinks etc.

Apache POI Maven Dependencies

If you are using maven, add below Apache POI dependencies.

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.10-FINAL</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.10-FINAL</version>
</dependency>

Current version of Apache POI is 3.10-FINAL. If you are having standalone java application, include jars from below image. Apache POI Jars, Apache POI, Apache POI example, apache poi tutorial

Apache POI Example - Read Excel File

Let’s say we have an excel file “Sample.xlsx” with two sheets and having data like below image. We want to read the excel file and create the list of Countries. Sheet1 has some additional data, that we will ignore while parsing it. Java Read Excel File, Apache POI, Apache POI Example, Apache POI tutorial Java Read Excel File, Apache POI, Apache POI Example, Apache POI tutorial Our Country java bean code is: Country.java

package com.journaldev.excel.read;

public class Country {

	private String name;
	private String shortCode;
	
	public Country(String n, String c){
		this.name=n;
		this.shortCode=c;
	}
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getShortCode() {
		return shortCode;
	}
	public void setShortCode(String shortCode) {
		this.shortCode = shortCode;
	}
	
	@Override
	public String toString(){
		return name + "::" + shortCode;
	}
	
}

Apache POI example program to read excel file to the list of countries looks like below. ReadExcelFileToList.java

package com.journaldev.excel.read;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

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.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelFileToList {

	public static List<Country> readExcelData(String fileName) {
		List<Country> countriesList = new ArrayList<Country>();
		
		try {
			//Create the input stream from the xlsx/xls file
			FileInputStream fis = new FileInputStream(fileName);
			
			//Create Workbook instance for xlsx/xls file input stream
			Workbook workbook = null;
			if(fileName.toLowerCase().endsWith("xlsx")){
				workbook = new XSSFWorkbook(fis);
			}else if(fileName.toLowerCase().endsWith("xls")){
				workbook = new HSSFWorkbook(fis);
			}
			
			//Get the number of sheets in the xlsx file
			int numberOfSheets = workbook.getNumberOfSheets();
			
			//loop through each of the sheets
			for(int i=0; i < numberOfSheets; i++){
				
				//Get the nth sheet from the workbook
				Sheet sheet = workbook.getSheetAt(i);
				
				//every sheet has rows, iterate over them
				Iterator<Row> rowIterator = sheet.iterator();
				while (rowIterator.hasNext()) 
		        {
					String name = "";
					String shortCode = "";
					
					//Get the row object
					Row row = rowIterator.next();
					
					//Every row has columns, get the column iterator and iterate over them
					Iterator<Cell> cellIterator = row.cellIterator();
		             
		            while (cellIterator.hasNext()) 
		            {
		            	//Get the Cell object
		            	Cell cell = cellIterator.next();
		            	
		            	//check the cell type and process accordingly
		            	switch(cell.getCellType()){
		            	case Cell.CELL_TYPE_STRING:
		            		if(shortCode.equalsIgnoreCase("")){
		            			shortCode = cell.getStringCellValue().trim();
		            		}else if(name.equalsIgnoreCase("")){
		            			//2nd column
		            			name = cell.getStringCellValue().trim();
		            		}else{
		            			//random data, leave it
		            			System.out.println("Random data::"+cell.getStringCellValue());
		            		}
		            		break;
		            	case Cell.CELL_TYPE_NUMERIC:
		            		System.out.println("Random data::"+cell.getNumericCellValue());
		            	}
		            } //end of cell iterator
		            Country c = new Country(name, shortCode);
		            countriesList.add(c);
		        } //end of rows iterator
				
				
			} //end of sheets for loop
			
			//close file input stream
			fis.close();
			
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return countriesList;
	}

	public static void main(String args[]){
		List<Country> list = readExcelData("Sample.xlsx");
		System.out.println("Country List\n"+list);
	}

}

The program is very easy to understand and contains following steps:

  1. Create Workbook instance based on the file type. XSSFWorkbook for xlsx format and HSSFWorkbook for xls format. Notice that we could have created a wrapper class with factory pattern to get the workbook instance based on the file name.
  2. Use workbook getNumberOfSheets() to get the number of sheets and then use for loop to parse each of the sheets. Get the Sheet instance using getSheetAt(int i) method.
  3. Get Row iterator and then Cell iterator to get the Cell object. Apache POI is using iterator pattern here.
  4. Use switch-case to read the type of Cell and the process it accordingly.

Now when we run above Apache POI example program, it produces following output on console.

Random data::1.0
Random data::2.0
Random data::3.0
Random data::4.0
Country List
[India::IND, Afghanistan::AFG, United States of America::USA, Anguilla::AIA, 
Denmark ::DNK, Dominican Republic ::DOM, Algeria ::DZA, Ecuador ::ECU]

Apache POI Example - Write Excel File

Writing excel file in apache POI is similar to reading, except that here we first create the workbook. Then set sheets, rows and cells values and use FileOutputStream to write it to file. Let’s write a simple apache POI example where we will use list of countries from the above method to save into another file in a single sheet. WriteListToExcelFile.java

package com.journaldev.excel.read;

import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.List;

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.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteListToExcelFile {

	public static void writeCountryListToFile(String fileName, List<Country> countryList) throws Exception{
		Workbook workbook = null;
		
		if(fileName.endsWith("xlsx")){
			workbook = new XSSFWorkbook();
		}else if(fileName.endsWith("xls")){
			workbook = new HSSFWorkbook();
		}else{
			throw new Exception("invalid file name, should be xls or xlsx");
		}
		
		Sheet sheet = workbook.createSheet("Countries");
		
		Iterator<Country> iterator = countryList.iterator();
		
		int rowIndex = 0;
		while(iterator.hasNext()){
			Country country = iterator.next();
			Row row = sheet.createRow(rowIndex++);
			Cell cell0 = row.createCell(0);
			cell0.setCellValue(country.getName());
			Cell cell1 = row.createCell(1);
			cell1.setCellValue(country.getShortCode());
		}
		
		//lets write the excel data to file now
		FileOutputStream fos = new FileOutputStream(fileName);
		workbook.write(fos);
		fos.close();
		System.out.println(fileName + " written successfully");
	}
	
	public static void main(String args[]) throws Exception{
		List<Country> list = ReadExcelFileToList.readExcelData("Sample.xlsx");
		WriteListToExcelFile.writeCountryListToFile("Countries.xls", list);
	}
}

When I execute above apache POI example program, the excel file generated looks like below image. Java Write Excel File, Apache POI, Apache POI Example, Apache POI tutorial

Apache POI Example - Read Excel Formula

Sometimes we need to handle complex excel files with formulas, let’s see a simple apache POI example to read the formula of a cell with it’s value. Java Excel Read Formula, Apache POI, Apache POI Example, Apache POI tutorial ReadExcelFormula.java

package com.journaldev.excel.read;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelFormula {

	public static void readExcelFormula(String fileName) throws IOException{
		
		FileInputStream fis = new FileInputStream(fileName);
		
		//assuming xlsx file
		Workbook workbook = new XSSFWorkbook(fis);
		Sheet sheet = workbook.getSheetAt(0);
		Iterator<Row> rowIterator = sheet.iterator();
		while (rowIterator.hasNext()) 
        {
			Row row = rowIterator.next();
			Iterator<Cell> cellIterator = row.cellIterator();
            
            while (cellIterator.hasNext()) 
            {
            	Cell cell = cellIterator.next();
            	switch(cell.getCellType()){
            	case Cell.CELL_TYPE_NUMERIC:
            		System.out.println(cell.getNumericCellValue());
            		break;
            	case Cell.CELL_TYPE_FORMULA:
            		System.out.println("Cell Formula="+cell.getCellFormula());
            		System.out.println("Cell Formula Result Type="+cell.getCachedFormulaResultType());
            		if(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){
            			System.out.println("Formula Value="+cell.getNumericCellValue());
            		}
            	}
            }
        }
	}
	
	public static void main(String args[]) throws IOException {
		readExcelFormula("FormulaMultiply.xlsx");
	}
}

When we execute above apache poi example program, we get following output.

1.0
2.0
3.0
4.0
Cell Formula=A1*A2*A3*A4
Cell Formula Result Type=0
Formula Value=24.0

Apache POI Example - Excel Write Formula

Sometimes, we need to do some calculations and then write the cell values. We can use the excel formulas to do this calculation and that will make it more accurate because values will change if the cell values used in calculations are changed. Let’s see a simple example to write excel file with formulas using apache poi api. WriteExcelWithFormula.java

package com.journaldev.excel.read;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcelWithFormula {

	public static void writeExcelWithFormula(String fileName) throws IOException{
		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet("Numbers");
		Row row = sheet.createRow(0);
		row.createCell(0).setCellValue(10);
		row.createCell(1).setCellValue(20);
		row.createCell(2).setCellValue(30);
		//set formula cell
		row.createCell(3).setCellFormula("A1*B1*C1");
		
		//lets write to file
		FileOutputStream fos = new FileOutputStream(fileName);
		workbook.write(fos);
		fos.close();
		System.out.println(fileName + " written successfully");
	}
	
	public static void main(String[] args) throws IOException {
		writeExcelWithFormula("Formulas.xlsx");
	}
}

The excel file produced with above Apache POI API example program looks like below image. java write excel formula, apache poi, apache poi tutorial, apache poi example That’s all on Apache POI tutorial for working with excel files, look into Apache POI classes methods to learn more features of it. References: Apache POI Developers Guide

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products

About the author(s)

Pankaj Kumar
Pankaj Kumar
See author profile
Category:
Tutorial
Tags:

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
JournalDev
DigitalOcean Employee
DigitalOcean Employee badge
March 11, 2014

Hi Pankaj Ji,Good Afternoon.Thank You for giving this information about Java Read/Write Excel File using Apache POI API to us.It was very useful.Please keep up the Good Work.May GOD BLESS YOU.

- Madiraju Krishna Chaitanya

    JournalDev
    DigitalOcean Employee
    DigitalOcean Employee badge
    April 11, 2014

    Good tutorial. It is exactly what I was looking for!

    - JohnDoe

      JournalDev
      DigitalOcean Employee
      DigitalOcean Employee badge
      April 12, 2014

      Hi , Can we write a formula in a cell and get the value of that cell through poi .

      - Gobi C

      JournalDev
      DigitalOcean Employee
      DigitalOcean Employee badge
      April 12, 2014

      its already there, check “Java Read Excel Formula” section.

      - Pankaj

        JournalDev
        DigitalOcean Employee
        DigitalOcean Employee badge
        May 10, 2014

        I want to update one row with some forumla for that reason i’m loading whole file to read. When i’m trying to read 60MB xslx data file its throwing memery out of exectpion i have allocated 1024M heap memory. is there any other way to update the specific row in a file

        - Srinivas

        JournalDev
        DigitalOcean Employee
        DigitalOcean Employee badge
        May 10, 2014

        Try SXSSF API or POI event model for large files.

        - Pankaj

        JournalDev
        DigitalOcean Employee
        DigitalOcean Employee badge
        June 12, 2014

        I want to load large file, file having more than 10,000 rows, XSSFWorkbook is fail to load large file. SXSSFWorkbook how to use load large file. Can anyone give me example of SXSSFWorkbook? https://poi.apache.org/spreadsheet/how-to.html#sxssf, this is example of creation but don’t have for load and read cells.

        - Jay

        JournalDev
        DigitalOcean Employee
        DigitalOcean Employee badge
        September 3, 2014

        Even I am facing similar problem @ Jay: Please let me know if you have adapted any solution.

        - Prasad

          JournalDev
          DigitalOcean Employee
          DigitalOcean Employee badge
          May 14, 2014

          Hello! Quick question for you! I am getting java.lang.NoClassDefFoundError at line 20 in WriteListToExcelFile.java

          - Sandro

            JournalDev
            DigitalOcean Employee
            DigitalOcean Employee badge
            May 15, 2014

            tell best site to download jar files and also i have indigi eclipse b ut i dont have maven dependencies how to configure those jar files

            - channaveer

            JournalDev
            DigitalOcean Employee
            DigitalOcean Employee badge
            May 15, 2014

            check for your maven dependencies in maven central, you can easily search it and get the dependency details. URL: https://search.maven.org/

            - Pankaj

              JournalDev
              DigitalOcean Employee
              DigitalOcean Employee badge
              May 21, 2014

              Excellent article. I’m using HSSFWrokbook to write excel file where data fetched from database. One of the column value exceeds 32767 characters and application throws exception as xls file cell will not take beyond 32767 characters. Please let me know how to handle the situation, can’t restrict on user data.

              - Raghu

                JournalDev
                DigitalOcean Employee
                DigitalOcean Employee badge
                May 29, 2014

                Hi Pankaj, When I am adding the below dependancy(that yo have advised to add for POI api), it shows build failure for my existing maven project. Can you please help me on this regard. Error message: Failed to execute goal org.apache.maven.plugins:maven-surefire-plugin:2.10:test (default-test) on project myProject: org.apache.poi poi 3.10-FINAL org.apache.poi poi-ooxml 3.10-FINAL

                - Hruda

                JournalDev
                DigitalOcean Employee
                DigitalOcean Employee badge
                May 29, 2014

                I got the sollution. Poi has transitive dependency with xmlbeans and dom4j. so I have added the dependency for all and it works fine. So my pom.xml loks like this: org.apache.xmlbeans xmlbeans 2.4.0 dom4j dom4j 1.6.1 xml-apis xml-apis org.apache.poi poi 3.10-FINAL org.apache.poi poi-ooxml 3.10-FINAL

                - Hruda

                JournalDev
                DigitalOcean Employee
                DigitalOcean Employee badge
                May 29, 2014

                Thanks for posting the solution, it might help someone else. Usually Maven pulls the transitive dependencies itself, not sure why it didn’t happened with your case. There might be some other settings for this I think.

                - Pankaj

                  JournalDev
                  DigitalOcean Employee
                  DigitalOcean Employee badge
                  May 29, 2014

                  Hello Pankaj. The informatoin was useful, but I still have a questions that I want to ask you. I have a xlsx file, that has a number of sheets in and the cells has defined by name. The question is, how to write into cells by its names??? Thank you in advance!!!

                  - Rus

                    JournalDev
                    DigitalOcean Employee
                    DigitalOcean Employee badge
                    June 12, 2014

                    Hi, For you are using cell.getNumericCellValue() to read a formula cell value , that may not work probably for DATE formula. I have tested that and it result 0.0 value. It may only be catched by using FormulaEvaluator. see: https://stackoverflow.com/questions/19412346/apache-poi-reading-date-from-excel-date-function-gives-wrong-date

                    - Jnnese

                      JournalDev
                      DigitalOcean Employee
                      DigitalOcean Employee badge
                      June 18, 2014

                      I am gettign error org.apache not resolved. can you please guide on what to do. i have opened a normal java project and added the jarsa in the build path.

                      - Peter Gerald

                        JournalDev
                        DigitalOcean Employee
                        DigitalOcean Employee badge
                        July 7, 2014

                        Nice tutorial. However i cant get apache poi to work in eclipse… I have used jCreator to run the java code and it works, but when trying to run in an app i get the error. Too many method references, try --Multi Dex option. How do you fix this error. Can you also provide a tutorial for that?

                        - GadgetMan

                          JournalDev
                          DigitalOcean Employee
                          DigitalOcean Employee badge
                          August 1, 2014

                          Hi, I found this tutorial very helpful. I have a doubt though. I need to read a value from the excel cell and compare it. (Say i need to check whether the value lies betwen 1 and 10). How can I do that ? i have seen cell.getStringCellValue.,atches(String regEx) But not sure how to implement it.

                          - Justin

                          JournalDev
                          DigitalOcean Employee
                          DigitalOcean Employee badge
                          August 1, 2014

                          Get the cell value, change it to Integer using Integer.parseInt(“5”) and compare it.

                          - Pankaj

                          JournalDev
                          DigitalOcean Employee
                          DigitalOcean Employee badge
                          August 5, 2014

                          Thanks For your Reply Pankaj. I have another doubt. In a case Cell.CELL_TYPE_NUMERIC:, i have multiple cells to check. So i have written seperateif condition for each cell. I used cell.getStringCellValue().trim()for getting the value. First if condition is only for first cell.Secondif condition is only for second cell.Like that rest of the if conditions.No else statementis included. My doubt is that, when we move from one if condition to another, will the cells also gets incremented using cellIterator.next``. Or else do I have to increment the cell after every `if condition` ?``

                          - Justin

                            JournalDev
                            DigitalOcean Employee
                            DigitalOcean Employee badge
                            August 12, 2014

                            Hi i am done like this but i got a error like “Conversion to Dalvik format failed with error 2”. I don’t now how to solve that please help me. I need your help please

                            - sai

                              JournalDev
                              DigitalOcean Employee
                              DigitalOcean Employee badge
                              September 1, 2014

                              Hi Pankaj, I have data in excel file and work id and row id are have hsql ,but my requiremet is through there sork id and row id to get the excel data into another excel ,but huge amount data have in excel ,then what to do ,Please help me ,

                              - ram

                              JournalDev
                              DigitalOcean Employee
                              DigitalOcean Employee badge
                              September 1, 2014

                              Read the data into a List or Array of Objects and then sort it. For custom sorting you need to utilize Comparable or Comparator.

                              - Pankaj

                                JournalDev
                                DigitalOcean Employee
                                DigitalOcean Employee badge
                                September 3, 2014

                                I am getting OutOfMemory error even after providing 2GB heap space to process a Excel File of 10 MB size. The error is thrown while creating the Workbook instance. Any idea how to fix this issue? I can’t keep increasing -Xmx for heap size parameter as we cannot restrict the data in excel files. Please provide an alternative solution .

                                - Prasad

                                  JournalDev
                                  DigitalOcean Employee
                                  DigitalOcean Employee badge
                                  September 11, 2014

                                  HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(“Validation”); String formula = “IF(LEN($O$1:$O10) > 100, TRUE, $O$1:$O10)”; CellRangeAddressList addressList = new CellRangeAddressList(1,10, 14, 14); DVConstraint constrainlen = DVConstraint.createFormulaListConstraint(formula); HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, constrainlen); dataValidation.setEmptyCellAllowed(true); dataValidation.setShowPromptBox(true); dataValidation.setSuppressDropDownArrow(false); dataValidation.createErrorBox(“ERROR”, “Comments length should be less than 100 characters”); sheet.addValidationData(dataValidation); FileOutputStream fileOut = new FileOutputStream(“C:/Users/krishna-c/Documents/XLValidation.xls”); try { workbook.write(fileOut); fileOut.close(); } catch (IOException e) { e.printStackTrace(); } } i need to check the length in particular cell … if length is greater than 100 then display an error message. but its not working . any solution pls ?

                                  - krishna

                                    JournalDev
                                    DigitalOcean Employee
                                    DigitalOcean Employee badge
                                    September 16, 2014

                                    the app, doesnt find the file, what i have to do? i dont understand where the compilator search the file…

                                    - Cristobal

                                      JournalDev
                                      DigitalOcean Employee
                                      DigitalOcean Employee badge
                                      September 16, 2014

                                      java.io.FileNotFoundException: KubiiSushiVentas (El sistema no puede encontrar el archivo especificado) at java.io.FileInputStream.open(Native Method) at java.io.FileInputStream.(Unknown Source) at java.io.FileInputStream.(Unknown Source) at LeerDatos.leerDatos(LeerDatos.java:22) at LeerDatos.main(LeerDatos.java:94) Productos List

                                      - Cristobal

                                        JournalDev
                                        DigitalOcean Employee
                                        DigitalOcean Employee badge
                                        October 8, 2014

                                        Its funny to see people trying to explain EXCEL access via JAVA in year 2014…

                                        - Sardar Parbat Singh

                                        JournalDev
                                        DigitalOcean Employee
                                        DigitalOcean Employee badge
                                        December 29, 2014

                                        Why funny SmartAss ??

                                        - JC

                                          JournalDev
                                          DigitalOcean Employee
                                          DigitalOcean Employee badge
                                          October 9, 2014

                                          Hi, i got a problem using you example Java Read Excel Formula. eclipse cant find xssf while using apache poi 3.7 and then i cant use “Workbook workbook = new XSSFWorkbook(fis);” Any idea what i should do instead?

                                          - Jesper

                                            JournalDev
                                            DigitalOcean Employee
                                            DigitalOcean Employee badge
                                            October 17, 2014

                                            Hi Pankaj, I have a requirement to read Image and relevant information about the image present along the image. For example Person’s photo, his name,age,address etc. Is there a way in POI with which I can gather this information simultaneously. I know from workbook one can get all images but this will not solve my problem as I would not have any way to relate (club) the image with the information. Any suggestion would be appreciated.

                                            - Saurabh Thakur

                                              JournalDev
                                              DigitalOcean Employee
                                              DigitalOcean Employee badge
                                              October 27, 2014

                                              HI, Does POI provide column based read of the excel columns with the column name. id user password a Ram *** b Shyam **** c Dharma **** d P.Ram *** like, if we need to pick column by name rather than by its index value. Thanks & regards Kumar

                                              - Kumar

                                                JournalDev
                                                DigitalOcean Employee
                                                DigitalOcean Employee badge
                                                November 4, 2014

                                                Hi i am having an excel sheet input data, taking value from that excel and running my programme after the execution i want to increase the data value by 1 and store it in the same value. so that when i run the same programme next time the value will be different, so everytim i am passing new value to my programme. when I does it says the excel is already used by another programme and cannt write value in it. so can you pls help me.

                                                - khiran

                                                JournalDev
                                                DigitalOcean Employee
                                                DigitalOcean Employee badge
                                                November 5, 2014

                                                Are you closing all the IO resources? This can happen if you have file opened by any other program too.

                                                - Pankaj

                                                  JournalDev
                                                  DigitalOcean Employee
                                                  DigitalOcean Employee badge
                                                  November 10, 2014

                                                  Hi, I’m trying to use an existing excel template to create a formatted Xlsx output. any help regarding this ? Can i fix the column for few values and then pass the values from database ?

                                                  - srihari

                                                    JournalDev
                                                    DigitalOcean Employee
                                                    DigitalOcean Employee badge
                                                    December 29, 2014

                                                    This tutorial very useful. I use comment to mark cell to read file excel, and I want lock and hide these comments. Do you have any way to resolve this problem? Please help me, thanks!

                                                    - Giang Phan

                                                      JournalDev
                                                      DigitalOcean Employee
                                                      DigitalOcean Employee badge
                                                      October 4, 2015

                                                      Hello Pankaj ji, This is best explanation of this topic I ever found. thanks you very much.

                                                      - Rupesh

                                                        JournalDev
                                                        DigitalOcean Employee
                                                        DigitalOcean Employee badge
                                                        November 25, 2015

                                                        public static File exportToXLS(List givenList, List dataTmpltList) { } how to list all elements from database table…WEB_IN_OUT_TRX is my database table name DataExpTmplt is my Template class in that i have getType(),GetFieldValue(),GetColumnIndex();

                                                        - szh

                                                          JournalDev
                                                          DigitalOcean Employee
                                                          DigitalOcean Employee badge
                                                          March 23, 2016

                                                          Hello, How can I find user selected cells or active cells? Thank u

                                                          - Handsome

                                                            JournalDev
                                                            DigitalOcean Employee
                                                            DigitalOcean Employee badge
                                                            April 29, 2016

                                                            Good tutorial. It is exactly what I was looking for! But how if I have column name for each cell then I want the app to skip that column name. Just read the values only. Thanks.

                                                            - Hendi Santika

                                                            JournalDev
                                                            DigitalOcean Employee
                                                            DigitalOcean Employee badge
                                                            April 29, 2016

                                                            Good tutorial. It is exactly what I was looking for! But how if I have column name for each cell then I want the app to skip that column name. Just read the values only. Thanks. DONE Already solved.

                                                            - Hendi Santika

                                                              JournalDev
                                                              DigitalOcean Employee
                                                              DigitalOcean Employee badge
                                                              July 20, 2016

                                                              how can i loop through an excel sheet with so many entries, look for a unique value and print only that section where that unique value applies for example if i have payslips for employees generated on an excel sheet and i want to go reading for employee number and then print off that slip then look out for another one e.t.c

                                                              - Godfrey Kabunga

                                                                JournalDev
                                                                DigitalOcean Employee
                                                                DigitalOcean Employee badge
                                                                April 10, 2017

                                                                Thank you very much for tutorial. I am having same type of application where POI and POI-OOXML are used together. But when I deploy them in Maven environment, I get these errors: Caused by: org.osgi.framework.BundleException: Uses constraint violation. Unable to resolve resource com.icg.isg.returns.VendorReturnsTranslator [com.icg.isg.returns.VendorReturnsTranslator [327](R 327.0)] because it is exposed to package ‘org.apache.poi.ss.usermodel’ from resources wrap_mvn_org.apache.poi_poi-ooxml_3.15 [wrap_mvn_org.apache.poi_poi-ooxml_3.15 [325](R 325.0)] and wrap_mvn_org.apache.poi_poi_3.15 [wrap_mvn_org.apache.poi_poi_3.15 [324](R 324.0)] via two dependency chains. Chain 1: com.icg.isg.returns.VendorReturnsTranslator [com.icg.isg.returns.VendorReturnsTranslator [327](R 327.0)] import: (osgi.wiring.package=org.apache.poi.ss.usermodel) | export: osgi.wiring.package: org.apache.poi.ss.usermodel wrap_mvn_org.apache.poi_poi-ooxml_3.15 [wrap_mvn_org.apache.poi_poi-ooxml_3.15 [325](R 325.0)] Chain 2: com.icg.isg.returns.VendorReturnsTranslator [com.icg.isg.returns.VendorReturnsTranslator [327](R 327.0)] import: (osgi.wiring.package=org.apache.poi.hssf.usermodel) | export: osgi.wiring.package: org.apache.poi.hssf.usermodel; uses:=org.apache.poi.ss.usermodel export: osgi.wiring.package=org.apache.poi.ss.usermodel " Any help to resolve is appreciated.

                                                                - Chintan Desai

                                                                  JournalDev
                                                                  DigitalOcean Employee
                                                                  DigitalOcean Employee badge
                                                                  April 25, 2017

                                                                  Hello, I am struggling to figure out the solution to my problem. I have an excel sheet .Xlsx with a pie chart and I want to copy this image to my another excel (sheet2). Will it possible using XSSFWorkbook in Apache poi? I went through many blogs but no help. Please suggest something…

                                                                  - Shweta

                                                                    JournalDev
                                                                    DigitalOcean Employee
                                                                    DigitalOcean Employee badge
                                                                    July 24, 2018

                                                                    Can I create CheckBox through POI

                                                                    - sunish

                                                                      JournalDev
                                                                      DigitalOcean Employee
                                                                      DigitalOcean Employee badge
                                                                      March 12, 2019

                                                                      hello, I have requirement of reading the data which is only marked true in checkbox and also the only data which is selected in dropdown list in an excel file to another excel file. Can anyone please help me with this?

                                                                      - shraddha21

                                                                        JournalDev
                                                                        DigitalOcean Employee
                                                                        DigitalOcean Employee badge
                                                                        July 7, 2019

                                                                        Sir/mam kindly help me. What if i want to compare two excel Sheets data with uneven no.of rows using apache poi in java netbeans, and has to displayed automatically in new spreadsheet. I found some method related to it but their have compared excel sheets which has even no.of rows in it or can i use Comparison related API available for java, if i use comparison related API whether apache poi API will support or error will occur. Please help with this issue. Thank you in advance

                                                                        - Annapoorani

                                                                          JournalDev
                                                                          DigitalOcean Employee
                                                                          DigitalOcean Employee badge
                                                                          November 21, 2019

                                                                          Hi , I have an issue . I want to write a code to populate a restricted excel sheet in which all the cells in excel must only contain the allowed values as drop down list ( as checkboxes i.e user can select more than one value from the drop down list to fill the excel sheet .So user have to select the multiple values from the list only. He cannot write something else) .

                                                                          - Sumit Mamtani

                                                                            JournalDev
                                                                            DigitalOcean Employee
                                                                            DigitalOcean Employee badge
                                                                            December 9, 2019

                                                                            package javaexcel; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; public class excelsheet { public static void main(String[] args) throws IOException { String path=“C:\\Users\\kjagtap\\OneDrive - TASI\\Desktop\\capture\\Book1.xlsx”; FileInputStream file = new FileInputStream(path); Workbook book= WorkbookFactory.create(file); Sheet s= book.getSheet(“sample”); Row row= s.getRow(0); Cell cell= row.getCell(0); String str=cell.getStringCellValue(); System.out.print(str); } } I have written this code but occurring lang null pointer exception on row class, can you please help me to handle this exception

                                                                            - kajol

                                                                              JournalDev
                                                                              DigitalOcean Employee
                                                                              DigitalOcean Employee badge
                                                                              January 2, 2020

                                                                              Hi Sir, i have a requirement like i have to design a excel sheet with 2 headers and my 1st header is merged with two columns and 2nd header as like single column single header. kindly can you give me some solutions for this

                                                                              - Bikash Sahoo

                                                                                JournalDev
                                                                                DigitalOcean Employee
                                                                                DigitalOcean Employee badge
                                                                                February 6, 2020

                                                                                Hi Pankaj, Nice tutorial. I want to create a multiple sheets in same workbook for a different dropdown option. As soon as i select the option from dropdown data get populate and that has to be written in Excel file and then select a second value say ‘B’ from drop down and should create sheet’B’. For selecting a option from dropdown i have a method public void selectAProviderOption() throws Exception { providerOption.clear(); providerOption.click(); providerOption.sendKeys(“aurora”); Thread.sleep(3000); driver.findElement(By.xpath(“//*[text()=’ Aurora’]”)).click(); providerOption.sendKeys(Keys.ENTER); } I do have 6 methods for selecting the option from dropdown, for each option each method. public void clickOnPerformanceDetails() throws Exception { File file = new File(“C:\\Users\\pdholakia\\Desktop\\PCPPerformanceAtNavvisLevel_NO.xlsx”); XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(new FileInputStream(file)); XSSFSheet sheet = wb.createSheet(" Scott"); // XSSFSheet sheet = wb.createSheet(“Khen”); // XSSFSheet sheet = wb.createSheet(“Tanii”); // XSSFSheet sheet = wb.createSheet(“Aurora”); // Excel code here } @Test{ performance_PlusTWOEx.selectAuroraProviderOption() ; performance_PlusTWOEx.selectProgram(); performance_PlusTWOEx.clickOnPerformanceDetails(); // performance_PlusTWOEx.selectBBProviderOption() ; // performance_PlusTWOEx.selectProgram(); // performance_PlusTWOEx.clickOnPerformanceDetails(); } Above code running successfully creating multiple sheets but i do have to comment all other 5 options if one of option is getting executing. Commenting creatsheet code and commenting methods of testclass. i am working with maven project using POM pattern and testng. Any help will be appreciated.

                                                                                - Purvi

                                                                                  Join the Tech Talk
                                                                                  Success! Thank you! Please check your email for further details.

                                                                                  Please complete your information!

                                                                                  Become a contributor for community

                                                                                  Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

                                                                                  DigitalOcean Documentation

                                                                                  Full documentation for every DigitalOcean product.

                                                                                  Resources for startups and SMBs

                                                                                  The Wave has everything you need to know about building a business, from raising funding to marketing your product.

                                                                                  Get our newsletter

                                                                                  Stay up to date by signing up for DigitalOcean’s Infrastructure as a Newsletter.

                                                                                  New accounts only. By submitting your email you agree to our Privacy Policy

                                                                                  The developer cloud

                                                                                  Scale up as you grow — whether you're running one virtual machine or ten thousand.

                                                                                  Get started for free

                                                                                  Sign up and get $200 in credit for your first 60 days with DigitalOcean.*

                                                                                  *This promotional offer applies to new accounts only.