使用ExcelJs完成excel导入导出及样式配置

开源

个人开源的leno-admin后台管理项目，前端技术栈：reactHooks、ant-design；后端技术栈：koa、mysql、redis，整个项目包含web端、electron客户端、mob移动端、template基础模板，能够满足你快速开发一整套后台管理项目；如果你觉得不错，就为作者点个✨star✨吧，你的支持就是对我最大的鼓励；

演示地址

文档地址

源码github地址

一、安装

npm i exceljs || yarn i exceljs

二、exceljs的基本使用

2-1、新建一个excel工作簿

const workbook = new ExcelJS.Workbook();

2-2、在工作簿中新建一张工作表

const sheet = workbook.addWorksheet('Sheet1');

2-3、在工作簿中删除一张工作表

// 创建工作表
const sheet = workbook.addWorksheet('Sheet1');

// 使用工作表 id 删除工作表
workbook.removeWorksheet(sheet.id)

2-4、获取工作簿中的某一张工作表

// 遍历所有工作表
// 注意： workbook.worksheets.forEach 仍然是可以正常运行的， 但是以下的方式更好
workbook.eachSheet(function(worksheet, sheetId) {
  // ...
});

// 按 name 提取工作表
const worksheet = workbook.getWorksheet('Sheet1');

// 按 id 提取工作表
const worksheet = workbook.getWorksheet(1);

2-5、获取工作表中的数据集

// 创建工作表
const sheet = workbook.addWorksheet('Sheet1');

// 获取工作表里面的数据
const sheetData = sheet.getSheetValues()

详细的配置项直接前往exceljs官方文档处查找即可：

exceljs中文文档

三、导出excel文件

3-1、封装 excel导出函数

/**
 * excel 导出
 * style:excel表的样式配置
 * tableData:表的数据内容
 * headerColumns:表头配置
 * sheetName：工作表名
 */
export const excelJsExport = async (options: excelParamsType) => {
  const { sheetName, style, headerColumns, tableData } = options

  // 创建工作簿
  const workbook = new XLSX.Workbook()
  workbook.creator = '我隔这敲代码呢'
  workbook.created = new Date()

  // 添加工作表
  const worksheet = workbook.addWorksheet(sheetName)

  if (headerColumns.length > 0) {
    // 设置列头
    const columnsData = headerColumns.map((column, index) => {
      const width = column.width
      return {
        header: column.title,
        key: column.dataIndex,
        width: isNaN(width) ? 20 : width / 10
      }
    })
    worksheet.columns = columnsData
    // 设置表头样式
    const headerRow = worksheet.getRow(1)
    headerRow.eachCell((cell) => {
      cell.style = style as Partial<XLSX.Style>
    })
  }
  // 设置行数据
  if (tableData.length > 0) {
    // 将传入的数据格式化为exceljs可使用的数据格式
    const data = []
    tableData.forEach((table) => {
      let obj = {}
      const tableFlat = flatten(table)

      headerColumns.forEach((header) => {
        if (excelMap.changDictExport[header.dataIndex]) {
          obj[header.dataIndex] =
            excelMap.changDictExport[header.dataIndex][table[header.dataIndex]]
        } else {
          obj[header.dataIndex] = tableFlat[header.dataIndex]
        }
      })
      data.push(obj)
    })

    // 添加行
    if (data) worksheet.addRows(data)
    // 获取每列数据，依次对齐
    worksheet.columns.forEach((column) => {
      column.alignment = style.alignment as Partial<XLSX.Alignment>
    })
    // 设置每行的边框
    const dataLength = data.length as number
    const tabeRows = worksheet.getRows(2, dataLength + 1)
    tabeRows.forEach((row) => {
      row.eachCell({ includeEmpty: true }, (cell) => {
        cell.border = style.border as Partial<XLSX.Borders>
      })
    })
  }

  return await workbook.xlsx.writeBuffer()
}

3-2、exceljs 接收的数据格式

excel的标题头部数据：

export const userExcelHeader = [
  {
    title: '用户序号',
    dataIndex: 'user_id',
    width: 80
  },
  {
    title: '登录名称',
    dataIndex: 'user_name'
  },
  {
    title: '用户邮箱',
    dataIndex: 'email',
    width: 240
  },
  {
    title: '手机号码',
    dataIndex: 'phonenumber'
  },
  {
    title: '用户性别',
    dataIndex: 'sex'
  },
  {
    title: '帐号状态',
    dataIndex: 'status'
  },
  {
    title: '最后登录IP',
    dataIndex: 'login_ip'
  },
  {
    title: '最后登录时间',
    dataIndex: 'login_date'
  },
  {
    title: '部门名称',
    dataIndex: 'dept.dept_name'
  },
  {
    title: '部门负责人',
    dataIndex: 'dept.leader'
  }
]

内容数据：

 [
  {
    user_id: 1,
    user_name: 'admin',
    email: '15345271705@163.com',
    phonenumber: '15345271705',
    sex: '男',
    status: '正常',
    login_ip: '',
    login_date: '00:00:00',
    'dept.dept_name': '深圳总公司',
    'dept.leader': 'wen'
  },
  {
    user_id: 2,
    user_name: 'password',
    email: null,
    phonenumber: null,
    sex: '未知',
    status: '正常',
    login_ip: null,
    login_date: null,
    'dept.dept_name': '研发部门',
    'dept.leader': 'wen'
  },
]

样式配置：

export const excelBaseStyle = {
  font: {
    size: 10,
    bold: true,
    color: { argb: 'ffffff' }
  },
  alignment: { vertical: 'middle', horizontal: 'center' },
  fill: {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: '808080' }
  },
  border: {
    top: { style: 'thin', color: { argb: '9e9e9e' } },
    left: { style: 'thin', color: { argb: '9e9e9e' } },
    bottom: { style: 'thin', color: { argb: '9e9e9e' } },
    right: { style: 'thin', color: { argb: '9e9e9e' } }
  }
}

最终excel的效果：

四、导入excel文件

因为导入的情况比较复杂，会分为多种情况上传excel文件：

1、单文件单工作表；

2、单文件多工作表；

3、多文件(单)多工作表；

我个人解决办法是获取放置excel文件的文件夹内所有的excel文件，然后进行数据的提取，在提取完数据后，就将该次的excel文件删除掉，当然导入excel功能也是需要进行公用封装的；

4-1、封装获取excel上传地址函数

/**
 * EXCEL 获取文件上传地址
 */
export const getExcelAddress = async (fileExistPath: string) => {
  // 多个excel文件保存路径

  let fileNames = []
  fs.readdirSync(path.format({ dir: fileExistPath })).forEach((excel) => {
    if (excel.split('.')[excel.split('.').length - 1] === 'xlsx' && 'xls') {
      fileNames.push(excel)
    }
  })
  return fileNames
}

4-2、封装解析上传的excel函数

/**
 * EXCEL 解析上传文件
 */
export const parsingExcel = async (fileName: string, fileExistPath: string) => {
  const workbook = new XLSX.Workbook()
  //整个文件的绝对路径
  const absoluteFilePath = fileExistPath + '\\' + fileName
  //这种方式是解析buffer
  return await workbook.xlsx.load(fs.readFileSync(absoluteFilePath))
}

4-3、提取上传excel文件的数据

/**
 * EXCEL 提取上传文件的数据
 */
export const importExcelsMid = (option: { password: boolean }) => {
  return async (ctx: Context, next: () => Promise<void>) => {
    try {
      const { password } = option
      const fileExistPath = path.resolve() + '\\src\\upload'
      const fileNames = await getExcelAddress(fileExistPath)
      // 存储多个excel文件
      const workbooksFromBuffer = []
      for (let i = 0; i < fileNames.length; i++) {
        const res = await parsingExcel(fileNames[i], fileExistPath)
        workbooksFromBuffer.push(res)
      }

      // 存储excel表提取的excel数据
      const dataSource = []

      // 第一遍遍历处每个excel文件
      workbooksFromBuffer.forEach((workbook) => {
        // 第二遍遍历操作每个excel文件夹里的每个excel表
        workbook._worksheets.forEach((sheet: XLSX.Worksheet) => {
          // 删除sheet开头的空行
          const sheetValues = workbook.getWorksheet(sheet.id).getSheetValues()
          sheetValues.shift()

          // 拿取字段头数据转成key
          const headerKeys = []
          sheetValues[0].shift()
          sheetValues[0].forEach((header: string, index: number) => {
            headerKeys.push(userExcelHeader[index].dataIndex)
          })
          sheetValues.shift()
          // 第三遍遍历，解析组合数据
          sheetValues.forEach((value: (string | number | null)[]) => {
            value.shift()
            const obj = {}
            value.forEach((item, index: number) => {
              // 如果值为字典内有的值，则需要转换
              const dictKey = excelMap.changDictExport[headerKeys[index]]
              if (dictKey) {
                for (const key in dictKey) {
                  if (item === dictKey[key]) {
                    obj[headerKeys[index]] = key
                  }
                }
              } else {
                obj[headerKeys[index]] = item
              }
              if (password) {
                const salt = bcrypt.genSaltSync(10)
                const hash = bcrypt.hashSync('123456', salt)
                obj['password'] = hash
              }
            })
            dataSource.push(obj)
          })
        })
      })

      // 获取数据后删除excel文件
      fileNames.forEach((path) => {
        removeSpecifyFile(path)
      })

      ctx.state.excelData = dataSource
    } catch (error) {
      console.error('用户excel上传表头格式不正确!', ctx.request['body'])
      return ctx.app.emit('error', importUserListErr, ctx)
    }
    await next()
  }
}

注意：里面有一处是字典替换的，因为暂时还未做字典管理的功能，所以暂时用写死的字典进行替换，后期字典功能做完后，会使用字典功能动态替换

// 写死的字典数据
  changDictExport: {
    sex: {
      '0': '男',
      '1': '女',
      '2': '未知'
    },
    status: {
      '0': '正常',
      '1': '停用'
    }
  }

4-5、将存在多层结构的数据替换

例如：dept.dept_name 替换为 dept_id

   // 将导入用户的部门名称替换成部门id
    for (let i = 0; i < excelData.length; i++) {
      for (let key in excelData[i]) {
        if (key === 'dept.dept_name') {
          const deptArr = (await Dept.findAll({
            raw: true,
            attributes: ['dept_id'],
            where: {
              dept_name: excelData[i][key]
            }
          })) as unknown as { dept_id: number }[]
          excelData[i]['dept_id'] = deptArr[0].dept_id
        }
      }
    }

注意：此处循环不要使用forEach，会造成sql数据获取异步的问题，使用for循环则不会造成；

4-6、将excel提取的数据写入到数据库

写入数据库分为两种情况：

1、新增 且 修改

2、不修改 只 新增

// 导入excel--修改sql
export const judegImportMid = (table, updates) => {
  return async (ctx: Context, next: () => Promise<void>) => {
    const { updateSupport } = ctx.request['body'] as {
      updateSupport: string
    }

    try {
      if (updateSupport === '1') {
        // 新增 且 修改
        await table.bulkCreate(ctx.state.excelData, {
          updateOnDuplicate: updates
        })
      } else {
        // 不更改 只新增
        await table.bulkCreate(ctx.state.excelData)
      }
    } catch (error) {
      console.error('user excel新增与修改错误', ctx.request['body'])
      return ctx.app.emit('error', { code: '400', message: error.errors[0].message }, ctx)
    }

    // 3、返回结果
    ctx.body = {
      code: 200,
      message: '用户信息上传成功！'
    }
  }
}

五、前端 React 封装下载函数

// 通用下载方法
export function download(url: string, fileName: string, params?: object) {
  return instance
    .post(url, params, {
      headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
      responseType: 'blob',
    })
    .then(async (res: any) => {
      let uploadExcel = (fileName: any) => {
        const blob = new Blob([res.data], {
          type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8',
        }) as any
        const url = URL.createObjectURL(blob)
        const aLink = document.createElement('a')
        aLink.setAttribute('download', fileName)
        aLink.setAttribute('href', url)
        document.body.appendChild(aLink)
        aLink.click()
        document.body.removeChild(aLink)
        URL.revokeObjectURL(blob)
      }
      // 此处控制下载文件的名字
      uploadExcel(`${fileName}_${new Date().valueOf()}.xlsx`)
    })
    .catch((r) => {
      console.error(r)
      message.error('下载文件出现错误，请联系管理员！')
    })
}

结语：

以上就是个人对exceljs的一些浅薄理解和使用，文中有错误及优化的地方希望可以在评论区指出，大家一起学习进步，谢谢啦！如果文章给您带来帮助，希望给作者一个三连~😘