Writing Excel sheet using Apache POI from a json array

Question

I'm trying to pull 5 pieces of json data and write them to an excel sheet. I'm converting the data to a jsonarray and then writing to excel using POI. For some reason, the program is currently only writing the 5th data piece to the first row, and not doing anything with the others. It should be looping through all 5 and putting each in the row it corresponds with. Any ideas?

private void sendPost() {
    int rowNumber = 1;

        while (rowNumber < 5 ) {
try {
String id = censusIdValue(rowNumber);
    String url = "https://www.broadbandmap.gov/broadbandmap/analyze/jun2014/summary/population/censusplace/ids/" + URLEncoder.encode(id, "UTF-8") + "?format=json";
    HttpClient client = HttpClientBuilder.create().build();
    HttpGet httpGet = new HttpGet(url);
    HttpResponse response = client.execute(httpGet);
    InputStream is = response.getEntity().getContent();
    String result = getStringFromInputStream(is);
    JSONObject jsonObj = new JSONObject(result.toString()); 
    JSONArray data = jsonObj.getJSONArray("Results"); 
      int rowCount = 0;
      XSSFWorkbook workbook = new XSSFWorkbook();
      XSSFSheet sheet = workbook.createSheet("wow");

            JSONObject rec = data.getJSONObject(0);
            String geographyId = rec.getString("geographyId");
            String strStatusType = rec.getString("geographyName");
            int numberOfWirelineProvidersEquals0 = rec.getInt("numberOfWirelineProvidersEquals0");

            XSSFRow row = sheet.createRow(rowCount++);
            XSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(geographyId);
            XSSFCell cell2 = row.createCell(2);
            cell2.setCellValue(strStatusType);
            XSSFCell cell3 = row.createCell(3);
            cell3.setCellValue(numberOfWirelineProvidersEquals0);

        try (FileOutputStream outputStream = new FileOutputStream("data.xlsx")) {
            workbook.write(outputStream);
        }


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

    rowNumber++;

        }
}

You should loop on your jsonarray to write all the lines in your sheet. — Xavier Bouclet
– Xavier Bouclet, Commented Oct 27, 2017 at 23:08

Mike · Accepted Answer · 2017-10-27 23:08:47Z

2

It looks like you're overwriting the file with each row. Try putting the write outside the loop.

Also, get rid of the rowCount variable. You want to use your rowNumber variable there.

Finally, I think you're only looping 4 times. (You indicate you want 5 in your question.)

answered Oct 27, 2017 at 23:08

Mike

5013 silver badges5 bronze badges

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

1 Comment

arsenal11 Over a year ago

rowNumber is for another part of the program, but thanks for bringing my attention to it! The problem was I needed to move "int rowCount = 0" outside the loop because it just kept resetting so no new rows were being created.

Collectives™ on Stack Overflow

Writing Excel sheet using Apache POI from a json array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related