Converting SQLite Database To Excel And Excel To SQLite Data In Android

In this article, we will learn how to export Android SQLite Database to Excel or Import Excel into SQLite Database. We can use SQLiteToExcel library to perform these functionalities.

SQLite Database

Introduction

In this article, we will learn how to export Android SQLite Database to Excel or Import Excel into SQLite Database. We can use SQLiteToExcel library to perform these functionalities.

SQLiteToExcel

It is a Light weight Library to Convert SQLite Database to Excel and Convert Excel to SQLite and it is open sourced in GitHub. You can find the Library in GitHub. It is small in size and its size around “16kb” only. This library is powered by “apache - poi”.

I have divided this Implementation into 4 steps as shown in the following.

Step 1

Creating New Project with Android Studio

Step 2

Setting up the library and AndroidManifest for the project

Step 3

Creating SQLite Database  

Step 4

Exporting SQLite Database to Excel.

Step 5

Importing Excel into SQLite.

Without any more introduction, we will jump into the coding part.

Step 1 - Creating New Project with Android Studio

  1. Open Android Studio and Select Create new project.
  2. Name the project as your wish and select your activity template.

    SQLite Database
  1. Click finish button to create new project in Android Studio.

Step 2 - Setting up the library and AndroidManifest for the project

  1. Open your app level build.gradle file and add the SQLiteToExcel library using the following line.

    compile 'com.ajts.androidmads.SQLite2Excel:library:1.0.2'
  1. Then click Sync Nowto add the library.
  2. Now open your Manifest File (AndroidManifest.xml) and the following permission.

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

Step 3 - Creating SQLite Database

  1. We are aware of the usage of SQLite in Android. If you want to know how to implement SQLite operations, click here.
  2. The given codes in the link shows how to create the implementation of SQLite in Android with SQLiteOpenHelper and how to perform the DB operations with SQLite. Above code will generate SQLite Database and tables.

Step 4 - Exporting SQLite Database to Excel

  1. Open your java or any Activity you want add the functionality and add the following lines to initialize the library. By using this, you can export the file into the default location.

    SqliteToExcel sqliteToExcel = new SqliteToExcel(this, "helloworld.db");
  1. If you want to export the file in user preferred path, use the following.

    SqliteToExcel sqliteToExcel = new SqliteToExcel(this, "helloworld.db", directory_path);

    Here, “helloworld.db” is the name of the SQLite Database created in Android Application.
  1. In this step, we will learn how to call the library to export SQLite database as excel file to the default location or the user defined location. This library allow us export a single table or a list of tables or the whole database as Excel.

SINGLE TABLE

The following lines are used to export a single table.

  1. sqliteToExcel.exportSingleTable("table1""table1.xls"new SQLiteToExcel.ExportListener() {  
  2.      @Override  
  3.      public void onStart() {  
  4.      }  
  5.      @Override  
  6.      public void onCompleted(String filePath) {  
  7.      }  
  8.      @Override  
  9.      public void onError(Exception e) {  
  10.      }  
  11. });  

LIST OF TABLES

The following lines are used to export a list of tables.

  1. sqliteToExcel.exportSingleTable(table1List, "table1.xls"new SQLiteToExcel.ExportListener() {  
  2.      @Override  
  3.      public void onStart() {  
  4.      }  
  5.      @Override  
  6.      public void onCompleted(String filePath) {  
  7.      }  
  8.      @Override  
  9.      public void onError(Exception e) {  
  10.      }  
  11. });  

WHOLE DATABASE

The following lines are used to export a list of tables.

  1. sqliteToExcel. exportAllTables ("table1.xls"new SQLiteToExcel.ExportListener() {  
  2.      @Override  
  3.      public void onStart() {  
  4.      }  
  5.      @Override  
  6.      public void onCompleted(String filePath) {  
  7.      }  
  8.      @Override  
  9.      public void onError(Exception e) {  
  10.      }  
  11. });  

Step 5 - Importing Excel into SQLite Database

In this step, we will learn how to call the library to import excel file into SQLite database.

  1. The following lines are used to initialize the library for Excel to Database conversion.

    ExcelToSQLite excelToSQLite = new ExcelToSQLite(getApplicationContext(), "helloworld.db");
  1. If you want to import table with dropping existing table in your DB use the following.

    ExcelToSQLite excelToSQLite = new ExcelToSQLite(getApplicationContext(), "helloworld.db", true);

    Here, helloworld.db is the name of the SQLite Database created in Android Application.
  1. We can import the excel files from Assets folder or from Directory Path with file name and extensions.

IMPORT FROM ASSETS

Use the following to import excel from assets folder of the application.

  1. excelToSQLite.importFromAsset("assetFileName.xls"new ExcelToSQLite.ImportListener() {  
  2.     @Override  
  3.     public void onStart() {  
  4.   
  5.     }  
  6.   
  7.     @Override  
  8.     public void onCompleted(String dbName) {  
  9.   
  10.     }  
  11.   
  12.     @Override  
  13.     public void onError(Exception e) {  
  14.   
  15.     }  
  16. });  

IMPORT FROM DIRECTORY

Use the following lines are used to import excel files from user defined or chosen path.

  1. excelToSQLite.importFromFile(directory_path, new ExcelToSQLite.ImportListener() {  
  2.     @Override  
  3.     public void onStart() {  
  4.   
  5.     }  
  6.   
  7.     @Override  
  8.     public void onCompleted(String dbName) {  
  9.   
  10.     }  
  11.   
  12.     @Override  
  13.     public void onError(Exception e) {  
  14.   
  15.     }  
  16. });  

Full Code of the Application

Full code of SQLite2ExcelActivity.java

  1. package com.ajts.androidmads.sqlite2xlDemo;  
  2.   
  3. import android.os.Bundle;  
  4. import android.os.Environment;  
  5. import android.support.v7.app.AppCompatActivity;  
  6. import android.view.MenuItem;  
  7. import android.view.View;  
  8. import android.widget.Button;  
  9. import android.widget.EditText;  
  10. import android.widget.ListView;  
  11.   
  12. import com.ajts.androidmads.library.SQLiteToExcel;  
  13. import com.ajts.androidmads.sqlite2xlDemo.adapter.CustomAdapter;  
  14. import com.ajts.androidmads.sqlite2xlDemo.db.DBHelper;  
  15. import com.ajts.androidmads.sqlite2xlDemo.db.DBQueries;  
  16. import com.ajts.androidmads.sqlite2xlDemo.model.Users;  
  17. import com.ajts.androidmads.sqlite2xlDemo.util.Utils;  
  18.   
  19. import java.io.File;  
  20. import java.util.ArrayList;  
  21. import java.util.List;  
  22.   
  23. public class SQLite2ExcelActivity extends AppCompatActivity {  
  24.   
  25.     EditText edtUser, edtContactNo;  
  26.     Button btnSaveUser, btnExport;  
  27.     ListView lvUsers;  
  28.     CustomAdapter lvUserAdapter;  
  29.     List<Users> usersList = new ArrayList<>();  
  30.   
  31.     DBHelper dbHelper;  
  32.     DBQueries dbQueries;  
  33.   
  34.     @Override  
  35.     protected void onCreate(Bundle savedInstanceState) {  
  36.         super.onCreate(savedInstanceState);  
  37.         setContentView(R.layout.activity_sqlite_2_xl);  
  38.   
  39.         assert getSupportActionBar() != null;  
  40.         getSupportActionBar().setDisplayHomeAsUpEnabled(true);  
  41.   
  42.         dbHelper = new DBHelper(getApplicationContext());  
  43.         dbQueries = new DBQueries(getApplicationContext());  
  44.   
  45.         edtUser = (EditText) findViewById(R.id.edt_user);  
  46.         edtContactNo = (EditText) findViewById(R.id.edt_c_no);  
  47.         btnSaveUser = (Button) findViewById(R.id.btn_save_user);  
  48.         btnExport = (Button) findViewById(R.id.btn_export);  
  49.   
  50.         lvUsers = (ListView) findViewById(R.id.lv_users);  
  51.         dbQueries.open();  
  52.         usersList = dbQueries.readUsers();  
  53.         lvUserAdapter = new CustomAdapter(getApplicationContext(), usersList);  
  54.         lvUsers.setAdapter(lvUserAdapter);  
  55.         dbQueries.close();  
  56.   
  57.         btnSaveUser.setOnClickListener(new View.OnClickListener() {  
  58.             @Override  
  59.             public void onClick(View view) {  
  60.                 if (validate(edtUser) && validate(edtContactNo)) {  
  61.                     dbQueries.open();  
  62.                     Users users = new Users(edtUser.getText().toString(), edtContactNo.getText().toString());  
  63.                     dbQueries.insertUser(users);  
  64.                     usersList = dbQueries.readUsers();  
  65.                     lvUserAdapter = new CustomAdapter(getApplicationContext(), usersList);  
  66.                     lvUsers.setAdapter(lvUserAdapter);  
  67.                     dbQueries.close();  
  68.                     Utils.showSnackBar(view, "Successfully Inserted");  
  69.                 }  
  70.             }  
  71.         });  
  72.   
  73.         btnExport.setOnClickListener(new View.OnClickListener() {  
  74.             @Override  
  75.             public void onClick(final View view) {  
  76.                 String directory_path = Environment.getExternalStorageDirectory().getPath() + "/Backup/";  
  77.                 File file = new File(directory_path);  
  78.                 if (!file.exists()) {  
  79.                     file.mkdirs();  
  80.                 }  
  81.                 // Export SQLite DB as EXCEL FILE  
  82.                 SQLiteToExcel sqliteToExcel = new SQLiteToExcel(getApplicationContext(), DBHelper.DB_NAME, directory_path);  
  83.                 sqliteToExcel.exportAllTables("users.xls"new SQLiteToExcel.ExportListener() {  
  84.                     @Override  
  85.                     public void onStart() {  
  86.   
  87.                     }  
  88.   
  89.                     @Override  
  90.                     public void onCompleted(String filePath) {  
  91.                         Utils.showSnackBar(view, "Successfully Exported");  
  92.                     }  
  93.   
  94.                     @Override  
  95.                     public void onError(Exception e) {  
  96.   
  97.                     }  
  98.                 });  
  99.             }  
  100.         });  
  101.     }  
  102.   
  103.     boolean validate(EditText editText) {  
  104.         if (editText.getText().toString().length() == 0) {  
  105.             editText.setError("Field Required");  
  106.             editText.requestFocus();  
  107.         }  
  108.         return editText.getText().toString().length() > 0;  
  109.     }  
  110.   
  111.     @Override  
  112.     public boolean onOptionsItemSelected(MenuItem item) {  
  113.         if (item.getItemId() == android.R.id.home)  
  114.             onBackPressed();  
  115.         return true;  
  116.     }  
  117.   

Full code of Excel2SQLiteActivity.java

  1. package com.ajts.androidmads.sqlite2xlDemo;  
  2.   
  3. import android.os.Bundle;  
  4. import android.os.Environment;  
  5. import android.support.v7.app.AppCompatActivity;  
  6. import android.view.MenuItem;  
  7. import android.view.View;  
  8. import android.widget.Button;  
  9. import android.widget.EditText;  
  10.   
  11. import com.ajts.androidmads.library.ExcelToSQLite;  
  12. import com.ajts.androidmads.sqlite2xlDemo.db.DBHelper;  
  13. import com.ajts.androidmads.sqlite2xlDemo.db.DBQueries;  
  14. import com.ajts.androidmads.sqlite2xlDemo.util.Utils;  
  15.   
  16. import java.io.File;  
  17.   
  18. public class Excel2SQLiteActivity extends AppCompatActivity {  
  19.   
  20.     EditText edtFilePath;  
  21.     Button btnImport;  
  22.     DBHelper dbHelper;  
  23.     DBQueries dbQueries;  
  24.     String directory_path = Environment.getExternalStorageDirectory().getPath() + "/Backup/users.xls";  
  25.   
  26.     @Override  
  27.     protected void onCreate(Bundle savedInstanceState) {  
  28.         super.onCreate(savedInstanceState);  
  29.         setContentView(R.layout.activity_xl_2_sqlite);  
  30.   
  31.         dbHelper = new DBHelper(getApplicationContext());  
  32.         dbQueries = new DBQueries(getApplicationContext());  
  33.   
  34.         assert getSupportActionBar() != null;  
  35.         getSupportActionBar().setDisplayHomeAsUpEnabled(true);  
  36.   
  37.         edtFilePath = (EditText) findViewById(R.id.edt_file_path);  
  38.         btnImport = (Button) findViewById(R.id.btn_import);  
  39.         edtFilePath.setText(directory_path);  
  40.         btnImport.setOnClickListener(new View.OnClickListener() {  
  41.             @Override  
  42.             public void onClick(final View view) {  
  43.                 File file = new File(directory_path);  
  44.                 if (!file.exists()) {  
  45.                     Utils.showSnackBar(view, "No file");  
  46.                     return;  
  47.                 }  
  48.                 dbQueries.open();  
  49.                 // Is used to import data from excel without dropping table  
  50.                 // ExcelToSQLite excelToSQLite = new ExcelToSQLite(getApplicationContext(), DBHelper.DB_NAME);  
  51.   
  52.                 // if you want to add column in excel and import into DB, you must drop the table  
  53.                 ExcelToSQLite excelToSQLite = new ExcelToSQLite(getApplicationContext(), DBHelper.DB_NAME, false);  
  54.                 // Import EXCEL FILE to SQLite  
  55.                 excelToSQLite.importFromFile(directory_path, new ExcelToSQLite.ImportListener() {  
  56.                     @Override  
  57.                     public void onStart() {  
  58.   
  59.                     }  
  60.   
  61.                     @Override  
  62.                     public void onCompleted(String dbName) {  
  63.                         Utils.showSnackBar(view, "Excel imported into " + dbName);  
  64.                     }  
  65.   
  66.                     @Override  
  67.                     public void onError(Exception e) {  
  68.                         Utils.showSnackBar(view, "Error : " + e.getMessage());  
  69.                     }  
  70.                 });  
  71.                 dbQueries.close();  
  72.             }  
  73.         });  
  74.   
  75.     }  
  76.   
  77.     @Override  
  78.     public boolean onOptionsItemSelected(MenuItem item) {  
  79.         if (item.getItemId() == android.R.id.home)  
  80.             onBackPressed();  
  81.         return true;  
  82.     }  

Note

  • If you find any issues in this library, feel free report the issues in GitHub issue
  • You can find the WIKI or documentation for the usage and its features here.

Download Code

You can find the library in GitHub. If you like this library, do star the library in GitHub and share this library. The sample implementation of this library can be find here.