I have one activity called task list.this task list contains json array I have one button now I want to export the task list into Excel sheet on button click
For Creating Excel sheet I have used poi-3.7.jar
my code for Excel sheet:
public void onClick(View v) {
// TODO Auto-generated method stub
switch (v.getId())
{
case R.id.param11:
saveExcelFile(this,"myexcel.xls");
break;
}
}
private static boolean saveExcelFile(Context context, String fileName) {
// check if available and not read only
if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) {
Log.e(TAG, "Storage not available or read only");
return false;
}
boolean success = false;
//New Workbook
Workbook wb = new HSSFWorkbook();
Cell c = null;
//Cell style for header row
CellStyle cs = wb.createCellStyle();
cs.setFillForegroundColor(HSSFColor.LIME.index);
cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//New Sheet
Sheet sheet1 = null;
sheet1 = wb.createSheet("wrok");
// Generate column headings
org.apache.poi.ss.usermodel.Row row = sheet1.createRow(0);
c = row.createCell(0);
c.setCellValue("task_id");
c.setCellStyle(cs);
c = row.createCell(1);
c = row.createCell(2);
c.setCellValue("d_alias");
c.setCellStyle(cs);
c = row.createCell(3);
c.setCellValue("worktype_name");
c.setCellStyle(cs);
c = row.createCell(4);
c.setCellValue("status");
c.setCellStyle(cs);
c = row.createCell(5);
c.setCellValue("departmentstatus");
c.setCellStyle(cs);
c = row.createCell(6);
c.setCellValue("priority");
c.setCellStyle(cs);
c = row.createCell(7);
c.setCellValue("staff_name");
c.setCellStyle(cs);
sheet1.setColumnWidth(0, (15 * 500));
sheet1.setColumnWidth(1, (15 * 500));
sheet1.setColumnWidth(2, (15 * 500));
// Create a path where we will place our List of objects on external storage
File file = new File(context.getExternalFilesDir(null), fileName);
FileOutputStream os = null;
try {
os = new FileOutputStream(file);
wb.write(os);
Log.w("FileUtils", "Writing file" + file);
success = true;
} catch (IOException e) {
Log.w("FileUtils", "Error writing " + file, e);
} catch (Exception e) {
Log.w("FileUtils", "Failed to save file", e);
} finally {
try {
if (null != os)
os.close();
} catch (Exception ex) {
}
}
return success;
}
public static boolean isExternalStorageReadOnly() {
String extStorageState = Environment.getExternalStorageState();
if (Environment.MEDIA_MOUNTED_READ_ONLY.equals(extStorageState)) {
return true;
}
return false;
}
public static boolean isExternalStorageAvailable() {
String extStorageState = Environment.getExternalStorageState();
if (Environment.MEDIA_MOUNTED.equals(extStorageState)) {
return true;
}
return false;
}
///i have converted json string to csv
for converting json to csv i have used org.json.jar
if (array.length() > 0) {
for (int i = 0; i < array.length(); i++) {
JSONObject obj = (JSONObject) array.get(i);
item.add(obj.getString("task_id").toString().trim());
WorkItem item = new WorkItem(obj.getString("id"),
obj.getString("task_id"),
obj.getString("firm_name"),
obj.getString("d_alias"),
obj.getString("worktype_name"),
obj.getString("status"),
obj.getString("departmentstatus"),
obj.getString("priority"),
obj.getString("staff_name"),
obj.getString("remark"));
String csv = CDL.toString(array);
System.out.println("csv " +csv); //csv
rowItems.add(item);
System.out.println("itemss" +item);
}
My excelsheet is created successfully but it contains only headers
My json string:
object[{"id":"10","task_id":"drm\/Technical \/75","firm_name":"dreamhome","d_alias":"Technical ","worktype_name":"Technical","status":"completed","departmentstatus":"completed","priority":"high","staff_name":"anna","remark":"good","createddate":"2015-02-11 03:42:17","updateddate":"2015-02-11 03:42:17","isdeleted":"0"},{"id":"10","task_id":"sv\/Technical \/89","firm_name":"svye","d_alias":"Technical ","worktype_name":"aa","status":"Submitted","departmentstatus":"incomplete","priority":"5.0","staff_name":"sam","remark":"jdjd","createddate":"2015-02-12 23:59:05","updateddate":"2015-02-12 23:59:05","isdeleted":"0"}]
//csv string
02-18 14:41:43.531: I/System.out(854): 10,Technical,good,a,completed,high,0,Technical ,drm/Technical /75,2015-02-11 03:42:17,2015-02-11 03:42:17,completed,devrai
02-18 14:41:43.531: I/System.out(854): 10,aa,jdjd,Vishal,Submitted,5.0,0,Technical ,sv/Technical /89,2015-02-12 23:59:05,2015-02-12 23:59:05,incomplete,devrai
I dont have any idea how to convert csv and save it into excel sheet. Please help me how to achieve this.
Just iterate over rowItems and use createRow / createCell no need for csv. After
sheet1.setColumnWidth(2, (15 * 500));
Add something like:
int row = 1;
for (final WorkItem item: rowItems) {
final Row row = sheet1.createRow(row);
int cell = 0;
Cell cell = row.createCell(cell++);
cell.setCellValue(item.getTaskId());
cell = row.createCell(cell++);
cell.setCellValue(item.getDAlias());
// ...
row += 1;
}
Some reading: Busy Developers' Guide to HSSF and XSSF Features