Part1插件介紹

excel-2b-json 插件用于將 google excel 文件轉(zhuǎn)化成 本地json文件。

適用場景： 項目國際化，配置多語言

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)化得到

下面是插件的實現(xiàn)

源碼已放到github：https://github.com/Sunny-lucking/HowToBuildMyExcelTobeJson

Part3一、涉及的算法

31. 26字母轉(zhuǎn)換成數(shù)字，26進制，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]
]

算法實現(xiàn)

  /**
   *
   * @param {array*2} matrix 一個二維數(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二、插件的實現(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文檔的步驟非常簡單，只要獲取原始的鏈接，然后拼接成下面的url，向這個Url發(fā)起請求，然后以流的方式寫入生成文件就可以了。

https://docs.google.com/spreadsheets/d/ + "文檔ID" + '/export?format=xlsx&id=' + id + '&' + hash

因此實現(xià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) {

  // 記錄當前下載文件的目錄，方便刪除
  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對象來完成
    })
  })

}

module.exports = downLoadExcel

入口文件可以這樣寫

async function excelToJson(excelPathName, outputPath) {
  if (Util.checkAddress(excelPathName) === 'google') {
    // 1.判斷是谷歌excel文檔，需要交給Google對象去處理，主要是下載線上的，生成本地excel文件
    const filePath = await downLoadExcel(excelPathName)

    // 2.解析本地excel成二維數(shù)組
    const data = await parseXlsx(filePath)
    
    // 3.生成json文件
    generateJsonFile(data, outputPath)
  }

}
module.exports = excelToJson

之所以寫if判斷，是為了后面擴展，也許就不止是解析google文檔了，或許也要解析騰訊等其他文檔呢

第一步已經(jīng)實現(xiàn)了，接下來就看第二步怎么實現(xiàn)

82. 解析本地excel成二維數(shù)組

解析本地excel文件，獲取excel的sheet信息和strings信息

excel 文件其實本質(zhì)上是多份xml文件的壓縮文件。

xml是存儲數(shù)據(jù)的，而html是顯示數(shù)據(jù)的

而在這里我們只需要獲取兩份xml 文件，一份是strings，就是excel里的內(nèi)容，一份是sheet，概括整個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文檔，會被解析成好多分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 => {
       
        // 每解析某個xml文件都會進來這里，但是我們只需要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()
        }
      })
  })
}

可以斷點看看entry.path，你就會看到分別進來了好幾次，然后我們會分別看到我們想要的那兩個文件

兩份xml文件解析之后就會到close方法里了，這時就可以看到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)容非常簡單，現(xiàn)在我們借助xmldom將內(nèi)容解析為節(jié)點對象，然后用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
  )

  // 把所有每個格子的內(nèi)容都放進了values數(shù)組里。
  values = select('//a:si', valuesDoc).map(string =>

    select('.//a:t', string)
      .map(t => t.textContent)
      .join('')
  )

'//a:si' 是xpath語法，//表示選擇當前節(jié)點下的所有子孫節(jié)點，a是http://schemas.openxmlformats.org/spreadsheetml/2006/main的命名空間。所以合起來就是找到當前節(jié)點下的所有si節(jié)點。.//a:t則是找到當前si節(jié)點下的所有t節(jié)點。

可以看到，xpath的用法很簡單，就是找到si節(jié)點下的子節(jié)點t的內(nèi)容，然后放進數(shù)組里

最終生成的values數(shù)組是[ 'lang', 'cn','en', 'lang001','我是陽光', 'i am sunny','lang002', '前端陽光','FE Sunny', 'lang003','帶帶我', 'ddw']

現(xiàn)在我們要獲取sheet的內(nèi)容了，我們先分析一下xml結構

可以看到sheetData節(jié)點其實就是記錄strings的內(nèi)容的信息的，strings的內(nèi)容是我們真正輸入的，而sheet則是類似一種批注。

我們分析看看

row就是表示表格中的行，c則表示的是列，屬性t="s"表示的是當前這個格子有內(nèi)容，r="A1"表示的是在第一行中的A列

。

而節(jié)點v則表示該格子是該表格的第幾個有值的格子，不信？我們可以試試看

可以看到這打印出來的xml內(nèi)容，strings中已經(jīng)沒有了那兩個值，而sheet中的那兩個格子的c節(jié)點的t屬性沒了，而且v節(jié)點也沒有了。

現(xiàn)在我們可以知道，string只保存有值的格子里的值，而sheet則是一個網(wǎng)格，不管格子有沒有值都會記錄，有值的會有個序號存在v節(jié)點中。

現(xiàn)在就要收集c節(jié)點

  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)
  )

每個c節(jié)點用cell對象來表示

可以看到cell節(jié)點有四個屬性。

你現(xiàn)在知道它為什么要保存順序了嗎？

因為這樣才可以直接從strings生成的values數(shù)組中拿出對應順序的值填充到網(wǎng)格中。

接下來要獲取總共有多少列數(shù)和行數(shù)。這就需要獲取最大最小行數(shù)列數(shù)，然后求差得到

// 計算該表格的最大最小列數(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)容

  // 計算該表格的最大最小列數(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的完整實現(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
    )

    // 把所有每個格子的內(nèi)容都放進了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)
  )

  // 計算該表格的最大最小列數(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ù)組的第一項子數(shù)組則是key列表了。

接下來就可以根據(jù)key來生成對應的json文件了。

93. 生成json數(shù)據(jù)

這一步非常簡單

function generateJsonFile(excelDatas, outputPath) {
  
  // 獲得轉(zhuǎn)化成json格式
  const jsons = convertProcess(excelDatas)

  // 生成寫入文件
  writeFile(jsons, outputPath)
}

首先就是獲取json數(shù)據(jù)

先獲取data數(shù)組的第一項數(shù)組，第一項數(shù)組是key，然后生成每種語言的json對象

  /**
   *
   * @param {array*2} data
   * 返回處理完后的多語言數(shù)組，每一項都是一個json對象。
   */
  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ù)是語言包
    data_arr = data.slice(1)

    for (i = 0, data_arr_len = data_arr.length; i < data_arr_len; i++) {
      // 取出第一個列語言包
      col_data_arr = data_arr[i]
      // 該列對應的臨時對象
      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)成功生成每一種語言的json對象了。

接下來只需要生成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();
  }

到此，一個稍微完美的插件就此完成了。 撒花撒花?。。?！