共计 2225 个字符,预计需要花费 6 分钟才能阅读完成。
最近遇到一个需要:解析 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)
});
正文完