Blog Archive

Android Read Write EXCEL file using Apache POI

In this example we learn about the following
  • How to write a TEXT file
  • How to read a TEXT file
  • How to write an EXCEL file
  • How to read an EXCEL file

Android read write excel file using Apache POI
Please Note: You need to download apache POI jar file for this project !

Click on the links below for ore ways to Generate Excel depending on your need ...

Source for XML layout main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    >
<LinearLayout android:id="@+id/linearLayout1" android:layout_height="wrap_content" 
 android:layout_width="match_parent" android:orientation="horizontal">
    <Button android:id="@+id/write" android:text="Write Text File" 
  android:layout_height="wrap_content" android:layout_gravity="left" 
  android:layout_width="200dp">
 </Button>
    <Button android:id="@+id/read" android:text="Read Text File" 
  android:layout_height="wrap_content" android:layout_width="200dp">
 </Button>
</LinearLayout>
<LinearLayout android:id="@+id/linearLayout2" android:layout_height="wrap_content" 
 android:layout_width="match_parent" android:orientation="horizontal">
    <Button android:id="@+id/writeExcel" android:text="Write Excel File" 
  android:layout_height="wrap_content" android:layout_width="200dp">
 </Button>
    <Button android:id="@+id/readExcel" android:text="Read Excel File" 
  android:layout_height="wrap_content" android:layout_width="200dp">
 </Button>
</LinearLayout>
</LinearLayout>

Java Source code for AndroidReadExcelActivity.java

package com.as400samplecode;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.PrintStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import android.app.Activity;
import android.content.Context;
import android.os.Bundle;
import android.os.Environment;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Toast;

public class AndroidReadExcelActivity extends Activity implements OnClickListener{
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        View writeButton = findViewById(R.id.write);
        writeButton.setOnClickListener(this);
        View readButton = findViewById(R.id.read);
        readButton.setOnClickListener(this);
        View writeExcelButton = findViewById(R.id.writeExcel);
        writeExcelButton.setOnClickListener(this);
        View readExcelButton = findViewById(R.id.readExcel);
        readExcelButton.setOnClickListener(this);

    }

    public void onClick(View v) {
        switch (v.getId()) {
        case R.id.write:
            saveFile(this,"myFile.txt");
            break;
        case R.id.read:
            readFile(this,"myFile.txt");
            break;
        case R.id.writeExcel:
            saveExcelFile(this,"myExcel.xls");
            break;
        case R.id.readExcel:
            readExcelFile(this,"myExcel.xls");
            break;   
        }
    }

    private static boolean saveFile(Context context, String fileName) { 

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

        // Create a path where we will place our List of objects on external storage 
        File file = new File(context.getExternalFilesDir(null), fileName); 
        PrintStream p = null; // declare a print stream object
        boolean success = false; 

        try { 
            OutputStream os = new FileOutputStream(file); 
            // Connect print stream to the output stream
            p = new PrintStream(os);
            p.println("This is a TEST");
            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 != p) 
                    p.close(); 
            } catch (Exception ex) { 
            } 
        } 

        return success; 
    } 

    private static void readFile(Context context, String filename) { 

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

        FileInputStream fis = null;

        try 
        { 
            File file = new File(context.getExternalFilesDir(null), filename); 
            fis = new FileInputStream(file); 
            // Get the object of DataInputStream
            DataInputStream in = new DataInputStream(fis);
            BufferedReader br = new BufferedReader(new InputStreamReader(in));
            String strLine;
            //Read File Line By Line
            while ((strLine = br.readLine()) != null) {
                Log.w("FileUtils", "File data: " + strLine);
                Toast.makeText(context, "File Data: " + strLine , Toast.LENGTH_SHORT).show();
            }
            in.close();
        } 
        catch (Exception ex) { 
            Log.e("FileUtils", "failed to load file", ex); 
        } 
        finally { 
            try {if (null != fis) fis.close();} catch (IOException ex) {} 
        } 

        return;
    } 

    private static boolean saveExcelFile(Context context, 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("myOrder");

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

        c = row.createCell(0);
        c.setCellValue("Item Number");
        c.setCellStyle(cs);

        c = row.createCell(1);
        c.setCellValue("Quantity");
        c.setCellStyle(cs);

        c = row.createCell(2);
        c.setCellValue("Price");
        c.setCellStyle(cs);

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

        // Create a path where we will place our List of objects on external storage 
        File file = new File(context.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; 
    } 

    private static void readExcelFile(Context context, String filename) { 

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

        try{
            // Creating Input Stream 
            File file = new File(context.getExternalFilesDir(null), filename); 
            FileInputStream myInput = new FileInputStream(file);

            // Create a POIFSFileSystem object 
            POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);

            // Create a workbook using the File System 
            HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);

            // Get the first sheet from workbook 
            HSSFSheet mySheet = myWorkBook.getSheetAt(0);

            /** We now need something to iterate through the cells.**/
            Iterator<Row> rowIter = mySheet.rowIterator();

            while(rowIter.hasNext()){
                HSSFRow myRow = (HSSFRow) rowIter.next();
                Iterator<Cell> cellIter = myRow.cellIterator();
                while(cellIter.hasNext()){
                    HSSFCell myCell = (HSSFCell) cellIter.next();
                    Log.w("FileUtils", "Cell Value: " +  myCell.toString());
                    Toast.makeText(context, "cell Value: " + myCell.toString(), Toast.LENGTH_SHORT).show();
                }
            }
        }catch (Exception e){e.printStackTrace(); }

        return;
    } 

    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; 
    } 
}

Source code for AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
      package="com.as400samplecode"
      android:versionCode="1"
      android:versionName="1.0">
    <uses-sdk android:minSdkVersion="13" />
    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />
    <application android:icon="@drawable/icon" android:label="@string/app_name" 
  android:theme="@android:style/Theme.Holo.Light">>
        <activity android:name=".AndroidReadExcelActivity"
                  android:label="@string/app_name">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />
                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>

    </application>
</manifest>

Recommended Reading


60 comments :

  1. One must download poi-3.7.jar or later [make sure its a jar file for eclipse] from this site:
    http://grepcode.com/snapshot/repo1.maven.org/maven2/org.apache.poi/poi/3.7

    then project-properties-java Buildpath - add External Jars - browse

    ReplyDelete
  2. The link to the Apache Website for downloading the POI jar is provided in the beginning of the blog.

    The website mentioned in the previous comment GrepCode.com is a code search engine built by developers for developers to search and browse open source Java projects.

    ReplyDelete
  3. can u please tell me which android version should i use to make the above code run?

    ReplyDelete
  4. As you can see in the Manifest I am using minSdkVersion="13". So if you are just copying everything from here then use that version or higher.
    But this code will run on any version, there is nothing fancy here that is only available in higher versions. Make sure you download the apache POI jar for your project.

    ReplyDelete
  5. Will this code work on an android 2.2 virtual device?
    I am just getting 4 buttons, 'Write Text File', 'Read Text File','Write Excel File','Read Excel File'. There is no action even after Clicking on any of these buttons.

    ReplyDelete
  6. Yes it should work. Please take a look at your log file for errors and make sure you have the apache POI jar files in your project.

    ReplyDelete
  7. No way the code is working. After executing twice a new error occured. "Conversion to Dalvik format failed with error 1". What should i do?

    ReplyDelete
  8. Can anyone please help me out to make a powerpoint viewer in android?

    ReplyDelete
  9. Hi. I have taken the code and written. Downloaded the .jar file too. It is running fine. When i click on any of the buttons then nothing happens and the log is as : 05-08 11:46:17.200: W/FileUtils(647): Storage not available or read only

    Please help me in this. Thank you. purplerain.moon@gmail.com

    ReplyDelete
  10. If I rightly understand you are trying to add something to a read only file or that your external storage is read only what exactly are you doing?

    ReplyDelete
  11. Shalini ChandelMay 9, 2012 at 2:46 AM

    Hi,

    I am using POI 3.8 and when clicking on "Read Excel" getting following error:

    05-09 12:05:07.436: E/AndroidRuntime(605): java.lang.NoClassDefFoundError: org.apache.poi.poifs.filesystem.POIFSFileSystem

    Please help. I can read text file.

    ReplyDelete
  12. Hi Shalini. Are you getting this error while reading/writing the excel file? If yes, then make a folder name as libs under your project root folder and put the .jar file into it and run the project. lets see what happens?
    Khalid..
    purplerain.moon@gmail.com,khalid.parvez@rossitek.com

    ReplyDelete
  13. Hi, I'm getting an error to write an excel file although I have renamed my folder to libs from lib.

    ReplyDelete
  14. Can we make pdf using this jar... if yes tell me how

    ReplyDelete
  15. Hi. .If I want to use Url Path mean. .Http://somsite/../work.xls.. then I cant read excel file ..bcz of wrong path . .please help me. .

    ReplyDelete
  16. i did wat u hav mentioned above but m facing this error... may i know wat s this ???
    thanks in advance.

    Conversion to Dalvik format failed with error 1

    ReplyDelete
    Replies
    1. do you found solution to this problem..if yes then please suggest

      Delete
  17. Your eclipse is running out of memory when packaging the application along with the external jars used in the project. You just need more memory not sure if there is any other solution. Please share if found another solution would be helpful for me.

    ReplyDelete
    Replies
    1. Conversion to Dalvik format failed with error 1..this same error occure with me if any one know how to remove this error please suggest

      Delete
  18. Hello, this is my problem: Could not find class 'java.awt.font.FontRenderContext', referenced from method org.apache.poi.hssf.usermodel.HSSFSheet.autoSizeColumn

    android 2.2
    version POI: 3.7

    thanks, any help.
    att: Zyro_Alpha.

    ReplyDelete
    Replies
    1. Correction POI: 3.6

      Delete
    2. Near "sheet1.setColumnWidth([Column number], (15 * 500));"
      Change "(15 * 500)" to "7500" and it will work

      Delete
  19. This code creates a new file always. Is it possible to append the Excel file?
    I need to add record to an excel file everytime i click a button.
    Can anyone please help?

    Thanks in advance.

    ReplyDelete
  20. plaese im copied and pasted the code but when i run it ,it does not create any excel or txt files, and even if it create where it should be

    ReplyDelete
  21. I am not getting correct jar file for it. please let me know correct one

    ReplyDelete
  22. Yeah, where are these files supposed to be located? Path pls?

    ReplyDelete
  23. Can you please tell me where do i locally save the .xls file to be read ?
    Should it be in the assets folder of my project? I get an error when i do so. Please help.

    ReplyDelete
  24. Replies
    1. while putting in sdcard displaying warning as:
      Unable to create external file directory.
      java.io.filenotfound Exception myExcel.xls (no such file or directory)

      Delete
  25. may any one tell me how can we display an excel file in a gridview or anything else in android..

    ReplyDelete
  26. Please can u send me Working source code..As its not Working..??

    ReplyDelete
  27. Hello, when i am running this project, i get this error: dalvik Could not find class 'java.awt.font.FontRenderContext', referenced from method org.apache.poi.hssf.usermodel.HSSFSheet.autoSizeColumn even i have use poi-3.7 jar

    ReplyDelete
    Replies
    1. I am also getting this same error how do you solved this pls help amitakotiya@gmail.com

      Delete
  28. Getting error INSTALL_FAILED_DEXOPT

    ReplyDelete
  29. i want to append data in existing excel file...any one help me plz....

    ReplyDelete
    Replies
    1. Please check Aspose.Cells for Android: http://www.aspose.com/android/excel-component.aspx
      It can surely help you, Not only help to append but also in reading, writing & manipulate Excel spreadsheets (XLS, XLSX, XLSM, SpreadsheetML, CSV, tab delimited) and HTML file formats without needing to rely on Microsoft Excel.

      Delete
  30. I got the "java.lang.NoClassDefFoundError: org.apache.poi.hssf.usermodel.HSSFWorkbook" error when I run this code in my application.Can you guide me how to remove this error from my code.I have added poi-3.9-20121203.jar file in my project.

    ReplyDelete
  31. Following exception and error given

    11-24 19:27:27.735: E/AndroidRuntime(11095): FATAL EXCEPTION: main
    11-24 19:27:27.735: E/AndroidRuntime(11095): java.lang.NoClassDefFoundError: org.apache.poi.poifs.filesystem.POIFSFileSystem
    11-24 19:27:27.735: E/AndroidRuntime(11095): at com.example.excelhandler.MainActivity.readExcelFile(MainActivity.java:161)
    11-24 19:27:27.735: E/AndroidRuntime(11095): at com.example.excelhandler.MainActivity.onClick(MainActivity.java:76)
    11-24 19:27:27.735: E/AndroidRuntime(11095): at android.view.View.performClick(View.java:4222)
    11-24 19:27:27.735: E/AndroidRuntime(11095): at android.view.View$PerformClick.run(View.java:17343)
    11-24 19:27:27.735: E/AndroidRuntime(11095): at android.os.Handler.handleCallback(Handler.java:615)
    11-24 19:27:27.735: E/AndroidRuntime(11095): at android.os.Handler.dispatchMessage(Handler.java:92)
    11-24 19:27:27.735: E/AndroidRuntime(11095): at android.os.Looper.loop(Looper.java:137)
    11-24 19:27:27.735: E/AndroidRuntime(11095): at android.app.ActivityThread.main(ActivityThread.java:4895)
    11-24 19:27:27.735: E/AndroidRuntime(11095): at java.lang.reflect.Method.invokeNative(Native Method)
    11-24 19:27:27.735: E/AndroidRuntime(11095): at java.lang.reflect.Method.invoke(Method.java:511)
    11-24 19:27:27.735: E/AndroidRuntime(11095): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:994)
    11-24 19:27:27.735: E/AndroidRuntime(11095): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:761)
    11-24 19:27:27.735: E/AndroidRuntime(11095): at dalvik.system.NativeStart.main(Native Method)

    ReplyDelete
  32. I don't get any output when clicking the button.
    It gives error on logcat as libegl called unimplemented opengl es api.
    plz give any suggession.

    ReplyDelete
  33. How can we display the contents of excel sheet read using the above code in tabular form (same as in excel sheet) in android app. Plz help....

    ReplyDelete
  34. where file storage? how to find the path to the file??

    ReplyDelete
  35. How can i read the particular column in sheet1 from excel sheet ?

    ReplyDelete
  36. I want to upload my excel file and i want to read it. But it is continuously reading the file you added in the code i.e. myExcel.xls.
    Can u plz tell me where this file is getting stored and how would I read mine?

    ReplyDelete
  37. Could not find class 'java.awt.font.FontRenderContext', referenced from method org.apache.poi.hssf.usermodel.HSSFSheet.autoSizeColumn i ma getting this error can u pls help me

    ReplyDelete
  38. Thank you for the code.
    I had one problem.it works fine when file is newly created but if i modify the excel sheet then it is unable display modified rows/new rows added.

    ReplyDelete
  39. There is error on import lines for apache... even after I add the file in the build path..please help me out...its urgent as I want to import excel file into sqlite database

    ReplyDelete
  40. This comment has been removed by the author.

    ReplyDelete
  41. This comment has been removed by the author.

    ReplyDelete
  42. Useful article. Is there another code sample that uses Google Sheet API?

    ReplyDelete