使用ExcelJS创建Excel文件并向其中写入数据

Question

我写了一个使用ExcelJS创建新Excel文件的脚本。添加了3个表头并插入2行数据，然后将该文件保存到磁盘。在下一步中，它应该读取先前保存的文件，在其基础上添加1行数据，并将其另存为新名称。我找不到第二部分失败的原因，即尝试添加第三行数据的部分。两个文件看起来一样，在控制台中也没有错误信息。

const Excel = require('exceljs');

async function exTest(){
  const workbook = new Excel.Workbook();
  const worksheet = workbook.addWorksheet("My Sheet");

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

worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970, 1, 1)});
worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965, 1, 7)});

// save under export.xlsx
await workbook.xlsx.writeFile('export.xlsx');

// load a copy of export.xlsx
const newWorkbook = new Excel.Workbook();
await newWorkbook.xlsx.readFile('export.xlsx');

const newworksheet = newWorkbook.getWorksheet('My Sheet');
newworksheet.addRow(
  {id: 3, name: 'New Guy', dob: new Date(2000, 1, 1)}
);

await newWorkbook.xlsx.writeFile('export2.xlsx');

console.log("File is written");
};

exTest();

- miyagisan

---

你能在控制台中看到“文件已写入”吗？ - Alekhya Satya

@AlekhyaSatya：是的。而且没有错误，两个文件都被创建了。问题在于第二个文件与第一个文件相同，它没有将额外的第三行数据添加到新行中。这段代码可以很容易地运行，你只需要 npm install exceljs。 - miyagisan

这两个文件是否已经打开？如果是，请关闭它们并重新运行代码。 - Alekhya Satya

@AlekhyaSatya 在代码运行之前，文件还不存在。 - miyagisan

@AlekhyaSatya已解决并发布了答案。 - miyagisan

Answer 1

通过在代码的第二部分添加数组来解决了问题。然后第三行成功添加了。当将带有列名的对象传递给addRow()时，我必须提供已存在于Excel文件中的列的描述。

const Excel = require('exceljs');

async function exTest(){
  const workbook = new Excel.Workbook();
  const worksheet = workbook.addWorksheet("My Sheet");

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

worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970, 1, 1)});
worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965, 1, 7)});

// save under export.xlsx
await workbook.xlsx.writeFile('export.xlsx');

//load a copy of export.xlsx
const newWorkbook = new Excel.Workbook();
await newWorkbook.xlsx.readFile('export.xlsx');

const newworksheet = newWorkbook.getWorksheet('My Sheet');
newworksheet.columns = [
 {header: 'Id', key: 'id', width: 10},
 {header: 'Name', key: 'name', width: 32}, 
 {header: 'D.O.B.', key: 'dob', width: 15,}
];
await newworksheet.addRow({id: 3, name: 'New Guy', dob: new Date(2000, 1, 1)});

await newWorkbook.xlsx.writeFile('export2.xlsx');

console.log("File is written");

};

exTest();

- miyagisan

Answer 2

附带的链接有效

const Excel = require('exceljs');

// Create workbook & add worksheet
const workbook = new Excel.Workbook();
const worksheet = workbook.addWorksheet('ExampleSheet');

// add column headers
worksheet.columns = [
  { header: 'Package', key: 'package_name' },
  { header: 'Author', key: 'author_name' }
];

// Add row using key mapping to columns
worksheet.addRow(
  { package_name: "ABC", author_name: "Author 1" },
  { package_name: "XYZ", author_name: "Author 2" }
);

// Add rows as Array values
worksheet
  .addRow(["BCD", "Author Name 3"]);

// Add rows using both the above of rows
const rows = [
  ["FGH", "Author Name 4"],
  { package_name: "PQR", author_name: "Author 5" }
];


worksheet
  .addRows(rows);

// save workbook to disk
workbook
  .xlsx
  .writeFile('sample.xlsx')
  .then(() => {
    console.log("saved");
  })
  .catch((err) => {
    console.log("err", err);
  });

https://repl.it/@vishwasc/ExcelJs-Example#index.js

图片附件

// add image to workbook by filename
const imageId1 = workbook.addImage({
  filename: 'path/to/image.jpg',
  extension: 'jpeg',
});

// add image to workbook by buffer
const imageId2 = workbook.addImage({
  buffer: fs.readFileSync('path/to.image.png'),
  extension: 'png',
});

// add image to workbook by base64
const myBase64Image = "data:image/png;base64,iVBORw0KG...";
const imageId2 = workbook.addImage({
  base64: myBase64Image,
  extension: 'png',
});

请注意，无论哪种情况下都必须指定文件扩展名。有效的扩展名包括“jpeg”，“png”和“gif”。

- Balaji

---

很棒的解决方案！ - Kunal Mukherjee

Answer 3

我从链接获取了解决方案，使用下面的代码，它对我很有效。希望它也能帮助到你们。谢谢 :)

const ExcelJS = require('exceljs');

const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('Sheet 1');

worksheet.columns = [
  { header: 'Name', key: 'name', width: 20 },
  { header: 'Email', key: 'email', width: 25 },
  { header: 'Age', key: 'age', width: 10 }
];

worksheet.addRow({ name: 'John Doe', email: '[e-mail]', age: 30 });
worksheet.addRow({ name: 'Jane Smith', email: '[e-mail]', age: 25 });

workbook.xlsx.writeFile('example.xlsx')
  .then(() => {
    console.log('Excel file created!');
  })
  .catch((error) => {
    console.log(error);
  });

- Ankur prajapati