Highlight Matching Rows With SheetJs

Asked by At

I am trying to work on a project where I've to upload an excel file using SheetJs and then save the data from there to database. It's working fine and here is the code samples that I tried so far:

Sample.aspx:

<link href="Styles/bootstrap.css" rel="stylesheet" />
<script src="jquery-1.10.2.min.js" type="text/javascript"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.7/xlsx.core.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.core.min.js"></script>
<script type="text/javascript" src="custom.js"></script>

<form id="form1" runat="server">
    <div class="container">
        <input type="file" id="excelfile" />
        <input type="button" id="viewfile" value="Export To Table" onclick="ExportToTable()" />
        <input type="button" id="dataTable" value="Read Table Data" onclick="ReadTableData()" />

        <input type="button" id="checkServerSide" value="Read Server Data" onclick="GetServerData()" />
        <br />
        <br />

        <div class="col-md-6">
            <table class="table table-responsive zui-table" id="exceltable">
            </table>
        </div>
        <script type="text/javascript">

        </script>
    </div>
</form>

Custom.js: This is the big one

var serverData = new Array();
var distinctIDs = new Array();

function ExportToTable() {
    $("#exceltable tr").remove();
    var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xlsx|.xls)$/;
    /*Checks whether the file is a valid excel file*/
    if (regex.test($("#excelfile").val().toLowerCase())) {
        var xlsxflag = false; /*Flag for checking whether excel is .xls format or .xlsx format*/
        if ($("#excelfile").val().toLowerCase().indexOf(".xlsx") > 0) {
            xlsxflag = true;
        }
        /*Checks whether the browser supports HTML5*/
        if (typeof (FileReader) != "undefined") {
            var reader = new FileReader();
            reader.onload = function (e) {
                var data = e.target.result;
                /*Converts the excel data in to object*/
                if (xlsxflag) {
                    var workbook = XLSX.read(data, { type: 'binary' });
                }
                else {
                    var workbook = XLS.read(data, { type: 'binary' });
                }
                /*Gets all the sheetnames of excel in to a variable*/
                var sheet_name_list = workbook.SheetNames;

                var cnt = 0; /*This is used for restricting the script to consider only first sheet of excel*/
                sheet_name_list.forEach(function (y) { /*Iterate through all sheets*/
                    /*Convert the cell value to Json*/
                    if (xlsxflag) {
                        var exceljson = XLSX.utils.sheet_to_json(workbook.Sheets[y]);
                    }
                    else {
                        var exceljson = XLS.utils.sheet_to_row_object_array(workbook.Sheets[y]);
                    }
                    if (exceljson.length > 0 && cnt == 0) {
                        BindTable(exceljson, '#exceltable');
                        console.log(exceljson);
                        console.log(distinctValue(exceljson, "ID"))
                        distinctIDs = distinctValue(exceljson, "ID");

                        if (distinctIDs.length > 0) {
                            GetServerData(distinctIDs)
                        }
                        cnt++;
                    }
                });

                $('#exceltable').show();

                /**This section is used to edit table rows on double click**/
                $('.zui-table').find('td').each(function () {
                    $(this).click(function () {
                        $('.zui-table td').not($(this)).prop('contenteditable', false);
                        $(this).prop('contenteditable', true);
                    });
                    $(this).blur(function () {
                        $(this).prop('contenteditable', false);
                    });
                });
            }

            if (xlsxflag) {/*If excel file is .xlsx extension than creates a Array Buffer from excel*/
                reader.readAsArrayBuffer($("#excelfile")[0].files[0]);
            }
            else {
                reader.readAsBinaryString($("#excelfile")[0].files[0]);
            }
        }
        else {
            alert("Sorry! Your browser does not support HTML5!");
        }
    }
    else {
        alert("Please upload a valid Excel file!");
    }
}

function distinctValue(arr, item) {
    var results = [];
    for (var i = 0, l = arr.length; i < l; i++)
        if (!item) {
            if (results.indexOf(arr[i]) === -1)
                results.push(arr[i]);
        } else {
            if (results.indexOf(arr[i][item]) === -1)
                results.push(arr[i][item]);
        }
    return results;
};


debugger;
function GetServerData(datas) {

    distinctIDs = new Array();

    var postData = { values: datas };

     $.ajax({
       type: "POST",
       url: "/Sample.aspx/GetData2",
       data: JSON.stringify(postData),
       contentType: "application/json; charset=utf-8",
       dataType: "json",
       success: function (data) {
           distinctIDs = data.d;
           console.log(distinctIDs)
       }
   });
}

function BindTable(jsondata, tableid) {/*Function used to convert the JSON array to Html Table*/
    var columns = BindTableHeader(jsondata, tableid); /*Gets all the column headings of Excel*/
    for (var i = 0; i < jsondata.length; i++) {
        var row$ = $('<tr/>');
        for (var colIndex = 0; colIndex < columns.length; colIndex++) {
                var cellValue = jsondata[i][columns[colIndex]];
                if (cellValue == null)
                    cellValue = "";

                if (GetServerData(jsondata[i][columns[1]]) == "123456")
                    row$.append($('<td/>').html(cellValue).addClass('red'));

                    row$.append($('<td/>').html(cellValue));
            }

        $(tableid).append(row$);
    }
}

function BindTableHeader(jsondata, tableid) {/*Function used to get all column names from JSON and bind the html table header*/
    var columnSet = [];
    var headerTr$ = $('<tr/>');
    for (var i = 0; i < jsondata.length; i++) {
        var rowHash = jsondata[i];
        for (var key in rowHash) {
            if (rowHash.hasOwnProperty(key)) {
                if ($.inArray(key, columnSet) == -1) {/*Adding each unique column names to a variable array*/
                    columnSet.push(key);
                    headerTr$.append($('<th/>').html(key));
                }
            }
        }
    }

    $(tableid).append(headerTr$);
    return columnSet;
}

function ReadTableData() {
    //Loop through the Table rows and build a JSON array.
    var dataAll = new Array();
    $("#exceltable TBODY TR").each(function () {
        var row = $(this);
        var data = {};
        data.id = row.find("TD").eq(0).html();
        dataAll.push(data);
    });

    //Send the JSON array to Controller using AJAX.
    $.ajax({
        type: "POST",
        url: "/Excel2Table/Sample.aspx/GetData",
        data: JSON.stringify({ "dataAll": dataAll }),
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: function (data) {
            alert("Total Data: " + data.d);
        }
    });
}

Now I've a requirement where I've to match data from database, say the excel file will have an id column and these ids should be validated from server-side. So in the above jQuery code, I retrieved data using Ajax as follows:

debugger;
function GetServerData(datas) {

distinctIDs = new Array();

var postData = { values: datas };

 $.ajax({
   type: "POST",
   url: "/Sample.aspx/GetData2",
   data: JSON.stringify(postData),
   contentType: "application/json; charset=utf-8",
   dataType: "json",
   success: function (data) {
       distinctIDs = data.d;
       console.log(distinctIDs)
   }
 });
} 

So what I tried, I got the server data using Ajax and put them into a global array variable for the matching. Now there is a BindTable method in the Custom.js file where I tried to match the ids as follows:

if (GetServerData(jsondata[i][columns[1]]) == "123456") //If matches, make the row red
row$.append($('<td/>').html(cellValue).addClass('red'));

Though it didn't work and I know, I am doing something childish here as I don't have in-depth idea on jSon. So would expect some suggestions on how to make it work.

Requirement Clarification: Excel file will have an id column with multiple rows and the matching rows will be highlighted red when it matches the server-side id as well excel file id.

Here is the C# method:

[WebMethod]
public static List<string> GetData2(List<string> values)
{
    List<string> validIDs = new List<string>();

    if(values.Count > 0)
    {
        foreach(string id in values)
        {
         var result = GetLst().Where(c => c.id == id).Select(d => d.id).ToList();

            if (result.Count > 0)
            {
                validIDs.Add(result.FirstOrDefault());
            }
        }
    }

    return validIDs;    
}

public static List<data> GetLst()
{
    List<data> aLst = new List<data>()
    {
        new data() { id = "123456" },
        new data() { id = "7891011" }
    };

    return aLst;
}

0 Answers