xlsx-populate 花式解析excel

最近遇到一个需求：解析 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('./myFile.xlsx')
  .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)
  });