A better excel library for Node.js
发布于 5 年前 作者 helloyou2012 5036 次浏览 来自 分享

目前 Node.js 里关于 Excel 处理的库感觉都比较难用,于是打算撸一个更好用的,参考了 go 语言写的 xlsx,第一个版本已经发布,后面还有很多功能要加,在此邀请有志同道合的小伙伴一起帮忙开发~~~~

项目地址:https://github.com/d-band/better-xlsx

功能列表:

  • [x] 生成 xlsx 文件
  • [ ] 解析读取 xlsx 文件
  • [x] html table 转 xlsx html2xlsx
  • [ ] xlsx 读取解析为 virtual-dom,然后 virtual-dom 生成 html table

PS: 如果大家还有需求也可以提呀~~~~~~

附一个简单的 Demo :

const fs = require('fs');
const xlsx = require('better-xlsx');

const file = new xlsx.File();

const sheet = file.addSheet('Sheet1');
const row = sheet.addRow();
const cell = row.addCell();

cell.value = 'I am a cell!';
cell.hMerge = 2;
cell.vMerge = 1;

const style = new xlsx.Style();

style.fill.patternType = 'solid';
style.fill.fgColor = '00FF0000';
style.fill.bgColor = 'FF000000';
style.align.h = 'center';
style.align.v = 'center';

cell.style = style;

file
  .saveAs()
  .pipe(fs.createWriteStream(__dirname + '/simple.xlsx'))
  .on('finish', () => console.log('Done.'));

一个复杂点的 Demo:

const fs = require('fs');
const xlsx = require('better-xlsx');

const file = new xlsx.File();
const sheet = file.addSheet('Sheet1');
const data = [
  ['Auto', 200, 90, 'B2-C2'],
  ['Entertainment', 200, 32, 'B3-C3'],
  ['Food', 350, 205.75, 'B4-C4'],
  ['Home', 300, 250, 'B5-C5'],
  ['Medical', 100, 35, 'B6-C6'],
  ['Personal Items', 300, 80, 'B7-C7'],
  ['Travel', 500, 350, 'B8-C8'],
  ['Utilities', 200, 100, 'B9-C9'],
  ['Other', 50, 60, 'B10-C10']
];

function border(cell, top, left, bottom, right) {
  const light = 'ffded9d4';
  const dark = 'ff7e6a54';
  cell.style.border.top = 'thin';
  cell.style.border.topColor = top ? dark : light;
  cell.style.border.left = 'thin';
  cell.style.border.leftColor = left ? dark : light;
  cell.style.border.bottom = 'thin';
  cell.style.border.bottomColor = bottom ? dark : light;
  cell.style.border.right = 'thin';
  cell.style.border.rightColor = right ? dark : light;
}

function fill(cell, type) {
  type = type || 0;
  const colors = ['ffffffff', 'ffa2917d', 'ffe4e2de', 'fffff8df', 'fff1eeec'];
  // 1: header, 2: first col, 3: second col, 4: gray, 0: white
  cell.style.fill.patternType = 'solid';
  cell.style.fill.fgColor = colors[type];
  cell.style.fill.bgColor = 'ffffffff';
}

const header = sheet.addRow();
header.setHeightCM(0.8);
const headers = ['Category', 'Budget', 'Actual', 'Difference'];
for (let i = 0; i < headers.length; i++) {
  const hc = header.addCell();
  hc.value = headers[i];
  hc.style.align.v = 'center';
  if (i > 0) hc.style.align.h = 'right';
  hc.style.font.color = 'ffffffff';
  border(hc, 0, 0, 1, 0);
  fill(hc, 1);
}

const len = data.length;
for (let i = 0; i < len; i++) {
  const line = data[i];
  const row = sheet.addRow();
  row.setHeightCM(0.8);
  // Col 1
  const cell1 = row.addCell();
  cell1.value = line[0];
  cell1.style.align.v = 'center';
  if (i === 0) {
    border(cell1, 1, 0, 0, 1);
  } else if (i === len - 1) {
    border(cell1, 0, 0, 1, 1);
  } else {
    border(cell1, 0, 0, 0, 1);
  }
  fill(cell1, 2);
  // Col 2
  const cell2 = row.addCell();
  cell2.value = line[1];
  cell2.numFmt = '$#,##0.00';
  cell2.cellType = 'TypeNumeric';
  cell2.style.align.v = 'center';
  if (i === 0) {
    border(cell2, 1, 1, 0, 0);
  } else if (i === len - 1) {
    border(cell2, 0, 1, 1, 0);
  } else {
    border(cell2, 0, 1, 0, 0);
  }
  fill(cell2, 3);
  // Col 3
  const cell3 = row.addCell();
  cell3.value = line[2];
  cell3.numFmt = '$#,##0.00';
  cell3.cellType = 'TypeNumeric';
  cell3.style.align.v = 'center';
  if (i === 0) {
    border(cell3, 1, 0, 0, 0);
  } else if (i === len - 1) {
    border(cell3, 0, 0, 1, 0);
  } else {
    border(cell3, 0, 0, 0, 0);
  }
  fill(cell3, i % 2 === 0 ? 0 : 4);
  // Col 4
  const cell4 = row.addCell();
  cell4.formula = line[3];
  cell4.numFmt = '$#,##0.00';
  cell4.cellType = 'TypeFormula';
  cell4.style.align.v = 'center';
  if (i === 0) {
    border(cell4, 1, 0, 0, 0);
  } else if (i === len - 1) {
    border(cell4, 0, 0, 1, 0);
  } else {
    border(cell4, 0, 0, 0, 0);
  }
  fill(cell4, i % 2 === 0 ? 0 : 4);
}

for (let i = 0; i < 4; i++) {
  sheet.col(i).width = 20;
}

file
  .saveAs()
  .pipe(fs.createWriteStream(__dirname + '/complex.xlsx'))
  .on('finish', () => console.log('Done.'));

Screen Shot 2016-11-24 at 5.41.46 PM.png

12 回复

@yuu2lee4 目前只支持生成,还有很多功能要加~~~~

感觉可以加入一起来构建

看到别人做什么,我就看看……然后点个赞

html table 转 excel 文件已完成第一版:https://github.com/d-band/html2xlsx

const fs = require('fs');
const htmlToXlsx = require('html2xlsx');

htmlToXlsx(`
  <style type="text/css">
    table td {
      color: #666;
      height: 20px;
      background-color: #f1f1f1;
      border: 1px solid #eee;
    }
  </style>
  <table>
    <tr>
      <td>foo</td>
      <td>bar</td>
    </tr>
    <tr>
      <td>hello</td>
      <td>world</td>
    </tr>
  </table>
`, (err, file) => {
  if (err) return console.error(err);

  file.saveAs()
    .pipe(fs.createWriteStream('test.xlsx'))
    .on('finish', () => console.log('Done.'));
});

@helloyou2012 html table跟xlsx可以互转吗?

@coolicer 目前只支持 html 转 xlsx,解析读取 xlsx 文件正在开发中,完成后就可以加 xlsx 转 html 了~~

html2xlsx 支持自定义数据类型定义:

const fs = require('fs');
const htmlTo = require('html2xlsx');

htmlTo(`
 <style type="text/css">
   table td {
     color: #666;
     height: 20px;
     background-color: #f1f1f1;
     border: 1px solid #eee;
   }
 </style>
 <table>
   <tr>
     <td>foo</td>
     <td>bar</td>
   </tr>
   <tr>
     <td>hello</td>
     <td>world</td>
   </tr>
   <tr>
     <td type="number">123</td>
     <td type="number">123.456</td>
   </tr>
   <tr>
     <td type="bool">true</td>
     <td type="bool">false</td>
   </tr>
   <tr>
     <td type="bool">1</td>
     <td type="bool">0</td>
   </tr>
   <tr>
     <td type="formula">SUM(A1:B1)</td>
     <td type="formula">A1-B1</td>
   </tr>
   <tr>
     <td type="date">2013-01-12T12:34:56+08:00</td>
     <td type="datetime">2013-01-12T12:34:56+08:00</td>
   </tr>
 </table>
`, (err, file) => {
 if (err) return console.error(err);

 file.saveAs()
   .pipe(fs.createWriteStream('test.xlsx'))
   .on('finish', () => console.log('Done.'));
});

🐂,赞一个。

回到顶部