读取excel内容,并显示

1.VB版本

  注意：excel版本的不同和对文件的读写权限

 

 读取文件后台代码：

Imports System.Data.OleDb
Imports System.Data

Partial Class ceshi_Default
    Inherits System.Web.UI.Page


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            bangding()
        End If
    End Sub

    Protected Sub bangding()
        Dim path As String = Server.MapPath("upload/Test3.xls")
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;"
        Dim conn As OleDbConnection = New OleDbConnection(strConn)
        conn.Open()
        Dim strExcel As String = ""
        Dim myCommand As OleDbDataAdapter = Nothing
        Dim ds As DataSet = Nothing
        strExcel = "select * from [sheet1$]"
        myCommand = New OleDbDataAdapter(strExcel, strConn)
        ds = New DataSet()
        myCommand.Fill(ds, "table1")
        conn.Close()
        Repeater2.DataSource = ds.Tables(0).DefaultView
        Repeater2.DataBind()
    End Sub
End Class

 

读取文件前台代码：

 

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="ceshi_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
      *
      {
       margin:0px; padding:0px;
       }
    </style>
</head>
<body style="text-align:center;">
    <form id="form1" runat="server">
    <div style=" width:900px; text-align:left; padding:0px; margin:0px;  height:422px; background-image: url('back.jpg')">
        <asp:Repeater ID="Repeater2" runat="server">
        <HeaderTemplate>
           <table style="width:73%; border:0px; margin-top:105px; margin-left:98px; color:White; font-size:13px;">
             <tr><td colspan="4" style="word-spacing:1px; border-bottom:1px solid blue">
                 This is a general notice that there will be a system maintenance affecting product registration services from </td></tr>
             <tr style="width:100%;">
                <td style="width:35%; text-align:left;">Area</td><td style="width:20%; text-align:center;">Time Zone</td><td style="width:20%; text-align:center;">From</td><td style="width:25%; text-align:center;">To</td>
             </tr>
        </HeaderTemplate>
        <ItemTemplate>
             <tr>
               <td><asp:Label ID="Label1" runat="server" Text='<%# Eval("Area") %>'></asp:Label></td>
               <td style="text-align:center;"><asp:Label ID="Label2" runat="server" Text='<%# Eval("Time") %>'></asp:Label></td>
               <td style="text-align:center;"><asp:Label ID="Label3" runat="server" Text='<%# Eval("From") %>'></asp:Label></td>
               <td style="text-align:center;"><asp:Label ID="Label4" runat="server" Text='<%# Eval("To") %>'></asp:Label></td>
             </tr>
        </ItemTemplate>
        <FooterTemplate>
         </table>
         <div style="margin-left:98px; color:White; font-size:13px; margin-top:5px; width:750px; height:40px; border-bottom:1px solid blue">
           The system will not be able to access during the maintenance hours, please save your work and log off before the maintenance start time .<br/>
           thank you for your understanding and support, we are sorry for any inconvenience caused.
        </div>
        </FooterTemplate>
        </asp:Repeater>
    </div>
    </form>
</body>
</html>

 

上传文件后台：

 

Partial Class ceshi_upfile
    Inherits System.Web.UI.Page

    '上传按钮 
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim b As Boolean = Upload(FileUpload1)  '上传excel文件  
        If (Not b) Then
            Return
        End If

        ' Dim name As String = FileUpload1.FileName
        ' Dim filepath As String = Server.MapPath("upload/") + name
    End Sub

    '上传文件方法  
    Private Function Upload(ByVal myFileUpload As FileUpload) As Boolean

        Dim flag As Boolean = False
        '是否允许上载  
        Dim fileAllow As Boolean = False
        '设定允许上载的扩展文件名类型  
        Dim allowExtensions As String() = {".xls"}
        '取得网站根目录路径  
        Dim path As String = HttpContext.Current.Request.MapPath("upload/")
        '检查是否有文件案  
        If myFileUpload.HasFile Then
            '取得上传文件之扩展文件名，并转换成小写字母
            Dim fileExtension As String = System.IO.Path.GetExtension(myFileUpload.FileName).ToLower()
            '检查扩展文件名是否符合限定类型  
            For i As Integer = 0 To allowExtensions.Length - 1
                If fileExtension = allowExtensions(i) Then
                    fileAllow = True
                End If
            Next
            If (fileAllow) Then
                Try
                    '存储文件到文件夹  
                    myFileUpload.SaveAs(path + myFileUpload.FileName)
                    lblMes.Text = "文件导入成功"
                    flag = True
                Catch ex As Exception
                    lblMes.Text += ex.Message
                    flag = False
                End Try
            Else
                lblMes.Text = "不允许上载：" + myFileUpload.PostedFile.FileName + "，只能上传xls的文件，请检查！"
                flag = False
            End If
        Else
            lblMes.Text = "请选择要导入的excel文件!"
            flag = False
        End If
        Return flag
    End Function

End Class

 

 

 

2.C#版本(转载牛腩的)

 

  1. // 上传按钮   
  2.     protected void btnUp_Click(object sender, EventArgs e)   
  3.     {   
  4.         bool b = Upload(fuExcel);  // 上传excel文件   
  5.         if (!b)   
  6.         {   
  7.             return;   
  8.         }   
  9.         string name = fuExcel.FileName;   
  10.         string filepath = Server.MapPath("~/upload/") + name;   
  11.         DataSet ds = ExcelDataSource(filepath, ExcelSheetName(filepath)[0].ToString());   
  12.         GridView1.DataSource = ds;   
  13.         GridView1.DataBind();   
  14.     }   
  15.   
  16.     //上传文件方法   
  17.     private bool Upload(FileUpload myFileUpload)   
  18.     {   
  19.         bool flag = false;   
  20.         //是否允许上载   
  21.         bool fileAllow = false;   
  22.         //设定允许上载的扩展文件名类型   
  23.         string[] allowExtensions = { ".xls" };   
  24.   
  25.         //取得网站根目录路径   
  26.         string path = HttpContext.Current.Request.MapPath("~/upload/");   
  27.         //检查是否有文件案   
  28.         if (myFileUpload.HasFile)   
  29.         {   
  30.             //取得上传文件之扩展文件名，并转换成小写字母   
  31.             string fileExtension = System.IO.Path.GetExtension(myFileUpload.FileName).ToLower();   
  32.             //检查扩展文件名是否符合限定类型   
  33.             for (int i = 0; i < allowExtensions.Length; i++)   
  34.             {   
  35.                 if (fileExtension == allowExtensions[i])   
  36.                 {   
  37.                     fileAllow = true;   
  38.                 }   
  39.             }   
  40.   
  41.             if (fileAllow)   
  42.             {   
  43.                 try  
  44.                 {   
  45.                     //存储文件到文件夹   
  46.                     myFileUpload.SaveAs(path + myFileUpload.FileName);   
  47.                     lblMes.Text = "文件导入成功";   
  48.                     flag = true;   
  49.                 }   
  50.                 catch (Exception ex)   
  51.                 {   
  52.                     lblMes.Text += ex.Message;   
  53.                     flag = false;   
  54.                 }   
  55.             }   
  56.             else  
  57.             {   
  58.                 lblMes.Text = "不允许上载：" + myFileUpload.PostedFile.FileName + "，只能上传xls的文件，请检查！";   
  59.                 flag = false;   
  60.             }   
  61.         }   
  62.         else  
  63.         {   
  64.             lblMes.Text = "请选择要导入的excel文件!";   
  65.             flag = false;   
  66.         }   
  67.         return flag;   
  68.     }   
  69.   
  70.     //该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径， sheetname为excel文件中的表名   
  71.     public DataSet ExcelDataSource(string filepath, string sheetname)   
  72.     {   
  73.         string strConn;   
  74.         strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";   
  75.         OleDbConnection conn = new OleDbConnection(strConn);   
  76.         OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);   
  77.         DataSet ds = new DataSet();   
  78.         oada.Fill(ds);   
  79.         conn.Close();   
  80.         return ds;   
  81.     }   
  82.   
  83.     //获得Excel中的所有sheetname。   
  84.     public ArrayList ExcelSheetName(string filepath)   
  85.     {   
  86.         ArrayList al = new ArrayList();   
  87.         string strConn;   
  88.         strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";   
  89.         OleDbConnection conn = new OleDbConnection(strConn);   
  90.         conn.Open();   
  91.         DataTable sheetNames = conn.GetOleDbSchemaTable   
  92.         (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });   
  93.         conn.Close();   
  94.         foreach (DataRow dr in sheetNames.Rows)   
  95.         {   
  96.             al.Add(dr[2]);   
  97.         }   
  98.         return al;   
  99.     }  

 
posted @ 2010-09-26 11:25  yu_liantao  阅读(553)  评论(0)    收藏  举报