gipi的學習筆記-經營管理、數據思考、終身學習

分享我的職場心得、管理思維、專案管理、產品管理與策略規劃經驗談

2009-06-07

[ASP.NET]Excel離線處理後上傳

在商業應用逐步趨向行動化的今天，業務人員終日在外奔波的狀況已經愈來愈多，當使用者能連線使用系統的機會不高，但又希望他能將資料輸入到系統中，怎麼辦？許多的系統便發展出透過Excel或純文字檔讓業務人員可先在行動裝置如NB、PDA上先輸入完資料，一旦可連線上網路便可將離線輸入的那些資料匯入系統中，達到資料管制的目的，本篇文章簡單說明一下如何透過Excel的離線登打，並上傳回系統。

在商業應用逐步趨向行動化的今天，業務人員終日在外奔波的狀況已經愈來愈多，當使用者能連線使用系統的機會不高，但又希望他能將資料輸入到系統中，怎麼辦？許多的系統便發展出透過Excel或純文字檔讓業務人員可先在行動裝置如NB、PDA上先輸入完資料，一旦可連線上網路便可將離線輸入的那些資料匯入系統中，達到資料管制的目的，本篇文章簡單說明一下如何透過Excel的離線登打，並上傳回系統。

【前置作業】

首先我們先建立一個專案，內容如下：

image

OfflineDown：裡頭放了我們要讓使用者下載的Excel範本

OfflineUp：用來暫存使用者上傳的Excel檔案

Old：(不管他)

OfflineExcel.aspx：做為使用者下載及上傳的操作介面

 預計的操作步驟是這樣的：使用者連上系統下載Excel範本-->進行離線的資料登打-->上傳Excel

既然提到Excel範本，我們先來看看我們的範本內容，裡頭只有四個欄位，這四個欄位是依北風資料庫的Customers資料表截取前四的欄位而來，所以最後我們的資料也是被存到北風資料庫的Customers資料表囉，

image 

接著看一下系統的實際使用介面，非常陽春，只有一個FileUpload跟兩個Button控制項：

image

 

【實際操作】

 

下載範本檔，要離線登打第一件事情當然是先下載範本檔案囉：

image 

輸入要上傳的資料，這邊我們通常會再搭配巨集讓使用者輸入資料時能比較方便，順便做一些格式檢查：

image

選擇好要上傳的檔案路徑，按下上傳：

image

出現新增資料成功：

image

 

 

接著到資料庫中查看資料是否有成功被insert進去囉，資料果然成功被insert了，成功囉!!

image

 

【程式撰寫】

下載的部分：將Server上準備好的那份excel檔透過Binarywrite輸出到client端

 

01     protected void Download_Click(object sender, EventArgs e)
02     {
03         Page.Response.ContentType = "application/vnd.ms-excel";
04         Page.Response.AddHeader("content-disposition", "attachment; filename=" + "OfflineData.xls");
05         FileStream tDownFile = new FileStream(System.Web.HttpContext.Current.Server.MapPath("./OfflineDown/OfflineData.xls"), FileMode.Open);
06         long tFileSize;
07         tFileSize = tDownFile.Length;
08         byte[] tContent = new byte[(int)tFileSize];
09         tDownFile.Read(tContent, 0, (int)tDownFile.Length);
10         tDownFile.Close();
11         Page.Response.BinaryWrite(tContent);
12     } 

上傳的部分分為三階段：上傳檔案到Server、從Excel中讀出資料、將Excel讀出的資料insert到北風資料庫

上傳檔案部分：FileUpload元件的標準寫法達成：

01 private void saveUploadFile()  
02 {  
03     HttpPostedFile tUploadFile = FileUpload1.PostedFile;  
04     int tFileLength = tUploadFile.ContentLength;  
05     byte[] tFileByte = new byte[tFileLength];  
06     tUploadFile.InputStream.Read(tFileByte, 0, tFileLength);  
07
08     FileStream tNewfile = new FileStream(System.Web.HttpContext.Current.Server.MapPath("./OfflineUp/")+DateTime.Now.ToString("yyyyMMddhhmm") + "_upload.xls", FileMode.Create);  
09     tNewfile.Write(tFileByte, 0, tFileByte.Length);  
10     tNewfile.Close();  
11
12     //這是一個全域變數，記錄excel的上傳路徑  
13     gUploadFileName = tNewfile.Name;  
14 }

從Excel讀取資料部分，這邊是透過OleDb的方式取讀取所要的excel檔案，並將資料轉存到DataTable中，這邊這樣寫純粹是個人習慣，沒有什麼特別原因。

01 private DataTable readDataFromXls()  
02 {
03
04 OleDbConnection tConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + gUploadFileName + ";Extended Properties=Excel 8.0;");  
05
06     try  
07     {  
08         tConn.Open();   //開啟excel路徑  
09         DataSet tDs = new DataSet();  
10
11         OleDbDataAdapter tDa = new OleDbDataAdapter("Select * From [Customers$] ", tConn);  
12         tDa.Fill(tDs, "Customers"); //將excel內的sheet讀入dataset中  
13         DataTable tDt = tDs.Tables["Customers"];  
14         return tDt;  
15     }  
16     catch  
17     {  
18         System.IO.FileInfo tDeletfile = new FileInfo(gUploadFileName);  
19         tDeletfile.Delete();  
20         string tErroralert = "<script>alert('上傳Excel格式錯誤')</script>";  
21         ClientScript.RegisterStartupScript(typeof(OfflineExcel), "formaterror", tErroralert);  
22         return null;  
23     }  
24     finally  
25     {  
26         tConn.Close();  
27     }  
28 }

 將Excel讀出的資料insert到資料庫部分，

01 private void insertOfflineDataToDB(DataTable pOfflineData)  
02 {  
03     SqlConnection tSqlConn = new SqlConnection(@"server=.;database=Northwind;uid=sa;pwd=sa");  
04
05     try  
06     {  
07         tSqlConn.Open();  
08         for (int i = 0; i < pOfflineData.Rows.Count; i++)  
09         {  
10             string tCommand = String.Format("Insert into Customers (CustomerID, CompanyName, ContactName, ContactTitle) Values (@CustomerID, @CompanyName, @ContactName, @ContactTitle)");  
11
12             SqlCommand tSqlCmd = new SqlCommand(tCommand, tSqlConn);  
13             tSqlCmd.Parameters.AddWithValue("CustomerID", pOfflineData.Rows[i][0].ToString());  
14             tSqlCmd.Parameters.AddWithValue("CompanyName", pOfflineData.Rows[i][1].ToString());  
15             tSqlCmd.Parameters.AddWithValue("ContactName", pOfflineData.Rows[i][2].ToString());  
16             tSqlCmd.Parameters.AddWithValue("ContactTitle", pOfflineData.Rows[i][3].ToString());  
17             tSqlCmd.ExecuteNonQuery();  
18
19         }  
20         string tErroralert = "<script>alert('新增資料成功!!')</script>";  
21         ClientScript.RegisterStartupScript(typeof(OfflineExcel), "formaterror", tErroralert);  
22     }  
23     catch(Exception ex)  
24     {  
25         string tErroralert = "<script>alert('新增資料失敗!!')</script>";  
26         ClientScript.RegisterStartupScript(typeof(OfflineExcel), "formaterror", tErroralert);  
27     }  
28     finally  
29     {  
30         tSqlConn.Close();  
31     }  
32 }

 如此就完成了Excel離線輸入後上傳回系統的功能囉，對使用者方便，又不會太難寫，。

游舒帆 (gipi)

探索原力Co-founder，曾任TutorABC協理與鼎新電腦總監，並曾獲選兩屆微軟最有價值專家 ( MVP )，離開職場後創辦探索原力，致力於協助青少年培養面對未來的能力。認為教育與組織育才其實息息相關，都是在為未來儲備能量，2018年起成立為期一年的專題課程《職涯躍升的關鍵24堂課》，為培養台灣未來的領袖而努力。

gipi