Read Excel file using c#.Net

In this article, I am going to show how to read Excel file using c#. To achieve that we are going to use
Excel Data Reader (http://exceldatareader.codeplex.com/) DLL.
To get Excel Data Reader DLL to your project use NuGet
readexcel
Code:

public void BtnUploadClick(object sender, EventArgs e)
        {
            string filePath = string.Empty;
            uploadPath = Server.MapPath("~/Excel/");            
                if (FileUpload1.HasFile)
                {
                    String fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                    String validExt = sAllowedExt;
                    if (validExt.IndexOf("," + fileExtension + ",") != -1)
                    { 
                        if (FileUpload1.FileBytes.Length <= maxsize)
                        {
                            filePath = uploadPath + FileUpload1.FileName; 
                            FileUpload1.PostedFile.SaveAs(filePath); 
                            Response.Write("File uploaded successfully.");
 
                            FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
                            IExcelDataReader excelReader;
                            if (filePath.Contains(".xlsx"))
                            {
                                //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
 
                            }  //...
                            else
                            {
                                //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                            } //...
                            //3. DataSet - The result of each spreadsheet will be created in the result.Tables
                            //DataSet result = excelReader.AsDataSet();
                            //...
                            //4. DataSet - Create column names from first row
                            excelReader.IsFirstRowAsColumnNames = true;
                            DataSet result = excelReader.AsDataSet();
                            //5. Data Reader methods
                            //foreach (DataRow item in result.Tables[0].Rows)
                            //{
                            //    //item[1].ToString();
                            //}
                            GridView1.DataSource = result;
                            GridView1.DataBind();
                        }
                        else
                        {
                            Response.Write("File size exceeded the permitted limit.");
                            return;
                        }
                    }
                    else
                    {
                        Response.Write("This file type is not accepted.");
                        return;
                    }      
                }
        }

Thanks

Download

[wpdm_file id=4]

Search, Sort in gridview using C#.Net, Ajax and jQuery

In this article, I will show you how to search in Gridview by using SqlDataSource’s FilterParameters and Sort by using jQuery tablesorter plugin.

Step:1 Create Searchbox and Gridview
Create asp:textbox called txtSearch to search data.
then create a simple Gridview called Gridview1 with TemplateFields for the fields that you would like to search for. Here I created a Two TemplateField for my two search fields, First Name and Last Name. The other point of interest is that the Eval statement for these 2 fields is wrapped around a function that we’re going to write called HighlightText which is use to highlight search text.

 
<asp:ScriptManager ID="ScriptManager" runat="server" />
      Search: <asp:TextBox ID="txtSearch" runat="server" OnTextChanged="txtSearch_TextChanged"  />
    <asp:UpdatePanel ID="UpdatePanel1" runat="server" >
        <ContentTemplate>
            
            <div class="GridviewDiv">
           
                 <asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="False" AllowPaging="True"
                    AllowSorting="true" DataSourceID="dsGridview" Width="540px" PageSize="10" CssClass="yui">
                    <Columns>
                        <asp:BoundField DataField="id" HeaderText="ID" SortExpression="id" ItemStyle-Width="40px"
                            ItemStyle-HorizontalAlign="Center" />
                        <asp:TemplateField HeaderText="First Name" SortExpression="FirstName">
                            <ItemStyle Width="120px" HorizontalAlign="Left" />
                            <ItemTemplate>
                                <asp:Label ID="lblFirstname" Text='<%# HighlightText(Eval("FirstName").ToString()) %>' runat="server"
                                    CssClass="TextField" />
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Last Name" SortExpression="LastName">
                            <ItemStyle Width="120px" HorizontalAlign="Left" />
                            <ItemTemplate>
                                <asp:Label ID="lblLastname" Text='<%# HighlightText(Eval("LastName").ToString()) %>' runat="server"
                                    CssClass="TextField" />
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:BoundField DataField="Department" HeaderText="Department" SortExpression="Department"
                            ItemStyle-Width="130px" />
                        <asp:BoundField DataField="Location" HeaderText="Location" SortExpression="Location"
                            ItemStyle-Width="130px" />
                    </Columns>
                </asp:GridView>
                </div>
        </ContentTemplate>
         <Triggers>
                <asp:AsyncPostBackTrigger ControlID="txtSearch" EventName="TextChanged" />
            </Triggers>
    </asp:UpdatePanel>

Note that in above code, I put gridview1 into asp:updatePanel and set trigger to refresh UpdatePanel (Gridview1) when txtSearch value changed. Please do not put txtSearch into UpdatePanel.

Step 2: Create a datasource with a FilterExpression
In order to enable our search functionality, add a FilterExpression to the datasource. The FilterExpression that I’m using checks for the first name and last name against the txtSearch Text box.

 
 <asp:SqlDataSource ID="dsGridview" runat="server" ConnectionString="<%$ ConnectionStrings:TempConnectionString %>"
        SelectCommand="SELECT * FROM [Employees]" FilterExpression="firstname like '%{0}%' or lastname like '%{1}%'">
        <FilterParameters>
            <asp:ControlParameter Name="firstname" ControlID="txtSearch" PropertyName="Text" />
            <asp:ControlParameter Name="lastname" ControlID="txtSearch" PropertyName="Text" />
        </FilterParameters>
    </asp:SqlDataSource>

Step 3: CodeBehind C#
Basically every time we’re displaying the First and Last name data in our Gridview, we check to see if there is any search text, and if there is, use a regular expression to enclose the search string in a CSS span which turns the text yellow.

 
   string SearchString = "";
    protected void Page_Load(object sender, EventArgs e)
    {
txtSearch.Attributes.Add("onkeyup", "setTimeout('__doPostBack(\\'" + txtSearch.ClientID.Replace("_", "$") + "\\',\\'\\')', 0);");
        if (!IsPostBack)
        {
            Gridview1.DataBind();
        }
    }
    protected void txtSearch_TextChanged(object sender, EventArgs e)
    {
         SearchString = txtSearch.Text;
    }
    public string HighlightText(string InputTxt)
    {
        string Search_Str = txtSearch.Text.ToString();
        // Setup the regular expression and add the Or operator.
        Regex RegExp = new Regex(Search_Str.Replace(" ", "|").Trim(), RegexOptions.IgnoreCase);
        // Highlight keywords by calling the 
        //delegate each time a keyword is found.
        return RegExp.Replace(InputTxt, new MatchEvaluator(ReplaceKeyWords));
        // Set the RegExp to null.
        RegExp = null;
    }
    public string ReplaceKeyWords(Match m)
    {
        return "<span class=highlight>" + m.Value + "</span>";
    }

Important: txtSearch_TextChanged event will not fire until you press enter. But to do a fast and easy search just typing in the textbox without havig to push any button or ‘enter’ to get the result back using AJAX updatepanel need to use Onkeyup event. just add this line to the Page_Load:

 
    txtSearch.Attributes.Add("onkeyup", "setTimeout('__doPostBack(\\'" + txtSearch.ClientID.Replace("_", "$") + "\\',\\'\\')', 0);");

Step 4: Add the CSS class for highlighting

Step 5: Sorting Gridview
I’m using jQuery tableSorter plugin to sort gridvew. you can download from here.

 
  <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
  <script type="text/javascript" src="http://ashishblog.com/ash/source/jquery.tablesorter-2.0.3.js"></script>
  <link type="text/css" rel="stylesheet" href="http://ashishblog.com/ash/source/style.css" />
  <script type="text/javascript">
     jQuery(document).ready(function () {
         $("#Gridview1").tablesorter({ debug: false, widgets: ['zebra'], sortList: [[0, 0]] });
     });
</script>

Thanks.