Uploading data into an Excel Spreadsheet.

[hyperliskdev]

What version of PnPjs library you are using

3.x

Minor Version Number

3.19.0

Target environment

All

Additional environment details

Currently performing testing locally on the workbench.

Question/Request

I am currently trying to add data into a sheet. I assumed that the function from getFileByServerRelativePath().

await getSP()
    .web.getFileByServerRelativePath(url)
    .getBuffer()
    .then((buffer) => {
      let workbook = new Excel.Workbook();
      workbook.xlsx.load(buffer).then((workbook) => {
        let worksheet = workbook.getWorksheet("sheet_name");

        workbook.xlsx.writeBuffer().then((buffer) => {
          getSP().web.getFileByServerRelativePath(url).setContent(buffer);
        });
      });
    });

This block of code works perfectly fine for reading data but as soon as I try to set the content of a changed worksheet, it changes the MIME-TYPE to "application/zip" and the workbook changes state to a "read-only" mode.

Another part of this error is that the reason Excel gives for being in a read-only mode and "repairing" itself is this AutoFilter table change.

I want to continue to use tables in the excel files that are being edited and removing the tables and reverting them back to their regular ranges removes the issue.

How can I ensure that the mime-type stays the same and that the workbook does not "break" and enter the read-only state?

[patrick-rodgers]

what does excel's writeBuffer() produce?

[hyperliskdev]

writeBuffer provides a Promise<Buffer> that contains the edits made to the workbook.

They seem to use their own type for Buffer that extends ArrayBuffer, there is a stack overflow question talking about how this excelJS.Buffer breaks the excel file. Athough there isnt a set solution that I have tested from this aspect.

[hyperliskdev]

writeBuffer provides a Promise<Buffer> that contains the edits made to the workbook.

They seem to use their own type for Buffer that extends ArrayBuffer, there is a stack overflow question talking about how this excelJS.Buffer breaks the excel file. Athough there isnt a set solution that I have tested from this aspect.

It doesnt look like this solution works in either casting to any or casting to plain JS Buffer.

[hyperliskdev]

writeBuffer(); seems to output a Uint8Array

[hyperliskdev]

_testingExcel = async (): Promise<void> => {
    const url =
      "______/something.xlsx";

    let workbook = new Excel.Workbook();

    await this._sp.web
      .getFileByServerRelativePath(url)
      .getBuffer()
      .then((buffer) => {
        console.log(buffer);                     // <-- 1
        workbook.xlsx.load(buffer);
        console.log(workbook);              // <-- 2
      });

    let excelBuffer = await workbook.xlsx.writeBuffer();
    console.log(excelBuffer);                 // <-- 3

    let setContent =  await this._sp.web.getFileByServerRelativePath(url).setContent(excelBuffer);
    console.log(setContent);                 // <-- 4
  };

[hyperliskdev]

This section of code is from the ExcelJS load function that I am using. This section could be the culprint for why the MIME-TYPE changes to "application/zip".

It could also be related to this section here because the Content-Type header is not set and sharepoint may default to application/zip.

[hyperliskdev]

This seems to be an issue with ExcelJS, I will close this now as this has appeared here.

[github-actions]

This issue is locked for inactivity or age. If you have a related issue please open a new issue and reference this one. Closed issues are not tracked.