POI-excel

目录

• excel
  • 原生POI
    • 注意点
  • easypoi
    • 模板导出
      • ExcelUtil
      • controller
      • service

excel

原生POI

注意点

• sheet.getRow(i); i:索引，从0开始
• row.getCell(i);i:索引，从0开始
• sheet.getLastRowNum(); 返回最后一行的索引值。
• 合并单元格：wb.getSheetAt(0).addMergedRegion(new CellRangeAddress(4,8,0,0));
  合并单元格语法： 开始行、结束行、开始列、结束列，都是(0开始)

easypoi

模板导出

ExcelUtil

点击查看代码

  /**
     * 获取模板的磁盘绝对路径
     * @param request
     * @param fileName 模板名
     * @return
     */
    public static String getTempletFilePath(HttpServletRequest request,String fileName){
        return request.getSession().getServletContext().getRealPath("/")
                + File.separator + "exporttemp" +File.separator+fileName;
    }
	
	  /**
     * 封装响应流（03excel）
     *
     * @param response 响应对象
     * @param fileName 下载的文件名
     * @throws UnsupportedEncodingException 异常
     */
    public static void responseHeader(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        fileName = URLEncoder.encode(fileName+".xls", "utf-8");
        response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
    }

controller

点击查看代码

 @RequestMapping("/export")
    @ResponseBody
    public void export(HttpServletRequest request, HttpServletResponse response) throws Exception {

        try {
            substationTripService.export(request,getLoginUser(),response);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("导出牵引跳闸记录异常", e.getMessage());
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            response.getWriter().println("导出失败，系统内部错误！");
        }
    }

service

点击查看代码

 public void export(HttpServletRequest request, LoginUser loginUser, HttpServletResponse response) throws Exception {
        Map<String, Object> params = WebUtils.getParametersStartingWith(request, "");
       
        String filePath = ExcelUtil.getTempletFilePath(request, "qianyingtiaozha.xls");

        Page<SubstationTrip> page = findPage(1, 65530, params, loginUser);

        TemplateExportParams templateExportParams = new TemplateExportParams(filePath);
        Map<String, Object> map = new HashMap();
        List<SubstationTrip> list = fmtList(page);
        map.put("list", list);
        Workbook wb = ExcelExportUtil.exportExcel(templateExportParams, map);
        if (wb == null) {
            throw new Exception("error");
        }
        // 重置响应对象
        response.reset();
        ExcelUtil.responseHeader(response,"牵引跳闸记录");
        try {
            OutputStream os = response.getOutputStream();
            BufferedOutputStream bo = new BufferedOutputStream(os);
            wb.write(bo);
            bo.flush();
            bo.close();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }