最近遇到一个需要：解析 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)
  });