Excel利用poi导入导出（下）

一、Controller层

 /**
     * 数据加工加工表下载导出
     *
     * @param
     * @param time
     * @param response
     * @return
     */
    @RequestMapping(value = "/exportExcel", method = RequestMethod.GET)
    @ResponseBody
    public Result exportExcel(@RequestParam(value = "tableFlag") Integer tableFlag, @RequestParam(value = "time", required = false) String time,
                              HttpServletResponse response) {
        String dateNowStr = null;
        if (!StringUtils.isNotEmpty(time)) {
            dateNowStr = time;
        } else {
            dateNowStr = day();
        }

        try {
            switch (tableFlag) {
                case 1:
                    //贷款结构汇总表 
                    LOGGER.info("=========进入贷款结构汇总表导出方法表标识==========");
                    List<LoanStr> loanList = dataProcessService.loanList(exampleLs);
                    if (loanList.size() <= 0) {
                        return new Result(-1, "暂无数据", null);
                     }
                    dataProcessService.exportELS(loanList, response, time);
                    return new Result(0, "导出成功", null);
                    ......................................................
                 }

　              } catch (Exception e) {

                  e.printStackTrace();
                 return new Result(-1, "导出失败", null);
             }
                 return new Result(null);
          }

二、service 

    void exportELS(List<LoanStr> loanList,  HttpServletResponse response,String time)throws IOException;

三、serviceImpl业务实现

@Override
    public void exportELS(List<LoanStr> list, HttpServletResponse response, String time) throws IOException {
        //读取模板路径
        String srcFilePath = Thread.currentThread().getContextClassLoader().getResource("excel/ls.xlsx").getPath();
        String fileName = time + "XXXX总表" + ".xlsx";
        FileInputStream fis = null;
        try {
            //创建Excel文件的输入流对象
            fis = new FileInputStream(srcFilePath);
            //根据模板创建excel工作簿
            XSSFWorkbook workBook = new XSSFWorkbook(fis);
            //获取创建的工作簿第一页
            XSSFSheet sheet = workBook.getSheetAt(0);
            //获取当前sheet最后一行数据对应的行索引
            int currentLastRowIndex = sheet.getLastRowNum();

            //修改标题
            XSSFRow row = sheet.getRow(1);

            XSSFCell cell = row.getCell(0);
//           //获取指定单元格值
            String s = cell.getStringCellValue();
            cell.setCellValue("数据日期:" + time);
            //开始创建并设置该行每一单元格的信息，该行单元格的索引从 0 开始
            //创建一个单元格，设置其内的数据格式为字符串，并填充内容，其余单元格类同
            //逻辑处理
            exLS(list, sheet, currentLastRowIndex);
            writeWorkBook(response, fileName, workBook);
            //关闭流
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            fis.close();
        }
    }

四、写入模板

  private void exLS(List<LoanStr> list, XSSFSheet sheet, int currentLastRowIndex) {
        for (int j = 0; j < list.size(); j++) {
            int newRowIndex = currentLastRowIndex + j + 1;
            XSSFRow newRow = sheet.createRow(newRowIndex);
            Cell cell = newRow.createCell(0);
            cell.setCellValue(list.get(j).getOrgName());
..................................................
       }

private void writeWorkBook(HttpServletResponse httpResp, String fileName, Workbook workbook)
        throws IOException {
    httpResp.reset();
    httpResp.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    String enFileName = URLEncoder.encode(fileName, "UTF-8");
    httpResp.setHeader("Content-Disposition", "attachment; filename*=UTF-8''"
            + enFileName);
    ServletOutputStream httpOut = httpResp.getOutputStream();
    workbook.write(httpOut);
    httpOut.flush();
    httpOut.close();
}