how to read data from xls excel file java android using poi library?

Hey Devs,  I hope you all aware about Excel files (more specifically Spreadsheet). Sometimes we used to get requirements like reading an excel file and collecting the data and do further manipulations on that data, in case of java based web application there are several free libraries available to read those Excel files but for Android apps options are very limited.

Here I have taken one of such free library to read the Excel file from an Android app. The library I have chosen is called POI a library from Apache Foundation.

So here we go –

  1. First of all download the library
  2. Add the gradle dependency for this in your project
  3. Add the .xls file within your projects “assets” directory
  4. Now you are ready to read your excel file
    private void readExcelFile(Context context, String filename) {
    
        if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) {
          Log.w("FileUtils", "Storage not available or read only");
          return;
        }
    
        new parseTask().execute(filename);
      }
    
      private class parseTask extends AsyncTask<String, Void, Void> {
    
        @Override
        protected Void doInBackground(String... params) {
          try {
            String filename = params[0];
            InputStream stream = getAssets().open(filename);
    
            // Create a POIFSFileSystem object
            POIFSFileSystem myFileSystem = new POIFSFileSystem(stream);
    
            // 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();
    
            int length = (mySheet.getLastRowNum() - mySheet
                .getFirstRowNum()) - 1;
            hospitalList = new ArrayList<Hospital>(length);
    
            rowIter.hasNext();
    
            while (rowIter.hasNext()) {
              HSSFRow myRow = (HSSFRow) rowIter.next();
              Iterator<Cell> cellIter = myRow.cellIterator();
    
              Hospital hospital = new Hospital();
              int colNumber = -1;
              while (cellIter.hasNext()) {
                colNumber++;
                HSSFCell myCell = (HSSFCell) cellIter.next();
                String ColValue = myCell.toString();
                switch (colNumber) {
                case 0:
                  // Serial Number
                  hospital.setSrNo(ColValue);
                  break;
                case 1:
                  // State
    
                  State state = new State();
                  state.setState(ColValue);
                  hospital.setState(state);
                  break;
                case 2:
                  // City
                  City city = new City();
                  city.setCity(ColValue);
                  hospital.setCity(city);
                  break;
                case 3:
                  // Type
                  hospital.setHospitalType(ColValue);
                  break;
                case 4:
                  // Hospital Name
                  hospital.setProviderName(ColValue);
                  break;
                case 5:
                  // Address 1
                  hospital.setAddress1(ColValue);
                  break;
                case 6:
                  // Address 2
                  hospital.setAddress2(ColValue);
                  break;
                case 7:
                  // Address Area
                  hospital.setArea(ColValue);
                  break;
                case 8:
                  // PinCode
                  hospital.setPin(ColValue);
                  break;
                case 9:
                  // Telephone Area Code
                  hospital.setTelephoneCode(ColValue);
                  break;
                case 10:
                  // Telephone Number
                  hospital.setTelephoneNumber(ColValue);
                  break;
                case 11:
                  // FAX Number
                  hospital.setFaxNo(ColValue);
                  break;
                case 12:
                  // Provider Number
                  hospital.setProviderNumber(ColValue);
                  break;
                default:
                  break;
                }
              }
              hospitalList.add(hospital);
              Log.v("Hospital", hospital.toString());
            }
          } catch (Exception e) {
            e.printStackTrace();
          }
    
          return null;
        }
    
        @Override
        protected void onPostExecute(Void result) {
          super.onPostExecute(result);
          adapter = new HospitalListAdapter(ExcelReader.this,
              R.layout.hospital_row, hospitalList);
          ListView lvHospital = getListView();
          lvHospital.setAdapter(adapter);
        }
      }
    
      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;
      }

    In this above code I have implemented the reading for an excel having list of hospitals with all sort of their information.

    I am not going to include the bean classes used here like, Hospital, City, State. The purpose of this code is to explain about api’s for this library.

  5. Need to read the file data using POIFSFileSystem after that need to select the specific workbook using HSSFWorkbook
  6. Now you need to select the particular sheet using
    // Get the first sheet from workbook
    HSSFSheet mySheet = myWorkBook.getSheetAt(0);
    
  7. Now you have to get the list of rows and iterate it to get the individual row using HSSFRow class
  8. After reading row just parse columns one by one

 

And bingo you have parsed the Excel file successfully.

Note: The code is not very optimized so please do so for better performance.

Please do not forget to like and share  with your friends.

Happy coding!!!

1 Response

  1. This is really good post i will link this post to my blog, keep posting!

Leave a Reply

Your email address will not be published. Required fields are marked *