Check xlsx-renderer browser support

[Siemienik]

Working code provided here: https://github.com/Siemienik/XToolset/issues/93#issuecomment-732309383

TODO:

• [x] #135 Add function to load directly from ArrayBuffer
• [ ] Add at least one example with xlsx-renderer runned on browser + test

[watanabethais]

Hi! I'm really interested in using your library in my React project. Do you have plans to support React?

[Siemienik]

@watanabethais ofc yes, I'm going to check all most popular frontend frameworks - probably for this I need to add a bundler like webpack. However in the case of React I suppose that xlsx-renderer should works (React apps usually uses babel which should bundle and compile properly code from installed node_module - but I hasn't checked it yet and any test to prove browser supports isn't existed yet. I will add it soon. )

Additionally ExcelJS (the only one dependency here) works pretty in browsers, so it shouldn't make problem here.

If did you do any attempts to use it under React, I will be really happy for feedback 🤓

[watanabethais]

@Siemienik Thanks for the quick response!

Actually I tried to use the library in my React project, but I don't know if I'm using correctly because it gives me the error: TypeError: Cannot read property 'F_OK' of undefined

After researching, I've found this error in the exceljs/exceljs repository. But I don't know how to solve or what I'm doing wrong...

[Siemienik]

@watanabethais could I ask you to create (or link) issue from exceljs - I will investigate it

[Siemienik]

@watanabethais please try to use this workaround for this moment, it has chance to works 🤞

import {Renderer} from 'xlsx-renderer';
const renderer = new Renderer();

const viewModel = { awesome:"Oh yeah!", items:[/*...*/] };

const resultWorkbook = await renderer.render(()=>{
     /** read file following exceljs doc: @see https://github.com/exceljs/exceljs#reading-xlsx */
     return workbook.xlsx.load(data.buffer).catch();
}, viewModel);

[watanabethais]

@Siemienik I really appreciate your help! But...

I don't know where data.buffer comes from... how do I get a local file in my project to load from buffer? There's a way to load the file without uploading the file?

[Siemienik]

There's a way to load the file without uploading the file?

Browsers hasn't access to local files (as long as their aren't hosted - then it is able to getting file by using fetch).

If is acceptable for you to upload a file, this post may be helpful for you: https://github.com/exceljs/exceljs/issues/832#issuecomment-495455990

[Siemienik]

@watanabethais I've just upgraded ExcelJS to 4.2.0, this should resolve your issues

[watanabethais]

@Siemienik Sorry for the late response, I only had time to test today. And I made it work using the code below:

import { Renderer } from "xlsx-renderer";
import { Workbook } from "exceljs";
import { saveAs } from "file-saver";

...

async function onRetrieveTemplate() {
    return fetch("./hts-template.xlsx").then((r) => r.blob());
}

async function generateReport() {
    onRetrieveTemplate().then((xlsxBlob) => {
      const reader = new FileReader();
      reader.readAsArrayBuffer(xlsxBlob);
      reader.addEventListener("loadend", async (e) => {
        const renderer = new Renderer();
        const workbook = new Workbook();
        const viewModel = { /* data */ };

        const result = await renderer.render(() => {
          return workbook.xlsx.load(reader.result).catch();
        }, viewModel);

        await result.xlsx.writeBuffer()
          .then((buffer) => saveAs(new Blob([buffer]), `${Date.now()}_result_report.xlsx`))
          .catch((err) => console.log("Error writing excel export", err));
      });
    });
 }

Thank you so much for your help! Now I'm going to fully implement the template :)

[Siemienik]

@watanabethais that great information 😄 Thank you for an information 🥇

[jacekkoziol]

I was trying to use the xlsx-renderer in the Browser but it works only partially, I mean the XLSX generates base on the template with provided data, but when it comes to loop it stops interpret the variables. For testing I have used this sample: https://github.com/Siemienik/XToolset/tree/master/packages/xlsx-renderer/tests/integration/data/Renderer005-ForEach-simple and as result I'm getting something like this:

Any idea how to solve the problem?

[Siemienik]

@jacekkoziol Jacek, Thank you for checking it. I'm going to investigate it soon.

Could I please you for sharing your code here?

[jacekkoziol]

@Siemienik Sure, the testing code is available here XSLX test And here is the TypeScript code:

Typescript code

```typescript import { Renderer } from 'xlsx-renderer'; import * as Excel from 'exceljs'; export const VM1: object = { projects: [ { name: 'ExcelJS', role: 'maintainer', platform: 'github', link: 'https://github.com/exceljs/exceljs', stars: 5300, forks: 682, }, { name: 'xlsx-import', role: 'owner', platform: 'github', link: 'https://github.com/siemienik/xlsx-import', stars: 2, forks: 0, }, { name: 'xlsx-import', role: 'owner', platform: 'npm', link: 'https://www.npmjs.com/package/xlsx-import', stars: 'n.o.', forks: 'n.o.', }, { name: 'xlsx-renderer', role: 'owner', platform: 'github', link: 'https://github.com/siemienik/xlsx-renderer', stars: 1, forks: 0, }, { name: 'xlsx-renderer', role: 'owner', platform: 'npm', link: 'https://www.npmjs.com/package/xlsx-renderer', stars: 'n.o.', forks: 'n.o.', }, { name: 'TS Package Structure', role: 'owner', platform: 'github', link: 'https://github.com/Siemienik/ts-package-structure', stars: 2, forks: 0, }, ], }; export class GenerateXLSXFile { constructor(private templateName: string, private viewModel: any, private buttonId: string) { const btn: HTMLElement | null = document.getElementById(buttonId); console.log('Init'); if (btn) { btn.addEventListener('click', () => { console.log(`Button ID: ${this.buttonId} clicked`); this.exportXLSX() }, false) } } public async onRetrieveTemplate(): Promise { return fetch(`./xlsx-templates/${this.templateName}`).then((r: Response) => r.blob()); } public async exportXLSX(): Promise { console.log('exportXLSX view model:: this.viewModel'); try { const xlsxBlob: Blob = await this.onRetrieveTemplate(); const reader: FileReader = new FileReader(); reader.readAsArrayBuffer(xlsxBlob); reader.addEventListener('loadend', async (e: ProgressEvent) => { if (reader.result instanceof ArrayBuffer) { const renderer: Renderer = new Renderer(); const workbook: Excel.Workbook = new Excel.Workbook(); await workbook.xlsx.load(reader.result); const result: Excel.Workbook = await renderer.render(() => Promise.resolve(workbook), this.viewModel); const buffer: Excel.Buffer = await result.xlsx.writeBuffer() this.saveBlobToFile(new Blob([buffer]), `${Date.now()}_result_report.xlsx`); } }); } catch (err) { console.log('Error:', err); } } // Utilities - File Save // --------------------------------------------------------------------------- private saveBlobToFile(blob: Blob, fileName: string = 'File.xlsx'): void { const link: HTMLAnchorElement = document.createElement('a'); const url: string = window.URL.createObjectURL(blob); link.href = url; link.download = fileName; link.target = '_blank'; document.body.appendChild(link); link.click(); link.remove(); setTimeout(() => { window.URL.revokeObjectURL(url); }, 4000); } } // Initialize // ----------------------------------------------------------------------------- new GenerateXLSXFile('template.xlsx', VM1, 'exportFile1'); new GenerateXLSXFile('template-hyperlink.xlsx', VM1, 'exportFileHyperlink'); ```

And there is another issue - The Error is thrown if there is a hyperlink in the template (You can see it trying to 'Export File With Hyperlink').

[Siemienik]

@jacekkoziol I have good information for you. I found the reason why it failed. XLSX renderer needs an argument called templateFactory, which returns Promise<Workbook>. In your code, this argument is an arrow function that returns a const wrapped inside Promise.resolve(). That makes that the renderer edits output, and it is the same object as the template. As a result, it makes total crazy things 😄.

Moving loading file logic into a function resolves that problem. I've created PR into your code: https://github.com/jacekkoziol/xlsx/pull/1/files#diff-a2a171449d862fe29692ce031981047d7ab755ae7f84c707aef80701b3ea0c80R94-R97 .

Additionally, notes/tips for you.

• The Renderer is stateless. It was designed to be a service possible to inject into Constructor (inversion of control). So I recommend to pass it into GenerateXLSXFile through the constructor or create it in a constructor (when you want to create a wrapper for it).
• If any problems have you with the renderer, You may pass the CellTemplateDebugPool into Renderer constructor, which makes it log information about what it is doing.

I am delighted that you asked me about this. It allowed me to find some possible improvements that I want to do for xlsx-renderer :smile: Thank you. I made some notes in the code with todo @siemienik, please just ignore these.

Additionally, your code proves that the lib works in a browser, that I am really thank you for doing that :)

Information for others, that is what I changed in a code:

    const workbook: Excel.Workbook = new Excel.Workbook();
    await workbook.xlsx.load(reader.result);
    const result: Excel.Workbook = await renderer.render(() => Promise.resolve(workbook), this.viewModel);

into:

      const templateFileBuffer = reader.result;

      const templateFactory = () => { // All this logic must be provided into xlsx-renderer as a function
        const workbook: Excel.Workbook = new Excel.Workbook();
        return workbook.xlsx.load(templateFileBuffer);
      };

      const result: Excel.Workbook = await this.renderer.render(templateFactory, this.viewModel);

[jacekkoziol]

@Siemienik Thank you for the answer, code update and tips. I have test it and it work... but only for the first time. Probably still there is something wrong with my code, but I can't find out what is causing the problem. When I export the file, for the first time it's generated correctly, however the second and subsequent times it's not generating properly - the page needs to be reloaded and then the first file generation goes smoothly.

[Siemienik]

Ohh this is a bug inside Renderer, which mutate view model, it shouldn't be. For now, this is quick workaround by making deep copy:

 const vmClone = JSON.parse(JSON.stringify(this.viewModel));
 const result: Excel.Workbook = await this.renderer.render(templateFactory, vmClone);

I'm going to create a patch through the weekend.

[jacekkoziol]

Thank you @Siemienik , it works perfectly 👍 The xlsx-renderer is great! :)

[Siemienik]

@jacekkoziol Nice to read that, so if you want you to support our work, please leave a star & recommend us to your friends 🥰 Additionally, I want you to invite you to use our community chat https://gitter.im/Siemienik/community .

[Siemienik]

Ohh this is a bug inside Renderer, which mutate view model, it shouldn't be.

Problem fixed in #138 and released in xlsx-renderer@2.3.2

[Siemienik]

I am delighted to inform you that the version has been released (xlsx-renderer v2.3.3), with added renderFromArrayBuffer.

// for browsers:
const result2 = await renderer.renderFromArrayBuffer(templateArrayBuffer, viewModel);
await result2.xlsx.writeBuffer().then(/* use saveAs() to download on a browser */);

Using renderFromArrayBuffer will makes using the xlsx-renderer much more convenient in a browser 🎯

[sumanth-basetty]

@Siemienik I was trying to use xlsx-renderer and i couldn't get the file generated, getting the below error Can't find end of central directory : is this a zip file ? If it is, see https://stuk.github.io/jszip/documentation/howto/read_zip.html Codesandbox: https://codesandbox.io/s/elated-diffie-hukqr i am following the steps provided in this thread, couldn't get it done using template and viewModel data from : Renderer016-ForEach-merged-pyramid

[jacekkoziol]

@sumanth-basetty move your template.xlsx file to the public folder and this should solve the problem :) And you need to update your template or viewModel to correctly generate the file. In your current model, there are no such fields: name, weight, price but x, y, z (which are nested in the arrays of properties set1, set2, set3 - so you need to create for each of these separate #!FOR_EACH statement, or just before passing the data to the renderer just concatenate all this arrays into one array).

[sumanth-basetty]

@jacekkoziol Thanks for pointing it out, i have changed the viewModel according to the template.xlsx and moved the template to public folder still get the same error as mentioned Error Can't find end of central directory : is this a zip file ? If it is, see https://stuk.github.io/jszip/documentation/howto/read_zip.html missing something, but not sure what it is, can you help me out on this

[jacekkoziol]

@sumanth-basetty The path to the template file needs to be ./template.xlsx instead of ../public/template.xlsx In App.js file, line 37.

[Siemienik]

@jacekkoziol, Thank you for resolving this problem quickly🥇 Good Job 😄

@sumanth-basetty I hope that XToolset will help you a lot with generating awesome spreadsheets. We are really open to proposals for the next features to add. If any help you need or have you any trouble write it out as a new issue or join to talk on our community chat on Gitter 😄

[sumanth-basetty]

@jacekkoziol thanks for pointing it out, it works fine now @Siemienik Cool stuff, will check it out no time wasting in styling, just generate the file, xlsx-renderer is awesome :)

[sumanth-basetty]

@Siemienik @jacekkoziol I have an issue with the renderer in the template, i have 1 row empty between header and data, xlsx generated is not as expected

Expected output(need the output file in this template) :
headers
empty row
data1
data2
data3
data4
data5
data6

Actual output:
headers
empty row
data1
data2
empty row
data3
data4
empty row
data5
data6

i get this empty row in between the data codesandbox: https://codesandbox.io/s/xlsx-renderer-check-y1jdx template.xlsx, Error-output.xlsx, Expected-output.xlsx all these files are uploaded in codesandbox can you help me on this

[Siemienik]

That happens because you have #! END_ROW between #! FOR_EACH and #! CONTINUE. Please try to:

• move FOR_EACH from B1 to B2,
• and END_ROW from B2 to B1

[sumanth-basetty]

@Siemienik got it, now i get the flow how to use those generics, works fine now 😃, thank you 👍

[sumanth-basetty]

@Siemienik why the file isn't generating when the template is out public, i have made the template out of public folder and updated the path in fetch, it doesn't generate the file it only generates when the template is in public folder codesandbox: https://codesandbox.io/s/stupefied-mirzakhani-h88n7

[Siemienik]

@Siemienik why the file isn't generating when the template is out public, i have made the template out of public folder and updated the path in fetch, it doesn't generate the file it only generates when the template is in public folder

It isn't a behaviour of XToolset/xlsx-renderer That happens because react dev server allows be public only files from that folder.

For the same purpose, your main file(index.html) is placed right there.

[Siemienik]

Here I update the code to make it simpler and shorter:

import { Renderer } from "xlsx-renderer";
import { saveAs } from "file-saver";

// ... define viewModel:
const viewModel = {}; 

//... generate a report:

// 1. Download a template.
fetch("./template.xlsx")
  // 2. Get template as ArrayBuffer.
  .then((response) => response.arrayBuffer())
  // 3. Fill the template with data (generate a report).
  .then((buffer) => new Renderer().renderFromArrayBuffer(buffer, viewModel))
  // 4. Get a report as buffer.
  .then((report) => report.xlsx.writeBuffer())
  // 5. Use `saveAs` to download on browser site.
  .then((buffer) => saveAs(new Blob([buffer]), `${Date.now()}_report.xlsx`))
  // Handle errors.
  .catch((err) => console.log("Error writing excel export", err));

Sandbox: https://codesandbox.io/s/xlsx-renderer-check-forked-xp91b?file=/src/App.js:233-836

[justice-sh]

Hello,

I've ran this code and on my system, this is the error I got:

Error: Can't find end of central directory : is this a zip file ? If it is, see https://stuk.github.io/jszip/documentation/howto/read_zip.html

And no, it's not a zip file. I get this error when I ran it with React, but with NodeJs, I don't get this error.

[jacekkoziol]

@Sherlock-HolmesJM Probably the paths to the xlsx template file is wrong.

[Siemienik]

Hi, @Sherlock-HolmesJM did you resolve this problem?

[justice-sh]

Hi, @Sherlock-HolmesJM did you resolve this problem?

I did by setting up a REST API to serve my template xlsx file as a buffer

Then fetch at the frontend and worked with it.

Thank you.

[Siemienik]

@Sherlock-HolmesJM Thank you for confirmation, nice to read that ;)

If any help needed, you may find us on Gitter