Java 将 json 数组转为 excel 和 csv 并下载

准备

1.需要一个 json 数据数组，格式如下

[
  {
    "screenWidth": 1280,
    "trackState": 1
  },
  {
    "screenWidth": 1280,
    "trackState": 1
  },
  {
    "screenWidth": 1280,
    "trackState": 1
  },
  {
    "screenWidth": 1280,
    "trackState": 1
  }
]

2.工具包(操作JSON)

<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.3.8</version>
        </dependency>

转为 excel 并下载

1.导入依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>

2. json 转 excel

     /**
     * json 转 excel
     * @param jsonArray
     * @return
     * @throws IOException
     */
    public static HSSFWorkbook jsonToExcel(JSONArray jsonArray) throws IOException {
        Set<String> keys = null;
        // 创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建HSSFSheet对象
        HSSFSheet sheet = wb.createSheet("sheet0");

        String str = null;
        int roleNo = 0;
        int rowNo = 0;
        List<JSONObject> jsonObjects = jsonArray.toList(JSONObject.class);

        // 创建HSSFRow对象
        HSSFRow row = sheet.createRow(roleNo++);
        // 创建HSSFCell对象
        //标题
        keys = jsonObjects.get(0).keySet();
        for (String s : keys) {
            HSSFCell cell = row.createCell(rowNo++);
            cell.setCellValue(s);
        }
        rowNo = 0;
        for (JSONObject jsonObject : jsonObjects) {
            row = sheet.createRow(roleNo++);
            for (String s : keys) {
                HSSFCell cell = row.createCell(rowNo++);
                cell.setCellValue(jsonObject.getStr(s));
            }
            rowNo = 0;
        }
        return wb;
    }

3.下载

 @GetMapping("/index/dataDownload/excel")
    @ResponseBody
    public Result exportExcel(String ip, String name, HttpServletResponse response) {
        try{
            //此处为你的json数组
            HSSFWorkbook sheets = jsonToExcel()
            // 配置文件下载
            response.setHeader("content-type", "application/octet-stream");
            response.setContentType("application/octet-stream");
            // 下载文件能正常显示中文
            response.setHeader("Content-Disposition", "attachment;filename=data.xls");
            OutputStream os = response.getOutputStream();
            sheets.write(os);
            sheets.close();
            os.close();
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return new Result(500, e.getMessage());
        }
    }

json 转为 csv 并 下载

1.导包

<dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-csv</artifactId>
            <version>1.5</version>
        </dependency>

2.转换并下载

    @GetMapping("/index/dataDownload/csv")
    @ResponseBody
    public Result exportCsv(String ip, String name, HttpServletResponse response) {
        try{
            // 配置文件下载
            response.setHeader("content-type", "application/octet-stream");
            response.setContentType("application/octet-stream");
            // 下载文件能正常显示中文
            response.setHeader("Content-Disposition", "attachment;filename=data.csv");
            OutputStream os = response.getOutputStream();
            OutputStreamWriter osw = new OutputStreamWriter(os, "GBK");
            // 此处替换为你的 json 数组
            List<JSONObject> list = clusterService.exportCsv(ip, name);

            Set<String> set = list.get(0).keySet();
            List<String> keys = new ArrayList<>(set);

            String[] strings = keys.toArray(new String[keys.size()]);
            CSVFormat csvFormat = CSVFormat.DEFAULT.withHeader(strings);
            CSVPrinter csvPrinter = new CSVPrinter(osw, csvFormat);
            Iterator<JSONObject> iterator = list.iterator();
            while(iterator.hasNext()) {
                Map<String, String> map = new HashMap<>();
                JSONObject next = iterator.next();
                StringBuilder str = new StringBuilder();
                for(String key : keys) {
//                    str.append(next.getStr(key)).append(",");
                    csvPrinter.print(next.getStr(key));
                }
//                str = new StringBuilder(str.substring(0, str.length() - 1));
                csvPrinter.printRecord();
            }
            csvPrinter.flush();
            csvPrinter.close();
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return new Result(500, e.getMessage());
        }
    }