Beispiel #1
0
        private void sbOpenFile_Click(object sender, EventArgs e)
        {
            // 開檔, Parsing 差異料清單
            OpenFileDialog dialog = new OpenFileDialog();

            dialog.Title  = "Open an Excel File";
            dialog.Filter = "Excel Files|*.xls;*.xlsx";
            // If the file name is not an empty string open it for saving.

            string filePathAndName = string.Empty;

            if (dialog.ShowDialog() == DialogResult.OK && dialog.FileName != null)
            {
                filePathAndName = dialog.FileName;
            }
            else
            {
                return;
            }

            // 讀取檔案內容, 並判斷是否有不合法的料號在內
            SplashScreenManager.ShowDefaultWaitForm();

            // 清空原始 treelist元件
            //clearAllCacheData();
            // initial hash tables

            bool      isSuccess = false;
            Workbook  workbook  = new Workbook();
            ArrayList itemList  = new ArrayList();
            DataTable excelToDt = null;

            // 讀取xlsx or xls
            string extensionName = Path.GetExtension(filePathAndName);

            using (FileStream stream = new FileStream(filePathAndName, FileMode.Open))
            {
                if (".xlsx".Equals(extensionName))
                {
                    isSuccess = workbook.LoadDocument(stream, DocumentFormat.Xlsx);
                }
                else
                {
                    isSuccess = workbook.LoadDocument(stream, DocumentFormat.Xls);
                }
            }

            if (isSuccess == false)
            {
                MessageBox.Show("請確認EXCEL檔案是否已解密");
                return;
            }

            // 判斷Sheet只能有一個, 等要release再打開
            if (workbook.Sheets.Count != 1)
            {
                MessageBox.Show(string.Format("請確認EXCEL檔案內只有一個sheet{0}該檔現有個sheets : {1}", Environment.NewLine, workbook.Sheets.Count));
                return;
            }

            // 取得sheet 內容
            Worksheet sheet = workbook.Worksheets[workbook.Sheets.Count - 1];
            Range     range = sheet.GetUsedRange();


            // 取得欄位標題資訊
            int j = 4;

            string[] excelHeader = new string[13];
            for (int i = 0; i < excelHeader.Length; i++)
            {
                excelHeader[i] = sheet.GetCellValue(i, j).ToString();
            }

            // 定義column
            excelToDt = CustomStructure.cloneExcelErpVendorMailerDtColumn();
            for (int i = 1; i < range.RowCount; i++)
            {
                j = 0;
                DataRow dr = excelToDt.NewRow();
                dr["MachineID"]       = sheet.GetCellValue(j, i);
                dr["ItemNumber"]      = sheet.GetCellValue(++j, i);
                dr["ItemName"]        = sheet.GetCellValue(++j, i);
                dr["ItemSpec"]        = sheet.GetCellValue(++j, i);
                dr["Amount"]          = sheet.GetCellValue(++j, i);
                dr["VendorID"]        = sheet.GetCellValue(++j, i);
                dr["VendorShortName"] = sheet.GetCellValue(++j, i);
                dr["UnitPrice"]       = sheet.GetCellValue(++j, i);
                dr["Price"]           = sheet.GetCellValue(++j, i);
                dr["Discount"]        = sheet.GetCellValue(++j, i);
                dr["Remark"]          = sheet.GetCellValue(++j, i);
                excelToDt.Rows.Add(dr);
            }

            // 取得Distinct的Vendor清單

            var distinctRows = (from DataRow dRow in excelToDt.Rows
                                select new { col1 = dRow["VendorID"] }).Distinct();

            foreach (var item in distinctRows)
            {
                VendorList.Add(item.col1);
            }

            VendorList.Add(distinctRows);

            // 設定各Sheet 權限
            // 不開放編輯功能, 或隱藏欄位等
            treeList1.DataSource = excelToDt;
            //setColumnsCaption(ref excelHeader, ref treeList1);

            //treeList1.BestFitColumns(false);
            treeList1.OptionsView.AutoWidth    = false;
            treeList1.OptionsBehavior.ReadOnly = true;
            treeList1.OptionsBehavior.Editable = false;

            // 命名每個Pages
            xtraTabControl1.TabPages[0].Text = "EXCEL_Vendor";
            xtraTabControl1.SelectedTabPage  = xtraTabControl1.TabPages[0];

            SplashScreenManager.CloseForm(false);
        }
        private void sbOpenFile_Click(object sender, EventArgs e)
        {
            // 開檔, Parsing 差異料清單
            OpenFileDialog dialog = new OpenFileDialog();

            dialog.Title  = "Open an Excel File";
            dialog.Filter = "Excel Files|*.xls;*.xlsx";
            // If the file name is not an empty string open it for saving.

            string filePathAndName = string.Empty;

            if (dialog.ShowDialog() == DialogResult.OK && dialog.FileName != null)
            {
                filePathAndName = dialog.FileName;
            }
            else
            {
                return;
            }

            // 讀取檔案內容, 並判斷是否有不合法的料號在內
            SplashScreenManager.ShowDefaultWaitForm();

            // 清空原始 treelist元件
            clearAllCacheData();
            // initial hash tables

            bool      isSuccess  = false;
            Workbook  workbook   = new Workbook();
            ArrayList itemList   = new ArrayList();
            DataTable excelToDt  = null;
            DataTable tempItemDt = null;

            // 讀取xlsx or xls
            string extensionName = Path.GetExtension(filePathAndName);

            using (FileStream stream = new FileStream(filePathAndName, FileMode.Open))
            {
                if (".xlsx".Equals(extensionName))
                {
                    isSuccess = workbook.LoadDocument(stream, DocumentFormat.Xlsx);
                }
                else
                {
                    isSuccess = workbook.LoadDocument(stream, DocumentFormat.Xls);
                }
            }

            if (isSuccess == false)
            {
                MessageBox.Show("請確認EXCEL檔案是否已解密");
                return;
            }

            // 判斷Sheet只能有一個, 等要release再打開
            if (workbook.Sheets.Count != 1)
            {
                MessageBox.Show(string.Format("請確認EXCEL檔案內只有一個sheet{0}該檔現有個sheets : {1}", Environment.NewLine, workbook.Sheets.Count));
                return;
            }

            // 取得sheet 內容
            Worksheet sheet = workbook.Worksheets[workbook.Sheets.Count - 1];
            Range     range = sheet.GetUsedRange();


            // 取得欄位標題資訊
            int j = 4;

            string[] excelHeader = new string[13];
            for (int i = 0; i < excelHeader.Length; i++)
            {
                excelHeader[i] = sheet.GetCellValue(i, j).ToString();
            }

            // 定義column
            excelToDt = CustomStructure.cloneExcelPrDtColumn();

            // 取得欄位內容
            // 判斷工號必需要完全相同
            string empNo = string.Empty;

            for (int i = 5; i <= range.RowCount; i++)
            {
                j = 0;
                DataRow dr = excelToDt.NewRow();
                dr["TB043"] = sheet.GetCellValue(j, i);
                dr["TA012"] = sheet.GetCellValue(++j, i);
                if (i == 5)
                {
                    empNo = dr["TA012"].ToString();
                }
                if (empNo.Length == 0)
                {
                    MessageBox.Show("請購人員不允許為空值");
                    return;
                }
                dr["TB004"] = sheet.GetCellValue(++j, i);
                // 判斷品號為空值時, 停止再向下parsing
                if (sheet.GetCellValue(j, i).IsEmpty == true)
                {
                    break;
                }
                // 當判斷品號不為空值時, 加判斷工號是否相同
                // 如不相同, 也不允許再往下執行
                if (empNo.Equals(dr["TA012"].ToString()) == false)
                {
                    MessageBox.Show("該Excel內含2組以上的工號, 不允許匯入作業");
                    return;
                }
                itemList.Add(dr["TB004"]);
                dr["TB005"] = sheet.GetCellValue(++j, i);
                dr["TB006"] = sheet.GetCellValue(++j, i);
                dr["TB009"] = sheet.GetCellValue(++j, i);
                dr["TB008"] = sheet.GetCellValue(++j, i);
                dr["TB011"] = sheet.GetCellValue(++j, i);
                dr["TB010"] = sheet.GetCellValue(++j, i);
                dr["TB012"] = sheet.GetCellValue(++j, i);
                dr["TB029"] = sheet.GetCellValue(++j, i);
                dr["TB201"] = sheet.GetCellValue(++j, i);
                dr["TA202"] = sheet.GetCellValue(++j, i);
                excelToDt.Rows.Add(dr);
            }

            // 判斷料號是否有不存在DB的資料
            // 如果有, 存入cacheillegalItem, 後續顯示訊息判斷使用
            tempItemDt = MvDbDao.checkData_hasIllegalItemListInInvmb((string[])itemList.ToArray(typeof(string)));
            // 依判斷是否有非合法的Item執行後續判斷
            // 即便有不合法的Item, 介面還是要顯示出來, 因為要提供使用者檢查使用
            // 判斷excel轉成data table後的欄位正確性查檢
            // 只有有問題的時候, 才需要取得BackColorList
            bool isIllegal = checkDataValidAndSetBackColor(excelToDt, tempItemDt, ref hashTreeListBackColor[0]);

            if (tempItemDt != null && tempItemDt.Rows.Count >= 1 || isIllegal == true)
            {
                // 判斷excel轉成data table後的欄位正確性查檢
                // 只有有問題的時候, 才需要取得BackColorList
                // checkDataValidAndSetBackColor(dt, tempItemDt, ref hashTreeListBackColor[0]);
                MessageBox.Show("部份料號不存在DB, 或Excel內容有不正確欄位");
            }
            else
            {
                cachePrDt = excelToDt.Copy();
            }
            // 設定各Sheet 權限
            // 不開放編輯功能, 或隱藏欄位等
            treeList1.DataSource = excelToDt;
            setColumnsCaption(ref excelHeader, ref treeList1);

            //treeList1.Columns["MB004"].Visible = false;

            treeList1.BestFitColumns();
            treeList1.OptionsView.AutoWidth    = false;
            treeList1.OptionsBehavior.ReadOnly = true;
            treeList1.OptionsBehavior.Editable = false;

            // 命名每個Pages
            xtraTabControl1.TabPages[0].Text = "EXCEL_PR";
            xtraTabControl1.SelectedTabPage  = xtraTabControl1.TabPages[0];

            SplashScreenManager.CloseForm(false);
        }