我可以在ASP.NET中浏览带有大量记录的Excel工作表吗？ [英] Can I browse excel sheet with large amount of record in ASP.NET?

问题描述

下面的代码是可行的，但如果我想浏览带有1000000记录的excel表，需要进行哪些更改???



我尝试了什么：



i尝试此代码

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(); 
  }

推荐答案

您可以使用虚拟滚动，请参阅此处的答案： asp.net gridview中的虚拟滚动 [ ^ ]

You can use virtual scrolling, see answer here: virtual scrolling in asp.net gridview[^]

不要使用Excel 2003，因为它限制为65,000行。

Excel 2007限制为1M行。

Excel - 行和列的历史记录 - Office Watch [ ^ ]

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[^]

这篇关于我可以在ASP.NET中浏览带有大量记录的Excel工作表吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！