前端在線渲染預(yù)覽Excel數(shù)據(jù)

前言

前段時間遇到這樣的需求：需要在瀏覽器中預(yù)覽Excel中的數(shù)據(jù)，并且后續(xù)還會在在線渲染的表格中進行一些貼合業(yè)務(wù)的操作，例如手動匹配或提取表格中的某些數(shù)據(jù)等。

剛拿到這個需求時，并沒有太好的思路。所以就先是看看大佬們是怎么實現(xiàn)的。

技術(shù)調(diào)研

說是技術(shù)調(diào)研，就是在百度谷歌網(wǎng)上搜搜相關(guān)文章，然后再GitHub上看看開源項目。

不搜不知道，搜了之后發(fā)現(xiàn)現(xiàn)在還是有不少成熟的方案。有付費的，也有開源免費的。這里提一下開源的Luckysheet。

Luckysheet：一款純前端類似excel的在線表格，功能強大、配置簡單、完全開源。

Luckysheet 的功能確實很強大。但是也因為功能過于強大，有許多的功能是在這次需求中用不到的，而這次需求中的一些特殊的功能點，用Luckysheet實現(xiàn)也不太容易。

最后經(jīng)過多方面的綜合考慮，還決定自己實現(xiàn)。

功能實現(xiàn)

主要圍繞HTML中的table來進行開發(fā)。

技術(shù)選型

表格解析：Java后端進行Excel文件的數(shù)據(jù)解析，包括單元格數(shù)據(jù)和合并單元格相關(guān)的數(shù)據(jù)；

前端框架：React，沒什么特殊的原因，公司技術(shù)棧。其他框架實現(xiàn)的方式類似。

解析后的數(shù)據(jù)源

因為本文主要介紹前端怎么在線渲染預(yù)覽Excel數(shù)據(jù)，所以就不介紹Java后端是怎么解析數(shù)據(jù)的了。下面直接把解析后的數(shù)據(jù)例子拿來用。

dataList：解析之后的Excel單元格數(shù)據(jù)；mergeList：單元格合并的相關(guān)信息。

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中學(xué)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":"學(xué)校"},{"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中學(xué)"},{"B5":"xxx班"},{"C5":"000000001"},{"D5":"小明"},{"E5":"數(shù)學(xué)"},{"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中學(xué)"},{"B6":"xxx班"},{"C6":"000000002"},{"D6":"小紅"},{"E6":"數(shù)學(xué)"},{"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中學(xué)"},{"B7":"xxx班"},{"C7":"000000004"},{"D7":"小花"},{"E7":"數(shù)學(xué)"},{"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中學(xué)"},{"B8":"xxx班"},{"C8":"000000005"},{"D8":"大力"},{"E8":"數(shù)學(xué)"},{"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中學(xué)"},{"B9":"xxx班"},{"C9":"000000003"},{"D9":"小李"},{"E9":"數(shù)學(xué)"},{"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中學(xué)"},{"B10":"xxx班"},{"C10":"000000008"},{"D10":"小海"},{"E10":"數(shù)學(xué)"},{"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中學(xué)"},{"B11":"xxx班"},{"C11":"000000007"},{"D11":"小樣"},{"E11":"數(shù)學(xué)"},{"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中學(xué)"},{"B12":"xxx班"},{"C12":"000000006"},{"D12":"小王"},{"E12":"數(shù)學(xué)"},{"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中學(xué)"},{"B13":"xxx班"},{"C13":"000000010"},{"D13":"小麗"},{"E13":"數(shù)學(xué)"},{"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中學(xué)"},{"B14":"xxx班"},{"C14":"000000009"},{"D14":"小鵬"},{"E14":"數(shù)學(xué)"},{"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
}

具體實現(xiàn)

直接貼代碼，里面有相關(guān)方法的注釋說明。

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;
}

//?根據(jù)A?返回對應(yīng)數(shù)字
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數(shù)據(jù)
????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數(shù)據(jù)
?????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>
????????????????}
????????????}
????????})
????}

????//?獲取單元格合并的類型和合并數(shù)量
????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
????????????????????//?轉(zhuǎn)換為行數(shù)相同，進行合并列數(shù)計算
????????????????????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
????????}
????}

????//?格式化表格數(shù)據(jù)，并把被合并的單元格進行標(biāo)記
????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;
}

最終效果

我們可以看看最終渲染出來的效果。

看起來是不是還是不錯的。

最后

本文是作者在貼合實際業(yè)務(wù)，具體實踐之后做的總結(jié)，也算是給前端在線渲染預(yù)覽 Excel 提供了一種思路。后續(xù)作者也在此基礎(chǔ)上進行了不少的優(yōu)化和功能的拓展，這些拓展都是結(jié)合了實際需求。不一定是最好，但也算是比較合適的解決方案了。

如果你有更好的看法或建議，歡迎一起討論~