0
0

前端导出导入excel 表格（分别讲解exceljs和xlsx两个库 ）

效果图

需求：在页面中下载关于该项目中的所有模板下载下来，在excel中填写好内容，再导入进系统。如果填写值有问题，或者修改了表头等字段，进行错误提示。其中表格要有样式，要导出下拉框

代码

对exceljs和xlsx两个库都进行讲解

xlsx 文档https://sheetjs.com/

exceljs 文档https://github.com/exceljs/exceljs/blob/master/README_zh.md

两个库使用下来对比 exceljs 比较简单容易上手有中文文档容易上手 xlsx 相对来说比较复杂，而且还要解决报错 样式要借助 xlsx-style 才能使用

exceljs

这里要下载两个库 exceljs 和 file-saver file-saver 是用来保存下载文件的

pnpm add exceljs file-saver
或者
npm install exceljs file-saver

导出文件

  1. 先试试是否能导出文件 这一步没值很正常因为我们还没有创建sheet
import FileSaver from "file-saver";
import ExcelJS from "exceljs";

const confirmHandle = () => {
  // 更具业务需求创建你的文件名字
  let fileName = '测试模板'
  // 创建工作簿
  const workbook = new ExcelJS.Workbook();
  // 写入文件
  workbook.xlsx.writeBuffer().then((buffer) => {
    let _file = new Blob([buffer], {
      type: "application/octet-stream",
    });
    //@ts-ignore
    FileSaver.saveAs(_file, fileName + '.xlsx');
  });
}

2.创建sheet

import FileSaver from "file-saver";
import ExcelJS from "exceljs";

const confirmHandle = () => {
  // 选出选中的表 更具业务需求来 或者你可以指定一个sheet
  let checkedList = machiningContentForm.value.filter(item => item.checked)
  // 更具业务需求创建你的文件名字
  let fileName = '测试模板'
  // 创建工作簿
  const workbook = new ExcelJS.Workbook();
  const createSheet = (item)=>{ 
    // 创建 worksheet  item.twoMenuName 是sheet名字 更具业务需求进行更换 
    // {views: [{showGridLines: false}]} 表示创建一个隐藏了网格线的工作表 下面会把官网几个都列出来
    const worksheet = workbook.addWorksheet(item.twoMenuName, {views: [{showGridLines: false}]});
   worksheet.columns = [
        {header: '字段名', key: 'fieldExegesis', width: 30,},
        {header: '字段值', key: 'fieldValue', width: 30}
      ]
    // 当然也可以 worksheet.addRow({fieldExegesis: '测试', fieldValue: 'John Doe'});
    let rows = [
    ['测试字段名',''], // {fieldExegesis: '测试', fieldValue: 'John Doe'}
    ['测试字段名2',''],
    ]
    worksheet.addRows(rows)

  }
  // 创建sheet表
  checkedList.forEach(createSheet)
  // 写入文件
  workbook.xlsx.writeBuffer().then((buffer) => {
    let _file = new Blob([buffer], {
      type: "application/octet-stream",
    });
    //@ts-ignore
    FileSaver.saveAs(_file, fileName + '.xlsx');
  });
}
  1. 创建样式和导出选项
  const coloring = (cell) => {
    if (!cell) return
    cell.fill = {type: 'pattern', pattern: 'solid', fgColor: {argb: '7ae1b6'},}
    cell.font = {name: "黑体",};
  }
  worksheet.eachRow((row, rowNumber) => {
    row.eachCell((cell, colNumber) => {
       // 加入实线
      cell.border = {
        top: {style: 'thin'}, left: {style: 'thin'}, bottom: {style: 'thin'}, right: {style: 'thin'},
      };
       // 要变颜色的 行和列
      if (fillRow.includes(rowNumber) || fillCol.includes(colNumber)) coloring(cell)
        
      // 加入选择框 
      cell.dataValidation = {
          type: "list",
          allowBlank: true,
          formulae: ['"1,2,3"'],
        };
    })
  })

4.完整的导出代码（基于我自己需求的）

import {fieldDetailsType, fieldTypeEnum, getProjectListAlreadyType} from "@/types/pojectType";
import {ref, watch} from "vue";
import {cloneDeep} from "lodash";
import {projectEnumMap, projectEnumsMap} from "@/enum/projectEnum";
import FileSaver from "file-saver";
import ExcelJS from "exceljs";
import {parseJSON} from "@/utils/public";

const props = defineProps<{
  contentForm: getProjectListAlreadyType[] | undefined,
  propTitle?: string,
  stage?: projectEnumsMap
}>()

const machiningContentForm = ref<{
  twoMenuName?: string,
  fieldObjectResponses: fieldDetailsType[],
  checked?: boolean,
}[]>([])

watch(() => props.contentForm, (value) => {
  value && (machiningContentForm.value = cloneDeep(value).map(item => ({...item, checked: false})))
}, {immediate: true})
const emits = defineEmits(['cancel',])

const setStyle = (worksheet, fillRow: number[], fillCol: number[], selectAll: any) => {
  const coloring = (cell) => {
    if (!cell) return
    cell.fill = {type: 'pattern', pattern: 'solid', fgColor: {argb: '7ae1b6'},}
    cell.font = {name: "黑体",};
  }
  worksheet.eachRow((row, rowNumber) => {
    row.eachCell((cell, colNumber) => {
      cell.border = {
        top: {style: 'thin'}, left: {style: 'thin'}, bottom: {style: 'thin'}, right: {style: 'thin'},
      };
      if (fillRow.includes(rowNumber) || fillCol.includes(colNumber)) coloring(cell)
      if (selectAll[cell.address]) {
        cell.dataValidation = {
          type: "list",
          allowBlank: true,
          formulae: ['"' + selectAll[cell.address].join(',') + '"'],
        };
      }
    })
  })
}

const confirmHandle = () => {
  emits('cancel')
  let checkedList = machiningContentForm.value.filter(item => item.checked)
  let fileName = props?.propTitle + projectEnumMap[props?.stage]?.value + '模板'
  const workbook = new ExcelJS.Workbook();
  const createSheet = (item) => {
    let isTable = item.fieldObjectResponses?.find(item => item.fieldType === fieldTypeEnum.TABLE)
    const worksheet = workbook.addWorksheet(item.twoMenuName, {views: [{showGridLines: false}]});
    if (isTable) {
      let fieldFormat = parseJSON(isTable.fieldFormat), fieldSelect = parseJSON(isTable.fieldSelect)
      let colColorLength = {}, columns = [], rows = [], selectAll = {}
      fieldSelect.forEach((fieldRow, rowIndex) => {
        let obj = {}
        fieldFormat.forEach((field, index) => {
          obj[field.fieldName] = fieldRow[field.fieldName] || ''
          if (!field.fieldType) {
            colColorLength[index + 1] = index + 1
          }
          if (field.fieldType === fieldTypeEnum.SELECT) {
            let col = String.fromCharCode(64 + (index + 1)), select = parseJSON(field.fieldSelect)
            select && (selectAll[col + (rowIndex + 2)] = select.map(item => item.fieldName))
          }
          let findCol = columns.find(item => item.key === field.fieldName)
          if (!findCol) {
            columns.push({
              header: field.fieldExegesis,
              key: field.fieldName,
              width: Math.max((obj[field.fieldName]?.length || 0) * 2 + 3, (field.fieldExegesis?.length || 0) * 2 + 3)
            })
          } else {
            findCol.width = Math.max(findCol.width, ((obj[field.fieldName]?.length || 0) * 2 + 3))
          }
        })
        rows.push(obj)
      })

      worksheet.columns = columns
      worksheet.addRows(rows)
      setStyle(worksheet, [1], Object.values(colColorLength), selectAll)
    } else {
      let rows = [], widthValue = 10, selectAll = {}
      let fieldArr = item.fieldObjectResponses?.sort?.((a, b) => a.fieldOrder - b.fieldOrder) || []
      fieldArr.map((field, index) => {
        let keyName = field.fieldExegesis + (field.fieldUnit ? ' (' + field.fieldUnit + ')' : '')
        let width = (keyName.length * 2) + 3
        widthValue = width > widthValue ? width : widthValue
        rows.push([keyName, ''])
        if (field.fieldType === fieldTypeEnum.SELECT) {
          let select = parseJSON(field.fieldSelect), col = String.fromCharCode(64 + 2)
          select && (selectAll[col + (index + 2)] = select.map(item => item.label))
        }
      })
      worksheet.columns = [
        {header: '字段名', key: 'fieldExegesis', width: widthValue,},
        {header: '字段值', key: 'fieldValue', width: 30}
      ]
      worksheet.addRows(rows)
      setStyle(worksheet, [1], [1], selectAll)
    }
  }
  // 创建sheet表
  checkedList.forEach(createSheet)

  // 写入文件
  workbook.xlsx.writeBuffer().then((buffer) => {
    let _file = new Blob([buffer], {
      type: "application/octet-stream",
    });
    //@ts-ignore
    FileSaver.saveAs(_file, fileName + '.xlsx');
  });
}

导入文件

  1. 先获取到文件 解析成我们想要的格式 我用的是element 里面的文件上传
  const templateImport = (file?: UploadRawFile) => {
     const reader = new FileReader();
     file && reader.readAsArrayBuffer(file);
     return new Promise((resolve, reject) => {
          reader.onload = (e) => {
              let fileString = e.target?.result
              resolve(fileString)
          }
          reader.onerror = () => {
              ElMessage.error('检查上传文件是否正确')
              reject(reader.error)
          }
        })
   }  
  // file 是element 获取过来的文件 用到了里面的raw  
  let data = await templateImport(file.raw)
  1. 把表和sheet都解析出来一一匹配
   import ExcelJS from "exceljs";

   const xlsxConversion = async (fileString: ArrayBuffer, cd?: (sheetName: string, data: any) => void) => {
    try {
        const workbook = new ExcelJS.Workbook();
        let {worksheets} = await workbook.xlsx.load(fileString)
        worksheets.forEach(sheet => {
            const sheetData: any[] = [];
            sheet.eachRow((row, rowNumber) => {
                const rowData: any[] = [];
                row.eachCell((cell, colNumber) => {
                    rowData.push(cell.value);
                });
                sheetData.push(rowData);
            })
            // const sheetData = sheet.getSheetValues() 或者可以直接这样获取sheet内容
            cd && cd(sheet.name, sheetData)
        })
    } catch (err) {
        ElMessage.error('检查上传文件是否正确')
    }
}
 
    const handleTemplateImport = (sheetName,sheetData)=> {
        // 这里就会获取到每个sheetName 和 sheet内容  
    }
    // xlsxConversion(<ArrayBuffer>data, assignmentTabFn)
    await xlsxConversion(<ArrayBuffer>data, handleTemplateImport)

xlsx

如果你不修改样式 只是导出导入一个带有数据的表格你只需要下载xlsx 这个库就行 其他的都不用下 这个库全给你解决了 

pnpm add xlsx
npm install xlsx

如果你要修改样式，那就要下载xlsx file-saver xlsx-style 这三个库了

  1. xlsx-style 这个安装会报错 解决参考 或者自己百度一下错误 有一大堆

  2. vite 的项目直接装 xlsx-style-vite 就不用 装xlsx-style 而且没有报错 美滋滋

pnpm add xlsx file-saver xlsx-style-vite
npm install xlsx file-saver xlsx-style-vite

导出文件

  1. 精简版
import * as XLSX from 'xlsx';
import FileSaver from "file-saver";
//  解决报错 require('./cpt' + 'able'); xlsx-style-vite 是专门针对vite的
import XLSXStyle from "xlsx-style-vite"
// 就是个JSON.parse 只不过我有业务需求 给封装起来了
import {parseJSON, } from "@/utils/public";

const s2ab = (s: any) => {
    let buf = new ArrayBuffer(s.length);
    let view = new Uint8Array(buf);
    for (let i = 0; i < s.length; i++) {
        view[i] = s.charCodeAt(i) & 0xFF;
    }
    return buf;
}
// 设置边框样式
const borderStyle = {
  top: {style: "thin"},
  bottom: {style: "thin"},
  left: {style: "thin"},
  right: {style: "thin"},
};

// 给sheet修改样式 第二个参数是表示要变颜色的列 完整代码中可以看到他的应用
const setStyle = (worksheet, colColorLength) => {

  // 为每个单元格应用边框
  for (let cell in worksheet) {
    if (worksheet.hasOwnProperty(cell) && cell[0] !== '!') {
      worksheet[cell].s = {...worksheet[cell].s, border: borderStyle};
    }
    const firstChar = cell.charAt(0);

    if (cell.replace(/[^0-9]/ig, '') === '1' || colColorLength.includes(firstChar)) {
      worksheet[cell].s = {
        ...worksheet[cell].s, alignment: {
          vertical: "center", horizontal: "center", wrapText: true,
        },
        fill: { //背景色
          fgColor: {rgb: '7ae1b6'}
        },
      }
    }
  }
}

const confirmHandle = () => {
     const workbook = XLSX.utils.book_new();
     let fileName = '自己可以更具需求起名字'

     let rows = [{测试1:'测试1值',测试2:'测试2值'}]
     
     const worksheet = XLSX.utils.json_to_sheet(rows);
     // 修改样式 为每个单元格应用边框
      setStyle(worksheet, [])
     /* fix headers */
     // XLSX.utils.sheet_add_aoa(worksheet, [['字段名', '字段值']],);

     XLSX.utils.book_append_sheet(workbook, worksheet, sheet名字,);
        
           // 改变列宽
      worksheet["!cols"] = [];

      let colWidths = [   /* 写你想要的列宽就好了 */]
      colWidths.forEach((item, index) => {
        worksheet["!cols"]?.push({wch: item});
      })
      // 不用样式就这样导出就好了
      // XLSX.writeFile(workbook, fileName + '.xlsx', {compression: true});
      // xlsx-style 导出  这里的属性可以参考xlsx-style文档
      const wbout = XLSXStyle.write(workbook, {
             bookType: "xlsx",
             bookSST: false,
             type: "binary"
          });
      try {
            FileSaver.saveAs(
            new Blob([s2ab(wbout)], {type: "application/octet-stream"}),
            fileName + '.xlsx'
        );
      } catch (e) {
            if (typeof console !== "undefined") console.log(e, wbout);
      }
}
  1. 下面是我自己业务代码 主要用来参考
import {fieldDetailsType, fieldTypeEnum, getProjectListAlreadyType} from "@/types/pojectType";
import {ref, watch} from "vue";
import {cloneDeep} from "lodash";
import {projectEnumMap, projectEnumsMap} from "@/enum/projectEnum";
import * as XLSX from 'xlsx';
import FileSaver from "file-saver";
import XLSXStyle from "xlsx-style-vite"
import {parseJSON, s2ab} from "@/utils/public";

const props = defineProps<{
  contentForm: getProjectListAlreadyType[] | undefined,
  propTitle?: string,
  stage?: projectEnumsMap
}>()

const machiningContentForm = ref<{
  twoMenuName?: string,
  fieldObjectResponses: fieldDetailsType[],
  checked?: boolean,
}[]>([])

watch(() => props.contentForm, (value) => {
  value && (machiningContentForm.value = cloneDeep(value).map(item => ({...item, checked: false})))
}, {immediate: true})
const emits = defineEmits(['cancel',])
// 设置边框样式
const borderStyle = {
  top: {style: "thin"},
  bottom: {style: "thin"},
  left: {style: "thin"},
  right: {style: "thin"},
};

const setStyle = (worksheet, colColorLength) => {

  // 为每个单元格应用边框
  for (let cell in worksheet) {
    if (worksheet.hasOwnProperty(cell) && cell[0] !== '!') {
      worksheet[cell].s = {...worksheet[cell].s, border: borderStyle};
    }
    const firstChar = cell.charAt(0);

    if (cell.replace(/[^0-9]/ig, '') === '1' || colColorLength.includes(firstChar)) {
      worksheet[cell].s = {
        ...worksheet[cell].s, alignment: {
          vertical: "center", horizontal: "center", wrapText: true,
        },
        fill: { //背景色
          fgColor: {rgb: '7ae1b6'}
        },
      }
    }
  }
}

const confirmHandle = () => {
  emits('cancel')
  let checkedList = machiningContentForm.value.filter(item => item.checked)
  let fileName = props?.propTitle + projectEnumMap[props?.stage]?.value + '模板'
  const workbook = XLSX.utils.book_new();
   const createSheet = (item) => {
    let isTable = item.fieldObjectResponses?.find(item => item.fieldType === fieldTypeEnum.TABLE)
    const rows = [], colWidths = [], colColorLength = []

    if (isTable) {
      let fieldFormat = parseJSON(isTable.fieldFormat)
      let fieldSelect = parseJSON(isTable.fieldSelect)
      fieldSelect.forEach(item => {
        let obj = {}
        fieldFormat.forEach((field, index) => {
          let key = field.fieldExegesis + (field.fieldUnit ? ' (' + field.fieldUnit + ')' : '')
          obj[key] = item[field.fieldName] || ''
          let maxLength = Math.max(key.length, obj[key].length) * 2
          if ((colWidths[index] || 0) < maxLength) colWidths[index] = maxLength
      })
      rows.push(obj)
      })
      fieldFormat.forEach((item, index) => {
        if (!item.fieldType) colColorLength.push(String.fromCharCode(64 + (index + 1)))
      })

    } else {
      let obj = {}
      item.fieldObjectResponses?.map(item => {
        let keyName = item.fieldExegesis + (item.fieldUnit ? ' (' + item.fieldUnit + ')' : '')
        colWidths.push(keyName.length * 2)
        obj[keyName] = ''
      })
      rows.push(obj)
    }

    const worksheet = XLSX.utils.json_to_sheet(rows);
    // 为每个单元格应用边框
    setStyle(worksheet, colColorLength)
    /* fix headers */
    // XLSX.utils.sheet_add_aoa(worksheet, [['字段名', '字段值']],);
    XLSX.utils.book_append_sheet(workbook, worksheet, item.twoMenuName,);
    // 改变列宽
    worksheet["!cols"] = [];
    colWidths.forEach((item, index) => {
      worksheet["!cols"]?.push({wch: item});
    })
  }
  // 创建xlsx 文件
  checkedList.forEach(createSheet )
  // XLSX.writeFile(workbook, fileName + '.xlsx', {compression: true});
  //这里的属性可以参考xlsx-style文档
  const wbout = XLSXStyle.write(workbook, {
    bookType: "xlsx",
    bookSST: false,
    type: "binary"
  });
  try {
    FileSaver.saveAs(
        new Blob([s2ab(wbout)], {type: "application/octet-stream"}),
        fileName + '.xlsx'
    );
  } catch (e) {
    if (typeof console !== "undefined") console.log(e, wbout);
  }

}

导入文件

1.用element 上传组件上传文件并且获取到文件内容 

const templateImport = (file?: UploadRawFile) => {
   const reader = new FileReader();
   file && reader.readAsArrayBuffer(file);
   return new Promise((resolve, reject) => {
       reader.onload = (e) => {
              let fileString = e.target?.result
              resolve(fileString)
          }
          reader.onerror = () => {
              ElMessage.error('检查上传文件是否正确')
              reject(reader.error)
          }
       })
}

let data = await templateImport(file.raw)

2.获取表里面的数据

import * as XLSX from 'xlsx'; 
const xlsxConversion = (fileString: ArrayBuffer, cd: (sheetName: string, data: any) => void) => {
    try {
        let workbook = XLSX.read(fileString, {
            type: 'array',
            cellDates: true,//设为true，将天数的时间戳转为时间格式
        });
        for (const key in workbook?.Sheets) {
            let sheet = workbook.Sheets[key]
            let data = XLSX.utils.sheet_to_json(sheet)
            cd && cd(key, data)
        }
    } catch (err) {
        ElMessage.error('检查上传文件是否正确1')
    }
}

let data = await templateImport(file.raw) 

const assignmentTabFn =(sheetName,sheetData)=>{
    // 这里就是每个sheet表的值了还有名字 而且非常清晰明了
}
xlsxConversion(<ArrayBuffer>data, assignmentTabFn)
标签： excel js vue
本文转载自: https://blog.csdn.net/weixin_58982241/article/details/142789689
版权归原作者 是乔木 所有， 如有侵权，请联系我们删除。
登录后发布评论

“前端导出导入excel 表格（分别讲解exceljs和xlsx两个库 ）”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？

文章导航