Posts Tagged Jquery

How to read Excel files in Javascript/Jquery and convert the value to Json

Introduction

We have multiple ways to read excel file in C# and then convert it to JSON or insert the data in to database etc. However there are not much options available when it comes to reading the Excel file using javascript. In this article I will explain an option using a Jquery Plugin called SheetJS.

SheetJS is Excel parser which supports multiple formats of excel files like 2007, 2010, 2013 office versions etc.

Implementation

To use the SheetJS user need to add the reference files first. You can do this either by downloading files from here and then refer these js files to project. Another option is to use the online CDN available.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.7/xlsx.core.min.js"></script>

Reading the Excel File

To read the excel file we use the read method in SheetJs like below

var workbook = XLSX.read(data, { type: 'binary' });

Converting Excel value to Json
To convert the values to Json will use “sheet_to_json” method

          var sheet_name_list = workbook.SheetNames;
           sheet_name_list.forEach(function (y) { /* iterate through sheets */
                    //Convert the cell value to Json
                    var roa = XLSX.utils.sheet_to_json(workbook.Sheets[y]);
                    if (roa.length > 0) {
                        result = roa;
                    }
                });

Complete Code

Sample Excel I used for this Demo

ujcrvsc

 

, , , ,

1 Comment

How to hide columns based on values selected in checkbox

Introduction:

How to hide columns based on values selected in checkbox

Description:

In this article I am going to explain how to hide columns in grid view based on user selection in checkbox. We can achieve this requirement with both client side and server side approach. Here I am going to explain the client side approach where we uses Jquery to hide column in gridview.

Gridview will render as table with header and rows at runtime, we will use jquery selector to access this header and calculate selected column index and then hide or show accordingly.

Function to hide and display Column

 <script type="text/javascript">
        $(document).ready(function () {
            //Attach click to checkboxlist
            $("#CheckBoxList1").click(function () {
                //loop through each item in checkboxlist
                $("[id*=CheckBoxList1] input").each(function () {
                    //Get the Gridview header row
                    var tblhead = $("#GridView1 th");
                    //Get the selected column index in gridview
                    var columnIndex = tblhead.index($("table[id*=GridView1] th:contains('" + $(this).next().html() + "')")) + 1;
                    //check if current items in checkboxlist is selected or not
                    if ($(this).is(":checked")) {
                        //if so then hide that column
                        $("table[id*=GridView1]").find("th:nth-child(" + columnIndex + "), td:nth-child(" + columnIndex + ")").hide();
                    } else {
                        //if not then display the column
                        $("table[id*=GridView1]").find("th:nth-child(" + columnIndex + "), td:nth-child(" + columnIndex + ")").show();
                    }
                });
            });
        });
    </script>

Complete Code

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    	<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
    <script src="//code.jquery.com/jquery-1.10.2.js"></script>
    <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
    	<link rel="stylesheet" href="/resources/demos/style.css">
    <script type="text/javascript">
        $(document).ready(function () {
            //Attach click to checkboxlist
            $("#CheckBoxList1").click(function () {
                //loop through each item in checkboxlist
                $("[id*=CheckBoxList1] input").each(function () {
                    //Get the Gridview header row
                    var tblhead = $("#GridView1 th");
                    //Get the selected column index in gridview
                    var columnIndex = tblhead.index($("table[id*=GridView1] th:contains('" + $(this).next().html() + "')")) + 1;
                    //check if current items in checkboxlist is selected or not
                    if ($(this).is(":checked")) {
                        //if so then hide that column
                        $("table[id*=GridView1]").find("th:nth-child(" + columnIndex + "), td:nth-child(" + columnIndex + ")").hide();
                    } else {
                        //if not then display the column
                        $("table[id*=GridView1]").find("th:nth-child(" + columnIndex + "), td:nth-child(" + columnIndex + ")").show();
                    }
                });
            });
        });
    </script>
</head>
<body>
<form id="form1" runat="server">
        <asp:CheckBoxList ID="CheckBoxList1" runat="server">
            <asp:ListItem Value="AddressID">AddressID</asp:ListItem>
            <asp:ListItem Value="AddressLine1">AddressLine1</asp:ListItem>
            <asp:ListItem Value="City">City</asp:ListItem>
            <asp:ListItem Value="StateProvinceID">StateProvinceID</asp:ListItem>
            <asp:ListItem Value="PostalCode">PostalCode</asp:ListItem>
            <asp:ListItem Value="ModifiedDate">ModifiedDate</asp:ListItem>
        </asp:CheckBoxList>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowSorting="True" DataSourceID="SqlDataSource2">
            <Columns>
                <asp:BoundField DataField="AddressID" HeaderText="AddressID" />
                <asp:BoundField DataField="AddressLine1" HeaderText="AddressLine1" />
                <asp:BoundField DataField="City" HeaderText="City" />
                <asp:BoundField DataField="StateProvinceID" HeaderText="StateProvinceID" />
                <asp:BoundField DataField="PostalCode" HeaderText="PostalCode" />
                <asp:BoundField DataField="ModifiedDate" HeaderText="ModifiedDate" />
            </Columns>
        </asp:GridView>

        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"
            SelectCommand="SELECT top 20 [AddressID], [AddressLine1],City,StateProvinceID,PostalCode,ModifiedDate FROM Person.Address"></asp:SqlDataSource>
    </form>

</body>
</html>

demo

, , , , , ,

Leave a comment

How to load data from database to Asp.Net Gridview using WebAPI

Introduction:

In this article we are discussing how to load data from database to Asp.Net Gridview using WebAPI.

Description:

We use WebAPI to get the data from database and then use the Jquery getJSON() method to Load JSON-encoded data from the server using a GET HTTP request.

Implementation:

In the New ASP.NET Project dialog, select the Empty template. Under “Add folders and core references for”, check WebForms , Web API. Click OK.

2016-04-30 19_11_49-Microsoft Visual Studio (Administrator)

When using WEBAPI you will get data in client side and you need to manipulate it at client side so that the data will get populated in Gridivew. Point to note here is we cannot go with the normal approach of setting DataSource and calling DataBind method. We have various options to load data into grid like AngularJs, Jquery etc.

As mentioned earlier in this article I am using getJSON() method to fetch the value and then populate the data in gridview. Gridview will load as table at runtime we will access this table and then append the values to it.

2016-04-30 14_29_24-http___localhost_52678_WebForm1.aspx - Internet Explorer

Sample Code:

HTML:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <script src="https://code.jquery.com/jquery-2.1.4.js"></script>
    <script type="text/javascript">
        //Code to load the data to gridview on pageload
        $(document).ready(function () {
           //Here you need to provide the webapi address
            $.getJSON('api/Customer')
                .done(function (data) {
                    $.each(data, function (key, item) {
                        //Add the result to gridview
                        $("#GridView1").append("

<tr>

<td>" + item.CustomerID + "</td>


<td>" + item.CompanyName + "</td>


<td>" + item.ContactName + "</td>


<td>" + item.ContactTitle + "</td>


<td>" + item.Address + "</td>

</tr>
");
                    });
                })
            .fail(function (d) {
                alert("error occured" + d.responseText);
            });
        });
    </script>
</head>
<body>
<form id="form1" runat="server">
<div>
            <asp:GridView ID="GridView1" runat="server" Width="1000px" ShowHeaderWhenEmpty="true"                 CellPadding="0" CellSpacing="0">
            </asp:GridView></div>
</form>

</body>
</html>

C#:

First we need to populate an empty row in GridView. Reason behind this is if we didn’t assign any datasource then the GridView won’t populate at runtime and we won’t be able to access it at client side.

protected void Page_Load(object sender, EventArgs e)
        {
            //Load and empty row to gridview
            if (!IsPostBack)
            {
                DataTable dt = new DataTable();
                dt.Columns.Add(&quot;CustomerID&quot;);
                dt.Columns.Add(&quot;CompanyName&quot;);
                dt.Columns.Add(&quot;ContactName&quot;);
                dt.Columns.Add(&quot;ContactTitle&quot;);
                dt.Columns.Add(&quot;Address&quot;);
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }

WebAPI Code:

When we add the WebAPI references in your project you will see a Model folder and Controller folder.

2016-04-30 19_20_50-WebAPISample - Microsoft Visual Studio (Administrator)

Model

Here we add the Model classes which basically represents data in WebAPI. For this article we add Customer class. Right Click on the Model folder and choose Add and then select class file. Name it like Customer

public class Customer
    {
        public string CustomerID { get; set; }
        public string CompanyName { get; set; }
        public string ContactName { get; set; }
        public string ContactTitle { get; set; }
        public string Address { get; set; }
    }

Controllers:

In controller we will add the code to fetch values from database. Right click on the Controllers folder and Choose Add and then select Controller. In code you can see an HttpGet entry added above to GetCustomers method. This is because Rest WebAPI is making use of verbs(Get,Post,Put,Delete) for each actions in Read,Create,Update,Delete respectively. You can get more details from here

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using WebApiSample.Models;

namespace WebApiSample.Controllers
{
    public class CustomerController : ApiController
    {
        [HttpGet]
        public IEnumerable<Customer> GetCustomers()
        {
            Customer[] aryCustomers = null;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NORTHWINDConnectionString"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "SELECT TOP 10 CustomerID,CompanyName,ContactName,ContactTitle,Address FROM Customers";
                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        List<Customer> lstcustomers = new List<Customer>();
                        while (reader.Read())
                        {
                            lstcustomers.Add(new Customer
                            {
                                CustomerID = reader["CustomerID"].ToString(),
                                CompanyName = reader["CompanyName"].ToString(),
                                ContactName = reader["ContactName"].ToString(),
                                ContactTitle = reader["ContactTitle"].ToString(),
                                Address = reader["Address"].ToString()
                            });
                            aryCustomers = lstcustomers.ToArray();
                        }
                    }
                }
            }
            return aryCustomers;
        }
    }
}

, , , , , , ,

Leave a comment

How to add a character counter to AjaxControlToolkit HTMLEditorExtender control

Introduction:

How to add a character counter to AjaxControlToolkit HTMLEditorExtender control

Description:

In this article we are discussing how to add a line counter to HTML Editor Extender control. Usually it’s very straight forward to add character counter to textbox or textarea by using its id.We generally use keypress event in textbox control to call character counter function.

However when we use at HTMLEditorExtender control we will face an issue as the HTMLEditorExtender control will render as a div with ContentEditable property set to true.  Div doesn’t not have a keypress event.

gxjynaa

As a solution you can use Jquery bind method to attach DOMSubTreeModified event to div and then call the character counter function in this event.

Code to attach character counter:

<script src="https://code.jquery.com/jquery-1.11.3.js"></script>
<script type="text/javascript">
      $(document).ready(function () {
         //attach change event to div using Jquery css class selector
         $('.ajax__html_editor_extender_texteditor').bind("DOMSubtreeModified", function () {
         //call function to calcualate count on change event of div
             textCounter();
          });
     });
     function textCounter() {
          //Get the text count
          var divlength = $('.ajax__html_editor_extender_texteditor').text().length;
          //check if count greater than 100
          if (divlength &gt; 50) {
          //Trim the text
          var trimmedtext = $('.ajax__html_editor_extender_texteditor').text().substring(0, 100);
         //assign it to editor
         $('.ajax__html_editor_extender_texteditor').html(trimmedtext);
        } else{
            //reduce the allowed no of characters in textbox
            $('#remLen').val(50 - divlength);
        }
     }
</script>
Demo

TxtCounter

, , , , ,

Leave a comment

Cascading Dropdownlist using AngularJs in Asp.Net

Introduction:

In this article I am going to explain you how to implement the cascading dropdownlist using AngularJs.  This is a continuation to my previous article where I have blogged about how to populate the Dropdownlist from AngularJs. Hence I won’t be explaining the first part where we populate the Order Dropdownlist. You can get the details about that from link above.

Implementation:

Here we make use of ng-change event in AngularJs to call the function which in turn populate the CustomerList based on OrderID selected. ng-change event will fire when user changes the input in element  and the new values to be committed to model.

AngularJS code to populate the Order and Customer Dropdownlist

HTML:

C#:

Demo

, , ,

2 Comments

How to highlight the selected rows in Repeater using Jquery

Introduction:

In this article I am going to explain how to highlight the selected row in Repeater control using Jquery.  At runtime Repeater control will render as table we will be manipulating this table structure to access each row and then use the css to highlight the row.

Prerequisites:

You need to add the necessary JQuery files to your page prior to use this plugin. In this example I have used the online JQuery CDN files available. If you don’t want to use the online available cdn file then you can manually add the jquery files to your solution. I have explained the steps here in detail.

Solution:

We will use the below css to hightlight the rows in gridview.

<style type="text/css">
        .highlightRow
        {
            background-color: #9999FF;
        }
    </style>

Jquery Code:             

Below Jquery function will remove the css from all other rows except the selected row

 <script src="http://code.jquery.com/jquery-1.11.0.min.js"></script>
 <script>
    $(function () {
         $(".trclass").click(function () {
            $(this).addClass("highlightRow").siblings().removeClass("highlightRow");
          });
     });
 </script>

HTML

   <asp:Repeater runat="server" ID="Repeater1">
            <HeaderTemplate>
                <table width="500px" border="1px">
                    <tr style="background-color: #fb7700">
                        <td>Value1
                        </td>
                        <td>Value2
                        </td>
                    </tr>
            </HeaderTemplate>
            <ItemTemplate>
                <tr class="trclass">
                    <td>
                        <asp:Label ID="lblValue1" runat="server" Text='<%# Eval("val1") %>'></asp:Label>
                    </td>
                    <td>
                        <asp:Label ID="lblValue2" runat="server" Text='<%# Eval("val2") %>'></asp:Label>
                    </td>
                </tr>
            </ItemTemplate>
            <FooterTemplate>
                </table>
            </FooterTemplate>
        </asp:Repeater>

Below code is to populate dummy data, you dont need this code if you are populating the repeater from database in code behind.

C#:

  protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataTable table1 = new DataTable();
                table1.Columns.Add("val1");
                table1.Columns.Add("val2");
                table1.Rows.Add("Value1", "Message1");
                table1.Rows.Add("Value2", "Message2");
                table1.Rows.Add("Value3", "Message3");
                table1.Rows.Add("Value4", "Message4");
                table1.Rows.Add("Value5", "Message5");
                table1.Rows.Add("Value6", "Message6");
                table1.Rows.Add("Value7", "Message7");
                Repeater1.DataSource = table1;
                Repeater1.DataBind();
            }
        }

Demo

, , , , ,

Leave a comment

How to implement a GridView in MVC

Introduction:

In this article I am going to explain how to implement a GridView in MVC.

Prerequisites:

We are going to use Grid.Mvc Jquery plugin to implement GridView in MVC. You can get more details of this control from here.

To use Grid.Mvc we have to first add the necessary Jquery files to solution. One easy option to do this is to use the Nugget package to add Grid.Mvc to your solution. You can use the below command

install-package Grid.Mvc

After successful installation you will be able to see the below messages.

You can also download the file and add it to your solution manually.

Implementation:

Here I am going to explain how to implement GridView in MVC without using the table approach.

Model:

First you need to create a class in the Model. In this example I have used the DatabaseFirst approach.

You can use the below steps to generate the class files

  • Right click on the Model folder
  • Select Add option
  • And then select the New Item option
  • Select the Net Entity Data Model option and Provide a name for it
  • In the Entity Data Model Wizard, Select EF Designer from database and click on Next

If you already have connection string configured then you can select that from the available list.

If you don’t have the connection string configured then use the New Connection option and create the connection

  • Expand the Tableoption and then select the tables which you want
  • Click on Finish
  • You will have necessary class files generated in your solution now

You can get more details about how to use DatabaseFirst approach from this link.

Controller:

Now we have the Model files created from database. Next step is to fetch the data from database and then pass it to the View from Controller. We will have a basic select in EF for this demo. You can modify it as per your need.

  public ViewResult Index()
        {
            //Create db context object here this is test record
            AdventureWorksDbContext dbContext = new AdventureWorksDbContext();
            //Get the value from database and then Pass it View
            var items = dbContext.Addresses.Take(10);
            return View(items.ToList());
        }

View:

In View you can use the below code to create GridView in MVC. This will generate a very basic GridView in MVC View

@model IEnumerable<MvcApplication1.Models.Address>
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <!-- CSS Includes -->
    <link rel="stylesheet" href="//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css">
    <link href="@Url.Content("~/Content/Gridmvc.css")" rel="stylesheet" type="text/css" />
    <!-- Script Includes-->
    <script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
    <script src="@Url.Content("~/Scripts/gridmvc.min.js")" type="text/javascript"> </script>
</head>
<body>
    <div style="width:550px">
        @*Refer the GridView reference here*@
        @using GridMvc.Html
        @Html.Grid(Model).Columns(columns =>
           {
               //Put the column details here
               columns.Add(s => s.AddressID).SetWidth(110).Sortable(true);
               columns.Add(s => s.AddressLine1).Sortable(true).SetWidth(110);
               columns.Add(s => s.City).Sortable(true).SetWidth(110);
               columns.Add(s => s.ModifiedDate).Sortable(true).SetWidth(110);
           }).WithPaging(20)
    </div>
</body>
</html>

Demo

Here I have used very little options, Grid.Mvc plugin supports variety of options like CustomColumns, Button controls in column, Editing in Gridview. You can get more details of this control from here.

, , ,

Leave a comment