在商業應用逐步趨向行動化的今天,業務人員終日在外奔波的狀況已經愈來愈多,當使用者能連線使用系統的機會不高,但又希望他能將資料輸入到系統中,怎麼辦?許多的系統便發展出透過Excel或純文字檔讓業務人員可先在行動裝置如NB、PDA上先輸入完資料,一旦可連線上網路便可將離線輸入的那些資料匯入系統中,達到資料管制的目的,本篇文章簡單說明一下如何透過Excel的離線登打,並上傳回系統。
在商業應用逐步趨向行動化的今天,業務人員終日在外奔波的狀況已經愈來愈多,當使用者能連線使用系統的機會不高,但又希望他能將資料輸入到系統中,怎麼辦?許多的系統便發展出透過Excel或純文字檔讓業務人員可先在行動裝置如NB、PDA上先輸入完資料,一旦可連線上網路便可將離線輸入的那些資料匯入系統中,達到資料管制的目的,本篇文章簡單說明一下如何透過Excel的離線登打,並上傳回系統。
【前置作業】
首先我們先建立一個專案,內容如下:
OfflineDown:裡頭放了我們要讓使用者下載的Excel範本
OfflineUp:用來暫存使用者上傳的Excel檔案
Old:(不管他)
OfflineExcel.aspx:做為使用者下載及上傳的操作介面
預計的操作步驟是這樣的:使用者連上系統下載Excel範本-->進行離線的資料登打-->上傳Excel
既然提到Excel範本,我們先來看看我們的範本內容,裡頭只有四個欄位,這四個欄位是依北風資料庫的Customers資料表截取前四的欄位而來,所以最後我們的資料也是被存到北風資料庫的Customers資料表囉,
接著看一下系統的實際使用介面,非常陽春,只有一個FileUpload跟兩個Button控制項:
【實際操作】
下載範本檔,要離線登打第一件事情當然是先下載範本檔案囉:
輸入要上傳的資料,這邊我們通常會再搭配巨集讓使用者輸入資料時能比較方便,順便做一些格式檢查:
選擇好要上傳的檔案路徑,按下上傳:
出現新增資料成功:
接著到資料庫中查看資料是否有成功被insert進去囉,資料果然成功被insert了,成功囉!!
【程式撰寫】
下載的部分:將Server上準備好的那份excel檔透過Binarywrite輸出到client端
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元件的標準寫法達成:
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中,這邊這樣寫純粹是個人習慣,沒有什麼特別原因。
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到資料庫部分,
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堂課》,為培養台灣未來的領袖而努力。 |