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

浅浅的花香味﹌ 2022-11-30 12:35 303阅读 0赞

## 准备 ##

### 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());
            }
        }