Autofill feature

How to get the dimensions (e.g. number of rows) from a file?

Hello,
would it be possible to add an option to write the column names of a worksheet based on the keys of the first row added to it ?
For example :

const worksheet = workbook.addWorksheet("test", { columns: true })
worksheet.addRow({username: "foo", email: "[email protected]"}).commit()
worksheet.addRow({username: "bar", email: "[email protected]"}).commit()
worksheet.commit()

Then the worksheet's columns would be "username" and "email".

The case for this is streaming from multiple sources (same structure of data, this is a sql query actually) into the same file.

EDIT : Nevermind, it's easily done in userland :

if (!worksheet.columns) {
    worksheet.columns = Object.keys(row).map((k) => ({ header: k, key: k }))
}

how to write in to the existing workbook without creating new workbook...

var workbook = new Excel.Workbook();
workbook.xlsx.readFile(filename)
.then(function() {
// edit worksheet
var worksheet = workbook.getWorksheet("My Sheet");
worksheet.getCell("A1") = new Date();
})
.then(function() {
return workbook.xlsx.writeFile(filename)
})
.then(function() {
console.log("Done");
});

Like jXLS, where data input is in terms of the array of objects.. and output is XLS with the template support.

Hi,

I have a questions that I have a very large excel file that has 80w rows and 10 columns?
Is it possible to use this library to process row by row such as nodejs stream module?

Hi is there a demo on using createAndFillWorkbook()? a jsfiddle perhaps?

I get this error

    /Users/gzRyan/Documents/reports-observation/server.js:13

    TypeError: Object #<Object> has no method 'createAndFillWorkbook'

where is the example code reference?

While using the library, I found a use case for hiding a column. IDs that I didn't want/need my users to see, but still needed in the uploaded spreadsheet when they were done working with it.

I was able to achieve this by setting width to zero. However, a hidden property could be potentially useful.

I started to create a pull request containing that seemingly simple code. But, then I realized that this could cause conflicts with the "Width" property, and wasn't sure how to handle it. so I thought I could open this for discussion.

Hi,

I'm currently trying to make my program much more efficient, hence I wanted to use the streaming API.
I've tried following code:

const Excel = require('exceljs');
const workbook = new Excel.xlsx.WorkbookWriter({
    'filename': './myfile.xlsx'
});

workbook.getWorksheet(1);

The last line returns undefined, it's kind of obvious to me since I call this function when the file has not even been read yet. But if I have to wait till it's read, that's no streaming anymore is it? :P
So what am I missing out, I have no clue how this should work.
Any help is greatly appreciated...

For example:

Thanks!

Hello!

I'm trying to write a number in cell A4 in my excel file and then get the result from cell B4.
Nodejs responds with "Unhandled rejection TypeError: Cannot read property 'target' of undefined".
What am I doing wrong?

var excelfile = "korrtermer/ID101_103_2015.xlsx";
var workbook = new Excel.Workbook();
workbook.xlsx.readFile(excelfile).then(function(){
var worksheet = workbook.getWorksheet(1);
console.log(worksheet.getCell("B4").value);
worksheet.getCell("A4").value = "23,4";
console.log(worksheet.getCell("B4").value);
worksheet.commit();
workbook.commit();
res.end();
});

Do you want to implement something like getRows function? I know about _rows object, but I don't know it is stable or not.

http://openxmldeveloper.org/

http://openxmldeveloper.org/wiki/w/wiki/open-xml-sdk-for-javascript.aspx

Hi,

I see that the cell types do not include Time. Is it possible to generate a Sheet such that the cell have a Date type with only the time part shown in the Excel?

Thanks,
Yash

I'm sorry if i was wrong.

I just have the problem to create xlsx file with exceljs.

Ex) below is pseudo code.
case OK:// All border shows correctly.
getCell("A1").border = border_all(top right bottom left);
getCell("A2").border = border_all(top right bottom left);
getCell("A3").border = border_all(top right bottom left);

case NG: // A1's border only shows.
getCell("A1").border = border_all(top right bottom left);
getCell("A2").border = border_all(top right bottom left);
getCell("A3").border = border_all(top right bottom left);
worksheet.mergeCell("A1:A3");

I have been looking around for an xlsx parser that can detect strikethrough and other font properties. Your module looks quite excellent, but I have not had a chance to clone your repo and mock up a test case, so I've decided to ask a question here instead... I'm looking at your examples and seeing the .font property being used as a setter for getCell()... Can I read an existing XLSX and access the existing cell font properties by doing something like the following?

// Iterate over all cells in a row (including empty cells)
row.eachCell({ includeEmpty: true }, function(cell, colNumber) {
    console.log("Cell " + colNumber + " = " + cell.value);

    // is .font valid in the context of reading cell content?
    console.log("... and .font.strike= " + cell.font.strike); 
});

Many thanks in advance for taking the time to answer a question!

Hi,

I see that the cell types do not include Time. Is it possible to generate a Sheet such that the cell have a Date type with only the time part shown in the Excel?

Thanks,
Yash

Hello,

my goal is to read any kind of Excel file (xlsx, xlsm, xlsb) and modify or add data to it and save it to disk. The file should contain all the changes that the original file that I am reading contains. I was able to figure out how to do that - so far so good. As mentioned in #35 with the current version 0.2.3 I was not able to produce none corrupt file. So I switched to the older version 0.2.2.

Now I am able to write a file that can be opened with Excel but I lost for example all cell formatting styles e.g. colors. I was also trying a different library with the same result. One option would be to add the styles, if possible, back to the new written file. But a better option would be if I could just use the read file as my "template".

Kind regards,
ezintz

When running exceljs under JXcore package, I've encountered an error:

[...]/node_modules/exceljs/lib/utils/utils.js:38
                    deferred.reject(err);
                                    ^
ReferenceError: err is not defined
    at [...]/node_modules/exceljs/lib/utils/utils.js:38:37

It looks like there is a typo in that line and should be deferred.reject(error) instead of deferred.reject(err).

How client(express) download excel file?

I noticed there wasn't any tags/release on the github repo while there had been 21 on npm, is it on purpose?

Is there a way to do this? For example, if I want to write the file into buffer/binary which then I can use it for some other stuff. I know there're write to stream, but not sure how can I do it or get the binary written.

Something like in SheetJS, XLSX.writeFile(workbook, { type: 'binary' }); will write the file in binary. Is there something for this?

Thanks for you work

Unhandled rejection TypeError: Cannot call method 'addRow' of undefined

var workbook = new excelJs.Workbook();
workbook.xlsx.writeFile("./public/2015-10.xlsx").then(function () {
var worksheet = workbook.getWorksheet("Sheet 1");
worksheet.addRow({name: "John Doe", jine: "100", created_at: '2015-09-30'});
worksheet.commit();
workbook.commit();
});

Hello, great lib, I have an issue with the following code whereby the updated file become corrupt. The val you see in code is a string version of a bool so "true" or "false", I did hardcode a string as val ("hello"), just to see if it was the bool that caused the issue, but the file still got corrupted. Can you please take a look and see what I may be doing wrong, thanks. If I am able to recover the file, it has the updated value, but my app is no longer able to read the file for subsequent parsing

function updateExcelEntry(inputPage,sheet,colKeyIndex,colIndexToUpdate,rowKey,val){
    //console.log("in excel " + inputPage);
    var workbook = new Excel.Workbook();
    workbook.xlsx.readFile(inputPage).then(function() {
            // use workbook
        //console.log("getting sheet " + sheet);
        var worksheet = workbook.getWorksheet(sheet);
        //console.log("getting keyCol " + colKeyIndex);
        var keyCol = worksheet.getColumn(colKeyIndex);
        keyCol.key = "key";
        var col = worksheet.getColumn(colIndexToUpdate);
        col.key = "colToUpdate";

        worksheet.eachRow(function(row, rowNumber) {

            //console.log("checking key val " + row.getCell("key").value);
            //console.log("vs rowKey " + rowKey);
            if(rowNumber !== 1 && row.getCell("key").value === rowKey){
                //console.log("Row " + rowNumber + " = " + JSON.stringify(row.values));
                row.getCell("colToUpdate").value = val;

            }
        });


    }).then(function() {
        return workbook.xlsx.writeFile(inputPage)
    }).then(function() {
        console.log("workbook updated " + inputPage);
    });
}

Hi guys! I'm going to use this lib but it seems it isn't supported. Is it stable for production? Thank you

I'm using a pc with windows 7 for development and a server with ubuntu for production.
There is no problem in windows node.js to generate .xlsx files. However, it is possible to generate some damaged .xlsx files in ubuntu node.js.

When the damaged file is opened, a error is shown: "Excel found unreadable content in *******.xls. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click yes".

I have no idea at all about the error. I found the file size generated in window and ubuntu are not same. I guess there is a problem during writing the file or zipping it.

There's a check in row.js
if (instance of Array)
preventing array of arrays being added as rows (which is very convenient I think).

Cheers

So i can have something like ..

----title---- (100% width)
-----id --- name --- dob ----

----title2----
-----other stuff ----

When writing and reading files, the technique used for Promises seems to be deprecated and this might break excel js in future updates.

Hi, from the documentation, it is not clear (to me). Does this lib support streaming reads of sheets in an Excel workbook? I want to work with potentially huge sheets of data without loading all into memory.

If a cell contains multiple formats (e.g. different colors in the same cell, which Open XML standard accepts), exceljs splits the text into many cells, therefore the result is corrupted.

Let's say cell A1 contains 3 different words, each in a different format. The parser exports the three words in three json fields, interpreting as A1 = [first word], B1 = [second word], C3 = [third word]. Therefore the whole result json is shifted. See an Open XML spec from MS here.

Just starting out with exceljs and lost about 30 minutes trying to figure out why I kept getting corrupt excel files. It turns out the issue was because of an ampersand character in the worksheet name. In excel, these are allowed so I guess I assumed they would work.

For example, the following generates a corrupt workbook: workbook.addWorksheet("hello & bye");

Great Repo! Thank you for your contribution. You might as well add a keyword 'xlsx' into the description of this awesome repository for more people can find it via Search.
I found that the streaming XLSX writer has a bug

worksheet.addRow({
   id: i,
   name: theName,
   etc: someOtherDetail
}).commit();

this do not work, but

worksheet.addRow([ i, theName, someOtherDetail ]).commit();

this work.
And I have a question, how to set a hyperlink which link to a cell of another worksheet.

Right now it doesn't seem to be the case (at least catch doesn't catch anything)

[Error: Cannot find module './xlsx/.rels'] code: 'MODULE_NOT_FOUND' . It is ok when running offline, but it reports the error when put it on line. I cant figure it out. help~~~

Pardon the stupid question, my assumption is that this project only works within node, is there anyway to make this a purely client side project (akin to xlsxjs)?

To clarify, when using browserify the module in a non-node project getting the following error:
process.binding is not supported

So my assumption is that it must use node for file system access, it would be great if there is a shim/factory pattern that would allow me to use a serialized file object in the browser.

Thanks in advance!

Hi! I have an .xlsx file, created in Excel, with a vertically-centered cell. Its alignment value seems to be "center" instead of "middle":

in xl/styles.xml:
...
<alignment horizontal="left" vertical="center" textRotation="0" 
  wrapText="false" indent="0" shrinkToFit="false"/>

...

Unfortunately I don't have Excel on my computer right now, only LibreOffice, but it seems to honor the "center" value and renders correctly (vertically centered).

But when I generate an .xlsx using exceljs, if I use alignment = {vertical: 'center'}, it is not written to the output file (because center is not a legal value for alignment in the current API). And if I use middle, it DOES get written to the output file but LibreOffice doesn't honor it and displays the text in the bottom of the cell.

I quickly hacked alignment.js:158 to look like this:

validVertical: function(value) {
  if ( value == "middle" ) return "center"; // added this line
  return this.validVerticalValues[value] ? value : undefined;
},

Now, I can use the "middle" value as the API expects, but it outputs "center" in the final file, and things work (on LibreOffice, that is; I still have to check on Excel).

I'm not sure, maybe this is just LibreOffice's fault, and the "middle" value is correct? I'm still searching for some documentation on the format.

Hello!

After running the code below and when I open the file the cell shows =A1+B1.
It seems that excel doesn't understand that it is a formula in B1.
What do I do wrong?

var Excel = require('exceljs');

exports.test = function(req, res){
  var workbook = new Excel.Workbook();
  var worksheet = workbook.addWorksheet("Sammanstallning");
  workbook.getWorksheet("Sammanstallning");
  worksheet.getCell("B1").value = '=A1+C1';
  worksheet.getCell("C1").value = 0.14;

  workbook.xlsx.writeFile("korrtermer/test_exceljs.xlsx");
  res.end();
}

Am getting an invalid signature: 0x80014 when reading an excel sheet that has 1 worksheet and 1443 rows, 8 cols to data. For some reason, this error starts been generated when the excel sheet has more than 1370 rows of data.

Any ideas?

Below code throws "undefined is not a function'' in workbook.commit() line.

var workbook = new Excel.Workbook();
workbook.creator = "Me";
workbook.lastModifiedBy = "Her";
workbook.created = new Date(1985, 8, 30);
workbook.modified = new Date();
var sheet = workbook.addWorksheet("ssheety");
var filename = 'test.xlsx';
var worksheet = workbook.getWorksheet(1);
worksheet.columns = [
{ header: "Id", key: "id", width: 10 },
{ header: "Name", key: "name", width: 32 },
{ header: "D.O.B.", key: "DOB", width: 10 }
];
worksheet.addRow({id: 1, name: "John Doe", dob: new Date(1970,1,1)});
worksheet.addRow({id: 2, name: "Jane Doe", dob: new Date(1965,1,7)});

workbook.commit()

res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
res.setHeader("Content-Disposition", "attachment; filename=" + "Report.xlsx");

workbook.xlsx.write(res);
res.end();

When I try use eachCell function it works strange. It seems that it dismiss null/undefined value or something like that.

// ...
var parsedRow = {};
row.eachCell(function (cell, col) {
       parsedRow[columnsRow.getCell(col).value] = cell.value;
});

For example, if I have 30 columns and some of them equals ""(empty string in the file) then Object.keys(parsedRow).length expression can return 26.

It's unclear from the documentation how I would go about doing this.

I want to read in an existing XLS file, update some values, and then get the new computed results of formulae.

For example, if I have this spreadsheet:

A1=3
A2=4
A3=7
A4=sum(A1:A3)

I'd like to update A3 to equal 8 and for A4's value to then yield 15.

Is there a chance to use this on clientside js? For example click a button and get the excel document from the data on the browser like downloading a document?

With an .xlsx file with more than 9 sheets crashes.

For resolve the problem in regular expressions I changed \d with \d+

default:
if (entry.path.match(/xl/worksheets/sheet\d+.xml/)) {
var match = entry.path.match(/xl/worksheets/sheet(\d+).xml/)
var sheetNo = match[1];
promise = self.parseWorksheet(entry)
.then(function(worksheet) {
worksheet.sheetNo = sheetNo;
model.worksheetHash[entry.path] = worksheet;
model.worksheets.push(worksheet);
});
} else if (entry.path.match(/xl/worksheets/_rels/sheet\d+.xml.rels/)) {
var match = entry.path.match(/xl/worksheets/_rels/sheet(\d+).xml.rels/)
var sheetNo = match[1];
promise = self.parseRels(entry)
.then(function(relationships) {
model.worksheetRels[sheetNo] = relationships;
});
} else {
entry.autodrain();
}
break;

is correct?
Thanks