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

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

Don't Miss

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.

Define WorkbookSetting

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();
		}
	}
}
  • We need to create WorkbookSetting by initializing WorkbookSettings class. Define Locale and Pass the file object and WorkbookSetting to the WritableWorkbook object.

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

24 COMMENTS

  1. 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

  2. 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?

    • I faced same issue and solved the issue by adding the latest jexcel jar. I downloded latest zip file from https://sourceforge.net/projects/jexcelapi/, extracted the jxl.jar, created a lib folder in Android Studio and pasted the jar int he lib folder. Finally select the jar, right click and select ‘add as library’. Rebuild the project and the error should get resolved.

  3. Hello, I have the following error: “java.io.FileNotFoundException: /storage/emulated/0/file.xls: open failed: EACCES (Permission denied)”. I’m using the genymotion emulator.
    I added the permissions in my manifest file, should I configure something in the emulator?

    Regards!

  4. que pena man, pero el boton no sirve, realice un metodo Onclick y nada se oprime y no se genera nada, seria tan amable y decir cual seria el problema, o lo que compartio esta imcompleto.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

564FansLike

Recent Posts

Concept of Session in Laravel Tutorial

Sessions are used to store details about the user throughout the requests. Laravel supplies various drivers like file, cookie, apc, array, Memcached, Redis, and database to handle session data. By default, file driver is used as a result of it's light-weight....

Laravel Url Generation Tutorial

Our web application revolves around routes and URLs. After all, they're what direct our users to our pages. At the end of the day, serving pages is what any web application should do. Our users may...

Concept of Laravel Views Tutorial

In MVC framework, the letter "V" stands for Views. It separates the application logic and presentation logic. Views are saved in resources/views listing. Generally, the view contains the HTML which might be served by the application.

Related Articles

Concept of Session in Laravel Tutorial

Sessions are used to store details about the user throughout the requests. Laravel supplies various drivers like file, cookie, apc, array, Memcached, Redis, and database to handle session data. By default, file driver is used as a result of it's light-weight....

Laravel Url Generation Tutorial

Our web application revolves around routes and URLs. After all, they're what direct our users to our pages. At the end of the day, serving pages is what any web application should do. Our users may...

Concept of Laravel Views Tutorial

In MVC framework, the letter "V" stands for Views. It separates the application logic and presentation logic. Views are saved in resources/views listing. Generally, the view contains the HTML which might be served by the application.
WP2Social Auto Publish Powered By : XYZScripts.com