将页面任意table内容导出为Excel

 主要思路是获取table中行与列封装为二维数组,传入后端解析后保存为Excel

<a-button @click="exportExcel" type="primary" icon="download" style="margin-left: 21px">导出</a-button>

定义导出按钮

//这个是页面按钮触发的方法
exportReport(this.$refs.table,this.title,3)
//触发方法调用的js
import { axios } from '@/utils/request'
import Vue from 'vue'
export function exportReport(table,excelName,headNum){
      if(!table){
        return
      }
      let tableObj =  table.$el.querySelector('table')
      let params = {}
      params.tableData = getTableArray(tableObj)
      params.tableName = excelName+'.xls'
      params.headNum = headNum
      let url = `${window._CONFIG['domianURL']}/controltotal/reportexport/export`
      downFile(url,params).then((data) => {
            if (!data || data.size === 0) {
                  Vue.prototype['$message'].warning('文件下载失败')
                  return
                }
                if (typeof window.navigator.msSaveBlob !== 'undefined') {
                  window.navigator.msSaveBlob(new Blob([data]), params.tableName)
                } else {
                  let url = window.URL.createObjectURL(new Blob([data]))
                  let link = document.createElement('a')
                  link.style.display = 'none'
                  link.href = url
                  link.setAttribute('download', params.tableName)
                  document.body.appendChild(link)
                  link.click()
                  document.body.removeChild(link) //下载完成移除元素
                  window.URL.revokeObjectURL(url) //释放掉blob对象
                }
      })

}
function downFile(url,parameter){
  return axios({
      url: url,
      method: 'post',
      data: parameter,
      responseType: 'blob'
      })
}
function getTableArray(table) {
   
    let rows =  table.querySelectorAll('tr')
    let rowsArr = []
    //第一次循环将要横向合并的单元格填充为空,同时考虑行列都有合并情况
    for(let rowsNumber = 0; rowsNumber < rows.length; rowsNumber++){
          let row = rows[rowsNumber]
          let colums = row.querySelectorAll('th, td')
          let columsArr = []
          for(let colNumber = 0; colNumber< colums.length; colNumber++){
                    let TableCell = {}
                    let cell = colums[colNumber]
                    TableCell.value = cell.textContent
                    TableCell.rowSpan = cell.getAttribute('rowspan')
                    TableCell.colSpan = cell.getAttribute('colspan')
                    if(cell.getAttribute('rowspan')){
                        let rowspan = cell.getAttribute('rowspan')
                        let leftNum = parseInt(rowspan)
                        TableCell.leftNum = leftNum
                    }
                    columsArr.push(TableCell)
                    if(cell.getAttribute('colspan')){
                      let colspan = cell.getAttribute('colspan')
                      let pushNum = parseInt(colspan)
                      for(let i=1;i<pushNum;i++){
                        let emp = {}
                        emp.value = ''
                        emp.rowSpan = null
                        emp.colSpan = null
                        if(TableCell.leftNum){
                           emp.leftNum = TableCell.leftNum
                        }
                        columsArr.push(emp)
                      }
                    }
          }
          rowsArr.push(columsArr)
    }

    //这次循环的目的是使每一行的单元格数量相等;并且考虑列合并
    for(let rowsNumber = 1; rowsNumber < rowsArr.length; rowsNumber++){
          let thisColumns = rowsArr[rowsNumber]
          let preColums = rowsArr[rowsNumber-1]
          let newArr = []
          let index = 0
          if(thisColumns.length != preColums.length){
                for(let i =0;i<preColums.length;i++){
                   let obj = preColums[i]
                   let emp = {}
                   emp.value = ''
                   emp.rowSpan = null
                   emp.colSpan = null
                   if(obj.leftNum){
                        let leftNum = obj.leftNum -1
                        if(leftNum > 1){
                              emp.leftNum =  obj.leftNum -1
                        }
                        newArr.push(emp)
                   }else{
                        if(thisColumns[index]){
                          newArr.push(thisColumns[index])
                        }else{
                          newArr.push(emp)
                        }
                        index++
                   }
                }
                rowsArr[rowsNumber] = newArr
          }
    }
    return rowsArr;
  }

后端就比较简单了,根据前端传来的数组,一行一行的创建单元格

@PostMapping(value = "/export")
    public void reportExport(@RequestBody ExportParam exportParam, HttpServletResponse response) {
         //这里的名字其实没有用的
        String excelName = "报表导出数据.xls";
        if (StringUtils.isNotBlank(exportParam.getTableName())) {
            excelName = exportParam.getTableName();
        }
        TableToExcelUtil util = new TableToExcelUtil();
        HSSFWorkbook workbook = util.createExcelFormTable(exportParam);
        response.setHeader("Content-Disposition", "attachment;filename="+excelName);
        response.setContentType("application/octet-stream");
        response.setStatus(HttpServletResponse.SC_OK);
        // 将 Excel 文件写入 response 输出流
        try {
            workbook.write(response.getOutputStream());
        }catch (IOException  e){
            response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
        }finally {
            try {
                workbook.close();
            }catch (IOException e){

            }
        }
    }

下面是工具类,内容主要都在循环中

 public HSSFWorkbook createExcelFormTable(ExportParam exportParam){
        JSONArray tableData = exportParam.getTableData();
        HSSFWorkbook wb = new HSSFWorkbook();
        CellStyle headStyle = this.createHeadStyle(wb);
        CellStyle bodyStyle = this.createBodyStyle(wb);
        HSSFSheet sheet = wb.createSheet("test");
        for (int rowNumber =0; rowNumber<tableData.size(); rowNumber++) {
            HSSFRow row = sheet.createRow(rowNumber);
            List<HashMap<String,String>> columData = (List<HashMap<String,String>>)tableData.get(rowNumber);
            for(int columNumber = 0; columNumber< columData.size(); columNumber++){
                row.createCell(columNumber);
                HSSFCell cell = row.getCell(columNumber);
                //判断是否为表头，使用对应的excel格式
                if(rowNumber < exportParam.getHeadNum()){
                    cell.setCellStyle(headStyle);
                }else{
                    cell.setCellStyle(bodyStyle);
                }
                HashMap<String,String> col = columData.get(columNumber);
                if(null != col.get("rowSpan")){
                    //纵向合并
                    CellRangeAddress cellAddresses = new CellRangeAddress(rowNumber, rowNumber+Integer.parseInt(col.get("rowSpan"))-1, columNumber, columNumber);
                    removeOverlappingMergedRegions(sheet, cellAddresses);
                    sheet.addMergedRegion(cellAddresses);
                }
                if (null != col.get("colSpan")) {
                    //横向合并
                    CellRangeAddress cellAddresses = new CellRangeAddress(rowNumber, rowNumber, columNumber, columNumber+Integer.parseInt(col.get("colSpan"))-1);
                    removeOverlappingMergedRegions(sheet, cellAddresses);
                    sheet.addMergedRegion(cellAddresses);
                }
                cell.setCellValue(col.get("value"));
            }
        }
        return wb;
    }