linche0859
10/3/2019 - 3:51 AM

匯入Excel

Controller

        [HttpPost]
        public ActionResult UploadCustStores(HttpPostedFileBase file)
        {
            if (file == null || file.ContentLength <= 0)
            {
                TempData["error"] = "請選擇要上傳的檔案";
                return RedirectToAction("CustStoreMgr");
            }
            var stream = file.InputStream;
            var fileName = Path.GetExtension(file.FileName);
            if (fileName != ".xls" && fileName != ".xlsx")
            {
                TempData["error"] = "請選擇Excel文件";
                return RedirectToAction("CustStoreMgr");
            }
            if (fileName.Equals(".xls"))
            {
                var workbook = new HSSFWorkbook(stream);
                TempData["error"] = Service.ImportCustStores(workbook);
            }
            else
            {
                var workbook = new XSSFWorkbook(stream);
                TempData["error"] = Service.ImportCustStores(workbook);
            }
            return RedirectToAction("CustStoreMgr");
        }

Service

xls檔案

        public string ImportCustStores(HSSFWorkbook workbook)
        {
            NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
            var result = string.Empty;
            var table = new DataTable();
            var rows = sheet.GetRowEnumerator();
            // 加入標頭
            for (int i = 0; i < (sheet.GetRow(0).LastCellNum); i++)
            {
                table.Columns.Add(sheet.GetRow(0).Cells[i].ToString());
            }
            while (rows.MoveNext())
            {
                var row = (HSSFRow)rows.Current;
                var dataRow = table.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                    dataRow[i] = cell == null ? string.Empty : cell.ToString();
                }
                table.Rows.Add(dataRow);
            }
            table.Rows.RemoveAt(0);

            ImportToFactBranch(table, out result);

            return string.IsNullOrEmpty(result) ? "匯入成功" : result;
        }

xlsx檔案

            NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
            var rows = sheet.GetRowEnumerator();
            var result = string.Empty;
            var table = new DataTable();

            for (int i = 0; i < (sheet.GetRow(0).LastCellNum); i++)
            {
                table.Columns.Add(sheet.GetRow(0).Cells[i].ToString());
            }
            while (rows.MoveNext())
            {
                var row = (XSSFRow)rows.Current;
                var dataRow = table.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                    dataRow[i] = cell == null ? string.Empty : cell.ToString();
                }
                table.Rows.Add(dataRow);
            }
            table.Rows.RemoveAt(0);

            ImportToFactBranch(table, out result);

            return string.IsNullOrEmpty(result) ? "匯入成功" : result;

新增至資料庫

        private void ImportToFactBranch(DataTable table, out string result)
        {
            FACT_BRANCH_Model factBranch;
            if (table != null && table.Rows.Count != 0)
            {
                // 加入欄位名稱
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    var codFact = table.Rows[i]["客戶代號"].ToString();
                    if (codFact.Length < 7)
                        codFact = Convert.ToInt32(codFact).ToString("0000000");
                    factBranch = new FACT_BRANCH_Model
                    {
                        COD_FACT = codFact,
                        FACT_BRANCH = table.Rows[i]["門店代號"].ToString(),
                        BRANCH_NAME = table.Rows[i]["門店名稱"].ToString(),
                        BRANCH_SHORT_NAME = table.Rows[i]["門店簡稱"].ToString(),
                        TAX_ID_NUMBER = table.Rows[i]["統一編號"].ToString(),
                        MOBILE = table.Rows[i]["行動電話"].ToString(),
                        TEL1 = table.Rows[i]["電話1"].ToString(),
                        TEL2 = table.Rows[i]["電話2"].ToString(),
                        FAX = table.Rows[i]["FAX"].ToString(),
                        BUSINESS_ADDR = table.Rows[i]["門店營業地址"].ToString(),
                        MAIL_ADDR = table.Rows[i]["門店郵寄地址"].ToString(),
                        REG_ADDR = table.Rows[i]["門店登記地址"].ToString(),
                        CONTACT_PERSON = table.Rows[i]["連絡人"].ToString(),
                        NAM_CHECK = table.Rows[i]["支票抬頭"].ToString(),
                        COMPANY_OWNER = table.Rows[i]["負責人"].ToString(),
                        TAX_TYPE = table.Rows[i]["稅別"].ToString(),
                        COD_DOLA = table.Rows[i]["使用幣別"].ToString(),
                        IO_FACT = table.Rows[i]["國內外廠商別"].ToString(),
                        NUM_CUST = table.Rows[i]["企業統一編號識別碼"].ToString(),
                        COD_DPT = table.Rows[i]["對應至內部單位之代碼"].ToString(),
                        COD_IND = table.Rows[i]["廠商類別"].ToString(),
                        FACT_SYS = "11",
                        CHK_PAYM = table.Rows[i]["付款途徑"].ToString(),
                        COD_PAYM = table.Rows[i]["付款條件"].ToString(),
                        DAT_MONBG = table.Rows[i]["起算日"].ToString(),
                        DAT_MONTH = table.Rows[i]["每月結帳日"].ToString(),
                        DAY_PAY = table.Rows[i]["每月付款日"].ToString(),
                        DAT_KEYIN = DateTime.Now.ToString("yyyyMMdd"),
                        EMP_APLY = table.Rows[i]["廠商申請人"].ToString(),
                        EMP_KEYIN = string.Empty,
                        DAT_LAST = DateTime.Now.ToString("yyyyMMdd"),
                        EMP_LAST = string.Empty
                    };
                    result = InsertCustStore(factBranch);
                    if (!string.IsNullOrEmpty(result))
                        break;
                }
            }
            result = string.Empty;
        }