java 实现导出excel列表

相关坐标

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

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

1.controller层方法

   /**
     * 导出excel列表
     *
     * @param technologyDisplayRepair
     * @author xujj
     * @since 2020-06-10 09:40:07
     */
    @RequestMapping("exportToExcel")
    public void exportExcel(DisplayRepair technologyDisplayRepair, HttpServletRequest request, HttpServletResponse response, HttpSession session) throws Exception {
        try {
            String title="展品报修列表";
            String downDate= DateUtil.ifType("yyyy-MM-dd", "", 0);
            response.setHeader(
                    "Content-disposition",
                    "attachment;" + "filename="
                            + GlobalFunc.toUtf8String(title+downDate+".xlsx"));
            response.setContentType("application/vnd.ms-excel;charset=utf8");
            ServletOutputStream output = response.getOutputStream();

            displayRepairService.exportListToExcel(technologyDisplayRepair, output, session);
            if(output != null){
                output.flush();
                output.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

2.service层方法

  /**
     * 导出列表
     *
     * @param technologyDisplayRepair
     * @param output
     * @param session
     * @throws Exception
     */
    public void exportListToExcel(DisplayRepair technologyDisplayRepair, ServletOutputStream output, HttpSession session) throws Exception {
        SXSSFWorkbook wb = new SXSSFWorkbook();
        Sheet sheet = wb.createSheet("展品报修列表"); // 工作表对象
        sheet = wb.getSheetAt(0);
        CellStyle style = wb.createCellStyle();
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直
        style.setAlignment(CellStyle.ALIGN_CENTER);// 水平
        Row nRow = null; // 行对象
        //Cell nCell = null; // 列对象

        //标题数组
        String[] titles = {"报修展品", "所属展厅", "报修人", "服务商","状态","更新时间"};
        //属性数组
        String[] props = {"displayName","roomName","createUserName","supplierName","repairStatus","updateTime"};
        nRow = sheet.createRow(0);
        for (int i = 0; i < titles.length; i++) {
            sheet.setColumnWidth((short) i, (short) (30 * 200));
            Cell cell = nRow.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(style);
        }
        technologyDisplayRepair.setPageSize(-1);
        List<DisplayRepair> list = this.displayRepairMapper.queryAll(technologyDisplayRepair);
        if (list != null && list.size()>0){
            for (int i = 0; i < list.size(); i++) {
                nRow = sheet.createRow(i + 1);
                nRow.setRowStyle(style);
                Cell cell = null;
                // 将对象转换成map
                Map<String,Object> map = JSON.parseObject(JSON.toJSONString(list.get(i)),Map.class);
                if (map.get("updateTime") != null){
                    String updateTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(map.get("updateTime"));
                    map.put("updateTime",updateTime);
                }
                for (int j = 0; j < props.length; j++) {
                    cell = nRow.createCell(j);
                    if (map.get(props[j]) != null){
                        cell.setCellValue(map.get(props[j]).toString());
                    }

                }
            }
        }
        wb.write(output);
        output.close();
    }