Reading from buffer

[mdumouchel]

Hello everyone,
I have an api that takes a excel file and I want to be able to read the buffer object in js-xlsx however I just keep getting errors.

I have tried the following

var workbook = XLSX.read(file.toString('utf-8'));
Error
Uncaught error: Unsupported file 161

var workbook = XLSX.read(file);
Error
Uncaught error: Object [object Object] has no method 'substr'

If i save the file to disk and then use xlsx.readFile with the file path it works fine..

[ottobonn]

If you have a Buffer object, you can pass it directly to XLSX.read() with no options:

var workbook = xlsx.read(file.buffer);

If you want to read from a string, it needs to be binary-encoded (but binary strings are deprecated in Node).

[mayureshnw]

Hello,
I am using sails, where the file is uploaded and I am saving it into a buffer.
Passing this buffer is giving the following error

Code

var finalBuffer = Buffer.concat(bufferList, totalLength).toString();
var fi = xlsx.read(finalBuffer,{type: 'base64'});

Error

switch(state[state.length-1][0]) {
                                        ^

TypeError: Cannot read property '0' of undefined
    at parse_xlml_xml (/Users/xyz/node_modules/xlsx/xlsx.js:8815:32)
    at parse_xlml (/Users/xyz/node_modules/xlsx/xlsx.js:9170:25)
    at Object.readSync [as read] (/Users/xyz/node_modules/xlsx/xlsx.js:11393:21)
    at Upstream.<anonymous> (Controller.js:75:21)
    at emitNone (events.js:67:13)
    at Upstream.emit (events.js:166:7)
    at endReadableNT (_stream_readable.js:903:12)
    at doNTCallback2 (node.js:439:9)
    at process._tickDomainCallback (node.js:394:17)

[gvsharma]

is this issue solved..?i need help here.

[danilodomingos]

Hi, this work for me. (function fixdata and btoa are the keys).

<script>

    function fixdata(data) {
        var o = "", l = 0, w = 10240;
        for(; l<data.byteLength/w; ++l) o+=String.fromCharCode.apply(null,new Uint8Array(data.slice(l*w,l*w+w)));
        o+=String.fromCharCode.apply(null, new Uint8Array(data.slice(l*w)));
        return o;
    }



    $('document').ready(function(){

        $('#flExcel').change(function(){

            var arquivo = this.files[0];
            var fileReader = new FileReader();
            fileReader.readAsArrayBuffer(arquivo);


            fileReader.onload = function (e){
              var data = e.target.result;
              var arr = fixdata(data);
              var workbook = XLSX.read(btoa(arr), {type: 'base64'});
            }

        });

    });
</script>

[Scimonster]

Passing the buffer directly to read() worked, but this should really be documented. I tried converting to it various strings, and even tried https://github.com/mgcrea/node-xlsx which clearly supports buffers, but i needed this library's features.

[SheetJSDev]

The read functions can take the following input types, controlled by the type field of the options object when you call XLSX.read(data, options):

type	expected input
"base64"	string: base64 encoding of the file
"binary"	string: binary encoded string (the n-th byte is data.charCodeAt(n))
"buffer"	nodejs Buffer
"array"	array: array of 8-bit unsigned numbers (the n-th byte is data[n])
"file"	string: filename that will be read and processed (nodejs only)

The underlying file type is guessed by inspecting the first few bytes of the file. The error message

Uncaught error: Unsupported file 161

means that the read function saw a file it didn't know how to handle. That number 161 is the first byte of the file, so the library thought the file started with 0xa1. In general, it's best to make sure you explicitly state the type of data using that type field, but in nodejs the assumed input type is a Buffer. In the browser, where Buffer is not available, the default is base64.

This will be added to the README.

[reviewher]

This is in the Input Type subsection of the Parsing Options section in the README.

[ghost]

You can read data from buffers as follow

var workbook = XLSX.read(buffer, {type:"buffer"});

[tmzaragoza]

I am getting Error: End of data reached (data length = 17910, asked index = 28335). Corrupted zip? when I run this through Lambda. Any suggestions?

`const buffers = [];
buff.on('data', (data) => { console.log('data', data); buffers.push(data); });
buff.on('end', () => {
console.log('ending');
const buffer = Buffer.concat(buffers);

console.log('buffer: ', buffer);
const workbook = XLSX.read(buffer);
console.log('workbook: ', workbook);

cb({ workbook })`

[SheetJSDev]

@tmzaragoza that looks like lambda is messing with the data. Can you test by modifying your endpoint to read as base64 (XLSX.read(buffer.toString(), type: "base64")), base64-encoding a sample file and pushing it to your endpoint?

[jzohrab]

Having the same problem with Lambda. @tmzaragoza did you solve this? I'll post here if we find a solution.

[tmzaragoza]

@jzohrab We needed to use multer to parse the data

[tiagolabsit]

@tmzaragoza would you share your implementation? I parse data using req.file.buffer.toString('base64') and got the same trouble Error: End of data reached...

[crazyoptimist]

This worked for me:

import * as XLSX from "xlsx"

const workbook = XLSX.read(req.file.buffer.data, { type: "buffer" })

[reviewher]

@tmzaragoza @jzohrab @tiagolabsit https://github.com/SheetJS/sheetjs/tree/master/demos/function#aws-lambda

When reading form data, be sure to include the necessary binary types on the AWS API Gateway console. To do this, navigate to the "Binary Media Types" section in the settings tab of the console. For reading a file, you may need to add "multipart/form-data". For downloading a file, you may need to add "application/vnd.ms-excel".