Excel导出

关于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();
    }