public static bool GetShopDocData(ComboBoxEx Op1Combobox, out Dictionary <DB_TEMain, IList <Com_ShopDoc> > DicShopDocData)
{
DicShopDocData = new Dictionary <DB_TEMain, IList <Com_ShopDoc> >();
try
{
for (int i = 0; i < OutputForm.TEPanel.Rows.Count; i++)
{
if (((bool)OutputForm.TEPanel.GetCell(i, 0).Value) == false || OutputForm.TEPanel.GetCell(i, 1).Value.ToString() != "ShopDoc")
{
continue;
}
Sys_TEExcel teExcelSrNo = session.QueryOver <Sys_TEExcel>()
.Where(x => x.teExcelType == OutputForm.TEPanel.GetCell(i, 1).Value.ToString())
.SingleOrDefault <Sys_TEExcel>();
if (teExcelSrNo == null)
{
continue;
}
Com_TEMain comTEMain = session.QueryOver <Com_TEMain>()
.Where(x => x.comPartOperation == (Com_PartOperation)Op1Combobox.SelectedItem)
.Where(x => x.sysTEExcel == teExcelSrNo)
.Where(x => x.ncGroupName == OutputForm.TEPanel.GetCell(i, 2).Value.ToString())
.SingleOrDefault <Com_TEMain>();
DB_TEMain sDB_TEMain = new DB_TEMain();
IList <Com_ShopDoc> comDimension = session.QueryOver <Com_ShopDoc>()
.Where(x => x.comTEMain == comTEMain)
.List <Com_ShopDoc>();
sDB_TEMain.comTEMain = comTEMain;
sDB_TEMain.excelTemplateFilePath = string.Format(@"{0}\{1}\{2}\{3}\{4}.xls"
, OutputForm.EnvVariables.env
, "TE_Config"
, "Config"
, OutputForm.TEPanel.GetCell(i, 1).Value.ToString()
, OutputForm.TEPanel.GetCell(i, 3).Value.ToString());
sDB_TEMain.ncGroupName = comTEMain.ncGroupName;
sDB_TEMain.factory = OutputForm.TEPanel.GetCell(i, 3).Value.ToString();
sDB_TEMain.partDesc = (session.QueryOver <Com_PEMain>()
.Where(x => x.peSrNo == ((Com_PartOperation)Op1Combobox.SelectedItem).comPEMain.peSrNo)
.SingleOrDefault <Com_PEMain>()).partDes;
DicShopDocData.Add(sDB_TEMain, comDimension);
//sDB_TEMain.comTEMain = comTEMain;
//sDB_TEMain.excelTemplateFilePath = string.Format(@"{0}\{1}.xls", OutputForm.serverTEConfig, OutputForm.TEPanel.GetCell(i, 3).Value.ToString());
//sDB_TEMain.factory = OutputForm.TEPanel.GetCell(i, 3).Value.ToString();
//DicShopDocData.Add(sDB_TEMain, comDimension);
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
return(false);
}
return(true);
}
private void OK_Click(object sender, EventArgs e)
{
try
{
#region (註解中)檢查PC有無Excel在執行
//bool flag = false;
//foreach (var item in Process.GetProcesses())
//{
// if (item.ProcessName == "EXCEL")
// {
// flag = true;
// break;
// }
//}
//if (flag)
//{
// MessageBox.Show("請先關閉所有Excel再重新執行輸出，如沒有EXCEL在執行，請開啟工作管理員關閉背景EXCEL");
// return;
//}
#endregion
bool flag = false;
Dictionary <string, List <Sys_TipTop> > DicToolERP = new Dictionary <string, List <Sys_TipTop> >();
if (productNo.Text != "")
{
if (!File.Exists("D:\\Knife.xls"))
{
MessageBox.Show("TipTop的文件不存在，無法比對資料，僅由UG資料輸出");
flag = true;
goto ExportFromUG;
}
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=D:\\Knife.xls;" + "Extended Properties='Excel 8.0;" + "HDR=YES;" + "IMEX=1'";
OleDbConnection GetXLS = new OleDbConnection(strCon);
GetXLS.Open();
System.Data.DataTable Table = GetXLS.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//查詢此Excel所有的工作表名稱
//List<Sys_TipTop> ListSheetA = new List<Sys_TipTop>();
string SelectSheetName = "";
foreach (DataRow row in Table.Rows)
{
//抓取Xls各個Sheet的名稱(+'$')-有的名稱需要加名稱''，有的不用
SelectSheetName = (string)row["TABLE_NAME"];
//工作表名稱有特殊字元、空格，需加'工作表名稱$'，ex：'Sheet_A$'
//工作表名稱沒有特殊字元、空格，需加工作表名稱$，ex：SheetA$
//所有工作表名稱為Sheet1，讀取此工作表的內容
if (SelectSheetName == "工作表1$")
{
//select 工作表名稱
OleDbCommand cmSheetA = new OleDbCommand(" SELECT * FROM [工作表1$] ", GetXLS);
OleDbDataReader drSheetA = cmSheetA.ExecuteReader();
//讀取工作表SheetA資料
//List<string> ListSheetA = new List<string>();
while (drSheetA.Read())
{
if (drSheetA[0].ToString() == "")
{
break;
}
List <Sys_TipTop> ListSheetA = new List <Sys_TipTop>();
status = DicToolERP.TryGetValue(drSheetA[0].ToString(), out ListSheetA);
if (!status)
{
ListSheetA = new List <Sys_TipTop>();
Sys_TipTop sSys_TipTop = new Sys_TipTop();
sSys_TipTop.productNo = drSheetA[0].ToString();
sSys_TipTop.stepNo = drSheetA[1].ToString();
sSys_TipTop.partNo = drSheetA[2].ToString();
sSys_TipTop.ois = drSheetA[3].ToString();
sSys_TipTop.erpNo = drSheetA[4].ToString();
sSys_TipTop.toolNo = drSheetA[5].ToString();
sSys_TipTop.usedCount = drSheetA[6].ToString();
sSys_TipTop.toolLife = drSheetA[7].ToString();
sSys_TipTop.toolChangeTime = drSheetA[8].ToString();
sSys_TipTop.toolSpec = drSheetA[9].ToString();
ListSheetA.Add(sSys_TipTop);
DicToolERP.Add(drSheetA[0].ToString(), ListSheetA);
}
else
{
Sys_TipTop sSys_TipTop = new Sys_TipTop();
sSys_TipTop.productNo = drSheetA[0].ToString();
sSys_TipTop.stepNo = drSheetA[1].ToString();
sSys_TipTop.partNo = drSheetA[2].ToString();
sSys_TipTop.ois = drSheetA[3].ToString();
sSys_TipTop.erpNo = drSheetA[4].ToString();
sSys_TipTop.toolNo = drSheetA[5].ToString();
sSys_TipTop.usedCount = drSheetA[6].ToString();
sSys_TipTop.toolLife = drSheetA[7].ToString();
sSys_TipTop.toolChangeTime = drSheetA[8].ToString();
sSys_TipTop.toolSpec = drSheetA[9].ToString();
ListSheetA.Add(sSys_TipTop);
DicToolERP[drSheetA[0].ToString()] = ListSheetA;
}
}
/*步驟4：關閉檔案*/
//結束關閉讀檔(必要，不關會有error)
drSheetA.Close();
GetXLS.Close();
}
}
}
else
{
flag = true;
}
ExportFromUG:
excelApp = new ApplicationClass();
book = null;
sheet = null;
oRng = null;
excelApp.Visible = false;
book = excelApp.Workbooks.Open(ToolListPath);
sheet = (Worksheet)book.Sheets[1];
oRng = (Range)sheet.Cells;
oRng[52, 10] = PartNo;
//Insert所需欄位並填入資料
//新版CurrentRow從7開始、ToolNumberColumn從1開始、ToolNameColumn從8開始
//舊版CurrentRow從8開始、ToolNumberColumn從2開始、ToolNameColumn從3開始
int
CurrentRow = 6,
ToolNumberColumn = 1,
ToolERPColumn = 2,
ToolCutterQtyColumn = 3,
ToolCutterLifeColumn = 4,
ToolFluteQtyColumn = 5,
ToolTitleColumn = 6,
ToolSpecColumn = 8,
ToolNoteColumn = 10;
//將相同刀號記錄起來，只輸出一筆資料
Dictionary <string, OperData> DicToolData = new Dictionary <string, OperData>();
//for (int i = 0; i < panel.Rows.Count; i++)
//{
// OperData toolData = new OperData();
// status = DicToolData.TryGetValue(ReplaceToolNumber(panel.GetCell(i, 0).Value.ToString()), out toolData);
// if (!status)
// {
// toolData.ERP_NO = panel.GetCell(i, 1).Value.ToString();
// toolData.CUTTER_QTY = panel.GetCell(i, 2).Value.ToString();
// toolData.CUTTER_LIFE = panel.GetCell(i, 3).Value.ToString();
// toolData.FLUTE_QTY = panel.GetCell(i, 4).Value.ToString();
// toolData.TITLE = panel.GetCell(i, 5).Value.ToString();
// toolData.SPECIFICATION = panel.GetCell(i, 6).Value.ToString();
// toolData.NOTE = panel.GetCell(i, 7).Value.ToString();
// DicToolData.Add(ReplaceToolNumber(panel.GetCell(i, 0).Value.ToString()), toolData);
// }
//}
if (flag)
{
int StartRow = 0, EndRow = 0;
oRng = (Range)sheet.Cells;
foreach (KeyValuePair <string, List <OperData> > kvp in DicNCData)
{
if (CurrentNCName != kvp.Key)
{
continue;
}
foreach (OperData i in kvp.Value)
{
CurrentRow = CurrentRow + 1;
StartRow = CurrentRow;
oRng[CurrentRow, ToolNumberColumn] = i.ToolNumber;//暫時不寫TOOL_NO，原因是有些刀子不是用系統掉進來會沒有屬性
oRng[CurrentRow, ToolERPColumn] = i.ERP_NO;
oRng[CurrentRow, ToolCutterQtyColumn] = i.CUTTER_QTY;
oRng[CurrentRow, ToolCutterLifeColumn] = i.CUTTER_LIFE;
oRng[CurrentRow, ToolFluteQtyColumn] = i.FLUTE_QTY;
oRng[CurrentRow, ToolTitleColumn] = i.TITLE;
oRng[CurrentRow, ToolSpecColumn] = i.SPECIFICATION;
oRng[CurrentRow, ToolNoteColumn] = i.NOTE;
if (i.ACCESSORY != "")
{
// xxx!QQ!TC10D20FL5R2SD10L40 ? zxb!zxv!zxc ? 34!23!12
string[] SplitAccessory = i.ACCESSORY.Split('?');
foreach (string j in SplitAccessory)
{
CurrentRow = CurrentRow + 1;
string[] Spliti = j.Split('!');
oRng[CurrentRow, ToolERPColumn] = Spliti[0];
oRng[CurrentRow, ToolTitleColumn] = Spliti[1];
oRng[CurrentRow, ToolSpecColumn] = Spliti[2];
}
}
EndRow = CurrentRow;
//合併儲存格
sheet.get_Range("A" + StartRow, "A" + EndRow).Merge(false);
}
}
//foreach (KeyValuePair<string, OperData> kvp in DicToolData)
//{
// CurrentRow = CurrentRow + 1;
// oRng[CurrentRow, ToolNumberColumn] = kvp.Key;
// oRng[CurrentRow, ToolERPColumn] = kvp.Value.ERP_NO;
// oRng[CurrentRow, ToolCutterQtyColumn] = kvp.Value.CUTTER_QTY;
// oRng[CurrentRow, ToolCutterLifeColumn] = kvp.Value.CUTTER_LIFE;
// oRng[CurrentRow, ToolFluteQtyColumn] = kvp.Value.FLUTE_QTY;
// oRng[CurrentRow, ToolTitleColumn] = kvp.Value.TITLE;
// oRng[CurrentRow, ToolSpecColumn] = kvp.Value.SPECIFICATION;
// oRng[CurrentRow, ToolNoteColumn] = kvp.Value.NOTE;
//}
oRng[51, 10] = "OIS-" + comboBoxNCName.Text.Split('P')[1].Split('_')[0];
}
else
{
//判斷TipTopExcel與UG的資料整理出要輸出的資料
Dictionary <string, List <Sys_TipTop> > DicExportData = new Dictionary <string, List <Sys_TipTop> >();
foreach (KeyValuePair <string, List <Sys_TipTop> > i in DicToolERP)
{
if (i.Key != productNo.Text)
{
continue;
}
foreach (KeyValuePair <string, OperData> j in DicToolData)
{
foreach (Sys_TipTop k in i.Value)
{
if (k.ois != ois.Text)
{
continue;
}
if (k.toolNo != j.Key)
{
continue;
}
List <Sys_TipTop> temp = new List <Sys_TipTop>();
status = DicExportData.TryGetValue(j.Key, out temp);
if (!status)
{
temp = new List <Sys_TipTop>();
Sys_TipTop sSys_TipTop = new Sys_TipTop();
sSys_TipTop.productNo = k.productNo;
sSys_TipTop.stepNo = k.stepNo;
sSys_TipTop.partNo = k.partNo;
sSys_TipTop.ois = k.ois;
sSys_TipTop.erpNo = k.erpNo;
sSys_TipTop.toolNo = k.toolNo;
sSys_TipTop.usedCount = k.usedCount;
sSys_TipTop.toolLife = k.toolLife;
sSys_TipTop.toolChangeTime = k.toolChangeTime;
sSys_TipTop.toolSpec = k.toolSpec;
sSys_TipTop.toolUGSpec = j.Value.SPECIFICATION;
temp.Add(sSys_TipTop);
DicExportData.Add(j.Key, temp);
}
else
{
Sys_TipTop sSys_TipTop = new Sys_TipTop();
sSys_TipTop.productNo = k.productNo;
sSys_TipTop.stepNo = k.stepNo;
sSys_TipTop.partNo = k.partNo;
sSys_TipTop.ois = k.ois;
sSys_TipTop.erpNo = k.erpNo;
sSys_TipTop.toolNo = k.toolNo;
sSys_TipTop.usedCount = k.usedCount;
sSys_TipTop.toolLife = k.toolLife;
sSys_TipTop.toolChangeTime = k.toolChangeTime;
sSys_TipTop.toolSpec = k.toolSpec;
sSys_TipTop.toolUGSpec = j.Value.SPECIFICATION;
temp.Add(sSys_TipTop);
DicExportData[j.Key] = temp;
}
}
}
}
oRng = (Range)sheet.Cells;
foreach (KeyValuePair <string, List <Sys_TipTop> > kvp in DicExportData)
{
foreach (Sys_TipTop i in kvp.Value)
{
CurrentRow = CurrentRow + 1;
oRng[CurrentRow, ToolNumberColumn] = i.toolNo;
oRng[CurrentRow, ToolERPColumn] = i.erpNo;
oRng[CurrentRow, ToolCutterQtyColumn] = "1";
oRng[CurrentRow, ToolCutterLifeColumn] = i.toolLife;
oRng[CurrentRow, ToolFluteQtyColumn] = i.usedCount;
oRng[CurrentRow, ToolTitleColumn] = i.toolSpec;
oRng[CurrentRow, ToolSpecColumn] = i.toolUGSpec;
}
}
}
if (Is_Local != null)
{
ISession session = MyHibernateHelper.SessionFactory.OpenSession();
Com_PEMain comPEMain = new Com_PEMain();
#region 由料號查peSrNo
try
{
comPEMain = session.QueryOver <Com_PEMain>().Where(x => x.partName == sPartInfo.PartNo)
.Where(x => x.customerVer == sPartInfo.CusRev)
.Where(x => x.opVer == sPartInfo.OpRev)
.SingleOrDefault <Com_PEMain>();
}
catch (System.Exception ex)
{
MessageBox.Show("1.資料庫中沒有此料號的紀錄，無法上傳刀具清單資料");
return;
}
#endregion
Com_PartOperation comPartOperation = new Com_PartOperation();
#region 由peSrNo和OpNum查partOperationSrNo
try
{
comPartOperation = session.QueryOver <Com_PartOperation>()
.Where(x => x.comPEMain.peSrNo == comPEMain.peSrNo)
.Where(x => x.operation1 == sPartInfo.OpNum)
.SingleOrDefault <Com_PartOperation>();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
MessageBox.Show("2.資料庫中沒有此料號的紀錄，無法上傳刀具清單資料");
return;
}
#endregion
#region 比對資料庫TEMain是否有同筆數據
IList <Com_TEMain> DBData_ComTEMain = session.QueryOver <Com_TEMain>().List <Com_TEMain>();
bool Is_Exist = false;
Com_TEMain currentComTEMain = new Com_TEMain();
foreach (Com_TEMain i in DBData_ComTEMain)
{
if (i.comPartOperation == comPartOperation && i.ncGroupName == CurrentNCName)
{
Is_Exist = true;
currentComTEMain = i;
break;
}
}
#endregion
if (Is_Exist)
{
#region 刪除Com_ToolList
IList <Com_ToolList> DB_ToolList = session.QueryOver <Com_ToolList>()
.Where(x => x.comTEMain.teSrNo == currentComTEMain.teSrNo).List <Com_ToolList>();
using (ITransaction trans = session.BeginTransaction())
{
foreach (Com_ToolList i in DB_ToolList)
{
session.Delete(i);
}
trans.Commit();
}
#endregion
foreach (KeyValuePair <string, List <OperData> > kvp in DicNCData)
{
if (CurrentNCName != kvp.Key)
{
continue;
}
foreach (OperData i in kvp.Value)
{
Com_ToolList comToolList = new Com_ToolList();
comToolList.comTEMain = currentComTEMain;
comToolList.toolNumber = i.TOOL_NO;
comToolList.erpNumber = i.ERP_NO;
comToolList.cutterQty = i.CUTTER_QTY;
comToolList.cutterLife = i.CUTTER_LIFE;
comToolList.fluteQty = i.FLUTE_QTY;
comToolList.title = i.TITLE;
comToolList.specification = i.SPECIFICATION;
comToolList.note = i.NOTE;
comToolList.accessory = i.ACCESSORY;
using (ITransaction trans = session.BeginTransaction())
{
session.Save(comToolList);
trans.Commit();
}
}
}
//foreach (KeyValuePair<string, OperData> kvp in DicToolData)
//{
// Com_ToolList comToolList = new Com_ToolList();
// comToolList.comTEMain = currentComTEMain;
// comToolList.toolNumber = kvp.Key;
// comToolList.erpNumber = kvp.Value.ERP_NO;
// comToolList.cutterQty = kvp.Value.CUTTER_QTY;
// comToolList.cutterLife = kvp.Value.CUTTER_LIFE;
// comToolList.fluteQty = kvp.Value.FLUTE_QTY;
// comToolList.title = kvp.Value.TITLE;
// comToolList.specification = kvp.Value.SPECIFICATION;
// comToolList.note = kvp.Value.NOTE;
// using (ITransaction trans = session.BeginTransaction())
// {
// session.Save(comToolList);
// trans.Commit();
// }
//}
}
}
//oRng = (Range)sheet.Cells;
//foreach (KeyValuePair<string,string> kvp in DicToolData)
//{
// CurrentRow = CurrentRow + 1;
// oRng[CurrentRow, ToolNumberColumn] = kvp.Key;
// oRng[CurrentRow, ToolNameColumn] = kvp.Value;
//}
//oRng = (Range)sheet.Cells;
//for (int i = 0; i < panel.Rows.Count; i++)
//{
// //取得Row,Column
// CurrentRow = CurrentRow + 1;
// //新版
// oRng[CurrentRow, ToolNumberColumn] = panel.GetCell(i, 1).Value.ToString();
// oRng[CurrentRow, ToolNameColumn] = panel.GetCell(i, 2).Value.ToString();
// //舊版
// //oRng[CurrentRow, OpNameColumn] = panel.GetCell(i, 0).Value.ToString();
// //oRng[CurrentRow, ToolNumberColumn] = panel.GetCell(i, 1).Value.ToString();
// //oRng[CurrentRow, ToolNameColumn] = panel.GetCell(i, 2).Value.ToString();
// //oRng[CurrentRow, ToolDescColumn] = panel.GetCell(i, 3).Value.ToString();
//}
MessageBox.Show("刀具清單【完成】");
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
MessageBox.Show("刀具清單【失敗】");
}
finally
{
book.SaveAs(OutputPath, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
book.Close(Type.Missing, Type.Missing, Type.Missing);
excelApp.Quit();
}
this.Close();
}
