Read write data in excel using java and apache POI

Question

I am trying to read and write data to excel using Apache POI files. I have imported the necessary jars namely: poi.4.1.1.jar xmlbeans poi-ooxml poi-ooxml-schemas

I have below code written currently which is failing at the line 'Workbook wb = WorkbookFactory.create(fis);':

package readingData;

import java.io.FileInputStream;
import java.io.FileOutputStream;

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 ReadDatafromExcel {

    private static Sheet sh;
    private static Row row;
    private static Cell cell;
    private static FileInputStream fis;
    private static FileOutputStream fos;

    public static void main(String[] args) throws Exception {
        fis = new FileInputStream("C:/Users/10610985/workspace/ReadData/SampleData.xlsx");
        Workbook wb = WorkbookFactory.create(fis);
//      sh = wb.getSheetAt(0);
////        int firstRow = sh.getFirstRowNum();
////        System.out.println(firstRow);
//      
//      int lastRow = sh.getLastRowNum();
//      System.out.println(lastRow);
    }
}

Below is the error message i get:

Exception in thread "main" java.io.IOException: org/apache/commons/compress/archivers/zip/ZipFile
    at org.apache.poi.ss.usermodel.WorkbookFactory.createWorkbook(WorkbookFactory.java:353)
    at org.apache.poi.ss.usermodel.WorkbookFactory.createXSSFWorkbook(WorkbookFactory.java:316)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:234)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:200)
    at readingData.ReadDatafromExcel.main(ReadDatafromExcel.java:22)
Caused by: java.lang.NoClassDefFoundError: org/apache/commons/compress/archivers/zip/ZipFile
    at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:307)
    at org.apache.poi.xssf.usermodel.XSSFWorkbookFactory.createWorkbook(XSSFWorkbookFactory.java:134)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.poi.ss.usermodel.WorkbookFactory.createWorkbook(WorkbookFactory.java:341)
    ... 4 more
Caused by: java.lang.ClassNotFoundException: org.apache.commons.compress.archivers.zip.ZipFile
    at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:355)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:351)
    ... 11 more

I tried to search the net for this but there was no solution I can find around. Any ideas on where I am going wrong, or if there is anything i can do to understand what are the various methods in Workbookfactory.

Below is the updated error message after adding apache common compress jars.

Exception in thread "main" java.io.IOException: org/apache/commons/collections4/ListValuedMap
    at org.apache.poi.ss.usermodel.WorkbookFactory.createWorkbook(WorkbookFactory.java:353)
    at org.apache.poi.ss.usermodel.WorkbookFactory.createXSSFWorkbook(WorkbookFactory.java:316)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:234)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:200)
    at readingData.ReadDatafromExcel.main(ReadDatafromExcel.java:22)
Caused by: java.lang.NoClassDefFoundError: org/apache/commons/collections4/ListValuedMap
    at org.apache.poi.xssf.usermodel.XSSFWorkbookFactory.createWorkbook(XSSFWorkbookFactory.java:88)
    at org.apache.poi.xssf.usermodel.XSSFWorkbookFactory.createWorkbook(XSSFWorkbookFactory.java:135)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.poi.ss.usermodel.WorkbookFactory.createWorkbook(WorkbookFactory.java:341)
    ... 4 more
Caused by: java.lang.ClassNotFoundException: org.apache.commons.collections4.ListValuedMap
    at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:355)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:351)
    ... 11 more

This was solved

added apache common compress jars from here. https://commons.apache.org/proper/commons-compress/download_compress.cgi

added common collection 4-4.1 jar from here. https://mvnrepository.com/artifact/org.apache.commons/commons-collections4/4.1

ZipFile (Apache Commons Compress 1.19 API) check if this jar exist in your project — Rajendra Gupta
– Rajendra Gupta, Commented Dec 2, 2019 at 11:55
Have you tried to use a differently formatted String as path? Like fis = new FileInputStream("C:\\Users\\10610985\\workspace\\ReadData\\SampleData.xlsx");? — deHaar
– deHaar, Commented Dec 2, 2019 at 11:56
Too bad, I was working with java.nio.Paths and those are dealing with the double backslashes. — deHaar
– deHaar, Commented Dec 2, 2019 at 12:27
@RajendraGupta - I tried referencing those jars and now got a new message which i edited in the main question. — MitSM
– MitSM, Commented Dec 2, 2019 at 12:29

Rajendra Gupta · Accepted Answer · 2019-12-02 12:37:40Z

1

Add commons-collections4-4.1.jar file in your build path and try it again. It will work.

You can find it here https://mvnrepository.com/artifact/org.apache.commons/commons-collections4/4.1

if you are not using maven you can directly download the jar and add it to your project

answered Dec 2, 2019 at 12:37

Rajendra Gupta

3791 silver badge16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nani · Accepted Answer · 2019-12-02 12:38:19Z

  FileOutputStream fileOut = new FileOutputStream("your file path");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("sheet name");
            try {

    //creating the headers          
                Row row = sheet.createRow(0);
                row.createCell(0).setCellValue("name");
                row.createCell(1).setCellValue("Name1");                    
                row.createCell(2).setCellValue("name2");

    //get the list which u want
                List<String> list = getNamesList();                         
            int rowNum = 1;
            for (Name name : list ) {
                Row row1 = sheet.createRow(rowNum++);
        row1.createCell(0).setCellValue((name.get..));

}
                workbook.write(fileOut);
}
shareeditdeleteflag

    enter code here

Collectives™ on Stack Overflow

Read write data in excel using java and apache POI

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related