Android Export SQLite Data into Excel Sheet(.xls) using Java Excel Library

10

In this Android tutorial, we will learn how to export SQLite Database into a Excel Sheet in a Android Application.



Introduction

SQLite is a database engine of Android Device. In this tutorial, we will get the data from Sqlite and export(dump) in the excel file using Java Excel Library.
Here, I am not explaining the SQLite Database. We will use only SQLite database to get data.

Create a new project

We are going to create a new android project. Go to File ⇒ New ⇒ New Projects in Android studio.

Add Permission

To create an Excel File with data in Android device, we need to add user permission in the AndroidManifest.xml file

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

Download Java Excel File

We need to download Java Excel library to create Excel File. Unzip downloaded Jar and paste it into app/libs folder.

Compile Jar

To compile Jar file, we need to add it in the dependencies into build.gradile file.

...
compile files('libs/jxl.jar')
...

Create DBHelper Class

We need to create a class named as DBHealper. In this class, we will create a Database and insert into data. This data, we will select and export into a Excel Sheet.

package in.studytutorial.AndroidExportDatabaseToExcel;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.HashMap;

public class DBHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "studytutorial";
    private static final int DATABASE_VERSION = 1;
    private HashMap hp;
    public String table_name = "users";


    public DBHelper(Context context) {
        super(context, DATABASE_NAME , null, DATABASE_VERSION);
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(
                "CREATE TABLE IF NOT EXISTS " + table_name +
                        "(id integer primary key, user_name text, phone_number text)"
        );
    }

    public void insertData(){
        SQLiteDatabase db1 = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("user_name", "Abhay");
        contentValues.put("phone_number", "9971634265");
        db1.insert(table_name, null, contentValues);
    }

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

    }

    public Cursor getuser() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from " + table_name + " ",
                null);
        return res;
    }
}

Initialize DBHealper Class

To insert and select data, we need to initialize DBHealper class in the MainActivity class and call insertData() and getuser() method.


public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        DBHelper dbHelper = new DBHelper(this);
        dbHelper.insertData();

        final Cursor cursor = dbHelper.getuser();
    }   
}

Generate Excel File

  • Create File Object

    We need create File Object with excel file directory and excel name. Pass the directory path where we want to store excel.

    
    public class MainActivity extends AppCompatActivity {
    
        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
            
            DBHelper dbHelper = new DBHelper(this);
            dbHelper.insertData();
    
            final Cursor cursor = dbHelper.getuser();
    
            File sd = Environment.getExternalStorageDirectory();
            String csvFile = "myData.xls";
    
            File directory = new File(sd.getAbsolutePath());
            //create directory if not exist
            if (!directory.isDirectory()) {
                directory.mkdirs();
            }
            try {
    
                //file path
                File file = new File(directory, csvFile);
           } catch(){
              e.printStackTrace();
           }
        }   
    }
    
  • Define WorkbookSetting

    We need to create WorkbookSetting by initializing WorkbookSettings class. Define Locale and Pass the file object and WorkbookSetting to the WritableWorkbook object.

    
    public class MainActivity extends AppCompatActivity {
    
        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
            
            DBHelper dbHelper = new DBHelper(this);
            dbHelper.insertData();
    
            final Cursor cursor = dbHelper.getuser();
    
            File sd = Environment.getExternalStorageDirectory();
            String csvFile = "myData.xls";
    
            File directory = new File(sd.getAbsolutePath());
            //create directory if not exist
            if (!directory.isDirectory()) {
                directory.mkdirs();
            }
            try {
    
                //file path
                File file = new File(directory, csvFile);
                WorkbookSettings wbSettings = new WorkbookSettings();
                wbSettings.setLocale(new Locale("en", "EN"));
                WritableWorkbook workbook;
                workbook = Workbook.createWorkbook(file, wbSettings);
    
           } catch(){
              e.printStackTrace();
           }
        }   
    }
    
  • Create Excel Sheet

    WritableWorkbook class helps to create Excel Sheet. We will call createSheet() method to initialize Sheet name. Below code define first sheet name as a UserList.

    
    public class MainActivity extends AppCompatActivity {
    
        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
            
            DBHelper dbHelper = new DBHelper(this);
            dbHelper.insertData();
    
            final Cursor cursor = dbHelper.getuser();
    
            File sd = Environment.getExternalStorageDirectory();
            String csvFile = "myData.xls";
    
            File directory = new File(sd.getAbsolutePath());
            //create directory if not exist
            if (!directory.isDirectory()) {
                directory.mkdirs();
            }
            try {
    
                //file path
                File file = new File(directory, csvFile);
                WorkbookSettings wbSettings = new WorkbookSettings();
                wbSettings.setLocale(new Locale("en", "EN"));
                WritableWorkbook workbook;
                workbook = Workbook.createWorkbook(file, wbSettings);
                //Excel sheet name. 0 represents first sheet
                WritableSheet sheet = workbook.createSheet("userList", 0);
    
    
           } catch(){
              e.printStackTrace();
           }
        }   
    }
    
  • Create Header

    To add header in the excel Sheet, we need to Pass the column position(INTEGER), row position(INTEGER) and header name(STRING) in the Label Object. And Pass this Label object to the addcell() method of the sheet class.

    public class MainActivity extends AppCompatActivity {
    
        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
            
            DBHelper dbHelper = new DBHelper(this);
            dbHelper.insertData();
    
            final Cursor cursor = dbHelper.getuser();
    
            File sd = Environment.getExternalStorageDirectory();
            String csvFile = "myData.xls";
    
            File directory = new File(sd.getAbsolutePath());
            //create directory if not exist
            if (!directory.isDirectory()) {
                directory.mkdirs();
            }
            try {
    
                //file path
                File file = new File(directory, csvFile);
                WorkbookSettings wbSettings = new WorkbookSettings();
                wbSettings.setLocale(new Locale("en", "EN"));
                WritableWorkbook workbook;
                workbook = Workbook.createWorkbook(file, wbSettings);
                //Excel sheet name. 0 represents first sheet
                WritableSheet sheet = workbook.createSheet("userList", 0);
                // column and row
                sheet.addCell(new Label(0, 0, "UserName")); 
                sheet.addCell(new Label(1, 0, "PhoneNumber"));
    
    
           } catch(){
              e.printStackTrace();
           }
        }   
    }
    
  • Dump Data into Excel

    Now, All data which is selected from SqlLite Database into Excel Sheet.

    public class MainActivity extends AppCompatActivity {
    
        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
            
            DBHelper dbHelper = new DBHelper(this);
            dbHelper.insertData();
    
            final Cursor cursor = dbHelper.getuser();
    
            File sd = Environment.getExternalStorageDirectory();
            String csvFile = "myData.xls";
    
            File directory = new File(sd.getAbsolutePath());
            //create directory if not exist
            if (!directory.isDirectory()) {
                directory.mkdirs();
            }
            try {
    
                //file path
                File file = new File(directory, csvFile);
                WorkbookSettings wbSettings = new WorkbookSettings();
                wbSettings.setLocale(new Locale("en", "EN"));
                WritableWorkbook workbook;
                workbook = Workbook.createWorkbook(file, wbSettings);
                //Excel sheet name. 0 represents first sheet
                WritableSheet sheet = workbook.createSheet("userList", 0);
                // column and row
                sheet.addCell(new Label(0, 0, "UserName")); 
                sheet.addCell(new Label(1, 0, "PhoneNumber"));
                
                if (cursor.moveToFirst()) {
                    do {
                        String name = cursor.getString(cursor.getColumnIndex("user_name"));
                        String phoneNumber = cursor.getString(cursor.getColumnIndex("phone_number"));
    
                        int i = cursor.getPosition() + 1;
                        sheet.addCell(new Label(0, i, name));
                        sheet.addCell(new Label(1, i, phoneNumber));
                    } while (cursor.moveToNext());
                }
    
           } catch(){
              e.printStackTrace();
           }
        }   
    }
    
  • Write Workbook and close

    Now call a write() of WritableWorkbook class and close it. Also don’t forgot to close cursor.

    public class MainActivity extends AppCompatActivity {
    
        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
            
            DBHelper dbHelper = new DBHelper(this);
            dbHelper.insertData();
    
            final Cursor cursor = dbHelper.getuser();
    
            File sd = Environment.getExternalStorageDirectory();
            String csvFile = "myData.xls";
    
            File directory = new File(sd.getAbsolutePath());
            //create directory if not exist
            if (!directory.isDirectory()) {
                directory.mkdirs();
            }
            try {
    
                //file path
                File file = new File(directory, csvFile);
                WorkbookSettings wbSettings = new WorkbookSettings();
                wbSettings.setLocale(new Locale("en", "EN"));
                WritableWorkbook workbook;
                workbook = Workbook.createWorkbook(file, wbSettings);
                //Excel sheet name. 0 represents first sheet
                WritableSheet sheet = workbook.createSheet("userList", 0);
                // column and row
                sheet.addCell(new Label(0, 0, "UserName")); 
                sheet.addCell(new Label(1, 0, "PhoneNumber"));
                
                if (cursor.moveToFirst()) {
                    do {
                        String name = cursor.getString(cursor.getColumnIndex("user_name"));
                        String phoneNumber = cursor.getString(cursor.getColumnIndex("phone_number"));
    
                        int i = cursor.getPosition() + 1;
                        sheet.addCell(new Label(0, i, name));
                        sheet.addCell(new Label(1, i, phoneNumber));
                    } while (cursor.moveToNext());
                }
    
                //closing cursor
                cursor.close();
                workbook.write();
                workbook.close();
                Toast.makeText(getApplication(), 
                    "Data Exported in a Excel Sheet", Toast.LENGTH_SHORT).show();
           } catch(){
              e.printStackTrace();
           }
        }   
    }
    







  • Bixby TS

    Gracias por el aporte, me sirvió de mucho.

  • Dessy Christin

    I have tried the above code but it did not work, I did not find its export to excel. Can you send to me the full source code ?

    Thank you

    • you can download source code on click download button. It is full source code

  • Yahia Allam

    I used it with FirebaseJobDispatcher to periodically export a database table to xls file.

    Thanks a lot.

    • Leo López Veintemilla

      Hi, can you tell me what is missing in the code, does not the button work?

      • Code is working. Please check your Code Whatever you did

        • Leo López Veintemilla

          Download the source code, I did not make any modifications. I am using the emulator Galaxy_Nexus_API25: 5554 and does not do anything the export button?

    • Welcome Yahia Allam

  • Leo López Veintemilla

    Buenos días, un favor, acabo de descargar el código, al hacer clic en el botón exportar datos, no hace nada, que puede estar pasando?