What exactly does need for POI Excel API

Question

I'm working on program that make Excel data via POI API 3.9 .

but there are many Jar files in directory as follows.

poi-3.9-20121203 
poi-excelant-3.9-20121203  
poi-ooxml-3.9-20121203 
poi-ooxml-schemas-3.9-20121203 
poi-scratchpad-3.9-20121203 
ooxml-lib/xmlbeans-2.3.0  
lib/commons-logging-1.1 
..etc.

I just need creating, reading and writing excel files . i'm confused what jar files is need.

please tell me what files i got to import. I have read many domcuments on website . But i'can find it about that.

Thank you

score 4 · Answer 1 · edited Jul 23 '13 at 10:19

You probably have to import all of them. As many frameworks, Apache POI is splitted in different libraries. Apache POI also uses external libraries like XML Beans.

On the Apache POI website, you will find the list of components and for which goal there are needed, look at the Component Map section.

Typically if you are handling only old Excel files, you don't need the poi-ooxml dependency.

Note that your list of external dependencies is listed in the Prerequisites column of the second table of the Component Map section, as described there you also need additional libraries: commons-logging, commons-codec, log4j.

Finally, to avoid headaches with dependency management, you could use a tool like Maven which cares of this for you.

score 3 · Answer 2 · answered Jul 23 '13 at 09:05

3

Don't worry about your dependencies too much in your case. Just use a tool like Maven or Gradle, and add the POI dependency:

Maven:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

Gradle

'org.apache.poi:poi:3.9'

This will give you the right dependencies.

answered Jul 23 '13 at 09:05

Erik Pragt

13,513
11
58
64

I've never heard like that tool .Thank you ! – dskim Jul 23 '13 at 09:16
You're welcome. And I'd really recommend using a tool like this, it will save you from tons of issues! – Erik Pragt Jul 23 '13 at 11:13

score 2 · Answer 3 · answered Jul 23 '13 at 09:16

2

You need just 5 jars for your mentioned tasks

for example if we are using apache 3.9

dom4j-1.6.1.jar
poi-3.9-20121203.jar
poi-ooxml-3.9-0121203.jar
poi-ooxml-schemas-3.9-0121203.jar
xmlbeans-2.3.0.jar

answered Jul 23 '13 at 09:16

Sankumarsingh

9,889
11
50
74

dom4j definitely not needed anymore – Kalpesh Soni Oct 17 '16 at 16:30

score 1 · Answer 4 · answered Jul 23 '13 at 09:18

If you use Maven for project build, then you can simply add the following two dependencies on your project -

<!-- Apache POI -->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.9</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.9</version>
</dependency>

This will download all the required jars for you.

Here is a demo project that I've developed demonstrating the excel reading approach. The above lines are taken from my project pom. This approach works just fine for me.

Thanks, I'll try to in my project – dskim Jul 24 '13 at 06:57 — dskim, Jul 24 '13 at 06:57

score 1 · Answer 5 · answered Jul 25 '13 at 01:57

(1)Hi, here I am posting a link where you will get all the information related to your question.

http://selenium-testng.com/test-data-through-excel/

(2)You can download all the required jars from this URL

http://poi.apache.org/download.html

(3)extract the URL and include all the jars in your classPath

(4)For MS Excel Examples you can check the following sites. It has all the examples required.

http://poi.apache.org/spreadsheet/quick-guide.html

score 0 · Answer 6 · answered Jul 23 '13 at 09:06

For standalone project you need the below jars.

commons-logging-1.1.jar

dom4j-1.6.1.jar

jsr173_1.0_api.jar

log4j-1.2.13.jar

poi-3.8-20120326.jar

poi-ooxml-3.8-20120326.jar

poi-ooxml-schemas-3.8-20120326.jar

resolver.jar

xbean_xpath.jar

xbean.jar

xmlbeans-qname.jar

xmlpublic.jar

score 0 · Answer 7 · answered May 23 '16 at 13:37

0

You can use ver 3.9 or ver 3.8 instead. But I find that poi ver.3.9 remove some class(org.apache.poi.xssf.usermodel.XSSFWorkbook for example) which till provides on poi-3.8-20120326.jar. So verify if those class used on your project.

Hope this help!

answered May 23 '16 at 13:37

Huy Hóm Hỉnh

597
7
18

Apache POI 3.9 is several years old, why on earth are you suggesting to people to use such an old version? – Gagravarr May 23 '16 at 13:54
uhmm, you are right but this not really an advice about up-to-date version. This is the warning about library changing among versions. Maybe someone till use an-several-years-old version and met this trouble like me. Hope this can help them! – Huy Hóm Hỉnh May 24 '16 at 03:31

score 0 · Answer 8 · answered Apr 15 '17 at 10:51

Thanks to the below link

http://www.mysamplecode.com/2011/10/android-read-write-excel-file-using.html

Worked for me :)

Working Edited Code:

public static boolean isExternalStorageReadOnly() {
    String extStorageState = Environment.getExternalStorageState();
    if (Environment.MEDIA_MOUNTED_READ_ONLY.equals(extStorageState)) {
        return true;
    }
    return false;
}

public static boolean isExternalStorageAvailable() {
    String extStorageState = Environment.getExternalStorageState();
    if (Environment.MEDIA_MOUNTED.equals(extStorageState)) {
        return true;
    }
    return false;
}
private static boolean saveExcelFile(MainActivity mainActivity, String fileName) {

        // check if available and not read only
        if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) {
            Log.w("FileUtils", "Storage not available or read only");
            return false;
        }

        boolean success = false;

        //New Workbook
        Workbook wb = new HSSFWorkbook();

        Cell c = null;

        //Cell style for header row
        CellStyle cs = wb.createCellStyle();
        cs.setFillForegroundColor(HSSFColor.LIME.index);
        cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        //New Sheet
        Sheet sheet1 = null;
        sheet1 = wb.createSheet("Sheet1");

        // Generate column headings
        Row row = sheet1.createRow(0);

        c = row.createCell(0);
        c.setCellValue(DBHelper.INVENTORY_PROJECT_CODE);
        c.setCellStyle(cs);

        c = row.createCell(1);
        c.setCellValue(DBHelper.INVENTORY_PROJECT_UPC_BAR_CODE);
        c.setCellStyle(cs);

        c = row.createCell(2);
        c.setCellValue(DBHelper.INVENTORY_PROJECT_NAME);
        c.setCellStyle(cs);

        c = row.createCell(3);
        c.setCellValue(DBHelper.INVENTORY_PROJECT_QUANTITY);
        c.setCellStyle(cs);

        c = row.createCell(4);
        c.setCellValue(DBHelper.INVENTORY_PROJECT_PRICE);
        c.setCellStyle(cs);


        ArrayList<String> arrayList1 = dbHelper.getAllRecords();

        int rowno = 1;
    int columnno=0;
            for(String red : arrayList1){
                columnno = 0;
                Row row1 = sheet1.createRow(rowno);
                String[] parts = red.split(Pattern.quote(","));
                for(String str : parts){
                    c = row1.createCell(columnno);
                    c.setCellValue(str);
                    c.setCellStyle(cs);
                    columnno++;
                }
                rowno++;
            }

        sheet1.setColumnWidth(0, (15 * 500));
        sheet1.setColumnWidth(1, (15 * 500));
        sheet1.setColumnWidth(2, (15 * 500));
        sheet1.setColumnWidth(3, (15 * 500));
        sheet1.setColumnWidth(4, (15 * 500));


        // Create a path where we will place our List of objects on external storage
        File file = new File(mainActivity.getExternalFilesDir(null), fileName);
        FileOutputStream os = null;

        try {
            os = new FileOutputStream(file);
            wb.write(os);
            Log.w("FileUtils", "Writing file" + file);
            success = true;
        } catch (IOException e) {
            Log.w("FileUtils", "Error writing " + file, e);
        } catch (Exception e) {
            Log.w("FileUtils", "Failed to save file", e);
        } finally {
            try {
                if (null != os)
                    os.close();
            } catch (Exception ex) {
            }
        }

        return success;


}

What exactly does need for POI Excel API

8 Answers8