---
tags: 國興實習
---
# 批次 + 檔案匯入

### 前端html

#### 自訂匯入按鈕+匯入介面視窗設計

@section RightNavBtn{
    <!--------自訂匯入按鈕------------------------>
    <div class="btn-section col-md-1 col-sm-2 col-xs-2" ng-show="!isMobile">
        <button type="button" id="btnimport" class="btn btn-main" ng-disabled="status == '2'" data-toggle="modal" data-target="#importModal"><i class="glyphicon glyphicon-floppy-open"></i>匯入</button>
    </div>
    <!--------匯入excel-------------------------------------------------->
    <div class="modal fade" id="importModal" tabindex="-1" role="dialog" aria-labelledby="importModalLabel" aria-hidden="true">
        <div class="modal-dialog" role="document">
            <div class="modal-content">
                <div class="modal-header">
                    <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
                    <h5 class="modal-title" id="exampleModalLabel">選擇檔案(限定副檔名.xlsx)</h5>
                </div>

                <div class="modal-body">
                    <div class="form-group">
                        <label class="col-sm-3 control-label required-label" style="text-align:right;">檔名</label>
                        <div class="input-group col-xs-6 col-xs-offset-3">
                            <label class="input-group-btn">
                                <span class="btn btn-primary">
                                    選擇檔案<input name="stxtD_FILENAME1" type="file" file-model="FILE_NAME" style="display: none;" class="form-control input-file">
                                </span> @*//type="file"：輸入檔案模式*@
                            </label>
                            <input name="show_FILE_NAME" type="text" class="form-control" readonly>
                        </div>
                        <div class="input-group col-xs-6 col-xs-offset-3">
                            <a href="~/zTemplate/GOOGRP040.xlsx">匯入範例下載</a>
                        </div>
                    </div>

                </div>
                <div class="form-group">
                    <div class="modal-footer">
                        <button type="button" class="btn btn-primary" ng-click="doMasterUpload()"><span class="glyphicon glyphicon-ok"></span>確定</button>
                        <button type="button" class="btn btn-default" ng-click="doMasterUploadCancel()" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span>取消</button>
                    </div>
                </div>
            </div>
        </div>
    </div>
}

:::info
* **直接用的話就更改`<a href="~/zTemplate/GOOGRP040.xlsx">匯入範例下載</a>`的路徑就好**
:::

### js

$scope.$on("masteruploading", function (e, a_arg) {
    if (isEmpty($scope.FILE_NAME)) {
        swal("請選擇檔案以供上傳!!", "", "error");
        a_arg.cancel = true;
    }
    var FILE_EXT = $scope.FILE_NAME.name.split('.');
    if (FILE_EXT[FILE_EXT.length - 1] != "xlsx") {
        swal("副檔名非xlsx，請檢查檔案!!", "", "error");
        a_arg.cancel = true;
    }
    //清空回傳序號
    $scope.searchData.FILE_NAME = $scope.FILE_NAME.name.split(".xlsx")[0];
    $scope.searchData.FILE_EXT = $scope.FILE_NAME.name.split('.')[FILE_EXT.length - 1];
    a_arg.files.push($scope.FILE_NAME);
})

//--取得[上傳檔案]結果 uploaded --------------------------------------------------------
$scope.$on("masteruploaded", function (e, a_arg) {
    //取得[上傳檔案]名稱
    //a_arg.message = "上傳作業完成！" + a_arg.files[0];
    a_arg.isShowMessage = false;//不顯示成功訊息
    $scope.searchData.UPLOAD_NAME = a_arg.files[0];

    //$scope.searchData.FILE_EXT = $scope.lbFILEEXT;
    $scope.doImpMasterXLS();//執行完upload後開始執行讀取檔案
    //將名稱清空，避免重複上傳
})
//--匯入excel結果 impMasterXLSed ----------------------------------------
$scope.$on("impMasterXLSed", function (e, a_arg) {
    a_arg.isShowMessage = false;
    //swal("已成功匯入！" + a_arg.result.msg, "", "success")
    if (a_arg.result["result"] == "0") {
        swal(a_arg.result["msg"], "", "success").then(function () {
            $("#importModal").modal("hide");
            $scope.doMasterUploadCancel();
            $scope.onBtnSearchClick();
        });
    }
    else {
        swal(a_arg.result["msg"], "", "warning");
    }

});

$scope.doMasterUploadCancel = function () {
    if (!isEmpty($scope.FILE_NAME)) {
        $("input[name='show_FILE_NAME']").val('');
        $("input[type='file']").val("");
    }
}

$(function () { //秀出檔案名
    $("input[type='file']").bind("change", function (event) {
        if (event.target.files.length > 0) {
            var file = event.target.files[0];
            $("input[name='show_FILE_NAME']").val(file.name);
        }
        else {
            $("input[name='show_FILE_NAME']").val('');
        }
    });
});

:::info
* **直接用的話不用更改**
:::

### 後端

[System.Web.Http.HttpPost]
        public IHttpActionResult ImpMasterXls(SearchClass a_Search)
        {
            string userId = UserInfo.getUserInfo(HttpContext.Current.Session).PASS_NO; //取得使用者ID
            string userIp = HttpContext.Current.Request.UserHostAddress; //取得使用者IP
            DateTime now = DateTime.Now; //取得目前日期時間

            #region 上傳檔案
            string l_uploadname = a_Search.UPLOAD_NAME;//上傳檔案產生的檔名
            string l_filename = a_Search.FILE_NAME;//檔案名稱
            string l_fileext = a_Search.FILE_EXT;//副檔名
            string l_root = HttpContext.Current.Server.MapPath("~/zTmpUp/");//原始路徑
            string l_newpath = HttpContext.Current.Server.MapPath("~/FILES/GOO/");//要存入的實際路徑
            string l_newfilepath = l_newpath + l_filename + "." + l_fileext;

            //若無此路徑，則建立
            if (!System.IO.Directory.Exists(l_newpath))
            {
                System.IO.Directory.CreateDirectory(l_newpath);
            }
            //若已存在檔案，則刪除
            if (System.IO.File.Exists(l_newfilepath))
            {
                try
                {
                    System.IO.File.Delete(l_newfilepath);
                }
                catch (System.IO.IOException e)
                {
                    Console.WriteLine(e.Message);
                }
            }
            //移動檔案並補上副檔名
            System.IO.File.Move(l_root + l_uploadname, l_newfilepath);
            // 這裡開始匯入處理

            #endregion

            DBController l_dbc = new DBController("UseDB"); //與資料庫連線
            DataSet ds = new DataSet(); 
            Hashtable l_ht = new Hashtable(); //存取資料
            StringBuilder l_SQL = new StringBuilder(); //SQL指令

            //回傳匯入結果
            Dictionary<string, object> l_dic = new Dictionary<string, object>();

            //顯示用
            int totalrow = 0;

            bool l_Succ = false;
            string l_Msg = "";
            string ErrMsg = "";//紀錄錯誤訊息

            //批次存檔用
            DeltaMD deltamd = new DeltaMD();
            deltamd.details = new DeltaDetail[] { };
            deltamd.masterRows = new DeltaRow[] { };
            deltamd.masterRow = new DeltaRow();
            List<DeltaRow> listDetail_unit = new List<DeltaRow>();

            string l_data_na = '\\' + l_filename + "." + l_fileext;

            using (FileStream fs = new FileStream(l_newpath + l_data_na, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                //成功開啟excel
                //載入Excel檔案
                using (ExcelPackage ep = new ExcelPackage(fs))
                {
                    ExcelWorksheet sheet = ep.Workbook.Worksheets[1];//取得Sheet1
                    int startRowNumber = sheet.Dimension.Start.Row;//起始列編號，從1算起
                    int endRowNumber = sheet.Dimension.End.Row;//結束列編號，從1算起
                    int startColumn = sheet.Dimension.Start.Column;//開始欄編號，從1算起
                    int endColumn = sheet.Dimension.End.Column;//結束欄編號，從1算起
                    startRowNumber = 2;//第一列為標題
                    totalrow = endRowNumber;

                    //讀取資料  
                    #region  判斷標題欄位位置
                    string col_na = ""; //供switch case尋找欄位名稱
                    int CUST_DEPT = -1; //紀錄相關欄位欄號之變數
                    int CUST_NO = -1;
                    int UNIT_NO = -1;
                    int UNIT_NM = -1;
                    int EMP_NO = -1;
                    int EMP_TEL = -1;

                    #endregion

                    #region  判斷標題欄位位置
                    for (int i = 1; i >= startColumn && i <= endColumn; i++)
                    {
                        if (sheet.Cells[1, i].Value != null) //取得標題列，且不為空值
                        {
                            col_na = (sheet.Cells[1, i].Value).ToString().Replace(" ", "").Trim(); //Replace及Trim都是在做去除空白的處理動作
                        }
                        else
                        {
                            col_na = ""; //將空值定義為空字串
                        }

                        switch (col_na) //辨別標題欄位並找到其"欄"位的欄號
                        {
                            case "部門代號":
                                CUST_DEPT = i;
                                break;
                            case "客戶代號":
                                CUST_NO = i;
                                break;
                            case "單位代碼":
                                UNIT_NO = i;
                                break;
                            case "單位名稱":
                                UNIT_NM = i;
                                break;
                            case "承辦人":
                                EMP_NO = i;
                                break;
                            case "電話":
                                EMP_TEL = i;
                                break;
                        }
                    }
                    #endregion

                    string cellValue_CUST_DEPT = ""; //用來抓取目前列之各欄資料
                    string cellValue_CUST_NO = "";
                    string cellValue_UNIT_NO = "";
                    string cellValue_UNIT_NM = "";
                    string cellValue_EMP_NO = "";
                    string cellValue_EMP_TEL = "";


                    for (int currentRow = startRowNumber; currentRow <= endRowNumber + 1; currentRow++)
                    {
                        ExcelRange range = sheet.Cells[currentRow, startColumn, currentRow, endColumn];//抓出目前的Excel列
                        if (range.Any(c => !string.IsNullOrEmpty(c.Text)) == false)//這是一個完全空白列(使用者用Delete鍵刪除動作)
                        {
                            continue;//略過此列
                        }

                        Dictionary<string, object> a_row = new Dictionary<string, object>();
                        #region  null判斷及空白清除
                        if (CUST_DEPT != -1)
                        {
                            cellValue_CUST_DEPT = General.nvl(sheet.Cells[currentRow, CUST_DEPT].Value, "").ToString();
                            cellValue_CUST_DEPT = cellValue_CUST_DEPT.Trim(); // 清除左右空白
                        }
                        if (CUST_NO != -1)
                        {
                            cellValue_CUST_NO = General.nvl(sheet.Cells[currentRow, CUST_NO].Value, "").ToString();
                            cellValue_CUST_NO = cellValue_CUST_NO.Trim(); // 清除左右空白
                        }
                        if (UNIT_NO != -1)
                        {
                            cellValue_UNIT_NO = General.nvl(sheet.Cells[currentRow, UNIT_NO].Value, "").ToString();
                            cellValue_UNIT_NO = cellValue_UNIT_NO.Trim(); // 清除左右空白
                        }
                        if (UNIT_NM != -1)
                        {
                            cellValue_UNIT_NM = General.nvl(sheet.Cells[currentRow, UNIT_NM].Value, "").ToString();
                            cellValue_UNIT_NM = cellValue_UNIT_NM.Trim(); // 清除左右空白
                        }
                        if (EMP_NO != -1)
                        {
                            cellValue_EMP_NO = General.nvl(sheet.Cells[currentRow, EMP_NO].Value, "").ToString();
                            cellValue_EMP_NO = cellValue_EMP_NO.Trim(); // 清除左右空白
                        }
                        if (EMP_TEL != -1)
                        {
                            cellValue_EMP_TEL = General.nvl(sheet.Cells[currentRow, EMP_TEL].Value, "").ToString();
                            cellValue_EMP_TEL = cellValue_EMP_TEL.Trim(); // 清除左右空白
                        }
                        #endregion

                        #region 欄位驗證

                        //Regex regex = new Regex(string pattern);
                        //Regex regex = new Regex(string pattern, RegexOptions options);

                        //最多999筆,超過1000要防呆
                        if (currentRow > 1000)
                        {
                            ErrMsg += "匯入筆數最多只可為999筆，超過999筆系統無法儲存<br>";
                            l_Succ = (ErrMsg != "") ? false : true;
                            return doImportOKResult(l_Succ, ErrMsg, l_dic);
                        }

                        //以下依資料庫規格更改if條件，以協助客戶更改資料規格
                        if (cellValue_CUST_DEPT == "")
                        {
                            ErrMsg += "列號：" + currentRow + ",[部門代號]不可空白<br>";
                        }
                        else if (cellValue_CUST_DEPT.Length > 10)
                        {
                            ErrMsg += "列號：" + currentRow + ",[部門代號]不可超過20碼<br>";
                        }
                        if (cellValue_CUST_NO == "")
                        {
                            ErrMsg += "列號：" + currentRow + ",[客戶代號]不可空白<br>";
                        }
                        else if (cellValue_CUST_NO.Length > 20)
                        {
                            ErrMsg += "列號：" + currentRow + ",[客戶代號]不可超過20碼<br>";
                        }

                        if (cellValue_UNIT_NO == "")
                        {
                            ErrMsg += "列號：" + currentRow + ",[單位代碼]不可空白<br>";
                        }
                        else if (cellValue_UNIT_NO.Length > 20)
                        {
                            ErrMsg += "列號：" + currentRow + ",[單位代碼]不可超過20碼<br>";
                        }

                        if (cellValue_UNIT_NM == "")
                        {
                            ErrMsg += "列號：" + currentRow + ",[單位名稱]不可空白<br>";
                        }
                        else if (cellValue_UNIT_NM.Length > 40)
                        {
                            ErrMsg += "列號：" + currentRow + ",[單位名稱]不可超過40碼<br>";
                        }

                        if (cellValue_EMP_NO == "")
                        {
                            ErrMsg += "列號：" + currentRow + ",[承辦人]不可空白<br>";
                        }
                        else if (cellValue_EMP_NO.Length > 20)
                        {
                            ErrMsg += "列號：" + currentRow + ",[承辦人]不可超過20碼<br>";
                        }

                        if (cellValue_EMP_TEL == "")
                        {
                            ErrMsg += "列號：" + currentRow + ",[電話]不可空白<br>";
                        }
                        else if (!Regex.Match(cellValue_EMP_TEL, "^[0-9]").Success) //正規表示法判斷是否只輸入數字
                        {
                            ErrMsg += "列號：" + currentRow + ",[電話]非數字<br>";
                        }
                        else if (cellValue_EMP_TEL.Length > 20)
                        {
                            ErrMsg += "列號：" + currentRow + ",[電話]不可超過20碼<br>";
                        }

                        #endregion

                        #region 存檔用 欄位
                        DeltaRow temp_unit = new DeltaRow();
                        temp_unit.newRow = new Dictionary<string, object>();
                        temp_unit.newRow.Add("CUST_DEPT", cellValue_CUST_DEPT);
                        temp_unit.newRow.Add("CUST_NO", cellValue_CUST_NO);
                        temp_unit.newRow.Add("UNIT_NO", cellValue_UNIT_NO);
                        temp_unit.newRow.Add("NAME", cellValue_UNIT_NM);
                        temp_unit.newRow.Add("EMP_NO", cellValue_EMP_NO);
                        temp_unit.newRow.Add("TEL", "0" + cellValue_EMP_TEL);
                        temp_unit.newRow.Add("CRT_NO", userId);
                        temp_unit.newRow.Add("CRT_DATE", now);
                        temp_unit.newRow.Add("CRT_IP", userIp);
                        temp_unit.newRow.Add("UPD_NO", userId);
                        temp_unit.newRow.Add("UPD_DATE", now);
                        temp_unit.newRow.Add("UPD_IP", userIp);

                        listDetail_unit.Add(temp_unit);
                        #endregion
                    }

                    l_Msg = ErrMsg;
                    l_Succ = string.IsNullOrEmpty(ErrMsg) ? true : false;
                }
            }

            //批次存檔
            if (ErrMsg == "")
            {
                List<Hashtable> batchHt = new List<Hashtable>();
                List<string> batchSQL = new List<string>();
                int count = 0;

                try
                {
                    if (listDetail_unit.Count > 0)
                    {
                        string _sql = @"INSERT INTO QA_CUST_UNIT (CUST_DEPT, CUST_NO, UNIT_NO, NAME, EMP_NO, TEL, CRT_NO, CRT_DATE, CRT_IP, UPD_NO, UPD_DATE, UPD_IP) 
                                    VALUES " + "({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11})"; //這裡的VALUES對應到【存檔用 欄位】輸入的順序，要特別注意

                        l_ht.Clear();

                        foreach (DeltaRow row in listDetail_unit)
                        {
                            #region 設定帶入SQL值 
                            string pCUST_DEPT = "@CUST_DEPT_" + count;  //+count是為了避免批次時l_SQL的@參數重複
                            string pCUST_NO = "@CUST_NO_" + count;
                            string pUNIT_NO = "@UNIT_NO_" + count;
                            string pNAME = "@NAME_" + count;
                            string pEMP_NO = "@EMP_NO_" + count;
                            string pTEL = "@TEL_" + count;
                            string pCRT_NO = "@CRT_NO_" + count;
                            string pCRT_DATE = "@CRT_DATE_" + count;
                            string pCRT_IP = "@CRT_IP_" + count;
                            string pUPD_NO = "@UPD_NO_" + count;
                            string pUPD_DATE = "@UPD_DATE_" + count;
                            string pUPD_IP = "@UPD_IP_" + count;
                            #endregion

                            l_SQL.AppendLine(string.Format(_sql, pCUST_DEPT, pCUST_NO, pUNIT_NO, pNAME, pEMP_NO, pTEL, pCRT_NO, pCRT_DATE, pCRT_IP, pUPD_NO, pUPD_DATE, pUPD_IP));

                            #region 設定hashtable
                            l_ht.Add(pCUST_DEPT, new StructureSQLParameter(row.newRow["CUST_DEPT"], SqlDbType.NVarChar));
                            l_ht.Add(pCUST_NO, new StructureSQLParameter(row.newRow["CUST_NO"], SqlDbType.NVarChar));
                            l_ht.Add(pUNIT_NO, new StructureSQLParameter(row.newRow["UNIT_NO"], SqlDbType.NVarChar));
                            l_ht.Add(pNAME, new StructureSQLParameter(row.newRow["NAME"], SqlDbType.NVarChar));
                            l_ht.Add(pEMP_NO, new StructureSQLParameter(row.newRow["EMP_NO"], SqlDbType.NVarChar));
                            l_ht.Add(pTEL, new StructureSQLParameter(row.newRow["TEL"], SqlDbType.NVarChar));
                            l_ht.Add(pCRT_NO, new StructureSQLParameter(row.newRow["CRT_NO"], SqlDbType.NVarChar));
                            l_ht.Add(pCRT_DATE, new StructureSQLParameter(row.newRow["CRT_DATE"], SqlDbType.DateTime));
                            l_ht.Add(pCRT_IP, new StructureSQLParameter(row.newRow["CRT_IP"], SqlDbType.NVarChar));
                            l_ht.Add(pUPD_NO, new StructureSQLParameter(row.newRow["UPD_NO"], SqlDbType.NVarChar));
                            l_ht.Add(pUPD_DATE, new StructureSQLParameter(row.newRow["UPD_DATE"], SqlDbType.DateTime));
                            l_ht.Add(pUPD_IP, new StructureSQLParameter(row.newRow["UPD_IP"], SqlDbType.NVarChar));
                            #endregion

                            //避免參數過多造成例外錯誤發生
                            if (l_ht.Count > 1800)
                            {
                                //加入批次執行List
                                batchHt.Add(new Hashtable(l_ht));
                                batchSQL.Add(_sql.ToString());
                                //清空此階段的Hashtable與StringBuilder
                                l_ht.Clear();
                                l_SQL.Clear();
                            }
                            count++;
                        }
                        //加入最後一次
                        if (l_ht.Count > 0)
                        {
                            batchHt.Add(new Hashtable(l_ht));
                            batchSQL.Add(l_SQL.ToString());
                            l_ht.Clear();
                            l_SQL.Clear();
                        }

                        //------批次執行List SQL------

                        DbConnection conn = l_dbc.NewConnection();
                        DbTransaction trans = conn.BeginTransaction();
                        string message = "";

                        try
                        {
                            for (var i = 0; i < batchSQL.Count; i++)
                            {
                                l_dbc.DbExecuteNonQuery(batchSQL[i], batchHt[i], conn, trans);
                            }
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            message = ex.ToString();
                        }
                        finally
                        {
                            trans.Dispose();
                            conn.Close();
                        }

                        if (l_Succ)
                        {
                            if (message == "")
                            {
                                l_dic["result"] = "0";
                                l_dic["msg"] = "共匯入" + listDetail_unit.Count + "筆資料";
                                return new OkNegotiatedContentResult<Dictionary<string, object>>(l_dic, this);
                            }
                            else
                            {
                                l_dic["result"] = "1";
                                l_dic["msg"] = message;
                                return new OkNegotiatedContentResult<Dictionary<string, object>>(l_dic, this);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    l_dic["result"] = "1";
                    l_dic["msg"] = ex.Message;
                    return new OkNegotiatedContentResult<Dictionary<string, object>>(l_dic, this);
                }
            }

            return doImportOKResult(l_Succ, l_Msg, l_dic);
        }

:::info
* **依註解慢慢更改欄位（多利用Ctrl+H）**
* **批次存檔就只是把SQL指令全串一起後再一次發送**
* **UserInfo右鍵查看各變數定義，依需求使用**
:::