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

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 ...

• Generate EXCEL in java using Apache POI HSSF implementation
• Generate EXCEL in java using Apache POI XSSF implementation
• Generate large EXCEL in java using Apache POI SXSSF implementation - Avoid java.lang.OutOfMemoryError
• Apache POI Excel Row Group - Collapse, Expand and setCellFormula

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

• Android Read Excel File from Website and display using a ListView