效果图
需求:在页面中下载关于该项目中的所有模板下载下来,在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
导出文件
- 先试试是否能导出文件 这一步没值很正常因为我们还没有创建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');
});
}
- 创建样式和导出选项
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');
});
}
导入文件
- 先获取到文件 解析成我们想要的格式 我用的是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)
- 把表和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 这三个库了
xlsx-style 这个安装会报错 解决参考 或者自己百度一下错误 有一大堆
vite 的项目直接装 xlsx-style-vite 就不用 装xlsx-style 而且没有报错 美滋滋
pnpm add xlsx file-saver xlsx-style-vite
npm install xlsx file-saver xlsx-style-vite
导出文件
- 精简版
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);
}
}
- 下面是我自己业务代码 主要用来参考
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)
版权归原作者 是乔木 所有, 如有侵权,请联系我们删除。