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.

After getting a lot of query from many people I was forced to give the downloadable link here. You can download the source file here from Google drive.

Happy Codding...

9 comments:

  1. This is awesome. May I request permission to use this for educational purposes? However, I am encountering an error. It would be grateful if you can help me. Compiler Error Message: CS0246: The type or namespace name 'DataTable' could not be found (are you missing a using directive or an assembly reference?)

    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.
    https://docs.google.com/uc?id=0B2Or112gItF9dVlWSy16dWhuSDg&export=download
    403. That’s an error.
    We're sorry, but you do not have access to this page. That’s all we know.

    ReplyDelete
    Replies
    1. You can resolve that issue easily, by adding the required reference. I guess in your case is System.Data.

      Delete
    2. Hi here is the source code you can download that from here.

      Check the bottom of the blog to download it from google drive

      https://drive.google.com/file/d/0B2Or112gItF9eVJhUUVoSDRlVHM/view?usp=sharing

      Delete
  2. Could you please send me the source code for this, i would really appreciate it. [email protected]

    ReplyDelete
    Replies
    1. Hi here is the source code you can download that from here.

      Check the bottom of the blog to download it from google drive

      https://drive.google.com/file/d/0B2Or112gItF9eVJhUUVoSDRlVHM/view?usp=sharing

      Delete
  3. Good day, i'm looking for the source code for this, I've been trying to add all the snippets, but i keep getting a modal error

    ReplyDelete
    Replies
    1. Hi here is the source code you can download that from here.

      Check the bottom of the blog to download it from google drive

      https://drive.google.com/file/d/0B2Or112gItF9eVJhUUVoSDRlVHM/view?usp=sharing

      Delete
  4. How i can found the download link?
    regards

    ReplyDelete
    Replies
    1. Hi here is the source code you can download that from here.

      Check the bottom of the blog to download it from google drive

      https://drive.google.com/file/d/0B2Or112gItF9eVJhUUVoSDRlVHM/view?usp=sharing

      Delete