Android generate excel sheet(.xls file) for retrieving contacts from phonebook

Hello Friends,

This post will help to you create or generate excel sheet (.xls file) for retrieving contacts from phonebook and stored locally on sd card.

Means this will help to you back up your contacts with name , email and phone number.

XML File:

    <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
        xmlns:tools="http://schemas.android.com/tools"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:paddingBottom="@dimen/activity_vertical_margin"
        android:paddingLeft="@dimen/activity_horizontal_margin"
        android:paddingRight="@dimen/activity_horizontal_margin"
        android:paddingTop="@dimen/activity_vertical_margin"
        tools:context=".MainActivity" >

       <Button
           android:id="@+id/btnBackUp"
           android:layout_width="wrap_content"
           android:layout_height="wrap_content"
           android:layout_alignParentTop="true"
           android:layout_centerHorizontal="true"
           android:layout_marginTop="79dp"
           android:text="Back Up Contacts" />

    </RelativeLayout>

Java Class File:

MainActivity.class

package com.optiinfo.contactsbackup;

import java.io.File;

import java.io.IOException;

import java.util.ArrayList;

import java.util.Locale;

import jxl.Workbook;

import jxl.WorkbookSettings;

import jxl.format.Alignment;

import jxl.format.Border;

import jxl.format.BorderLineStyle;

import jxl.format.Colour;

import jxl.write.Label;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

import android.app.Activity;

import android.app.ProgressDialog;

import android.content.ContentResolver;

import android.database.Cursor;

import android.os.Bundle;

import android.os.Environment;

import android.os.Handler;

import android.os.Message;

import android.provider.ContactsContract;

import android.view.Menu;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.Toast;

public class MainActivity extends Activity {

Button btnBackup;

ArrayList<String> nameList, phoneList, emailList;

static ArrayList<ArrayList<String>> smsbufferList = new ArrayList<ArrayList<String>>();

String smsFile = "SMS" + ".csv";

int widthInChars;

private ProgressDialog pdialog;

File file;

Handler handler = new Handler() {

@Override

public void handleMessage(Message msg) {

// TODO Auto-generated method stub

super.handleMessage(msg);

if (pdialog != null && pdialog.isShowing()) {

pdialog.dismiss();

}

if (file != null) {

Toast.makeText(

MainActivity.this,

"Your File Located at :" + "\n"

+ file.getAbsolutePath(), Toast.LENGTH_LONG)

.show();

}

}

};

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

btnBackup = (Button) findViewById(R.id.btnBackUp);

btnBackup.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

pdialog = ProgressDialog.show(MainActivity.this,

"Backup Contacts", "Retrieving Contacts....");

new Thread(new Runnable() {

@Override

public void run() {

// TODO Auto-generated method stub

try {

Thread.sleep(1000);

ShowContact();

handler.sendEmptyMessage(0);

} catch (Exception e) {

// TODO: handle exception

}

}

}).start();

}

});

}

@Override

public boolean onCreateOptionsMenu(Menu menu) {

// Inflate the menu; this adds items to the action bar if it is present.

getMenuInflater().inflate(R.menu.main, menu);

return true;

}

public ArrayList<String> ShowContact() throws RowsExceededException,

WriteException {

nameList = new ArrayList<String>();

phoneList = new ArrayList<String>();

emailList = new ArrayList<String>();

ContentResolver cr = getContentResolver();

Cursor cur = cr.query(ContactsContract.Contacts.CONTENT_URI, null,

null, null, null);

if (cur.getCount() > 0) {

while (cur.moveToNext()) {

String id = cur.getString(cur

.getColumnIndex(ContactsContract.Contacts._ID));

String name = cur

.getString(cur

.getColumnIndex(ContactsContract.Contacts.DISPLAY_NAME));

if (Integer

.parseInt(cur.getString(cur

.getColumnIndex(ContactsContract.Contacts.HAS_PHONE_NUMBER))) > 0) {

// Query phone here. Covered next

Cursor pCur = cr.query(

ContactsContract.CommonDataKinds.Phone.CONTENT_URI,

null,

ContactsContract.CommonDataKinds.Phone.CONTACT_ID

+ " = ?", new String[] { id }, null);

while (pCur.moveToNext()) {

// Do something with phones

String phoneNo = pCur

.getString(pCur

.getColumnIndex(ContactsContract.CommonDataKinds.Phone.NUMBER));

nameList.add(name); // Here you can list of contact.

phoneList.add(phoneNo); // Here you will get list of

// phone number.

Cursor emailCur = cr

.query(ContactsContract.CommonDataKinds.Email.CONTENT_URI,

null,

ContactsContract.CommonDataKinds.Email.CONTACT_ID

+ " = ?", new String[] { id },

null);

while (emailCur.moveToNext()) {

String email = emailCur

.getString(emailCur

.getColumnIndex(ContactsContract.CommonDataKinds.Email.DATA));

emailList.add(email); // Here you will get list of

// email

}

emailCur.close();

}

pCur.close();

}

}

}

generateExcelFileForSMS(nameList, phoneList, emailList);

return nameList; // here you can return whatever you want.

}

private void generateExcelFileForSMS(ArrayList<String> stringList1,

ArrayList<String> stringList2, ArrayList<String> stringList3)

throws RowsExceededException, WriteException {

String Fnamexls = "testfile" + ".xls";

File sdCard = Environment.getExternalStorageDirectory();

File directory = new File(sdCard.getAbsolutePath()

+ "/MyContacts_Backup");

directory.mkdirs();

file = new File(directory, Fnamexls);

WorkbookSettings wbSettings = new WorkbookSettings();

wbSettings.setLocale(new Locale("en", "EN"));

WritableWorkbook workbook;

try {

workbook = Workbook.createWorkbook(file, wbSettings);

// workbook.createSheet("Report", 0);

WritableSheet sheet = workbook.createSheet("First Sheet", 0);

WritableFont cellFont = new WritableFont(WritableFont.TIMES, 11);

cellFont.setColour(Colour.BLUE);

WritableCellFormat cellFormat = new WritableCellFormat(cellFont);

cellFormat.setBackground(Colour.AQUA);

cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);

cellFormat.setAlignment(Alignment.CENTRE);

Label labelName = null, labelPhone = null, labelEmail = null;

Label label0 = new Label(0, 0, "Name", cellFormat);

Label label3 = new Label(1, 0, "Phone", cellFormat);

Label label5 = new Label(2, 0, "Email", cellFormat);

WritableFont cellFontForChild = new WritableFont(

WritableFont.TIMES, 9);

cellFontForChild.setColour(Colour.BLACK);

WritableCellFormat cellFormatInner = new WritableCellFormat(

cellFontForChild);

cellFormatInner.setAlignment(Alignment.CENTRE);

for (int i = 0; i < stringList1.size(); i++) {

String stringName = stringList1.get(i);

labelName = new Label(0, i, stringName, cellFormatInner);

widthInChars = 17;

sheet.setColumnView(0, widthInChars);

sheet.addCell(labelName);

}

for (int i = 0; i < stringList2.size(); i++) {

String stringPhone = stringList2.get(i);

labelPhone = new Label(1, i, stringPhone, cellFormatInner);

widthInChars = 22;

sheet.setColumnView(1, widthInChars);

sheet.addCell(labelPhone);

}

for (int i = 0; i < stringList3.size(); i++) {

String stringEmail = stringList3.get(i);

labelEmail = new Label(2, i, stringEmail, cellFormatInner);

widthInChars = 30;

sheet.setColumnView(2, widthInChars);

sheet.addCell(labelEmail);

}

try {

sheet.addCell(label5);

sheet.addCell(label0);

sheet.addCell(label3);

} catch (RowsExceededException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (WriteException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

workbook.write();

try {

workbook.close();

} catch (WriteException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

// createExcel(excelSheet);

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

In this project for generate excel sheet (.xls file) you need to add third party library jar file which will be jxl.jar file. You need to add that jar file to your libs folder and add as a external jar file too.

Finally you need to add permission in your AndroidManifest.xml file.

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

So hope it will be helpful to all.

Enjoy Happy Coding!!!!