Comments (37)
@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 :)
from xtoolset.
@sumanth-basetty The path to the template file needs to be ./template.xlsx
instead of ../public/template.xlsx
In App.js
file, line 37.
from xtoolset.
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.
from xtoolset.
@watanabethais could I ask you to create (or link) issue from exceljs - I will investigate it
from xtoolset.
@Siemienik Sure, the testing code is available here XSLX test
And here is the TypeScript code:
Typescript code
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<Blob> {
return fetch(`./xlsx-templates/${this.templateName}`).then((r: Response) => r.blob());
}
public async exportXLSX(): Promise<void> {
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<FileReader>) => {
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').
from xtoolset.
Thank you @Siemienik , it works perfectly π The xlsx-renderer is great! :)
from xtoolset.
Ohh this is a bug inside Renderer, which mutate view model, it shouldn't be.
Problem fixed in #138 and released in [email protected]
from xtoolset.
@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
from xtoolset.
@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).
from xtoolset.
@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
from xtoolset.
@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 π
from xtoolset.
@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 :)
from xtoolset.
@Siemienik got it, now i get the flow how to use those generics, works fine now π, thank you π
from xtoolset.
@Sherlock-HolmesJM Probably the paths to the xlsx template file is wrong.
from xtoolset.
Hi, @Sherlock-HolmesJM did you resolve this problem?
from xtoolset.
@Sherlock-HolmesJM Thank you for confirmation, nice to read that ;)
If any help needed, you may find us on Gitter
from xtoolset.
Hi!
I'm really interested in using your library in my React project.
Do you have plans to support React?
from xtoolset.
@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 π€
from xtoolset.
@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...
from xtoolset.
@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);
from xtoolset.
@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?
from xtoolset.
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:
exceljs/exceljs#832 (comment)
from xtoolset.
@watanabethais I've just upgraded ExcelJS to 4.2.0, this should resolve your issues
from xtoolset.
@watanabethais that great information π Thank you for an information π₯
from xtoolset.
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?
from xtoolset.
@jacekkoziol Jacek, Thank you for checking it. I'm going to investigate it soon.
Could I please you for sharing your code here?
from xtoolset.
@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
intoRenderer
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 π 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);
from xtoolset.
@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.
from xtoolset.
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.
from xtoolset.
@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 .
from xtoolset.
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 π―
from xtoolset.
@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
from xtoolset.
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
from xtoolset.
@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
from xtoolset.
@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.
from xtoolset.
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
from xtoolset.
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.
from xtoolset.
Related Issues (20)
- [BUG] xlsx-renderer: When iterates on empty collection it leaves garbages HOT 5
- Add TemplateStringCell: Allow to mix dynamic values (variables) with static values in the same cell. HOT 2
- [Draft] Consider to add If ... else statement support. HOT 1
- [BUG] The xlsx-renderer stops to work correctly after code compression.
- [DRAFT] Check bundlers
- Add TemplateFormulaCell : Possibility to add a template for a custom formula HOT 4
- [DRAFT] Describe how to extend xlsx-renderer for custom cell templates
- (xlsx-import) How can I read xlsx from S3 ? HOT 1
- Render data to specific worksheet HOT 1
- [Question] how to add images in the worksheet
- Do code-smells from Sonar Cloud
- Update vulnerable devDependency
- [BUG] #! DUMP_COLS should shift columns HOT 2
- Rich text support for cell templates
- Sample Example for Frontend: ReactJS + TS with Invoice Example HOT 5
- TypeError: range_1.default is not a constructor HOT 5
- γxlsx-rendererγThe chart in the template will be lost. HOT 1
- Incorrect render styles HOT 1
- Dates are output as strings in ISO format HOT 3
- xlsx-import should check if configured worksheet exists and return a suitable error if not HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
π Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. πππ
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google β€οΈ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from xtoolset.