ASP.NET中读取Excel内容，并显示在界面上

来源：互联网发布：php 中文字符串截取编辑：程序博客网时间：2024/05/22 15:49

客户端上传本地excel文件到服务器上，并在客户端显示。

  // 上传按钮     protected void btnUp_Click(object sender, EventArgs e)   {       bool b = Upload(fuExcel);  // 上传excel文件        if (!b)       {           return;       }       string name = fuExcel.FileName;       string filepath = Server.MapPath("~/upload/") + name;       DataSet ds = ExcelDataSource(filepath, ExcelSheetName(filepath)[0].ToString());       GridView1.DataSource = ds;       GridView1.DataBind();   }   //上传文件方法     private bool Upload(FileUpload myFileUpload)   {       bool flag = false;       //是否允许上载         bool fileAllow = false;       //设定允许上载的扩展文件名类型         string[] allowExtensions = { ".xls" };       //取得网站根目录路径         string path = HttpContext.Current.Request.MapPath("~/upload/");      if (myFileUpload.HasFile)       { string fileExtension = System.IO.Path.GetExtension(myFileUpload.FileName).ToLower();        for (int i = 0; i < allowExtensions.Length; i++)           {               if (fileExtension == allowExtensions[i])               {                   fileAllow = true;               }           }           if (fileAllow)           {               try               {                   //存储文件到文件夹                    myFileUpload.SaveAs(path + myFileUpload.FileName);                   lblMes.Text = "文件导入成功";                   flag = true;               }               catch (Exception ex)               {                   lblMes.Text += ex.Message;                   flag = false;               }           }           else           {               lblMes.Text = "不允许上载：" + myFileUpload.PostedFile.FileName + "，只能上传xls的文件，请检查！";               flag = false;           }       }       else       {           lblMes.Text = "请选择要导入的excel文件!";           flag = false;       }       return flag;   }   //该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径， sheetname为excel文件中的表名

public DataSet ExcelDataSource(string filepath, string sheetname)   {       string strConn;       strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";       OleDbConnection conn = new OleDbConnection(strConn);       OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);       DataSet ds = new DataSet();       oada.Fill(ds);       conn.Close();       return ds;   }   //获得Excel中的所有sheetname。   public ArrayList ExcelSheetName(string filepath)   {       ArrayList al = new ArrayList();       string strConn;       strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";       OleDbConnection conn = new OleDbConnection(strConn);       conn.Open();       DataTable sheetNames = conn.GetOleDbSchemaTable       (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });       conn.Close();       foreach (DataRow dr in sheetNames.Rows)       {           al.Add(dr[2]);       }       return al;   }

也可以在webconfig文件里面指定连接字符串的参数：

<connectionStrings>    <add name="xls"connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample1.xls;Extended Properties=Excel 8.0"/>    <add name="xlsx"connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties=Excel 12.0"/>  </connectionStrings>

用

string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;

注意XP下文件操作权限的问题。

来自:http://www.cnblogs.com/chenqingwei/archive/2009/09/27/1575024.html