natergj / excel4node Goto Github PK
View Code? Open in Web Editor NEWNode module to allow for easy Excel file creation
License: MIT License
Node module to allow for easy Excel file creation
License: MIT License
Hi ,
Great work !
How can i use an existing file as a template for all style and only add the data and formulas to the spreadsheet ?
Tx.
Idan
XML tag appears to be ignored by Excel
first, i'd like to say great job, because this module has the cleanest syntax i've seen and it doesn't use a ridiculous amount of CPU or RAM like some other modules which shall remain nameless.
may i request changing the compression type for JSZip from default 'STORE' to 'DEFLATE'? or alternatively, adding the abiility to pass in a config object for JSZip file() method, or a flag to switch compression types?
example:
xlsx.file("[Content_Types].xml",Content_TypesXML.end(xmlOutVars), {compression: 'DEFLATE'});
xlsx.file("[Content_Types].xml",Content_TypesXML.end(xmlOutVars), this.jszipOpts);
xlsx.file("[Content_Types].xml",Content_TypesXML.end(xmlOutVars), {compression: this.compression});
i would be more than happy to make a pull request if you would let me know your preference!
ws.setValidation({
type: "list",
allowBlank: 1,
showInputMessage: 1,
showErrorMessage: 1,
sqref: "X2:X10",
formulas: [
'value1,value2'
]
});
This code segment explains how to apply a list validation to a certain cell which is really helpful. But is it possible to adjust the formula to refer to a range of cells from a different sheet or at least the same sheet?
Thanks in advance
Nice tool! I've had some success writing excel files, but for some reason xlrd seems unable to find the data in certain cells.
For example, if you run the sample script and then run the runxlrd.py script (sudo pip install xlrd), you see the cells are empty:
$ runxlrd.py show Excel.xlsx
=== File: Excel.xlsx ===
Open took 0.01 seconds
BIFF version: (not BIFF); datemode: 0
codepage: None (encoding: None); countries: (0, 0)
Last saved by: u''
Number of data sheets: 2
Use mmap: 0; Formatting: 0; On demand: 0
Ragged rows: 0
Load time: -1.00 seconds (stage 1) -1.00 seconds (stage 2)
sheet 0: name = u'my worksheet'; nrows = 2; ncols = 5
cell A1: type=1, data: u'Cell A1'
cell B1: type=1, data: u'Cell B1'
cell C1: type=1, data: u'newValue'
cell D1: type=1, data: u'newValue'
cell E1: type=1, data: u'newValue'
cell A2: type=2, data: 100.0
cell B2: type=0, data: ''
cell C2: type=0, data: ''
cell D2: type=0, data: ''
cell E2: type=1, data: u'2ndValue'
sheet 1: name = u'my 2nd worksheet'; nrows = 2; ncols = 4
cell A1: type=0, data: ''
cell B1: type=0, data: ''
cell C1: type=0, data: ''
cell D1: type=1, data: u'cell data'
cell A2: type=2, data: 5.0
cell B2: type=2, data: 10.0
cell C2: type=0, data: '' Why is this empty?
cell D2: type=0, data: '' Why is this empty?
command took 0.00 seconds
Could you look into adding additional features to your library such as graphs and the ability to create data tables from columns and rows.
First of all i would thank you for an awesome library :) Is there any way of generating an excel with password protection. Also read an file with password given for those. Awaiting for your response author
The files generated by excel4node have some slight differences from files generated by Excel. After opening one of these files, Excel prompts to save unsaved changes.
Comparing the generated XML, I see that it's mostly minor things such as a missing encoding on the xml tag (encoding="UTF-8"
), missing newlines, or missing/rearranged parameters.
cell.Format.Font.Size(value) doesn't work because of invalid attribute name: 'size' instead of 'sz' Cell.js:340
cell.Format.Font.Alignment.Horizontal(align)
fails with error:
TypeError: Cannot read property 'cells' of undefined
at Object.formatter.font.alignment.Horizontal ([...]/excel4node/lib/Row.js:487:19)
Same thing with Vertical. Both methods reference this
, assuming it will be the formatter object, but they are children of the alignment
object, so this fails. Horizontal is also missing its val
argument.
The following lines also appear to be wrong, I think they should be tmpStyle.Font.Alignment...
https://github.com/natergj/excel4node/blob/master/lib/Row.js#L479
https://github.com/natergj/excel4node/blob/master/lib/Row.js#L493
I'd submit a PR but I'm not sure how you'd want to handle this since the only way I can see to remedy this without moving method definitions would be to somewhat hackily pass the formatter context down.
wb.write("My Excel File.xlsx",res);
Need a bit of clarification on how this can be sent via a node response to be downloaded. I am using Express js routing.
A sample code could help. Thanks in advance.
First of all why I cannot use 'landscape' orientation without page scaling.
Second, I would like to print column(s) and row(s) cross all pages
Found this while investigating #15, running my document through the OpenXML SDK 2.0 Validator:
The attribute 'rgb' has invalid value 'FF0000'. The actual length according to datatype 'hexBinary' is not equal to the specified length. The expected length is 4.
Comparing the Source file to a version that was opened and saved in Excel 2007, it looks like Excel07 fixes it by prepending 'FF' to all of my 6-digit hexadecimal colors (FF0000
becomes FFFF0000
, 2C68FA
becomes FF2C68FA
, etc). Not sure how other versions of Excel handle or react to this, however.
Sorry for my English; but I think I found a bug when trying to add multiple images; never enters the loop at line 220, and lines 238, 246 and 253, the rId is always rId1; all this in Images.js;
I fixed changing the loop with this imgID = ws.drawings.drawings.length;
and line 238, 246,253 with this 'rId'+imgID
hope you can fix it. thanks
When I try to send the newly created xlsx file via res, it doesn't work. No error either so I'm not really sure what is going on. I'm using the example in the documentation:
wb.write("My Excel File.xlsx",res);
Inside Excel, there's a "Currency" format which allows decimal precision and currency symbol to be specified, rather than taking a full formatting string, which allows user-locale-specific presentation details like the digit group separator (,
in US English) to be determined by Excel when the document is opened. Can this be implemented in this project?
After exporting to excel file by attaching it to response, I get this message
function forEach() { [native code] }
Has anyone encountered this problem or might know why is happening?
Is support for conditional formatting already on the roadmap? This is by far the most comprehensive Excel library for node js that I could find and it would be great to see that feature added.
First of all, thanks for the awesome library. It works perfectly well!
I just have a basic question here. I want to set a column width to 100%, the column could be able to expand to cover the longest content in one line. It might be somethings like this:
ws.Column(1).Width('100%');
Is it possible to do so? I tried many options but still didn't work.
hi natergj :)
tested your excel4node module an I have to say that it is great, but it there a way to set the paper formater.
I mean is it possible to set the left, right, top and down margin?
Is it also possible to set the content on the center of the page horizontaly but not vertically?
Would be also nice to be able to zoome the wole content.
thanks for the great work!
I two sheets are added with the same name or with a name not specified, excel file will give error when opening file and rename sheets.
I am encountering a problem with NPM on AWS Opsworks caching [email protected] with incorrect filenames (first letter capitalized) for the files in the lib folder. These files were renamed to lower case on commit 5409206
When npm install
is executed on a Ubuntu instance running under AWS Opsworks [email protected] is installed, but the files in the lib folder have the first letter of the filename being uppercase. This causes my deployment to fail with the following error:
Error: Cannot find module './style'
at Function.Module._resolveFilename (module.js:336:15)
at Function.Module._load (module.js:278:25)
at Module.require (module.js:365:17)
at require (module.js:384:17)
at Object.<anonymous>
(/srv/www/clients/releases/20160223175901/node_modules/excel4node/lib/index.js:1:75)
at Module._compile (module.js:460:26)
at Object.Module._extensions..js (module.js:478:10)
at Module.load (module.js:355:32)
at Function.Module._load (module.js:310:12)
at Module.require (module.js:365:17)
at require (module.js:384:17)
Clearly this is an AWS issue and not an issue with excel4node, but wanted to mention it here incase other people are experiencing this problem.
I am open to any suggestion on how to resolve this problem.
Issue does not cause problems with filtering, but should be fixed regardless.
The same xlsx file generated and sent to http response for a second time is opened as a corrupted file and then recovered by Ms Excel.
I got the same behavior when I use the sample.js
Hello,
first of all - thanks for this awesome library!
bug description:
when I set cell.String("") (empty string), the resulting cell in xlsx has value "undefined"
steps to reproduce:
x = require('excel4node');
wb = new x.WorkBook();
ws = wb.WorkSheet();
ws.Cell(1,1).String('');
ws.Cell(2,1,10,10).String('');
wb.write('test.xlsx');
best regards,
Tom
Hi,
I've prepared simple sample which shows the issue:
var xl = require('excel4node');
var wb = new xl.WorkBook();
var ws = wb.WorkSheet('Worksheet');
for(var i = 1; i <= 5; i++) {
for(var j = 1; j <= 25; j++) {
ws.Cell(i,j).String(i + '.' + j);
}
}
console.log('Row #1 cellCount: ' + ws.Row(1).cellCount()); // output: Row #1 cellCount: 9
ws.Row(1).Filter(); // This method add filter only on first 10 cells
wb.write("My Excel File.xlsx");
This is the thrown error:
[Error: Could not create any elements with: [object Object]]
such as path, size, etc.
I'm having a fair bit of trouble replicating this bug, but I have a fairly large sheet of data (40-by-250 ish) where I am trying to freeze the first row and header at the same time, and they appear to be malformed somehow as on Excel 2007 the frozen row shows up twice (disappears on scroll, and is fixed on saving through excel), and on Excel 2013 it shows a message on startup that the file was malformed and it has been repaired, which removes the row's freezing.
I tried to create a test case replicating this behavior, but was unsuccessful.
Let me know if there's anything I can try or investigate for you to debug.
That is very bad idea to overriding Array forEach by default.
excel4node/lib/workbook/workbook.js
Line 22 in ef04a5e
You can break entire project(s) for developers who will use your library.
In most case to avoid if condition developers may use (for ..var) loop in Array
var array = ['A', 'B', 'C'];
for (var index in array) {
console.log(index + '=' + array[index]);
}
Result is:
0=A
1=B
2=C
now adding on top your Workbook constructor:
new Excel.WorkBook();
for (var index in array) {
console.log(index + '=' + array[index]);
}
Result changed!!!!!!!!!!!!!!!!
0=A
1=B
2=C
e4nForEach=function forEach() { [native code] }
PLEASE AT LEAST SET "allowInterrupt" to false. But better to find another way how to handle async forEach.
The below code for creating borders doesnt apply or exist in the time of individual cell creation until we inherit from wb.Style();
cell.Format.Border({
top:{
style:'thin',
color:'CCCCCC'
},
bottom:{
style:'thick'
},
left:{
style:'thin'
},
right:{
style:'thin'
}
});
Please add border function for individual cell creation as well
reproduce by calling .Hide() on column that has no data
Thanks for the quick fix to issue 24 ( #24 )
Now the same issue is happening again but now between cells AZ and BA. To test this:
Also, if a cell spans from before cell Z to after cell BA, the issue is almost the same, but not quite. To test this:
If the problem was with ordering, I'm guessing the ordering could be something like:
Because I'm assuming this is going to be an issue on col AAA as well. I know its weird, but I have several dozen cols 'grouped' by a single, very long, merged cell, that is why this issues keep happening to me.
Thanks again for all the hard work, the quick fixes, but specially the great library.
Best regards,
Rafael Pólit.
When I try to read excel using nodeJS it read only upto Z column. can not read after that. i.e AA,AB after all column.
Thanks for this wonderful tool!
I am seeing a very weird issue:
To test this, a simple line will do:
You will see that the Y cell has the text and remains unmerged, then columns Z and AA are merged, and then column AB will be unmerged again and hold the text individually.
Thanks for any solution,
Best regards,
Rafael Pólit.
Hi Guys,
Your library is awesome. I already planed to write my own Excel project and finally found your project. But I need some additional features. I created pull request to handle complex strings.
https://msdn.microsoft.com/en-us/library/office/gg278314.aspx
http://officeopenxml.com/SSstyles.php
Pull Request: #60
if the write function is called more than once, there is a corruption in the XML data as various attributes are added multiple times.
Hi. I found lot of issues with image API's
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.