2014年6月26日 星期四

ASP.NET MVC 匯入 Excel 簡單做 - Part.2 匯入資料

上一篇完成了 Excel 的檔案上傳功能,那接下來要處理的就是已上傳 Excel 檔案的資料匯入,其實整個操作的流程會是檔案上傳完成後就直接進行 Excel 資料的匯入,我們就來完成這些功能吧。

 


請務必要先看過上一篇「ASP.NET MVC 匯入 Excel 簡單做 - Part.1 檔案上傳」,否則會在很多地方感覺到莫名其妙。

Excel 資料的匯入功能,其實資料的內容會決定功能好不好做,有的 Excel 資料的格式試相當複雜,例如欄位並非固定,有的會跨欄、有的會跨行,有的一張表會需要匯入到不同的資料庫表格,不管怎樣的格式,我們這邊就是相當單純的一個 Excel 檔案、一個 WorkSheet、直接匯入到一個資料庫表格。

資料的匯入並不是上傳一個 Excel 資料之後就把資料讀出來然後直接寫入到 Table 裡,在匯入之前要先做資料的檢查,以確保資料匯入的正確性,如果所上傳的 Excel 資料有錯誤,則該次的所上傳的 Excel 資料就不匯入,並且要讓使用者知道所上傳的 Excel 資料有哪些錯誤。

 

建立相關檔案

CheckResult.cs

image

這是用來裝載匯入資料的檢查資訊,其實如果做得更為仔細一點的話,這就是每一次上傳 Excel 檔案的檢查紀錄檔。

再來就是 ImportDataHelper.cs,這個類別就是用來檢查上傳的 Excel 檔案資料,以及將 Excel 資料匯入到 Table 裡,而讀取 Excel 資料的部份就是要透過 Linq to Excel,這個套件可以透過 NuGet 來安裝,

image

ImportDataHelper.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using BlogSample.Models;
using LinqToExcel;
 
namespace BlogSample.Infrastructure.Helpers
{
    public class ImportDataHelper
    {
        /// <summary>
        /// 檢查匯入的 Excel 資料.
        /// </summary>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="importZipCodes">The import zip codes.</param>
        /// <returns></returns>
        public CheckResult CheckImportData(
            string fileName,
            List<TaiwanZipCode> importZipCodes)
        {
            var result = new CheckResult();
 
            var targetFile = new FileInfo(fileName);
 
            if (!targetFile.Exists)
            {
                result.ID = Guid.NewGuid();
                result.Success = false;
                result.ErrorCount = 0;
                result.ErrorMessage = "匯入的資料檔案不存在";
                return result;
            }
 
            var excelFile = new ExcelQueryFactory(fileName);
 
            //欄位對映
            excelFile.AddMapping<TaiwanZipCode>(x => x.ID, "ID");
            excelFile.AddMapping<TaiwanZipCode>(x => x.Zip, "Zip");
            excelFile.AddMapping<TaiwanZipCode>(x => x.CityName, "CityName");
            excelFile.AddMapping<TaiwanZipCode>(x => x.Town, "Town");
            excelFile.AddMapping<TaiwanZipCode>(x => x.Sequence, "Sequence");
 
            //SheetName
            var excelContent = excelFile.Worksheet<TaiwanZipCode>("臺灣郵遞區號");
 
            int errorCount = 0;
            int rowIndex = 1;
            var importErrorMessages = new List<string>();
 
            //檢查資料
            foreach (var row in excelContent)
            {
                var errorMessage = new StringBuilder();
                var zipCode = new TaiwanZipCode();
 
                zipCode.ID = row.ID;
                zipCode.Sequence = row.Sequence;
                zipCode.Zip = row.Zip;
                zipCode.CreateDate = DateTime.Now;
 
                //CityName
                if (string.IsNullOrWhiteSpace(row.CityName))
                {
                    errorMessage.Append("縣市名稱 - 不可空白. ");
                }
                zipCode.CityName = row.CityName;
 
                //Town
                if (string.IsNullOrWhiteSpace(row.Town))
                {
                    errorMessage.Append("鄉鎮市區名稱 - 不可空白. ");
                }
                zipCode.Town = row.Town;
 
                //=============================================================================
                if (errorMessage.Length > 0)
                {
                    errorCount += 1;
                    importErrorMessages.Add(string.Format(
                        "第 {0} 列資料發現錯誤:{1}{2}",
                        rowIndex,
                        errorMessage,
                        "<br/>"));
                }
                importZipCodes.Add(zipCode);
                rowIndex += 1;
            }
 
            try
            {
                result.ID = Guid.NewGuid();
                result.Success = errorCount.Equals(0);
                result.RowCount = importZipCodes.Count;
                result.ErrorCount = errorCount;
 
                string allErrorMessage = string.Empty;
 
                foreach (var message in importErrorMessages)
                {
                    allErrorMessage += message;
                }
 
                result.ErrorMessage = allErrorMessage;
 
                return result;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
 
 
        /// <summary>
        /// Saves the import data.
        /// </summary>
        /// <param name="importZipCodes">The import zip codes.</param>
        /// <exception cref="System.NotImplementedException"></exception>
        public void SaveImportData(IEnumerable<TaiwanZipCode> importZipCodes)
        {
            try
            {
                //先砍掉全部資料
                using (var db = new SampleEntities())
                {
                    foreach (var item in db.TaiwanZipCodes.OrderBy(x => x.ID))
                    {
                        db.TaiwanZipCodes.Remove(item);
                    }
                    db.SaveChanges();
                }
 
                //再把匯入的資料給存到資料庫
                using (var db = new SampleEntities())
                {
                    foreach (var item in importZipCodes)
                    {
                        db.TaiwanZipCodes.Add(item);
                    }
                    db.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }
    }
}

在最後的資料寫入 Table 部份,我並沒有去做資料重複性的檢查,因為是簡單做,所以我就直接先把原本的資料給全部刪除,接著再做匯入資料的新增。

 

ZipCodeController.cs

完成資料匯入的相關檔案建立之後,回到 ZipCodeController 裡建立檔案上傳完成之後的資料匯入的流程處理,

[HttpPost]
public ActionResult Import(string savedFileName)
{
    var jo = new JObject();
    string result;
 
    try
    {
        var fileName = string.Concat(Server.MapPath(fileSavedPath), "/", savedFileName);
 
        var importZipCodes = new List<TaiwanZipCode>();
 
        var helper = new ImportDataHelper();
        var checkResult = helper.CheckImportData(fileName, importZipCodes);
 
        jo.Add("Result", checkResult.Success);
        jo.Add("Msg", checkResult.Success ? string.Empty : checkResult.ErrorMessage);
 
        if (checkResult.Success)
        {
            //儲存匯入的資料
            helper.SaveImportData(importZipCodes);
        }
        result = JsonConvert.SerializeObject(jo);
    }
    catch (Exception ex)
    {
        throw;
    }
    return Content(result, "application/json");
}

 

前端

完成了 ZipCodeController.cs 的 Import Action 方法,所以就在 View 裡增加方法的路徑,並且在 project.ZipCode.js 增加匯入資料的前端處理。

~/Views/ZipCode/Index.cshtml

image

project.ZipCode.js

//================================================================================================
/// <reference path="_references.js" /> Ps.如果你要新增給IntelliSense用的js檔案, 請加在該檔案中
//================================================================================================
 
(function (app) {
    //===========================================================================================
    var current = app.ZipCode = {};
    //===========================================================================================
 
    jQuery.extend(app.ZipCode,
    {
        Initialize: function (actionUrls) {
            /// <summary>
            /// 初始化函式
            /// </summary>
            /// <param name="actionUrls"></param>
 
            jQuery.extend(project.ActionUrls, actionUrls);
 
            //上傳檔案事件處理
            current.UploadEventHandler();
        },
 
        UploadEventHandler: function () {
            /// <summary>
            /// 上傳匯入資料
            /// </summary>
 
            $("#UploadForm").ajaxForm({
                iframe: true,
                dataType: "json",
                success: function (result) {
                    $("#UploadForm").resetForm();
                    if (!result.Result) {
                        project.AlertErrorMessage("錯誤", result.Msg);
                    }
                    else {
                        $('#ResultContent').html(result.Msg);
                        project.ShowMessageCallback("訊息", "檔案上傳完成, 點選「確認」後開始進行資料匯入", function () {
                            current.ImportData(result.Msg);
                        });
                    }
                },
                error: function (xhr, textStatus, errorThrown) {
                    $("#UploadForm").resetForm();
                    project.AlertErrorMessage("錯誤", "檔案上傳錯誤");
                }
            });
        },
 
        ImportData: function (savedFileName) {
            /// <summary>
            /// 資料匯入
            /// </summary>
            /// <param name="mainID"></param>
 
            $.ajax({
                type: 'post',
                url: project.ActionUrls.Import,
                data: { savedFileName: savedFileName },
                async: false,
                cache: false,
                dataType: 'json',
                success: function (data) {
                    if (data.Msg) {
                        project.AlertErrorMessage("錯誤", data.Msg);
                        $('#UploadModal').modal('hide');
                    }
                    else {
                        project.ShowMessageCallback("訊息", "匯入完成", function () {
                            $('#UploadModal').modal('hide');
                            window.location.reload();
                        });
                    }
                },
                error: function () {
                    project.AlertErrorMessage("錯誤", "資料匯入發生錯誤");
                    $('#UploadModal').modal('hide');
                }
            });
        }
 
    });
})
(project);

 

執行結果

上傳一個內容資料有錯誤的 Excel 檔案

image

將檢查出錯誤的訊息給顯示出來

image

再來上傳一個資料正確的 Excel 檔案,上傳完成並且匯入完成

image

在顯示「匯入完成」的訊息視窗裡按下「確認」後就會重新整理頁面,就會把剛才所上傳並且匯入的資料顯示出來

image

 

就這樣,我們完成了一個簡單的 Excel 上傳與匯入的功能。既然已經完成 Excel 上傳與匯入功能之後,那麼也應該要再做一個匯出 Excel 的功能吧~

是的,下一篇就來說明怎麼簡單做 Excel 匯出的功能。

 

以上

21 則留言:

  1. 不好意思 我是新手

    想請問您 您的範例做檢查資料部分是以列資料為檢查方式

    請問試做匯出資料的欄位是否有缺 該如何進行檢查

    謝謝

    回覆刪除
    回覆
    1. 匯出?這篇是講匯入的資料檢查,如果提問是在下一篇資料匯出裡會比較恰當(因為有時後面的討論會偏離文章的主題)
      其實我看不太懂你的問題,你是要問「在匯出資料的時候,要如何檢查資料是否有缺」是嗎?
      資料匯出的檢查就比較單純,匯入的檢查反倒是我認為最難以掌握的,因為匯入的 Excel 既使是前有規定每個欄位的格式與內容,
      但使用者還是有辦法跳脫這些制訂好的規範,然後在 Excel 裡面填入各式各樣的資料。
      .
      相反的,要把系統的資料匯出 Excel,原本在系統內的資料就會做好整理,所以在匯出的時候就比較不會有例外的情況發生,至於要怎麼檢查匯出資料的欄位是否有缺,前面說過,原本在系統內的資料是我們可以掌握的,某個欄位的資料有缺,這要看這個欄位在設計的時候本來就是 Nullable 的格式還是有些什麼樣的狀況而造成該欄位資料為空的情況。
      .
      所以在資料匯出 Excel 前,就是將資料給取出並且整理成要匯出的內容與格式,這個時候就可以檢查,我不曉得你有沒有看過下一篇文章「ASP.NET MVC 匯出 Excel 簡單做 - 使用 ClosedXML (http://kevintsengtw.blogspot.tw/2014/06/aspnet-mvc-excel-closedxml.html#.VES-vvmUepQ)」,建議你看過文章並且把我公開在 GitHub 的原始碼下載後進行瞭解,原始碼的專案都是可以正常執行的,詳細的看過文章與原始碼再做思考,我想你就會知道怎麼做了。

      刪除
    2. 不好意思 打錯 是匯入要做的資料檢查

      另外 我還想請問一下 controlle中 var fileName = string.Concat(Server.MapPath(fileSavedPath), "/", savedFileName)
      的【"/"】用途為何?

      不好意思

      刪除
    3. 基本上那是個誤加的一個符號,沒必要加,可以移除,加了也沒有對程式讀取檔案造成影響,總之那是個當初寫程式時的一個疏漏與多餘的內容。

      刪除
  2. 有關匯入資料的欄位檢查,這邊我給你一些思考方向,
    我這篇所做的 Excel 匯入是先將檔案上傳,上傳成功之後再將 Excel 內的資料給讀出來,
    這邊我所使用的是 LINQ To Excel 來處理這個讀取 Excel 檔案的工作,
    而 Excel 資料要做匯入,早已經決定好會有哪些的欄位,欄位的名稱與格式、型別,
    所以我就有使用 Linq to Excel 的 AddMapping 的方法,逐一地把 Excel 欄位給對映到物件,
    而 Excel 裡面有很多筆資料,每一個 row 都是一筆資料,如果其中一筆資料有空白,或是有缺,其實要看那個 cell 的那一列是什麼欄位,
    那一個 cell 有缺的話,之前做 Mapping 的時候就會將對映後的屬性依照型別的預設值來給,
    不會說那一格 cell 有空缺,下一格 cell 就會頂替前一格的 cell 而變成那一個 cell 的欄位,
    所以你看我程式裡「ImportDataHelper 類別的 CheckImportData 方法」,中間有一個 foreach 迴圈就是在做每個 row 資料的欄位檢查,
    其中就可以看到我有特別針對「縣市名稱」「鄉鎮市區名稱」來做有無資料的判斷,如果為空白就表示該 row 的資料有錯誤,
    那麼就會輸出錯誤訊息,錯誤訊息是會說明什麼欄位有什麼錯誤。
    上面是我在這一篇文章裡對於匯入資料的檢查。
    .
    如果你要在針對 row 的資料檢查前,想要先檢查匯入的 excel 裡有沒有缺少哪一個 column 的話,
    其實這也很簡單,首先就是判定匯入的 Excel 的 columns 總數,如果有缺少的話,那麼就是逐一檢查是那一個 column 有缺少,
    以 Linq To Excel 來說,
    使用「excelFile.GetColumnNames("WorkSheet name")」就可以知道匯入的 Excel 檔案的指定 WorkSheet 裡有哪些欄位,
    既然知道匯入的 Excel 有那些欄位的話,那麼就可以判斷欄位欄位有沒有缺少,如果有缺少,也可以得知是少了哪一個欄位。
    .
    每一個處理 Excel 匯入與讀取 Excel 的套件功能與使用方式都不盡相同,但至少取得每個欄位的名稱與讀取每一列資料的功能都會有,
    就看我們開發人員如何去使用。

    回覆刪除
  3. 您好:請問能否在檔案寫入FileUpload前 就先讀檔做檔案內容的驗證,以免FileUpload資料夾存入過多不必要的檔案

    回覆刪除
    回覆
    1. 你好,其實可以做得到,我在上一篇「ASP.NET MVC 匯入 Excel 簡單做 - Part.1 檔案上傳」裡面對於上傳檔案的檢查只有針對副檔名是否為 xls 或 xlsx,但如果是拿不是真的 Excel 檔案然後修改副檔名上傳的話,其實在這個只有檢查副檔名的簡陋機制下是可以輕鬆過關的,也造成了一個漏洞,如果要避免的話,可以在網路上去尋找相關的檔案檢查機制的方法。
      而我雖然在這個範例裡沒有做到這一點,但是可以利用 LinqToExcel 的讀取 Excel 內容的功能來做這部分的功能,可以用個 try...catch 的把使用 LinqToExcel 讀取上傳的檔案給包起來,假如沒有發生錯誤就表示該檔案為真的 Excel,但如果有發生 Exception 的話,就可以判定該檔案並不是真的 Excel。
      這是我所想到比較簡易的作法,如果需要比要嚴謹的方式,就要再去網路上找找看囉。

      刪除
    2. 謝謝,對不起想請教一下,是否要抓取檔案上傳前的存放路徑及檔名,才能在上傳前先進行檔案內容的驗證

      刪除
    3. 首先你要釐清網路諮詢安全的問題,倘若有一個網站在你未上傳任何檔案或是我們沒有做任何事情之前,網站就會對我們使用者電腦的檔案進行讀取的動作,這不就是一種駭客的行為嗎?
      基本上,在預設的狀態下,網站是不可以擅自讀取使用者電腦裡的檔案(除了瀏覽器裡的相關必要資訊與檔案),所以針對你所提問的問題,在預設情況是不能這麼做的,網站無法在未上傳檔案前就去讀取使用者電腦裡的檔案,就算是有提供檔案的存放路徑與檔名也是一樣不可以。
      這是基本的資安問題。
      也許你會想說,那為什麼一些所謂的線上掃毒或是一些服務就可以掃描甚至讀取使用者所指定的檔案呢?
      有個前提,那就是這些服務在使用者第一次使用時就會先請使用者下載程式並且安裝,而且這是要使用者同意才行,安裝了服務的 client 端程式之後,這些服務的網站才有辦法透過 client 端程式去讀取使用者電腦內的檔案。
      .
      以一個單純的 Excel 檔案上傳與匯入的功能(就算 Excel 內容有多麼的複雜與龐雜,與上述所提及的服務相比,Excel 上傳匯入的功能真的是比較單純),我想沒有必要為了這樣一個上傳匯入的功能就去寫一個 client 端程式然後叫使用者去安裝,太耗費成本而且也有太多資安問題需要考慮,而且每一種瀏覽器就有必要去寫專門的 client 端程式(你可以想想,現在很多銀行的網站不再像以往只顯現於 IE 瀏覽器使用,現在大多數銀行網站的線上服務都有支援三種主流瀏覽器,每種瀏覽器都有其專門的 client 端程式),瀏覽器會改版,一旦改版後,原本執行正常的 client 端程式就有可能無法正常運作,這時候必須要去做更新修改,然後為了這樣一個單純的功能就必須要耗費這麼多的成本。
      不是說不能做,也不是說無法達成值這樣的需求,只是要考慮到是否符合成本以及資安的問題。

      刪除
    4. 更正
      「網路諮詢安全」應為「網路資訊安全」

      刪除
  4. 我想請問一下, 當調用 $("#UploadForm").resetForm(); 時, Chrome 為什麼會出現 Uncaught RangeError: Maximum call stack size exceeded ?

    回覆刪除
    回覆
    1. 找到原因了, 應該是 $("#UploadForm").validate().resetForm()

      刪除
  5. 請問→ImportDataHelper 這個應該要放在那個路徑下?是建一個App_Code ?還是?我是用MVC5 ,謝謝你的回應

    回覆刪除
    回覆
    1. 可以對應命名空間來看釋放在那一個目錄下,或是你自己決定要放在那裡,
      寫程式,就靈活一點,沒有那麼死板,可以自己決定。
      Source Code:
      https://github.com/kevintsengtw/MVC-Excel-Import-Export

      刪除
  6. 請問:1.我嚐試改成匯入另一個table,其中我有改三個js中的project.XXXX.js,另也調整controller、helper檔,在測試error的excel檔時有
    成功檢測,但完整的excel檔卻無法成功匯入,不知是哪裏出問題?
    2.測試時發現office2010版的excel無法成功檢測,降版為97~2003版就ok了,請問有解決excel版本限制的方法嗎?

    回覆刪除
    回覆
    1. 忘了說明錯誤訊息 => 資料匯入發生錯誤。

      刪除
    2. 完整的 Excel 檔案無法匯入成功,你所提供的訊息也是讓我不知道哪裡出問題?
      可能的方向...... 匯入的資料型別沒有與 Table 的欄位型別一致、欄位長度或是欄位名稱錯誤都有可能,因為你所提供的訊息真的讓我做判斷,所以無法給你明確的答覆。
      解決 Excel 版本的問題,那就是由我們開發者提供一個公版的 Excel 範本檔案,而這一個 Excel 範本檔案最後也是由程式所產生的,讓使用者所匯入的 Excel 檔案都是使用同樣一個來源的範本,這可以解決版本的問題,我之前也碰到類似的狀況,處理的做法就是我這邊用程式去匯出一個 Excel 檔案,然後讓使用者用,當使用者匯入有問題時就問他們是否使用範本檔,由我們開發端來做一個制約。
      如果使用端無法接受這樣的做法時,那我也沒辦法,如果使用端的 Excel 檔案版本無法一致,就無法解決根本的問題。

      刪除
    3. 當匯入出現問題然後在頁面的 Alert 裡看到「資料匯入發生錯誤」這個訊息,可以去追後端 Import 這個 Action 方法的 Exception 內容,或者是直接去追 ImportDataHelper 的 SaveImportData() 所拋出的 Excpetion 內容。
      因為你有說有更改過 ImportDataHelper 的程式內容,這部分我無法知道你改了什麼,所以怎麼出現 Exception 的原因就要由你去找出來。

      刪除
    4. 不好意思,因為我是初學者,想再與您討論:1.匯入的資料型別-這在欄位對應AddMapping上是否需要與table全部的column都mapping上?還是可以挑選想要匯入的column? 2.Excel 第一列的column name是否需要與model內設定的相同? 還是可以自訂,然後在AddMapping這裏Map上就可以了? 另外我在model上table的1個key值設定上有增加[Column("StoreID")], 我不了解這會不會有關係? 3.關於欄位長度,我在您的範本上不了解哪一段與欄位長度有關? 但我的Excel內 儲存格格式 都是 "通用格式",而關於bool的值我是用 0/1 放上去。 4.js 檔- project.xxx.js 除了檔名、內文的 app.xxxxx 也有改為table name了。
      5.hepler-SaveImportData,裏面只改了IEnumerable import+(table-xxx),db也改了context了…我也不知道Remove是單純執行delete動作還是這跟models內其它的table有什麼關係?
      不過,因為我會試著透過自動匯出的來源檔再測試匯入,謝謝您的指導。

      刪除
    5. 那個... 往左看... 有看到「詢問與建議」吧
      將你的問題重新整理一遍,將你實作的狀況與遇到的狀況,還有你曾經做過哪些嘗試與修改,把這些情況提供給我,我才比較好回答你,這邊的留言板不適合做深度的討論,所以請改用「詢問與建議」的功能(可以夾帶附件檔)。

      刪除
    6. https://www.microsoft.com/zh-tw/download/confirmation.aspx?id=23734
      Server 要先安裝這個

      刪除

提醒

千萬不要使用 Google Talk (Hangouts) 或 Facebook 及時通訊與我聯繫、提問,因為會掉訊息甚至我是過了好幾天之後才發現到你曾經傳給我訊息過,請多多使用「詢問與建議」(在左邊,就在左邊),另外比較深入的問題討論,或是有牽涉到你實作程式碼的內容,不適合在留言板裡留言討論,請務必使用「詢問與建議」功能(可以夾帶檔案),謝謝。