exceljs 笔记

引用

npm install exceljs 

创建表格 sheet

var Excel = require('exceljs'); var workbook = new Excel.Workbook(); var sheet = workbook.addWorksheet('My Sheet'); // Remove the worksheet using worksheet id workbook.removeWorksheet(sheet.id) // Iterate over all sheets // Note: workbook.worksheets.forEach will still work but this is better workbook.eachSheet(function(worksheet, sheetId) { // ... }); // fetch sheet by name var worksheet = workbook.getWorksheet('My Sheet'); // fetch sheet by id var worksheet = workbook.getWorksheet(1); 

sheet的id就是 1,2,3，分别对应第几个sheet；

加表头

worksheet.columns = [ { header: 'Id', key: 'id', width: 10 }, { header: 'Name', key: 'name', width: 32 }, { header: 'D.O.B.', key: 'DOB', width: 10 } ]; 

按列加内容

6是第6列，起始是1

worksheet.getColumn(6).values = [1,2,3,4,5]; 

加row

//通过key加 worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)}); //通过数组添加 worksheet.addRow([3, 'Sam', new Date()]); //多行添加 var rows = [ [5,'Bob',new Date()], // row by array {id:6, name: 'Barbara', dob: new Date()} ]; worksheet.addRows(rows); 

获取row值

// Get a row object. If it doesn't already exist, a new empty one will be returned var row = worksheet.getRow(5); // Get the last editable row in a worksheet (or undefined if there are none) var row = worksheet.lastRow; // Set a specific row height row.height = 42.5; // make row hidden row.hidden = true; worksheet.getRow(1).values //返回第一行的数组 

获取cell

//承接上面的row row.getCell(1).value = 5; // A5's value set to 5 row.getCell('name').value = 'Zeb'; // B5's value set to 'Zeb' - assuming column 2 is still keyed by name row.getCell('C').value = new Date(); // C5's value set to now var cell = worksheet.getCell('C3'); // Modify/Add individual cell cell.value = new Date(1968, 5, 1); 

eachRow

// Iterate over all rows that have values in a worksheet worksheet.eachRow(function(row, rowNumber) { console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values)); }); // Iterate over all rows (including empty rows) in a worksheet worksheet.eachRow({ includeEmpty: true }, function(row, rowNumber) { console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values)); }); 

eachCell

// Iterate over all non-null cells in a row row.eachCell(function(cell, colNumber) { console.log('Cell ' + colNumber + ' = ' + cell.value); }); // Iterate over all cells in a row (including empty cells) row.eachCell({ includeEmpty: true }, function(cell, colNumber) { console.log('Cell ' + colNumber + ' = ' + cell.value); }); 

row 其他

// Cut one or more rows (rows below are shifted up) // Known Issue: If a splice causes any merged cells to move, the results may be unpredictable //删掉第4行开始往下共三行（4,5,6），其他行上移 worksheet.spliceRows(4,3); // remove one row and insert two more. // Note: rows 4 and below will be shifted down by 1 row. //删掉第三行，在加两行，其他行下移 var newRow3Values = [1,2,3,4,5]; var newRow4Values = ['one', 'two', 'three', 'four', 'five']; worksheet.spliceRows(3, 1, newRow3Values, newRow4Values); // Cut one or more cells (cells to the right are shifted left) // Note: this operation will not affect other rows //删掉row里面第3，4个cell row.splice(3,2); // remove one cell and insert two more (cells to the right of the cut cell will be shifted right) //删掉row里面第4个cell ，在加俩cell row.splice(4,1,'new value 1', 'new value 2'); // Commit a completed row to stream row.commit(); // row metrics //获取一行有几个cell //去掉空的，真实有值的。 var rowSize = row.cellCount; var numValues = row.actualCellCount; 

Merged Cells

// merge a range of cells worksheet.mergeCells('A4:B5'); // ... merged cells are linked worksheet.getCell('B5').value = 'Hello, World 

输出表格

//Writing XLSX workbook.xlsx.writeFile('filename.xlsx') .then(function() { // done console.log('done') }); 

STYLE

//改字体 ws.getRow(2).font = { name: 'Comic Sans MS', family: 4, size: 16, underline: 'double', bold: true }; ws.getRow(1).font = { name: "Segoe UI", size: 10, color: { argb: 'FFFFFFFF' }, family: 1 } 

align

 // set cell alignment to top-left, middle-center, bottom-right ws.getCell('A1').alignment = { vertical: 'top', horizontal: 'left' }; ws.getCell('B1').alignment = { vertical: 'middle', horizontal: 'center' }; ws.getCell('C1').alignment = { vertical: 'bottom', horizontal: 'right' }; // set cell to wrap-text ws.getCell('D1').alignment = { wrapText: true }; // set cell indent to 1 ws.getCell('E1').alignment = { indent: 1 }; // set cell text rotation to 30deg upwards, 45deg downwards and vertical text ws.getCell('F1').alignment = { textRotation: 30 }; ws.getCell('G1').alignment = { textRotation: -45 }; ws.getCell('H1').alignment = { textRotation: 'vertical' }; //目前我用到了 ：水平垂直居中，wraptext sheet.getCell(col+1).alignment={ vertical: 'middle', horizontal: 'center', wrapText: true }; 

border

Valid Border Styles

• thin （常用）
• dotted
• dashDot
• hair
• dashDotDot
• slantDashDot
• mediumDashed
• mediumDashDotDot
• mediumDashDot
• medium
• double
• thick

// set single thin border around A1 //封装成函数每个cell都要加一遍 ws.getCell('A1').border = { top: {style:'thin'}, left: {style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'} }; // set double thin green border around A3 ws.getCell('A3').border = { top: {style:'double', color: {argb:'FF00FF00'}}, left: {style:'double', color: {argb:'FF00FF00'}}, bottom: {style:'double', color: {argb:'FF00FF00'}}, right: {style:'double', color: {argb:'FF00FF00'}} }; // set thick red cross in A5 ws.getCell('A5').border = { diagonal: {up: true, down: true, style:'thick', color: {argb:'FFFF0000'}} }; 

Fill

// ws.getCell(xx).fill={ type: "pattern", pattern: "solid", fgColor: { argb: 'FF' } } 

目前就用到这些 that’s all！！！！

readfile

// read from a file var workbook = new Excel.Workbook(); workbook.xlsx.readFile(filename) .then(function() { // use workbook workbook.worksheets 能得到每个worksheet已json形式的数组集合。 其中 workbook.worksheets[0].name 能得到第一个sheet的表名。 }); // pipe from stream var workbook = new Excel.Workbook(); stream.pipe(workbook.xlsx.createInputStream());