public static bool CreateToolListExcel_XinWu(string cusName, string partNo, string cusVer, string opVer, string op1, DB_TEMain sDB_TEMain, IList <Com_ToolList> cCom_ToolList)
{
//判斷Server的Template是否存在
if (!File.Exists(sDB_TEMain.excelTemplateFilePath))
{
return(false);
}
ApplicationClass excelApp = new ApplicationClass();
Workbook workBook = null;
Worksheet workSheet = null;
Range workRange = null;
try
{
//設定輸出路徑
OutputPath = string.Format(@"{0}\{1}_{2}_{3}\{4}_{5}_{6}_{7}"
, Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
, partNo
, cusVer
, opVer
, partNo
, cusVer
, opVer
, sDB_TEMain.comTEMain.ncGroupName + "_" + "ToolList" + ".xls");
//1.開啟Excel
//2.將Excel設為不顯示
//3.取得第一頁Sheet
workBook = (Workbook)excelApp.Workbooks.Open(sDB_TEMain.excelTemplateFilePath);
excelApp.Visible = false;
workSheet = (Worksheet)workBook.Sheets[1];
//建立Sheet頁數符合所有的Operation
//之後應該要改成同一頁然後一直新增ROW
status = Excel_CommonFun.AddNewSheet(cCom_ToolList.Count, 40, excelApp, workSheet);
if (!status)
{
MessageBox.Show("建立Sheet頁失敗，請聯繫開發工程師");
return(false);
}
//修改每一個Sheet名字和頁數
status = Excel_CommonFun.ModifySheet(partNo, "ToolList", workBook, workSheet, workRange, sDB_TEMain.factory);
if (!status)
{
MessageBox.Show("修改Sheet名字和頁數失敗，請聯繫開發工程師");
return(false);
}
//開始填表
int
CurrentRow = 6,
ToolNumberColumn = 1,
ToolERPColumn = 2,
ToolCutterQtyColumn = 3,
ToolCutterLifeColumn = 4,
ToolFluteQtyColumn = 5,
ToolTitleColumn = 6,
ToolSpecColumn = 8,
ToolNoteColumn = 10;
RowColumn sRowColumn; int StartRow = 0, EndRow = 0;
for (int i = 0; i < cCom_ToolList.Count; i++)
{
//GetExcelRowColumn(i, out sRowColumn);
//取得當前Operation該放置的Sheet
int currentSheet_Value = (i / 40);
if (currentSheet_Value == 0)
{
workSheet = (Worksheet)workBook.Sheets[1];
}
else
{
workSheet = (Worksheet)workBook.Sheets[currentSheet_Value + 1];
}
CurrentRow = CurrentRow + 1;
StartRow = CurrentRow;
workRange = (Range)workSheet.Cells;
workRange[CurrentRow, ToolNumberColumn] = cCom_ToolList[i].toolNumber;
workRange[CurrentRow, ToolERPColumn] = cCom_ToolList[i].erpNumber;
workRange[CurrentRow, ToolCutterQtyColumn] = cCom_ToolList[i].cutterQty;
workRange[CurrentRow, ToolCutterLifeColumn] = cCom_ToolList[i].cutterLife;
workRange[CurrentRow, ToolFluteQtyColumn] = cCom_ToolList[i].fluteQty;
workRange[CurrentRow, ToolTitleColumn] = cCom_ToolList[i].title;
workRange[CurrentRow, ToolSpecColumn] = cCom_ToolList[i].specification;
workRange[CurrentRow, ToolNoteColumn] = cCom_ToolList[i].note;
if (cCom_ToolList[i].accessory != "")
{
string[] SplitAccessory = cCom_ToolList[i].accessory.Split('?');
foreach (string j in SplitAccessory)
{
CurrentRow = CurrentRow + 1;
string[] Spliti = j.Split('!');
workRange[CurrentRow, ToolERPColumn] = Spliti[0];
workRange[CurrentRow, ToolTitleColumn] = Spliti[1];
workRange[CurrentRow, ToolSpecColumn] = Spliti[2];
}
}
EndRow = CurrentRow;
//合併儲存格
workSheet.get_Range("A" + StartRow, "A" + EndRow).Merge(false);
}
//加入 料號.品名.設計.審核.批准
workRange[52, 10] = partNo;
workRange[51, 10] = "OIS-" + sDB_TEMain.ncGroupName.Split('P')[1].Split('_')[0];
workRange[52, 5] = sDB_TEMain.comTEMain.designed;
workRange[52, 6] = sDB_TEMain.comTEMain.reviewed;
workRange[52, 7] = sDB_TEMain.comTEMain.approved;
if (File.Exists(OutputPath))
{
File.Delete(OutputPath);
}
workBook.SaveAs(OutputPath, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workBook.Close(Type.Missing, Type.Missing, Type.Missing);
excelApp.Quit();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
if (File.Exists(OutputPath))
{
File.Delete(OutputPath);
}
workBook.SaveAs(OutputPath, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workBook.Close(Type.Missing, Type.Missing, Type.Missing);
excelApp.Quit();
File.Delete(OutputPath);
return(false);
}
finally
{
Dispose(excelApp, workBook, workSheet, workRange);
}
return(true);
}
public static bool CreateShopDocExcel_XinWu(string cusName, string partNo, string cusVer, string opVer, string op1, DB_TEMain sDB_TEMain, IList <Com_ShopDoc> cCom_ShopDoc)
{
//判斷Server的Template是否存在
if (!File.Exists(sDB_TEMain.excelTemplateFilePath))
{
return(false);
}
ApplicationClass excelApp = new ApplicationClass();
Workbook workBook = null;
Worksheet workSheet = null;
Range workRange = null;
try
{
//設定輸出路徑
OutputPath = string.Format(@"{0}\{1}_{2}_{3}\{4}_{5}_{6}_{7}"
, Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
, partNo
, cusVer
, opVer
, partNo
, cusVer
, opVer
, sDB_TEMain.comTEMain.ncGroupName + "_" + "ShopDoc" + ".xls");
//MessageBox.Show("0");
//1.開啟Excel
//2.將Excel設為不顯示
//3.取得第一頁Sheet
workBook = (Workbook)excelApp.Workbooks.Open(sDB_TEMain.excelTemplateFilePath);
//MessageBox.Show("1");
excelApp.Visible = false;
//MessageBox.Show("2");
workSheet = (Worksheet)workBook.Sheets[1];
//MessageBox.Show("3");
//建立Sheet頁數符合所有的Operation
status = Excel_CommonFun.AddNewSheet(cCom_ShopDoc.Count, 8, excelApp, workSheet);
if (!status)
{
MessageBox.Show("建立Sheet頁失敗，請聯繫開發工程師");
return(false);
}
//修改每一個Sheet名字和頁數
status = Excel_CommonFun.ModifySheet(sDB_TEMain.ncGroupName, "ShopDoc", workBook, workSheet, workRange, sDB_TEMain.factory);
if (!status)
{
MessageBox.Show("修改Sheet名字和頁數失敗，請聯繫開發工程師");
return(false);
}
//計算欄位的初始TOP
double de = 0;
for (int i = 1; i < 23; i++)
{
workRange = (Range)workSheet.Cells[i, 1];
de = de + Convert.ToDouble(workRange.Height);
//CaxLog.ShowListingWindow(oRng.Height.ToString());
}
//CaxLog.ShowListingWindow(de.ToString());
//計算欄位的初始LEFT
double abc = 0;
for (int i = 1; i < 9; i++)
{
workRange = (Range)workSheet.Cells[23, i];
abc = abc + Convert.ToDouble(workRange.Width.ToString());
}
//開始填表
RowColumn sRowColumn;
for (int i = 0; i < cCom_ShopDoc.Count; i++)
{
GetExcelRowColumn(i, out sRowColumn);
//取得當前Operation該放置的Sheet
int currentSheet_Value = (i / 8);
//int currentSheet_Reserve = (i % 8);
if (currentSheet_Value == 0)
{
workSheet = (Worksheet)workBook.Sheets[1];
}
else
{
workSheet = (Worksheet)workBook.Sheets[currentSheet_Value + 1];
}
workRange = (Range)workSheet.Cells;
workRange[sRowColumn.OperImgToolRow, sRowColumn.OperImgToolColumn] = cCom_ShopDoc[i].toolNo + "_" + cCom_ShopDoc[i].operationName;
//workRange[sRowColumn.ToolNumberRow, sRowColumn.ToolNumberColumn] = cCom_ShopDoc[i].toolNo;
workRange[sRowColumn.ToolNameRow, sRowColumn.ToolNameColumn] = cCom_ShopDoc[i].toolID;
workRange[sRowColumn.OperNameRow, sRowColumn.OperNameColumn] = cCom_ShopDoc[i].operationName;
workRange[sRowColumn.HolderRow, sRowColumn.HolderColumn] = cCom_ShopDoc[i].holderID;
workRange[sRowColumn.ToolFeedRow, sRowColumn.ToolFeedColumn] = "F：" + cCom_ShopDoc[i].feed;
workRange[sRowColumn.ToolSpeedRow, sRowColumn.ToolSpeedColumn] = cCom_ShopDoc[i].speed;
workRange[sRowColumn.PartStockRow, sRowColumn.PartStockColumn] = cCom_ShopDoc[i].partStock;
workRange[sRowColumn.CutterLifeRow, sRowColumn.CutterLifeColumn] = cCom_ShopDoc[i].cutterLife;
workRange[sRowColumn.ExtensionRow, sRowColumn.ExtensionColumn] = cCom_ShopDoc[i].extension;
workRange[sRowColumn.CuttingTimeRow, sRowColumn.CuttingTimeColumn] = cCom_ShopDoc[i].machiningtime;
workRange[sRowColumn.PartNoRow, sRowColumn.PartNoColumn] = partNo;
workRange[sRowColumn.TotalCuttingTimeRow, sRowColumn.TotalCuttingTimeColumn] = sDB_TEMain.comTEMain.totalCuttingTime;
workRange[sRowColumn.PartDescRow, sRowColumn.PartDescColumn] = "OIS-" + sDB_TEMain.ncGroupName.Split('P')[1];
//加入 機台型號.設計.審核.批准
workRange[sRowColumn.MachineNoRow, sRowColumn.MachineNoColumn] = sDB_TEMain.comTEMain.machineNo;
workRange[sRowColumn.DesignedRow, sRowColumn.DesignedColumn] = sDB_TEMain.comTEMain.designed;
workRange[sRowColumn.ReviewedRow, sRowColumn.ReviewedColumn] = sDB_TEMain.comTEMain.reviewed;
workRange[sRowColumn.ApprovedRow, sRowColumn.ApprovedColumn] = sDB_TEMain.comTEMain.approved;
OperImgPosiSize sImgPosiSize = new OperImgPosiSize();
GetOperImgPosiAndSize(i, out sImgPosiSize);
if (File.Exists(cCom_ShopDoc[i].opImagePath))
{
workSheet.Shapes.AddPicture(cCom_ShopDoc[i].opImagePath, Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue, sImgPosiSize.OperPosiLeft,
sImgPosiSize.OperPosiTop, sImgPosiSize.OperImgWidth, sImgPosiSize.OperImgHeight);
}
}
//加入控制尺寸資訊(未開發)
ISession session = MyHibernateHelper.SessionFactory.OpenSession();
IList <Com_ControlDimen> ListComControlDimen = session.QueryOver <Com_ControlDimen>()
.Where(x => x.comTEMain == sDB_TEMain.comTEMain).List();
int j = 0, initialControlDimenRow = 39, ControlDimenToolNoColumn = 9, ControlDimenBalloonColumn = 10, ControlDimenColumn = 11;
string toolNo = "";
foreach (Com_ControlDimen i in ListComControlDimen)
{
workSheet = (Worksheet)workBook.Sheets[1 + j];
workRange = (Range)workSheet.Cells;
if (toolNo == "" || toolNo != i.toolNo)
{
workRange[initialControlDimenRow, ControlDimenToolNoColumn] = i.toolNo;
toolNo = i.toolNo;
}
workRange[initialControlDimenRow, ControlDimenBalloonColumn] = i.controlBallon;
workRange[initialControlDimenRow, ControlDimenColumn] = i.controlDimen;
initialControlDimenRow++;
if (initialControlDimenRow == 47)
{
j++;
}
}
//加入治具圖片
if (sDB_TEMain.comTEMain.fixtureImgPath != "")
{
FixImgPosiSize sFixImgPosiSize = new FixImgPosiSize();
GetFixImgPosiAndSize(out sFixImgPosiSize);
for (int i = 0; i < workBook.Sheets.Count; i++)
{
workSheet = (Worksheet)workBook.Sheets[i + 1];
if (File.Exists(sDB_TEMain.comTEMain.fixtureImgPath))
{
workSheet.Shapes.AddPicture(sDB_TEMain.comTEMain.fixtureImgPath, Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue, (float)(abc + 3),
(float)(de + 5), sFixImgPosiSize.FixImgWidth, sFixImgPosiSize.FixImgHeight);
}
}
}
if (File.Exists(OutputPath))
{
File.Delete(OutputPath);
}
workBook.SaveAs(OutputPath, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workBook.Close(Type.Missing, Type.Missing, Type.Missing);
excelApp.Quit();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
if (File.Exists(OutputPath))
{
File.Delete(OutputPath);
}
workBook.SaveAs(OutputPath, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workBook.Close(Type.Missing, Type.Missing, Type.Missing);
excelApp.Quit();
File.Delete(OutputPath);
return(false);
}
finally
{
Dispose(excelApp, workBook, workSheet, workRange);
}
return(true);
}