Click here to Skip to main content
15,887,477 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
below code is work but if i want to browse excel sheet with 1000000 record, what changes need ???

What I have tried:

i try this code
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();
   
    }

    protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
  {
    string FolderPath = ConfigurationManager.AppSettings["FolderPath"] ;
    string FileName = GridView1.Caption;
    string Extension = Path.GetExtension(FileName);
    string FilePath = Server.MapPath(FolderPath + FileName);
 
    Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text); 
    GridView1.PageIndex = e.NewPageIndex;
    GridView1.DataBind(); 
  }
Posted
Updated 7-Jul-17 23:13pm

You can use virtual scrolling, see answer here: virtual scrolling in asp.net gridview[^]
 
Share this answer
 
Don't use Excel 2003 as it is limited to 65k rows.
Excel 2007 is limited to 1M rows.
Excel – a history of rows and columns - Office Watch[^]
 
Share this answer
 
Comments
[no name] 8-Jul-17 5:28am    
Really not an answer, maybe a comment. What this has to do with the question?
BTW: No vote from my side, only in case :)
Patrice T 8-Jul-17 6:12am    
The OP want to store 1M rows in an excel file.
Since Excel 2003 file format can only handle 65K rows, The OP must remove the excel 2003 file format. This is the change I see since the OP did not stated another problem.
No vote is fine to me.
[no name] 8-Jul-17 6:30am    
Sorry, I see it now. My apologies and a 5.
Patrice T 8-Jul-17 7:11am    
Thank you

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900