I am trying to download json to excel. I have a requirement to add an image in cell a1, and then bold the headers.

I have this code below. I took from google, but I have data I'd like to populate dynamically (headers too). I'm struggling to figure out how. (datatableRows has my data - json array of arrays ). I haven't yet seen either how to bold headers and how to add the image.

import React, { useContext } from 'react';
import ExcelJS from 'exceljs';
import AlertContext from '../../AlertContext';
import { errorAlert } from '../Alert';

const useFileExportToExcel = <T extends object>() => {
  const { setAlert } = useContext(AlertContext);
  const workbook = new ExcelJS.Workbook();
  workbook.addWorksheet('sheet1');
  const worksheet = workbook.getWorksheet('sheet1');

  worksheet.columns = [
    { header: 'ID', key: 'id' },
    { header: 'fads', key: 'createdAt' },
    { header: 'fadsf', key: 'name' },
  ];

  worksheet.addRows([
    {
      id: 'f001',
      createdAt: 1629902208,
      name: 'fasdf',
    },
    {
      id: 'f002',
      createdAt: 1629902245,
      name: 'fasd',
    },
    {
      id: 'f003',
      createdAt: 1629902265,
      name: 'fdafads',
    },
  ]);

  return (dataTableRows: T[], fileName: string) => {
    try {
      // const blob = new Blob([xls]);
      workbook.xlsx.writeBuffer().then(data => {
        const blob = new Blob([data], {
          type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        });
        const a = window.document.createElement('a');
        const downloadUrl = window.URL.createObjectURL(blob);
        a.href = downloadUrl;
        a.download = `${fileName}.xlsx`;
        document.body.appendChild(a);
        a.click();
        document.body.removeChild(a);
        window.URL.revokeObjectURL(downloadUrl);
      });
    } catch (err) {
      setAlert(errorAlert('An error occured during export'));
    }
  };
};

export default useFileExportToExcel;

CodePudding user response：

const rowHeader = [
  { key: 'xxx' },
  { key: 'adsff' },
  { key: 'ff' },
  { key: 'ffff' },
  { key: 'sdfasdf' },
  { key: 'fasdfads' },
  { key: 'fasdfasdf' },
  { key: 'fasdfadf' },
  { key: 'fasdfawsdft' },
];
const imageId2 = workbook.addImage({
            base64: myBase64Image,
            extension: 'png',
          });
          worksheet.addImage(imageId2, 'A1:D3');
          worksheet.mergeCells('A1:D3');
          worksheet.addRow({});
          const col: string[] = [];
    
          rowHeader.forEach(header => {
            col.push(header.key);
          });
          const columnHeaders: string[] = Object.values(col);
          worksheet.getRow(5).values = columnHeaders;
          worksheet.getRow(5).font = {
            name: 'Arial Black',
            bold: true,
          };
          worksheet.columns = rowHeader;
          worksheet.addRows(dataTableRows);
    
          workbook.xlsx.writeBuffer().then(data => {
            const blob = new Blob([data], {
              type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            });
            const a = window.document.createElement('a');
            const downloadUrl = window.URL.createObjectURL(blob);
            a.href = downloadUrl;
            a.download = `${fileName}.xlsx`;
            document.body.appendChild(a);
            a.click();
            document.body.removeChild(a);
            window.URL.revokeObjectURL(downloadUrl);
          });