欢迎使用CSDN-markdown编辑器

关于Excel导出

项目描述：本项目采用框架为前后端分离，后端应用的是SpringBoot,前端采用的是node.js,Excel导出主要可以有一下几种方法实现：

1、在后端通过HSSFWorkbook生成二进制文件流，前端通过Blob来生成文件后，通过href链接过去，（但是Blob不兼容Ie）代码如下：

**生成WorkBoot文件，service代码如下**：public Workbook exportArrangeDuty(ArrangeDutyConditionVo conditionVo) {        Workbook workbook = null;        try{            workbook = new HSSFWorkbook();            Sheet sheet0 = workbook.createSheet();            sheet0.setDefaultColumnWidth((short)20);            Row row0 = sheet0.createRow(0);             String headers[] =                     new String[]{"序号","编码","姓名","权名称","地点","号码","开始日期","结束日期","类型"};            for(int i=0;i<headers.length;i++){                Cell cell = row0.createCell(i);                HSSFRichTextString text = new HSSFRichTextString(headers[i]);                cell.setCellValue(text);            }            List<ArrangeDutyVo> arrangeDutyVoList = this.queryArrangeDuty(conditionVo);            int index = 0;            int num = 1;//序号            HSSFCellStyle cellStyle = (HSSFCellStyle) workbook.createCellStyle();            HSSFDataFormat format= (HSSFDataFormat) workbook.createDataFormat();            cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));            for (ArrangeDutyVo arrangeDutyVo : arrangeDutyVoList) {                index++;                Row row = sheet0.createRow(index);                Cell cell0 = row.createCell(0);                cell0.setCellValue(num++);                Cell cell1 = row.createCell(1);                cell1.setCellValue(arrangeDutyVo.getUserCode());                Cell cell2 = row.createCell(2);                cell2.setCellValue(arrangeDutyVo.getUserName());                Cell cell3 = row.createCell(3);                cell3.setCellValue(arrangeDutyVo.getPermitComName());                Cell cell4 = row.createCell(4);                cell4.setCellValue(arrangeDutyVo.getDutyAddress());                Cell cell5 = row.createCell(5);                cell5.setCellValue(arrangeDutyVo.getTelPhone());                Cell cell6 = row.createCell(6);                cell6.setCellStyle(cellStyle);                cell6.setCellValue(arrangeDutyVo.getDutyBeginTime());                Cell cell7 = row.createCell(7);                cell7.setCellStyle(cellStyle);                cell7.setCellValue(arrangeDutyVo.getDutyEndTime());                Cell cell8 = row.createCell(8);                if(null == arrangeDutyVo.getCaseType() || "".equals(arrangeDutyVo.getCaseType())){                    cell8.setCellValue("所有");                }else{                    cell8.setCellValue(arrangeDutyVo.getCaseType());                }            }        }catch(Exception e){//          e.printStackTrace();            throw new BusinessException("导出文件异常，请稍后再试", false);        }        return workbook;    }**api代码如下**@RequestMapping(value = "/exportArrangeDuty")public ResponseEntity<Resource> exportArrangeDuty(HttpServletRequest request,ArrangeDutyConditionVo conditionVo,HttpServletResponse response){    ByteArrayOutputStream bos = null;    String filename = "测试.xlsx";    try {        Workbook workbook = arrangeDutyService.exportArrangeDuty(conditionVo);        bos = new ByteArrayOutputStream();        workbook.write(bos);        workbook.close();        HttpHeaders headers = new HttpHeaders();        headers.add("Cache-Control", "public no-cache, no-store, must-revalidate");        headers.add("Pragma", "no-cache");        headers.add("Expires", "0");        headers.add("charset", "utf-8");

// headers.add(“filename”, filename);
//设置下载文件名
filename = URLEncoder.encode(filename, “UTF-8”);
headers.add(“Content-Disposition”,”attachment;filename=\”” + filename + “\”“);
Resource resource = new InputStreamResource(new ByteArrayInputStream(bos.toByteArray()));
// return ResponseEntity.ok().headers(headers).contentType(MediaType.parseMediaType(“application/x-msdownload”)).body(resource);
return ResponseEntity.ok().headers(headers).contentType(MediaType.parseMediaType(“application/x-msdownload”)).body(resource);
} catch (IOException e) {
if (null != bos) {
try {
bos.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
}
return null;
}
前端代码如下：
_exportExcel(){
var url = window._domain + “/arrangeduty/exportArrangeDuty”;
var xhr = new XMLHttpRequest();
xhr.open(‘POST’,url, true);
// alert($(‘#searchForm’).serialize());
xhr.setRequestHeader(‘Content-Type’, ‘application/x-www-form-urlencoded’);
xhr.responseType = “blob”;
xhr.onload = function() {
if (this.status == 200) {
var filename=”测试.xls”;
var linkElement = document.createElement(‘a’);
var blob = this.response;
var url = window.URL.createObjectURL(blob);
linkElement.setAttribute(‘href’, url);
linkElement.setAttribute(“download”,filename);

            var clickEvent = new MouseEvent("click", {                "view": window,                "bubbles": true,                "cancelable": false            });            linkElement.dispatchEvent(clickEvent);        }    }    xhr.send($('#searchForm').serialize());  }

2、将数据在后台生成后传到前台，前台生成：

**js代码如下** var rows = 0;        var tblDocument = document;          if (!!inWindow && inWindow != "") {              if (!document.all(inWindow)) {                  return null;              } else {                  tblDocument = eval(inWindow).document;              }          }          var curTbl = tblDocument.getElementById(inTbl);          if (curTbl.rows.length > 65000) {              alert('源行数不能大于65000行');              return false;          }          if (curTbl.rows.length <= 1) {                    //  alert('数据源没有数据');                    return "";                }                var outStr = "";                if (curTbl != null) {                  for (var j = 0; j < curTbl.rows.length; j++) {                      for (var i = 0; i < curTbl.rows[j].cells.length; i++) {                          if (i == 0 && rows > 0) {                              outStr += " \t"; rows -= 1;                          }                          var tc = curTbl.rows[j].cells[i];                          if (j > 0 && tc.hasChildNodes() && tc.firstChild.nodeName.toLowerCase() == "input") {                              if (tc.firstChild.type.toLowerCase() == "checkbox") {                                 var rowNum = j;                                outStr += rowNum + "\t";                                 // if (tc.firstChild.checked == true) {                                  //     outStr += j+1 + "\t";                                  // } else {                                  //     continue;                                  // }                              }                          } else {                              outStr += " " + curTbl.rows[j].cells[i].innerText + "\t";                          }                          if (curTbl.rows[j].cells[i].colSpan > 1) {                              for (var k = 0; k < curTbl.rows[j].cells[i].colSpan - 1; k++) {                                  outStr += " \t";                              }                          }                          if (i == 0) {                              if (rows == 0 && curTbl.rows[j].cells[i].rowSpan > 1) {                                  rows = curTbl.rows[j].cells[i].rowSpan - 1;                              }                          }                      }                      outStr += "\r\n";                  }              } else {              outStr = null; alert(inTbl + "不存在!");          }          return outStr;      }      function doFileExport(inName, inStr) {          var xlsWin = null;          if (!!document.all("glbHideFrm")) {              xlsWin = glbHideFrm;          } else {              var width = 1; var height = 1;              var openPara = "left=" + (window.screen.width / 2 + width / 2) + ",top=" + (window.screen.height + height / 2) +              ",scrollbars=no,width=" + width + ",height=" + height;              xlsWin = window.open("", "_blank", openPara);          }        xlsWin.document.write(inStr);        xlsWin.document.close();          xlsWin.document.execCommand('Saveas', true, inName);          xlsWin.close();      }      function Cleanup() {        window.clearInterval(idTmr);        CollectGarbage();    }