DEV Community

Cover image for How to Download xlsx Files from a Next.js Route Handler
Dave Gray
Dave Gray

Posted on โ€ข Originally published at davegray.codes on

How to Download xlsx Files from a Next.js Route Handler

TLDR: You can set up a Next.js Route Handler that creates and downloads Microsoft Excel (xlsx) files.

XLSX Files

No matter where you work as a developer, there's a good chance someone will ask you to send them an MS Excel spreadsheet sooner or later. Those files end with the extension xlsx or xls.

At my job, I manage a large data project and regularly receive requests for table exports as spreadsheets.

I decided to set up an API endpoint via Next.js route handler which will allow my boss and co-workers to create and download their own table exports on demand.

xlsx dependency

The xlsx package is also known as SheetJS.

Install xlsx:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.1/xlsx-0.20.1.tgz
Enter fullscreen mode Exit fullscreen mode

Note: Do NOT get the xlsx package from the npm registry with npm install xlsx. Sheetjs has stopped using the public registry at version 18.5. You will see the version in the public registry is 2+ years old and has a high severity vulnerability now. You can confirm this vulnerability on the Socket.dev xlsx page.

Add xlsx to Your Project

Next, import xlsx into your project:

import * as XLSX from 'xlsx'
Enter fullscreen mode Exit fullscreen mode

My Next.js route handler starts by receiving a parameter with the requested table name. I'm also including some pseudo-code comments to allow you to follow the logic process until I get to the xlsx details.

/* example path: /api/tables/[table] */

export async function GET(
    request: NextRequest,
    { params }: { params: { table: string } }
) {
    // check for authorized user first! 
    try {
        const { table } = params
        if (!table) throw new Error('Table name required')

        // check table name with list of table names here

        // if table doesn't exist, throw an error 

        // Query: SELECT * FROM table and get a JSON response 

    } catch (e) {
        if (e instanceof Error) {
            return new Response(e.message, {
                status: 400,
            })
        }
    }


}
Enter fullscreen mode Exit fullscreen mode

Creating the XLSX File

Here's the good stuff using the xlsx package:

// ...previous code 

// Query: SELECT * FROM your table and get a JSON response

// Create a new XLSX workbook:
const workbook = XLSX.utils.book_new()

// Create a new worksheet: 
const worksheet = XLSX.utils.json_to_sheet(jsonTableData)

// Append the worksheet to the workbook: 
XLSX.utils.book_append_sheet(workbook, worksheet, "MySheet")

// Create data buffer 
const buffer = XLSX.write(workbook, { type: "buffer", bookType: "xlsx" })

// Create and send a new Response
return new Response(buffer, {
    status: 200,
    headers: {
        'Content-Disposition': `attachment; filename="${table}.xlsx"`,
        'Content-Type': 'application/vnd.ms-excel',
    }
})

// } catch (e) { and rest of code...
Enter fullscreen mode Exit fullscreen mode

You can find a similar example for a Node.js & Express server in the SheetJS docs.

The key to success here is creating the buffer with the XLSX.write method, and then sending it in the Response with the proper headers.

Other File Types

Do you want to download CSV (comma-separated) or TSV (tab-separated) files?

Or maybe just display an HTML version?

No problem!

You can find all of the appropriate XLSX methods in the SheetJS docs, but here is how I provide CSV downloads, too.

I'm looking for a format parameter. If it equals csv, then I'm sending that file type instead.

/* example path: /api/tables/[table]?format=csv */

// begin route handler code above 

// put this somewhere before the XLSX creation and response 
const searchParams = request.nextUrl.searchParams
const format = searchParams.get('format')

if (format === 'csv') {

    const csv = XLSX.utils.sheet_to_csv(worksheet, {
        forceQuotes: true,
    })

    return new Response(csv, {
        status: 200,
        headers: {
            'Content-Disposition': `attachment; filename="${tableName}.csv"`,
            'Content-Type': 'text/csv',
        }
    })
}
Enter fullscreen mode Exit fullscreen mode

Final Notes:

  • Always check for an authorized user.

  • Don't allow SQL injections. I'm verifying the table parameter with a list of accurate table names. Only those specific values are allowed.

Enjoy creating downloads!


Let's Connect!

Hi, I'm Dave. I work as a full-time developer, instructor and creator.

If you enjoyed this article, you might enjoy my other content, too.

My Stuff: Courses, Cheat Sheets, Roadmaps

My Blog: davegray.codes

YouTube: @davegrayteachescode

X: @yesdavidgray

GitHub: gitdagray

LinkedIn: /in/davidagray

Patreon: Join my Support Team!

Buy Me A Coffee: You will have my sincere gratitude

Thank you for joining me on this journey.

Dave

Image of Timescale

๐Ÿš€ pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applicationsโ€”without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Image of Docusign

๐Ÿ› ๏ธ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more

๐Ÿ‘‹ Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay