I am trying to preserve the style information while I read and then write an excel File. Seems like the style information is read when I read the file, but unfortunately when I write the file its not preserved.

Thoughts on how I can read and write the style information?

            excelFile = XLSX.readFile(file.path, {
                cellStyles: true
            });

            // some processing here!

            XLSX.writeFile(excelFile, fileName, {
                cellStyles: true
            });

@sheetjs After a conversation with @elad (https://github.com/hubba/js-xlsx/commit/5e9bca78f2b0c54242cefc8a358f3151232941ab#commitcomment-8039333) I was informed that the styling information is only read, and not written back to excel.

EDIT: please raise a new issue if you encounter build issues with browserify, webpack, requirejs, systemjs, or any other module bundler or loader or packaging system.

In webpack I get this error:

ERROR in ../~/xlsx/xlsx.js
Module not found: Error: Cannot resolve module 'fs' in /Users/Ram/Desktop/bob/node_modules/xlsx
 @ ../~/xlsx/xlsx.js 32:3635-3648 33:3206-3224

ERROR in ../~/xlsx/ods.js
Module not found: Error: Cannot resolve module 'fs' in /Users/Ram/Desktop/bob/node_modules/xlsx
 @ ../~/xlsx/ods.js 67:9-27

ERROR in ../~/xlsx/dist/cpexcel.js
Module not found: Error: Cannot resolve 'file' or 'directory' ./cptable in /Users/Ram/Desktop/bob/node_modules/xlsx/dist
 @ ../~/xlsx/dist/cpexcel.js 1:165488-165513

When calling xlsx.utils.sheet_to_json(sheet, {header: 1}) I would expect to receive an array of arrays including empty rows. However the function explicitly skips out empty rows which makes it impossible to select a particular row in the results. It would be good to make this behaviour configurable.

As a workaround, I have used sheet_to_csv, which doesn't skip rows, followed by csv-parse.

Excel supports cells that display formatted dates. For testing purposes I created a file with only one cell (A1) and entered the value 2014-09-28. The value was immediately formatted as a date and displayed as 9/28/14.

I wanted to see how I can reliably extract back the original date. I used the following program:

var xlsx = require('xlsx');

var workbook = xlsx.readFile(process.argv[2]);
for (var i = 0; i < workbook.SheetNames.length; i++){
    var worksheet = workbook.Sheets[workbook.SheetNames[i]];
    console.log(worksheet);
}

And got the following output (after running it with node myfile.xlsx);

{ A1: { t: 'n', v: 41910, w: '9/28/14' }, '!ref': 'A1' }
{}
{}

I see several problems here:

• The number 41910 is the number of days from some epoch, either 1900 or 1904, depending on where the file was created (Windows or Mac). There should be a way to tell how to treat this value.
• The t field (for type) indicates this is a number. Can it instead indicate it's a date? or should there be a different property?
• There's no way to retrieve the actual value that was entered - 2014-09-28 - so the original date, along with a reliable way of retrieving it, seems to be lost.

All of the above also applies for time fields.

Am I missing something? Is there a way to reliably get 2014-09-28 from the cell data?

EDIT: if you encounter this issue, confirm you are using the latest version and that you are using the correct input type! See https://github.com/sheetjs/sheetjs#input-type

For example, using a FileReader with readAsArrayBuffer will call the load event where the result is an ArrayBuffer, so type should be "array"

If data is received through an XHR or fetch, verify that the source data is not UTF8-encoded. Some platforms like AWS Gateway API do this automatically and require an override as explained in the function demo

--

I am trying to use this library to parse a base64 encoded .xslx file on the server using node.js. I have also removing the base64 header info and loading the data into a binary buffer, but I get the same error.

Here is my function to parse the file:

var parseFile = function(data){
  var xlsx = XLSX.read(data, {type: 'base64'});
  var sheet_name_list = xlsx.SheetNames;
  xlsx.SheetNames.forEach(function(y) {
    for (z in xlsx.Sheets[y]) {
      if(z[0] === '!') continue;
      console.log(y + "!" + z + "=" + JSON.stringify(xlsx.Sheets[y][z].v));
    }
  });
};

However, when I try to parse the data, I get the following error.

Error: Corrupted zip : can't find end of central directory
        at Object.ZipEntries.readEndOfCentral (/Users/.../node_modules/xlsx/jszip.js:2087:27)
        at Object.ZipEntries.load (/Users/.../node_modules/xlsx/jszip.js:2104:18)
        at Object.ZipEntries (/Users/.../node_modules/xlsx/jszip.js:2010:18)
        at Object.JSZip.load (/Users/.../node_modules/xlsx/jszip.js:2115:22)
        at Object.JSZip (/Users/.../node_modules/xlsx/jszip.js:5:14)
        at Object.readSync [as read] (/Users/.../node_modules/xlsx/xlsx.js:940:24)
        at parseFile (/Users/.../routes/tickets.js:35:19)

any thoughts as to why this might be happening? When I load my file into your demo site, it parses it to json just fine.

I am trying to replace an implementation of PHPExcel with js-xlsx under node js as I am no longer using php server-side.

I have a series of excel templates that are pre-formatted with colours and styling, and also page size, margins, print areas etc etc, and then I simply add text to the relevant cells, under PHPexcel it works very well.

I discovered js-xlsx a few hours ago and thought I had found the solution, but it appears that I am unable to duplicate the original file, only with the cell values changed, whilst maintaining everything else in the original file.

var XLSX = require('xlsx');
var wb = XLSX.readFile('/usr/share/nodejs/dwap/public/xls/qplan.xlsx',{cellStyles:true});
XLSX.writeFile(wb,'/usr/share/nodejs/dwap/public/xls/out.xlsx',{bookSST:true});

I have used the simple code above to read in a formatted file and then save it again, but all of the styling is lost.

Is it possible to achieve this kind of functionality with js-xlsx and if so what am I doing wrong ?

So, I've been digging around this repo a bit and it seems like the go-to method for filtering etc is to convert things to json using XLSX.utils.sheet_to_json(wb.Sheets.SheetName), however I can't find any documentation on then turning that manipulated json back into a workbook. Please advise! (thanks in advance)

Hi, I know there is already a very similar issue here but it does not provide a solution, because the author of this issue just uses a different approach to download the file in the end. https://github.com/SheetJS/js-xlsx/issues/122

I'm trying to download a file which is generated in the nodejs backend from the angular frontend, but when I try to open it, it's always corrupted. Of course I have seen the example in the readme of this project and tried it, but it also came out corrupted. I can write the output to a file and then point the browser to it by using window.location() and the file is fine. Sadly I could not get this approach working with my authorization code. I read somewhere that it's better to send buffer objects when working with expressjs, so here is my code right now:

// Backend
var wopts = { bookType:'xlsx', bookSST:false, type:'buffer' };

        var wbout = XLSX.write(workbook,wopts);

        res.writeHead(200, [['Content-Type',  'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']]);
        res.end( wbout );

// Frontend
// This triggers the backend function:
 $http.get("/abrechnung/exportToExcel/" + JSON.stringify(pageAndFilter.filterByFields)).
             success(function(data, status, headers, config) {  
              // The data object is the buffer
        saveAs(new Blob([data],{type:"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"}), "test.xlsx");
        }).
  error(function(data, status, headers, config) {
  });

Any help would be greatly appreciated!

My node.js code var file = fs.readFileSync('./report/output/out.xlsx', 'binary'); res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); res.setHeader('Content-Disposition', "attachment; filename=" + "out.xlsx") return res.end(file, 'binary'); How javascript client download this file ? I have try many way but get open file corrupt Thanks for help

Hi! I try use js-xlsx, bower and glup, but when I added 2 files to my progect

require('../vendors/js-xlsx/dist/jszip.js'); require('../vendors/js-xlsx/xlsx.js');

I got this error

{ [Error: module "./cptable" not found from "F:.......\vendors\js-xlsx\dist\cpexcel.js"] plugin: 'gulp-browserify', showStack: undefined, name: 'Error', message: 'module "./cptable" not found from "F:\.......\vendors\js-xlsx\dist\cpexcel.js"', fileName: undefined, lineNumber: undefined

I have cpexcel.js file in 'dist' folder. Any idia how I can fix it? Thanks!

Hi does this library allow me to export a xls with a image somewhere on the sheet? if there are any jsfiddles on the web showing this it would also be great.. thanks

I found that the return time of the datenum method in Chrome browser is incorrect, consider customizing a getTimeZoneOffset function to fix Date.prototype.getTimezoneOffset() return error value

Hi is it possible to write the output to the excel file as shown below? Have the headers in one column (A1) and data in another (B1).

Rather than the normal behavior as shown below

This is more of a question. I have scoured the internet trying to find out if there is a way to format an entire column as text. My "source" sheet has the entire column formatted as text but only the header row is text when I download/save it. The source file is just a "template" with only the header row having values. Rest are blank rows.

this is all I am doing in my code example (result is an arrayBuffer coming from the .NET webapi)

this.carrierRatesService.getCarrierRatesFileTemplate()
      .subscribe(result => {
        if (result.byteLength > 0) {
          const wb = xlsx.read(result, { type: 'buffer', cellStyles: true });
          const fileName = 'CarrierRatesTemplate.xlsx';
          xlsx.writeFile(wb, fileName)
        }})

This is the source file (7th row showing as Text datatype)

This is the result of my download (7th row as General datatype)

Is there a way to set an entire column manually? It is causing leading 0's to be cut off.

Gist to test the content of an Excel File in a controller (e2e) test, with a lib like supertest:

  function binaryParser(res, callback) {
    res.setEncoding('binary')
    res.data = ''
    res.on('data', function (chunk) {
      res.data += chunk
    })
    res.on('end', function () {
      callback(null, new Buffer(res.data, 'binary'))
    })
  }

  describe('exports/:id (GET)', () => {
    it('should return an excel File', async () => {
      const res = await request(app.getHttpServer())
        .get('/exports/xxx')
        .expect('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8')
        .buffer()
        .parse(binaryParser)

      assert.ok(Buffer.isBuffer(res.body))

      const workbook = XLSX.read(res.body, { type: 'buffer' })
      const sheet = workbook.Sheets[workbook.SheetNames[0]]
      const json = XLSX.utils.sheet_to_json(sheet, { header: 1 })

      expect(json).toEqual([
        ['My first Row', 'My second Row'],
        ['val 1', 50],
        ['val 2', 10]
      ])
    })
  })