[JavaScript知识库]关于react前端实现导出excel

代码

1.导出按钮

<Button
   type="primary"
   onClick={this.onExportFY}
   style={{ marginRight: '60px' }}
 >导出excel</Button>

2.导出方法

onExportFY = async() => {//导出方法

    let workbook = new Excel.Workbook();

    // Set Workbook Properties
    //设置工作簿属性
    workbook.creator = 'Web';
    workbook.lastModifiedBy = 'Web';
    workbook.created = new Date();
    workbook.modified = new Date();
    workbook.lastPrinted = new Date();
    let sheetName = 'sheet名';
    //大于等于本月1日，小于次月1日
    let currentData = moment().format("YYYY-MM-DD");
    let month = moment().month()+1;
    let year = moment().year();
    let endYear = year;
    let endMonth = month+1;
    if(month==12){
      endYear = year+1;
      endMonth = "01"
    }else if (month<9){
      month = "0"+month
      endMonth ="0"+endMonth
    }else if (month=9){
      month = "0"+month
    }
    let startDate = year+"-"+month+"-"+"01";
    let endDate = endYear+"-"+endMonth+"-"+"01";
    console.log("startDate",startDate,endDate)
    let command ={
      command: [查询语句]}
    let dataSource = await getDocumentsByMongoSql({ 
      collectionName: '数据库名',
      serviceName: '微服务名',
      command,
      pagination:{current:1,pageSize:10000} })
    let worksheet = workbook.addWorksheet(sheetName,{views:[{showGridLines:false}]});
    
    console.log('4455',this.props)
    let colums =  [
  {
      title: '列名',
      dataIndex: 'lieming',
      key: 'lieming',
  }
    ]
    let formData =  [];
    let sheetData = dataSource.content;
    if(!sheetData||Array.isArray(sheetData)&&sheetData.length==0){
      message.warn("无可导出数据")
      return
    }
    console.log('sheetData',sheetData);
    formData = sheetData.map((item,index)=>{
      let map  = {}
      // map["序号"] = index+1
      for(let col of colums){
        let dataIndex = col.dataIndex
        let title = col.title
        map[title] = item[dataIndex]
      return map
    })

    //根据表格数据，获取对应的表格信息
    let columnArr = [];
    for(let i in formData[0]){
      let tempObj = {name:""};
      tempObj.name = i;
      columnArr.push(tempObj)
    }
    //设置表格顶部的信息和样式
     
    //设置表格的主要数据部分
    let headerName = "RequestsList";
    worksheet.addTable({
      name:headerName,
      ref:"A1",
      headerRow: true,
      totalsRow: false,
      style: {
        theme: "TableStyleMedium2",
        showRowStripes: false,
        width: 200
      },
      columns: columnArr ? columnArr : [{name:""}],//把之前的表头数据传递过来
      rows: formData.map((e) => {
        let arr = [];
        for(let i in e){
          arr.push(e[i]);
        }
        return arr;
      })
    })
    // worksheet.getRow(1).eachCell((cell, index) => {
    //   // 设置边框
    //   cell.border = excel_border
    //   console.log("cell",cell);
    //   // if (index % 2 == 1) {
    //   //   // 设置表单label样式：加粗
    //   //   cell.font = excel_header_font_style
    //   // }
    //   // // 设置金额列右对齐
    //   // if(types[index-1]==='number'){
    //   //   if(cell.value==''){
    //   //     cell.value = 0
    //   //   }
    //   //   cell.alignment = {horizontal:"right"}
    //   //   cell.numFmt = "#,##0.00"
    //   // }
    // })
    // // 自动调整列宽
    adjustSheetColumnsWidth(worksheet)
    // 设置边框
    worksheet.getRow(0).eachCell(cell => cell.border = excel_border)
    //表格数据绘制完成，定义下载方法，将数据导出到Excel文件
    workbook.xlsx.writeBuffer().then((data) => {
      const blob = new Blob([data], { type: 'xlsx' });
      FileSaver.saveAs(blob,  `导出数据(${month}月).xlsx`);
    });

}

3.调整列宽

export function adjustSheetColumnsWidth(worksheet) {
  let lengths = []
  worksheet.eachRow(row => 
    row.eachCell((cell, index) => {
      if (!_.isEqual(cell.font, excel_title_font_style)) {
        lengths[index] = Math.max(getByteLength(cell.value), lengths[index] || 0)
      }
    }))
  worksheet.columns = lengths.map(length => ({width: length + 2}))
}

4.引包

import Excel from 'exceljs'
import * as FileSaver from 'file-saver'