Android Import Excel Sheet in SQLite Database

  • by
Android-Excel-Sheet-Import-Sqlite-Database

In this post we will create an Android application for importing Excel sheet into SQLite database. The application will be able to read both .xls and .xlsx excel sheet formats. It will have three fields. Product, Company and Price. I will provide sample sheets in the project source code download files at the end of this post. So let’s begin.

DOWNLOAD SOURCE CODE

The app will have only one activity. This will help us in selecting, uploading excel sheet and getting data from our Android SQLite database table. It is same like my previous post on Android Import CSV file into SQLite DB. Create a layout file with name activity_main.xml as edit it 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: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="@android:id/list"
android:layout_width="match_parent"
android:layout_height="0dp"
android:layout_below="@+id/lvcontainer"
android:layout_weight="8">

</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
android:id="@+id/lvbottom"
android:layout_width="match_parent"
android:layout_height="0dp"
android:layout_weight="0.5"
android:orientation="horizontal"
android:weightSum="1">

<Button
android:id="@+id/btnupload"
android:layout_width="0dp"
android:layout_height="match_parent"
android:layout_weight="1"
android:background="#1083f5"
android:gravity="center"
android:text="UPLOAD"
android:textColor="#ffffff"
android:textSize="15sp"
android:textStyle="bold" />
</LinearLayout>

</LinearLayout>

Create another layout file with name lst_template.xml as edit it as below:

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>

This will be a template file for our Android ListView. The layout is fairly simple to understand.

  1. Click on Upload button
  2. Select your Excel sheet
  3. File will be imported into Android SQLite database if it is a valid excel sheet.

Android-Excel-Sheet-Import-Sqlite-Database

Sample Excel sheet (.xlsx format):

android-excel-sheet-import-sqlite-01

Okay! So let’s begin coding:

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 (app):

apply plugin: 'com.android.application'

android {
compileSdkVersion 28
buildToolsVersion "29.0.0"

defaultConfig {
applicationId "parallelcodes.excelreader"
minSdkVersion 21
targetSdkVersion 28
versionCode 1
versionName "1.0"

testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
}

buildTypes {
release {
minifyEnabled false
proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
}
}

}

dependencies {
implementation fileTree(include: ['*.jar'], dir: 'libs')
implementation 'androidx.appcompat:appcompat:1.1.0'
implementation 'androidx.constraintlayout:constraintlayout:1.1.3'
testImplementation 'junit:junit:4.12'
androidTestImplementation 'androidx.test.ext:junit:1.1.1'
androidTestImplementation 'androidx.test.espresso:espresso-core:3.2.0'
implementation files('libs/poishadow-all.jar')

}

Now create a class file with name XlsxCon.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 our target table. If you want to learn more on Android SQLite database, please visit this link.

My package name: parallelcodes.excelreader

XlsxCon.java:

package parallelcodes.excelreader.excel;

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 XlsxCon {
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 DBHelper dbHelper;

public XlsxCon(Context context) {
dbHelper = new DBHelper(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);
}

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

public DBHelper(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);
}

}

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;
}
}

Now create another class file with name ExcelHelper.java and edit it as below. This class will be interacting with our database for adding the data and reading the rows from the excel sheet. We will pass excel.Sheet object to this class.

ExcelHelper.java:

package parallelcodes.excelreader.excel;

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(XlsxCon 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());
}
}
}
}

Now edit your MainActivity.java class file as below:

MainActivity.java:

package parallelcodes.excelreader;

import android.app.ListActivity;
import android.content.ActivityNotFoundException;
import android.content.Intent;
import android.content.res.AssetManager;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.TextView;

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

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

import parallelcodes.excelreader.excel.ExcelHelper;
import parallelcodes.excelreader.excel.XlsxCon;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class MainActivity extends ListActivity {

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;
XlsxCon controller = new XlsxCon(this);
Button btnimport;
ListView lv;
public static final int requestcode = 1;
static String tableName;

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)

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

btnimport = (Button) findViewById(R.id.btnupload);

lbl = (TextView) findViewById(R.id.txtresulttext);
lv = getListView();
tableName = "info";

btnimport.setOnClickListener(new View.OnClickListener() {

@Override
public void onClick(View v) {
Intent fileintent = new Intent(Intent.ACTION_GET_CONTENT);
fileintent.setType("gagt/sdf");
try {
startActivityForResult(fileintent, requestcode);
} catch (ActivityNotFoundException e) {
lbl.setText("No activity can handle picking a file. Showing alternatives.");
}

}
});
ArrayList<HashMap<String, String>> myList = controller.getProducts();
if (myList.size() != 0) {
lv = getListView();
ListAdapter adapter = new SimpleAdapter(MainActivity.this, myList,
R.layout.lst_template, new String[]{Company, Product,
Price},
new int[]{R.id.txtproductcompany, R.id.txtproductname,
R.id.txtproductprice});
setListAdapter(adapter);
}

}

protected void onActivityResult(int requestCode, int resultCode, Intent data) {
if (data == null)
return;
switch (requestCode) {
case requestcode:
String FilePath = data.getData().getPath();

Log.e("File path", FilePath);

if (FilePath.contains("/root_path"))
FilePath = FilePath.replace("/root_path", "");

Log.e("New File path", FilePath);

try {
if (resultCode == RESULT_OK) {
AssetManager am = this.getAssets();
InputStream inStream;
Workbook wb = null;

try {
inStream = new FileInputStream(FilePath);
Log.e("Extension",FilePath.substring(FilePath.lastIndexOf(".")));

if (FilePath.substring(FilePath.lastIndexOf(".")).equals(".xls")) {
Log.e("File Type", "Selected file is XLS");
wb = new HSSFWorkbook(inStream);
}
else if (FilePath.substring(FilePath.lastIndexOf(".")).equals(".xlsx")) {
Log.e("File Type", "Selected file is XLSX");
wb = new XSSFWorkbook(inStream);
}
else {
wb = null;
lbl.setText("Please select a valid Excel file");
return;

}

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

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

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

dbAdapter.close();

}
} catch (Exception ex) {
lbl.setText(ex.getMessage().toString() + "Second");
Log.e("POI Error", ex.getMessage().toString());
}

ArrayList<HashMap<String, String>> myList = controller
.getProducts();
if (myList.size() != 0) {
ListView lv = getListView();
ListAdapter adapter = new SimpleAdapter(MainActivity.this, myList,
R.layout.lst_template, new String[]{Company, Product,
Price},
new int[]{R.id.txtproductcompany, R.id.txtproductname,
R.id.txtproductprice});
setListAdapter(adapter);
}
}
}
}

Add permission to your AndroidManifest.xml file for reading External storage as below:

AndroidManifest.xml:

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="parallelcodes.excelreader">

<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />
<application
android:allowBackup="true"
android:icon="@mipmap/ic_launcher"
android:label="@string/app_name"
android:roundIcon="@mipmap/ic_launcher_round"
android:supportsRtl="true"
android:theme="@style/AppTheme">
<activity android:name=".MainActivity">
<intent-filter>
<action android:name="android.intent.action.MAIN" />

<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>

</manifest>

DOWNLOAD SOURCE CODE

References:
Android + Proguard + Apache POI

As of now this code works with: Total Commander file manager.

We are working towards solving file selection issue.

Thank you.


  1. Base64Encode.io - Realtime Encode your string to Base64 format.
  2. Base64Decode.io - Realtime Decode Base64 data to plain text.
  3. Md5Hash.io - Realtime Encoding in Md5Hash.

1 thought on “Android Import Excel Sheet in SQLite Database”

  1. Pingback: Android Import CSV file to SQLite Database • ParallelCodes

Comments are closed.