excel转换成json

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

pom文件

<!--Excel to Json-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            <version>2.8.2</version>
        </dependency>
        <dependency>
            <groupId>net.sf.json-lib</groupId>
            <artifactId>json-lib</artifactId>
            <version>2.4</version>
            <classifier>jdk15</classifier>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.56</version>
            <scope>compile</scope>
        </dependency>
        <!--Excel to Json End-->

Controller部分

/**
     * 读取Excel转换成 Json
     *
     */
    @PostMapping("/previewExcelToJson")
    @ResponseBody
    public String previewExcel(@RequestBody String path) {
        try {
            JSONArray dataArray = new JSONArray();
            URL httpurl=new URL(URLDecoder.decode(path, "UTF-8"));
            InputStream is;
            HttpURLConnection httpConn=(HttpURLConnection)httpurl.openConnection();
            httpConn.setDoOutput(true);// 使用 URL 连接进行输出
            httpConn.setDoInput(true);// 使用 URL 连接进行输入
            httpConn.setUseCaches(false);// 忽略缓存
            httpConn.setRequestMethod("GET");// 设置URL请求方法
            //可设置请求头
            httpConn.setRequestProperty("Content-Type", "application/octet-stream");
            httpConn.setRequestProperty("Connection", "Keep-Alive");// 维持长连接
            httpConn.setRequestProperty("Charset", "UTF-8");
            httpConn.connect();
            if (httpConn.getResponseCode() >= 400 ) {
                is = httpConn.getErrorStream();
            }
            else{
                is = httpConn.getInputStream();
            }
            InputStream inStream =is;

            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inStream);
            // 循环工作表Sheet
            for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
                String sheetName = xssfSheet.getSheetName();
                if (xssfSheet == null) {
                    continue;
                }
                //当前sheet的json文件
                JSONObject sheetJson = new JSONObject();
                //当前sheet的array，作为sheetJson 的value值
                net.sf.json.JSONArray sheetArr = new net.sf.json.JSONArray();
                //sheet的第一行，获取作为json的key值
                JSONArray key = new JSONArray();
                int xssfLastRowNum = xssfSheet.getLastRowNum();
                // 循环行Row
                for (int rowNum = 0; rowNum <= xssfLastRowNum; rowNum++) {
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if (xssfRow == null) {
                        continue;
                    }

                    // 循环列Cell，在这里组合json文件
                    int firstCellNum = xssfRow.getFirstCellNum();
                    int lastCellNum = xssfRow.getLastCellNum();
                    JSONObject rowJson = new JSONObject();
                    for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                        XSSFCell cell = null;
                        try {
                            cell = xssfRow.getCell(cellNum);
                            if (cell == null) {
                                rowJson.put(key.getString(cellNum), "");
                                continue;
                            }
                            if (rowNum == 0)
                                key.add(toString(cell));
                            else {
                                //若是列号超过了key的大小，则跳过
                                if (cellNum >= key.size()) continue;
                                rowJson.put(key.getString(cellNum), toString(cell));
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                    if (rowJson.keySet().size() > 0)
                        sheetArr.add(rowJson);
                }
                sheetJson.put(sheetName, shuffleData(sheetArr));
                dataArray.add(sheetJson);
            }
            return dataArray.toString();
        } catch (Exception e) {
            e.printStackTrace();
            return JSONNull.getInstance().toString();
        }
    }

Controller中用到的方法

/**
     * 解析json
     *
     * @param cell
     * @return
     */
    private static Object toString(XSSFCell cell) {
        switch (cell.getCellTypeEnum()) {
            case _NONE:
                cell.setCellType(CellType.STRING);
                return "";
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                    return sdf.format(cell.getDateCellValue());
                }
                cell.setCellType(CellType.STRING);
                return cell.getStringCellValue();
            case STRING:
                String val = cell.getStringCellValue();
                if ("无".equalsIgnoreCase(val)) return "";
                //将其中的map格式和数组格式的字符串，转化为相应的数据类型
                if (val.indexOf("{") > -1) {
                    JSONObject jsonObject = JSONObject.fromObject(val);
                    Map<String, Integer> mapJson = JSONObject.fromObject(jsonObject);
                    return mapJson;
                }
                if (val.indexOf("[") > -1) {
                    val = val.substring(1, val.length() - 1);
                    String[] array = val.split(",");
                    return array;
                }
                return val;
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            case BOOLEAN:
                return cell.getBooleanCellValue() + "";
            case ERROR:
                return "非法字符";
            default:
                return "未知字符";
        }
    }

    /**
     * 输出数据
     */
    private static net.sf.json.JSONArray shuffleData(net.sf.json.JSONArray sheetArr) {
        net.sf.json.JSONArray array = new net.sf.json.JSONArray();
        for (int i = 0; i < sheetArr.size(); i++) {
            JSONObject object = sheetArr.getJSONObject(i);
            int count = 0;
            int length = 0;
            for (Object key : object.keySet()) {
                Object o = object.get((String) key);
                length++;
                boolean b = StringUtils.isEmpty(o.toString());
                if (b) {
                    count++;
                }
            }
            if (count != length) {
                array.add(object);
            }
        }
        return array;
    }

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