Building Dynamic Table Headers in React + ExcelJS

Building Dynamic Table Headers in React + ExcelJS

This article is a follow-up to my previous post, where I showed how to export data to Excel using ExcelJS and ReactJS. Now, let’s take it a step further — by creating dynamic table headers both in the UI and the Excel export.

Use Case

You have sales or forecast data for each product per month (V1 to V12, P1 to P12) — but you only want to export a specific range of months (e.g., May to July).

Each product has:

[
{"Product_Code":"CC0110102","Product_Name":"Lorem","Start_Month":5,"End_Month":7,"V1":".00","P1":"0","V2":".00","P2":"0","V3":".00","P3":"0","V4":".00","P4":"0","V5":"213.00","P5":"-.2159624413145","V6":"167.00","P6":"-.0179640718562","V7":"164.00","P7":"-10","V8":".00","P8":"0","V9":".00","P9":"0","V10":".00","P10":"0","V11":".00","P11":"0","V12":".00","Total_Qty":"544.00"},
{"Product_Code":"CC0110102","Product_Name":"Lorem","Start_Month":5,"End_Month":7,"V1":".00","P1":"0","V2":".00","P2":"0","V3":".00","P3":"0","V4":".00","P4":"0","V5":"213.00","P5":"-.2159624413145","V6":"167.00","P6":"-.0179640718562","V7":"164.00","P7":"-10","V8":".00","P8":"0","V9":".00","P9":"0","V10":".00","P10":"0","V11":".00","P11":"0","V12":".00","Total_Qty":"544.00"},
{"Product_Code":"CC0110102","Product_Name":"Lorem","Start_Month":5,"End_Month":7,"V1":".00","P1":"0","V2":".00","P2":"0","V3":".00","P3":"0","V4":".00","P4":"0","V5":"213.00","P5":"-.2159624413145","V6":"167.00","P6":"-.0179640718562","V7":"164.00","P7":"-10","V8":".00","P8":"0","V9":".00","P9":"0","V10":".00","P10":"0","V11":".00","P11":"0","V12":".00","Total_Qty":"544.00"},
{"Product_Code":"CC0110102","Product_Name":"Lorem","Start_Month":5,"End_Month":7,"V1":".00","P1":"0","V2":".00","P2":"0","V3":".00","P3":"0","V4":".00","P4":"0","V5":"213.00","P5":"-.2159624413145","V6":"167.00","P6":"-.0179640718562","V7":"164.00","P7":"-10","V8":".00","P8":"0","V9":".00","P9":"0","V10":".00","P10":"0","V11":".00","P11":"0","V12":".00","Total_Qty":"544.00"}
]

We’ll:

Dynamically build headers: Qty May, % May, Qty Jun, Qty Jul, etc.

Render only the relevant Vx and Px values based on Start_Month and End_Month.

Step-by-Step: Dynamic Header Excel Export

Step 1: Create an Export Excel Function

import ExcelJS from 'exceljs';
import { saveAs } from 'file-saver';
import { has } from "lodash"

const ExportToExcel = ({ data, fileName }) => {
    const exportExcel = async () => {
        // 1. Create a new workbook
        const workbook = new ExcelJS.Workbook();
        const worksheet = workbook.addWorksheet('Data Report');

        const month = [
            'Jan',
            'Feb',
            'Mar',
            'Apr',
            'May',
            'Jun',
            'Jul',
            'Aug',
            'Sep',
            'Oct',
            'Nov',
            'Dec',
        ]


        // 2. Define the table headers
        let header = []
        let startMonth = data[0].Start_Month
        let endMonth = data[0].End_Month
        let firstData = data[0];

        header.push({ name: '#', filterButton: false })
        header.push({ name: 'Product Name', filterButton: false })

        for (let index = startMonth; index <= endMonth; index++) {
            const key1 = `V${index}`
            const key2 = `P${index}`
            if (has(firstData, key1)) {

                header.push({
                    name: `${month[index - 1]}`,
                    key: key1,
                    style: {
                        alignment: {
                            vertical: 'middle',
                            horizontal: 'right',
                            wrapText: true,
                        },
                    },
                    filterButton: true,
                })

                if (index !== endMonth) {
                    if (has(firstData, key2)) {
                        header.push({
                            name: `${month[index - 1]} (+/-)%`,
                            key: key2,
                            style: {
                                alignment: {
                                    vertical: 'middle',
                                    horizontal: 'center',
                                    wrapText: true,
                                },
                            },
                            filterButton: true,
                        })
                    }
                }
            }
        }

        header.push({ name: 'Total', filterButton: false })


        // 3. generate data
        let dataArray = []

        data.forEach((data, index) => {
            let temp = []
            temp.push(index + 1)
            temp.push(data?.Product_Name)

            let total = 0;

            for (let index = startMonth; index <= endMonth; index++) {
                const key1 = `V${index}`
                const key2 = `P${index}`

                if (has(data, key1)) {
                    if (data[key1] === ".00") {
                        temp.push(0)
                    } else {
                        temp.push(parseInt(data[key1]))
                        total = total + parseInt(data[key1]);
                    }

                    if (index !== endMonth) {
                        if (has(data, key2)) {
                            temp.push(parseFloat(data[key2]))
                        }
                    }
                }
            }

            temp.push(total)
            dataArray.push(temp)
        })

        worksheet.addTable({
            name: 'headerName',
            ref: `A3`,
            headerRow: true,
            totalsRow: false,
            style: {
                theme: 'TableStyleMedium2',
                showRowStripes: true,
                // @ts-ignore: Unreachable code error
                width: 200,
            },
            columns: header ? header : [{ name: '' }],
            rows: dataArray.map((e) => {
                let arr = []
                for (let i in e) {
                    arr.push(e[i])
                }
                return arr
            }),
        })



        // 5. Save the workbook as an Excel file
        const buffer = await workbook.xlsx.writeBuffer();
        const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
        saveAs(blob, `${fileName}.xlsx`);
    };

    return (
        <button onClick={exportExcel}>Download Excel</button>
    );
};

export default ExportToExcel;

Step 2: Use the ExportToExcel Component
After creating the ExportToExcel component, use it in a relevant file, such as App.js. Ensure you have the data ready for export to Excel.

import React from 'react';
import ExportToExcel from './ExportToExcel';

const App = () => {
  const data = [
    { id: 1, name: 'Name 1', email: '[e-mail]', joinDate: '2023-01-15' },
    { id: 2, name: 'Name 2', email: '[e-mail]', joinDate: '2023-02-20' },
    // Add more data as needed
  ];

  return (
    <div>
      <h1>Export Data to Excel</h1>
      <ExportToExcel data={data} fileName="Data_Report"/>
    </div>
  );
};

export default App;