I’m developing an application using React in FrontEnd and Kotlin in Backend, but the problem is that i have to provide for the users an Excel file with their Template, i only need to put data in his template and send back to him, but the main libs to deal with that(free ones) like XLSX and EXCELJS don’t help to solve this problem, because when i generate the Excel, all the formulas, refs, styles are lost. With ExcelJs some styles are preserved, but the rest of the things, no.

There are another libs who help with that problem?

I know that Aspose Cloud can deal with that, but there another way? and i think the price for Aspose to solve this problem doesn’t worth it…

Thanks.

this is the code using the ExcelJS, in this example i’m using sharepoint to get the template and populate with data and send back(i get the msal token from kotlin)

import React, { useContext, useState, useEffect } from "react";
import { useMsal } from "@azure/msal-react";
import { Client } from "@microsoft/microsoft-graph-client";
import ExcelJS from "exceljs";
import { saveAs } from "file-saver";
import "isomorphic-fetch";
import { useGetMsalTokenQuery } from "../components/store/service/UsersData";
import * as XLSX from "xlsx";

const ModelInventarioCopy = () => {
  const { instance, accounts } = useMsal();
  const [fileId, setFileId] = useState(""); // Set this to your SharePoint file ID
  const [siteId, setSiteId] = useState(""); // Set this to your SharePoint site ID
  const [dataResultsTotal, setDataResultsTotal] = useState();
  const {
    data: dataToken,
    error: errorToken,
    isLoading: loadingToken,
  } = useGetMsalTokenQuery();

  useEffect(() => {
    if (dataToken) {
      setDataResultsTotal(dataToken);
    } else if (errorToken) {
      console.log(errorToken);
    }
  }, [dataToken, errorToken]);

  useEffect(() => {
    // Replace with actual file ID and site ID
    setFileId("idexample");
    setSiteId("idexample");
  }, []);

  const getAuthenticatedClient = (accessToken) => {
    return Client.init({
      authProvider: (done) => {
        done(null, accessToken); // First parameter is the error, second is the token
      },
    });
  };

  const downloadFileFromSharePoint = async () => {
    try {
      const client = getAuthenticatedClient(dataResultsTotal);
      const response = await client
        .api(`/sites/${siteId}/drive/items/${fileId}/content`)
        .responseType("arraybuffer")
        .get();

      await handleModifyExcel(response);
    } catch (error) {
      console.error("Error downloading file from SharePoint:", error);
    }
  };

  const handleModifyExcel = async (arrayBuffer) => {
    try {
      const workbook = new ExcelJS.Workbook();
      await workbook.xlsx.load(arrayBuffer);
      const worksheet = workbook.getWorksheet(1);

      if (!worksheet) {
        console.error("Worksheet not found.");
        return;
      }

      let nuPonto = 3;
      const listData = [
        {
          idEquipeEps: 1,
          noEncarregado: "Lucas B",
          dtOperacao: "01/09/2024",
          dtSituacao: "01/09/2024",
          cdMedicao: 123,
          dsDivisaoDois: "testenoDiv2",
          noDivisaoTres: "testenoDiv3",
          nuParcela: 111,
          nuLine: 1,
          nuTree: 1,
          idCodTreeOne: 1234,
          vlCapFirstLevel: 1,
          vlHeigthFirstLevel: 1,
          dsObservation: "nothing",
          noOperacao: "testenoopp",
          vlCapRated: 1,
          vlHeigthRated: 1,
        },
        {
          idEquipeEps: 2,
          noEncarregado: "Lucas C",
          dtOperacao: "01/09/2024",
          dtSituacao: "01/09/2024",
          cdMedicao: 123,
          dsDivisaoDois: "testenoDiv2",
          noDivisaoTres: "testenoDiv3",
          nuParcela: 111,
          nuLine: 1,
          nuTree: 1,
          idCodTreeOne: 12345,
          vlCapFirstLevel: 1,
          vlHeigthFirstLevel: 1,
          dsObservation: "wtf",
          noOperacao: "testenoopp",
          vlCapRated: 2,
          vlHeigthRated: 2,
        },
      ];

      // Update cells
      listData.forEach((e) => {
        if (e) {
          const row = worksheet.getRow(nuPonto);
          row.getCell(5).value = e.idEquipeEps || "";
          row.getCell(6).value = e.noEncarregado || "";
          row.getCell(7).value = e.dtOperacao || "";
          row.getCell(8).value = e.dtSituacao || "";
          row.getCell(10).value = e.cdMedicao || "";
          row.getCell(11).value = e.dsDivisaoDois || "";
          row.getCell(12).value = e.noDivisaoTres || "";
          row.getCell(13).value = e.nuParcela || "";
          row.getCell(14).value = e.nuLine || "";
          row.getCell(15).value = e.nuTree || "";
          row.getCell(17).value = e.idCodTreeOne || "";
          row.getCell(18).value = e.vlCapFirstLevel || "";
          row.getCell(20).value = e.vlHeigthFirstLevel || "";
          row.getCell(23).value = e.dsObservation || "";
          row.getCell(24).value = e.noOperacao || "";
          row.getCell(25).value = e.vlCapRated || "";
          row.getCell(26).value = e.vlHeigthRated || "";
          row.commit();
        }
        nuPonto += 1;
      });

      // Save the updated workbook to a blob and allow the user to download it
      const buffer = await workbook.xlsx.writeBuffer();
      const blob = new Blob([buffer], {
        type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      });
      saveAs(blob, "UpdatedTemplate.xlsx");
    } catch (error) {
      console.error("Error modifying the Excel file:", error);
    }
  };


  return (
    <div>
      <button onClick={downloadFileFromSharePoint}>
        Download and Modify File
      </button>
    </div>
  );
};

export default ModelInventarioCopy;