LuckySheet官网链接
提示:官方提供了LuckyExcel工具做导入excel,但仅支持xlsx格式
- 本文是前端处理方案,读取excel转为luckysheet数据格式;也可在后端去处理,通过接口看能否读取什么格式的excel,都能转为Iuckysheet楼据格式json返回给前端直接用。
- 或者通过后端将其他格式的excel(xls、et)转为xlsx文件,返回给前端再用LuckyExcel处理。
- 坑很多,且其实没必要支持在线编辑excel,一来本身完全可以用office或wps去弄,二来luckysheet不兼容不支持所有的excel编辑功能,所以要么放弃使用lucysheet,前后端配合单纯做excel预览或excel转pdf再预览。
如遇到想支持多种格式excel导入的需求,可以尝试先说服甲方将其他格式的excel另存为成xlsx文件再导入,这是皆大欢喜的方案。
注意是另存为,而不是改文件名后缀,这是不行的
如果前端需求只是做单纯的预览,可以设置禁用编辑,要是不禁编辑但不用保存编辑后的内容,使用luckysheet应该也问题不大;如果要对编辑后的内容保存,之后还要重新下载出来(例如模版导入导出),那就要谨慎使用这类工具。一旦采用这个方案,开发的人就忍受一下痛苦吧哈哈,因为官方也没有提供导出excel的工具方法
,市面上也没有兼容的便捷导出方法,不是数据结构不同就是配置属性API有差异,之间需要做转换,导致学习使用时间或开发成本都变高,本人后来是使用exceljs去适配实现这部分功能,不久后再出一篇文章讲述吧。
1、handleUpload:一般和(UI库)上传组件绑定上传(导入)方法
LuckyExcel支持单元格公式和样式保留,且自动处理
代码如下(示例):
import luckysheet from 'luckysheet'
import LuckyExcel from 'luckyexcel'
import XLSX from 'xlsx'
import _ from 'lodash'
import dayjs from 'dayjs'
import { handleCharToNum, handleRowNo, handleCreateTemplate, luckyDefaultOptions } from '@/utils/handle'
async handleUpload(data) {
const name = data.file.name
const suffix = name.slice(name.lastIndexOf('.') + 1)
const reg = /\d{5,}/
// 识别excel文件后缀格式
if (suffix === 'xlsx') {
// LuckyExcel 仅支持 xlsx
LuckyExcel.transformExcelToLucky(data.file, (exportJson, luckysheetfile) => {
if (!exportJson.sheets || exportJson.sheets.length === 0) {
// 读取Excel文件失败
return
}
const firstSheet = exportJson.sheets[0]
if (firstSheet.celldata.length > 0) {
// 设置报表行列数
const colNum = _.maxBy(firstSheet.celldata, o => o.c).c + 1
const rowNum = _.maxBy(firstSheet.celldata, o => o.r).r + 1
firstSheet.column = colNum
firstSheet.row = rowNum
/**
* 自定义的日期格式'm/d/yy'不能正常显示(显示成数字),特殊处理
* 'yyyy/m/d'会变成'm/d/yy',且无法特殊处理
*/
firstSheet.celldata.forEach(({ v }) => {
if (v.ct && !Object.keys(v.ct).length && !v.m && v.v && typeof v.v === 'string' && reg.test(v.v)) {
const cellDate = dayjs('18991230').add(v.v, 'day').format('M/D/YY')
if (cellDate !== 'Invalid Date') {
v.ct = { fa: 'm/d/yy' }
v.v = Number(v.v)
v.m = cellDate
}
}
})
}
})
this.createLuckysheet(firstSheet)
} else {
// 处理非xlsx问题
this.readNotXlsx(data.file, reg)
}
}
特殊日期格式导入后显示成的数字含义,其实是从1899年12月30日到日期那天的天数
2、createLuckysheet:创建LuckySheet工具
代码如下(示例):
createLuckysheet(sheetConfig) {
const sheetElement = document.getElementById('luckysheet') // 也可用ref获取dom节点
if (!sheetElement && sheetElement.offsetHeight !== 0) {
const options = luckyDefaultOptions(sheetConfig)
luckysheet.create(options)
luckysheet.refreshFormula() // 强制刷新公式,避免修改excel模版多个和公式有关联的单元格值后再导入,没有更新为最新的计算值
}
}
3、readNotXlsx:采用FileReader和xlsx读取excel转为luckysheet数据格式
此方案不能支持单元格样式保留
代码如下(示例):
readNotXlsx(file, reg) {
const reader = new FileReader()
reader.onload = (e) => {
try {
const workbook = XLSX.read(e.target.result, {
type: 'binary',
cellDates: true,
cel1styles: true
})
const sheetData = workbook.Sheets[workbook.SheetNames[0]]
// 若是空白模板,则直接生成空白模板
if (!sheetData['!ref']) {
const defaultTemplate = handleCreateTemplate()
this.createLuckysheet(defaultTemplate)
return
}
// 处理列宽行高
const columnlen = {}
sheetData['!cols'].forEach((it, index) => {
columnlen[index] = it.wpx
})
const rowlen = {}
sheetData['!rows'].map((it, index) => {
rowlen[index] = it.hpx
})
// 处理合并单元格
const merge = {}
sheetData['!merges'].forEach(it => {
const key = it.s.r + '_' + it.s.c
merge[key] = {
r: it.s.r,
c: it.s.c,
rs: it.e.r - it.s.r + 1,
cs: it.e.c - it.s.c + 1
}
})
// 处理数据格式
const celldata = [] // 初始化的单元格数据
const calcChain = [] // 初始化公式链数据
Object.keys(sheetData).forEach(key => {
const cell = sheetData[key]
if (/^[A-Z]{0,}[0-9]*$/.test(key) && cell.t !== 'z') {
const r = handleRowNo(key.replace(/[A-Z]/g, ''))
const c = handleCharToNum(key.replace(/[0-9]/g, ''))
const v = this.valueConvert(r, c, cell, calcChain, reg)
celldata.push({ r, c, v })
}
})
// 获取初始范围
const sqrt = sheetData['!ref'].split(':')
// 初始化工作表配置
const templateData = {
name: workbook.SheetNames[0],
index: '1',
order: 0,
status: 1,
column: handleCharToNum(sqrt[1].replace(/0-9/g, '')) + 1,
config: { columnlen, rowlen, merge },
celldata,
calcChain
}
this.createLuckysheet(templateData)
} catch (err) {
console.error(err)
}
}
reader.readAsBinaryString(file)
}
4、valueConvert:值转换,数据格式转换
代码如下(示例):
valueConvert(r, c, cell, calcChain, reg) {
const cellObj = {
ct: {
fa: cell.t === 's' ? '@' : cel1.z,
t: cell.t
},
v: cell.t === 'd' ? cell.w : cell.v,
m: cell.w
}
if (cell.f) {
cellObj.f = `=${cell.f}`
calcChain.push({ r, c, index: '1' })
}
/**
* 自定义的日期格式'm/d/yy'不能正常显示(显示成数字),特殊处理
* 'yyyy/m/d'会变成'm/d/yy’,且无法特殊处理
*/
if (!cell.z && cell.t === 'n' && reg.test(cell.v)) {
const cellDate = dayjs('18991230').add(cell.v, 'day').format('M/D/YY')
if (cellDate !== 'Invalid Date') {
cellObj.ct = { fa: 'm/d/yy', t: 'd' }
}
}
return cellObj
}
5、'@/utils/handle’文件的工具函数
代码如下(示例):
import { subtract, add, multiply } from 'lodash'
// 行处理
export function handleRowNo(rowNo) {
return subtract(rowNo, 1)
}
// 列处理,字母转数字
export function handleCharToNum(char) {
if (char.length === 0) {
return 0
} else if (char.length === 1) {
return char.charCodeAt(0) - 65
} else {
const charCode = char.charCodeAt(0)
const charNum = handleCharToNum(char.substring(1, char.length))
return add(multiply(subtract(charCode, 64), 26), charNum)
}
}
// 创建空白模版
export function handleCreateTemplate(type = 'view') {
const excelWorkSheet = {
name: 'default',
index: '1',
order: 0,
status: 1,
column: 30,
row: 144,
cellData: [],
data: []
}
for (let i = 0; i < 144; i++) {
const nullArr = []
for (let j = 0; j < 30; j++) {
nullArr.push(null)
}
excelWorkSheet.data.push(nullArr)
}
if (type === 'view') {
// 设滚动条位 0 0
excelWorkSheet.scrollLeft = 0
excelWorkSheet.scrollTop = 0
// 设置权限保护
excelWorkSheet.config = {
authority: {
sheet: 1, // 如果为1或true,则该工作表受到保护;如果为0或false,则该工作表不保护
selectLockedCells: 1, // 选定锁定单元格
selectunLockedCells: 1, // 选定解除锁定的单元格
formatCells: 1, // 设置单元格格式
formatColumns: 1, // 设置列格式
formatRows: 1, // 设置行格式
sort: 1, // 排序
filter: 1, // 使用自动筛选
insertColumns: 0, // 插入列
insertRows: 0, // 插入行
insertHyperlinks: 0, // 插入超链接
deleteColumns: 0, // 删除列
deleteRows: 0, // 删除行
usePivotTablereports: 0, // 使用数据透视表和报表
editObjects: 0, // 编辑对象
editScenarios: 0, // 编辑方案
hintText: '', // 弹窗提示的文字
algorithmName: 'None', // 加密方案: MD2,MD4,MD5,RIPEMD-128,RIPEMD-160,SHA-1,SHA-256,SHA-384,SHA-512,WHIRLPOOL
saltValue: null, // 密码解密的盐参数,为一个自己定的随机数值
allowRangeList: [{ // 区域保护
name: 'area', // 名称
password: '123456', // 密码
hintText: '', // 提示文字
algorithmName: 'None', // 加密方案: MD2,MD4,MD5,RIPEMD-128,RIPEMD-160,SHA-1,SHA-256,SHA-384,SHA-512,WHIRLPOOL
saltValue: null, // 密码解密的盐参数,为一个自己定的随机数值
sqref: '$A1:$Q144' // 区域范围
}]
}
}
}
return excelWorkSheet
}
// 将模版与luckysheet默认配置关联
export function luckyDefaultOptions(sheetOpts) {
return {
container: 'luckysheet',
lang: 'zh',
showtoolbarConfig: [
'undo', 'redo', 'paintFormat', '|',
'font', 'fontSize', 'moreFormats', '|',
'bold', 'italic', 'strikethrough', 'underline', 'textColor', '|',
'fillColor', 'border', 'mergeCe11', '|',
'horizontalAlignMode', 'verticalAlignMode', 'textWrapMode', '|',
'function', 'frozenMode', 'sortAndFilter', 'findAndReplace', 'protection'
],
showinfobar: false,
showsheetbar: false,
enableAddBackTop: false,
enableAddRow: false,
showConfigWindowResize: false,
forceCalculation: false,
// 配置右键菜单
cellRightClickConfig: {
copy: false, // 复制
copyAs: false, // 复制为
paste: false, // 粘贴
insertRow: true, // 插入行
insertColumn: true, // 插入列
deleteRow: true, // 删除选中行
deleteColumn: true, // 删除选中列
deleteCell: false, // 删除单元格
hideRow: false, // 隐藏选中行和显示选中行
hideColumn: false, // 隐藏选中列和显示选中列
rowHeight: true, // 行高
columnWidth: true, // 列宽
clear: false, // 清除内容
matrix: false, // 矩阵操作选区
sort: false, // 排序选区
filter: false, // 筛选选区
chart: false, // 图表生成
image: false, // 插入图片
link: false, // 插入链接
data: false, // 数据验证
cellFormat: false, // 设置单元格格式
},
data: [sheetOpts] // 工作表配置
}
}