How to create Excel file in Android?

by / Thursday, 31 October 2013 / Published in Android

In this example we will show how to create an excel file In Android, the file will be created in sdcard. First we have included apache poi jar file to the project. we also declare the following permission in AndroidManifest.xml:

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

We have created two buttons the create button will create the excel sheet and the show button will show it in a listview.

Code for layout:

Code for activity_main.xml:

<LinearLayout 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"
tools:context=".MainActivity"
android:orientation="vertical"
android:gravity="center">

<Button
android:id="@+id/btn_insert"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Create"
android:layout_margin="10dp"/>

<Button
android:id="@+id/btn_show"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Show"
android:layout_margin="10dp"/>

</LinearLayout>

Code for MainActivity.java:

Button insertButton,showButton;

insertButton=(Button)findViewById(R.id.btn_insert);
showButton=(Button)findViewById(R.id.btn_show);

insertButton.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View v) {

HSSFWorkbook hwb=new HSSFWorkbook();
HSSFSheet sheet =  hwb.createSheet("new sheet");

HSSFRow rowhead=   sheet.createRow((short)0);
rowhead.createCell((short) 0).setCellValue("Emp Code");
rowhead.createCell((short) 1).setCellValue("Emp Name");
rowhead.createCell((short) 2).setCellValue("Dob");
rowhead.createCell((short) 3).setCellValue("C Name");

HSSFRow row=   sheet.createRow((short)1);
row.createCell((short) 0).setCellValue("Emp0029");
row.createCell((short) 1).setCellValue("ABC");
row.createCell((short) 2).setCellValue("25/1/88");
row.createCell((short) 3).setCellValue("Mettletech");

HSSFRow row1=   sheet.createRow((short)2);
row1.createCell((short) 0).setCellValue("Emp0027");
row1.createCell((short) 1).setCellValue("PQR");
row1.createCell((short) 2).setCellValue("13/4/89");
row1.createCell((short) 3).setCellValue("Mettletech");

HSSFRow row2=   sheet.createRow((short)3);
row2.createCell((short) 0).setCellValue("Emp0025");
row2.createCell((short) 1).setCellValue("XYZ");
row2.createCell((short) 2).setCellValue("11/5/90");
row2.createCell((short) 3).setCellValue("Mettletech");
try {
File file=new File(Environment.getExternalStorageDirectory(),"new.xls");
if(file.exists()){
FileOutputStream fileOut =  new FileOutputStream(file);
hwb.write(fileOut);
fileOut.close();
}else{
file.createNewFile();
FileOutputStream fileOut =  new FileOutputStream(file);
hwb.write(fileOut);
fileOut.close();
}

} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
});

showButton.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View v) {

Intent intent=new Intent(MainActivity.this,ShowDetailsActivity.class);
startActivity(intent);
finish();
}
});
}

}

Code for ShowDetailsActivity.java:

public class ShowDetailsActivity extends Activity {

ArrayList<ArrayList<String>> all=new ArrayList<ArrayList<String>>();
MyThumbnaildapter adapter=null;
ListView list;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_show_details);
list=(ListView)findViewById(R.id.listview);

try {
FileInputStream file = new FileInputStream(new File(Environment.getExternalStorageDirectory(),"new.xls"));
HSSFWorkbook myWorkBook = new HSSFWorkbook(file);
HSSFSheet mySheet = myWorkBook.getSheetAt(0);
Iterator<HSSFRow> rowIterator = mySheet.rowIterator();
while(rowIterator.hasNext()) {
HSSFRow row = rowIterator.next();
ArrayList <String> allist=new ArrayList<String>();
Iterator<HSSFCell> cellIterator = row.cellIterator();
while(cellIterator.hasNext()) {
HSSFCell cell = cellIterator.next();
allist.add(cell.getStringCellValue());
}

all.add(allist);
}
} catch (FileNotFoundException e) {

e.printStackTrace();
} catch (IOException e) {

e.printStackTrace();
}

adapter=new MyThumbnaildapter(ShowDetailsActivity.this, R.layout.excellayout, all);
list.setAdapter(adapter);
adapter.notifyDataSetChanged();

}

public class MyThumbnaildapter extends ArrayAdapter<ArrayList<String>> {
ArrayList<ArrayList<String>> emplist;
public MyThumbnaildapter(Context context, int textViewResourceId,ArrayList<ArrayList<String>> emplist) {
super(context, textViewResourceId,emplist);
this.emplist=emplist;
}

@Override
public View getView(int position, View convertView, ViewGroup parent) {

ArrayList<String> emList=emplist.get(position);

LayoutInflater inflatertable = (LayoutInflater)ShowDetailsActivity.this.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
View view = inflatertable.inflate(R.layout.excellayout, null);

TextView txtEmpCode=(TextView) view.findViewById(R.id.text_code);
TextView txtEmpName=(TextView) view.findViewById(R.id.text_name);
TextView txtEmpDob=(TextView) view.findViewById(R.id.text_dob);
TextView txtEmpCname=(TextView) view.findViewById(R.id.text_cname);

txtEmpCode.setText(emList.get(0));
txtEmpName.setText(emList.get(1));
txtEmpDob.setText(emList.get(2));
txtEmpCname.setText(emList.get(3));

return view;
}

}

}

Code for corresponding layouts:

Code for activity_show_details.xml:

<LinearLayout 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"
tools:context=".ShowDetailsActivity"
android:gravity="center"
android:orientation="vertical">

<ListView
android:id="@+id/listview"
android:layout_width="match_parent"
android:layout_height="wrap_content"/>

</LinearLayout>

Code for excellayout.xml:

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical" >

<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center">

<TextView
android:id="@+id/text_code"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="1"/>
<TextView
android:id="@+id/text_name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="1"/>
<TextView
android:id="@+id/text_dob"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="1"/>
<TextView
android:id="@+id/text_cname"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="1"/>

</LinearLayout>

</LinearLayout>

TOP