当前位置:   article > 正文

excel转换成json_electron execl转json

electron execl转json

记录获取文件服务器(网络地址)中的excel转换为json格式传送到前端

pom文件

  1. <!--Excel to Json-->
  2. <dependency>
  3. <groupId>org.apache.poi</groupId>
  4. <artifactId>poi</artifactId>
  5. <version>3.17</version>
  6. </dependency>
  7. <dependency>
  8. <groupId>com.google.code.gson</groupId>
  9. <artifactId>gson</artifactId>
  10. <version>2.8.2</version>
  11. </dependency>
  12. <dependency>
  13. <groupId>net.sf.json-lib</groupId>
  14. <artifactId>json-lib</artifactId>
  15. <version>2.4</version>
  16. <classifier>jdk15</classifier>
  17. </dependency>
  18. <dependency>
  19. <groupId>com.alibaba</groupId>
  20. <artifactId>fastjson</artifactId>
  21. <version>1.2.56</version>
  22. <scope>compile</scope>
  23. </dependency>
  24. <!--Excel to Json End-->

Controller部分

  1. /**
  2. * 读取Excel转换成 Json
  3. *
  4. */
  5. @PostMapping("/previewExcelToJson")
  6. @ResponseBody
  7. public String previewExcel(@RequestBody String path) {
  8. try {
  9. JSONArray dataArray = new JSONArray();
  10. URL httpurl=new URL(URLDecoder.decode(path, "UTF-8"));
  11. InputStream is;
  12. HttpURLConnection httpConn=(HttpURLConnection)httpurl.openConnection();
  13. httpConn.setDoOutput(true);// 使用 URL 连接进行输出
  14. httpConn.setDoInput(true);// 使用 URL 连接进行输入
  15. httpConn.setUseCaches(false);// 忽略缓存
  16. httpConn.setRequestMethod("GET");// 设置URL请求方法
  17. //可设置请求头
  18. httpConn.setRequestProperty("Content-Type", "application/octet-stream");
  19. httpConn.setRequestProperty("Connection", "Keep-Alive");// 维持长连接
  20. httpConn.setRequestProperty("Charset", "UTF-8");
  21. httpConn.connect();
  22. if (httpConn.getResponseCode() >= 400 ) {
  23. is = httpConn.getErrorStream();
  24. }
  25. else{
  26. is = httpConn.getInputStream();
  27. }
  28. InputStream inStream =is;
  29. XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inStream);
  30. // 循环工作表Sheet
  31. for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
  32. XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
  33. String sheetName = xssfSheet.getSheetName();
  34. if (xssfSheet == null) {
  35. continue;
  36. }
  37. //当前sheet的json文件
  38. JSONObject sheetJson = new JSONObject();
  39. //当前sheet的array,作为sheetJson 的value值
  40. net.sf.json.JSONArray sheetArr = new net.sf.json.JSONArray();
  41. //sheet的第一行,获取作为json的key值
  42. JSONArray key = new JSONArray();
  43. int xssfLastRowNum = xssfSheet.getLastRowNum();
  44. // 循环行Row
  45. for (int rowNum = 0; rowNum <= xssfLastRowNum; rowNum++) {
  46. XSSFRow xssfRow = xssfSheet.getRow(rowNum);
  47. if (xssfRow == null) {
  48. continue;
  49. }
  50. // 循环列Cell,在这里组合json文件
  51. int firstCellNum = xssfRow.getFirstCellNum();
  52. int lastCellNum = xssfRow.getLastCellNum();
  53. JSONObject rowJson = new JSONObject();
  54. for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
  55. XSSFCell cell = null;
  56. try {
  57. cell = xssfRow.getCell(cellNum);
  58. if (cell == null) {
  59. rowJson.put(key.getString(cellNum), "");
  60. continue;
  61. }
  62. if (rowNum == 0)
  63. key.add(toString(cell));
  64. else {
  65. //若是列号超过了key的大小,则跳过
  66. if (cellNum >= key.size()) continue;
  67. rowJson.put(key.getString(cellNum), toString(cell));
  68. }
  69. } catch (Exception e) {
  70. e.printStackTrace();
  71. }
  72. }
  73. if (rowJson.keySet().size() > 0)
  74. sheetArr.add(rowJson);
  75. }
  76. sheetJson.put(sheetName, shuffleData(sheetArr));
  77. dataArray.add(sheetJson);
  78. }
  79. return dataArray.toString();
  80. } catch (Exception e) {
  81. e.printStackTrace();
  82. return JSONNull.getInstance().toString();
  83. }
  84. }

Controller中用到的方法

  1. /**
  2. * 解析json
  3. *
  4. * @param cell
  5. * @return
  6. */
  7. private static Object toString(XSSFCell cell) {
  8. switch (cell.getCellTypeEnum()) {
  9. case _NONE:
  10. cell.setCellType(CellType.STRING);
  11. return "";
  12. case NUMERIC:
  13. if (DateUtil.isCellDateFormatted(cell)) {
  14. SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
  15. return sdf.format(cell.getDateCellValue());
  16. }
  17. cell.setCellType(CellType.STRING);
  18. return cell.getStringCellValue();
  19. case STRING:
  20. String val = cell.getStringCellValue();
  21. if ("无".equalsIgnoreCase(val)) return "";
  22. //将其中的map格式和数组格式的字符串,转化为相应的数据类型
  23. if (val.indexOf("{") > -1) {
  24. JSONObject jsonObject = JSONObject.fromObject(val);
  25. Map<String, Integer> mapJson = JSONObject.fromObject(jsonObject);
  26. return mapJson;
  27. }
  28. if (val.indexOf("[") > -1) {
  29. val = val.substring(1, val.length() - 1);
  30. String[] array = val.split(",");
  31. return array;
  32. }
  33. return val;
  34. case FORMULA:
  35. return cell.getCellFormula();
  36. case BLANK:
  37. return "";
  38. case BOOLEAN:
  39. return cell.getBooleanCellValue() + "";
  40. case ERROR:
  41. return "非法字符";
  42. default:
  43. return "未知字符";
  44. }
  45. }
  46. /**
  47. * 输出数据
  48. */
  49. private static net.sf.json.JSONArray shuffleData(net.sf.json.JSONArray sheetArr) {
  50. net.sf.json.JSONArray array = new net.sf.json.JSONArray();
  51. for (int i = 0; i < sheetArr.size(); i++) {
  52. JSONObject object = sheetArr.getJSONObject(i);
  53. int count = 0;
  54. int length = 0;
  55. for (Object key : object.keySet()) {
  56. Object o = object.get((String) key);
  57. length++;
  58. boolean b = StringUtils.isEmpty(o.toString());
  59. if (b) {
  60. count++;
  61. }
  62. }
  63. if (count != length) {
  64. array.add(object);
  65. }
  66. }
  67. return array;
  68. }

以上  记录一下 大部分代码是查询的 然后有一些问题 进行修改了一下 所以弄成原创吧 哈哈

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/繁依Fanyi0/article/detail/770423
推荐阅读
相关标签
  

闽ICP备14008679号