How to Read Particular Cell Value in Excel File Using Java

Creating/Reading an Excel file in Android.

Abode > Creating/Reading an Excel file in Android.

Its piece of cake to shop records in database via android application. But what's the use of it when we cannot get the records on a sail of paper.

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

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

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

http://world wide web.java2s.com/Code/Jar/p/Downloadpoi37jar.htm

Notation:- 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 together "WRITE_EXTERNAL_STORAGE " permission every bit we crave to admission the external storage for saving the Excel file.

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

3) Add together the poi-3.7.jar (or updated version) that y'all have downloaded from the link provided above. Add this jar to your projection'south External Jars.

For that get to your projection's properties –> click on coffee Buildpath –> add External Jars –> Scan the jar.

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

Here we have 2 functionality to encompass.

Showtime 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" beneath

private static boolean saveExcelFile(Context context, String fileName) { // bank check if available and not read only if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) { Log.e(TAG, "Storage not available or read just"); render faux; } boolean success = imitation; //New Workbook Workbook wb = new HSSFWorkbook(); Prison cell c = null; //Cell way for header row CellStyle cs = wb.createCellStyle(); cs.setFillForegroundColor(HSSFColor.LIME.index); cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //New Canvass 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(one, (15 * 500)); sheet1.setColumnWidth(2, (15 * 500)); // Create a path where nosotros will place our List of objects on external storage File file = new File(context.getExternalFilesDir(null), fileName); FileOutputStream os = null; endeavour { os = new FileOutputStream(file); wb.write(os); Log.w("FileUtils", "Writing file" + file); success = true; } catch (IOException e) { Log.w("FileUtils", "Fault writing " + file, e); } catch (Exception due east) { Log.w("FileUtils", "Failed to relieve file", eastward); } 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 nosotros add/enter the value in the cell.

The above lawmaking will add together the value "Detail Number" in the 0th row and 0th cavalcade.

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 non 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).bear witness(); } } }grab (Exception e){due east.printStackTrace(); } return; }

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

[wcp-carousel id="10027″]

Here is the entire lawmaking.

MainActivity.java

package com.example.excel_example; import coffee.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import coffee.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.Prison 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.Action; import android.content.Context; import android.os.Bundle; import android.os.Surroundings; 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 { Push 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()) { example R.id.writeExcel: saveExcelFile(this,"myExcel.xls"); interruption; 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 non available or read only"); return fake; } boolean success = false; //New Workbook Workbook wb = new HSSFWorkbook(); Cell c = null; //Jail cell manner for header row CellStyle cs = wb.createCellStyle(); cs.setFillForegroundColor(HSSFColor.LIME.index); cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //New Sheet Sheet sheet1 = nothing; 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("Cost"); c.setCellStyle(cs); sheet1.setColumnWidth(0, (15 * 500)); sheet1.setColumnWidth(i, (fifteen * 500)); sheet1.setColumnWidth(two, (15 * 500)); // Create a path where nosotros will place our List of objects on external storage File file = new File(context.getExternalFilesDir(cipher), fileName); FileOutputStream bone = null; endeavor { os = new FileOutputStream(file); wb.write(bone); Log.westward("FileUtils", "Writing file" + file); success = truthful; } take hold of (IOException e) { Log.due west("FileUtils", "Error writing " + file, eastward); } catch (Exception eastward) { Log.w("FileUtils", "Failed to salvage file", e); } finally { effort { if (null != os) bone.close(); } catch (Exception ex) { } } return success; } private static void readExcelFile(Context context, String filename) { if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) { Log.e(TAG, "Storage not bachelor or read merely"); 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 showtime sheet from workbook HSSFSheet mySheet = myWorkBook.getSheetAt(0); /** We now demand 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, "Jail cell Value: " + myCell.toString()); Toast.makeText(context, "cell Value: " + myCell.toString(), Toast.LENGTH_SHORT).show(); } } }catch (Exception e){e.printStackTrace(); } render; } public static boolean isExternalStorageReadOnly() { Cord extStorageState = Environment.getExternalStorageState(); if (Surround.MEDIA_MOUNTED_READ_ONLY.equals(extStorageState)) { render truthful; } return false; } public static boolean isExternalStorageAvailable() { String extStorageState = Environment.getExternalStorageState(); if (Environment.MEDIA_MOUNTED.equals(extStorageState)) { return true; } return faux; } }

Create a main.xml file in "layout" folder

In this xml file, add two buttons with id'southward "writeExcel" and "readExcel" representing the "WriteExel" and "ReadExcel" buttons respectively.

Once y'all have done with all the above steps, your awarding is ready to test.

[wcp-carousel id="10008″]

Read More

haynesdure1965.blogspot.com

Source: https://www.cuelogic.com/blog/creatingreading-an-excel-file-in-android

Share this post