How to Convert my JSON data to Excel using Android

Question

I am new to Android Dev,This is my first Question in SO. i try to export my reports in Excel format. so i try to create the Excel file.Here is the code.

I am struck with get the json and put into the ROW and CELL

protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    TextView hwtxt = (TextView)findViewById(R.id.hw);


    HSSFWorkbook workbook = new HSSFWorkbook();

    HSSFSheet firstSheet = workbook.createSheet("Sheet1");

    String url = "http://192.168.1.13:8090/Sanjay_API/showBalanceAmtList.php";

    List<NameValuePair> params = new ArrayList<>();
    params.add(new BasicNameValuePair("hwtxt", hwtxt.getText().toString()));

    try {
        JSONObject parentObject = new JSONObject(getJSONUrl(url, params));
        JSONArray data = parentObject.getJSONArray("balance_list");


        MyArrList = new ArrayList<HashMap<String, String>>();
        HashMap<String, String> map;

        for(int i = 0; i < data.length(); i++){
            JSONObject c = data.getJSONObject(i);

            String myVal1 = c.getString("entry_date");
            System.out.println(myVal1);
        }

    }catch (JSONException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    FileOutputStream fos = null;
    try {
        String str_path = Environment.getExternalStorageDirectory().toString();
        File file;
        //String CurrentDateAndTime = java.text.DateFormat.getDateTimeInstance().format(new Date());
        file = new File(str_path, getString(R.string.app_name) + ".xls");

        fos = new FileOutputStream(file);
        workbook.write(fos);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (fos != null) {
            try {
                fos.flush();
                fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        Toast.makeText(MainActivity.this, "Excel Sheet Generated", Toast.LENGTH_SHORT).show();
    }
}

public String getJSONUrl(String url,List<NameValuePair> params) {
    StringBuilder str = new StringBuilder();
    HttpClient client = new DefaultHttpClient();
    HttpPost httpPost = new HttpPost(url);

    try {
        httpPost.setEntity(new UrlEncodedFormEntity(params));
        HttpResponse response = client.execute(httpPost);
        StatusLine statusLine = response.getStatusLine();
        int statusCode = statusLine.getStatusCode();
        if (statusCode == 200) { // Download OK
            HttpEntity entity = response.getEntity();
            InputStream content = entity.getContent();
            BufferedReader reader = new BufferedReader(new InputStreamReader(content));
            String line;
            while ((line = reader.readLine()) != null) {
                str.append(line);
            }
        } else {
            Log.e("Log", "Failed to download result..");
        }
    } catch (ClientProtocolException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return str.toString();
}

And, I have a data in json which is return from APT using PHP, My json is

 {
    "vnumber": [{
        "vid": "1",
        "vnumber": "STAT01",
        "vname": "STAT01",
        "vyear": "2011",
        "fcdate": "22/12/2017",
        "insdate": "22/12/2017",
        "perdate": "22/12/2017",
        "gtax": "0123456",
        "polcer": "TEST DEMO CERTIFY"
    }, {
        "vid": "2",
        "vnumber": "STAT02",
        "vname": "STAT02",
        "vyear": "2015",
        "fcdate": "12/12/2012",
        "insdate": "12/12/2012",
        "perdate": "12/12/2012",
        "gtax": "100",
        "polcer": "100"
    }]
}

I dont know how to export my data in excel format in SD Card.

http://stackoverflow.com/questions/28583343/i-want-to-extract-and-display-json-array-data-into-excel-sheet — Anis LOUNIS aka AnixPasBesoin, Jan 25 '17 at 07:09
thanks for the response i am going to try this one, and come back @AnixPasBesoin — iam user, Jan 25 '17 at 07:13

score 2 · Answer 1 · edited Nov 21 '17 at 06:18

2

Can you please try following.

File csvFile = new File(directory, Utils.CSV_FILE_NAME);

try {
            csvFile.createNewFile();
            CSVWriter csvWrite = new CSVWriter(new FileWriter(csvFile));

            String heading[] = {"vid", "vnumber", "vname", "vyear", "fcdate", "insdate", "perdate", "gtax", "polcer"};
            csvWrite.writeNext(heading);
            for (int i = 0; i < arrayList.size(); i++) {
                YourModel model = arrayList.get(i); // You can also use JsonObject from Json array.
                String arrStr[] = {String.valueOf(model.getVid()), String.valueOf(model.getVnumber()), model.getVname(), model.getVyear(), model.getFcdate(), model.getInsdate(), model.getPerdate(),model.getGtax(), model.getPolcer() };
                csvWrite.writeNext(arrStr);
            }
            csvWrite.close();
        } catch (Exception sqlEx) {
            Log.e(TAG, sqlEx.getMessage(), sqlEx);
        }

edited Nov 21 '17 at 06:18

Shubham Sejpal

3,556
2
14
31

answered Jan 25 '17 at 07:13

Mehul Kabaria

6,404
4
25
50

after getting success response of your json data. – Mehul Kabaria Jan 25 '17 at 07:24
i am struck with get the json into excel. actually how/where to write the row and cells create ? i think i try to inside of for loop but no success – iam user Jan 26 '17 at 09:58
what an issue? it's straight forward code. you have to just pass JsonArray in for loop. "heading" is your heading for column and in csvWrite.writeNext(arrStr); write row for your data from json. – Mehul Kabaria Jan 27 '17 at 04:52
thats the problem bro , can you help me plz. i dont know how to do that. here is my full code http://stackoverflow.com/questions/41872815/export-excel-using-api @Mehul – iam user Jan 27 '17 at 05:10
Have you got json Response successfully? – Mehul Kabaria Jan 27 '17 at 05:24
yes yes i got it, the problem is assign into rows and cells – iam user Jan 27 '17 at 05:32
I couldn't find CSVWriter class anywhere, please provide me exact CSVWriter class or any dependency link for it – Parthiban M Sep 16 '19 at 12:00
@ParthibanM : Might be this one dependency `compile 'com.opencsv:opencsv:4.6'` – Mehul Kabaria Sep 16 '19 at 12:07

score 1 · Answer 2 · edited May 23 '17 at 12:16

1

Android is basically Java. Here is one question on Stackoverflow itself that can help you with this convert json to excel in java

edited May 23 '17 at 12:16

Community

1
1

answered Jan 25 '17 at 07:11

hnrindani

9
5

score 0 · Answer 3 · answered Jun 19 '22 at 12:02

I have spent too much time for this implementation and finally below code is working for me

Add the following dependency in build.gradle

implementation 'org.apache.poi:poi:3.17'

Use the following code for Convert Java Object into Excel Cells and Row and finally create an excel file and download it to your phone storage.

HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet hssfSheet = hssfWorkbook.createSheet("Custom Sheet");
        //---------------
        HSSFRow hssfRowMainTitle = hssfSheet.createRow(0);
        hssfRowMainTitle.createCell(0).setCellValue("Document");
        //-------------
        HSSFRow hssfRowTitle = hssfSheet.createRow(1);
        hssfRowTitle.createCell(0).setCellValue("srno");
        hssfRowTitle.createCell(1).setCellValue("date");
        hssfRowTitle.createCell(2).setCellValue("type");
        hssfRowTitle.createCell(3).setCellValue("transactionid");
        //--------------
        int row = 2;
        int srNo = 1;
        for (Ledger a : currentFilterDataList) {
            HSSFRow hssfRow = hssfSheet.createRow(row);
            hssfRow.createCell(0).setCellValue(srNo);
            hssfRow.createCell(1).setCellValue(a.getDate());
            hssfRow.createCell(2).setCellValue(a.getType());
            hssfRow.createCell(3).setCellValue(a.getVoucherNo());
            row++;
            srNo++;
        }
        //---------
        File path = Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS + File.separator);
        File file = new File(path.toString());
        file.mkdirs();
        String fileName = path + "/" + "transaction_" + System.currentTimeMillis() + ".xls";
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(fileName);
            hssfWorkbook.write(fileOutputStream);
            fileOutputStream.flush();
            fileOutputStream.close();
            showSnackBar(getContext(), mBinding.getRoot(), "File downloaded successfully \n " + path);
        } catch (IOException e) {
            e.printStackTrace();
        }

How to Convert my JSON data to Excel using Android

3 Answers3