Posted 21 August 2024, 5:37 pm EST - Updated 21 August 2024, 7:12 pm EST
Hi,
I am trying to convert an excel buffer data to spread js buffer in NodeJs environment hosted on AWS lambda. I have the Spread Js license correctly set and I have all the global variables set along with canvas. I’m encountering ‘Incorrect file format’ when trying to import the blob to workbook. Here is the code snippet:
import { InvokeCommand, LambdaClient, LogType } from "@aws-sdk/client-lambda";
import axios from "axios";
import fileReader from "filereader";
import FormData from "form-data";
import { JSDOM } from "jsdom";
import mockCanvas from "./stubCanvas";
const https = require("https");
const { window } = new JSDOM();
mockCanvas(window);
global.self = global;
global.window = window;
global.document = window.document;
global.navigator = window.navigator;
global.HTMLCollection = window.HTMLCollection;
global.HTMLElement = window.HTMLElement;
global.customElements = window.customElements;
global.FileReader = fileReader;
global.canvas = window.canvas;
const spreadJsMajorVersion: number = 17;
const hostname: string = "hostname";
import GC from "@mescius/spread-sheets";
import '@mescius/spread-sheets-io';
// license is set correctly here, verified that the license is active
function setSpreadJSLicenseKey(): void {
const spreadJSLicense = AtxSpreadJsLicenses?.[spreadJsMajorVersion]?.[hostname];
GC.Spread.Sheets.LicenseKey = spreadJSLicense;
}
async function downloadFileFromPresignedUrl(downloadUrl: string): Promise<ArrayBuffer> {
try {
const response = await axios({
method: 'GET',
url: downloadUrl,
responseType: 'arraybuffer',
httpsAgent: https.Agent({ keepAlive: true }),
});
console.log("download file response", response.status)
return response.data;
} catch (err) {
console.log("error while downloading file", err);
throw err;
}
}
// The buffer downloaded from downloadFileFromPresignedUrl() is passed here
async function convertXlsxBufferToSjs(buffer: ArrayBuffer) {
// create a new workbook
const workbook = new GC.Spread.Sheets.Workbook();
// import array buffer data to a Blob
const blob = new Blob([buffer], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
});
console.log("entering workbook import");
return new Promise<Blob>((resolve, reject) => {
workbook.import(
//@ts-expect-error Blob should also work when importing using SpreadJs
blob,
() => {
workbook.save(
async (blob: Blob) => {
resolve(blob);
},
(e: unknown) => {
console.log("Error saving spreadsheet");
console.error(e);
reject(e);
},
{
includeEmptyRegionCells: false,
includeCalcModelCache: true,
}
);
},
(e: unknown) => {
console.log("Error loading blob, entered failure callback", e);
reject(e);
},
{
fileType: GC.Spread.Sheets.FileType.excel,
doNotRecalculateAfterLoad: true,
calcOnDemand: true,
}
);
});
}Here the workbook.import() calls the failure callback with “Error loading blob, entered failure callback { errorCode: 1, errorMessage: ‘Incorrect file format.’ }”
Considering alternatives, when I try to use ExcelIO.open() to load workbook, I am getting error “Error: cannot read as File: {}”:
"stack": [
"Error: cannot read as File: {}",
" at readFile (/var/task/node_modules/filereader/FileReader.js:266:15)",
" at FileReader.self.readAsArrayBuffer (/var/task/node_modules/filereader/FileReader.js:286:7)",
" at e.loadFile (/var/task/node_modules/@mescius/spread-excelio/dist/gc.spread.excelio.min.js:34:19238)",
" at e.open (/var/task/node_modules/@mescius/spread-excelio/dist/gc.spread.excelio.min.js:30:16314)",
" at json (/var/task/dist/app.js:115:21)",
" at new Promise (<anonymous>)",
" at /var/task/dist/app.js:114:28",
" at new Promise (<anonymous>)",
" at convertXlsxBufferToSjs (/var/task/dist/app.js:112:12)",
" at Runtime.handler (/var/task/dist/app.js:257:33)"with the code below:
async function convertXlsxBufferToSjs(buffer: ArrayBuffer) {
const workbook = new GC.Spread.Sheets.Workbook();
// Initialize Excel.IO for handling import/export
const excelIO = new ExcelIO.IO();
// Convert ArrayBuffer to Blob
const blob = new Blob([buffer], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
});
return new Promise<Blob>((resolve, reject) => {
excelIO.open(blob, (json) => {
workbook.fromJSON(json); // Load JSON into the workbook
// Save the workbook as SJS
workbook.save(
(savedBlob: Blob) => {
resolve(savedBlob);
},
(error) => {
console.error("Error saving the workbook as SJS:", error);
reject(error);
},
{
includeEmptyRegionCells: true,
saveR1C1Formula: true,
includeCalcModelCache: true,
}
);
}, (error) => {
console.error("Error importing the Blob into workbook:", error);
reject(error);
});
});
}Can you please help me resolve this issue, I have tried about everything here to unblock the issue. thanks.
