1

I am using Apache Poi in an Android application, but I have a problem with the HSSFWorkbook.

I just want to read an Excel file found in the internal memory of the Android tablet. (To display data in Android application)

my code: 

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);



try {
    AssetManager assetManager = this.getAssets();
    InputStream input = assetManager.open("test42.XLS");

    HSSFWorkbook classeur = new HSSFWorkbook(input);

    //HSSFSheet feuille = classeur.getSheetAt(0);

    //Cell cell = feuille.getRow(1).getCell(1);

    //String value = cell.getStringCellValue();

    TextView tv = new TextView(this);


    tv.setText("Test");

    setContentView(tv);

} catch (IOException e) {
    e.printStackTrace();
    System.out.println("prob");

}

}

The log :

01-28 14:09:55.036: E/dalvikvm(6919): Could not find class 'org.apache.xmlbeans.XmlOptions', referenced from method org.apache.poi.POIXMLDocumentPart.<clinit>

01-28 14:09:55.036: W/dalvikvm(6919): VFY: unable to resolve new-instance 2751 (Lorg/apache/xmlbeans/XmlOptions;) in Lorg/apache/poi/POIXMLDocumentPart;

01-28 14:09:55.036: D/dalvikvm(6919): VFY: replacing opcode 0x22 at 0x0008

01-28 14:09:55.056: E/AndroidRuntime(6919): FATAL EXCEPTION: main

01-28 14:09:55.056: E/AndroidRuntime(6919): java.lang.ExceptionInInitializerError

01-28 14:09:55.056: E/AndroidRuntime(6919):     at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:72)

01-28 14:09:55.056: E/AndroidRuntime(6919):     at android.app.Activity.performCreate(Activity.java:5267)

01-28 14:09:55.056: E/AndroidRuntime(6919):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1097)

01-28 14:09:55.056: E/AndroidRuntime(6919):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2209)

01-28 14:09:55.056: E/AndroidRuntime(6919):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2295)

01-28 14:09:55.056: E/AndroidRuntime(6919):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1280)

01-28 14:09:55.056: E/AndroidRuntime(6919):     at java.lang.reflect.Method.invokeNative(Native Method)

the new log cat :

01-29 10:00:44.809: I/dalvikvm(7833): Could not find method org.apache.commons.codec.digest.DigestUtils.md5, referenced from method org.apache.poi.hssf.usermodel.HSSFWorkbook.addPicture

01-29 10:00:44.809: W/dalvikvm(7833): VFY: unable to resolve static method 6355: Lorg/apache/commons/codec/digest/DigestUtils;.md5 ([B)[B

01-29 10:00:44.809: D/dalvikvm(7833): VFY: replacing opcode 0x71 at 0x0004

01-29 10:00:44.829: W/System.err(7833): java.io.IOException: Invalid header signature; read 0x0010000000060409, expected 0xE11AB1A1E011CFD0

01-29 10:00:44.829: W/System.err(7833):     at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:140)

01-29 10:00:44.829: W/System.err(7833):     at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:104)

01-29 10:00:44.829: W/System.err(7833):     at 

org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:138)
01-29 10:00:44.829: W/System.err(7833):     at 


org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:322)
01-29 10:00:44.829: W/System.err(7833):     at 

org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:303)

01-29 10:00:44.829: W/System.err(7833):     at com.example.test.MainActivity.onCreate(MainActivity.java:41)

01-29 10:00:44.829: W/System.err(7833):     at android.app.Activity.performCreate(Activity.java:5267)

01-29 10:00:44.829: W/System.err(7833):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1097)


01-29 10:00:44.829: W/System.err(7833):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2209)

01-29 10:00:44.829: W/System.err(7833):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2295)

01-29 10:00:44.829: W/System.err(7833):     at android.app.ActivityThread.access$700(ActivityThread.java:150)


01-29 10:00:44.829: W/System.err(7833):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1280)

01-29 10:00:44.829: W/System.err(7833):     at android.os.Handler.dispatchMessage(Handler.java:99)

01-29 10:00:44.829: W/System.err(7833):     at 
android.os.Looper.loop(Looper.java:176)

01-29 10:00:44.829: W/System.err(7833):     at android.app.ActivityThread.main(ActivityThread.java:5279)

01-29 10:00:44.829: W/System.err(7833):     at java.lang.reflect.Method.invokeNative(Native Method)

01-29 10:00:44.829: W/System.err(7833):     at java.lang.reflect.Method.invoke(Method.java:511)


01-29 10:00:44.829: W/System.err(7833):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1102)

01-29 10:00:44.829: W/System.err(7833):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:869)

01-29 10:00:44.829: W/System.err(7833):     at dalvik.system.NativeStart.main(Native Method)


01-29 10:00:44.899: W/ResourceType(7833): Failure getting entry for 0x01080a03 (t=7 e=2563) in package 0 (error -75)

01-29 10:00:44.999: D/libEGL(7833): loaded /vendor/lib/egl/libEGL_POWERVR_SGX544_115.so

01-29 10:00:45.049: D/libEGL(7833): loaded /vendor/lib/egl/libGLESv1_CM_POWERVR_SGX544_115.so

01-29 10:00:45.059: D/libEGL(7833): loaded /vendor/lib/egl/libGLESv2_POWERVR_SGX544_115.so

01-29 10:00:45.129: D/OpenGLRenderer(7833): Enabling debug mode 0
user3244162
  • 101
  • 1
  • 7

3 Answers3

1
 WorkbookFactory.create(fis);

use Workboook factory to create Workoook.

see section Files vs InputStreams here

Gaurav Gupta
  • 4,586
  • 4
  • 39
  • 72
  • I use "Workbook wb = WorkbookFactory.create(input);" but but my application crashes – user3244162 Jan 28 '14 at 12:43
  • I think I've found a problem I use an InputStream instead of a FileInputStream but I do not know how to have a FileInputStream – user3244162 Jan 28 '14 at 13:50
  • 1
    Are you reading `.xlsx` file? For getting FileInputStream you can try something like following" `File yourFile = new File("yourFilePath"); FileInputStream fis = new FileInputStream(yourFile);` – Gaurav Gupta Jan 28 '14 at 13:55
  • The file is located in the internal memory of my Android tablet, how can I have the exact path of the file? – user3244162 Jan 28 '14 at 14:01
1

try some thing like the following for the xls files : 

   try {
                InputStream caInput = new BufferedInputStream(getAssets().open(
                        "ie_data.xls"));
                HSSFWorkbook workbook = new HSSFWorkbook(caInput);
                HSSFSheet feuille = workbook.getSheetAt(0);

                Cell cell = feuille.getRow(1).getCell(1);

                String value = cell.getNumericCellValue()+"";
                Toast.makeText(getApplicationContext(), value, Toast.LENGTH_LONG)
                        .show();
            } catch (IOException e1) {
                // TODO Auto-generated catch block


e1.printStackTrace();
        }

and for xlsx :

1st - import the follwoing jar files :

(dom4j-1.6.1.jar , poi-ooxml-3.5-FINAL.jar , poi-ooxml-schemas-3.8-beta4.jar , poi-3.9-20121203.jar , xmlbeans-2.3.0.jar).

2nd - do the follwoing inside a try catch block :

if you are using a File object do the follwoing : 

File file = new File("path");



        InputStream caInput = new FileInputStream(file);

if you are reading the file using inputStram do the following : 

InputStream caInput = new BufferedInputStream(getAssets().open(
                            "ie_data.xlsx"));

            XSSFWorkbook workbook = new XSSFWorkbook(caInput);
            XSSFSheet feuille = workbook.getSheetAt(0);

            Cell cell = feuille.getRow(0).getCell(0);

            String value = cell.getStringCellValue() + "";

            System.out.print("hello = " + value);

but please notice i am getting a StringCellValue according to my xslx file , you should check your xlsx file and see if its a string and use getStringCellValue() or a Numeric and use getNumericCellValue() .

please give me some feedback

Hope that helps .

  • I put "NPOIFSFileSytem fs = new NPOIFSFileSystem(assetManager.open("test42.XLS"));" in my code, but Eclipse tell me : NPOIFSFileSytem cannot be resolved to a Type. – user3244162 Jan 28 '14 at 12:16
  • import org.apache.poi.poifs.filesystem.NPOIFSFileSystem ? –  Jan 28 '14 at 12:31
  • 1
    and you are using poi jar file ? –  Jan 28 '14 at 12:35
  • I add "import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;" but I have the same problem. – user3244162 Jan 28 '14 at 13:05
  • 1
    please check my updated answer , and give me feedback –  Jan 28 '14 at 16:04
  • Are you sure you have a new enough copy of Apache POI? Missing NPOIFSFileSystem makes it sound like you have an ancient version... – Gagravarr Jan 28 '14 at 17:20
  • The file is in the root of the internal memory so the path should be "/test42.xls"? – user3244162 Jan 29 '14 at 08:22
  • no it should not , plesae just try the code as i wrote . ive test it , and its working –  Jan 29 '14 at 08:27
  • I'm desperate, your code does not work for me, I'm not even capable of displaying the contents of a cell. – user3244162 Jan 29 '14 at 08:52
  • I add the new log cat after the first log cat – user3244162 Jan 29 '14 at 09:07
  • please tell me what apache poi version you are using ? –  Jan 29 '14 at 09:51
  • i am using the POI 3.9 –  Jan 29 '14 at 09:55
  • please tell me what is the code at line 41 in the MainActivity.java –  Jan 29 '14 at 10:24
  • please download this jar file and put it in your application http://www.findjar.com/jar/commons-codec/commons-codec/1.4/commons-codec-1.4.jar.html –  Jan 29 '14 at 10:30
  • this one is better http://commons.apache.org/proper/commons-codec/download_codec.cgi –  Jan 29 '14 at 10:32
  • the exception you got indicated that the Commons Codec library was missing, adding that solves the issue. –  Jan 29 '14 at 10:34
  • Normally, it should display something but there is nothing – user3244162 Jan 29 '14 at 10:38
  • no worries , will still work on it until get it to work please tell me what is the code at line 41 in the MainActivity.java –  Jan 29 '14 at 10:39
  • line 41 there are comments, I have this exception: 01-29 11:40:08.216: W/System.err(12830): java.io.IOException: Invalid header signature; read 0x0010000000060409, expected 0xE11AB1A1E011CFD0 – user3244162 Jan 29 '14 at 10:43
  • what is the code , not the error , i can see the errors through logcat –  Jan 29 '14 at 10:45
  • and could you please try another excel file ? –  Jan 29 '14 at 10:46
  • Same problem with another Excel file – user3244162 Jan 29 '14 at 10:49
  • ok weird , please try another excel file , maybe there is something wrong with the file you are trying to read –  Jan 29 '14 at 10:49
  • you are getting same logcat ? –  Jan 29 '14 at 10:51
  • try to open you xls file with excel and save as the file , then try again please –  Jan 29 '14 at 10:53
  • It is now a xlsx file – user3244162 Jan 29 '14 at 10:56
  • the application crashes now – user3244162 Jan 29 '14 at 10:57
  • please provide new logcat –  Jan 29 '14 at 10:59
  • 01-29 11:56:25.156: E/AndroidRuntime(13717): FATAL EXCEPTION: main 01-29 11:56:25.156: E/AndroidRuntime(13717): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.test/com.example.test.MainActivity}: 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) – user3244162 Jan 29 '14 at 11:00
  • I have to leave some time (perhaps several hours) – user3244162 Jan 29 '14 at 11:04
  • 2
    will work on it today , and will keep you posted , we will solve this . –  Jan 29 '14 at 11:05
  • 1
    please check my updated answer and give me some feedback –  Jan 29 '14 at 12:04
  • I already try to use XSSF but it does not work (the application crashes), I have an exception : 01-29 14:35:08.589: E/AndroidRuntime(5910): FATAL EXCEPTION: main 01-29 14:35:08.589: E/AndroidRuntime(5910): java.lang.VerifyError: org/apache/poi/xssf/usermodel/XSSFWorkbook – user3244162 Jan 29 '14 at 13:39
  • 1
    Did you import the jar files ? –  Jan 29 '14 at 14:58
  • Is that working with poi 3.10? I think poi has some references to awt components. ex org.apache.poi.hssf.usermodel.HSSFPicture – Mahendran Jun 13 '14 at 13:05
0

Finally I found the path to my file in the tablet (to avoid using the assets) : "/storage/sdcard0/Documents/test42.xls"

The file is found by the application, but there is still a problem while creating HSSFWorkbook, There is an exception "01-29 16:15:21.329: W/System.err(13127): java.io.IOException: Invalid header signature; read 0x0010000000060409, expected 0xE11AB1A1E011CFD0"

I do not understand this exception, but at least I find my file Enplacement

My code :

    try {
        File file = new File("/storage/sdcard0/Documents/test42.xls");
        FileInputStream input = new FileInputStream(file);





        HSSFWorkbook classeur =  new HSSFWorkbook(input);

        //HSSFSheet feuille = classeur.getSheetAt(0);

        //Cell cell = feuille.getRow(1).getCell(1);

        //String value = cell.getStringCellValue();


        String path = Environment.getExternalStorageDirectory().getAbsolutePath();

        TextView tv = new TextView(this);

        tv.setText(path );

        setContentView(tv);

    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
user3244162
  • 101
  • 1
  • 7
  • i am telling you , you got something wrong with your files . its saied invalid Header , that means that POI cant read these kind of files with this kind of header . –  Jan 30 '14 at 05:12
  • please refer to this answer here http://stackoverflow.com/a/7100758/957654 its talking about the invalid header . –  Jan 30 '14 at 05:15
  • please download this xls file , and run your code www.econ.yale.edu/~shiller/data/ie_data.xls –  Jan 30 '14 at 05:18
  • With your file there are no exceptional but: 01-30 09:05:38.496: W/ResourceType(4883): Failure getting entry for 0x01080a03 (t=7 e=2563) in package 0 (error -75) – user3244162 Jan 30 '14 at 08:08
  • With my xls files there is the exception so I have to open Excel and save them, but they will become XLXS files – user3244162 Jan 30 '14 at 08:19
  • try to use this online converter from xlsx to xls www.freefileconvert.com/ –  Jan 30 '14 at 08:21
  • It would not be easy trying to open an xlsx file (even if the method is different) – user3244162 Jan 30 '14 at 08:29
  • i posted how to open an xlsx , its working perfectly with me did you tried it ? –  Jan 30 '14 at 08:30
  • I look at your code but for "BufferedInputStream" what if I use a file, InputStream caInput = new BufferedInputStream(file); do not work – user3244162 Jan 30 '14 at 08:37
  • you want to use a file ? i will update my answer write away to sue a file –  Jan 30 '14 at 08:39
  • I have a code that looks like yours I'll test it: File file = new File("/storage/sdcard0/Documents/test42.xlsx"); InputStream input = new FileInputStream(file); XSSFWorkbook classeur = new XSSFWorkbook(input); – user3244162 Jan 30 '14 at 08:53
  • i really cant get you , are you saving your xls file in the assest folder ? or on the SD card ? –  Jan 30 '14 at 08:55
  • Eclipse crashes when I try to test the application – user3244162 Jan 30 '14 at 08:59
  • The file is in memory of the tablet (internal sd card) – user3244162 Jan 30 '14 at 09:00
  • then why you were trying to read it from the Assest folder ? and please post your logcat –  Jan 30 '14 at 09:02
  • there is still a problem: Could not find method org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook.getSheets, referenced from method org.apache.poi.xssf.usermodel.XSSFWorkbook.containsSheet – user3244162 Jan 30 '14 at 09:05
  • i think i cant keep trying for you any more you are on your own now everything is working fine with me and i posted my code , and i told you what jar files you should use and not even a vote up –  Jan 30 '14 at 09:06
  • Amrola thank you, it works, I see the contents of a cell. I save one of my xls file, and there is no problem – user3244162 Jan 30 '14 at 09:24
  • Sorry but i'm not able to vote up (because i don't have 15 reputation) – user3244162 Jan 30 '14 at 09:40