Wednesday, August 10, 2016

, ,

Exporting Data To MS Word in ASP.NET

Exporting Data From ASP.NET application to MS Word

Previously I have written 4 articles on exporting data from ASP.NET. Chek out all the Export Data Articles I have written.

Download source code here.

Let's Get into the Code

Below is the code you can have a look and then I will discuss every bit of it for your understanding.

protected void ExportToWord(object sender, EventArgs e)
    {
        DataTable dt = GetData();
    
        //Create a dummy GridView
        GridView GridView1 = new GridView();
        GridView1.AllowPaging = false;
        GridView1.DataSource = dt;
        GridView1.DataBind();
    
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename=MyReport.doc");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-word ";
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.RenderControl(hw);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
I guess this must be very clear to everyone what I have written inside. Anyway let's discuss it here.

  1. First, I get the data source from the method GetData(), which is nothing but just a method that returns a datatable with some data inside it. You can have your own dataset here from DB or wherever you want.
  2. Second, I have taken a grid view so that I can render that data in it. If you have a grid view at your page level then no need to take one and bind it here, even you need not to get the data again if you have already that data rendered in any gridview or what in your page.
  3. Third, Add Response headers, content types for Excel download. Remember the Content Type is very important, this is code which decides the file type you are downloading. So here for Excel sheet download the content Type is "application/vnd.ms-word". Also give name to the file you are downloading.
  4. Fourth, Get a string writer and Html writer and render the grid view inside it. Now the string writer will have the exact html of  the grid and we are all set to throw it into an Word File
  5. Fifth, Now write the string writer value into response object and you are all set to download the word file.
If you are looking for the GetData() method to test out the feasibility of this process of exporting data into excel sheet in ASP.NET and C# then here is it.
     private DataTable GetData()
     {
         // Here we create a DataTable with four columns.
         DataTable dtSample = new DataTable();
         dtSample.Columns.Add("Dosage", typeof(int));
         dtSample.Columns.Add("Drug", typeof(string));
         dtSample.Columns.Add("Patient", typeof(string));
         dtSample.Columns.Add("Date", typeof(DateTime));

         // Here we add five DataRows.
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         
         return dtSample;

     }
Hope this article helps you, as this method helped me a lot to overcome the unnecessary usage of external dll for exporting data into excel sheets.

Demo:

Export Data From ASP.NET




Publisher: Tapan kumar - 1:06 AM
, ,

Exporting Data To PDF in ASP.NET

Exporting Data From ASP.NET application to PDF 

In previous two articles we have seen how we can Export Data Into Excel Sheet and Export Data Into CSV File.

Here is the tag where you can find all the Export Data Articles.

Now we will see how we can export data into a PDF file in asp.net.

Download source code here.

Let's Get into the Code

Here is the method which will help exporting data into a PDF. 
protected void ExportToPDF(object sender, EventArgs e)
    {
        DataTable dt = GetData();
    
        //Create a dummy GridView
        GridView GridView1 = new GridView();
        GridView1.AllowPaging = false;
        GridView1.DataSource = dt;
        GridView1.DataBind();
    
        Response.ContentType = "application/pdf";
        Response.AddHeader("content-disposition", "attachment;filename=MyReport.pdf");
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.RenderControl(hw);
        StringReader sr = new StringReader(sw.ToString());
        Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
        HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
        PdfWriter.GetInstance(pdfDoc, Response.OutputStream);
        pdfDoc.Open();
        htmlparser.Parse(sr);
        pdfDoc.Close();
        Response.Write(pdfDoc);
        Response.End();
    }
I guess this must be very clear to everyone what I have written inside. Anyway let's discuss it here.

  1. First, I get the data source from the method GetData(), which is nothing but just a method that returns a datatable with some data inside it. You can have your own dataset here from DB or wherever you want.
  2. Second, I have taken a grid view so that I can render that data in it. If you have a grid view at your page level then no need to take one and bind it here, even you need not to get the data again if you have already that data rendered in any gridview or what in your page.
  3. Third, Add Response headers, content types for Excel download. Remember the Content Type is very important, this is code which decides the file type you are downloading. So here for Excel sheet download the content Type is "application/pdf". Also give name to the file you are downloading.
  4. Fourth, I have used iTextSharp for creating PDF file.
  5. Fifth, Now write the string writer value into response object and you are all set to download the excel sheet.
If you are looking for the GetData() method to test out the feasibility of this process of exporting data into excel sheet in ASP.NET and C# then here is it.
     private DataTable GetData()
     {
         // Here we create a DataTable with four columns.
         DataTable dtSample = new DataTable();
         dtSample.Columns.Add("Dosage", typeof(int));
         dtSample.Columns.Add("Drug", typeof(string));
         dtSample.Columns.Add("Patient", typeof(string));
         dtSample.Columns.Add("Date", typeof(DateTime));

         // Here we add five DataRows.
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         
         return dtSample;

     }
Hope this article helps you, as this method helped me a lot to overcome the unnecessary usage of external dll for exporting data into excel sheets.

Demo:

Export Data From ASP.NET



Publisher: Tapan kumar - 12:59 AM
, ,

Exporting Data From ASP.NET Application To CSV File

Exporting Data From ASP.NET application to CSV File

In the Previous Article we saw how to export data to an excel sheet through ASP.NET application without using any external DLLs.

Here is the tag where you can find all the Export Data Articles.

In this article we will see how to export the same data to CSV file using the same technique and not using any external libraries.

Here I will walk you through the code for exporting data in Excel sheet in ASP.NET.

Download source code here.

Let's Get into the Code

Everything is same as exporting data into an excel sheet.

Below is the code you can have a look and then I will discuss every bit of it for your understanding.
  protected void ExportToCSV(object sender, EventArgs e)
  {
      DataTable dt = GetData();

      Response.Clear();
      Response.Buffer = true;
      Response.AddHeader("content-disposition", "attachment;filename=MyReport.csv");
      Response.Charset = "";
      Response.ContentType = "application/text";


      StringBuilder sb = new StringBuilder();
      for (int k = 0; k < dt.Columns.Count; k++)
      {
          //add separator
          sb.Append(dt.Columns[k].ColumnName + ',');
      }
      //append new line
      sb.Append("\r\n");
      for (int i = 0; i < dt.Rows.Count; i++)
      {
          for (int k = 0; k < dt.Columns.Count; k++)
          {
              //add separator
              sb.Append(dt.Rows[i][k].ToString().Replace(",", ";") + ',');
          }
          //append new line
          sb.Append("\r\n");
      }
      Response.Output.Write(sb.ToString());
      Response.Flush();
      Response.End();
  }
I guess this must be very clear to everyone what I have written inside. Anyway let's discuss it here.

  1. First, I get the data source from the method GetData(), which is nothing but just a method that returns a datatable with some data inside it. You can have your own dataset here from DB or wherever you want.
  2. Second, I have taken a grid view so that I can render that data in it. If you have a grid view at your page level then no need to take one and bind it here, even you need not to get the data again if you have already that data rendered in any gridview or what in your page.
  3. Third, Add Response headers, content types for Excel download. Remember the Content Type is very important, this is code which decides the file type you are downloading. So here for CSV file download the content Type is "attachment;filename=MyReport.csv". Also give name to the file you are downloading.
  4. Fourth, Get a string builder and run a loop to create a comma delimited string.
  5. Fifth, Now write the string builder value into response object and you are all set to download the excel sheet.
If you are looking for the GetData() method to test out the feasibility of this process of exporting data into excel sheet in ASP.NET and C# then here is it.
     private DataTable GetData()
     {
         // Here we create a DataTable with four columns.
         DataTable dtSample = new DataTable();
         dtSample.Columns.Add("Dosage", typeof(int));
         dtSample.Columns.Add("Drug", typeof(string));
         dtSample.Columns.Add("Patient", typeof(string));
         dtSample.Columns.Add("Date", typeof(DateTime));

         // Here we add five DataRows.
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         
         return dtSample;

     }
Hope this article helps you, as this method helped me a lot to overcome the unnecessary usage of external dll for exporting data into excel sheets.

Demo:

Export Data From ASP.NET



Publisher: Tapan kumar - 12:50 AM

Saturday, July 30, 2016

, , ,

Exporting Data To Excel Sheet in ASP.NET (C#) Without using External DLLs(Libraries)

Exporting Data From ASP.NET application to Excel Sheet

Exporting data is an integral part of any application we develop irrespective of the technology. We need to export data while generating various reports to take care of the activities going on. For an example; lets say you need to send out the annual sales report to your CA/CS for Tax filing, in that case what you have to do if you are maintaining all the things in a web application ? Definitely we need to export all the data for the specified time period to some specified file formats may it be excel, pdf or csv.

In this case if you are a developer you must have tried many things using some external libraries, methods. I too tried any methods. But each time I did not feel good, as I don't like using external libraries.

After doing a series of experiments and searching a lot in Google, finally got some ideas that really works awesome for exporting data to the required file formats as expected.

Here I will walk you through the code for exporting data in Excel sheet in ASP.NET.

Here is the tag where you can find all the Export Data Articles.

Download source code here.

Let's Get into the Code

In order to export data into excel sheet, first we need some data. In your case you can get it from DB or anywhere you want. You can have it in DataTable format or any custom List format or anything you want.

Here in my case I am using a method to create a dummy DataTable and feeds some data into it. I will use this DataTable as my data source, which needs to get exported into excel sheet.

Below is the code you can have a look and then I will discuss every bit of it for your understanding.
    private void ExportToExcelSheet()
    {
        //Get the data we want to export.
        DataTable dt = GetData();

        //Create a dummy GridView and Bind the data source we have.
        GridView grdExportData = new GridView();

        grdExportData.AllowPaging = false;
        grdExportData.DataSource = dt;
        grdExportData.DataBind();

        //Clear the response and add the content types and headers to it.
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename=MyReport.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";

        //We need this string writer and HTML writer in order to render the grid inside it.
        StringWriter swExportData = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(swExportData);

        //Lets render the Grid inside the HtmlWriter and then automatically we will have it converted into eauivalent string.
        grdExportData.RenderControl(hw);

        //Write the response now and you will get your excel sheet as download file
        Response.Output.Write(swExportData.ToString());
        Response.Flush();
        Response.End();
    }
I guess this must be very clear to everyone what I have written inside. Anyway let's discuss it here.

  1. First, I get the data source from the method GetData(), which is nothing but just a method that returns a datatable with some data inside it. You can have your own dataset here from DB or wherever you want.
  2. Second, I have taken a grid view so that I can render that data in it. If you have a grid view at your page level then no need to take one and bind it here, even you need not to get the data again if you have already that data rendered in any gridview or what in your page.
  3. Third, Add Response headers, content types for Excel download. Remember the Content Type is very important, this is code which decides the file type you are downloading. So here for Excel sheet download the content Type is "application/vnd.ms-excel". Also give name to the file you are downloading.
  4. Fourth, Get a string writer and Html writer and render the grid view inside it. Now the string writer will have the exact html of  the grid and we are all set to throw it into an Excel sheet.
  5. Fifth, Now write the string writer value into response object and you are all set to download the excel sheet.
If you are looking for the GetData() method to test out the feasibility of this process of exporting data into excel sheet in ASP.NET and C# then here is it.
     private DataTable GetData()
     {
         // Here we create a DataTable with four columns.
         DataTable dtSample = new DataTable();
         dtSample.Columns.Add("Dosage", typeof(int));
         dtSample.Columns.Add("Drug", typeof(string));
         dtSample.Columns.Add("Patient", typeof(string));
         dtSample.Columns.Add("Date", typeof(DateTime));

         // Here we add five DataRows.
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         
         return dtSample;

     }
Hope this article helps you, as this method helped me a lot to overcome the unnecessary usage of external dll for exporting data into excel sheets.

Demo:

Export Data From ASP.NET



Publisher: Tapan kumar - 1:26 AM

Saturday, January 9, 2016

, , ,

Import Data From Excel Sheet With Column Mapping Modal in ASP.NET (C#)

Problem with Conventional Excel Import

If you search in the internet for Excel import you will get thousands of articles with numerous ways to do it. And I can say its very simple to do it. But there is a thing you need to think about.

In the conventional way for importing the excel sheet you specify a format and put dump your data there then import. Correct me if I am wrong in the comments section.

Just think what will happen if somebody has changed the formatted excel sheet's column header or the ordering of the columns ? The whole process will be dead. As we have written the code to import data by considering neither the column header's will change nor their orderings.

How Column Mapping Modal Will Solve this Issue?

After facing this type of issue I thought of a way to make it simple by using a mapping modal where user can map the Excel columns with the Database columns.

 Column Mapping Modal



This is a custom modal I have made where I have listed all the column names of my database table and a dropdown aside, which holds all the column names in it. And now I have the full flexibility to map the excel sheet columns to my database table columns and there is no problem with the column names or formatting or the orderings too.

If you are looking for the code then here you go. First lets make the Column Mapping Modal. Below is the code block for creating the above mapping modal.
<div class="modal" id="divSelectColumns" data-backdrop="static" data-keyboard="false" hidden="hidden">
    <div class="modal-dialog" style="width: 800px;">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                <h4 id="h1" runat="server" class="modal-title">Map xls Columns with DataBase Columns
                </h4>
            </div>
            <div class="modal-body">
                <div class="form-horizontal row">
                    <div class="col-sm-6">
                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="ltlFName" Text="Serial No"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl1" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="ltlHub" Text="Mobile No."></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl7" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="Literal10" Text="Customer IP"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl10" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>

                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="ltlRole" Text="Address"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl5" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="ltlEmail" Text="Area"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl3" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="Literal12" Text="Internet Plan"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl12" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>

                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="Literal14" Text="Previous Dues"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl14" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="Literal16" Text="Other Charges"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl16" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="Literal18" Text="Total Dues"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl18" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>
                    v>
                     class="col-sm-6">
                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="ltlPhone" Text="Customer Name"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl4" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="Literal11" Text="Antenna IP"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl11" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="Literal17" Text="Status"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl17" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="ltlPosition" Text="Address 2"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl6" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="ltlLName" Text="Area Leader"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl2" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>

                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="ltlTeam" Text="Mobile No. 2"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl8" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="Literal9" Text="Mobile No. 3"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl9" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>


                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="Literal13" Text="Plan Rental"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl13" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>

                    <div class="form-group">
                        <label class="col-sm-4 control-label">
                            <asp:Literal runat="server" ID="Literal15" Text="Current Rental"></asp:Literal>
                        </label>
                        <div class="col-sm-7">
                            <asp:DropDownList runat="server" ID="ddl15" CssClass="form-control" AppendDataBoundItems="true"></asp:DropDownList>
                        </div>
                    </div>

                    </div>
                </div>
            </div>
            <div class="modal-footer">
                <div class="pull-right">
                    <asp:LinkButton ID="lbtnAddEdit" runat="server" CssClass="btn btn-primary" OnClick="lbtnAddEdit_Click">
                        <span class="glyphicon glyphicon-ok"></span>&nbsp
                        <b>
                            <asp:Literal runat="server" ID="Literal7" Text="Import Customer"></asp:Literal></b>
                    </asp:LinkButton>
                    <span class="btn btn-warning" data-dismiss="modal">
                        <span class="glyphicon glyphicon-remove"></span>&nbsp
                       <b>
                           <asp:Literal runat="server" ID="Literal8" Text="Cancel"></asp:Literal></b>
                    </span>
                </div>
            </div>
        </div>
        <!-- /.modal-content -->
    </div>
    <!-- /.modal-dialog -->
</div>
I have made this according to my Database table, you can change it according to you. Now we will see the code behind part. Now add a file uploader control and a button to select the excel sheet.
<div class="col-xs-12 col-md-3">
    <asp:FileUpload ID="fuCustomerRecords" CssClass="form-control"
        runat="server" />
</div>
<div class="col-xs-12 col-md-3">
    <asp:LinkButton ID="lnkUpload" runat="server" CssClass="btn btn-primary"
        OnClick="lnkUpload_Click">
    <span class="glyphicon glyphicon-upload"></span><b>
        Import Customers</b>
    </asp:LinkButton>
</div>
Before moving to the code behind first of all you have to add a reference to Excel 4.5 library in your project. Click here to download it.

Add the namespace Excel to your code behind file. Write down the below code on the click even of the Import button. Before writing this block of code create folder named "ExcelFile" in your project's root folder as the imported excel sheet will be saved there.
protected void lnkUpload_Click(object sender, EventArgs e)
  {
      if (fuCustomerRecords.HasFile)
      {
          string ExcelData = "~/ExcelFile/" + fuCustomerRecords.FileName;
          fuCustomerRecords.PostedFile.SaveAs(Server.MapPath(ExcelData));
          string extension = Path.GetExtension(Server.MapPath(ExcelData));
          FileStream streamOpen = File.Open(Server.MapPath(ExcelData), FileMode.Open, FileAccess.Read);
          IExcelDataReader excelReader = null;
          switch (extension)
          {
              case ".xls":
                  //Excel 97-03
                  excelReader = ExcelReaderFactory.CreateBinaryReader(streamOpen);
                  break;
              case ".xlsx":
                  //Excel 07
                  excelReader = ExcelReaderFactory.CreateOpenXmlReader(streamOpen);
                  break;
          }
          excelReader.IsFirstRowAsColumnNames = true;
          DataSet result = excelReader.AsDataSet();
          DataTable dtResult = result.Tables[0];

          GetColumnNamesFromTheExcelSheet(dtResult);             
      }
  }


  private void GetColumnNamesFromTheExcelSheet(DataTable dtCustomers)
  {
      string[] columnNames = dtCustomers.Columns.Cast<DataColumn>()
                           .Select(x => x.ColumnName)
                           .ToArray();

      List<string> lstColumnNames = columnNames.OfType<string>().ToList();

      BindMapingColumnsDropDown(ddl1, lstColumnNames, "Serial Number");
      BindMapingColumnsDropDown(ddl2, lstColumnNames, "Area Leader");
      BindMapingColumnsDropDown(ddl3, lstColumnNames, "Area");
      BindMapingColumnsDropDown(ddl4, lstColumnNames, "Customer Name");
      BindMapingColumnsDropDown(ddl5, lstColumnNames, "Address");
      BindMapingColumnsDropDown(ddl6, lstColumnNames, "Address 2");
      BindMapingColumnsDropDown(ddl7, lstColumnNames, "Mobile");
      BindMapingColumnsDropDown(ddl8, lstColumnNames, "Mobile 2");
      BindMapingColumnsDropDown(ddl9, lstColumnNames, "Mobile 3");
      BindMapingColumnsDropDown(ddl10, lstColumnNames, "Customer Ip");
      BindMapingColumnsDropDown(ddl11, lstColumnNames, "Antenna Ip");
      BindMapingColumnsDropDown(ddl12, lstColumnNames, "Internet Plan");
      BindMapingColumnsDropDown(ddl13, lstColumnNames, "Plan Rental");
      BindMapingColumnsDropDown(ddl14, lstColumnNames, "Previous Dues");
      BindMapingColumnsDropDown(ddl15, lstColumnNames, "Current Rental");
      BindMapingColumnsDropDown(ddl16, lstColumnNames, "Other Charges");
      BindMapingColumnsDropDown(ddl17, lstColumnNames, "Status");
      BindMapingColumnsDropDown(ddl18, lstColumnNames, "Total Dues");

      ClientScript.RegisterStartupScript(GetType(), "Load", String.Format("<script type='text/javascript'>ShowColumnMappingModal();</script>"));

      ViewState["dtCustomers"] = dtCustomers;
  }
  
  public void BindMapingColumnsDropDown(DropDownList dropDownList, object dataSource, string selectedValue = "-Select-")
  {
      dropDownList.Items.Clear();
      dropDownList.Items.Add(new ListItem("-Select-"));
      dropDownList.DataSource = dataSource;
      dropDownList.AppendDataBoundItems = true;
      dropDownList.DataBind();

      try
      {
          dropDownList.SelectedValue = selectedValue;
      }
      catch (Exception ex)
      {
          dropDownList.SelectedValue = "-Select-";
      }          
  }


The above two method will save the excel sheet to a physical location on your system/server and then the IExcelDataReader object will extract a DataTable from that saved excel sheet.

GetColumnNamesFromTheExcelSheet, this method will get all the columns names from the saved DataTable and bind those to the dropdowns we have taken. Look into the BindMappingColumnsDropdown you can see that I am setting the passed column value as selected value of the dropdown. If someone changes the column name in the excel sheet then also we do not have any problem because we will set the selected value to "-Select-" as I am doing this in catch block.

Now we are all set to import the mapped columns to database. So here we go to click on the Import button of the modal to execute the below code block.
protected void lbtnAddEdit_Click(object sender, EventArgs e)
    {
        DataTable dtCustomers = (DataTable)ViewState["dtCustomers"];
        Customers objCustomer = new Customers();
    
        int count = 0;
    
        try
        {
            if (dtCustomers != null && dtCustomers.Rows.Count > 0)
            {
                dtCustomers.Columns.Add("-Select-");
    
                foreach (DataRow row in dtCustomers.Rows)
                {
                    row["-Select-"] = string.Empty;
    
                    objCustomer.SerialNo = row[ddl1.SelectedValue].ToString();
                    objCustomer.Mobile = row[ddl7.SelectedValue].ToString();
                    objCustomer.CustomerIp = row[ddl10.SelectedValue].ToString();
                    objCustomer.Address = row[ddl5.SelectedValue].ToString();
                    objCustomer.Area = row[ddl3.SelectedValue].ToString();
                    objCustomer.InternetPlan = row[ddl12.SelectedValue].ToString();
                    objCustomer.PreviousDues = row[ddl14.SelectedValue].ToString();
                    objCustomer.OtherCharges = row[ddl16.SelectedValue].ToString();
                    objCustomer.TotalDues = row[ddl18.SelectedValue].ToString();
                    objCustomer.Name = row[ddl4.SelectedValue].ToString();
                    objCustomer.AntennaIp = row[ddl11.SelectedValue].ToString();
                    objCustomer.Status = row[ddl17.SelectedValue].ToString();
                    objCustomer.Address2 = row[ddl6.SelectedValue].ToString();
                    objCustomer.AreaLeader = row[ddl2.SelectedValue].ToString();
                    objCustomer.Mobile2 = row[ddl8.SelectedValue].ToString();
                    objCustomer.Mobile3 = row[ddl9.SelectedValue].ToString();
                    objCustomer.PlanRental = row[ddl13.SelectedValue].ToString();
                    objCustomer.CurrentRental = row[ddl15.SelectedValue].ToString();
    
                    objCustomerBusinessLayer.AddEditCustomerDetails(objCustomer);
    
                    count++;
                }
            }
        }
        catch (Exception ex)
        {
        }
    
        if (count > 0)
        {
            divAlert.Visible = true;
            divAlert.Attributes.Add("class", "alert alert-success alert-dismissible");
            lblAlert.Text = count.ToString() + " Customer Data Imported successfully.";
        }
        else
        {
            divAlert.Visible = true;
            divAlert.Attributes.Add("class", "alert alert-danger alert-dismissible");
            lblAlert.Text = "Some Error Ocured.";
        }
    }
Here I am looping through the datatable I have stored in viewstate and calling my Data Layer method to perform the Insert/Update operation.

I hope this article will help you a lot to customize the way you import excell sheet data into Sql database in C# or ASP.NET.

Happy Codding...
Publisher: Tapan kumar - 4:40 AM

Monday, June 29, 2015

, ,

Fixes For the issue "The relative virtual path 'Site.Mobile.Master' is not allowed here" of FriendlyUrls in ASP.NET

Why this Site.Mobile.Master issue appear ?

If you are using the FriendlyURLs of ASP.NET in order to remove the extensions from your web pages then you might have faced this problem while browsing you site in mobile devices.

Its actually a bug in the current version of Web Forms friendly URLs (1.0.2) which tries to find the site.mobile.master whenever you try to open up your website in a mobile device and if you have not that master page in your solution then it will end up by giving you this shit error "The relative virtual path 'Site.Mobile.Master' is not allowed here".

How To Fix this Site.Mobile.Master Issue

To fix this issue you need to override the method TrySetMobileMasterPage() of  Microsoft.AspNet.FriendlyUrls.

TrySetMobileMasterPage() this method is the culprit here for the issue which was searching for the site.mobile.master page in the solutions and if you do not have it then it ends up giving you the error.



Hence our goal is to override this method. Now create a class named SiteMobileMasterFriendlyUrlResolver  and inside it we will override the method TrySetMobileMasterPage().

Here is the code for the newly created resolver class.
/// <summary>
/// Override the method TrySetMobileMasterPage by returning false while it tries to find the site.mobile.master page in the solution
/// </summary>
public class SiteMobileMasterFriendlyUrlResolver : Microsoft.AspNet.FriendlyUrls.Resolvers.WebFormsFriendlyUrlResolver
{
    protected override bool TrySetMobileMasterPage(HttpContextBase httpContext, Page page, string mobileSuffix)
    {
        return false;
        //return base.TrySetMobileMasterPage(httpContext, page, mobileSuffix);
    }
}
Now in the RouteConfig class change the routes.EnableFriendlyUrls(settings) method, like below.
public static class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            var settings = new FriendlyUrlSettings();
            settings.AutoRedirectMode = RedirectMode.Permanent;
            routes.EnableFriendlyUrls(settings, new SiteMobileMasterFriendlyUrlResolver());
        }
    }
}


Now you can run your application in any mobile device and you will not see this error again. as we have overridden the code.

Happy Coding...

Publisher: Tapan kumar - 2:50 AM

Sunday, April 12, 2015

How to write a simple html programming for beginners.

Following steps are required for writing a html code...

  • First we need a notepad.
  • Open a notepad. then write the following code.
  • Save it as .html.
  • ex: programme.html
  • Then open it through any browser.
<html>
    <head>
            <title>
                    HERE WE SHOULD WRITE THE TITLE THE PROGRAM
            </title>
            HERE WE SHOULD WRITE THE HEAD PART OF THE PROGRAM 
    </head>
    <body>
            welcome to the html code
            <br>
            bye bye from html code
    </body>
</html>

There are two types of tags used in a html program

  • Paired Tags 
  • Unpaired Tags

Paired Tags:

A tag is said to be a paired tag if the text is placed between a tag and its companion tag. In paired tags, the first tag is referred to as Opening Tag and the second tag is referred to as Closing Tag.

example: <html></html>

Unpaired Tags:

An unpaired tag does not have a companion tag. Unpaired tags are also known as Singular or Stand-Alone Tags.

example:<br>.

From the Program.

Here <head></head> tag used for heading to your  programme.
< title></title> tag is for title to your programme which will show in the title bar of the browser.
<br> tag use for break line.
<body></body> tag used for show the body part of html programme.

Thanks...

Publisher: bidya sagar mishra - 3:49 AM

Wednesday, March 25, 2015

, ,

How To Get The Logged In User Count In ASP.NET

Why Do You Need The Current Logged In User Count

Sometimes it's a client requirement to show the current logged in user count in the web application. This feature is frequently used in forums to show how many users are currently browsing the site. This is just a feature to keep the users updated about the current visitors to the forum.

How To Get The Logged In User Count Without SQL In C#

There are few ways you can keep track of it, First one is using the Database where you have to update it according to every user log in and log out and session out. But I will not prefer it to do a database update for a simple task that can be done easily.

I was stuck into this problem and after a few Google search I got the solution that gives me the current logged in user count from active sessions without using any database operation.

For this you need to refer the following namespaces.
using System.Reflection;
using System.Threading.Tasks;
using System.Web.SessionState;
After adding these namespaces add the below method which will retrive the current logged in user count from session, this method uses reflection.
public int CalculateLoggedinUser()
   {
       int loggedinUserCount = 0;

       object obj = typeof(HttpRuntime).GetProperty("CacheInternal", BindingFlags.NonPublic | BindingFlags.Static).GetValue(null, null);

       object[] obj2 = (object[])obj.GetType().GetField("_caches", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(obj);

       for (int i = 0; i < obj2.Length; i++)
       {
           Hashtable c2 = (Hashtable)obj2[i].GetType().GetField("_entries", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(obj2[i]);

           foreach (DictionaryEntry entry in c2)
           {
               object o1 = entry.Value.GetType().GetProperty("Value", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(entry.Value, null);

               if (o1.GetType().ToString() == "System.Web.SessionState.InProcSessionState")
               {
                   SessionStateItemCollection sess = (SessionStateItemCollection)o1.GetType().GetField("_sessionItems", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(o1);

                   if (sess != null)
                   {
                       if (sess["User"] != null)
                       {
                           loggedinUserCount++;

                       }
                   }
               }
           }
       }
       return loggedinUserCount;
   }
But always there is a catch in Microsoft products. This method will work perfectly in your local machine but when you host your site in any windows server then this will crash.

In Windows Server you can not read the property "_cache" from "CacheInternal" object so I have modified the code for local and server.
First you have to check if the code is being executed in server or local with the below line of codes

  if(IsLocalIpAddress(HttpContext.Current.Request.UserHostAddress))
       loggedinUserCount = CalculateLoggedinUserForLocal();
  else
       loggedinUserCount = CalculateLoggedinUserForServer();
               
Then this method will actually say you if it is the server or local

public bool IsLocalIpAddress(string host)
    {
        try
        { // get host IP addresses
            IPAddress[] hostIPs = Dns.GetHostAddresses(host);
            // get local IP addresses
            IPAddress[] localIPs = Dns.GetHostAddresses(Dns.GetHostName());

            // test if any host IP equals to any local IP or to localhost
            foreach (IPAddress hostIP in hostIPs)
            {
                // is localhost
                if (IPAddress.IsLoopback(hostIP)) return true;
                // is local address
                foreach (IPAddress localIP in localIPs)
                {
                    if (hostIP.Equals(localIP)) return true;
                }
            }
        }
        catch { 
        }
        return false;
    }
If its local then "CalculateLoggedinUserForLocal()" method will be executed

public int CalculateLoggedinUserForLocal()
    {
        int loggedinUserCount = 0;

        object obj = typeof(HttpRuntime).GetProperty("CacheInternal", BindingFlags.NonPublic | BindingFlags.Static).GetValue(null, null);

        object[] obj2 = (object[])obj.GetType().GetField("_caches", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(obj);

        for (int i = 0; i < obj2.Length; i++)
        {
            Hashtable c2 = (Hashtable)obj2[i].GetType().GetField("_entries", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(obj2[i]);

            foreach (DictionaryEntry entry in c2)
            {
                object o1 = entry.Value.GetType().GetProperty("Value", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(entry.Value, null);

                if (o1.GetType().ToString() == "System.Web.SessionState.InProcSessionState")
                {
                    SessionStateItemCollection sess = (SessionStateItemCollection)o1.GetType().GetField("_sessionItems", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(o1);

                    if (sess != null)
                    {
                        if (sess["User"] != null)
                        {
                            loggedinUserCount++;

                        }
                    }
                }
            }
        }
        return loggedinUserCount;
    }
Else if it is server then method will be executed

public int CalculateLoggedinUserForServer()
   {
       int loggedinUserCount = 0;

       object obj = typeof(HttpRuntime).GetProperty("CacheInternal", BindingFlags.NonPublic | BindingFlags.Static).GetValue(null, null);

       Hashtable c2 = (Hashtable)obj.GetType().GetField("_entries", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(obj);

       foreach (DictionaryEntry entry in c2)
       {
           object o1 = entry.Value.GetType().GetProperty("Value", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(entry.Value, null);

           if (o1.GetType().ToString() == "System.Web.SessionState.InProcSessionState")
           {
               SessionStateItemCollection sess = (SessionStateItemCollection)o1.GetType().GetField("_sessionItems", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(o1);

               if (sess != null)
               {
                   if (sess["User"] != null)
                   {
                       loggedinUserCount++;
                   }
               }
           }
       }
       return loggedinUserCount;
   }



Now you got the current logged in user by writing just 2-3 methods of not more than 20 lines of code.

Happy Coding...
Publisher: Tapan kumar - 12:39 PM

Powered by Cybria Technology