HB Blog 129: Android Read/Write Excel Tutorial.

Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. 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.
To achieve this download poi-3.7.jar or later from the following reference:-
http://grepcode.com/snapshot/repo1.maven.org/maven2/org.apache.poi/poi/3.7

Refer the below link for complete sample code:-

Download Sample Code

Have a look on few code snippets,

//MainActivity.java 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217

package com.example.harshalbenake.readexceldata; import android.app.Activity; import android.content.Context; import android.os.Environment; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.util.Log; import android.view.View; import android.widget.Button; import android.widget.TextView; import android.widget.Toast; 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 java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.Iterator; import java.util.Random; public class MainActivity extends Activity { private TextView mtv_result; private String strResult=""; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); Button btn_write = (Button) findViewById(R.id.btn_write); Button btn_read = (Button) findViewById(R.id.btn_read); mtv_result = (TextView) findViewById(R.id.tv_result); btn_write.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { saveExcelFile("hb.xls"); } }); btn_read.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { readExcelFile("hb.xls"); } }); } private void readExcelFile(String filename) { if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) { System.out.println("Storage not available or read only"); return; } try { // Creating Input Stream File file = new File(Environment.getExternalStorageDirectory(), 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(); System.out.println("Cell Value: " + myCell.toString()); strResult=strResult+" "+myCell.toString()+" "; } strResult=strResult+" \n"; mtv_result.setText(strResult); } } catch (Exception e) { e.printStackTrace(); } return; } private boolean saveExcelFile(String fileName) { // check if available and not read only if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) { System.out.println("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.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); cs.setFillForegroundColor(HSSFColor.LIME.index); cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //New Sheet Sheet sheet1 = null; sheet1 = wb.createSheet("HB Prodcut list"); // Generate column headings Row row = sheet1.createRow(0); c = row.createCell(0); c.setCellValue("Sr.No."); c.setCellStyle(cs); c = row.createCell(1); c.setCellValue("Quantity"); c.setCellStyle(cs); c = row.createCell(2); c.setCellValue("Price"); c.setCellStyle(cs); // Generate column headings - row1 Row row1 = sheet1.createRow(1); c = row1.createCell(0); c.setCellValue("1"); c.setCellStyle(cs); c = row1.createCell(1); c.setCellValue("Product"); c.setCellStyle(cs); c = row1.createCell(2); c.setCellValue("10"); c.setCellStyle(cs); for(int rowItem=2;rowItem<5;rowItem++){ Row rowNo = sheet1.createRow(rowItem); c = rowNo.createCell(0); c.setCellValue(rowItem+""); c.setCellStyle(cs); c = rowNo.createCell(1); c.setCellValue("Product Item"+rowItem); c.setCellStyle(cs); c = rowNo.createCell(2); //minimum + rn.nextInt(maxValue - minvalue + 1) c.setCellValue(new Random().nextInt(100-10+1) + 10+""); c.setCellStyle(cs); } sheet1.setColumnWidth(0, (15 * 100)); 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(Environment.getExternalStorageDirectory(), fileName); FileOutputStream os = null; try { os = new FileOutputStream(file); wb.write(os); System.out.println("Writing file" + file); success = true; } catch (IOException e) { System.out.println("Error writing " + file); } catch (Exception e) { e.printStackTrace(); } finally { try { if (null != os) os.close(); } catch (Exception ex) { } } return success; } 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; } }