asp.net匯入的excel檔案到資料庫

標籤：

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using System.Data.OleDb;using System.Collections;using System.Data.SqlClient;using Budget.DBUtility;namespace Budget.Web.Admin.RMBase.SysUser{    public partial class ceshi : System.Web.UI.Page    {        SqlConnection sqlcon;        SqlCommand sqlcom;        string strCon = "MyOfficeConnectionString";        protected void Page_Load(object sender, EventArgs e)        {            if (!IsPostBack)            {              //  bind();            }        }        protected void Button1_Click(object sender, EventArgs e)        {            if (Fileupload1.HasFile)            {                //擷取檔案路徑                //string path =  Fileupload1.PostedFile.FileName;                string path = HttpContext.Current.Request.MapPath("../upload/預算管理系統使用者模板.xlsx");                //擷取檔案名稱                string FileName = Fileupload1.FileName;                //根據檔案結尾名判斷檔案格式                if (FileName.ToLower().Substring(FileName.Length - 3) != "xls" && FileName.ToLower().Substring(FileName.Length - 4) != "xlsx")                {                    Response.Write("<script type=‘text/JavaScript‘>alert(‘請選擇Excel檔案！‘)</script>");                    return;                }                //擷取Excel的資料datatable                DataTable dt = GetTable(path, FileName);                using (SqlConnection conn = new SqlConnection("server=192.168.29.59;database=ExpenseBudget4.0Test;uid=sa;pwd=123;"))                {                    conn.Open();                    SqlTransaction sqlTransaction = conn.BeginTransaction();                    //   將事務應用於Command                       SqlCommand sqlCommand = new SqlCommand();                    sqlCommand.Connection = conn;                    sqlCommand.Transaction = sqlTransaction;                    try                    {                        foreach (DataRow row in dt.Rows)                        {                            string user_id = Guid.NewGuid().ToString().ToLower();                            string user_code = row["工號"].ToString();                            string user_name = row["姓名"].ToString();                            string shouji = row["手機號/電話"].ToString();                            string User_Account = row["登入ID"].ToString();                                                   string sql= string.Format("insert into Base_UserInfo(User_ID,User_Code,User_Name,User_Account) values(‘{0}‘,‘{1}‘,‘{2}‘,‘{3}‘)", user_id, user_code, user_name, User_Account);//sql資料庫                            int a = new SqlServerHelper().RunSqlGetRowCount(sql);                                                   sqlCommand.CommandText = string.Format("insert into Base_AppendPropertyInstance(PropertyInstance_ID,PropertyInstance_Key,Property_Control_ID,PropertyInstance_Value) values(NEWID(),‘{0}‘,‘{1}‘,‘{2}‘)", user_id, "User_Tel", shouji);//sql資料庫                            sqlCommand.ExecuteNonQuery();                                                 }                        //成功提交                           sqlTransaction.Commit();                        Response.Write("<script type=‘text/JavaScript‘>alert(‘已成功匯入檔案！‘)</script>");                    }                    catch (Exception ex)                    {                        //   出錯復原                           sqlTransaction.Rollback();                        Response.Write("<script type=‘text/JavaScript‘>alert(‘匯入檔案失敗！‘)</script>");                    }                    finally                    {                        conn.Close();                    }                }            }            else            {                Response.Write("<script type=‘text/JavaScript‘>alert(‘請選擇要匯入的檔案！‘)</script>");            }        }        //將Execl的資料讀取到Datatable        protected DataTable GetTable(string path, string fileName)        {            string connString = "";            if (fileName.ToLower().IndexOf(".xlsx") < 0)            {                connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=‘{0}‘;Extended Properties=‘Excel 8.0;IMEX=1‘", path);            }            else            {                connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=‘{0}‘;Extended Properties=‘Excel 12.0;HDR=YES‘", path);            }            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connString);            string sql = "SELECT * FROM [Sheet1$]";            DataTable dt;            try            {                conn.Open();                System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(sql, conn);                DataSet ds = new DataSet();                myCommand.Fill(ds, "[Sheet1$]");                conn.Close();                dt = ds.Tables[0];                GridView1.DataSource = ds;                GridView1.DataBind();            }            catch (Exception err)            {                return null;            }            return dt;        }        //上傳檔案方法          private bool Upload(FileUpload myFileUpload)        {            bool flag = false;            //是否允許上傳              bool fileAllow = false;            //設定允許上傳的擴充檔案名稱類型              string[] allowExtensions = { ".xls", ".xlsx" };            //取得網站根目錄路徑              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;        }        }}

　　

asp.net匯入的excel檔案到資料庫