In this tutorial we will explain how to create excel file in android using Java for excel library. The example provided below is the extended version of our previous android SQLite database tutorial. We recommend you to have a glance at it before going through this example.
In our previous example, we have explained you how to create an TODO application in android using SQLite database. Now in this post we will export the data saved in database to an excel file and will store it on SDCard.
Step-1
Download Java for Excel library from below path.
http://jexcelapi.sourceforge.net/
http://sourceforge.net/projects/jexcelapi/files/
Step-2
Export and save data from cursor into excel spreadsheet and saves the spreadsheet into external storage directory.
/**
* Exports the cursor value to an excel sheet.
* Recommended to call this method in a separate thread,
* especially if you have more number of threads.
*
* @param cursor
*/
private void exportToExcel(Cursor cursor) {
final String fileName = "TodoList.xls";
//Saving file in external storage
File sdCard = Environment.getExternalStorageDirectory();
File directory = new File(sdCard.getAbsolutePath() + "/javatechig.todo");
//create directory if not exist
if(!directory.isDirectory()){
directory.mkdirs();
}
//file path
File file = new File(directory, fileName);
WorkbookSettings wbSettings = new WorkbookSettings();
wbSettings.setLocale(new Locale("en", "EN"));
WritableWorkbook workbook;
try {
workbook = Workbook.createWorkbook(file, wbSettings);
//Excel sheet name. 0 represents first sheet
WritableSheet sheet = workbook.createSheet("MyShoppingList", 0);
try {
sheet.addCell(new Label(0, 0, "Subject")); // column and row
sheet.addCell(new Label(1, 0, "Description"));
if (cursor.moveToFirst()) {
do {
String title = cursor.getString(cursor.getColumnIndex(DatabaseHelper.TODO_SUBJECT));
String desc = cursor.getString(cursor.getColumnIndex(DatabaseHelper.TODO_DESC));
int i = cursor.getPosition() + 1;
sheet.addCell(new Label(0, i, title));
sheet.addCell(new Label(1, i, desc));
} while (cursor.moveToNext());
}
//closing cursor
cursor.close();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
workbook.write();
try {
workbook.close();
} catch (WriteException e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
Tanx …Awesome.
how i can make my xls file right to left(Persian)?
Not quite sure. if that is supported by the library. You probably have to check the api docs.
private void exportToExcel(Cursor cursor) {
final String fileName = “TodoList.xls”;
//Saving file in external storage
File sdCard = Environment.getExternalStorageDirectory();
File directory = new File(sdCard.getAbsolutePath() + “/javatechig.todo”);
//create directory if not exist
if(!directory.isDirectory()){
directory.mkdirs();
}
//file path
File file = new File(directory, fileName);
WorkbookSettings wbSettings = new WorkbookSettings();
wbSettings.setLocale(new Locale(“en”, “EN”));
WritableWorkbook workbook;
try {
workbook = Workbook.createWorkbook(file, wbSettings);
//Excel sheet name. 0 represents first sheet
WritableSheet sheet = workbook.createSheet(“MyShoppingList”, 0);
try {
// column and row
sheet.addCell(new Label(0, 0, “Code”));
sheet.addCell(new Label(1, 0, “Format”));
sheet.addCell(new Label(2, 0, “Type”));
sheet.addCell(new Label(3, 0, “Name”));
sheet.addCell(new Label(4, 0, “Price”));
sheet.addCell(new Label(5, 0, “Quantity”));
if (cursor.moveToFirst()) {
do {
String code = cursor.getString(cursor.getColumnIndex(p.getCode()));
String format = cursor.getString(cursor.getColumnIndex(p.getFormat()));
String type = cursor.getString(cursor.getColumnIndex(p.getType()));
String name = cursor.getString(cursor.getColumnIndex(p.getName()));
String price = cursor.getString(cursor.getColumnIndex(p.getQuantity()));
String quantity = cursor.getString(cursor.getColumnIndex(p.getPrice()));
int i = cursor.getPosition() + 1;
sheet.addCell(new Label(0, i, code));
sheet.addCell(new Label(1, i, format));
sheet.addCell(new Label(2, i, type));
sheet.addCell(new Label(1, i, name));
sheet.addCell(new Label(1, i, price));
sheet.addCell(new Label(1, i, quantity));
} while (cursor.moveToNext());
}
//closing cursor
cursor.close();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
workbook.write();
try {
workbook.close();
} catch (WriteException e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
Have you downloaded and used the Java for Excel library?
If already added please revisit your build path.
please, can you give me a project of an application that uses this method to export the database in an excel file?
Here is the project code.
https://github.com/javatechig/Advance-Android-Tutorials/tree/master/TODO%20Android%20App
I could not download it, you could send it to me by email (billel.31@outlook.com) ???
done. Check your mail.
thank you so much.
The current example accepts the Cursor object. Cursor is a result set data returned from SQLite database.
You can pass arraylist of any other type of collections to generate Excel files.
Please download from github.
Download it here
https://github.com/javatechig/Advance-Android-Tutorials/archive/master.zip
Note, you will get other projects example included.
Hi, I seem to be getting an error when I try to run this. It does not recognize “DatabaseHelper”. Any idea what I’m doing wrong?
Thanks.
checkout the download link
https://github.com/javatechig/Advance-Android-Tutorials/archive/master.zip
Hi ,
I am able to see the excel sheet on click on exportExcel menu item .I downloaded the Todo APP and run the app than added record into it but couldnot succeed to see the excel sheet.Please help me .Thanks
Hi I am not see the excelsheet from the TodoAPp.Thanks
This article is good. However if i am looking for a manner to export to my USB, what would it be? because it need to be rooting to see the excel file.
Hello, the statements Environment.getExternalStorageDirectory(); only create in internal storage and that’s not good, the good will be to create in real external memory. so How can we do?.