locked
Show Excel Data In Asp.net Repeater Label1, Label2 On Button Click RRS feed

  • Question

  • User-807418713 posted

    Hello

    I Have Excel In Client System D:\CustomerShipingDetails.xls File and Data Will Be  Like This Below

    Customer Code Shipping Address Billing Address
         
    C1 No 4 LK ROAD No 5 ZR ROAD
    Z12 No 67 FD ROAD No OU ROAD
    K6 No 77 ER ROAD No 55 HT ROAD

    In That I Have 3 Columns Customer Code, Shipping Address, Billing Address

    Now On Asp.net Page I Have One Textbox In That I Will Type For Example: Z12 and Then on button click it has to show in Repeater control label1 = shipping address details and label2 billing address details

    Thanking You

    Sunday, December 20, 2020 7:21 PM

Answers

  • User1535942433 posted

    Hi Gopi.MCA,

    Accroding to your description,if you need users upload the excel to bind the gridview, you could use fileupload. Just like this:

    Web.config:

    <connectionStrings>
     <add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
     <add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1}'"/>
    </connectionStrings >

    HTML page:

    asp:FileUpload ID="FileUpload1" runat="server" />
    <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
    <br />
    <asp:Label ID="Label1" runat="server" Text="Has Header ?"></asp:Label><br />
    <asp:RadioButtonList ID="rbHDR" runat="server" RepeatLayout = "Flow">
        <asp:ListItem Text="Yes" Value="Yes" Selected="True"></asp:ListItem>
        <asp:ListItem Text="No" Value="No"></asp:ListItem>
    </asp:RadioButtonList>
    <asp:GridView ID="GridView1" runat="server" OnPageIndexChanging="PageIndexChanging"
        AllowPaging="true">
    </asp:GridView>

    Code-behind:

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
     
            string FilePath = Server.MapPath(FolderPath + FileName);
            FileUpload1.SaveAs(FilePath);
            Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
        }
    }
    private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
        string conStr = "";
        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        conStr = String.Format(conStr, FilePath, isHDR);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        cmdExcel.Connection = connExcel;
     
        //Get the name of First Sheet
        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();
     
        //Read Data from First Sheet
        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();
     
        //Bind Data to GridView
        GridView1.Caption = Path.GetFileName(FilePath);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }

    More details,you could refer to below article:

    https://www.aspsnippets.com/Articles/Import-data-from-Excel-to-GridView-in-ASPNet.aspx

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 23, 2020 6:30 AM

All replies

  • User1535942433 posted

    Hi Gopi.MCA,

    I Have Excel In Client System D:\CustomerShipingDetails.xls File and Data Will Be  Like This Below

    Customer Code Shipping Address Billing Address
         
    C1 No 4 LK ROAD No 5 ZR ROAD
    Z12 No 67 FD ROAD No OU ROAD
    K6 No 77 ER ROAD No 55 HT ROAD

    In That I Have 3 Columns Customer Code, Shipping Address, Billing Address

    Now On Asp.net Page I Have One Textbox In That I Will Type For Example: Z12 and Then on button click it has to show in Repeater control label1 = shipping address details and label2 billing address details

    Thanking You

    Accroding to your description,I don't understand your requirment clearly. Do you  have a excel in your localhost, and then you need to post to the server? If you need to do this,I think it's impossible.

    Could you tell me more details of the operations of showing excel data in repeater?It will help us to solve your problems.

    Best regards,

    Yijing Sun

    Monday, December 21, 2020 2:12 AM
  • User-807418713 posted

    Hello Sir

    This is my excel file with data

    Customer Code Shipping Address Billing Address
         
    C1 No 4 LK ROAD No 5 ZR ROAD
    Z12 No 67 FD ROAD No OU ROAD
    K6 No 77 ER ROAD No 55 HT ROAD

    Now when i enter in textbox1 z12 i want to show the data in gridview or repater.

    Monday, December 21, 2020 9:03 AM
  • User1535942433 posted

    Hi Gopi.MCA,

    I suggest you need to learn some basic knowledge of B/S and C/S and learn more difference of these.And then you need to make sure what's your structure.

    Best regards,

    Yijing Sun

    Tuesday, December 22, 2020 7:54 AM
  • User-807418713 posted

    Hello
    Good Day

    Is it possible to show excel data in gridview on button click..?

    Thanking You

    Tuesday, December 22, 2020 8:08 AM
  • User1535942433 posted

    Hi Gopi.MCA,

    Accroding to your description,if you need users upload the excel to bind the gridview, you could use fileupload. Just like this:

    Web.config:

    <connectionStrings>
     <add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
     <add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1}'"/>
    </connectionStrings >

    HTML page:

    asp:FileUpload ID="FileUpload1" runat="server" />
    <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
    <br />
    <asp:Label ID="Label1" runat="server" Text="Has Header ?"></asp:Label><br />
    <asp:RadioButtonList ID="rbHDR" runat="server" RepeatLayout = "Flow">
        <asp:ListItem Text="Yes" Value="Yes" Selected="True"></asp:ListItem>
        <asp:ListItem Text="No" Value="No"></asp:ListItem>
    </asp:RadioButtonList>
    <asp:GridView ID="GridView1" runat="server" OnPageIndexChanging="PageIndexChanging"
        AllowPaging="true">
    </asp:GridView>

    Code-behind:

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
     
            string FilePath = Server.MapPath(FolderPath + FileName);
            FileUpload1.SaveAs(FilePath);
            Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
        }
    }
    private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
        string conStr = "";
        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        conStr = String.Format(conStr, FilePath, isHDR);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        cmdExcel.Connection = connExcel;
     
        //Get the name of First Sheet
        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();
     
        //Read Data from First Sheet
        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();
     
        //Bind Data to GridView
        GridView1.Caption = Path.GetFileName(FilePath);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }

    More details,you could refer to below article:

    https://www.aspsnippets.com/Articles/Import-data-from-Excel-to-GridView-in-ASPNet.aspx

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 23, 2020 6:30 AM