Android Import Excel Sheet in SQLite Database

This post explains how to import Excel Sheets (.xlxs and .xls files) into Android SQLite Database. I have tested this code on Android 9, 10 and 11 and it works on all these Android versions. This is an updated post as my previous code is no longer supported on Android 10+ devices. I will skip the introduction on how to create a blank Android project in Android Studio.

For importing excel sheet we will be using poishadow-all.jar file library. This will be able to read .XLS as well as .XLSX file formats (which is the exact purpose of our app). You can read more on this from Official Apache Documentations.

Download the jar file add it as Android Library to your app.
build.gradle:

plugins {
id 'com.android.application'
}

android {
compileSdk 32

defaultConfig {
applicationId "app.parallelcodes.excel"
minSdk 21
targetSdk 32
versionCode 1
versionName "1.0"

testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
}

buildTypes {
release {
minifyEnabled false
proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
}
}
compileOptions {
sourceCompatibility JavaVersion.VERSION_1_8
targetCompatibility JavaVersion.VERSION_1_8
}
buildFeatures {
viewBinding true
}
}

dependencies {

implementation 'androidx.appcompat:appcompat:1.4.0'
implementation 'com.google.android.material:material:1.4.0'
implementation 'androidx.constraintlayout:constraintlayout:2.1.2'
implementation 'androidx.navigation:navigation-fragment:2.3.5'
implementation 'androidx.navigation:navigation-ui:2.3.5'
implementation files('src\\main\\libs\\poishadow-all.jar')
testImplementation 'junit:junit:4.+'
androidTestImplementation 'androidx.test.ext:junit:1.1.3'
androidTestImplementation 'androidx.test.espresso:espresso-core:3.4.0'
}

Once you have edited the gradle file, please sync it to make the library classes and methods available in your project.

Now create a class file with name DBHelper.java and edit it as below. This is our SQLite database class. This will create, insert, delete data from our table and also help us in retrieving data from the target table. If you want to learn more on Android SQLite database, please visit this link.

My package name: app.parallelcodes.excel

DBHelper.java:

package app.parallelcodes.excel.helpers;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.util.ArrayList;
import java.util.HashMap;

 

public class DBHelper {

String TAG = "DBAdapter";

public static final String Tablename = "MyTable1";
public static final String id = "_id";// 0 integer
public static final String Company = "Company";// 1 text(String)
public static final String Product = "Product";// 2 integer
public static final String Price = "Price";// 3 date(String)

private SQLiteDatabase db;
private Database dbHelper;

private static final int VERSION = 1;
private static final String DB_NAME = "MyDB1.db";

public DBHelper(Context context) {
dbHelper = new Database(context);
}

public void open() {
if (null == db || !db.isOpen()) {
try {
db = dbHelper.getWritableDatabase();
} catch (SQLiteException sqLiteException) {
}
}
}

public void close() {
if (db != null) {
db.close();
}
}

public int insert(String table, ContentValues values) {
try {
db = dbHelper.getWritableDatabase();
int y = (int) db.insert(table, null, values);
db.close();
Log.e("Data Inserted", "Data Inserted");
Log.e("y", y + "");
return y;
} catch (Exception ex) {
Log.e("Error Insert", ex.getMessage().toString());
return 0;
}
}

public void delete() {
db.execSQL("delete from " + Tablename);
}

public Cursor getAllRow(String table) {
return db.query(table, null, null, null, null, null, id);
}

public ArrayList<HashMap<String, String>> getProducts() {
ArrayList<HashMap<String, String>> prolist;
prolist = new ArrayList<HashMap<String, String>>();
String selectQuery = "SELECT * FROM " + Tablename;
SQLiteDatabase database = dbHelper.getReadableDatabase();
Cursor cursor = database.rawQuery(selectQuery, null);
if (cursor.moveToFirst()) {
do {
HashMap<String, String> map = new HashMap<String, String>();
map.put(Company, cursor.getString(1));
map.put(Product, cursor.getString(2));
map.put(Price, cursor.getString(3));
prolist.add(map);
} while (cursor.moveToNext());
}
return prolist;
}

private class Database extends SQLiteOpenHelper {
private static final int VERSION = 1;
private static final String DB_NAME = "MyDB1.db";

public Database(Context context) {
super(context, DB_NAME, null, VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
String create_sql = "CREATE TABLE IF NOT EXISTS " + Tablename + "("
+ id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ Company + " TEXT ," + Product + " TEXT ,"
+ Price + " TEXT " + ")";
db.execSQL(create_sql);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + Tablename);
}

}
}

Create another class file with name ExcelHelper.java and edit it as below:

ExcelHelper.java:

package app.parallelcodes.excel.helpers;

import android.content.ContentValues;
import android.util.Log;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.Iterator;

public class ExcelHelper {

public static final String Tablename = "MyTable1";
public static final String id = "_id";// 0 integer
public static final String Company = "Company";// 1 text(String)
public static final String Product = "Product";// 2 text(String)
public static final String Price = "Price";// 3 text(String)

public static void insertExcelToSqlite(DBHelper dbAdapter, Sheet sheet) {

for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext(); ) {
Row row = rit.next();

ContentValues contentValues = new ContentValues();
row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellType(CellType.STRING);
row.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellType(CellType.STRING);
row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellType(CellType.STRING);

contentValues.put(Company, row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue());
contentValues.put(Product, row.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue());
contentValues.put(Price, row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue());

try {
if (dbAdapter.insert("MyTable1", contentValues) < 0) {
return;
}
} catch (Exception ex) {
Log.d("Exception in importing", ex.getMessage().toString());
}
}
}
}

This class is for reading the excel sheets provided by the user into the SQLite database.

Edit your activity_mail.xml layout file as below:

res > layout > activity_main.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:background="#fff"
android:id="@+id/main_layout"
android:orientation="vertical"
android:weightSum="10">

<LinearLayout
android:id="@+id/lvcontainer"
android:layout_width="match_parent"
android:layout_height="0dp"
android:layout_alignParentTop="true"
android:layout_weight="0.5"
android:background="#000"
android:orientation="horizontal"
android:padding="1dp"
android:weightSum="3">

<TextView
android:id="@+id/txtCompany"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:gravity="left"
android:text="Company"
android:textColor="#fff"
android:textSize="16sp" />

<TextView
android:id="@+id/txtProduct"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:gravity="left"
android:padding="3dp"
android:text="Product"
android:textColor="#fff"
android:textSize="16sp" />

<TextView
android:id="@+id/txtPrice"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:gravity="left"
android:padding="3dp"
android:text="Price"
android:textColor="#fff"
android:textSize="16sp" />
</LinearLayout>

<ListView
android:id="@+id/lstView"
android:layout_width="match_parent"
android:layout_height="0dp"
android:layout_below="@+id/lvcontainer"
android:layout_weight="8.5">

</ListView>

<TextView
android:id="@+id/txtresulttext"
android:layout_width="match_parent"
android:layout_height="0dp"
android:layout_below="@android:id/list"
android:layout_marginLeft="5dp"
android:layout_marginTop="2dp"
android:layout_weight="1"
android:gravity="left"
android:text=""
android:textColor="#FFF55F54"
android:textSize="10sp"
android:textStyle="italic|bold"></TextView>

</LinearLayout>

This is our main layout file, it only has a listview and a label to display any errors occurred in any of the processes.
Create lst_template.xml layout file, this is the design layout file for the listView which will display excel sheet data from SQLite database.

res > layout > lst_template.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:orientation="vertical">

<LinearLayout
android:id="@+id/lvContainer"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:background="#ffe6e6e6"
android:orientation="horizontal"
android:scrollbars="horizontal"
android:weightSum="3">

<TextView
android:id="@+id/txtproductcompany"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:gravity="left"
android:text="NAME"
android:textColor="#000000"
android:textSize="13sp" />

<TextView
android:id="@+id/txtproductname"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:gravity="left"
android:padding="3dp"
android:text="ISSN"
android:textColor="#000000"
android:textSize="13sp" />

<TextView
android:id="@+id/txtproductprice"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:gravity="left"
android:padding="3dp"
android:text="IMPACTFACTOR"
android:textColor="#000000"
android:textSize="13sp" />

</LinearLayout>
</LinearLayout>

Edit the menu option file as below:

res > menu > menu_main.xml:

<menu 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"
tools:context="app.parallelcodes.excel.MainActivity">
<item
android:id="@+id/action_import_xlxs"
android:orderInCategory="100"
android:title="@string/action_import_xlxs"
app:showAsAction="never" />

<item
android:id="@+id/action_import_xls"
android:orderInCategory="100"
android:title="@string/action_import_xls"
app:showAsAction="never" />
</menu>

Edit the strings.xml file as below:

res > values > strings.xml:

<resources>
<string name="app_name">Excel Reader</string>
<string name="action_import_xlxs">Import .XLXS</string>
<string name="action_import_xls">Import .XLS</string>

<string name="storage_access_required">This app requires your memory access to read excel-sheet files</string>
<string name="storage_access_denied">Please goto app settings and grant memory permission. </string>

<string name="storage_unavailable">Your device does not support storage</string>

</resources>

Now edit the MainActivity.java class file as below:

MainActivity.java:

package app.parallelcodes.excel;

import android.Manifest;
import android.app.Activity;
import android.content.ActivityNotFoundException;
import android.content.Context;
import android.content.Intent;
import android.content.pm.PackageManager;
import android.net.Uri;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.TextView;
import android.widget.Toast;

import androidx.activity.result.ActivityResultLauncher;
import androidx.activity.result.contract.ActivityResultContracts;
import androidx.annotation.NonNull;
import androidx.appcompat.app.AppCompatActivity;
import androidx.core.app.ActivityCompat;

import com.google.android.material.snackbar.Snackbar;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;

import app.parallelcodes.excel.helpers.DBHelper;
import app.parallelcodes.excel.helpers.ExcelHelper;

public class MainActivity extends AppCompatActivity {

static {
System.setProperty(
"org.apache.poi.javax.xml.stream.XMLInputFactory",
"com.fasterxml.aalto.stax.InputFactoryImpl"
);
System.setProperty(
"org.apache.poi.javax.xml.stream.XMLOutputFactory",
"com.fasterxml.aalto.stax.OutputFactoryImpl"
);
System.setProperty(
"org.apache.poi.javax.xml.stream.XMLEventFactory",
"com.fasterxml.aalto.stax.EventFactoryImpl"
);
}

TextView lbl;
DBHelper controller = new DBHelper(this);
ListView lv;

private static final int PERMISSION_REQUEST_MEMORY_ACCESS = 0;
private static String fileType = "";
private View mLayout;
private static String extensionXLS = "XLS";
private static String extensionXLXS = "XLXS";
ActivityResultLauncher<Intent> filePicker;

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

lbl = (TextView) findViewById(R.id.txtresulttext);
lv = findViewById(R.id.lstView);
mLayout = findViewById(R.id.main_layout);

filePicker = registerForActivityResult(
new ActivityResultContracts.StartActivityForResult(),
result -> {
if (result.getResultCode() == Activity.RESULT_OK) {

Intent intent1 = result.getData();

Uri uri = intent1.getData();
ReadExcelFile(MainActivity.this
, uri);

}
});
FillList();
}

private boolean CheckPermission() {
if (ActivityCompat.checkSelfPermission(this, Manifest.permission.READ_EXTERNAL_STORAGE)
== PackageManager.PERMISSION_GRANTED) {
return true;
} else {
Snackbar.make(mLayout, R.string.storage_access_required,
Snackbar.LENGTH_INDEFINITE).setAction("OK", new View.OnClickListener() {
@Override
public void onClick(View view) {
requestStoragePermission();
}
}).show();

return false;
}
}

public void FillList() {
try {
if (controller == null) {
DBHelper controller = new DBHelper(MainActivity.this);
}
ArrayList<HashMap<String, String>> myList = controller.getProducts();
if (myList.size() != 0) {
lv = findViewById(R.id.lstView);
ListAdapter adapter = new SimpleAdapter(MainActivity.this, myList,
R.layout.lst_template, new String[]{DBHelper.Company, DBHelper.Product,
DBHelper.Price},
new int[]{R.id.txtproductcompany, R.id.txtproductname,
R.id.txtproductprice});
lv.setAdapter(adapter);
}
} catch (Exception ex) {
Toast("FillList error: " + ex.getMessage(), ex);
}
}

public void ReadExcelFile(Context context, Uri uri) {
try {
InputStream inStream;
Workbook wb = null;

try {
inStream = context.getContentResolver().openInputStream(uri);

if (fileType == extensionXLS)
wb = new HSSFWorkbook(inStream);
else
wb = new XSSFWorkbook(inStream);

inStream.close();
} catch (IOException e) {
lbl.setText("First " + e.getMessage().toString());
e.printStackTrace();
}

DBHelper dbAdapter = new DBHelper(this);
Sheet sheet1 = wb.getSheetAt(0);

dbAdapter.open();
dbAdapter.delete();
dbAdapter.close();
dbAdapter.open();
ExcelHelper.insertExcelToSqlite(dbAdapter, sheet1);

dbAdapter.close();

FillList();
} catch (Exception ex) {
Toast("ReadExcelFile Error:" + ex.getMessage().toString(), ex);
}
}

public void ChooseFile() {
try {
Intent fileIntent = new Intent(Intent.ACTION_GET_CONTENT);
fileIntent.addCategory(Intent.CATEGORY_OPENABLE);

if (fileType == extensionXLS)
fileIntent.setType("application/vnd.ms-excel");
else
fileIntent.setType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

filePicker.launch(fileIntent);
} catch (Exception ex) {
Toast("ChooseFile error: " + ex.getMessage().toString(), ex);

}
}

void Toast(String message, Exception ex) {
if (ex != null)
Log.e("Error", ex.getMessage().toString());
Toast.makeText(MainActivity.this, message, Toast.LENGTH_LONG).show();

}

@Override
public void onRequestPermissionsResult(int requestCode, @NonNull String[] permissions,
@NonNull int[] grantResults) {
super.onRequestPermissionsResult(requestCode, permissions, grantResults);
if (requestCode == PERMISSION_REQUEST_MEMORY_ACCESS) {
if (grantResults.length == 1 && grantResults[0] == PackageManager.PERMISSION_GRANTED) {
OpenFilePicker();
} else {
Snackbar.make(mLayout, R.string.storage_access_denied,
Snackbar.LENGTH_SHORT)
.show();
}
}
}

private void requestStoragePermission() {

if (ActivityCompat.checkSelfPermission(this,
Manifest.permission.READ_EXTERNAL_STORAGE) != PackageManager.PERMISSION_GRANTED) {

ActivityCompat.requestPermissions(MainActivity.this,
new String[]{Manifest.permission.READ_EXTERNAL_STORAGE},
PERMISSION_REQUEST_MEMORY_ACCESS);

} else {
Snackbar.make(mLayout, R.string.storage_unavailable, Snackbar.LENGTH_SHORT).show();
ActivityCompat.requestPermissions(this,
new String[]{Manifest.permission.CAMERA}, PERMISSION_REQUEST_MEMORY_ACCESS);
}
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
getMenuInflater().inflate(R.menu.menu_main, menu);
return true;
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
int id = item.getItemId();

if (id == R.id.action_import_xls) {
fileType = extensionXLS;
OpenFilePicker();
} else if (id == R.id.action_import_xlxs) {
fileType = extensionXLXS;
OpenFilePicker();
}

return super.onOptionsItemSelected(item);
}

public void OpenFilePicker() {
try {
if (CheckPermission()) {
ChooseFile();
}
} catch (ActivityNotFoundException e) {
lbl.setText("No activity can handle picking a file. Showing alternatives.");
}

}
}

The app will allow to select any excel file (.xls and .xlxs files) when the user clicks on the menu to import one, it will first check for the permission to read external storage. If the permissions are not granted, app will ask for the user permission to access external storage and then file as per the user selection.
The data from the excel sheet will be fist imported to Android SQLite database and then the newly inserted data will be display on the listView.

DOWNLOAD THIS PROJECT