[C#][WebForm]Excel to GridView
最近公司內部系統剛好有這需求，雖然自己對Excel沒啥好感
但面對衣食父母的要求也得開開心心完成這小小功能。
這裡簡單記錄一下。
設定Web.config
appSettings增加上傳存放路徑
connectionStrings增加連線字串
.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);
}結果:
