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 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; } }} |
Output:
Data saved in Database in this way
GitHub link: https://github.com/Anni1123/UploadDataExcelSheet
