前端在线渲染预览Excel数据
前端车站
共 13282字，需浏览 27分钟
·
2022-05-26 03:57
前言
前段时间遇到这样的需求：需要在浏览器中预览Excel中的数据，并且后续还会在在线渲染的表格中进行一些贴合业务的操作，例如手动匹配或提取表格中的某些数据等。
刚拿到这个需求时，并没有太好的思路。所以就先是看看大佬们是怎么实现的。
技术调研
说是技术调研，就是在百度谷歌网上搜搜相关文章，然后再GitHub上看看开源项目。
不搜不知道，搜了之后发现现在还是有不少成熟的方案。有付费的，也有开源免费的。这里提一下开源的Luckysheet。
Luckysheet：一款纯前端类似excel的在线表格，功能强大、配置简单、完全开源。
Luckysheet 的功能确实很强大。但是也因为功能过于强大，有许多的功能是在这次需求中用不到的，而这次需求中的一些特殊的功能点，用Luckysheet实现也不太容易。
最后经过多方面的综合考虑，还决定自己实现。
功能实现
主要围绕HTML中的table来进行开发。
技术选型
表格解析：Java后端进行Excel文件的数据解析，包括单元格数据和合并单元格相关的数据；
前端框架：React，没什么特殊的原因，公司技术栈。其他框架实现的方式类似。
解析后的数据源
因为本文主要介绍前端怎么在线渲染预览Excel数据，所以就不介绍Java后端是怎么解析数据的了。下面直接把解析后的数据例子拿来用。
dataList：解析之后的Excel单元格数据；mergeList：单元格合并的相关信息。
data.ts
const dataList=[[{"A1":"2020-01-01xx考试xx成绩表"},{"B1":""},{"C1":""},{"D1":""},{"E1":""},{"F1":""},{"G1":""},{"H1":""},{"I1":""},{"J1":""},{"K1":""},{"L1":""},{"M1":""},{"N1":""},{"O1":""},{"P1":""},{"Q1":""},{"R1":""},{"S1":""},{"T1":""},{"U1":""},{"V1":""},{"W1":""},{"X1":""},{"Y1":""},{"Z1":""},{"AA1":""},{"AB1":""},{"AC1":""}],[{"A2":"单位：xx中学xx班"},{"B2":""},{"C2":""},{"D2":""},{"E2":""},{"F2":""},{"G2":""},{"H2":""},{"I2":""},{"J2":""},{"K2":""},{"L2":""},{"M2":""},{"N2":""},{"O2":""},{"P2":""},{"Q2":""},{"R2":""},{"S2":""},{"T2":""},{"U2":""},{"V2":""},{"W2":""},{"X2":""},{"Y2":""},{"Z2":""},{"AA2":""},{"AB2":""},{"AC2":""}],[{"A3":"学校"},{"B3":"班级"},{"C3":"考号"},{"D3":"姓名"},{"E3":"科目"},{"F3":"成绩"},{"G3":"排名"},{"H3":""},{"I3":"卷1成绩"},{"J3":"卷2成绩"},{"K3":"01"},{"L3":"02"},{"M3":"03"},{"N3":"04"},{"O3":"05"},{"P3":"06"},{"Q3":"07"},{"R3":"08"},{"S3":"09"},{"T3":"10"},{"U3":"11"},{"V3":"12"},{"W3":"13-16"},{"X3":"17"},{"Y3":"18"},{"Z3":"19"},{"AA3":"20"},{"AB3":"21"},{"AC3":"22"}],[{"A4":""},{"B4":""},{"C4":""},{"D4":""},{"E4":""},{"F4":""},{"G4":"班"},{"H4":"校"},{"I4":""},{"J4":""},{"K4":""},{"L4":""},{"M4":""},{"N4":""},{"O4":""},{"P4":""},{"Q4":""},{"R4":""},{"S4":""},{"T4":""},{"U4":""},{"V4":""},{"W4":""},{"X4":""},{"Y4":""},{"Z4":""},{"AA4":""},{"AB4":""},{"AC4":""}],[{"A5":"xxx中学"},{"B5":"xxx班"},{"C5":"000000001"},{"D5":"小明"},{"E5":"数学"},{"F5":139.0},{"G5":1.0},{"H5":1.0},{"I5":60.0},{"J5":79.0},{"K5":5.0},{"L5":5.0},{"M5":5.0},{"N5":5.0},{"O5":5.0},{"P5":5.0},{"Q5":5.0},{"R5":5.0},{"S5":5.0},{"T5":5.0},{"U5":5.0},{"V5":5.0},{"W5":15.0},{"X5":12.0},{"Y5":12.0},{"Z5":7.0},{"AA5":11.0},{"AB5":12.0},{"AC5":10.0}],[{"A6":"xxx中学"},{"B6":"xxx班"},{"C6":"000000002"},{"D6":"小红"},{"E6":"数学"},{"F6":139.0},{"G6":1.0},{"H6":1.0},{"I6":60.0},{"J6":79.0},{"K6":5.0},{"L6":5.0},{"M6":5.0},{"N6":5.0},{"O6":5.0},{"P6":5.0},{"Q6":5.0},{"R6":5.0},{"S6":5.0},{"T6":5.0},{"U6":5.0},{"V6":5.0},{"W6":15.0},{"X6":12.0},{"Y6":12.0},{"Z6":12.0},{"AA6":12.0},{"AB6":6.0},{"AC6":10.0}],[{"A7":"xxx中学"},{"B7":"xxx班"},{"C7":"000000004"},{"D7":"小花"},{"E7":"数学"},{"F7":137.0},{"G7":3.0},{"H7":3.0},{"I7":60.0},{"J7":77.0},{"K7":5.0},{"L7":5.0},{"M7":5.0},{"N7":5.0},{"O7":5.0},{"P7":5.0},{"Q7":5.0},{"R7":5.0},{"S7":5.0},{"T7":5.0},{"U7":5.0},{"V7":5.0},{"W7":15.0},{"X7":12.0},{"Y7":12.0},{"Z7":12.0},{"AA7":10.0},{"AB7":6.0},{"AC7":10.0}],[{"A8":"xxx中学"},{"B8":"xxx班"},{"C8":"000000005"},{"D8":"大力"},{"E8":"数学"},{"F8":136.0},{"G8":4.0},{"H8":4.0},{"I8":60.0},{"J8":76.0},{"K8":5.0},{"L8":5.0},{"M8":5.0},{"N8":5.0},{"O8":5.0},{"P8":5.0},{"Q8":5.0},{"R8":5.0},{"S8":5.0},{"T8":5.0},{"U8":5.0},{"V8":5.0},{"W8":15.0},{"X8":12.0},{"Y8":12.0},{"Z8":10.0},{"AA8":12.0},{"AB8":5.0},{"AC8":10.0}],[{"A9":"xxx中学"},{"B9":"xxx班"},{"C9":"000000003"},{"D9":"小李"},{"E9":"数学"},{"F9":134.0},{"G9":5.0},{"H9":5.0},{"I9":55.0},{"J9":79.0},{"K9":5.0},{"L9":5.0},{"M9":5.0},{"N9":5.0},{"O9":5.0},{"P9":5.0},{"Q9":5.0},{"R9":5.0},{"S9":5.0},{"T9":5.0},{"U9":5.0},{"V9":0.0},{"W9":15.0},{"X9":12.0},{"Y9":12.0},{"Z9":12.0},{"AA9":12.0},{"AB9":6.0},{"AC9":10.0}],[{"A10":"xxx中学"},{"B10":"xxx班"},{"C10":"000000008"},{"D10":"小海"},{"E10":"数学"},{"F10":133.0},{"G10":6.0},{"H10":7.0},{"I10":55.0},{"J10":78.0},{"K10":5.0},{"L10":5.0},{"M10":5.0},{"N10":5.0},{"O10":5.0},{"P10":5.0},{"Q10":5.0},{"R10":5.0},{"S10":5.0},{"T10":5.0},{"U10":0.0},{"V10":5.0},{"W10":15.0},{"X10":12.0},{"Y10":12.0},{"Z10":12.0},{"AA10":12.0},{"AB10":5.0},{"AC10":10.0}],[{"A11":"xxx中学"},{"B11":"xxx班"},{"C11":"000000007"},{"D11":"小样"},{"E11":"数学"},{"F11":131.0},{"G11":7.0},{"H11":8.0},{"I11":60.0},{"J11":71.0},{"K11":5.0},{"L11":5.0},{"M11":5.0},{"N11":5.0},{"O11":5.0},{"P11":5.0},{"Q11":5.0},{"R11":5.0},{"S11":5.0},{"T11":5.0},{"U11":5.0},{"V11":5.0},{"W11":10.0},{"X11":12.0},{"Y11":12.0},{"Z11":12.0},{"AA11":12.0},{"AB11":3.0},{"AC11":10.0}],[{"A12":"xxx中学"},{"B12":"xxx班"},{"C12":"000000006"},{"D12":"小王"},{"E12":"数学"},{"F12":129.0},{"G12":8.0},{"H12":9.0},{"I12":55.0},{"J12":74.0},{"K12":5.0},{"L12":5.0},{"M12":5.0},{"N12":5.0},{"O12":5.0},{"P12":5.0},{"Q12":5.0},{"R12":5.0},{"S12":5.0},{"T12":5.0},{"U12":5.0},{"V12":0.0},{"W12":10.0},{"X12":12.0},{"Y12":12.0},{"Z12":12.0},{"AA12":12.0},{"AB12":6.0},{"AC12":10.0}],[{"A13":"xxx中学"},{"B13":"xxx班"},{"C13":"000000010"},{"D13":"小丽"},{"E13":"数学"},{"F13":127.0},{"G13":9.0},{"H13":11.0},{"I13":60.0},{"J13":67.0},{"K13":5.0},{"L13":5.0},{"M13":5.0},{"N13":5.0},{"O13":5.0},{"P13":5.0},{"Q13":5.0},{"R13":5.0},{"S13":5.0},{"T13":5.0},{"U13":5.0},{"V13":5.0},{"W13":10.0},{"X13":12.0},{"Y13":7.0},{"Z13":12.0},{"AA13":12.0},{"AB13":4.0},{"AC13":10.0}],[{"A14":"xxx中学"},{"B14":"xxx班"},{"C14":"000000009"},{"D14":"小鹏"},{"E14":"数学"},{"F14":124.0},{"G14":10.0},{"H14":14.0},{"I14":40.0},{"J14":84.0},{"K14":5.0},{"L14":5.0},{"M14":5.0},{"N14":5.0},{"O14":5.0},{"P14":5.0},{"Q14":5.0},{"R14":0.0},{"S14":5.0},{"T14":0.0},{"U14":0.0},{"V14":0.0},{"W14":20.0},{"X14":12.0},{"Y14":12.0},{"Z14":12.0},{"AA14":12.0},{"AB14":6.0},{"AC14":10.0}]]
const mergeList = [
['A3', 'A4'],
['B3', 'B4'],
['C3', 'C4'],
['D3', 'D4'],
['E3', 'E4'],
['M3', 'M4'],
['N3', 'N4'],
['O3', 'O4'],
['P3', 'P4'],
['F3', 'F4'],
['G3', 'H3'],
['I3', 'I4'],
['J3', 'J4'],
['K3', 'K4'],
['AA3', 'AA4'],
['AB3', 'AB4'],
['AC3', 'AC4'],
['A1', 'AC1'],
['A2', 'AC2'],
['V3', 'V4'],
['W3', 'W4'],
['X3', 'X4'],
['Y3', 'Y4'],
['Z3', 'Z4'],
['Q3', 'Q4'],
['R3', 'R4'],
['S3', 'S4'],
['T3', 'T4'],
['U3', 'U4'],
['L3', 'L4']
]
export {
dataList,
mergeList
}
具体实现
直接贴代码，里面有相关方法的注释说明。
index.tsx
import React, { useEffect, useState } from 'react';
import './index.less';
import _ from 'lodash';
import { dataList, mergeList } from './data';
// 生成ABCD AA AB 序列
const createCellPos = (n: number) =>{
const ordA = 'A'.charCodeAt(0);
const ordZ = 'Z'.charCodeAt(0);
const len = ordZ - ordA + 1;
let s = "";
while( n >= 0 ) {
s = String.fromCharCode(n % len + ordA) + s;
n = Math.floor(n / len) - 1;
}
return s;
}
// 根据A 返回对应数字
const fromWordToNum = (_data: any[], word: string) => {
if(_.isEmpty(_data)) {
return
}
let num = 0
const arr = _data[0];
let theadData: any[] = []
arr.forEach((item: any, index: number) => {
theadData.push(createCellPos(index))
})
theadData.forEach((item: any, index: number) => {
if(item === word) {
num = index + 1
}
})
return num;
}
// 获取某个单元格的行，如 A1 中的 1
const getNum = (str: string) => {
return Number(str.toString().replace(/[A-Z]+/, ''))
}
// 获取某个单元格的列，如 A1 中的 A
const getWord = (str: string) => {
return str.toString().replace(/[0-9]+/, '')
}
// 格式化 ['A3', 'A4'] 为 [[1,3], [1,4]]
const formatMergeList = (_mergeData: any[], _dataSource: any[]) => {
let formatData: any[] = [];
_mergeData.forEach((i: any) => {
const startCol = getWord(i[0]);
const endCol = getWord(i[1]);
const formartStartCol = fromWordToNum(_dataSource, startCol);
const formartendCol = fromWordToNum(_dataSource, endCol);
const startRow = getNum(i[0]);
const endRow = getNum(i[1]);
formatData.push([
[formartStartCol, startRow],
[formartendCol, endRow]
])
})
return formatData
}
const SheetRender = () => {
const [dataSource, setDataSource] = useState([]);
useEffect(() => {
formatDataSource(dataList, mergeList);
}, [])
// 获取并生成thead数据
const getThead = (_data: any[]) => {
if(_.isEmpty(_data)) {
return []
}
const arr = _data[0];
return arr.map((item: any, index: number) => {
return <td key={index}>{createCellPos(index)}td>
})
}
// 获取并生成tbody数据
const getTbody = (_data: any[]) => {
if(_.isEmpty(_data)) {
return []
}
return _data.map((i, index) => {
return <tr key={index}>
<td>{index + 1}td>
{renderTbodyTd(i)}
tr>
})
}
const renderTbodyTd = (trData: any[]) => {
return trData.map((i: any, index: number) => {
const cell = Object.keys(i)[0];
const value: any = Object.values(i)[0];
const isMerge = i.isMerge;
const {spanType, colSpanNum, rowSpanNum} = getColMerge(mergeList, trData, cell, dataSource)
if(spanType === 'col') {
return <td key={cell} colSpan={colSpanNum}>
{value}
td>
} else if (spanType === 'row') {
return <td key={cell} rowSpan={rowSpanNum}>
{value}
td>
} else if(spanType === 'row-col') {
return <td key={cell} rowSpan={rowSpanNum} colSpan={colSpanNum}>
{value}
td>
} else {
if(Object.values(i)[0] === '' && isMerge) {
return
} else if(Object.values(i)[0] === '' && !isMerge) {
return <td key={cell}>td>
} else {
return <td key={cell}>
{value}
td>
}
}
})
}
// 获取单元格合并的类型和合并数量
const getColMerge = (_mergeData: any[], trData: any[], cell: string, _dataSource: any[]) => {
let spanType = ''; // col, row
let colSpanNum = 0;
let rowSpanNum = 0;
_mergeData.forEach((i: any) => {
if(cell === i[0]) {
const startCol = getWord(i[0]);
const endCol = getWord(i[1]);
const startRow = getNum(i[0]);
const endRow = getNum(i[1]);
if(startCol === endCol) {
spanType = 'row'
rowSpanNum = endRow - startRow + 1
}
if(startRow === endRow) {
spanType= 'col'
let startIndex = 0;
let endIndex = 0;
trData.forEach((td: any, index) => {
if(Object.keys(td)[0] === i[0]) {
startIndex = index
}
if(Object.keys(td)[0] === i[1]) {
endIndex = index
}
})
colSpanNum = endIndex - startIndex + 1;
}
if(startCol !== endCol && startRow !== endRow) {
spanType = 'row-col';
let startIndex = 0;
let endIndex = 0;
rowSpanNum = endRow - startRow + 1
// 转换为行数相同，进行合并列数计算
const transi = endCol + startRow;
trData.forEach((td: any, index) => {
if(Object.keys(td)[0] === i[0]) {
startIndex = index
}
if(Object.keys(td)[0] === transi) {
endIndex = index
}
})
colSpanNum = endIndex - startIndex + 1;
}
}
})
return {
spanType,
colSpanNum,
rowSpanNum
}
}
// 格式化表格数据，并把被合并的单元格进行标记
const formatDataSource = (_dataSource: any[], _mergeData: any[]) => {
if(_.isEmpty(_dataSource)) {
return [];
}
const formatData = formatMergeList(_mergeData, _dataSource);
const dataSourceCopy = _.cloneDeep(_dataSource);
dataSourceCopy.forEach((i: any) => {
i.forEach((j: any) => {
const col = getWord(Object.keys(j)[0]);
const row = getNum(Object.keys(j)[0]);
const formatCol = fromWordToNum(_dataSource, col);
const formatCell: any[] = [formatCol, row];
formatData.forEach((formatDatai: any) => {
if(formatCell[0] >= formatDatai[0][0] && formatCell[0] <= formatDatai[1][0] && formatCell[1] >= formatDatai[0][1] && formatCell[1] <= formatDatai[1][1]) {
j.isMerge = true;
}
})
})
})
setDataSource(dataSourceCopy);
}
return (
<div className='sheet-container'>
<table
className='my-sheet'
cellPadding='0'
cellSpacing='0'
>
<thead>
<tr>
<td className='select-all'>td>
{getThead(dataSource)}
tr>
thead>
<tbody>
{getTbody(dataSource)}
tbody>
table>
div>
)
}
export default SheetRender;
index.css
.sheet-container {
background-color: #fff;
width: 100%;
max-height: calc(100vh - 200px);
overflow: auto;
}
.my-sheet {
border-collapse: separate;
white-space: nowrap;
empty-cells: show;
border: 0px;
background-color: #fff;
width: 0;
border-top: 1px solid transparent;
border-left: 1px solid transparent;
border-right: 1px solid #ccc;
border-bottom: 1px solid #ccc;
}
.my-sheet > thead > tr > td {
border-top: 1px solid #ccc;
border-left: 1px solid #ccc;
border-right: 1px solid transparent;
border-bottom: 1px solid transparent;
background-color: #f3f3f3;
padding: 2px;
cursor: pointer;
box-sizing: border-box;
overflow: hidden;
position: sticky;
top: 0;
z-index: 2;
text-align: center;
width: 50px;
}
.my-sheet > tbody > tr > td:first-child {
position: relative;
background-color: #f3f3f3;
text-align: center;
}
.my-sheet > tbody > tr > td {
border-top: 1px solid #ccc;
border-left: 1px solid #ccc;
border-right: 1px solid transparent;
border-bottom: 1px solid transparent;
padding: 4px 8px;
white-space: nowrap;
box-sizing: border-box;
width: 50px;
text-align: center;
}
最终效果
我们可以看看最终渲染出来的效果。
看起来是不是还是不错的。
最后
本文是作者在贴合实际业务，具体实践之后做的总结，也算是给前端在线渲染预览 Excel 提供了一种思路。后续作者也在此基础上进行了不少的优化和功能的拓展，这些拓展都是结合了实际需求。不一定是最好，但也算是比较合适的解决方案了。
如果你有更好的看法或建议，欢迎一起讨论~
评论
kkFileViewSpring Boot 构建 Excel 文件在线预览项目方案
此项目为文件文档在线预览项目解决方案，对标业内付费产品有【永中office】【office365】【idocv】等，在取得公司高层同意后以Apache协议开源出来反哺社区，在此特别感谢@唐老大的支持以
kkFileViewSpring Boot 构建 Excel 文件在线预览项目方案
0