Creating/Reading an Excel file in Android.

by sagar patil
April 5, 2013April 17, 2020 by sagar patil

4 mins read

Android

Its easy to store records in database via android application. But what's the use of it when we cannot get the records on a sheet of paper.

Here's a simple and systematic solution to get your records in an excel sheet in Android.

Excel Sheet accessing through android application in very few lines of code.

To achieve this download poi-3.7.jar or later from the following reference:-

http://www.java2s.com/Code/Jar/p/Downloadpoi37jar.htm

Note:- make sure its a jar file for eclipse.

Steps to achieve the target:-

1) Create a project in android "Exel_Example" and fill all the required details.

2) In AndroidManifest.xml file add "WRITE_EXTERNAL_STORAGE " permission as we require to access the external storage for saving the Excel file.

<uses-permissionandroid:name="android.permission.WRITE_EXTERNAL_STORAGE"/>

3) Add the poi-3.7.jar (or updated version) that you have downloaded from the link provided above. Add this jar to your project's External Jars.

For that go to your project's properties --> click on java Buildpath --> add External Jars --> Browse the jar.

4)Create a new class named "MainActivity.java"

Here we have 2 functionality to cover.

First is to save the Excel File and other is to read the contents of the Excel File.

To save a Excel file, check out the function "saveExcelFile" below

 private static boolean saveExcelFile(Context context, String fileName) { 
 
        // check if available and not read only 
        if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) { 
            Log.e(TAG, "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; 
    }

Here we pass the file name as the parameter to the function.

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

This is how we add/enter the value in the cell.

The above code will add the value "Item Number" in the 0th row and 0th column.

To read the contents of the file have a look at the function "readExcelFile" below

 private static void readExcelFile(Context context, String filename) { 
 
        if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) 
        { 
            Log.e(TAG, "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 rowIter = mySheet.rowIterator();
 
            while(rowIter.hasNext()){
                HSSFRow myRow = (HSSFRow) rowIter.next();
                Iterator cellIter = myRow.cellIterator();
                while(cellIter.hasNext()){
                    HSSFCell myCell = (HSSFCell) cellIter.next();
                    Log.d(TAG, "Cell Value: " +  myCell.toString());
                    Toast.makeText(context, "cell Value: " + myCell.toString(), Toast.LENGTH_SHORT).show();
                }
            }
        }catch (Exception e){e.printStackTrace(); }
 
        return;
    }

Each and every entry in the cell will be printed in the log.

Here is the entire code.

MainActivity.java

package com.example.excel_example;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
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.Button;
import android.widget.Toast;
 
public class MainActivity extends Activity implements OnClickListener
{
	Button writeExcelButton,readExcelButton;
	static String TAG = "ExelLog";
    @Override
    public void onCreate(Bundle savedInstanceState) 
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
 
        writeExcelButton = (Button) findViewById(R.id.writeExcel);
        writeExcelButton.setOnClickListener(this);
        readExcelButton = (Button) findViewById(R.id.readExcel);
        readExcelButton.setOnClickListener(this);
 
    }
 
    public void onClick(View v) 
    {
        switch (v.getId()) 
        {
        case R.id.writeExcel:
            saveExcelFile(this,"myExcel.xls");
            break;
        case R.id.readExcel:	
            readExcelFile(this,"myExcel.xls");
            break;   
        }
    }
 
    private static boolean saveExcelFile(Context context, String fileName) { 
 
        // check if available and not read only 
        if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) { 
            Log.e(TAG, "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.e(TAG, "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 rowIter = mySheet.rowIterator();
 
            while(rowIter.hasNext()){
                HSSFRow myRow = (HSSFRow) rowIter.next();
                Iterator cellIter = myRow.cellIterator();
                while(cellIter.hasNext()){
                    HSSFCell myCell = (HSSFCell) cellIter.next();
                    Log.d(TAG, "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; 
    } 
}