I want to extract and display Json array data into Excel sheet

Question

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.

CSV is a simple text file with semicolon between the columns. Though there is some kind of standard, Excel (and other spread sheets) interpret it different (escaping quotes and semicolon, use of tabs instead of semicolon). You might look out for a CSV lib that creates the file for you. — qwerty_so
– qwerty_so, Commented Feb 18, 2015 at 12:13
do u have any idea which CSV libs are there..because right now i have used org.json.jar // String csv = CDL.toString(array); — Pooja
– Pooja, Commented Feb 18, 2015 at 12:16
I have no recommendation. And CSV is no witch craft. But remember my comment: you will need a few cycles to get a common CSV working that Excel will accept. Create a Excel sheet and put some quotes ("), new-lines and semicolons in. Then export as CSV and look at the result. — qwerty_so
– qwerty_so, Commented Feb 18, 2015 at 12:41

user180100 · Accepted Answer · 2015-02-18 12:41:49Z

2

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

answered Feb 18, 2015 at 12:41

user180100

Sign up to request clarification or add additional context in comments.

1 Comment

user180100 Over a year ago

What do you mean by header for each cell?

Collectives™ on Stack Overflow

I want to extract and display Json array data into Excel sheet

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related