ExcelJs download xlsx file with tRPC router

Question

I want to click the bottom and download the excel file, but I can't figure out why it is not working.
the main problem is at tRPC router side.

the tool I using:

1. T3
2. ExcelJs

tRPC router:

  .mutation("xlsx", {
    input: z.object({
      id: z.string(),
    }),
    resolve: async ({ ctx }) => {
      const FILE_PATH = "./src/utils/01.xlsx";

      const wb = new ExcelJs.Workbook();
      await wb.xlsx.readFile(FILE_PATH).then(() => {
        var ws = wb.getWorksheet(1);
        ws.getCell("H4").value = "fkfk";
      });

      return wb.xlsx.write(ctx.res);
    },
  });

Frontend:

function Print() {

  const xlsxMutation = trpc.useMutation(['guest.xlsx'])

  const onDownload = React.useCallback(()=>{
    xlsxMutation.mutate({
      id:"test"
    })
  },[xlsxMutation])


  return (
    <>
    <button onClick={()=>handleClickOpen()}>download</button>
   
    </>
   
  );
}

CodeSandBox

the codesandbox not install ExcelJS yet, because I'm not sure why the error show up.
anyway, it simulated my code structure.

is there anyone using NextJS tRPC and ExcelJS share the code.

##edit
since the xlsx file already exist (FILE_PATH), I should something like ctx.res.pipe() right? but how??

Answer 1

not sure this approach is right or not, read the document about Blob ,then understand how server and client Transformers data like image or pdf files and so on, so I choose this way.

tRPC:

  .mutation("xlsx", {
    input: z.object({
      id: z.string(),
    }),
    resolve: async ({ ctx }) => {

      const wb = await new ExcelJs.Workbook();
      await wb.xlsx.readFile(PUBLIC_FILE_PATH).then(() => {
        var ws = wb.getWorksheet(1);
        ws.getCell("H4").value = "OKM";
      });



      await wb.xlsx.writeFile(PUBLIC_FILE_PATH);

      const stream = fs.readFileSync(PUBLIC_FILE_PATH);

      return {xxx:stream.toString("base64")}

     
    },
  });

frontend:

function Print() {
  const xlsxMutation = trpc.useMutation(["guest.xlsx"]);

  const onDownload = React.useCallback(() => {
    xlsxMutation.mutate({
      id: "test",
    });
  }, [xlsxMutation]);
  const [open, setOpen] = React.useState(true);

  const handleClickOpen = () => {
    onDownload();
    if (!xlsxMutation.data) return;
    const mediaType =
      "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,";

    window.location.href = `${mediaType}${xlsxMutation.data.xxx}`;
  };

  const handleClose = () => {
    setOpen(false);
  };
  return (
    <>
      <button onClick={() => handleClickOpen()}>Download</button>
    </>
  );
}

basically, the router side sends back the excel file converted to base64, fontend got the base64 data to convert back to xlsx file.

refer to :

1. enter link description here
2. Downloading Excel .xlsx saves base64 string in file

Answer 2

Similar to @Balius's method, mine take advantage of useQuery property of enabled + saveAs npm library:

import { saveAs } from "file-saver";

const Questionnaires: NextPage = () => {
  const [exportToDocxClicked, setExportToDocxClicked] = useState('');
  const docxBlobQuery = trpc.form.report.useQuery(
    { id: exportToDocxClicked },
    { enabled: !!exportToDocxClicked }
  );
  
  useEffect(() => {
    if (docxBlobQuery.isSuccess) {
      const blob = new Blob(
        [docxBlobQuery.data?.buffer],
        {type: 'application/vnd.openxmlformats-officedocument.wordprocessingml.document'}
      );
      saveAs(blob, 'test.docx');
      setExportToDocxClicked('');
    }
  }, [docxBlobQuery]);
  
  
  const exportToDocx = (id: string) => {
    setExportToDocxClicked(id);
  }

  ...
  <Button onClick={() => exportToDocx(id)} />
...

Then in the tRPC router just create the file and return the buffer, in this case I use docx-templates package to create report in docx buffer (just string)

import createReport from 'docx-templates';

  report: protectedProcedure
  .input(
    z
      .object({
        id: z.string().optional(),
      })
  )
  .query(async ({ ctx, input }) => {

    try {
      const template = await fs.readFile(templatesDirectory + '/test_template.docx');
      
      const buffer = await createReport({
        template
      });
      
      return {
        buffer
      };
    } catch (error) {
      console.error(error);
      throw new TRPCError({
        code: 'INTERNAL_SERVER_ERROR',
        message: 'An unexpected error occurred, please try again later.',
        // optional: pass the original error to retain stack trace
        cause: error,
      });
    }
  }),

I recommend "XLSX" npm package to export file, it has more options and it worked. Whereas exceljs didn't work for me, even if I follow the guide to a T.