個(gè)人中心
退出
我寫了一個(gè)將 excel 文件轉(zhuǎn)化成 本地json文件的插件
Part1插件介紹
excel-2b-json 插件用于將 google excel 文件轉(zhuǎn)化成 本地json文件。
適用場(chǎng)景： 項(xiàng)目國(guó)際化，配置多語(yǔ)言
Part2使用方法
11. 安裝excel-2b-json
npm install excel-2b-json
22. 引入使用
const excelToJson = require('excel-2b-json');
// path 生成的json文件目錄
excelToJson('https://docs.google.com/spreadsheets/d/12q3leiNxdmI_ZLWFj4LP_EA5PeJpLF18vViuyiSOuvM/edit#gid=0', path)
轉(zhuǎn)化得到
下面是插件的實(shí)現(xiàn)
源碼已放到github：https://github.com/Sunny-lucking/HowToBuildMyExcelTobeJson
Part3一、涉及的算法
31. 26字母轉(zhuǎn)換成數(shù)字，26進(jìn)制，a為1，aa為27，ab為28
function colToInt(col) {
const letters = ['', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
col = col.trim().split('')
let n = 0
for (let i = 0; i < col.length; i++) {
n *= 26
n += letters.indexOf(col[i])
}
return n
}
42. 生成幾行幾列的二維空數(shù)組
function getEmpty2DArr(rows, cols) {
let arrs = new Array(rows);
for (var i = 0; i < arrs.length; i++) {
arrs[i] = new Array(cols).fill(''); //每行有cols列
}
return arrs;
}
53. 清除二維數(shù)組中空的數(shù)組
[
[1,2,3],
['','',''],
[7,8,9]
]
轉(zhuǎn)化為
[
[1,4,7],
[3,6,9]
]
clearEmptyArrItem(matrix) {
return matrix.filter(function (val) {
return val.some(function (val1) {
return val1.replace(/\s/g, '') !== ''
})
})
}
64. 矩陣的翻轉(zhuǎn)
[
[1,2,3],
[4,5,6],
[7,8,9]
]
轉(zhuǎn)化為
[
[1,4,7],
[2,5,8],
[3,6,9]
]
算法實(shí)現(xiàn)
/**
*
* @param {array*2} matrix 一個(gè)二維數(shù)組，返回旋轉(zhuǎn)后的二維數(shù)組。
*/
rotateExcelDate(matrix) {
if (!matrix[0]) return []
var results = [],
result = [],
i,
j,
lens,
len
for (i = 0, lens = matrix[0].length; i < lens; i++) {
result = []
for (j = 0, len = matrix.length; j < len; j++) {
result[j] = matrix[j][i]
}
results.push(result)
}
return results
}
Part4二、插件的實(shí)現(xiàn)
71. 下載google Excel文檔到本地
我們先看看google Excel文檔的url的組成
https://docs.google.com/spreadsheets/d/文檔ID/edit#哈希值
例如下面這條，你可以嘗試打開，下面這條鏈接是可以打開的。
https://docs.google.com/spreadsheets/d/12q3leiNxdmI_ZLWFj4LP_EA5PeJpLF18vViuyiSOuvM/edit#gid=0
下載google文檔的步驟非常簡(jiǎn)單，只要獲取原始的鏈接，然后拼接成下面的url，向這個(gè)Url發(fā)起請(qǐng)求，然后以流的方式寫入生成文件就可以了。
https://docs.google.com/spreadsheets/d/ + "文檔ID" + '/export?format=xlsx&id=' + id + '&' + hash
因此實(shí)現(xiàn)下載的方法非常簡(jiǎn)單，可以直接看代碼
downLoadExcel.js
const fs = require('fs')
const request = require('superagent')
const rmobj = require('./remove')
/**
* 下載google excel 文檔到本地
* @param {*} url // https://docs.google.com/spreadsheets/d/12q3leiNxdmI_ZLWFj4LP_EA5PeJpLF18vViuyiSOuvM/edit#gid=0
* @returns
*/
function downLoadExcel(url) {
// 記錄當(dāng)前下載文件的目錄，方便刪除
rmobj.push({
path: __dirname,
ext: 'xlsx'
})
return new Promise((resolve, reject) => {
var down1 = url.split('/')
var down2 = down1.pop() // edit#gid=0
var url2 = down1.join('/') // https://docs.google.com/spreadsheets/d/12q3leiNxdmI_ZLWFj4LP_EA5PeJpLF18vViuyiSOuvM
var id = down1.pop() // 12q3leiNxdmI_ZLWFj4LP_EA5PeJpLF18vViuyiSOuvM
var hash = down2.split('#').pop() // gid=0
var downurl = url2 + '/export?format=xlsx&id=' + id + '&' + hash // https://docs.google.com/spreadsheets/d/12q3leiNxdmI_ZLWFj4LP_EA5PeJpLF18vViuyiSOuvM/export?format=xlsx&id=12q3leiNxdmI_ZLWFj4LP_EA5PeJpLF18vViuyiSOuvM&gid=0
var loadedpath = __dirname + '/' + id + '.xlsx'
const stream = fs.createWriteStream(loadedpath)
const req = request.get(downurl)
req.pipe(stream).on('finish', function () {
resolve(loadedpath)
// 已經(jīng)成功下載下來了，接下來將本地excel轉(zhuǎn)化成json的工作就交給Excel對(duì)象來完成
})
})
}
module.exports = downLoadExcel
入口文件可以這樣寫
async function excelToJson(excelPathName, outputPath) {
if (Util.checkAddress(excelPathName) === 'google') {
// 1.判斷是谷歌excel文檔，需要交給Google對(duì)象去處理，主要是下載線上的，生成本地excel文件
const filePath = await downLoadExcel(excelPathName)
// 2.解析本地excel成二維數(shù)組
const data = await parseXlsx(filePath)
// 3.生成json文件
generateJsonFile(data, outputPath)
}
}
module.exports = excelToJson
之所以寫if判斷，是為了后面擴(kuò)展，也許就不止是解析google文檔了，或許也要解析騰訊等其他文檔呢
第一步已經(jīng)實(shí)現(xiàn)了，接下來就看第二步怎么實(shí)現(xiàn)
82. 解析本地excel成二維數(shù)組
解析本地excel文件，獲取excel的sheet信息和strings信息
excel 文件其實(shí)本質(zhì)上是多份xml文件的壓縮文件。
xml是存儲(chǔ)數(shù)據(jù)的，而html是顯示數(shù)據(jù)的
而在這里我們只需要獲取兩份xml 文件，一份是strings，就是excel里的內(nèi)容，一份是sheet，概括整個(gè)excel文件的信息。
async function parseXlsx(path) {
// 1. 解析本地excel文件，獲取excel的sheet信息和content信息
const files = await extractFiles(path);
// 2. 根據(jù)strings和sheet解析成二維數(shù)組
const data = await extractData(files)
// 3. 處理二維數(shù)組的內(nèi)容，
const fixData = handleData(data)
return fixData;
}
所以第一步我們看看怎么獲取excel的sheet信息和strings信息
function extractFiles(path) {
// excel的本質(zhì)是多份xml組成的壓縮文件，這里我們只需要xl/sharedStrings.xml和xl/worksheets/sheet1.xml
const files = {
strings: {}, // strings內(nèi)容
sheet: {},
'xl/sharedStrings.xml': 'strings',
'xl/worksheets/sheet1.xml': 'sheet'
}
const stream = path instanceof Stream ? path : fs.createReadStream(path)
return new Promise((resolve, reject) => {
const filePromises = [] // 由于一份excel文檔，會(huì)被解析成好多分xml文檔，但是我們只需要兩份xml文檔，分別是（xl/sharedStrings.xml和xl/worksheets/sheet1.xml），所以用數(shù)組接受
stream
.pipe(unzip.Parse())
.on('error', reject)
.on('close', () => {
Promise.all(filePromises).then(() => {
return resolve(files)
})
})
.on('entry', entry => {
// 每解析某個(gè)xml文件都會(huì)進(jìn)來這里，但是我們只需要xl/sharedStrings.xml和xl/worksheets/sheet1.xml，并將內(nèi)容保存在strings和sheet中
const file = files[entry.path]
if (file) {
let contents = ''
let chunks = []
let totalLength = 0
filePromises.push(
new Promise(resolve => {
entry
.on('data', chunk => {
chunks.push(chunk)
totalLength += chunk.length
})
.on('end', () => {
contents = Buffer.concat(chunks, totalLength).toString()
files[file].contents = contents
if (/?/g.test(contents)) {
throw TypeError('本次轉(zhuǎn)化出現(xiàn)亂碼?')
} else {
resolve()
}
})
})
)
} else {
entry.autodrain()
}
})
})
}
可以斷點(diǎn)看看entry.path，你就會(huì)看到分別進(jìn)來了好幾次，然后我們會(huì)分別看到我們想要的那兩個(gè)文件
兩份xml文件解析之后就會(huì)到close方法里了，這時(shí)就可以看到strings和sheet都有內(nèi)容了，而且內(nèi)容都是xml
我們分別看看strings和sheet的內(nèi)容
stream
.pipe(unzip.Parse())
.on('error', reject)
.on('close', () => {
Promise.all(filePromises).then(() => {
console.log(files.strings.contents);
console.log(files.sheet.contents);
return resolve(files)
})
})
格式化一下
strings
sheet
可以發(fā)現(xiàn)strings的內(nèi)容非常簡(jiǎn)單，現(xiàn)在我們借助xmldom將內(nèi)容解析為節(jié)點(diǎn)對(duì)象，然后用xpath插件來獲取內(nèi)容
xpath的用法：https://github.com/goto100/xpath#readme
const XMLDOM = require('xmldom')
const xpath = require('xpath')
const ns = { a: 'http://schemas.openxmlformats.org/spreadsheetml/2006/main' }
const select = xpath.useNamespaces(ns)
const valuesDoc = new XMLDOM.DOMParser().parseFromString(
files.strings.contents
)
// 把所有每個(gè)格子的內(nèi)容都放進(jìn)了values數(shù)組里。
values = select('//a:si', valuesDoc).map(string =>
select('.//a:t', string)
.map(t => t.textContent)
.join('')
)
'//a:si' 是xpath語(yǔ)法，//表示選擇當(dāng)前節(jié)點(diǎn)下的所有子孫節(jié)點(diǎn)，a是http://schemas.openxmlformats.org/spreadsheetml/2006/main的命名空間。所以合起來就是找到當(dāng)前節(jié)點(diǎn)下的所有si節(jié)點(diǎn)。.//a:t則是找到當(dāng)前si節(jié)點(diǎn)下的所有t節(jié)點(diǎn)。
可以看到，xpath的用法很簡(jiǎn)單，就是找到si節(jié)點(diǎn)下的子節(jié)點(diǎn)t的內(nèi)容，然后放進(jìn)數(shù)組里
最終生成的values數(shù)組是[ 'lang', 'cn','en', 'lang001','我是陽(yáng)光', 'i am sunny','lang002', '前端陽(yáng)光','FE Sunny', 'lang003','帶帶我', 'ddw']
現(xiàn)在我們要獲取sheet的內(nèi)容了，我們先分析一下xml結(jié)構(gòu)
可以看到sheetData節(jié)點(diǎn)其實(shí)就是記錄strings的內(nèi)容的信息的，strings的內(nèi)容是我們真正輸入的，而sheet則是類似一種批注。
我們分析看看
row就是表示表格中的行，c則表示的是列，屬性t="s"表示的是當(dāng)前這個(gè)格子有內(nèi)容，r="A1"表示的是在第一行中的A列
而節(jié)點(diǎn)v則表示該格子是該表格的第幾個(gè)有值的格子，不信？我們可以試試看
可以看到這打印出來的xml內(nèi)容，strings中已經(jīng)沒有了那兩個(gè)值，而sheet中的那兩個(gè)格子的c節(jié)點(diǎn)的t屬性沒了，而且v節(jié)點(diǎn)也沒有了。
現(xiàn)在我們可以知道，string只保存有值的格子里的值，而sheet則是一個(gè)網(wǎng)格，不管格子有沒有值都會(huì)記錄，有值的會(huì)有個(gè)序號(hào)存在v節(jié)點(diǎn)中。
現(xiàn)在就要收集c節(jié)點(diǎn)
const na = {
textContent: ''
}
class CellCoords {
constructor(cell) {
cell = cell.split(/([0-9]+)/)
this.row = parseInt(cell[1])
this.column = colToInt(cell[0])
}
}
class Cell {
constructor(cellNode) {
const r = cellNode.getAttribute('r')
const type = cellNode.getAttribute('t') || ''
const value = (select('a:v', cellNode, 1) || na).textContent
const coords = new CellCoords(r)
this.column = coords.column // 該格子所在列數(shù)
this.row = coords.row // 該格子所在行數(shù)
this.value = value // 該格子的順序
this.type = type // 該格子是否為空
}
}
const cells = select('/a:worksheet/a:sheetData/a:row/a:c', sheet).map(
node => new Cell(node)
)
每個(gè)c節(jié)點(diǎn)用cell對(duì)象來表示
可以看到cell節(jié)點(diǎn)有四個(gè)屬性。
你現(xiàn)在知道它為什么要保存順序了嗎？
因?yàn)檫@樣才可以直接從strings生成的values數(shù)組中拿出對(duì)應(yīng)順序的值填充到網(wǎng)格中。
接下來要獲取總共有多少列數(shù)和行數(shù)。這就需要獲取最大最小行數(shù)列數(shù)，然后求差得到
// 計(jì)算該表格的最大最小列數(shù)行數(shù)
d = calculateDimensions(cells)
const cols = d[1].column - d[0].column + 1
const rows = d[1].row - d[0].row + 1
function calculateDimensions(cells) {
const comparator = (a, b) => a - b
const allRows = cells.map(cell => cell.row).sort(comparator)
const allCols = cells.map(cell => cell.column).sort(comparator)
const minRow = allRows[0]
const maxRow = allRows[allRows.length - 1]
const minCol = allCols[0]
const maxCol = allCols[allCols.length - 1]
return [{ row: minRow, column: minCol }, { row: maxRow, column: maxCol }]
}
接下來就根據(jù)列數(shù)和行數(shù)造空二維數(shù)組，然后再根據(jù)cells和values填充內(nèi)容
// 計(jì)算該表格的最大最小列數(shù)行數(shù)
d = calculateDimensions(cells)
const cols = d[1].column - d[0].column + 1
const rows = d[1].row - d[0].row + 1
// 生成二維空數(shù)組
data = getEmpty2DArr(rows, cols)
// 填充二維空數(shù)組
for (const cell of cells) {
let value = cell.value
// s表示該格子有內(nèi)容
if (cell.type == 's') {
value = values[parseInt(value)]
}
// 填充該格子
if (data[cell.row - d[0].row]) {
data[cell.row - d[0].row][cell.column - d[0].column] = value
}
}
return data
我們看看最終生成的data，可以發(fā)現(xiàn)，excel的網(wǎng)格已經(jīng)被二維數(shù)組模擬出來了
所以我們看看extractData的完整實(shí)現(xiàn)
function extractData(files) {
let sheet
let values
let data = []
try {
sheet = new XMLDOM.DOMParser().parseFromString(files.sheet.contents)
const valuesDoc = new XMLDOM.DOMParser().parseFromString(
files.strings.contents
)
// 把所有每個(gè)格子的內(nèi)容都放進(jìn)了values數(shù)組里。
values = select('//a:si', valuesDoc).map(string =>
select('.//a:t', string)
.map(t => t.textContent)
.join('')
)
console.log(values);
} catch (parseError) {
return []
}
const na = {
textContent: ''
}
class CellCoords {
constructor(cell) {
cell = cell.split(/([0-9]+)/)
this.row = parseInt(cell[1])
this.column = colToInt(cell[0])
}
}
class Cell {
constructor(cellNode) {
const r = cellNode.getAttribute('r')
const type = cellNode.getAttribute('t') || ''
const value = (select('a:v', cellNode, 1) || na).textContent
const coords = new CellCoords(r)
this.column = coords.column // 該格子所在列數(shù)
this.row = coords.row // 該格子所在行數(shù)
this.value = value // 該格子的順序
this.type = type // 該格子是否為空
}
}
const cells = select('/a:worksheet/a:sheetData/a:row/a:c', sheet).map(
node => new Cell(node)
)
// 計(jì)算該表格的最大最小列數(shù)行數(shù)
d = calculateDimensions(cells)
const cols = d[1].column - d[0].column + 1
const rows = d[1].row - d[0].row + 1
// 生成二維空數(shù)組
data = getEmpty2DArr(rows, cols)
// 填充二維空數(shù)組
for (const cell of cells) {
let value = cell.value
// s表示該格子有內(nèi)容
if (cell.type == 's') {
value = values[parseInt(value)]
}
// 填充該格子
if (data[cell.row - d[0].row]) {
data[cell.row - d[0].row][cell.column - d[0].column] = value
}
}
return data
}
接下來就是要去除空行和空列，并將二維數(shù)組翻轉(zhuǎn)成我們需要的格式
function handleData(data) {
if (data) {
data = clearEmptyArrItem(data)
data = rotateExcelDate(data)
data = clearEmptyArrItem(data)
}
return data
}
可以看到，現(xiàn)在數(shù)組的第一項(xiàng)子數(shù)組則是key列表了。
接下來就可以根據(jù)key來生成對(duì)應(yīng)的json文件了。
93. 生成json數(shù)據(jù)
這一步非常簡(jiǎn)單
function generateJsonFile(excelDatas, outputPath) {
// 獲得轉(zhuǎn)化成json格式
const jsons = convertProcess(excelDatas)
// 生成寫入文件
writeFile(jsons, outputPath)
}
首先就是獲取json數(shù)據(jù)
先獲取data數(shù)組的第一項(xiàng)數(shù)組，第一項(xiàng)數(shù)組是key，然后生成每種語(yǔ)言的json對(duì)象
/**
*
* @param {array*2} data
* 返回處理完后的多語(yǔ)言數(shù)組，每一項(xiàng)都是一個(gè)json對(duì)象。
*/
function convertProcess(data) {
var keys_arr = [],
data_arr = [],
result_arr = [],
i,
j,
data_arr_len,
col_data_json,
col_data_arr,
data_arr_col_len
// 表格合并處理，這是json屬性列。
keys_arr = data[0]
// 第一例是json描述，后續(xù)是語(yǔ)言包
data_arr = data.slice(1)
for (i = 0, data_arr_len = data_arr.length; i < data_arr_len; i++) {
// 取出第一個(gè)列語(yǔ)言包
col_data_arr = data_arr[i]
// 該列對(duì)應(yīng)的臨時(shí)對(duì)象
col_data_json = {}
for (
j = 0, data_arr_col_len = col_data_arr.length;
j < data_arr_col_len;
j++
) {
col_data_json[keys_arr[j]] = col_data_arr[j]
}
result_arr.push(col_data_json)
}
return result_arr
}
我們可以看看生成的result_arr
可見已經(jīng)成功生成每一種語(yǔ)言的json對(duì)象了。
接下來只需要生成json文件就可以了，注意把之前生成的excel文件刪除
//得到的數(shù)據(jù)寫入文件
function writeFile(datas, outputPath) {
for (let i = 0, len = datas.length; i < len; i++) {
fs.writeFileSync(outputPath +
(datas[i].filename || datas[i].lang) +
'.json',
JSON.stringify(datas[i], null, 4)
)
}
rmobj.flush();
}
到此，一個(gè)稍微完美的插件就此完成了。 撒花撒花！?。?！
作者：事業(yè)有成的張啦啦
歡迎關(guān)注微信公眾號(hào) ：前端陽(yáng)光
分類導(dǎo)航