Tutorials Android How To Create Excel File In Android

How To Create Excel File In Android

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();
	}
}
Total
0
Shares
guest
18 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Ehsan Bahari
Ehsan Bahari
7 years ago

Tanx …Awesome.

how i can make my xls file right to left(Persian)?

Nilanchala Panigrahy
Nilanchala Panigrahy
7 years ago
Reply to  Ehsan Bahari

Not quite sure. if that is supported by the library. You probably have to check the api docs.

Billel
Billel
7 years ago

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

Nilanchala Panigrahy
Nilanchala Panigrahy
7 years ago
Reply to  Billel

Have you downloaded and used the Java for Excel library?

If already added please revisit your build path.

Billel
Billel
7 years ago

please, can you give me a project of an application that uses this method to export the database in an excel file?

Nilanchala Panigrahy
Nilanchala Panigrahy
7 years ago
Reply to  Billel
Billel
Billel
7 years ago

I could not download it, you could send it to me by email (billel.31@outlook.com) ???

Nilanchala Panigrahy
Nilanchala Panigrahy
7 years ago
Reply to  Billel

done. Check your mail.

Billel
Billel
7 years ago

thank you so much.

Nilanchala Panigrahy
Nilanchala Panigrahy
7 years ago

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.

Nilanchala Panigrahy
Nilanchala Panigrahy
7 years ago

Please download from github.

JavaTechig
JavaTechig
7 years ago

Download it here

https://github.com/javatechig/Advance-Android-Tutorials/archive/master.zip

Note, you will get other projects example included.

Anu
Anu
7 years ago

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.

Nilanchala Panigrahy
Nilanchala Panigrahy
6 years ago
Jaishree Rathod
Jaishree Rathod
6 years ago

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

Jaishree Rathod
Jaishree Rathod
6 years ago

Hi I am not see the excelsheet from the TodoAPp.Thanks

Rio
Rio
4 years ago

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.

Leo
Leo
4 years ago

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

Previous Post

Android Third Party Libraries and SDK’s

Next Post
Android Tutorials

Repeat Alarm Example In Android Using AlarmManager

Related Posts
By clicking “Allow All”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookie Notice
Allow All
18
0
Would love your thoughts, please comment.x
()
x