ASP.NET中读取Excel内容，并显示在界面上代码,Asp.Net代码库,德仔网

代码语言
代码分类
【Asp.Net】 ASP.NET中读取Excel内容，并显示在界面上
作者:chenqingwei / 发布于2011/5/4/ 629
<div>客户端上传本地excel文件到服务器上，并在客户端显示。
<pre class="code"><span style="color: green">  // 上传按钮  
   </span><span style="color: blue">protected void </span>btnUp_Click(<span style="color: blue">object </span>sender, <span style="color: rgb(43,145,175)">EventArgs </span>e)
   {
       <span style="color: blue">bool </span>b = Upload(fuExcel);  <span style="color: green">// 上传excel文件 
       </span><span style="color: blue">if </span>(!b)
       {
           <span style="color: blue">return</span>;
       }
       <span style="color: blue">string </span>name = fuExcel.FileName;
       <span style="color: blue">string </span>filepath = Server.MapPath(<span style="color: rgb(163,21,21)">"~/upload/"</span>) + name;
       <span style="color: rgb(43,145,175)">DataSet </span>ds = ExcelDataSource(filepath, ExcelSheetName(filepath)[0].ToString());
       GridView1.DataSource = ds;
       GridView1.DataBind();
   }

   <span style="color: green">//上传文件方法  
   </span><span style="color: blue">private bool </span>Upload(<span style="color: rgb(43,145,175)">FileUpload </span>myFileUpload)
   {
       <span style="color: blue">bool </span>flag = <span style="color: blue">false</span>;
       <span style="color: green">//是否允许上载  
       </span><span style="color: blue">bool </span>fileAllow = <span style="color: blue">false</span>;
       <span style="color: green">//设定允许上载的扩展文件名类型  
       </span><span style="color: blue">string</span>[] allowExtensions = { <span style="color: rgb(163,21,21)">".xls" </span>};

       <span style="color: green">//取得网站根目录路径  
       </span><span style="color: blue">string </span>path = <span style="color: rgb(43,145,175)">HttpContext</span>.Current.Request.MapPath(<span style="color: rgb(163,21,21)">"~/upload/"</span>);
      <span style="color: green"> </span><span style="color: blue">if </span>(myFileUpload.HasFile)
       { <span style="color: green"> 
           </span><span style="color: blue">string </span>fileExtension = System.IO.<span style="color: rgb(43,145,175)">Path</span>.GetExtension(myFileUpload.FileName).ToLower();
        <span style="color: green">   </span><span style="color: blue">for </span>(<span style="color: blue">int </span>i = 0; i < allowExtensions.Length; i++)
           {
               <span style="color: blue">if </span>(fileExtension == allowExtensions[i])
               {
                   fileAllow = <span style="color: blue">true</span>;
               }
           }

           <span style="color: blue">if </span>(fileAllow)
           {
               <span style="color: blue">try
               </span>{
                   <span style="color: green">//存储文件到文件夹 
                   </span>myFileUpload.SaveAs(path + myFileUpload.FileName);
                   lblMes.Text = <span style="color: rgb(163,21,21)">"文件导入成功"</span>;
                   flag = <span style="color: blue">true</span>;
               }
               <span style="color: blue">catch </span>(<span style="color: rgb(43,145,175)">Exception </span>ex)
               {
                   lblMes.Text += ex.Message;
                   flag = <span style="color: blue">false</span>;
               }
           }
           <span style="color: blue">else
           </span>{
               lblMes.Text = <span style="color: rgb(163,21,21)">"不允许上载：" </span>+ myFileUpload.PostedFile.FileName + <span style="color: rgb(163,21,21)">"，只能上传xls的文件，请检查！";</span>
               flag = <span style="color: blue">false</span>;
           }
       }

       <span style="color: blue">else
       </span>{
           lblMes.Text = <span style="color: rgb(163,21,21)">"请选择要导入的excel文件!"</span>;
           flag = <span style="color: blue">false</span>;
       }
       <span style="color: blue">return </span>flag;
   }

   <span style="color: green">//该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径， sheetname为excel文件中的表名</span></pre>
<pre class="code"><span style="color: green">   </span><span style="color: blue">public </span><span style="color: rgb(43,145,175)">DataSet </span>ExcelDataSource(<span style="color: blue">string </span>filepath, <span style="color: blue">string </span>sheetname)
   {
       <span style="color: blue">string </span>strConn;
       strConn = <span style="color: rgb(163,21,21)">"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" </span>+ filepath + <span style="color: rgb(163,21,21)">";Extended Properties=Excel 8.0;"</span>;
       OleDbConnection conn = <span style="color: blue">new </span>OleDbConnection(strConn);
       OleDbDataAdapter oada = <span style="color: blue">new </span>OleDbDataAdapter(<span style="color: rgb(163,21,21)">"select * from [" </span>+ sheetname + <span style="color: rgb(163,21,21)">"]"</span>, strConn);
       <span style="color: rgb(43,145,175)">DataSet </span>ds = <span style="color: blue">new </span><span style="color: rgb(43,145,175)">DataSet</span>();
       oada.Fill(ds);
       conn.Close();
       <span style="color: blue">return </span>ds;
   }

   <span style="color: green">//获得Excel中的所有sheetname。
   </span><span style="color: blue">public </span><span style="color: rgb(43,145,175)">ArrayList </span>ExcelSheetName(<span style="color: blue">string </span>filepath)
   {
       <span style="color: rgb(43,145,175)">ArrayList </span>al = <span style="color: blue">new </span><span style="color: rgb(43,145,175)">ArrayList</span>();
       <span style="color: blue">string </span>strConn;
       strConn = <span style="color: rgb(163,21,21)">"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" </span>+ filepath + <span style="color: rgb(163,21,21)">";Extended Properties=Excel 8.0;"</span>;
       OleDbConnection conn = <span style="color: blue">new </span>OleDbConnection(strConn);
       conn.Open();
       <span style="color: rgb(43,145,175)">DataTable </span>sheetNames = conn.GetOleDbSchemaTable
       (System.Data.OleDb.<span style="color: rgb(43,145,175)">OleDbSchemaGuid</span>.Tables, <span style="color: blue">new object</span>[] { <span style="color: blue">null</span>, <span style="color: blue">null</span>, <span style="color: blue">null</span>, <span style="color: rgb(163,21,21)">"TABLE" </span>});
       conn.Close();
       <span style="color: blue">foreach </span>(<span style="color: rgb(43,145,175)">DataRow </span>dr <span style="color: blue">in </span>sheetNames.Rows)
       {
           al.Add(dr[2]);
       }
       <span style="color: blue">return </span>al;
   } </pre>
<pre class="code">
也可以在webconfig文件里面指定连接字符串的参数：</pre>
<pre class="code"><span style="color: blue"><</span><span style="color: rgb(163,21,21)">connectionStrings</span><span style="color: blue">>

    <</span><span style="color: rgb(163,21,21)">add </span><span style="color: red">name</span><span style="color: blue">=</span>"<span style="color: blue">xls</span>"<span style="color: red">connectionString</span><span style="color: blue">=</span>"<span style="color: blue">Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample1.xls;Extended Properties=Excel 8.0</span>"<span style="color: blue">/>

    <</span><span style="color: rgb(163,21,21)">add </span><span style="color: red">name</span><span style="color: blue">=</span>"<span style="color: blue">xlsx</span>"<span style="color: red">connectionString</span><span style="color: blue">=</span>"<span style="color: blue">Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties=Excel 12.0</span>"<span style="color: blue">/>

  </</span><span style="color: rgb(163,21,21)">connectionStrings</span><span style="color: blue">></span></pre>
用
<pre class="code"><span style="color: blue">string </span>connString = <span style="color: rgb(43,145,175)">ConfigurationManager</span>.ConnectionStrings[<span style="color: rgb(163,21,21)">"xls"</span>].ConnectionString;</pre>
注意XP下文件操作权限的问题。
</div>
试试其它关键字
　读取Excel内容　
同语言下
可能有用的
chenqingwei贡献的其它代码(18)