Excel Sheet accessing through android application

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.4)Create a new class named “MainActivity.java” 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; } Row row = sheet1.createRow(0); c = row.createCell(0); c.setCellValue("Item Number"); c.setCellStyle(cs);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; }
Here is the entire code.MainActivity.javapackage 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; }}Create a main.xml file in “layout” folderFor that go to your project’s properties –> click on java Buildpath –> add External Jars –> Browse the jar.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” belowHere we pass the file name as the parameter to the function.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.Each and every entry in the cell will be printed in the log.In this xml file, add two buttons with id’s “writeExcel” and “readExcel” representing the “WriteExel” and “ReadExcel” buttons respectively.Once you have done with all the above steps, your application is ready to test.