Import various spreadsheets with JS-XLSX

[mjhaston]

I recently had the need to import a .xlsx spreadsheet which couldn't be accomplished with CPYFRMIMPF. Since we have Node running on our IBMi, I was able to use an NPM package JS-XLSX (https://github.com/SheetJS/sheetjs) which can read .txt, .csv. xls, .xlsx and others. For those imports without user intervention my RPG program makes a REST call to an express server instance passing the path and filename. My Node script uses JS-XLSX to read the file from the IFS and sends the header and row data in JSON.


When we need the user to validate the data as it's imported, we allow them to select a file and we present it in an ExtJS grid. Errors and warnings are highlighted, validated against our ERP, before we allow the data to be imported. http://haston.name/iseries/import/IBMi_ExtJS_import.mp4

The call from the SQLRPGLE program:

select systools.httpPostClob(
               'http://123.123.123.123:99999/import',
               cast('<httpHeader><header name="Accept" value="application/json"/><header name="Content-Type" value="application/json"/></httpHeader>'  as clob(1k) ccsid 1208),
               cast(' ' as clob(20k) ccsid 1208))

             from sysibm.sysdummy1

I'm sure someone much better with Node and Javascript can clean this up a bit, but it works.

var express = require('express'),
    XLSX = require('xlsx'),
    app = express(),
    ip = '123.123.123.123',
    port = 99999;
app.listen(port, ip, function () {
    console.log('JS-XLSX API server is listening on port ' + port + '!');
});
app.use(express.json({
    type: '*/*'
}));
app.get('/', function (req, res) {
    res.send('JS-XLSX API server');
});
app.post('/', function (req, res) {
    res.send('JS-XLSX API server');
});
app.post('/import', function(req, res) {
    var body = req.body,
        workbook = {},
        worksheet = {},
        response = {},
        filename;
    res.set('Content-Type', 'text/json');
    if (body && body.filename) {
        filename = body.filename;
        workbook = XLSX.readFile(filename);
        worksheet = workbook.SheetNames[0];
        jsonData = XLSX.utils.sheet_to_json(workbook.Sheets[worksheet], {
            raw: true,
            range: 1
        });
        response = {
            jsonData: jsonData
        };

    } else {
        response = {
            error: true,
            body: body,
            filename: filename
        };
    }
    res.status(200).json(response);
});