[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堂課》,為培養台灣未來的領袖而努力。