#眉標=Enterprise Library、Smart Client、Software Factory #副標=設計模型套件系列（22） #大標=建置Windows應用程式的軟體工程 #作者=文／圖 王寧疆 ===<反灰>============= public const string ImportExcelToDB = "ImportExcelToDB"; ================ ===<反灰>============= AddToolStripButton(Constants.CommandNames.ImportExcelToDB, Properties.Resources.ImportExcel, "匯入Excel文件到資料庫"); ================ ===<反灰>============= using System.Drawing; using SCSFDemo.ImportExcelModule.Interface.Constants; ================ ===<反灰>============= private void AddToolStripButton(string CommandName, Image ButtonImage, string ToolTipText) { ToolStripButton button = new ToolStripButton(); //建立ToolStripButton類別的物件 button.Image=ButtonImage; //設定ToolStripButton類別的物件顯示的圖示 button.ToolTipText = ToolTipText; //建立ToolStripButton類別的物件顯示的工具提示 WorkItem.UIExtensionSites[UIExtensionSiteNames.MainToolbar].Add(button); //將Button加入到工具列 WorkItem.Commands[CommandName].AddInvoker(button, "Click"); //指定處理Click事件的命令名稱 } ================ ===<反灰>============= using Microsoft.Practices.CompositeUI; using System.IO; ================ ===<反灰>============= [Service] public class FileService { public string[] GetExcelDocumentLists() { List li = new List(); //建立List集合物件 DirectoryInfo di = new DirectoryInfo("Documents"); //建立DirectoryInfo物件 foreach (FileInfo fi in di.GetFiles("*.xls")) //取出Documents中xls檔案 { li.Add(fi.FullName); //將xls檔案的名稱加入到List集合 } return li.ToArray(); //將名稱轉型成字串陣列後傳回 } } ================ ===<反灰>============= using System.Data.SqlClient; using System.Data; ================ ===<反灰>============= [Service] public class DBService { string strConn = "Data Source=.;Initial Catalog=Northwind;Integrated Security=true"; public DataTable GetOrderDetails() { string strSQL = "Select * from [Order Details]"; //查詢訂單明細的SQL敘述 SqlDataAdapter da = new SqlDataAdapter(strSQL, strConn); //建立SqlDataAdapter物件 DataSet ds = new DataSet(); //建立DataSet類別的物件 da.Fill(ds); //將查詢得到訂單明細記錄存入DataSet物件 return ds.Tables[0]; //傳回存放訂單明細記錄的資料表 } public void InsertOrderDetails(int OrderID, int ProductID, float UnitPrice, Int16 Quantity, float Discount) { string strSQL = "Insert Into [Order Details](OrderID, ProductID, UnitPrice, Quantity, Discount) " + "Values(@OrderID,@ProductID,@UnitPrice,@Quantity,@Discount)"; //新增訂單明細記錄 SqlConnection conn = new SqlConnection(strConn); //建立SqlConnection物件 SqlCommand cmd = new SqlCommand(strSQL, conn); //建立SqlCommand物件 cmd.CommandType = CommandType.Text; //設定SqlCommand類別的物件的CommandType屬性 cmd.Parameters.AddWithValue("@OrderID", OrderID); //填入OrderID參數 cmd.Parameters.AddWithValue("@ProductID", ProductID); //填入ProductID參數 cmd.Parameters.AddWithValue("@UnitPrice", UnitPrice); //填入UnitPrice參數 cmd.Parameters.AddWithValue("@Quantity", Quantity); //填入Quantity參數 cmd.Parameters.AddWithValue("@Discount", Discount); //填入Discount參數 conn.Open(); //開啟資料庫連線 cmd.ExecuteNonQuery(); //執行新增訂單明細記錄 cmd.Dispose(); //丟棄SqlCommand類別的物件 conn.Close(); cmd.Dispose(); //丟棄SqlConnection類別的物件 } } ================ ===<反灰>============= void BindDocumentList(string[] DocList); ================ ===<反灰>============= public void BindDocumentList(string[] DocList) { treeView1.Nodes.Clear(); //清除TreeView顯示的文件 TreeNode root=treeView1.Nodes.Add("訂單"); //為TreeView加入根節點 foreach(string s in DocList) //取出Excel檔名 { root.Nodes.Add(s); //將Excel檔名加入到根節點下 } } ================ ===<反灰>============= [ServiceDependency] public FileService ListFileService { set { m_FileService = value; } } ================ ===<反灰>============= public override void OnViewReady() { View.BindDocumentList( m_FileService.GetExcelDocumentLists()); //呼叫DocView類別的BindDocumentList方法 base.OnViewReady(); } ================ ===<反灰>============= OrderID ProductID UnitPrice Quantity Discount 10248 1 18 10 0 ================ ===<反灰>============= DocView docview = ShowViewInWorkspace(WorkspaceNames.LeftWorkspace); ================ ===<反灰>============= public void BindOrderDetails(System.Data.DataTable dt) { dataGridView1.DataSource = dt; //設定DataGridView顯示的資料來源 } ================ ===<反灰>============= [ServiceDependency] public DBService QueryDBService { set { m_DBService = value; } } ================ ===<反灰>============= public override void OnViewReady() { View.BindOrderDetails(m_DBService.GetOrderDetails()); //呼叫View的BindOrderDetails base.OnViewReady(); } ================ ===<反灰>============= DBView dbview = ShowViewInWorkspace(WorkspaceNames.RightWorkspace); ================ ===<反灰>============= using Microsoft.Practices.CompositeUI.Commands; using System.Windows.Forms; ================ ===<反灰>============= [CommandHandler(Constants.CommandNames.ImportExcelToDB)] public void OnImportExcelToDB(object sender, EventArgs e) { if (treeView1.SelectedNode != null) //判斷是否已選取匯入的Excel { if (Path.GetExtension( treeView1.SelectedNode.Text).ToUpper().Equals(".XLS")) //判斷匯入文件的副檔 { OnDataImported(new Microsoft.Practices.CompositeUI.Utility.DataEventArgs( treeView1.SelectedNode.Text)); //引發DataImported事件 MessageBox.Show(treeView1.SelectedNode.Text + "匯入Excel文件成功!"); //匯入文件成功 } else { MessageBox.Show("請選擇欲匯入資料庫的文件!"); //錯誤訊息 } } } ================ ===<反灰>============= public void ImportExcelData(string FileName) { string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"; //連線Excel文件的資訊 string strSQL = "Select * from [Sheet1$]"; //查詢第一個工作表 　　DataSet ds = new DataSet(); //建立DataSet物件 　 OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); //建立OleDbDataAdapter物件 da.Fill(ds); //執行查詢，並將查詢結果存入DataSet物件 foreach (DataRow dr in ds.Tables[0].Rows) //取得查詢得到的記錄 { int OrderID = Convert.ToInt32(dr["OrderID"]); //取得OrderID欄位值 int ProductID = Convert.ToInt32(dr["ProductID"]); //取得ProductID欄位值 float UnitPrice = Convert.ToSingle(dr["UnitPrice"]); //取得UnitPrice欄位值 Int16 Quantity = Convert.ToInt16(dr["Quantity"]); //取得Quantity欄位值 float Discount = Convert.ToSingle(dr["Discount"]); //取得Discount欄位值 m_DBService.InsertOrderDetails( OrderID,ProductID,UnitPrice,Quantity,Discount); //資料存入資料庫 } View.BindOrderDetails(m_DBService.GetOrderDetails()); //命令DBView顯示最新明細 } ================ ===<反灰>============= ================ ============= 環境建置 ● Smart Client Software Factory – April 2008：http://www.microsoft.com/downloads/details.aspx?FamilyID=3BE112CC-B2C1-4215-9330-9C8CF9BCC6FA&displaylang=en。 ● Guidance Automation Extensions - February 2008 Release：http://www.microsoft.com/downloads/details.aspx?FamilyID=df79c099-4753-4a59-91e3-5020d9714e4e&DisplayLang=en。 ================