How to send excel file with nodemailer

Question

I'm trying to send an email with an excel file attached, but when I open it an error pops up saying that the file is damaged. It also downloads the file (which I actually don't want to) and I can open that with no problem.

I'm using the js-xlsx library to create the file and the nodemailer library to send it.

Function in email.service.ts:

sendEmail(values: any) {
    let message: {from: string, to: string, subject: string, html: string, attachments: {}[] | null} = {
        from: 'gestionepresenze@gmail.com',
        to: values.email,
        subject: values.oggetto,
        html: this.storage.createTable(values.messaggio).outerHTML,
        attachments: null
    }

    if (values.attach) {
        message["attachments"] = [{
            filename: values.fileName + '.xlsx',
            content: this.excel.createExcel(values),
            contentType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        }]
    }

    return this.http.post('http://localhost:3000/send', message)
}

Function in excel.service.ts:

createExcel(values: any) {
    const fileName = values.fileName + '.xlsx';
    const table = this.storage.createTable('');
    const ws: XLSX.WorkSheet = XLSX.utils.table_to_sheet(table);
    const wb: XLSX.WorkBook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, 'Presenze');
    return XLSX.writeFile(wb, fileName);
}

Giuseppe Graziano · Accepted Answer · 2022-10-13T09:30:22.853

I got it to work.

First of all the right method is XLSX.write(wb, {type: 'buffer', bookType: 'xlsx'});, not XLSX.writeFile(...).

But the server was still throwing an error, saying that I was passing an Object. The problem was that I was sending what I thought was an Uint8Array, while the node app was actually receiving an Object.

Why? I don't know.

But knowing this I now convert the content to a Uint8Array directly in the node app.

Here's how I changed the code:

Function in email.service.ts:

sendEmail(values: any) {
    let message: {from: string, to: string, subject: string, html: string, attachments: {}[] | null} = {
        from: 'gestionepresenze@gmail.com',
        to: values.email,
        subject: values.oggetto,
        html: this.storage.createTable(values.messaggio).outerHTML,
        attachments: null
    }

    if (values.attach) {
        message["attachments"] = [{
            filename: values.fileName + '.xlsx',
            content: this.excel.createExcel(values)
        }]
    }

    return this.http.post('http://localhost:3000/send', message)
}

Function in excel.service.ts:

createExcel(values: any) {
    const fileName = values.fileName + '.xlsx';
    const table = this.storage.createTable('');
    const ws: XLSX.WorkSheet = XLSX.utils.table_to_sheet(table);
    const wb: XLSX.WorkBook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, 'Presenze');
    return XLSX.write(wb, {type: 'buffer', bookType: 'xlsx'});
}

Function in the node app to send the email:

fastify.post('/send', (request, reply) => {
    let content = request.body.attachments[0].content;
    let array = new Uint8Array(Object.entries(content).length);

    for (let [key, value] of Object.entries(content)) {
        array[key] = value;
    }

    request.body.attachments[0].content = array;
    const mailOptions = request.body
    
    transporter.sendMail(mailOptions, (error, info) => {
        if (error) {
            console.log(error);
        } else {
            console.log('Sent: ' + info.response);
        }
    })
})

Thanks for sharing what worked for you. Can you maybe take a screenshot of the request body from the network tab of your browser? I am interested in seeing what is actually contained in the request body. — Fabian Strathaus, Oct 11 '22 at 16:41
@fabian there's some Italian in there, I hope it's not a problem: https://imgur.com/AmLPWTJ. What I think is happening is that the function creates a javascript object with the properties of an Uint8Array. — Giuseppe Graziano, Oct 13 '22 at 07:40

score 0 · Answer 2 · answered Oct 11 '22 at 12:02

0

From the documentation (https://docs.sheetjs.com/docs/api/write-options) I can see the following:

XLSX.writeFile(wb, filename, write_opts) attempts to write wb to filename. In browser-based environments, it will attempt to force a client-side download.

This is the reason a download is triggered on your client.

You probably want to use XLSX.write(workbook, { type: "array", bookType: "xlsx" });, so that an Uint8Array is returned from the function and attached as content to your form data.

answered Oct 11 '22 at 12:02

Fabian Strathaus

3,192
1
4
26

what should the content type be at that point? – Giuseppe Graziano Oct 11 '22 at 12:50
Well, content type is an optional property, so I would suggest to just don't set it (if not set will be derived from the filename property). – Fabian Strathaus Oct 11 '22 at 13:15
Didn't word unfortunately. The server throws this error: "TypeError [ERR_INVALID_ARG_TYPE]: The "chunk" argument must be of type string or an instance of Buffer or Uint8Array. Received an instance of Object" – Giuseppe Graziano Oct 11 '22 at 13:55
This is strange, since setting 'array' as return type should return an Uint8Array. Nevertheless you can also try to set type to "buffer". – Fabian Strathaus Oct 11 '22 at 14:04
Same result. I even tried other return types and other methods. Most of the times it throws the same error and if it doesn't, the file results damaged. – Giuseppe Graziano Oct 11 '22 at 14:25
I have an update: I used "XLSX.write(wb, {type: 'buffer', bookType: 'xlsx'})" and logged the result to the console and it says it's a Uint8Array, but when I pass it to nodemailer it says it's an Object (as you can see in the error) – Giuseppe Graziano Oct 11 '22 at 14:54
okay it works now, here's how and why https://stackoverflow.com/a/74030751/20212996. Thanks for the help, I didn't think about types before your answer. – Giuseppe Graziano Oct 11 '22 at 15:45

How to send excel file with nodemailer

2 Answers2