SheetJS: write method returns blank sheets when sending data to FormStack #3029

New Issue

antonioOrtiz · 2023-11-16T20:37:11Z

antonioOrtiz commented

2023-11-16 20:37:11 +00:00

I am making a API call to FormStack which has a xlsx template I am using to pass some data to.

The idea is I am sending different data to this template, and each time I do that operation, that data will populate a new sheet. At the end I should have a excel document with n amount of sheets. e.g. [ { sheet-1-data },{ sheet-2-data } ] => spits out excel doc with two sheets with each sheet styled/based on template living in FormStack.

My problem is while it generates a file generated with two sheets, each tab is blank.

I am using Nest.js, And this is my service which is processing the Buffer.

@Injectable()
export class DSDocExcelProcessorService {
	constructor(
		private readonly logService: DSLogService,
		private readonly auditService: AuditService
	) {}
	async processExcelDocs(contentArr: { order: number; fileContent: Buffer }[] = [], docGenReq: DSDocGenerateRequestModel): Promise<Buffer> {
		try {
			this.logService.info(
				`Started processing Excel generation for Request Id ${DSRequestContextService.getCurrentReqId()}. Total Excel docs to be processed : ${
					docGenReq.templates.length
				}`
			);
			const finalXLSX: WorkBook = await utils.book_new();
			let xlsx: Buffer;

			for await (xlsx of orderBy(contentArr, 'order').map((item) => item.fileContent)) {

				const workbook: WorkBook = read(xlsx, { type: 'buffer' });

				utils.book_append_sheet(finalXLSX, workbook);
			}
			this.logService.info(`Successfully uploaded file ${docGenReq.outputFile} to ${docGenReq.destinationBucket}`);

			return write(finalXLSX, { type: 'buffer', bookType: 'xlsx' });
		} catch (error) {
			this.logService.error(error);
			await this.auditService.updateRequestStatus(DSRequestContextService.getCurrentReqId(), EDSRequestStatus.ERROR, true, [error.message]);
			throw error;
		}
	}
}

Any thoughts as to why I am getting no template of data for it in each sheet?

Any help would be appreciated!

I am making a API call to [FormStack][1] which has a `xlsx` template I am using to pass some data to. The idea is I am sending different data to this template, and each time I do that operation, that data will populate a new sheet. At the end I should have a excel document with `n` amount of sheets. e.g. `[ { sheet-1-data },{ sheet-2-data } ] => spits out excel doc with two sheets with each sheet styled/based on template living in FormStack`. My problem is while it generates a file generated with two sheets, each tab is blank. I am using Nest.js, And this is my service which is processing the Buffer. @Injectable() export class DSDocExcelProcessorService { constructor( private readonly logService: DSLogService, private readonly auditService: AuditService ) {} async processExcelDocs(contentArr: { order: number; fileContent: Buffer }[] = [], docGenReq: DSDocGenerateRequestModel): Promise<Buffer> { try { this.logService.info( `Started processing Excel generation for Request Id ${DSRequestContextService.getCurrentReqId()}. Total Excel docs to be processed : ${ docGenReq.templates.length }` ); const finalXLSX: WorkBook = await utils.book_new(); let xlsx: Buffer; for await (xlsx of orderBy(contentArr, 'order').map((item) => item.fileContent)) { const workbook: WorkBook = read(xlsx, { type: 'buffer' }); utils.book_append_sheet(finalXLSX, workbook); } this.logService.info(`Successfully uploaded file ${docGenReq.outputFile} to ${docGenReq.destinationBucket}`); return write(finalXLSX, { type: 'buffer', bookType: 'xlsx' }); } catch (error) { this.logService.error(error); await this.auditService.updateRequestStatus(DSRequestContextService.getCurrentReqId(), EDSRequestStatus.ERROR, true, [error.message]); throw error; } } } Any thoughts as to why I am getting no template of data for it in each sheet? Any help would be appreciated! [1]: https://www.webmerge.me/developers/documents#developer_resources

sheetjs commented

2023-11-16 21:14:52 +00:00

Most likely the error is here:

				const workbook: WorkBook = read(xlsx, { type: 'buffer' });

				utils.book_append_sheet(finalXLSX, workbook);

You need to pull and append individual sheets:

				workbook.SheetNames.forEach(n => utils.book_append_sheet(finalXLSX, workbook.Sheets[n], n, true));

The true argument ensures that worksheet names are deduplicated.

Most likely the error is here: ```js const workbook: WorkBook = read(xlsx, { type: 'buffer' }); utils.book_append_sheet(finalXLSX, workbook); ``` You need to pull and append individual sheets: ```js workbook.SheetNames.forEach(n => utils.book_append_sheet(finalXLSX, workbook.Sheets[n], n, true)); ``` The `true` argument ensures that worksheet names are deduplicated.

sheetjs closed this issue

2023-11-16 21:14:52 +00:00

antonioOrtiz commented

2023-11-16 22:41:51 +00:00

Thanks so much! The templates got generated!

Sign in to join this conversation.