SpredJS nodeJS import from json to excel

Posted by: kpatel on 6 March 2019, 2:18 am EST

  • Posted 6 March 2019, 2:18 am EST

    Hello,

    I am trying to build a nodeJS application that will allow us to convert excel to spreadJS json and save it in our database and then at some point convert them back from spredJS Json to excel.

    I am using excelIO api I am able to save from excel to json but when I take the same json and trying to convert from json to excel it gives
    { errorCode: 1, errorMessage: 'Incorrect file format.' }


    Here is the sample code I am using to test the API before I write real production code

    var mockBrowser = require('mock-browser').mocks.MockBrowser;
    var fileReader = require('FileReader');
    var fs = require('fs')
    global.FileReader = fileReader;
    global.window = mockBrowser.createWindow()
    global.document = window.document
    global.navigator = window.navigator
    global.HTMLCollection = window.HTMLCollection
    global.getComputedStyle = window.getComputedStyle
    var GC = require('@grapecity/spread-sheets')
    var GCExcel = require('@grapecity/spread-excelio');
    var excelIo = new GCExcel.IO();

    try {
    var file = fs.readFileSync('/home/kpatel/Desktop/test.xlsx');
    excelIo.open(file.buffer, (data) => {
    //yay excel converted to json and I can save it in database
    console.log(JSON.stringify(data));

    //taking same json and trying to convert it back to excel its giving error here
    excelIo.save(data, function (blob) {
    console.log(blob);
    }, function (e) {
    console.error(e);
    });
    });
    } catch (e) {
    console.error("** Error manipulating spreadsheet **");
    console.error(e);
    }

  • Replied 6 March 2019, 2:23 am EST

    fyi here is the json I get when converting the excel, but then its the conversion from json to excel that fails.
    {
    "version": "12.0.9",
    "namedStyles": [
    {
    "foreColor": "#000000",
    "themeFont": "Body",
    "font": "normal normal 14.7px Calibri",
    "locked": true,
    "textIndent": 0,
    "wordWrap": false,
    "vAlign": 2,
    "hAlign": 3,
    "borderLeft": null,
    "borderRight": null,
    "borderTop": null,
    "borderBottom": null,
    "borderVertical": null,
    "borderHorizontal": null,
    "diagonalUp": null,
    "diagonalDown": null,
    "backColor": null,
    "name": "__builtInStyle1"
    },
    {
    "foreColor": null,
    "font": "normal normal 14.7px Tahoma",
    "locked": true,
    "textIndent": 0,
    "wordWrap": false,
    "vAlign": 2,
    "hAlign": 3,
    "borderLeft": null,
    "borderRight": null,
    "borderTop": null,
    "borderBottom": null,
    "borderVertical": null,
    "borderHorizontal": null,
    "diagonalUp": null,
    "diagonalDown": null,
    "backColor": null,
    "name": "__builtInStyle2"
    }
    ],
    "startSheetIndex": 0,
    "activeSheetIndex": 0,
    "tabStripRatio": 0.6,
    "sheetCount": 1,
    "sheets": {
    "Evaluation Version": {
    "name": "Evaluation Version",
    "index": 0,
    "allowCellOverflow": true,
    "defaults": {
    "colHeaderRowHeight": 20,
    "rowHeaderColWidth": 40,
    "rowHeight": 30,
    "colWidth": 60
    },
    "rowCount": 20,
    "columnCount": 20,
    "data": {
    "dataTable": {
    "1": {
    "1": {
    "value": "License Not Found"
    }
    },
    "2": {
    "1": {
    "value": "You need a valid license key to run Spread.Sheets."
    }
    },
    "3": {
    "1": {
    "value": "Temporary keys are available for evaluation. "
    }
    },
    "4": {
    "1": {
    "value": "If you purchased a license, your key is in your purchase confirmation email. "
    }
    },
    "5": {
    "1": {
    "value": "Email us.sales@grapecity.com if you need assistance."
    }
    }
    }
    }
    }
    }
    }
  • Replied 7 March 2019, 2:49 am EST

    Hi,

    We are able to replicate the issue and hence it has been forwarded to the concerned team for further investigation. We will let you know regarding any updates on this.
    Sorry for the inconvenience caused.

    Regards
  • Replied 19 March 2019, 10:19 pm EST

    Hello,

    Tracking id for your issue is #270881. We will update you once we will get the response from our concerned team.

    Thanks,
    Reeva
  • Marked as Answer

    Replied 31 March 2019, 3:03 pm EST

    Hi,

    According to the devTeam, The issue was arising because the node doesn't have a blob type, so we should use the buffer instead. Please refer to the following code snippet and attached sample:
    excelIo.open(file.buffer, (data) => {
    //yay excel converted to json and I can save it in database
    excelIo.save(data, (blob)=> {
    console.log(blob);
    fs.appendFileSync(path.join(__dirname, 'out.xlsx'), Buffer.from(blob));
    }, (e)=> {
    console.error(e);
    }, {
    useArrayBuffer: true
    });
    });


    Regards
    spreadSampleExcelIO.zip
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels