How to Insert/Append Data to Excel using Android?

File handling in Java is used to read and write data to a file. The particular file class from the package called java.io allows us to handle and work with different formats of files. File handling allows users to store data permanently in a file. And the best or suggested document for storing data is Microsoft Excel because it provides the organized storage of data i.e. in rows and columns, easy calculations on data, and easy data analysis.

Note: In order, to view an excel sheet, any excel sheet viewer must be installed in the emulator or real device e.g. Microsoft Excel

Requirements

Excel File handling in java requires the implementation of the following programming concepts:

1. FileInputStream Class
2. FileOutputStream Class
3. Apache POI Library

FileInputStream Class

Class of java.io package which is used to read byte data from a file. In order to create a file input stream, import the given package in the java file

import java.io.FileInputStream;

Syntax:

FileInputStream input = new FileInputStream(filePath);

FileOutputStream Class

Class of java.io package which is used to write byte data to file. In order to create a file output stream, import the given package in the java file

import java.io.FileOutputStream;

Syntax:

FileOutputStream output = new FileOutputStream(filePath);

Apache POI Library

Apache POI is a Java Library that is used to handle Microsoft Office Documents. Apache POI is open source, and can be used by JVM-based programming languages. Apache POI provides the basis and advanced functionality of Apache POI Technology.

Dependency (build.gradle:app):

implementation ‘org.apache.poi:poi:3.17’

Implementation Steps

Step 1: Add implementation ‘org.apache.poi:poi:3.17’ dependency in build.gradle:app in Android Studio and Sync Project to commit changes.

Step 2: Add code for asking permission to read and write in the external storage of the device from the user in the AndroidManifest.xml file in Android Studio.

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

Step 3: Write the given code in your xml layout file (e.g. activity_main)

XML

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity">

    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_margin="30dp"
        android:hint="Enter Name"
        android:id="@+id/eName"/>

    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_margin="30dp"
        android:hint="Enter File Name"
        android:id="@+id/eFileName"/>

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_margin="30dp"
        android:text="Add to Excel"
        android:backgroundTint="#34AF00"
        android:id="@+id/btnCreate" />

</LinearLayout>

Step 4: Write the given code in your java activity file (e.g. MainActivity.java)

Java

// program to create/append data to excel file using apache poi

import static java.nio.file.StandardOpenOption.APPEND;

import androidx.appcompat.app.AppCompatActivity;
import androidx.core.app.ActivityCompat;

import android.Manifest;

import android.content.pm.PackageManager;
import android.os.Bundle;
import android.os.Environment;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.StandardOpenOption;

public class MainActivity extends AppCompatActivity {

    EditText eName, eFileName;
    Button btnCreate;

    File filePath;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        eName = findViewById(R.id.eName);
        eFileName = findViewById(R.id.eFileName);
        btnCreate = findViewById(R.id.btnCreate);

        ActivityCompat.requestPermissions(this, new String[]{Manifest.permission.WRITE_EXTERNAL_STORAGE, Manifest.permission.READ_EXTERNAL_STORAGE}
                , PackageManager.PERMISSION_GRANTED);

        btnCreate.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                filePath = new File(Environment.getExternalStorageDirectory() + "/" + eFileName.getText().toString() + ".xls");

                try {

                    if (!filePath.exists()) {
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
                HSSFSheet hssfSheet = hssfWorkbook.createSheet("MySheet");

                HSSFRow hssfRow = hssfSheet.createRow(0);
                HSSFCell hssfCell = hssfRow.createCell(0);

                hssfCell.setCellValue(eName.getText().toString());
                        filePath.createNewFile();
                        FileOutputStream fileOutputStream = new FileOutputStream(filePath);
                        hssfWorkbook.write(fileOutputStream);
                        eName.setText("");
                        Toast.makeText(MainActivity.this, "File Created", Toast.LENGTH_SHORT).show();

                        if (fileOutputStream != null) {
                            fileOutputStream.flush();
                            fileOutputStream.close();
                        }
                    }

                    else{

                        FileInputStream fileInputStream = new FileInputStream(filePath);
                        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);
                        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
                        int lastRowNum = hssfSheet.getLastRowNum();

                        HSSFRow hssfRow = hssfSheet.createRow(++lastRowNum);
                        hssfRow.createCell(0).setCellValue(eName.getText().toString());

                        fileInputStream.close();

                        FileOutputStream fileOutputStream = new FileOutputStream(filePath);
                        hssfWorkbook.write(fileOutputStream);
                        eName.setText("");
                        Toast.makeText(MainActivity.this, "File Updated", Toast.LENGTH_SHORT).show();
                        fileOutputStream.close();
                    }


                } catch (Exception e) {
                    e.printStackTrace();

                }
            }
        });
    }
}

Step 5: Run the app

Screenshot(s) app:

Inserting new record to GeeksForGeeks.xls

appending new record to GeeksForGeeks.xls

Screenshot(s) Excel:

Created excel file : GeeksForGeeks.xls