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