Post
Ask Question

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

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 the SQLiteToExcel library to perform these functionalities.
 

SQLiteToExcel

 
It is a Lightweight 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 a New Project with Android Studio.
 
Step 2
 
Setting up the library and AndroidManifest for the project.
 
Step 3
 
Creating a 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 a New Project with Android Studio
  1. Open Android Studio and Select Create a new project.
  2. Name the project as you wish and select your activity template.
     
    SQLite Database
  1. Click the “Finish” button to create a 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 Now” to 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 a 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 show 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 to 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 a 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 like an excel file to the default location or the user-defined location. This library allows us to 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 a 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 the 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.   
  118. } 
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.     }  
  83. } 
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 found here.


Similar Articles