nodejs 生成csv与excel文件

https://www.npmjs.com/package/json2csv
https://www.npmjs.com/package/xlsx
https://www.npmjs.com/package/js-xlsx

MIME类型列表

https://developer.mozilla.org/zh-CN/docs/Web/HTTP/Basics_of_HTTP/MIME_types/Complete_list_of_MIME_types

npm install json2csv xlsx

例子一 json2csv

var {Parser} = require('json2csv');
var fs = require('fs');
var fields = ['car', 'price'];
var myCars = [
  {
    "car": "Audi",
    "price": 40000,
    "color": "blue"
  }, {
    "car": "BMW",
    "price": 35000,
    "color": "black"
  }, {
    "car": "Porsche",
    "price": 60000,
    "color": "green"
  }
];
var opts = {
    fields: fields,
//  quotes: '',
    excelStrings: true
};
try {
  var parser = new Parser(opts);
  var csv = parser.parse(myCars);
  fs.writeFile('file.csv', csv, function(err) {
    if (err) throw err;
    console.log('file saved');
  });
  fs.writeFile('file.xls', csv, function(err) {
    if (err) throw err;
    console.log('file saved');
  });
} catch (err) {
  console.log(err);
}

debugger;
var XLSX = require('xlsx');
var workbook = XLSX.readFile('./file.csv');
// var workbook = XLSX.readFile('./file.xls'); // 两种都可以使用
var fs = require('fs');
XLSX.writeFile(workbook, 'out.xlsx');
var wopts = { bookType:'xlsx', bookSST:false, type:'binary' };
var wbout = XLSX.write(workbook, wopts);
function s2ab(s) {
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
    return buf;
}
let buffer = Buffer.from(s2ab(wbout));
fs.writeFile('file2.xls', buffer, {encoding: "utf-8"},function(err) {
    if (err) throw err;
    console.log('file saved');
});
debugger;

var {Parser} = require('json2csv');
var XLSX = require('xlsx');
var fields = ['car', 'price'];
var myCars = [
  {
    "car": "Audi",
    "price": 40000,
    "color": "blue"
  }, {
    "car": "BMW",
    "price": 35000,
    "color": "black"
  }, {
    "car": "Porsche",
    "price": 60000,
    "color": "green"
  }
];
var opts = {
    fields: fields,
//  quotes: '',
    excelStrings: true
};
try {
  var parser = new Parser(opts);
  var csv = parser.parse(myCars);
  // let buffer = Buffer.from(csv);
    // var workbook = XLSX.read(buffer.toString(), {type:"binary"});
  var workbook = XLSX.read(csv, {type:"binary"});
  XLSX.writeFile(workbook, 'out2.xlsx');
} catch (err) {
  console.log(err);
}

例子二 xlsx

读

const XLSX = require('xlsx');
const filename = './output.xlsx';
const workbook = XLSX.readFile(filename);
// 获取 Excel 中所有表名
var sheetNames = workbook.SheetNames; // 返回 ['sheet1', 'sheet2',……]
// 根据表名获取对应某张表
var worksheet = workbook.Sheets[sheetNames[0]];
// 将表转换为json
var workjson = XLSX.utils.sheet_to_json(worksheet);
console.log(workjson);

写

const XLSX = require('xlsx');
const fs = require('fs');
var json = [{ key: 'aaa', value: 'bbb' }];
var workbook = XLSX.utils.json_to_sheet(json);
// 构建 workbook 对象
var wb = {
  SheetNames: ['mySheet'],
  Sheets: {
    'mySheet': workbook
  }
};
XLSX.writeFile(wb, './output.xlsx');