how can i select or retrieve cell data in excel sheet using javascript?

First laod excel sheet data

var csvdata = "-1";

if (window.XMLHttpRequest)
    oReq = new XMLHttpRequest();
else if (window.ActiveXObject)
    oReq = new ActiveXObject('MSXML2.XMLHTTP.3.0');
else
    throw "XHR unavailable for your browser";
var __excelurl = "filename.xlsx";
if (__excelurl.toUpperCase().indexOf(".XLS") > -1 || __excelurl.toUpperCase().indexOf(".XLSX") > -1)
{
    oReq.open("GET", __excelurl, true);
}
xlsfile = false;

function getdata()
{
    if (__excelurl.toUpperCase().indexOf(".XLS") > -1 || __excelurl.toUpperCase().indexOf(".XLSX") > -1)
    {
        if (__excelurl.toUpperCase().indexOf(".XLSX") < 0)
        {
            xlsfile = true;
        }
        if (typeof Uint8Array !== 'undefined')
        {
            oReq.responseType = "arraybuffer";
            oReq.onload = function (e)
            {
                if (typeof console !== 'undefined')
                    console.log("onload", new Date());
                var arraybuffer = oReq.response;
                var data = new Uint8Array(arraybuffer);
                var arr = new Array();
                for (var i = 0; i != data.length; ++i)
                    arr[i] = String.fromCharCode(data[i]);
                var wb = XLSX.read(arr.join(""), {type: "binary"});
                ;
                process_wb(wb);
            };
        } else
        {
            oReq.setRequestHeader("Accept-Charset", "x-user-defined");
            oReq.onreadystatechange = function ()
            {
                if (oReq.readyState == 4 && oReq.status == 200)
                {
                    var ff = convertResponseBodyToText(oReq.responseBody);
                    if (typeof console !== 'undefined')
                        console.log("onload", new Date());
                    var wb = XLSX.read(ff, {type: "binary"});
                    ;
                    process_wb(wb);
                }
            };
        }
        oReq.send();
    } else
    {
        $.ajax({url: __excelurl, aync: false, success: function (csvd) {
                csvdata = csvd;
                to_csv()
            },
            dataType: "text",
            complete: function () {}
        });
    }
}
getdata();

Now convert excel data to csv

var finaldata = [];
function process_wb(wb)
{
    icount = 0;
    inprocess = false;
    pcount = 0;
    output = to_csv(wb);
}

function to_csv(workbook)
{
    csvcount = 0;
    if (csvdata == "-1")
    {
        csvdata = [];
        try
        {
            workbook.SheetNames.forEach(function (sheetName) {
                if (xlsfile)
                {
                    var csv = XLS.utils.sheet_to_csv(workbook.Sheets[sheetName]);
                } else
                {
                    var csv = XLSX.utils.sheet_to_csv(workbook.Sheets[sheetName]);
                }
                if (csv.length > 0)
                {
                    csvtemp = csv.split("\n");
                    for (icsv = 0; icsv < csvtemp.length; icsv++)
                    {
                        if (!csvtemp[icsv])
                        {
                            continue;
                        }
                        csvcount++;
                        var xdata = csvtemp[icsv];
                        csvdata.push(xdata);
                    }
                }
            });
        } catch (e)
        {
            console.log('Format not compatible. Convert the data to Excel or CSV or Paste to see the details in the map.', 1, "Error !!!", "");
            return;
        }
    } else
    {
        if (csvdata.length > 0)
        {
            csvtemp = csvdata.split("\n");
            csvdata = [];
            for (icsv = 0; icsv < csvtemp.length; icsv++)
            {
                if (!csvtemp[icsv])
                {
                    continue;
                }
                var xdata = csvtemp[icsv];
                csvdata.push(xdata);
            }
        }
    }
    if (csvdata.length == 0)
    {
        console.log("No data to generate chart");
        return;
    }
    header = csvdata[0];
    header = header.split('"');
    header = header.join("")
    header.replace(/'"'/g, '');
    header = header.split(",");
    finaldata = [];
    for (idata = 1; idata < csvdata.length; idata++)
    {
        fdata = {};
        ftdata = csvdata[idata]
        ftx = ftdata.split('"');
        ftdata = ftx.join("")
        ftdata.replace(/'"'/g, '');
        ftdata = ftdata.split(',');
        for (ihead = 0; ihead < header.length; ihead++)
        {
            if (!header[ihead])
            {
                continue;
            }
            ftx = ftdata[ihead];
            ftx = ftx.replace(/'"'/g, "")
            fdata[header[ihead]] = ftx;
        }
        finaldata.push(fdata);
    }    
}
console.log(finaldata);

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top