How to Upload Excel Sheet Data to Firebase Realtime Database in Android?

Last Updated : 10 Feb, 2022

Firebase Realtime Database is the backend service which is provided by Google for handling backend tasks for your Android apps, IOS apps as well as your websites. It provides so many services such as storage, database, and many more. The feature for which Firebase is famous for its Firebase Realtime Database. By using Firebase Realtime Database in your app you can give live data updates to your users without actually refreshing your app. In this article, we will be uploading Excel Sheet Data into the firebase real-time database. This can be useful when you are creating a quiz app where you have to upload a lot of question. In that case, you can upload your data using an excel sheet.

What we are going to build in this article?

We will be building a simple application in which we will be uploading data into the firebase real-time database using excel Sheet. Firstly we will be selecting an excel file and then it will be uploaded to firebase by taking the total number of rows and columns and then a random id will be generated in which row-wise data will be stored. Note that we are going to implement this project using the Java language.

Step by Step Implementation

Step 1: Create a New Project

To create a new project in Android Studio please refer to How to Create/Start a New Project in Android Studio. Note that select Java as the programming language.

Step 2: Working with the AndroidManifest.xml file

For adding data to Firebase we should have to give permissions for accessing the internet. For adding these permissions navigate to the app > AndroidManifest.xml and Inside that file add the below permissions to it.

<uses-permission android:name=”android.permission.INTERNET” />

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

<uses-permission android:name=”android.permission.READ_EXTERNAL_STORAGE” />

Step 3: Working with the build.gradle(app) file

Add these implementations into it

implementation fileTree(dir: ‘libs’, include: [‘*.jar’])

implementation ‘com.google.firebase:firebase-database:16.0.4’

implementation files(‘libs/poi-3.12-android-a.jar’)

Step 4: Working with the activity_main.xml file

Navigate to the app > res > layout > activity_main.xml and add the below code to that file. Below is the code for the activity_main.xml file.

XML

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:gravity="center"
    tools:context=".MainActivity">
 
    <Button
        android:id="@+id/excel"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Click Here to upload excel Sheet" />
 
</LinearLayout>

Step 5: Working with the MainActivity.java file

Open the MainActivity.java file there within the class, first of all, create the object of the Button class.

public static final int cellCount=2;
Button excel;

Secondly, inside the onCreate() method, we have to link those objects with their respective IDs that we have given in .XML file.

excel = findViewById(R.id.excel);

Checking for permission to excel file from phone storage

if(requestCode == 101){
            if(grantResults[0] == PackageManager.PERMISSION_GRANTED){
                // if permission granted them select file
                selectfile();
            } else {
                Toast.makeText(MainActivity.this, "Permission Not granted",Toast.LENGTH_LONG).show();
       }
 }

Selecting excel file from phone

Intent intent = new Intent(Intent.ACTION_OPEN_DOCUMENT);
        intent.setType("*/*");
        intent.addCategory(Intent.CATEGORY_OPENABLE);
        
        // file is selected now start activity function to proceed
        startActivityForResult(Intent.createChooser(intent, "Select File"),102);

Getting an excel sheet and check for total rows and columns and will add those values to the database.

XSSFSheet sheet=workbook.getSheetAt(0);
FormulaEvaluator formulaEvaluator=workbook.getCreationHelper().createFormulaEvaluator();
int rowscount=sheet.getPhysicalNumberOfRows();
if(rowscount > 0){                                      
     // check row wise data
     for (int r=0;r<rowscount;r++){                   
            Row row=sheet.getRow(r);
            if(row.getPhysicalNumberOfCells()==cellCount) {
                      // get cell data
                      String A = getCellData(row,0,formulaEvaluator);
                      String B = getCellData(row,1,formulaEvaluator);            
              }
             else {
                   Toast.makeText(MainActivity.this,"row no. "+(r+1)+" has incorrect data",Toast.LENGTH_LONG).show();
                   return;                                   
         }
}

Java

import android.Manifest;
import android.app.ProgressDialog;
import android.content.Intent;
import android.content.pm.PackageManager;
import android.net.Uri;
import android.os.AsyncTask;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;
 
import androidx.annotation.NonNull;
import androidx.annotation.Nullable;
import androidx.appcompat.app.AppCompatActivity;
import androidx.core.app.ActivityCompat;
 
import com.google.android.gms.tasks.OnCompleteListener;
import com.google.android.gms.tasks.Task;
import com.google.firebase.database.FirebaseDatabase;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.UUID;
 
public class MainActivity extends AppCompatActivity {
     
    // initialising the cell count as 2
    public static final int cellCount = 2;
    Button excel;
 
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
 
        excel = findViewById(R.id.excel);
         
        // click on excel to select a file
        excel.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                if (ActivityCompat.checkSelfPermission(MainActivity.this, Manifest.permission.READ_EXTERNAL_STORAGE) == PackageManager.PERMISSION_GRANTED) {
                    selectfile();
                } else {
                    ActivityCompat.requestPermissions(MainActivity.this, new String[]{Manifest.permission.READ_EXTERNAL_STORAGE}, 101);
                }
            }
        });
    }
 
    // request for storage permission if not given
    @Override
    public void onRequestPermissionsResult(int requestCode, @NonNull String[] permissions, @NonNull int[] grantResults) {
        super.onRequestPermissionsResult(requestCode, permissions, grantResults);
        if (requestCode == 101) {
            if (grantResults[0] == PackageManager.PERMISSION_GRANTED) {
                selectfile();
            } else {
                Toast.makeText(MainActivity.this, "Permission Not granted", Toast.LENGTH_LONG).show();
            }
        }
    }
 
    private void selectfile() {
        // select the file from the file storage
        Intent intent = new Intent(Intent.ACTION_OPEN_DOCUMENT);
        intent.setType("*/*");
        intent.addCategory(Intent.CATEGORY_OPENABLE);
        startActivityForResult(Intent.createChooser(intent, "Select File"), 102);
    }
 
    protected void onActivityResult(int requestCode, int resultCode, @Nullable Intent data) {
        super.onActivityResult(requestCode, resultCode, data);
        if (requestCode == 102) {
            if (resultCode == RESULT_OK) {
                String filepath = data.getData().getPath();
                // If excel file then only select the file
                if (filepath.endsWith(".xlsx") || filepath.endsWith(".xls")) {
                    readfile(data.getData());
                }
                // else show the error
                else {
                    Toast.makeText(this, "Please Select an Excel file to upload", Toast.LENGTH_LONG).show();
                }
            }
        }
    }
 
    ProgressDialog dialog;
 
    private void readfile(final Uri file) {
        dialog = new ProgressDialog(this);
        dialog.setMessage("Uploading");
        dialog.setCanceledOnTouchOutside(false);
        dialog.show();
        AsyncTask.execute(new Runnable() {
            @Override
            public void run() {
 
                final HashMap<String, Object> parentmap = new HashMap<>();
 
                try {
                    XSSFWorkbook workbook;
                    
                    // check for the input from the excel file
                    try (InputStream inputStream = getContentResolver().openInputStream(file)) {
                        workbook = new XSSFWorkbook(inputStream);
                    }
                    final String timestamp = "" + System.currentTimeMillis();
                    XSSFSheet sheet = workbook.getSheetAt(0);
                    FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
                    int rowscount = sheet.getPhysicalNumberOfRows();
                    if (rowscount > 0) {
                        // check row wise data
                        for (int r = 0; r < rowscount; r++) {
                            Row row = sheet.getRow(r);
                            if (row.getPhysicalNumberOfCells() == cellCount) {
                                 
                                // get cell data
                                String A = getCellData(row, 0, formulaEvaluator);
                                String B = getCellData(row, 1, formulaEvaluator);
                                 
                                // initialise the hash map and put value of a and b into it
                                HashMap<String, Object> quetionmap = new HashMap<>();
                                quetionmap.put("A", A);
                                quetionmap.put("B", B);
                                String id = UUID.randomUUID().toString();
                                parentmap.put(id, quetionmap);
                            } else {
                                dialog.dismiss();
                                Toast.makeText(MainActivity.this, "row no. " + (r + 1) + " has incorrect data", Toast.LENGTH_LONG).show();
                                return;
                            }
                        }
                        // add the data in firebase if everything is correct
                        runOnUiThread(new Runnable() {
                            @Override
                            public void run() {
                                // add the data according to timestamp
                                FirebaseDatabase.getInstance().getReference().child("Data").
                                        child(timestamp).updateChildren(parentmap).addOnCompleteListener(new OnCompleteListener<Void>() {
                                    @Override
                                    public void onComplete(@NonNull Task<Void> task) {
                                        if (task.isSuccessful()) {
                                            dialog.dismiss();
                                            Toast.makeText(MainActivity.this, "Uploaded Successfully", Toast.LENGTH_LONG).show();
                                        } else {
                                            dialog.dismiss();
                                            Toast.makeText(MainActivity.this, "Something went wrong", Toast.LENGTH_LONG).show();
                                        }
                                    }
                                });
                            }
                        });
                    }
                    // show the error if file is empty
                    else {
                        runOnUiThread(new Runnable() {
                            @Override
                            public void run() {
                                dialog.dismiss();
                                Toast.makeText(MainActivity.this, "File is empty", Toast.LENGTH_LONG).show();
                            }
                        });
                        return;
                    }
                }
                // show the error message if failed
                // due to file not found
                catch (final FileNotFoundException e) {
                    e.printStackTrace();
                    runOnUiThread(new Runnable() {
                        @Override
                        public void run() {
                            Toast.makeText(MainActivity.this, e.getMessage(), Toast.LENGTH_LONG).show();
                        }
                    });
                }
                // show the error message if there 
                // is error in input output
                catch (final IOException e) {
                    e.printStackTrace();
                    runOnUiThread(new Runnable() {
                        @Override
                        public void run() {
                            Toast.makeText(MainActivity.this, e.getMessage(), Toast.LENGTH_LONG).show();
                        }
                    });
                }
            }
        });
    }
 
    private String getCellData(Row row, int cellposition, FormulaEvaluator formulaEvaluator) {
         
        String value = "";
         
        // get cell from excel sheet
        Cell cell = row.getCell(cellposition);
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                return value + cell.getBooleanCellValue();
            case Cell.CELL_TYPE_NUMERIC:
                return value + cell.getNumericCellValue();
            case Cell.CELL_TYPE_STRING:
                return value + cell.getStringCellValue();
            default:
                return value;
        }
    }
}