项目描述
纯前端excel文件对比项目,用于替换w.jascript.cn
技术栈
- 纯前端 纯html+js项目
- 前端ui框架:zui https://www.openzui.com/ ,为了加载快,仅用css不用动态类组件
- 数据绑定:Alpine.js https://www.alpinejs.cn/
- excel读取SheetJs https://github.com/SheetJS/sheetjs
- Electron 客户端化备选,写完再考虑
一些点
- 由于对比原文件是一个类似某机构公开的数据库的文件,不需要经常更新,所以首次加载后使用localStorage保存,下次打开后直接读取,避免手工选文件
- 为避免每次都遍历所有数据库数据,对比时先copy一份数据库数据 ,然后每对比一次,将匹配到的数据从数据库删除
- 其他好像没什么,Alpine.js 用起来很舒服很适合后端写前端,很像之前用的rivets.js,几次出bug以为是他的问题,结果是代码逻辑写错了,下面是列表渲染代码
<template x-for="(row,index) in appData.tableData.rows" key="index">
<tr>
<td x-text="index+1"></td>
<td x-text="row['MCC']"></td>
<td x-text="row['MNC']"></td>
<td x-show="!appData.isCompareResult" x-text="row['Country Initials']"></td>
<td x-text="row['Organisation']"></td>
<td x-show="!appData.isCompareResult" x-text="row['TADIG code']"></td>
<td x-text="row['Network']"></td>
<td x-text="row['PPCI&N']"></td>
<td :class="row['PPCI&N']===row['Long Name']?[]:['active']" x-show="appData.isCompareResult" x-text="row['Long Name']"></td>
<td x-text="row['Abrev. Net. Name']"></td>
<td :class="row['Abrev. Net. Name']=== row['Short Nam']?[]:['active']" x-show="appData.isCompareResult" x-text="row['Short Name']"></td>
<td x-text="row['Net. Type']"></td>
<td x-show="!appData.isCompareResult" x-text="row['SIM']"></td>
<td x-show="!appData.isCompareResult" x-text="row['Last Update']"></td>
</tr>
</template>
已上线
- w.jascript.cn
核心代码
/**
* 加载数据库文件事件
* @param {File} file
* @param {AppData} appData
*/
function _loadDbFileChange(file,appData){
//读取excel文件更新到数据库
appData.statusData = "加载数据库文件"
loadFileProcess(file).then(function(result){
let workbook = XLSX.read(result, {
type: 'binary',
//日期需要处理
cellDates: true,
cellText: false
});
let excelJson = readWorkbookToJson(workbook)
console.log(excelJson)
if(checkDataFromExcelJson(excelJson)){
appData.dbData.rows = excelJson
appData.dbData.isReady = true
localStorage.setItem("yunFileDbData",JSON.stringify(appData.dbData))
appData.loadingShow = false
appData.isCompareResult = false,
appData.tableTitle = "数据库内容(仅显示前20行)",
//仅加载数据库时,表数据为数据库数据的前20行
appData.tableData.rows = appData.dbData.rows.slice(0,20)
appData.statusData = "数据库加载完毕共" + excelJson.length + "行"
}else{
appData.statusIsDanger= true
appData.statusData = "数据库加载失败,可能是无效数据库文件"
}
})
}
/**
* 对比新文件按包文件更新事件,返回
* @param {File} file
* @param {AppData} appData
*/
function _loadComparFileChange(file,appData){
//读取文件
/** @type {Array<any>} */
let excelJson = []
loadFileProcess(file).then(function(result){
let workbook = XLSX.read(result, {
type: 'binary',
cellText: true
});
excelJson = readWorkbookToJson(workbook)
if(!checkDataFromExcelJson(excelJson)){
appData.statusIsDanger= true
appData.statusData = "非正常可对比文件"
return;
}
//遍历和数据库对比
appData.statusIsDanger= false
appData.statusData = "正在进行文件对比"
let comparResult = []
// if recoderPPCIN != result_dict['PPCIN'] or recoderAbreNetName != result_dict['AbrevNetName']
/** @type {Array<any>} 没有用过的数据库数据集合*/
let unusedData = [...appData.dbData.rows]
/** @type {Array<any>} - 已经用过的数据库数据集合,备用如果对比数据有重复的时候,先遍历unused再遍历used */
let usedData = []
for(let comparData of excelJson){
//由于重复的数据都在一起直接用最后一次使用的数据进行判断,重复行无需重新对比
if(usedData.length>0){
let lastUsedData = usedData[usedData.length-1]
if(comparData["MCC"] === lastUsedData["MCC"] && comparData["MNC"] === lastUsedData["MNC"]){
continue;
}
}
//遍历查找
for (let i = 0; i < unusedData.length; i++) {
let dbData = unusedData[i]
if(comparData["MCC"] === dbData["MCC"] && comparData["MNC"] === dbData["MNC"]){
let dbData = unusedData[i]
//对比数据
if(comparData["Long Name"] !== dbData["PPCI&N"] || comparData["Short Name"] !== dbData["Abrev. Net. Name"]){
let result = { ...dbData };
result["Long Name"] = comparData["Long Name"]
result["Short Name"] = comparData["Short Name"]
console.log(comparData["MCC"] + "-" + comparData["MNC"] + "对比有异常")
//去掉 Country Initials,TADIG code,SIM
delete result['Last Update']
delete result['Country Initials']
delete result['TADIG code']
delete result['SIM']
comparResult.push(result)
}else{
console.log(comparData["MCC"] + "-" + comparData["MNC"] + "对比无异常")
}
//删除匹配的元素,存入usedData
unusedData.splice(i, 1)
i--
usedData.push(dbData)
break
}
}
}
console.log(comparResult)
appData.tableTitle = "对比结果"
appData.isCompareResult = true
appData.comparFileName = file.name;
appData.tableData.rows = comparResult
appData.statusData = "对比完成"
})
}
/**
*
* @returns {AppData}
*/
function appDataInit(){
let statusData = "等待加载数据库"
let data = localStorage.getItem("yunFileDbData")
let dbData = new YunfileData(false,[])
if(data !== null){
dbData = JSON.parse(data)
statusData = "已从缓存中加载了数据库"
}
let loadingShow = true
let tableData = new YunfileData(false,[])
if(dbData.isReady){
tableData.rows = dbData.rows.slice(0,20)
loadingShow =false;
}
return {
dbData:dbData,
tableData:tableData,
tableTitle: "数据库内容(仅显示前20行)",
statusData: statusData,
statusIsDanger: false,
loadingShow:loadingShow,
isCompareResult:false,
//对比文件名,用于下载时生成文件名
comparFileName:"",
}
}