[C#][WebForm]Excel to GridView

  • 8443
  • 0
  • C#
  • 2009-12-11

[C#][WebForm]Excel to GridView

最近公司內部系統剛好有這需求,雖然自己對Excel沒啥好感

但面對衣食父母的要求也得開開心心完成這小小功能。

這裡簡單記錄一下。

 

設定Web.config

appSettings增加上傳存放路徑

image

connectionStrings增加連線字串

image

.aspx


<form id="form1" runat="server"> 
   
    <asp:FileUpload ID="FileUpload1" runat="server" />   
    <asp:DropDownList
        ID="DropDownList1" runat="server" AutoPostBack="true" 
        onselectedindexchanged="DropDownList1_SelectedIndexChanged"></asp:DropDownList>
         <asp:Button ID="Button1" runat="server" Text="上傳" onclick="Button1_Click" />
    
     <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
    
     <asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource>
     
    </form>

.aspx.cs


protected void Button1_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                String FName = Path.GetFileName(FileUpload1.PostedFile.FileName);                
                String ExcelPath = ConfigurationManager.AppSettings["ExcelPath"];//取得Server資料夾
                String FullPath = Server.MapPath(ExcelPath + FName);
                FileUpload1.SaveAs(FullPath);
                Cache["excelpath"] = FullPath;   
                ExcelToGV(FullPath, "");               
            }
        }      

 private void ExcelToGV(String FullPath, String MySheet)
        {            
            String conStr = ConfigurationManager.ConnectionStrings["ExcelConString"].ConnectionString;
            conStr = String.Format(conStr, FullPath);
            OleDbConnection exlconn = new OleDbConnection(conStr);
            OleDbCommand exlcmd = new OleDbCommand();
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataTable dt = new DataTable();           
            exlcmd.Connection = exlconn;   
            exlconn.Open();
            if (MySheet.Length<=0)
            {
                DataTable Exldt = exlconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //取得全部 sheet
                foreach (DataRow dr in Exldt.Rows)
                {
                    DropDownList1.Items.Add(dr.ItemArray[2].ToString());
                }
                MySheet = DropDownList1.Items[0].Value.ToString();
            }  
            //讀取資料           
            exlcmd.CommandText = "SELECT * From [" + MySheet + "]";
            da.SelectCommand = exlcmd;
            da.Fill(dt);
            exlconn.Close();
            GridView1.Caption = Path.GetFileName(FullPath)+"檔案內容";
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }        

 protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            String mysheet = DropDownList1.SelectedItem.Value.ToString();
            ExcelToGV(Cache["excelpath"].ToString(), mysheet);
        }

結果:

image

image 

image