java导出json数据到excel中

野性酷女 2021-12-24 08:45 540阅读 0赞

可以用前端js导，也可以使用后端的POI导出

前端导出

base64(s) { return window.btoa(unescape(encodeURIComponent(s))) },
    
    exportExcel() { // 前端导出excel
      console.log(this.showList)
      let str = '<tr><td>代理名称</td><td>allianceid</td><td>attachmentFileId</td>'
      for (let i = 0; i < this.showList.length; i++) {
        str += '<tr>'
        for (let item in this.showList[i]) {
          // 增加\t为了不让表格显示科学计数法或者其他格式
          str += `<td>${this.showList[i][item] + '\t'}</td>`
        }
        str += '</tr>'
      }
      let worksheet = 'Sheet1'
      let uri = 'data:application/vnd.ms-excel;base64,'
      // 下载的表格模板数据
      let template = `<html xmlns:o="urn:schemas-microsoft-com:office:office" 
      xmlns:x="urn:schemas-microsoft-com:office:excel" 
      xmlns="http://www.w3.org/TR/REC-html40">
      <head>
        </head><body><table>${str}</table></body></html>`
      // 下载模板
      window.location.href = uri + this.base64(template)
    },//showList是table数据

后端POI导出excel

public static ResponseEntity<byte[]> export(List<Position> pos) throws IOException {
            //创建一个 excel 文档
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建 excel 属性配置
            workbook.createInformationProperties();
            //获取并且配置文档属性
            DocumentSummaryInformation information = workbook.getDocumentSummaryInformation();
    
            information.setCategory("职位表");
            information.setManager("管理员");
            information.setCompany("liy");
    
            //创建表单
            HSSFSheet sheet = workbook.createSheet();
    
            HSSFRow row = sheet.createRow(0);
            HSSFCell c0 = row.createCell(0);
            HSSFCell c1 = row.createCell(1);
            HSSFCell c2 = row.createCell(2);
            HSSFCell c3 = row.createCell(3);
    
    
           c0.setCellValue("编号");
           c1.setCellValue("职位名称");
           c2.setCellValue("创建日期");
           c3.setCellValue("是否可用");
    
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
    
            for (int i = 0; i < pos.size(); i++) {
                Position position = pos.get(i);
                HSSFRow r = sheet.createRow(i + 1);
                HSSFCell cl0 = r.createCell(0);
                HSSFCell cl1 = r.createCell(1);
                HSSFCell cl2 = r.createCell(2);
                HSSFCell cl3 = r.createCell(3);
    
                cl0.setCellValue(position.getId());
                cl1.setCellValue(position.getName());
                cl2.setCellValue(position.getCreatedate());
                cl2.setCellStyle(cellStyle);
                cl3.setCellValue(position.getEnabled()?"是":"否");
    
            }
    
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            workbook.write(baos);
    
            HttpHeaders headers = new HttpHeaders();
            headers.setContentDispositionFormData("attachment",new String("职位表.xls".getBytes("UTF-8"),"iso-8859-1"));
            return new ResponseEntity<byte[]>(baos.toByteArray(),headers, HttpStatus.CREATED);
    
        }