How to read excel(.xlsx) in java using poi?

Question

I am trying to read excel in java.I have following code.

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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 Test {
    public static void main(String[] args) throws IOException {
        String fname = "D:\\Test.xlsx"; // or "C:\\Test.xls" C:\\SDI-XL.xls
        InputStream inp = new FileInputStream(fname);
        Workbook  wb = new XSSFWorkbook(inp); // Declare XSSF WorkBook
        Sheet sheet = wb.getSheetAt(0); // sheet can be used as common for XSSF and HSSF

        Iterator<Row> rows=sheet.rowIterator();
        while (rows.hasNext()) {
            Row row = (Row) rows.next();
            System.out.println("row#=" + row.getRowNum() + "");
            Iterator cells = row.cellIterator();
            while (cells.hasNext()) {
                Cell cell = (Cell) cells.next();

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.println(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        System.out.println(cell.getDateCellValue() + "");
                    } else {
                        System.out.println(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.println(cell.getBooleanCellValue() + "");
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    System.out.println(cell.getCellFormula());
                    break;
                default:
                }
            }
        }
        inp.close();
    }
}

I was import the poi.3.6jar and poi.ooxml-3.6 jar. When i am run this program i got following error message.

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException
    at Test.main(Test.java:16)
Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException
    at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:423)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:356)
    ... 1 more

I dont get it why this error message come.So plz help me.

See also this related [example](http://stackoverflow.com/a/3562214/230513) using [_for-each_](http://docs.oracle.com/javase/1.5.0/docs/guide/language/foreach.html) loops. — trashgod, May 03 '13 at 11:31

score 13 · Answer 1 · edited Dec 18 '13 at 10:13

13

Add following jar files:

poi-3.9.jar
poi-ooxml-3.9.jar
poi-ooxml-schemas-3.7.jar
xmlbeans-2.3.0.jar
dom4j-1.6.1.jar

edited Dec 18 '13 at 10:13

Andrea

11,801
17
65
72

answered Dec 18 '13 at 09:55

Vinay Ahire

131
1
2

You need all the above jars to read/write excel file in java. You will end up in run time errors even though one jar is missing. – Jagadish Talluri Dec 01 '15 at 11:21

score 11 · Accepted Answer · answered May 03 '13 at 11:25

11

add xmlbeans-2.3.0.jar file to your classpath.

answered May 03 '13 at 11:25

NPKR

5,368
4
31
48

I import that jar .but i got following error msg..Exception in thread "main" java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheet at Test.main(Test.java:16) Caused by: java.lang.ClassNotFoundException: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet – vijayk May 03 '13 at 11:37
7

you're missing poi-ooxml-schemas.jar, please at least google such errors; the jar is the first hit here. – mabi May 03 '13 at 11:40
1

Thanks for your help, it worked when I included poi-ooxml-schemas.jar as suggested by @mabi and dom4j.jar. – vijayk May 03 '13 at 11:52
can you please told me how to read specific row in the excel? – vijayk May 03 '13 at 11:55
refer this link http://stackoverflow.com/questions/10630749/how-to-read-a-specific-row-from-the-excel-based-some-unique-value-in-java-using – NPKR May 03 '13 at 11:58
3

[Apache POI](http://poi.apache.org/) provides a handy list of [Components and their dependencies](http://poi.apache.org/overview.html#components) on the website, which explain which jars you need for what! – Gagravarr May 03 '13 at 13:24

score 1 · Answer 3 · edited Oct 31 '14 at 10:43

1

Add

xmlbeans-2.3.0.jar
dom4j-1.6.1.jar

along with regular POI XMLs, it will surely solve the issue.

edited Oct 31 '14 at 10:43

tomrozb

25,773
31
101
122

answered Oct 31 '14 at 10:26

Asit

11
1

score 0 · Answer 4 · answered Oct 12 '18 at 10:48

Use this : Input String filePath Output is list of list of json objects

Have these dependencies
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-collections4</artifactId>
        <version>4.0</version>
    </dependency>


/**
 * Function reads the input excel and gives list(sheet) of objects(rows of each sheet)
 * @param filePath
 * @return list of list of json objects
 */
public static List<List> excelFileReader(String filePath){

    List<List>totalSheetList=new ArrayList<>();
    XSSFWorkbook workbook= null;
    try {
        workbook = new XSSFWorkbook(filePath);
    } catch (IOException e) {
        e.printStackTrace();
    }

    //iteration for sheets in a workbook
    for(int sheetIndex=0;sheetIndex<workbook.getNumberOfSheets();sheetIndex++){
        XSSFSheet sheet=workbook.getSheetAt(sheetIndex);
        XSSFRow row=null;
        List<JSONObject> singleSheetList=new ArrayList<>();

        //excel header - first row and non-empty
        String[] header=new String[sheet.getRow(0).getPhysicalNumberOfCells()];
        if (sheet.getPhysicalNumberOfRows()!=0 && sheet.getRow(0).getRowNum()==0) {
            row=sheet.getRow(0);
            for(int index=0;index<row.getPhysicalNumberOfCells();index++){
                header[index]=(row.getCell(index).getStringCellValue());
            }
        }

        //if header exists , start reading the values excluding first row (header)
        if(header.length!=0){
            for(int rowIndex=1;rowIndex<sheet.getPhysicalNumberOfRows();rowIndex++){
                row=sheet.getRow(rowIndex);
                HashMap<String,Object> eachRow=new HashMap<>();
                for(int colIndex=0;colIndex<header.length;colIndex++) {
                    String cell = "";
                    if(row.getCell(colIndex)==null){
                        cell="";
                    }else{
                        if(row.getCell(colIndex).getCellType()==Cell.CELL_TYPE_STRING){
                            cell = row.getCell(colIndex).getRichStringCellValue().toString();
                        }else{
                            row.getCell(colIndex).setCellType(Cell.CELL_TYPE_STRING);
                            cell=row.getCell(colIndex).getStringCellValue();
                        }
                    }
                    eachRow.put(header[colIndex], cell);
                }
                JSONObject eachRowJsonObject = new JSONObject(eachRow);
                singleSheetList.add(eachRowJsonObject);
            }
        }
        totalSheetList.add(singleSheetList);
    }
    return totalSheetList;
}

score -1 · Answer 5 · answered Feb 27 '17 at 10:13

-1

Add following jars and add them to your classpath then run your project.

dom4j-1.6.1-sources.jar
dom4j.jar
log4j-1.2.17.jar
poi-3.5-FINAL.jar
poi-ooxml-3.5-beta5.jar
poi-ooxml-schemas-3.9.jar
xmlbeans-2.3.0.jar

answered Feb 27 '17 at 10:13

Saad Bin Iqbal

65
7

As per http://poi.apache.org/faq.html#faq-N10204 this is *not supported* as you're mixing POI jars between versions. Also, why on earth are you suggesting to someone today to use such old versions of POI? – Gagravarr Feb 27 '17 at 14:07
I tried with all recommended jars with latest version but did not work. So I tried all above jars and It works now. – Saad Bin Iqbal Mar 01 '17 at 04:22
Add below line in your main method to not to get exception ->BasicConfigurator.configure(); – Saad Bin Iqbal Mar 01 '17 at 04:25

How to read excel(.xlsx) in java using poi?

5 Answers5

Linked