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); }