最近遇到一个需要：解析 excel 并获取单元格内的色彩。说起解析 excel，不得不提一下 SheetJS js-xlsx , github 上有 22k 多 Star，性能非常弱小。

惋惜，他们是有个免费版的，解析单元格背景色彩的性能并不对我等白嫖用户凋谢。

在我坚定不移的面向程（gu）序（ge）编程下，终于让我发现一个能解决需要的 node 库 xlsx-populate

装置 xlsx-populate

这个无需多言，关上终端间接输出命令就好

npm install xlsx-populate

开始应用

创立一个新的 excel

const XlsxPopulate = require('xlsx-populate');
XlsxPopulate.fromBlankAsync()
    .then(workbook => {workbook.sheet("Sheet1").cell("A1").value("This is neat!");
        return workbook.toFileAsync("./out.xlsx");
    });
  

解析 excel

const XlsxPopulate = require('xlsx-populate');
XlsxPopulate.fromFileAsync("./Book1.xlsx")
    .then(workbook => {const value = workbook.sheet("Sheet1").cell("A1").value();
        console.log(value);
    });

下面只是惯例操作，上面来点不一样的

将单元格设置为富文本

const RichText = require('xlsx-Populate').RichText;
const cell = workbook.sheet(0).cell('A1');
cell.value(new RichText());
 
cell.value()
    .add('hello', { italic: true, bold: true})
    .add('world!', { fontColor: 'FF0000'});

增加超链接

cell.value("Link Text")
    .style({fontColor: "0563c1", underline: true})
    .hyperlink("http://example.com");

获取单元格背景

const XlsxPopulate = require('xlsx-populate');
XlsxPopulate.fromFileAsync("./Book1.xlsx")
    .then(workbook => {const background = workbook.sheet("Sheet1").cell("A1").style("fill");
        console.log(background);
    });

其中 style(value) 可选的参数十分多 style 参数，根本的款式都可能获取的到

应用分享

上面是我利用 xlsx-populate 解析 excel 的代码

XlsxPopulate.fromFileAsync(newPath)
  .then(workbook => {var sheet = workbook.sheet(0)
    var rows = sheet._rows
    let resultData = {tableHead: [],
        tableData: []}
    let tableId = []
    rows.forEach(row => {
      row._cells.forEach(cell => {let col = cell.columnNumber()
        let row = cell.rowNumber()
        if(row == 1 && cell.value()){let value = cell.value()
          resultData.tableHead.push(value)
        }
        let backgroundCR = cell.columnName() + row
        let background = workbook.sheet("Sheet1").cell(backgroundCR).style("fill")
        let bg
        if(background){bg = background.color != undefined ? '#' + workbook.sheet("Sheet1").cell(backgroundCR).style("fill").color.rgb.substring(2) : null
        }else{bg = null}
        if(tableId.indexOf(row) == -1 && row != 1){
          let resultDataItem = [{
            columnNumber: col,
            rowNumber: row,
            background: bg,
            value: cell.value() || "none"}]
          resultData.tableData.push(resultDataItem)
          tableId.push(row)
        }else if(tableId.indexOf(row) != -1 && row != 1){
          let resultDataItem = {
            columnNumber: col,
            rowNumber: row,
            background: bg,
            value: cell.value() || "none"}
          resultData.tableData[row - 2].push(resultDataItem)
        }
      })
    })
  }).catch( error => {console.log(error)
  });