java将数据生成execl的两种方式

一：后台拼接返回页面直接下载：

前端代码：
//导出
      $("#excelBtn").on('click', function () {
      var ruleId = $("#ruleId").val();
      var sort = $("#sort").val();
      var dwcode = $("#dwcode").val();
      //得到绩效名字
      var ruleName= $("#ruleId").find("option:selected").text();
      var checkMonth = $("#checkMonth").val();
  window.open("<%=basePath%>admin/performance/count/count_monthListUI_excel?                        ruleId="+ruleId+"&sort="+sort+"&dwcode="+dwcode+"&ruleName="+ruleName+"&checkMonth="+checkMonth);
     });

接口代码：
/**
* 窗口月考核统计excel导出
* @return
*/
@RequestMapping("admin/performance/count/count_monthListUI_excel")
public void queue_list_exportData(HttpServletRequest request, HttpServletResponse response, OutputStream os) throws IOException{
   try{
      // 定义导出参数
      ExportParams params = new ExportParams();
      String ruleName = Common.toUTF(request.getParameter("ruleName"));
      params.setTitle("窗口考核得分("+ruleName+")");
      Workbook workbook = new XSSFWorkbook();
      Sheet sheet = workbook.createSheet();
      Map para = new HashMap();
      para.put("ruleId", Common.toUTF(request.getParameter("ruleId")));
      para.put("sort", Common.toUTF(request.getParameter("sort")));
      para.put("dwcode", Common.toUTF(request.getParameter("dwcode")));
      para.put("checkMonth", request.getParameter("checkMonth"));
      List list = countService.selectCountMonthList(para);
      Row row = sheet.createRow(0);
      row.createCell(0).setCellValue("序号");
      row.createCell(1).setCellValue("考核月份");
      row.createCell(2).setCellValue("所属部门");
      row.createCell(3).setCellValue("人员数");
      row.createCell(4).setCellValue("窗口考核分");
      row.createCell(5).setCellValue("人员考核平均分");
      row.createCell(6).setCellValue("考核总分");
      Row row_i;
      for (int i = 0; i < list.size(); i++) {
         Map map = (Map)list.get(i);
         row_i = sheet.createRow(i+1);
         row_i.createCell(0).setCellValue(i+1);
         row_i.createCell(1).setCellValue(Common.isBlank(map.get("ruleTime")));
         row_i.createCell(2).setCellValue(Common.isBlank(map.get("dwname")));
         row_i.createCell(3).setCellValue(Common.isBlank(map.get("count")));
         row_i.createCell(4).setCellValue(Common.isBlank(map.get("score")));
         row_i.createCell(5).setCellValue(Common.isBlank(map.get("avg")));
         row_i.createCell(6).setCellValue(Common.isBlank(map.get("sum")));
      }
      //设置列宽
      String fname = "窗口考核得分("+ruleName+")"+".xls";
      // 清空输出流
      response.reset();
      // 设置相应内容的编码格式
      response.setCharacterEncoding("UTF-8");
      response.setHeader("Content-Disposition", "attachment;filename=" + new String(fname.getBytes(), "ISO8859-1"));
      // 定义输出类型
      response.setContentType("application/msexcel");
      // 将文件写入输出流
      workbook.write(os);
      os.close();
   } catch (Exception e) {
      System.out.println("导出数据失败！"+e.getCause());
      response.sendRedirect("fileError");
   }
}

二：后台得到数据前端渲染后下载：

前端代码：

 //导出
$("#excelBtn").on('click', function () {
   var ruleId = $("#ruleId").val();
var sort = $("#sort").val();
var checkMonth = $("#checkMonth").val();
var dwcode = $("#dwcode").val();
window.open("<%=basePath%>admin/performance/count/count_windowListUI_excel?ruleId="
             +ruleId+"&sort="+sort+"&checkMonth="+checkMonth+"&dwcode="+dwcode);
  });

接口代码：

 

/**
 * 窗口考核得分列表  excel导出
 * @param request
 * @return
 */
@RequestMapping("admin/performance/count/count_windowListUI_excel")
public String count_windowListUI_excel(HttpServletRequest request){
   String checkMonth = Common.toUTF(request.getParameter("checkMonth"));
   Map para = new HashMap();
   para.put("type", "0");
   para.put("ruleId", Common.toUTF(request.getParameter("ruleId")));
   para.put("sort", Common.toUTF(request.getParameter("sort")));
   para.put("checkMonth", checkMonth);
   para.put("dwcode", Common.toUTF(request.getParameter("dwcode")));
   //二级扣分明细目录
   para.put("mxType", 2);
   request.setAttribute("mxDeList", countService.selectMxCount(para));
   para.put("mxType", 1);
   request.setAttribute("mxAddList", countService.selectMxCount(para));
   //评价列表
   request.setAttribute("checkMonth", checkMonth);
   request.setAttribute("pjList", countService.selectPjCount(para));
   request.setAttribute("countList", countService.selectCountWindowExcel(para));;
   return "performance/count/count_windowListUI_excel";
}

渲染页面：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<%@ taglib prefix="shiro" uri="http://shiro.apache.org/tags"%>
<%@ page contentType= "application/msexcel"%>
<%
String checkMonth = request.getParameter("checkMonth");
String fileName="市民中心窗口考核得分统计表（"+checkMonth+")";
fileName = new String(fileName.getBytes( "GBK"), "ISO8859-1");
response.setHeader("Content-disposition", "inline; filename=" +fileName+ ".xls");
%>
<!DOCTYPE html>
<html>
<body>
   <table border ="1">
      <tr>
         <th rowspan="2">单位</th>
         <th colspan="${fn:length(mxDeList) }">考核内容(扣分值)</th>
         <th colspan="${fn:length(mxAddList) }">考核内容(加分值)</th>
         <th rowspan="2">考核得分</th>
         <c:if test="${fn:length(pjList)>0 }">
            <th colspan="${fn:length(pjList) }">评级满意率</th>
         </c:if>
      </tr>
      <tr>
         <c:forEach items="${mxDeList }" var="list">
            <th>${list.name }</th>
         </c:forEach>
         <c:forEach items="${mxAddList }" var="list">
            <th>${list.name }</th>
         </c:forEach>
         <c:forEach items="${pjList }" var="list">
            <th>${list.name }</th>
         </c:forEach>
      </tr>
      <c:forEach items="${countList}" var="list" varStatus="vs">
         <tr>
            <td>${list.dwname}</td>
            <c:forEach items="${mxDeList }" var="mx" varStatus="status">
               <c:set var="tds" value="td${status.index+1 }"/>
               <td>${list[tds]}</td>
            </c:forEach>
            <c:forEach items="${mxAddList }" var="mx" varStatus="status">
               <c:set var="tds" value="tt${status.index+1 }"/>
               <td>${list[tds]}</td>
            </c:forEach>
            <td>${list.score}</td>
            <c:forEach items="${pjList }" var="mx" varStatus="status">
               <c:set var="pjs" value="pj${status.index+1 }"/>
               <td>${list[pjs]}</td>
            </c:forEach>
         </tr>
      </c:forEach>
   </table>
</body>
</html>