question

AshokkumarS-5421 avatar image
0 Votes"
AshokkumarS-5421 asked LanHuang-MSFT answered

Retrieve the data from excel row by row randomly or sequentially and need to display in ASP.net c#

I want to Retrieve the data from excel row by row randomly or sequentially and need to display it in ASP.net c# web page

I tried and done to display gridview data but not individual data. Kindly help me

dotnet-csharpdotnet-aspnet-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

LanHuang-MSFT avatar image
0 Votes"
LanHuang-MSFT answered

Hi @AshokkumarS-5421,
https://docs.microsoft.com/en-us/answers/questions/954594/retrieve-the-data-from-oracle-database-row-by-row.html
I see you asked two questions, do you want to achieve retrieving data from excel or oracle database?

I tried and done to display gridview data but not individual data.

individual data? What does individual data refer to, you don't want to do it through gridview? Can you describe in more detail?
I wrote an example, hope it helps you.

 <asp:DropDownList ID="dropdown1" runat="server" OnSelectedIndexChanged="ddlSlno_SelectedIndexChanged"   AutoPostBack="true" AppendDataBoundItems="True">  
                <asp:ListItem Selected="True" Value="Choose">- Choose -</asp:ListItem>  
            </asp:DropDownList>  
            <asp:GridView ID="Grid1" runat="server">  
            </asp:GridView>  
            <asp:Label ID="lbl1" runat="server" /> 


   OleDbConnection x;
         protected void Page_Load(object sender, EventArgs e)
         {
             if (!IsPostBack)
             {
                 GenerateExcelData("Choose");
             }
         }
         protected void ddlSlno_SelectedIndexChanged(object sender, EventArgs e)
         {
             GenerateExcelData(dropdown1.SelectedValue);
         }
    
         private void GenerateExcelData(string SlnoAbbreviation)
         {
             try
             {
                 string read = Path.GetFullPath(Server.MapPath("~/empdetail.xlsx"));
    
                 if (Path.GetExtension(read) == ".xls")
                 {
                     x = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + read + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
                 }
                 else if (Path.GetExtension(read) == ".xlsx")
                 {
                     x = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + read + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
                 }
                 x.Open();
                 OleDbCommand y = new OleDbCommand();
                 OleDbDataAdapter z = new OleDbDataAdapter();
                 DataSet dset = new DataSet();
                 y.Connection = x;
                 y.CommandType = CommandType.Text;
                 y.CommandText = " SEL ECT distinct([Slno]) FROM [Sheet1$]";
                 z = new OleDbDataAdapter(y);
                 z.Fill(dset, "Slno");
                dropdown1.DataSource = dset.Tables["Slno"].DefaultView;
                if (!IsPostBack)
                {
                 dropdown1.DataTextField = "Slno";
                 dropdown1.DataValueField = "Slno";
                 dropdown1.DataBind();
                }
               if (!String.IsNullOrEmpty(SlnoAbbreviation) && SlnoAbbreviation != "Choose")
                {
                    y.CommandText = " S E L E C T *  FROM [ She et1 $ ] w he re Slno= @ Slno_Abbreviation";
                    y.Parameters.AddWithValue("@Slno_Abbreviation", SlnoAbbreviation);
                }
             else
              {
                 y.CommandText = "SEL ECT * FROM [ Sheet1 $ ]";
              }
             z = new OleDbDataAdapter(y);
             z.Fill(dset);
               Grid1.DataSource = dset.Tables[1].DefaultView;
               Grid1.DataBind();
              
             }
             catch (Exception ex)
             {
                 lbl1.Text = ex.ToString();
             }
             finally
             {
                 x.Close();
             }
         }

228040-test1.gif
Best regards,
Lan Huang


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



test1.gif (91.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.