springmvc实现导出数据信息为excle表格示例代码

1.项目增加导出日志信息

2.项目中导入poi-*.jar等操作excel文件的jar文件

• poi-3.7-20120326.jar
• poi-excelant-3.7-20101029.jar
• poi-ooxml-3.7.jar
• poi-ooxml-schemas-3.7.jar

Excel导出就是根据前台条件将参数传到controller，根据参数去数据库中进行查询，查询出list集合，将list集合生成excle数据下载。

代码片段：

Contorller.Java

/**
   * 导出信息
   * @param model
   */
  @RequestMapping("exportCustomer.do")
  @SystemControllerLog(description = "数据库表单导出Excle")
  public void exportCustomer(ModelMap model) {
    //TODO 如需添加条件
    //model.addAttribute("username", nameStr);
    //获取需要导出的数据List
    List<CMcustomer> cusList=customerService.exportCustomer(model);
      //使用方法生成excle模板样式
    HSSFWorkbook workbook = customerService.createExcel(cusList, request);
    SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss"); // 定义文件名格式 

    try {
    //定义excle名称 ISO-8859-1防止名称乱码
      String msg = new String(
          ("客户信息_" + format.format(new Date()) + ".xls").getBytes(),
          "ISO-8859-1");
      // 以导出时间作为文件名
      response.setContentType("application/vnd.ms-excel");
      response.addHeader("Content-Disposition", "attachment;filename="
          + msg);
      workbook.write(response.getOutputStream());
    } catch (IOException e) {
      logger.error(e);
    }
  }

2.Service中createExcel方法

public HSSFWorkbook createExcel(List<CMcustomer> cusList,
    HttpServletRequest request) { 

    // 创建一个webbook，对应一个excel文件
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 在webbook中添加一个sheet,对应excel文件中的sheet
    HSSFSheet sheet = workbook.createSheet("客户信息表");
    // 设置列宽
    sheet.setColumnWidth(0, 25 * 100);
    sheet.setColumnWidth(1, 35 * 100);
    sheet.setColumnWidth(2, 35 * 100);
    sheet.setColumnWidth(3, 40 * 100);
    sheet.setColumnWidth(4, 45 * 100);
    sheet.setColumnWidth(5, 45 * 100);
    sheet.setColumnWidth(6, 50 * 100);
    sheet.setColumnWidth(7, 80 * 100);
    sheet.setColumnWidth(8, 35 * 100);
    sheet.setColumnWidth(9, 40 * 100);
    // 在sheet中添加表头第0行
    HSSFRow row = sheet.createRow(0);
    // 创建单元格，并设置表头，设置表头居中
    HSSFCellStyle style = workbook.createCellStyle();
    // 创建一个居中格式
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    // 带边框
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    // 生成一个字体
    HSSFFont font = workbook.createFont();
    // 字体增粗
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    // 字体大小
    font.setFontHeightInPoints((short) 12);
    // 把字体应用到当前的样式
    style.setFont(font); 

    // 单独设置整列居中或居左
    HSSFCellStyle style1 = workbook.createCellStyle();
    style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFCellStyle style2 = workbook.createCellStyle();
    style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); 

    HSSFCellStyle style3 = workbook.createCellStyle();
    style3.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    HSSFFont hssfFont = workbook.createFont();
    hssfFont.setColor(HSSFFont.COLOR_RED);
    hssfFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    style3.setFont(hssfFont); 

    HSSFCellStyle style4 = workbook.createCellStyle();
    style4.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    HSSFFont hssfFont1 = workbook.createFont();
    hssfFont1.setColor(HSSFFont.COLOR_NORMAL);
    hssfFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    style4.setFont(hssfFont1); 

    HSSFCell cell = row.createCell(0);
    cell.setCellValue("序号");
    cell.setCellStyle(style); 

    cell = row.createCell(1);
    cell.setCellValue("客户姓名");
    cell.setCellStyle(style); 

    cell = row.createCell(2);
    cell.setCellValue("性别");
    cell.setCellStyle(style); 

    cell = row.createCell(3);
    cell.setCellValue("状态");
    cell.setCellStyle(style); 

    cell = row.createCell(4);
    cell.setCellValue("电话");
    cell.setCellStyle(style); 

    cell = row.createCell(5);
    cell.setCellValue("邮箱");
    cell.setCellStyle(style); 

    cell = row.createCell(6);
    cell.setCellValue("地址");
    cell.setCellStyle(style);
    for (int i = 0; i < cusList.size(); i++) {
      String logTypeDis = "";
      row = sheet.createRow(i + 1);
      CMcustomer cMcustomer = cusList.get(i);
      // 创建单元格，并设置值
      // 编号列居左
      HSSFCell c1 = row.createCell(0);
      c1.setCellStyle(style2);
      c1.setCellValue(i);
      HSSFCell c2 = row.createCell(1);
      c2.setCellStyle(style1);
      c2.setCellValue(cMcustomer.getCustomername());//客户姓名 

      String sexStr = cMcustomer.getSex();//性别 0:女,1:男
      String sex="";
      if ("1".equals(sexStr)) {
        sex="男";
      }
      if ("0".equals(sexStr)) {
        sex="女";
      }
      HSSFCell c3 = row.createCell(2);//性别
      c3.setCellStyle(style1);
      c3.setCellValue(sex); 

      String statusStr = cMcustomer.getStatus();//客户状态1.在职，2.离职
      String status="";
      if ("1".equals(statusStr)) {
        status="在职";
      }
      if ("2".equals(statusStr)) {
        status="离职";
      }
      HSSFCell c4 = row.createCell(3);//状态
      c4.setCellStyle(style1);
      c4.setCellValue(status);
      String customerid = cMcustomer.getCustomerid();//客户id
      List<CMphone> phoneList = cMphoneMapper.selectByCustomerid(customerid);
      String phone="";
      if (phoneList!=null&&phoneList.size()>0) {
        for (int j = 0; j < phoneList.size(); j++) {
          phone = phoneList.get(j).getPhone();
        }
      }
      HSSFCell c5 = row.createCell(4);//电话
      c5.setCellStyle(style1);
      c5.setCellValue(phone);
      List<CMemail> emailList = cMemailMapper.selectAll(customerid);
      String email="";
      if (emailList!=null&&emailList.size()>0) {
        for (int j = 0; j < emailList.size(); j++) {
          email = emailList.get(j).getEmail();
        }
      }
      HSSFCell c6 = row.createCell(5);//邮箱
      c6.setCellStyle(style1);
      c6.setCellValue(email);
      CMaddress cMaddress=new CMaddress();
      cMaddress.setCustomerid(customerid);
    List<CMaddress> adderssList = cMaddressMapper.selectAll(cMaddress);
      String adderss="";
      if (adderssList!=null&&adderssList.size()>0) {
        for (int j = 0; j < adderssList.size(); j++) {
          adderss = adderssList.get(j).getAddress();
        }
      }
      HSSFCell c7 = row.createCell(6);//地址
      c7.setCellStyle(style1);
      c7.setCellValue(adderss); 

      //使用默认格式
      row.createCell(1).setCellValue(cMcustomer.getCustomername());
      row.createCell(2).setCellValue(sex);
      row.createCell(3).setCellValue(status);
      row.createCell(4).setCellValue(phone);
      row.createCell(5).setCellValue(email);
      row.createCell(6).setCellValue(adderss);
    }
    return workbook;
}

3.页面jsp调用

//导出信息
    function exporBtn(){
    $.ajax({
      type:"POST",
      url:"<%=path%>/customer/exportCustomer.do",
      success:function(data){
        window.open('<%=path%>/customer/exportCustomer.do');
      } 

    });
  } 

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。