protobi / js-xlsx Goto Github PK

Hi,

npm doesn't deal well with v0.87. I assume it was supposed to be 0.8.7. Would it be possible to fix the version? Or have a 0.8.8 release? 0.8.6 doesn't have some fixes we need.

thanks,

geoff

Hi. First of all, thanks a lot for this project, it's been immensely useful.

I've been writing a new feature to write a XLSX file, with a particular style, and I've stumbled upon a (admittedly minor) problem. This document has some rows that are left empty, but nonetheless need to be styled (to have a matching background, for instance).

To style the cell, of course, it has to exist, but I've been unable to create cells with no content (like having .v set to null) and xlsx won't allow it.

I've resorted to giving it an empty string as value (""), but this creates a new problem: Excel doesn't see it as a truly empty cell, and won't allow the previous cell to override it, which is already making my users complain. Here's an example:

That third row has a cell with a long text that should take on the next cell, which is empty. This can be fixed just by selecting and deleting that next cell, and it will keep it style while being truly empty, but I want to make my users edit this file as little as posible.

What would be the best solution? Any way to allow the styling of cells that don't exist? Or would it be best to make XLSX able to hold cells with really no value (which would have to be raised to the original project, I guess)?

This library doesn't seem to support writing cell styles from scratch. For instance, parsing a JSON object and writing directly to a sheet with styles (or comments)

I tried to read the bgcolor of an excel file. "cellStyles:true" is set.
Somehow i only get bgColor:64 for any color.

Maybe im just missing something.
Any advice would be appreciated :)

Excel-Test.xlsx

I'm getting partial styles back with .ods files but only fill data seems to come in, and it's not wrapped in a fill property, so it doesn't write back to the spreadsheet correctly.

Notably, if I create an xlsx file with LibreOffice, this problem occurs also, but at least some cell sizing is maintained (no idea why). If I try to re-save the file with excel, it does not appear to resolve this issue.

For reference, the object looks like this

{
  "A1": {
    "t": "s",
    "v": " ",
    "r": "<t xml:space=\"preserve\"> <\/t>",
    "h": " ",
    "w": " ",
    "s": {
      "patternType": "solid",
      "fgColor": {
        "rgb": "000000"
      },
      "bgColor": {
        "rgb": "450000"
      }
    }
  },
  "A2": {
    "t": "s",
    "v": "Text without formatting",
    "r": "<t xml:space=\"preserve\">Text without formatting<\/t>",
    "h": "Text without formatting",
    "w": "Text without formatting"
  }
}

I am getting an error with 503 when i am trying to install the package using bower.

I have a workbook that has some cells bordered but there is not content in it.

When I save the document these cells are completely cleared out, is there a way to keep those cells that have no content, just styling?

I base one Meteor.
1- It is Ok for native https://github.com/SheetJS/js-xlsx install and use.
but It don't get style when I generate excel file form template.
2- And then I would like to try this instead, but show error

Cannot find module 'jszip'

Is it possible to adjust row heights (per row) in worksheets written using this library?

Right now when you add formula =TODAY() into the xlsx file it will be ran and get cached as the value of the field. When reading the file with js-xlsx the formula is not ran and the value for the cell will be as it was when the file was last opened by a proper application (Excel, Calc etc.).

In the XLSX files, the empty cells are skipped, but they are skipped even if they hold style informations.
I think they shouldn't be skipped in this case as they still hold relevant information, plus, importing + exporting an excel file without changing it should be an idempotent operation.

See : #8

Looking at all the options here: https://github.com/protobi/js-xlsx#cell-styles

Couldn't figure it out if it is possible to create hyperlinks inside the cell.
Is that possible?

In routine parse_s_xml_cols

• parsexmltag is used with skip_root set to true, which looses all of the info on <col ... />

A quick hack to hide grid lines is to find the line (e.g. xlsx.js:7823)

  o[o.length] = (writextag('dimension', null, {'ref': ref}));

and immediately after that add this line

  o[o.length] = '<sheetViews><sheetView showGridLines="0" tabSelected="1" workbookViewId="0"/></sheetViews>';

A better solution would be to read wb.Props and set the values conditionally. Better still would be to use the provided methods like writeextag rather than inserting a raw xml string. Saving that for daylight.

Hi,

I'm using this great powerful library.
Now what I need is to write several words in different colors in one single cell.
Could that be possible?

Any help will be appreciated, thanks!

Hi, can you update the fork to 0.9.10 version like in the main repo? Or, can it be implemented?

I need to use both style writing your fork implements and write to XLSB format..

Would love some release tags or release on npm (or both).

Does writing to cell format include style? if yes, then how can it be set? as an object, xml?
I tried something like this: A1: { t: 'n', v: 1, w: '1', s: { patternType: 'solid', fgColor: { rgb: 'FFFF00' }, bgColor: { indexed: 64 } } } }

How to create dropdown values column on export in sheetjs

Wondering how I could have a cell where only part of the cell is styled. eg:

{
v: "this is my BOLD text"
s: { font: { bold: true } }
}

Stepping back a bit, there are a few pending pull requests and issues related to styles that are pending. Would like to incorporate these, but wish to run it against the awesome test library first for safety.

Node 4
However, the library doesn't work on Node 4 and thus the tests fail. All else equal, I'd prefer to pull changes from the main trunk that address this before proceeding. Running multiple versions of node is an option but ...

Main branch
More broadly, this branch isn't yet merged back to the main project. Have been holding off updating too much to both (a) preserve option of merging the master branch of this fork back into main trunk yet also (b) release https://github.com/protobi/js-xlsx#beta on npm and bower. Further, there are other pending topics unrelated to styles (e.g. browserify).

Net net, I wonder if it's better to make this a separate project, give it its own package name, version, etc.

Alternative
An alternative approach I'm considering is to extend https://github.com/chuanyi/msexcel-builder so that it can can take CSF objects as input and/or style cells. That's a tiny light library. More often my goal is just to generate an XLSX with rich styling. That library only writes XLSX, whereas XLSX can read and write XLS, XLSX, XLSB. It might be good to make these two libraries mutually compatible.

Hello.
I have tested this library (beta branch) and I faced a problem with printing downloaded file - when I try to do it with MS Excel (2013), it simply crashes. The problem doesn't exist in Libre Office Calc, and vanishes, when I save the file as a new one, and reopen.

I hope you can see the problem in those files:
The one, that was downloaded with this library: downloaded_file.xlsx - MS Excel crashes, when I try to print it.
And the second one, created through saving downloaded file once again: resaved_file.xlsx - MS Excel prints document perfectly

Is it possible to set different print area per worksheets using js-xlsx?

Great work! Just need to fix docs for the "Cell Styles" section:

alignment     vertical         "bottom"||"center"||"top"
            horizontal         "bottom"||"center"||"top"

Change to :
horizontal "left"||"center"||"right"

Tested changed settings and works.

Hey,
the example-style.js does not open on a mobile viewer.
Are there any fixes?
https://github.com/protobi/js-xlsx/blob/master/example-style.js

I have my project running xlsx-style perfectly except for a couple annoying issues. The biggest and most problematic of these is the fact that whenever I save my workbook and then attempt to open it. I get this problem:

Which isn't really descriptive.. Upon further investigation I get this:

Note that I can actually read the file and everything in it was written correctly with the correct styles, etc. But for some reason it feels the need to go through this repair business which for the client would be an extreme inconvenience.

Any help with this would be greatly appreciated.

I have the following styles:

var result = xlsx.write(wb, {
    bookType: 'xlsx',
    bookSST: false,
    type: 'buffer',
    defaultCellStyle: options.defaultStyles || {
      font: { name: 'Arial', sz: '12' },
      fill: {
        fgColor: { rgb: 'FFFFFFFF' }
      }
    }
  });

The file crashes excel. If I open it in some other program that opens xlsx files it works, and if i resave it
opens in Excel. I can send you the file in an email, don't want to post it for the world.

Hello.
Can I somehow set the width of the cell? I have a cell with a large content in my html-table, but when I create XLSX - it has default cell width.

Is it possible to embed HTML values? I have an API returning HTML for formatting purposes (list, strong text, etc.), which needs to be exported into Excel. I previously exported the HTML using the following code:

var html = document.getElementById(id).outerHTML;
if (window.navigator.msSaveOrOpenBlob) {
   var blobObject = new Blob([html]);
   window.navigator.msSaveOrOpenBlob(blobObject, filename)
} else {
   var link = $('<a id="download-xls"></a>');
   $('body').append(link);
   link.attr('href', 'data:application/vnd.ms-excel,' + encodeURIComponent(html));
   link.attr('download', filename);
   link[0].click();
}

This worked for the most part, except Excel understandably gave a security warning when opening the file.

Setting cell.v obviously does not work, and cell.h seems to only be used for reading. It seems like it may not be possible, but I want to make sure I haven't missed something.

Excel allows you to apply a style to a table (not just one cell).
For example, if you apply style 9, the table filters are added, all the cells are colored automatically in accordance with that style.
Can it be done in xlsx-style?

See SheetJS#128 (comment)

I don't understand, i installed using : npm install xlsx-style

my nodejs script is as simple as this :

var xlsx = require('xlsx-style');
var workbook = xlsx.readFile('template.xlsx');
xlsx.writeFile(workbook, 'out.xlsx');
console.log('Finished');

But all the styles disappeared. Am I missing something ?
Thanks

Is there anyway to provide 'grouping' to the xlsx? Allowing collapse and expand behavior.

Are there any plans to use new jszip 3.0 version? Async writing would be great!

We use this great library to generate XLSX export out of our online application. But we encounter two artefacts in the resulting files:

1. (major) it is impossible to copy from that file, and paste into another Excel file (copy-pasting inside the file itself works fine though).
   Here is the message we systematically get (here when trying to paste in my blank file cell A1 from the file generated with the library):
   https://www.dropbox.com/s/xkmfv5ht6kmugim/Screenshot%202017-04-20%2012.12.07.png?dl=0

2. (minor) Excel crashes when trying to print that file, on some old version of Excel (typically Windows - version 2010)

In both cases, doing a "Save as" of the file solves the issue. I send a test file right away by email.

File is attached (I'll also send by email):
Conformity of Production Site of Carrefour Products_Coopérative de Grastine (Germany)_2014.xlsx

Thanks for any hint as to how to solve that.
Xavier

Hi,
I am seeing an issue where even after setting the wrapText property and setting the write attribute with bookSST : true, the cell does not display the text in multiline until I explicitly double click on the cell.

Is there a way for it to display correctly without any manual edits?

function writeExcel(workSheet,cell,data){
workSheet[cell] = {v:"Test Case Steps",
t:'s',
};
workSheet[cell].s = {alignment:{wrapText: true}};
cellColumnStart = cell.split('',1)[0];
cellRowStart = parseInt(cell.replace(/[A-Z]/,""));
var dataString;

  data.forEach(function (row){
    cellRowStart += 1;
    cellRef = cellColumnStart+cellRowStart;
    dataString = "";
    row = row[0];
    cellCurrent = {v:dataString,
                   t:'s',
                  };
    row.forEach(function (scenario){
         datastring += scenario+"\n"; # this should be display as multiline in each cell
      })
    cellCurrent.v = "\"" + dataString  + "\"";
    workSheet[cellRef] = cellCurrent;
    workSheet[cellRef].s = {alignment:{wrapText: true}};
  })
  var range = workSheet['!ref'];
  var firstCell = range.split(':')[0];
  var lastCell = (range.split(':')[1]).replace(/[A-Z]/,cellColumnStart);
  workSheet['!ref'] = firstCell+":"+lastCell;
}

function handleWriteFileSelect(evt) {
writeExcel(workSheet,last_cell,excelData);
workBookFinal = XLSX.write(workBook,{type: 'base64',bookSST: true});

};

function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}

function handleDownloadFileSelect(evt) {

  saveAs(new Blob([s2ab(atob(workBookFinal))],{type:"application/excel"}), "test.xlsx");

};

I try to format my cell with an Euro french format, like '1 234 567 890.00 €'. I set the cell numFmt to '# ##0.00 [$€-40C]'. But when I open the sheet I get '1234567 890.00 €'. Change the language to 'French (France)' in the Format->Cell menu seems to do the thing. So, how can I set the language for a cell ?

See the 'Montant' column in this file.
test.xlsx

Hi problem with languages that uses right to left syntax when read and write the columns are get in the opposite direction , does any one knows a solution for this problem?

Can this fork be updated to SheetJS/js-xlsx version 0.8.3?

I get the below error message when I attempt to add a new worksheet to / copy cells from an Excel 2007 file generated in browser. The issue does not seem to occur with the original SheetJS version, but then I lose the styling options that make this version so great!

Here is an example: https://jsfiddle.net/jlhjr/0f8L9yfu/1/. Am I doing something obviously wrong?

Note that a workaround is to do a "Save As" of the temporary file, which must cause Excel to fix the underlying issue.

Thank you!

So this bit of code:

var _fs, jszip;
if(typeof JSZip !== 'undefined') jszip = JSZip;
if (typeof exports !== 'undefined') {
    if (typeof module !== 'undefined' && module.exports) {
        if(has_buf && typeof jszip === 'undefined') jszip = require('js'+'zip');
        if(typeof jszip === 'undefined') jszip = require('./js'+'zip').JSZip;
        _fs = require('f'+'s');
    }
}

Does not take into account if you are using requireJS without node. So you have modularity, the first if statement will not assign JSZip, but you're not using node, so the second if statement will not require properly, either. I suggest adding a default jszip = require('jszip') or changing the if/else logic a bit to allow for this.

So, maybe:

var _fs, jszip;
if(typeof JSZip !== 'undefined') jszip = JSZip;
else if (typeof exports !== 'undefined') {
    if (typeof module !== 'undefined' && module.exports) {
        if(has_buf && typeof jszip === 'undefined') jszip = require('js'+'zip');
        if(typeof jszip === 'undefined') jszip = require('./js'+'zip').JSZip;
        _fs = require('f'+'s');
    }
}
else
    jszip = require('js'+'zip');

Hi, i'm using your library which works perfectly.
I was wondering if it was possible to insert a macro in the workbook.

I want to create 2 worksheets, the first being a pivot table of the second.
For that I want to insert a vba macro that will create the first worksheet as pivot table created from the second worksheet.

Is there a way to insert vba script in the workbook generated by the library ?

Thanks in advance

Just opening and re-writing a file will mess up the grayscale bgcolor/fgcolor in the file :

Will output

I require mixed data-types in a column, i.e. a combination of percentage, number, decimal, string. I am able to generate excel, however on opening it says: 'Excel found unreadable content. Do you want to recover contents of the workbook'. On choosing 'Yes' option, it opens. This is one issue regarding the unreadable content.

Second is the excel shows all the cells formatted as that particular data-type which I specified, however doesn't let me do calculation if I do on same types after applying filter :(

You may adopt these change to make your fork work with browserify/webpack:
chriddyp@ece7444

Hello.
Could You make change from this issue:
SheetJS#472

This is my code：

...
// cell
const cell = {
	v: data[R][C],
	s: {
		font: {
			color: {
				rgb: 'FFC6EFCE'
			}
		}
	}
}
...
// write a xlsx
	const wxbot = XLSX.write(wb, {
		type: 'binary',
		bookSST: true,
		bookType: 'xlsx',
		cellStyles: true
	})

// save to xlsx with file-saver
saveAs(new Blob([sheet_to_blob(wxbot)], {
		type: 'application/octet-stream'
	}), 'test.xlsx')

Open the `test.xlsx` ，then style is not render. why? tell me. thanks！

Is there any way to freeze the first row(s) or column(s)?

Is there a way to add a filter row to the XLSX? and freeze the row.

Hi,I saw there was a commit named ' headerrow freeze',but I didn't find some useful information from code, could u plz tell me how to use this feature?