一对多数据结构导出Excel表格

同时导出两张表的数据，使用的技术POI，这里说明一下如果导出.xls结尾的使用WSSFWorkbook类，如果使用.xlsx 结尾的使用XSSFWorkbook类

下面就是导出Excel表格的Java代码

try {
          String fileName = "会议"+DateUtils.getDate("yyyyMMddHHmmss")+".xlsx";
   String sheetName = "会议";//sheet名
   List<MeetingIn> list=meetingInService.findList(meetingIn);
   // 第一步，创建一个webbook，对应一个Excel文件
   XSSFWorkbook xb=new XSSFWorkbook();
   // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
   XSSFSheet sheet = xb.createSheet(sheetName);
   // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
   XSSFRow row = sheet.createRow(0);
   // 第四步，创建单元格，并设置值表头 设置表头居中
   XSSFCellStyle style = xb.createCellStyle();
   style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
   XSSFCell cell = null;
   //创建标题
   String[] title = new String[]{"部门","电话","签收时间","签收状态","参会人数","实到人数","姓名","职务","联系方式","参会状态"};//标题
   for(int i=0;i<title.length;i++){
      cell = row.createCell(i);
      cell.setCellValue(title[i]);
      cell.setCellStyle(style);
   }
   SimpleDateFormat sm=new SimpleDateFormat("yyyy-MM-dd HH:mm");
   //创建内容
   Integer k = 1;
   for (int i=0;i<list.size();i++){
      row = sheet.createRow(k);
      row.createCell(0).setCellValue(list.get(i).getUserName());
      row.createCell(1).setCellValue(list.get(i).getUserphone());
      if (list.get(i).getWhetherCheckDate()!=null){
         row.createCell(2).setCellValue(sm.format(list.get(i).getWhetherCheckDate()));
      }else {
         row.createCell(2).setCellValue("");
      }
      if(list.get(i).getWhetherCheck().equals("0")){
         row.createCell(3).setCellValue("未签收");
      }else {
         row.createCell(3).setCellValue("已签收");
      }
      row.createCell(4).setCellValue(list.get(i).getMeetingParticipantsCount());
      row.createCell(5).setCellValue(list.get(i).getMeetingParticipantsCheckIn());
      List<MeetingParticipants> meetingParticipants = list.get(i).getMeetingParticipantsList();
      if(meetingParticipants.size() > 0){
         row.createCell(6).setCellValue(meetingParticipants.get(0).getName());
         row.createCell(7).setCellValue(meetingParticipants.get(0).getDuties());
         row.createCell(8).setCellValue(meetingParticipants.get(0).getPhone());
         if(meetingParticipants.get(0).getType().equals("0")){
            row.createCell(9).setCellValue("未签到");
         }else if(meetingParticipants.get(0).getType().equals("1")){
            row.createCell(9).setCellValue("已签到");
         }else if(meetingParticipants.get(0).getType().equals("2")){
            row.createCell(9).setCellValue("迟到");
         }else {
            row.createCell(9).setCellValue("请假");
         }
         for(int j = 1; j< meetingParticipants.size();j++){
            k++;
            row = sheet.createRow(k);
            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(meetingParticipants.get(j).getName());
            row.createCell(7).setCellValue(meetingParticipants.get(j).getDuties());
            row.createCell(8).setCellValue(meetingParticipants.get(j).getPhone());
            if(meetingParticipants.get(j).getType().equals("0")){
               row.createCell(9).setCellValue("未签到");
            }else if(meetingParticipants.get(j).getType().equals("1")){
               row.createCell(9).setCellValue("已签到");
            }else if(meetingParticipants.get(j).getType().equals("2")){
               row.createCell(9).setCellValue("迟到");
            }else {
               row.createCell(9).setCellValue("请假");
            }
         }
      }
      k++;
   }
   //将文件存到指定位置
   try {
      this.setResponseHeader(response, fileName);
      OutputStream os = response.getOutputStream();
      xb.write(os);
      os.flush();
      os.close();
   } catch (Exception e) {
      e.printStackTrace();
   }
} catch (Exception e) {
   addMessage(redirectAttributes, "导出会议记录失败！失败信息："+e.getMessage());
}

这段代码防止乱码
public void setResponseHeader(HttpServletResponse response, String fileName) {
   try {
      try {
         fileName = new String(fileName.getBytes(),"ISO8859-1");
      } catch (UnsupportedEncodingException e) {
         e.printStackTrace();
      }
      response.setContentType("application/octet-stream;charset=ISO8859-1");
      response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
      response.addHeader("Pargam", "no-cache");
      response.addHeader("Cache-Control", "no-cache");
   } catch (Exception ex) {
      ex.printStackTrace();
   }
}
前端js代码如下
$("#btnExport").click(function(){
    top.layer.confirm('确认要导出Excel吗?', {icon: 3, title:'系统提示'}, function(index){
        //导出excel
        $("#form").attr("action","${ctx}/meeting/export?meetingId=${meeting.id}");
        $("#form").submit();
        top.layer.close(index);
    });
});
<form id="form" method="post">
    <tbody>
    </tbody>
</form>
下面这些表格的数据就是要导出的数据
var url = "${ctx}/meeting/meetingreceice/?meetingId=${meeting.id}";
$.getJSON(url, function (result) {
    var html = "";
    $.each(result.data, function () {
        html += "<tr>" +
                "<td rowspan=" + (this.meetingParticipantsCount+1) + ">" + this.userName + "</td>" +
                "<td rowspan=" + (this.meetingParticipantsCount+1) + ">" + phoneChange(this.officePhone) + "</td>" +
                "<td rowspan=" + (this.meetingParticipantsCount+1) + ">" + date(this.whetherCheckDate) + "</td>" +
                "<td rowspan=" + (this.meetingParticipantsCount+1) + ">" + whetherCheck(this.whetherCheck) + "</td>" +
                "<td rowspan=" + (this.meetingParticipantsCount+1) + ">" + this.meetingParticipantsCheckIn + '/' + this.meetingParticipantsCount + "</td>" +
                "</tr>";

        $.each(this.meetingParticipantsList, function () {
                html += "<tr>" +
                        "<td>" + this.name + "</td>" +
                        "<td>" + this.duties + "</td>" +
                        " <td class='text-navy'>" + phoneChange(this.phone) + "</td> " +
                        "<td>" + change(this.type) + "</td>" +
                        "</tr>";
        });
    });
    $("#tab-1").children().children().find("tbody").html(html);
});
以上导出的难点在于算法上，还有表格的结构不能乱。