64637

convert json to excel in java

Question:

I have to convert very complex json file to excel. my json file is something like that:

{ "expand": "schema,names", "startAt": 2, "maxResults": 120, "total": 36, "issues": [ { "expand": "editmeta,renderedFields,transitions,changelog,operations", "id": "13132", "self": "http:// collaboration/rest/api/2/issue/13132", "key": "SAV-119", "fields": { "worklog": { "startAt": 0, "maxResults": 0, "total": 0, "worklogs": [] } } }, { "expand": "editmeta,renderedFields,transitions,changelog,operations", "id": "13127", "self": "http:// collaboration/rest/api/2/issue/13127", "key": "SAV-114", "fields": { "worklog": { "startAt": 0, "maxResults": 123456789, "total": 4, "worklogs": [ { "self": "http:// collaboration/rest/api/2/issue/13127/worklog/12614", "author": { "self": "http:// collaboration/rest/api/2/user?username=whatEver", "name": "whatEver", "emailAddress": "s_whatEver@company.co.ir", "avatarUrls": { "16x16": "http:// collaboration/secure/useravatar?size=xsmall&ownerId=whatEver&avatarId=10501", "24x24": "http:// collaboration/secure/useravatar?size=small&ownerId=whatEver&avatarId=10501", "32x32": "http:// collaboration/secure/useravatar?size=medium&ownerId=whatEver&avatarId=10501", "48x48": "http:// collaboration/secure/useravatar?ownerId=whatEver&avatarId=10501" }, "displayName": "soeyeh whatEver", "active": true }, "updateAuthor": { "self": "http:// collaboration/rest/api/2/user?username=whatEver", "name": "whatEver", "emailAddress": "s_whatEver@company.co.ir", "avatarUrls": { "16x16": "http:// collaboration/secure/useravatar?size=xsmall&ownerId=whatEver&avatarId=10501", "24x24": "http:// collaboration/secure/useravatar?size=small&ownerId=whatEver&avatarId=10501", "32x32": "http:// collaboration/secure/useravatar?size=medium&ownerId=whatEver&avatarId=10501", "48x48": "http:// collaboration/secure/useravatar?ownerId=whatEver&avatarId=10501" }, "displayName": "soeyeh whatEver", "active": true }, "created": "2014-11-26T18:06:01.000+0330", "updated": "2014-11-26T18:06:01.000+0330", "started": "2014-11-26T18:05:00.000+0330", "timeSpent": "1d 1h", "timeSpentSeconds": 32400, "id": "12614" } ] } } } ]

}

as you see this json file have jsonObject/jsonArray and key:value in it. Inside of jsonArray I can have jsonObject as array element and vise versa. in my excel output I have new row for each jsonObject element.

here is what I expect: <img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/qd2nl.jpg" data-original="https://i.stack.imgur.com/qd2nl.jpg" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

here is my code:

public class JsonParseTest { private static List<String> header = new ArrayList<String>(); private static List<Row> rows = new ArrayList<Row>(); private static Row row = new Row(); private static int rowsSize; public static List<String> getHeader() { return header; } public static List<Row> getRows() { return rows; } public static void main(String[] args) { try { // 1.read the json file JSONObject jsonObject = readJson(); //2.iterate json file for (Iterator iterator = jsonObject.keySet().iterator(); iterator.hasNext(); ) { String header = (String) iterator.next(); short type = getType(jsonObject, header); if (type == (short) 0) { iterateJsonObject(jsonObject, header); } else if (type == (short) 1) { iteratorJsonArray((JSONArray) jsonObject.get(header), header); row = getRows().get(rowsSize); } else if (type == (short) 2) { createHeader(header); addFieldToRow(String.valueOf(jsonObject.get(header)), header); } } createExcelFile(); } catch (FileNotFoundException ex) { ex.printStackTrace(); } catch (IOException ex) { ex.printStackTrace(); } catch (ParseException ex) { ex.printStackTrace(); } catch (NullPointerException ex) { ex.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } } public static void iterateJsonObject(JSONObject jsonObject, String header) { for (Iterator outerIterate = jsonObject.keySet().iterator(); outerIterate.hasNext(); ) { String key = (String) outerIterate.next(); short type = getType(jsonObject, key); String newHeader = header + "__" + key; if (type == (short) 0) { iterateJsonObject((JSONObject) jsonObject.get(key), newHeader); } else if (type == (short) 1) { iteratorJsonArray((JSONArray) jsonObject.get(key), newHeader); if (getRows().size() != 0) { row = getRows().get(rowsSize); } } else if (type == (short) 2) { createHeader(newHeader); addFieldToRow(String.valueOf(jsonObject.get(key)), key); } } } public static void iteratorJsonArray(JSONArray jsonArray, String header) { if (jsonArray != null) { int index = 0; for (Iterator iterator = jsonArray.iterator(); iterator.hasNext(); ) { List<String> beforeItrFields = new ArrayList<String>(); for (String field : row.getField()) { beforeItrFields.add(""); } if (index == 0) { rowsSize = getRows().size(); } JSONObject jsonObject = (JSONObject) iterator.next(); iterateJsonObject(jsonObject, header); if (!getRows().contains(row)) { getRows().add(row); } reInitializeObj(row); row.setField(beforeItrFields); index++; } } } public static void reInitializeObj(Object o) { if (o instanceof Row) { row = null; row = new Row(); } } //0:jsonObject,1:jsonArray ,2:key/value public static Short getType(JSONObject jsonObject, String key) { if (jsonObject.get(key) instanceof JSONObject) return (short) 0; else if (jsonObject.get(key) instanceof JSONArray) return (short) 1; else return (short) 2; } public static void createHeader(String key) { if (!getHeader().contains(key)) getHeader().add(key); } public static void addFieldToRow(String value, String key) { row.addField(value); } public static JSONObject readJson() throws IOException, ParseException { String filePath = "E:\\1.json"; FileReader reader = new FileReader(filePath); JSONParser jsonParser = new JSONParser(); return (JSONObject) jsonParser.parse(reader); } public static void createExcelFile() throws IOException, IllegalAccessException, InstantiationException { FileOutputStream fileOut = new FileOutputStream("Jira.xls"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet worksheet = workbook.createSheet("work log"); HSSFRow row1 = worksheet.createRow((short) 0); short index = 0; //create header for (String header : getHeader()) { HSSFCell cellA1 = row1.createCell(index); cellA1.setCellValue(header); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GOLD.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellA1.setCellStyle(cellStyle); index++; } //create rows index = 1; for (Row row : getRows()) { HSSFRow excelRow = worksheet.createRow(index); short flag = 0; for (String field : row.getField()) { HSSFCell cellA1 = excelRow.createCell(flag); cellA1.setCellValue(field); flag++; } index++; } workbook.write(fileOut); fileOut.flush(); fileOut.close(); }

}

excel header set fine, but unfortunately fields of rows aren't in correct column they are displaced.

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/BUBVv.jpg" data-original="https://i.stack.imgur.com/BUBVv.jpg" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

what should I do to solve that.thank you

Answer1:

Recommend using a library such as Jackson or Gson to help with this. The idea is to use the library to consume the JSON and convert it into a Map<String, Object> and then drill into that map with your Java code to create the desired output as a .csv file fully compatible with Excel. Does it make sense? This <a href="https://stackoverflow.com/questions/2525042/how-to-convert-a-json-string-to-a-mapstring-string-with-jackson-json" rel="nofollow">link</a> may be helpful.

Recommend

  • XPath - determine the element position
  • How to expire a session if user leave an interface for 2 minutes? ASP.NET
  • changing src attributes
  • Apple Push Notification Bundle Name
  • How to set a field containing unique key
  • Xamarin.iOS Status Bar Disappears When Changing Its Text Color
  • Migrating to CursorLoader & LoaderManager for AutoCompleteTextView with SQLiteDatabase
  • How do you place a variable inside a template tag's argument?
  • Create a mobile app that just points to URL
  • Docker build error: “could not connect to server” (behind proxy)
  • Unknown type name with typedef struct in C
  • GLConsumer is already attached to a context for new SurfaceTexture
  • Firebase suddenly reports invalid signature
  • Hyperlink to Outlook Attachment
  • Create an Office365 mailbox from within C# Web API method
  • How to join two tables from different databases
  • Wireshark Display Filter for Unique Source/Destination IP and Protocol
  • Separating definition/instantiation of template classes without 'extern'
  • Year over Year Stats from a Crossfilter Dataset
  • Rotating Towards Path in OpenGL
  • Floating parent div grows to hypothetical width of floating child div
  • C++ STL stack pop operation giving segmentation fault
  • Cloud Code: Creating a Parse.File from URL
  • How to integrate angular2-material (alpha 8.2) with angular2-Quickstart app
  • How to handle div that is created dynamically in a table
  • Send array to next viewcontroller iOs xcode [duplicate]
  • How to get rgb from transparent pixel in js
  • Why my AngularJS async test in Jasmine 1.3.x is not working?
  • calling IO Operations from thread in ruby c extension will cause ruby to hang
  • Codeigniniter insert data through models and controller
  • XEP-0166: Jingle protocol implementation for voice/video chat in iOS
  • Passing value from popup window to parent form's TextBox
  • Cross compile glibc for arm, got undefined reference to some unwind functions
  • PHP Permalinks.. how to change?
  • Write to .csv file with PHP (Commas in Data Error)
  • media foundation H264 decoder not working properly
  • What does the “id” field in an Android “Google Play Music” broadcast intent correspond to?
  • ARKit code issue {unknown error -1=ffffffffffffffff error: Task failed with exit 1}