如何在Node.js中使用exceljs下载已创建的Excel文件

Question

如何在Node.js中使用exceljs下载已创建的Excel文件

22

我正在使用exceljs模块创建Excel文件。问题是它既没有被创建，也没有保存在路径中。

var excel = require('exceljs');
var options = {
    filename: './streamed-workbook.xlsx',
    useStyles: true,
    useSharedStrings: true
};

var workbook = new Excel.stream.xlsx.WorkbookWriter(options);
var sheet = 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: 10 }
];

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)});
worksheet.commit();

workbook.commit().then(function(){
    console.log('xls file is written.');
});

但是当我运行代码时，什么也没有发生。Excel文件没有被创建。我在这里错过了什么？

*********************** 编辑 ************************** 对我的代码进行了以下更改，但仍然无法正常工作。

        var Excel = require('exceljs');
        var workbook = new Excel.Workbook();
        var 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: 10 }
        ];
        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)});
        workbook.commit();
        workbook.xlsx.writeFile('./temp.xlsx').then(function() {
            // done
            console.log('file is written');
        });

- codeinprogress

你在哪里使用了这段代码，是在控制器还是模式页面中？@codeinprogress - Vinoth

我不知道你所说的“模式页面”是什么意思，这段代码在我的路由器js文件中。 - codeinprogress

你有关于Excel下载的示例代码吗？@codeinprogress - Vinoth

请查看下面的解决方案。 - codeinprogress

我需要在我的控制器代码中使用你的代码吗？还是如何在服务器页面中调用你的代码？@代码正在进行中。 - Vinoth

6个回答

8

从这个链接获取答案：https://github.com/exceljs/exceljs/issues/37

    router.get('/createExcel', function (req, res, next) {
    var workbook = new Excel.Workbook();

    workbook.creator = 'Me';
    workbook.lastModifiedBy = 'Her';
    workbook.created = new Date(1985, 8, 30);
    workbook.modified = new Date();
    workbook.lastPrinted = new Date(2016, 9, 27);
    workbook.properties.date1904 = true;

    workbook.views = [
        {
            x: 0, y: 0, width: 10000, height: 20000,
            firstSheet: 0, activeTab: 1, visibility: 'visible'
        }
    ];
    var 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: 10, outlineLevel: 1, type: 'date', formulae: [new Date(2016, 0, 1)] }
    ];

    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) });

    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader("Content-Disposition", "attachment; filename=" + "Report.xlsx");
    workbook.xlsx.write(res)
        .then(function (data) {
            res.end();
            console.log('File write done........');
        });
 }

- gandalivs

你的答案实际上是最好的。 - Ameer Dheyaa

1

这个生成的文件已经损坏了，你有什么想法为什么会这样？ - user3491125

7

我发现错误是因为workbook.commit()导致的。我移除了commit，一切都开始像魅力一样运行起来了。这里是创建和下载Excel文件的完整工作代码：

注意：我使用了一个名为tempfile的npm模块来创建所创建的Excel文件的临时文件路径。然后这个文件路径会自动被删除。希望能对你有所帮助。

try {
        var workbook = new Excel.Workbook();
        var 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: 10 }
        ];
        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)});

        var tempFilePath = tempfile('.xlsx');
        workbook.xlsx.writeFile(tempFilePath).then(function() {
            console.log('file is written');
            res.sendFile(tempFilePath, function(err){
                console.log('---------- error downloading file: ' + err);
            });
        });
    } catch(err) {
        console.log('OOOOOOO this is the error: ' + err);
    }

- codeinprogress

如何从controller.js端调用此代码？你能告诉我@codein progress吗？ - Vinoth

1

在我的情况下，这是一个 router.get() 请求。所以 router.get('/download', function(req, res){...上面的代码}); - codeinprogress

你能检查一下我的代码吗？@Codeinprogress https://stackoverflow.com/questions/44369082/excel-download-is-not-working-in-mean-stack-app?noredirect=1#comment75820326_44369082 我已经这样做了。 - Vinoth

1

看起来你正在使用不同的 Excel 模块用于 Node.js。这是我正在使用的模块：https://www.npmjs.com/package/exceljs - codeinprogress

嗨，我是AngularJS的新手。我遇到了“router未定义”的错误，请问我应该在哪里使用这段代码：var excel = require('exceljs'); var options = { filename: './streamed-workbook.xlsx', useStyles: true, useSharedStrings: true }; - Vinoth

显示剩余5条评论

1

这段代码片段使用node.js与excel4node和express模块，将JSON数据转换为Excel文件并使用Javascript将其发送给客户端。

const xl = require('excel4node');
const express = require('express');
const app = express();

var json = [{"Vehicle":"BMW","Date":"30, Jul 2013 09:24 AM","Location":"Hauz Khas, Enclave, New Delhi, Delhi, India","Speed":42},{"Vehicle":"Honda CBR","Date":"30, Jul 2013 12:00 AM","Location":"Military Road,  West Bengal 734013,  India","Speed":0},{"Vehicle":"Supra","Date":"30, Jul 2013 07:53 AM","Location":"Sec-45, St. Angel's School, Gurgaon, Haryana, India","Speed":58},{"Vehicle":"Land Cruiser","Date":"30, Jul 2013 09:35 AM","Location":"DLF Phase I, Marble Market, Gurgaon, Haryana, India","Speed":83},{"Vehicle":"Suzuki Swift","Date":"30, Jul 2013 12:02 AM","Location":"Behind Central Bank RO, Ram Krishna Rd by-lane, Siliguri, West Bengal, India","Speed":0},{"Vehicle":"Honda Civic","Date":"30, Jul 2013 12:00 AM","Location":"Behind Central Bank RO, Ram Krishna Rd by-lane, Siliguri, West Bengal, India","Speed":0},{"Vehicle":"Honda Accord","Date":"30, Jul 2013 11:05 AM","Location":"DLF Phase IV, Super Mart 1, Gurgaon, Haryana, India","Speed":71}]

const createSheet = () => {

  return new Promise(resolve => {

// setup workbook and sheet
var wb = new xl.Workbook();

var ws = wb.addWorksheet('Sheet');

// Add a title row

ws.cell(1, 1)
  .string('Vehicle')

ws.cell(1, 2)
  .string('Date')

ws.cell(1, 3)
  .string('Location')

ws.cell(1, 4)
  .string('Speed')

// add data from json

for (let i = 0; i < json.length; i++) {

  let row = i + 2

  ws.cell(row, 1)
    .string(json[i].Vehicle)

  ws.cell(row, 2)
    .date(json[i].Date)

  ws.cell(row, 3)
    .string(json[i].Location)

  ws.cell(row, 4)
    .number(json[i].Speed)
}

resolve( wb )

  })
}

app.get('/excel', function (req, res) {

  createSheet().then( file => {
    file.write('ExcelFile.xlsx', res);
  })

});

app.listen(3040, function () {
  console.log('Excel app listening on port 3040');
});

- philipeachille

0

你的代码中有一个错误。

你声明了一个叫My Sheet的变量，但在整个代码中使用了一个不同的变量。

var sheet = 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: 10 } ];

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)});

worksheet.commit();

将变量Sheet更改为Worksheet，像下面的代码一样

变量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: 10 } ]; 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)}); worksheet.commit(); 这应该可以解决你的问题。谢谢

- Princess

我已经进行了上述编辑更改，但仍然无法正常工作。 - codeinprogress

我在编辑器中放置的代码是我正在使用的代码。我根据他们的文档编写了一个示例，但它没有起作用。 - codeinprogress

0

var excel = require("exceljs");
var workbook1 = new excel.Workbook();
workbook1.creator = 'Me';
workbook1.lastModifiedBy = 'Me';
workbook1.created = new Date();
workbook1.modified = new Date();
var sheet1 = workbook1.addWorksheet('Sheet1');
var reHeader=['FirstName','LastName','Other Name'];
var reColumns=[
    {header:'FirstName',key:'firstname'},
    {header:'LastName',key:'lastname'},
    {header:'Other Name',key:'othername'}
];
sheet1.columns = reColumns;
workbook1.xlsx.writeFile("./uploads/error.xlsx").then(function() {
    console.log("xlsx file is written.");
});

这会在上传文件夹中创建一个xlsx文件。

- Karthik

好的，但是否有一种在文件创建后立即下载的方法？我希望用户点击一个按钮>生成Excel文件>向他们提供下载/保存选项。 - codeinprogress

我已经找到了下载创建文件的解决方案，请参考：http://stackoverflow.com/questions/39667773/how-to-give-download-window-option-to-user-in-a-node-js-express-application/39669021#39669021 - codeinprogress

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Danielle · Accepted Answer

为了将Excel工作簿发送给客户端，您可以：

使用异步等待：

async function sendWorkbook(workbook, response) { 
    var fileName = 'FileName.xlsx';

    response.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    response.setHeader("Content-Disposition", "attachment; filename=" + fileName);

     await workbook.xlsx.write(response);

    response.end();
}

使用 Promise：

function sendWorkbook(workbook, response) { 
    var fileName = 'FileName.xlsx';

    response.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    response.setHeader("Content-Disposition", "attachment; filename=" + fileName);

    workbook.xlsx.write(response).then(function(){
        response.end();
    });
}