public static void GetOperImgPosiAndSize(int i, out OperImgPosiSize sOperImgPosiSize)
{
string ScreenWidth = SystemInformation.PrimaryMonitorSize.Width.ToString();
string ScreenHeight = SystemInformation.PrimaryMonitorSize.Height.ToString();
sOperImgPosiSize = new OperImgPosiSize();
int currentNo = (i % 8);
if (currentNo == 0)
{
sOperImgPosiSize.OperPosiLeft = 5;
sOperImgPosiSize.OperPosiTop = 118;
}
else if (currentNo == 1)
{
sOperImgPosiSize.OperPosiLeft = 5 + (float)(180 * 1366 / Convert.ToDouble(ScreenWidth));
sOperImgPosiSize.OperPosiTop = 118;
}
else if (currentNo == 2)
{
sOperImgPosiSize.OperPosiLeft = 5 + (float)(360 * 1366 / Convert.ToDouble(ScreenWidth));
sOperImgPosiSize.OperPosiTop = 118;
}
else if (currentNo == 3)
{
sOperImgPosiSize.OperPosiLeft = (float)(540 * 1366 / Convert.ToDouble(ScreenWidth));
sOperImgPosiSize.OperPosiTop = 118;
}
else if (currentNo == 4)
{
sOperImgPosiSize.OperPosiLeft = 5;
sOperImgPosiSize.OperPosiTop = 265;
}
else if (currentNo == 5)
{
sOperImgPosiSize.OperPosiLeft = 5 + (float)(180 * 1366 / Convert.ToDouble(ScreenWidth));
sOperImgPosiSize.OperPosiTop = 265;
}
else if (currentNo == 6)
{
sOperImgPosiSize.OperPosiLeft = 5 + (float)(360 * 1366 / Convert.ToDouble(ScreenWidth));
sOperImgPosiSize.OperPosiTop = 265;
}
else if (currentNo == 7)
{
sOperImgPosiSize.OperPosiLeft = (float)(540 * 1366 / Convert.ToDouble(ScreenWidth));
sOperImgPosiSize.OperPosiTop = 265;
}
sOperImgPosiSize.OperImgWidth = 160;
sOperImgPosiSize.OperImgHeight = 115;
float width = sOperImgPosiSize.OperImgWidth;
float height = sOperImgPosiSize.OperImgHeight;
Excel_CommonFun.GetScreenResolution(ref width, ref height);
sOperImgPosiSize.OperImgWidth = width;
sOperImgPosiSize.OperImgHeight = height;
}
public static void GetFixImgPosiAndSize(double left, double top, double width, double height, out FixImgPosiSize sFixImgPosiSize)
{
string ScreenWidth = SystemInformation.PrimaryMonitorSize.Width.ToString();
string ScreenHeight = SystemInformation.PrimaryMonitorSize.Height.ToString();
sFixImgPosiSize = new FixImgPosiSize();
//sFixImgPosiSize.FixPosiLeft = 485;
sFixImgPosiSize.FixPosiLeft = (float)(left * 1366 / Convert.ToDouble(ScreenWidth));
sFixImgPosiSize.FixPosiTop = (float)top;
//sFixImgPosiSize.FixPosiTop = (float)(423 * 768 / Convert.ToDouble(ScreenHeight));
//sFixImgPosiSize.FixImgWidth = 225;
//sFixImgPosiSize.FixImgHeight = 198;
float widthfloat = (float)width;
float heightfloat = (float)height;
Excel_CommonFun.GetScreenResolution(ref widthfloat, ref heightfloat);
sFixImgPosiSize.FixImgWidth = widthfloat;
sFixImgPosiSize.FixImgHeight = heightfloat;
}
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 CreateFQCExcel_XiAn(string cusName, string partNo, string cusVer, string opVer, string op1, DB_MEMain sDB_MEMain, IList <Com_Dimension> cCom_Dimension)
{
//判斷Server的Template是否存在
if (!File.Exists(sDB_MEMain.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
, op1 + "_" + sDB_MEMain.factory + ".xls");
//1.開啟Excel
//2.將Excel設為不顯示
//3.取得第一頁Sheet
workBook = excelApp.Workbooks.Open(sDB_MEMain.excelTemplateFilePath);
excelApp.Visible = false;
workSheet = (Worksheet)workBook.Sheets[1];
workRange = (Range)workSheet.Cells;
workRange[5, 1] = cusName;
workRange[5, 5] = partNo;
workRange[5, 8] = sDB_MEMain.comMEMain.partDescription;
workRange[5, 10] = cusVer;
//設定欄位的Row,Column
int currentRow = 7, ballonColumn = 1, locationColumn = 2, dimenColumn = 3, instrumentColumn = 4;
//Insert所需欄位
for (int i = 1; i < cCom_Dimension.Count; i++)
{
workRange = (Range)workSheet.Range["A8"].EntireRow;
workRange.Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);
}
for (int i = 0; i < cCom_Dimension.Count; i++)
{
workRange = (Range)workSheet.Cells;
//取得Row,Column
currentRow = currentRow + 1;
status = Excel_CommonFun.MappingDimenData(cCom_Dimension[i], workSheet, currentRow, dimenColumn);
if (!status)
{
MessageBox.Show("MappingDimenData時發生錯誤，請聯繫開發工程師");
return(false);
}
#region 檢具、泡泡、泡泡位置
workRange[currentRow, instrumentColumn] = cCom_Dimension[i].instrument;
workRange[currentRow, ballonColumn] = cCom_Dimension[i].ballon;
workRange[currentRow, locationColumn] = cCom_Dimension[i].location;
#endregion
}
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)
{
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 CreateFQCExcel_XinWu(string cusName, string partNo, string cusVer, string opVer, string op1, DB_MEMain sDB_MEMain, IList <Com_Dimension> cCom_Dimension)
{
//判斷Server的Template是否存在
if (!File.Exists(sDB_MEMain.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
, op1 + "_" + sDB_MEMain.factory + ".xls");
//1.開啟Excel
//2.將Excel設為不顯示
//3.取得第一頁Sheet
workBook = (Workbook)excelApp.Workbooks.Open(sDB_MEMain.excelTemplateFilePath);
excelApp.Visible = false;
workSheet = (Worksheet)workBook.Sheets[1];
workRange = (Range)workSheet.Cells;
workRange[5, 1] = cusName;
workRange[5, 5] = partNo;
workRange[5, 6] = cusVer;
workRange[5, 7] = sDB_MEMain.comMEMain.partDescription;
//Insert所需欄位
int Dicount = 0;
foreach (Com_Dimension i in cCom_Dimension)
{
if (i.balloonCount != null)
{
Dicount = Convert.ToInt32(i.balloonCount) + Dicount;
}
else
{
Dicount++;
}
}
for (int i = 1; i < Dicount; i++)
{
workRange = (Range)workSheet.Range["A8"].EntireRow;
workRange.Insert(XlInsertShiftDirection.xlShiftDown, workRange.Copy(Type.Missing));
}
//設定欄位的Row,Column
int currentRow = 7, ballonColumn = 1, locationColumn = 2, dimenColumn = 3, instrumentColumn = 4, checkColumn = 18;
foreach (Com_Dimension i in cCom_Dimension)
{
if (i.balloonCount == null)
{
workRange = (Range)workSheet.Cells;
//取得Row,Column
currentRow = currentRow + 1;
status = Excel_CommonFun.MappingDimenData(i, workSheet, currentRow, dimenColumn);
//status = Excel_CommonFun.MappingDimenData(cCom_Dimension[i], workRange, currentRow, dimenColumn);
if (!status)
{
MessageBox.Show("MappingDimenData時發生錯誤，請聯繫開發工程師");
return(false);
}
#region 檢具、頻率、Max、Min、泡泡、泡泡位置、料號、日期
string[] splitInstrument = i.instrument.Split('，');
if (splitInstrument[1] != null)
{
workRange[currentRow, instrumentColumn] = splitInstrument[1];
}
else
{
workRange[currentRow, instrumentColumn] = splitInstrument[0];
}
workRange[currentRow, ballonColumn] = i.ballon;
workRange[currentRow, locationColumn] = i.location;
workRange[currentRow, checkColumn] = i.checkLevel;
#endregion
}
else
{
for (int j = 0; j < Convert.ToInt32(i.balloonCount); j++)
{
workRange = (Range)workSheet.Cells;
//取得Row,Column
currentRow = currentRow + 1;
status = Excel_CommonFun.MappingDimenData(i, workSheet, currentRow, dimenColumn);
//status = Excel_CommonFun.MappingDimenData(cCom_Dimension[i], workRange, currentRow, dimenColumn);
if (!status)
{
MessageBox.Show("MappingDimenData時發生錯誤，請聯繫開發工程師");
return(false);
}
#region 檢具、頻率、Max、Min、泡泡、泡泡位置、料號、日期
string[] splitInstrument = i.instrument.Split('，');
if (splitInstrument[1] != null)
{
workRange[currentRow, instrumentColumn] = splitInstrument[1];
}
else
{
workRange[currentRow, instrumentColumn] = splitInstrument[0];
}
if (i.balloonCount != null && Convert.ToInt32(i.balloonCount) > 1)
{
workRange[currentRow, ballonColumn] = i.ballon.ToString() + "." + Convert.ToChar(65 + j).ToString().ToLower();
}
else
{
workRange[currentRow, ballonColumn] = i.ballon.ToString();
}
workRange[currentRow, locationColumn] = i.location;
workRange[currentRow, checkColumn] = i.checkLevel;
#endregion
}
}
}
//for (int i = 0; i < cCom_Dimension.Count; i++)
//{
// workRange = (Range)workSheet.Cells;
// //取得Row,Column
// currentRow = currentRow + 1;
// status = Excel_CommonFun.MappingDimenData(cCom_Dimension[i], workSheet, currentRow, dimenColumn);
// //status = Excel_CommonFun.MappingDimenData(cCom_Dimension[i], workRange, currentRow, dimenColumn);
// if (!status)
// {
// MessageBox.Show("MappingDimenData時發生錯誤，請聯繫開發工程師");
// return false;
// }
// #region 檢具、頻率、Max、Min、泡泡、泡泡位置、料號、日期
// workRange[currentRow, instrumentColumn] = cCom_Dimension[i].instrument;
// workRange[currentRow, ballonColumn] = cCom_Dimension[i].ballon;
// workRange[currentRow, locationColumn] = cCom_Dimension[i].location;
// workRange[currentRow, checkColumn] = cCom_Dimension[i].checkLevel;
// #endregion
//}
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 CreateIQCExcel_XinWu(string cusName, string partNo, string cusVer, string opVer, string op1, DB_MEMain sDB_MEMain, IList <Com_Dimension> cCom_Dimension)
{
//判斷Server的Template是否存在
if (!File.Exists(sDB_MEMain.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
, op1 + "_" + sDB_MEMain.factory + ".xls");
//1.開啟Excel
//2.將Excel設為不顯示
//3.取得第一頁Sheet
workBook = (Workbook)excelApp.Workbooks.Open(sDB_MEMain.excelTemplateFilePath);
excelApp.Visible = false;
workSheet = (Worksheet)workBook.Sheets[1];
//建立Sheet頁數符合所有的Dimension
int Dicount = 0;
foreach (Com_Dimension i in cCom_Dimension)
{
if (i.balloonCount != null)
{
Dicount = Convert.ToInt32(i.balloonCount) + Dicount;
}
else
{
Dicount++;
}
}
status = Excel_CommonFun.AddNewSheet(Dicount, 8, excelApp, workSheet);
if (!status)
{
MessageBox.Show("建立Sheet頁失敗，請聯繫開發工程師");
return(false);
}
//修改每一個Sheet名字和頁數
status = Excel_CommonFun.ModifySheet(partNo, "IQC", workBook, workSheet, workRange, sDB_MEMain.factory);
if (!status)
{
MessageBox.Show("修改Sheet名字失敗，請聯繫開發工程師");
return(false);
}
RowColumn sRowColumn = new RowColumn();
int currentSheet_Value;
int count = -1;
foreach (Com_Dimension i in cCom_Dimension)
{
if (i.balloonCount == null)
{
count++;
GetExcelRowColumn(count, out sRowColumn);
currentSheet_Value = (count / 8);
if (currentSheet_Value == 0)
{
workSheet = (Worksheet)workBook.Sheets[1];
}
else
{
workSheet = (Worksheet)workBook.Sheets[currentSheet_Value + 1];
}
workRange = (Range)workSheet.Cells;
status = Excel_CommonFun.MappingDimenData(i, workSheet, sRowColumn.DimensionRow, sRowColumn.DimensionColumn);
//status = Excel_CommonFun.MappingDimenData(cCom_Dimension[i], workRange, sRowColumn.DimensionRow, sRowColumn.DimensionColumn);
if (!status)
{
MessageBox.Show("MappingDimenData時發生錯誤，請聯繫開發工程師");
return(false);
}
#region 檢具、頻率、Max、Min、泡泡、泡泡位置、料號、日期
workRange[sRowColumn.GaugeRow, sRowColumn.GaugeColumn] = "=" + "\"" + i.instrument.Split('，')[0] + "\"" + "&char(10)&" + "\"" + i.instrument.Split('，')[1] + "\"";
workRange[sRowColumn.BallonRow, sRowColumn.BallonColumn] = i.ballon;
workRange[sRowColumn.LocationRow, sRowColumn.LocationColumn] = i.location;
workRange[sRowColumn.PartNoRow, sRowColumn.PartNoColumn] = partNo + string.Format("({0})", cusVer);
workRange[sRowColumn.PartDescRow, sRowColumn.PartDescColumn] = sDB_MEMain.comMEMain.partDescription;
workRange[sRowColumn.MaterialRow, sRowColumn.MaterialColumn] = sDB_MEMain.comMEMain.material;
workRange[sRowColumn.OISRow, sRowColumn.OISColumn] = op1;
workRange[sRowColumn.OISRevRow, sRowColumn.OISRevColumn] = sDB_MEMain.comMEMain.draftingVer;
#endregion
}
else
{
for (int j = 0; j < Convert.ToInt32(i.balloonCount); j++)
{
count++;
GetExcelRowColumn(count, out sRowColumn);
currentSheet_Value = (count / 8);
if (currentSheet_Value == 0)
{
workSheet = (Worksheet)workBook.Sheets[1];
}
else
{
workSheet = (Worksheet)workBook.Sheets[currentSheet_Value + 1];
}
workRange = (Range)workSheet.Cells;
status = Excel_CommonFun.MappingDimenData(i, workSheet, sRowColumn.DimensionRow, sRowColumn.DimensionColumn);
//status = Excel_CommonFun.MappingDimenData(cCom_Dimension[i], workRange, sRowColumn.DimensionRow, sRowColumn.DimensionColumn);
if (!status)
{
MessageBox.Show("MappingDimenData時發生錯誤，請聯繫開發工程師");
return(false);
}
#region 檢具、頻率、Max、Min、泡泡、泡泡位置、料號、日期
workRange[sRowColumn.GaugeRow, sRowColumn.GaugeColumn] = "=" + "\"" + i.instrument.Split('，')[0] + "\"" + "&char(10)&" + "\"" + i.instrument.Split('，')[1] + "\"";
if (i.balloonCount != null && Convert.ToInt32(i.balloonCount) > 1)
{
workRange[sRowColumn.BallonRow, sRowColumn.BallonColumn] = i.ballon.ToString() + "." + Convert.ToChar(65 + j).ToString().ToLower();
}
else
{
workRange[sRowColumn.BallonRow, sRowColumn.BallonColumn] = i.ballon.ToString();
}
workRange[sRowColumn.LocationRow, sRowColumn.LocationColumn] = i.location;
workRange[sRowColumn.PartNoRow, sRowColumn.PartNoColumn] = partNo + string.Format("({0})", cusVer);
workRange[sRowColumn.PartDescRow, sRowColumn.PartDescColumn] = sDB_MEMain.comMEMain.partDescription;
workRange[sRowColumn.MaterialRow, sRowColumn.MaterialColumn] = sDB_MEMain.comMEMain.material;
workRange[sRowColumn.OISRow, sRowColumn.OISColumn] = op1;
workRange[sRowColumn.OISRevRow, sRowColumn.OISRevColumn] = sDB_MEMain.comMEMain.draftingVer;
#endregion
}
}
}
//for (int i = 0; i < cCom_Dimension.Count; i++)
//{
// GetExcelRowColumn(i, out sRowColumn);
// currentSheet_Value = (i / 8);
// if (currentSheet_Value == 0)
// {
// workSheet = (Worksheet)workBook.Sheets[1];
// }
// else
// {
// workSheet = (Worksheet)workBook.Sheets[currentSheet_Value + 1];
// }
// workRange = (Range)workSheet.Cells;
// status = Excel_CommonFun.MappingDimenData(cCom_Dimension[i], workSheet, sRowColumn.DimensionRow, sRowColumn.DimensionColumn);
// //status = Excel_CommonFun.MappingDimenData(cCom_Dimension[i], workRange, sRowColumn.DimensionRow, sRowColumn.DimensionColumn);
// if (!status)
// {
// MessageBox.Show("MappingDimenData時發生錯誤，請聯繫開發工程師");
// return false;
// }
// #region 檢具、頻率、Max、Min、泡泡、泡泡位置、料號、日期
// workRange[sRowColumn.GaugeRow, sRowColumn.GaugeColumn] = cCom_Dimension[i].instrument;
// workRange[sRowColumn.BallonRow, sRowColumn.BallonColumn] = cCom_Dimension[i].ballon;
// workRange[sRowColumn.LocationRow, sRowColumn.LocationColumn] = cCom_Dimension[i].location;
// workRange[sRowColumn.PartNoRow, sRowColumn.PartNoColumn] = partNo + string.Format("({0})", cusVer);
// workRange[sRowColumn.PartDescRow, sRowColumn.PartDescColumn] = sDB_MEMain.comMEMain.partDescription;
// workRange[sRowColumn.MaterialRow, sRowColumn.MaterialColumn] = sDB_MEMain.comMEMain.material;
// workRange[sRowColumn.OISRow, sRowColumn.OISColumn] = op1;
// workRange[sRowColumn.OISRevRow, sRowColumn.OISRevColumn] = sDB_MEMain.comMEMain.draftingVer;
// #endregion
//}
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)
{
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 CreateIPQCExcel_XiAn(string cusName, string partNo, string cusVer, string opVer, string op1, DB_MEMain sDB_MEMain, IList <Com_Dimension> cCom_Dimension)
{
//判斷Server的Template是否存在
if (!File.Exists(sDB_MEMain.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
, op1 + "_" + sDB_MEMain.factory + ".xls");
//判斷Server的Template是否存在
if (!File.Exists(sDB_MEMain.excelTemplateFilePath))
{
return(false);
}
//1.開啟Excel
//2.將Excel設為不顯示
//3.取得第一頁Sheet
workBook = excelApp.Workbooks.Open(sDB_MEMain.excelTemplateFilePath);
excelApp.Visible = false;
workSheet = (Worksheet)workBook.Sheets[1];
//建立Sheet頁數符合所有的Dimension
status = Excel_CommonFun.AddNewSheet(cCom_Dimension.Count, 15, excelApp, workSheet);
if (!status)
{
MessageBox.Show("建立Sheet頁失敗，請聯繫開發工程師");
workBook.SaveAs(sDB_MEMain.excelTemplateFilePath, 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();
return(false);
}
//修改每一個Sheet名字和頁數
status = Excel_CommonFun.ModifySheet(partNo, "IPQC", workBook, workSheet, workRange, sDB_MEMain.factory);
if (!status)
{
MessageBox.Show("修改Sheet名字和頁數失敗，請聯繫開發工程師");
//workBook.SaveAs(sDB_MEMain.excelTemplateFilePath, 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();
return(false);
}
RowColumn sRowColumn = new RowColumn();
int currentSheet_Value;
for (int i = 0; i < cCom_Dimension.Count; i++)
{
GetExcelRowColumn(i, sDB_MEMain.factory, out sRowColumn);
currentSheet_Value = (i / 15);
if (currentSheet_Value == 0)
{
workSheet = (Worksheet)workBook.Sheets[1];
}
else
{
workSheet = (Worksheet)workBook.Sheets[currentSheet_Value + 1];
}
workRange = (Range)workSheet.Cells; /*workSheet.Range[sRowColumn.DimensionRow, sRowColumn.DimensionColumn].Characters[1]*/
status = Excel_CommonFun.MappingDimenData(cCom_Dimension[i], workSheet, sRowColumn.DimensionRow, sRowColumn.DimensionColumn);
if (!status)
{
MessageBox.Show("MappingDimenData時發生錯誤，請聯繫開發工程師");
//workBook.SaveAs(sDB_MEMain.excelTemplateFilePath, 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();
return(false);
}
#region 泡泡、泡泡位置、檢具、刀號、頻率、料號、製程序、客戶版次、日期
workRange[sRowColumn.BallonRow, sRowColumn.BallonColumn] = cCom_Dimension[i].ballon;
workRange[sRowColumn.LocationRow, sRowColumn.LocationColumn] = cCom_Dimension[i].location;
workRange[sRowColumn.GaugeRow, sRowColumn.GaugeColumn] = cCom_Dimension[i].instrument;
workRange[sRowColumn.ToolNoControlRow, sRowColumn.ToolNoControlColumn] = cCom_Dimension[i].toolNoControl;
workRange[sRowColumn.FrequencyRow, sRowColumn.FrequencyColumn] = cCom_Dimension[i].frequency;
workRange[sRowColumn.PartNoRow, sRowColumn.PartNoColumn] = partNo;
workRange[sRowColumn.OISRow, sRowColumn.OISColumn] = op1;
workRange[sRowColumn.OISRevRow, sRowColumn.OISRevColumn] = cusVer;
workRange[sRowColumn.DateRow, sRowColumn.DateColumn] = DateTime.Now.ToShortDateString();
#endregion
}
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)
{
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 CreateFixInsExcel(string cusName, string partNo, string cusVer, string opVer, string op1, DB_FixInspection sDB_FixInspection, IList <Com_FixDimension> cCom_FixDimension)
{
//判斷Server的Template是否存在
if (!File.Exists(sDB_FixInspection.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_FixInspection.comFixInspection.fixPartName + "模檢治" + ".xls");
//1.開啟Excel
//2.將Excel設為不顯示
//3.取得第一頁Sheet
workBook = (Workbook)excelApp.Workbooks.Open(sDB_FixInspection.excelTemplateFilePath);
excelApp.Visible = false;
workSheet = (Worksheet)workBook.Sheets[1];
//建立Sheet頁數符合所有的Dimension
int Dicount = 0;
foreach (Com_FixDimension i in cCom_FixDimension)
{
if (i.balloonCount != null)
{
Dicount = Convert.ToInt32(i.balloonCount) + Dicount;
}
else
{
Dicount++;
}
}
status = Excel_CommonFun.AddNewSheet(Dicount, 15, excelApp, workSheet);
if (!status)
{
MessageBox.Show("建立Sheet頁失敗，請聯繫開發工程師");
workBook.SaveAs(sDB_FixInspection.excelTemplateFilePath, 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();
return(false);
}
RowColumn sRowColumn = new RowColumn();
int currentSheet_Value;
int count = -1;
foreach (Com_FixDimension i in cCom_FixDimension)
{
if (i.balloonCount == null)
{
count++;
GetExcelRowColumn(count, out sRowColumn);
currentSheet_Value = (count / 15);
if (currentSheet_Value == 0)
{
workSheet = (Worksheet)workBook.Sheets[1];
}
else
{
workSheet = (Worksheet)workBook.Sheets[currentSheet_Value + 1];
}
workRange = (Range)workSheet.Cells;
status = Excel_CommonFun.MappingFixInsDimenData(i, workSheet, sRowColumn.FixInsDimensionRow, sRowColumn.FixInsDimensionColumn);
if (!status)
{
MessageBox.Show("MappingDimenData時發生錯誤，請聯繫開發工程師");
workBook.Close(Type.Missing, Type.Missing, Type.Missing);
excelApp.Quit();
return(false);
}
#region 泡泡、模檢治編號、ERP編號、品名
workRange[sRowColumn.BallonRow, sRowColumn.BallonColumn] = i.ballon.ToString();
workRange[sRowColumn.FixInsNoRow, sRowColumn.FixInsNoColumn] = sDB_FixInspection.comFixInspection.fixinsNo.ToString();
workRange[sRowColumn.FixInsDescRow, sRowColumn.FixInsDescColumn] = sDB_FixInspection.comFixInspection.fixinsDescription.ToString();
workRange[sRowColumn.ERPRow, sRowColumn.ERPColumn] = sDB_FixInspection.comFixInspection.fixinsERP.ToString();
#endregion
}
else
{
for (int j = 0; j < Convert.ToInt32(i.balloonCount); j++)
{
count++;
GetExcelRowColumn(count, out sRowColumn);
currentSheet_Value = (count / 15);
if (currentSheet_Value == 0)
{
workSheet = (Worksheet)workBook.Sheets[1];
}
else
{
workSheet = (Worksheet)workBook.Sheets[currentSheet_Value + 1];
}
workRange = (Range)workSheet.Cells;
status = Excel_CommonFun.MappingFixInsDimenData(i, workSheet, sRowColumn.FixInsDimensionRow, sRowColumn.FixInsDimensionColumn);
if (!status)
{
MessageBox.Show("MappingDimenData時發生錯誤，請聯繫開發工程師");
workBook.Close(Type.Missing, Type.Missing, Type.Missing);
excelApp.Quit();
return(false);
}
#region 泡泡、模檢治編號、ERP編號、品名
if (i.balloonCount != null && Convert.ToInt32(i.balloonCount) > 1)
{
workRange[sRowColumn.BallonRow, sRowColumn.BallonColumn] = i.ballon.ToString() + Convert.ToChar(65 + j).ToString().ToLower();
}
else
{
workRange[sRowColumn.BallonRow, sRowColumn.BallonColumn] = i.ballon.ToString();
}
workRange[sRowColumn.FixInsNoRow, sRowColumn.FixInsNoColumn] = sDB_FixInspection.comFixInspection.fixinsNo.ToString();
workRange[sRowColumn.FixInsDescRow, sRowColumn.FixInsDescColumn] = sDB_FixInspection.comFixInspection.fixinsDescription.ToString();
workRange[sRowColumn.ERPRow, sRowColumn.ERPColumn] = sDB_FixInspection.comFixInspection.fixinsERP.ToString();
#endregion
}
}
}
//計算欄位的初始top
double topDouble = 0;
for (int i = 1; i < 5; i++)
{
workRange = (Range)workSheet.Cells[i, 1];
topDouble = topDouble + Convert.ToDouble(workRange.Height);
//CaxLog.ShowListingWindow(oRng.Height.ToString());
}
//CaxLog.ShowListingWindow(de.ToString());
//計算欄位的初始left
double leftDouble = 0;
for (int i = 1; i < 2; i++)
{
workRange = (Range)workSheet.Cells[1, i];
leftDouble = leftDouble + Convert.ToDouble(workRange.Width.ToString());
}
//計算圖片的width
double widthDouble = 0;
for (int i = 2; i < 34; i++)
{
workRange = (Range)workSheet.Cells[1, i];
widthDouble = widthDouble + Convert.ToDouble(workRange.Width.ToString());
}
//計算圖片的height
double heightDouble = 0;
for (int i = 5; i < 14; i++)
{
workRange = (Range)workSheet.Cells[i, 1];
heightDouble = heightDouble + Convert.ToDouble(workRange.Height);
}
//加入圖片
FixImgPosiSize sFixImgPosiSize = new FixImgPosiSize();
GetFixImgPosiAndSize(leftDouble, topDouble, widthDouble, heightDouble, out sFixImgPosiSize);
for (int i = 0; i < workBook.Sheets.Count; i++)
{
workSheet = (Worksheet)workBook.Sheets[i + 1];
string[] splitFixImgPath = sDB_FixInspection.comFixInspection.fixPicPath.Split(',');
foreach (string j in splitFixImgPath)
{
workSheet.Shapes.AddPicture(j, Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue, sFixImgPosiSize.FixPosiLeft,
sFixImgPosiSize.FixPosiTop, sFixImgPosiSize.FixImgWidth, sFixImgPosiSize.FixImgHeight);
}
//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);
}
public static bool CreateCPExcel(DB_CPKey sDB_CP, List <DB_CPValue> sDB_CPValue)
{
//判斷Server的Template是否存在
if (!File.Exists(sDB_CP.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}"
, Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
, sDB_CP.PartNo
, sDB_CP.CusVer
, sDB_CP.OpVer
, sDB_CP.PartNo
, sDB_CP.CusVer
, sDB_CP.OpVer + "_" + "ControlPlan" + ".xls");
//1.開啟Excel
//2.將Excel設為不顯示
//3.取得第一頁Sheet
workBook = (Workbook)excelApp.Workbooks.Open(sDB_CP.excelTemplateFilePath);
excelApp.Visible = false;
workSheet = (Worksheet)workBook.Sheets[1];
workRange = (Range)workSheet.Cells;
workRange[6, 1] = sDB_CP.PartNo;
workRange[6, 4] = sDB_CP.CusVer;
workRange[8, 1] = sDB_CP.PartDesc;
//Insert所需欄位，此處有判斷是否有SPC欄位需要增加
int dimenCount = 0;
foreach (DB_CPValue i in sDB_CPValue)
{
foreach (KeyValuePair <int, IList <Com_Dimension> > y in i.DicBalloonData)
{
int balloonDimenCount = 0;
foreach (Com_Dimension k in y.Value)
{
dimenCount++;
balloonDimenCount++;
//當該泡泡的尺寸數量輪巡到最後一筆的時候，判斷是否有SPC，如果有則再加一欄
if (y.Value.Count == balloonDimenCount & (k.spcControl != null & k.spcControl != ""))
{
dimenCount++;
}
}
}
}
//之後可以試看看先紀錄第16行，要插入資料的時候再新增就好
while (dimenCount - 1 != 0 && dimenCount > 1)
{
workRange = (Range)workSheet.Range["A16"].EntireRow;
workRange.Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);
dimenCount--;
}
//設定欄位的Row,Column
int
currentRow = 16, dimenColumn = 8, op1Column = 1, op2Column = 2, ballonColumn = 4, productColumn = 5, keyChara = 7,
instrumentColumn = 9, methodColumn = 12, spcControlColumn = 9, sizeColumn = 10, freqColumn = 11;
foreach (DB_CPValue i in sDB_CPValue)
{
if (i.DicBalloonData.Count == 0)
{
continue;
}
workRange = (Range)workSheet.Cells;
workRange[currentRow, op1Column] = i.Op1;
workRange[currentRow, op2Column] = i.Op2;
string OP1MergeRow = currentRow.ToString();
foreach (KeyValuePair <int, IList <Com_Dimension> > j in i.DicBalloonData)
{
workRange[currentRow, ballonColumn] = j.Key;
//判斷是否有SPC，如果有則多合併一個欄位
if (j.Value[0].spcControl != null & j.Value[0].spcControl != "")
{
//合併儲存格-泡泡
workSheet.get_Range("D" + currentRow.ToString(), "D" + (currentRow + j.Value.Count).ToString()).Merge(false);
//workSheet.get_Range("D:D", Type.Missing).HorizontalAlignment = XlVAlign.xlVAlignCenter;
//workSheet.get_Range("D:D", Type.Missing).VerticalAlignment = XlVAlign.xlVAlignDistributed;
//合併儲存格-Product
workSheet.get_Range("E" + currentRow.ToString(), "E" + (currentRow + j.Value.Count).ToString()).Merge(false);
//合併儲存格-KC
workSheet.get_Range("G" + currentRow.ToString(), "G" + (currentRow + j.Value.Count).ToString()).Merge(false);
//合併儲存格-尺寸
workSheet.get_Range("H" + currentRow.ToString(), "H" + (currentRow + j.Value.Count).ToString()).Merge(false);
}
else
{
//合併儲存格-泡泡
workSheet.get_Range("D" + currentRow.ToString(), "D" + (currentRow + j.Value.Count - 1).ToString()).Merge(false);
//workSheet.get_Range("D:D", Type.Missing).HorizontalAlignment = XlVAlign.xlVAlignCenter;
//workSheet.get_Range("D:D", Type.Missing).VerticalAlignment = XlVAlign.xlVAlignDistributed;
//合併儲存格-Product
workSheet.get_Range("E" + currentRow.ToString(), "E" + (currentRow + j.Value.Count - 1).ToString()).Merge(false);
//合併儲存格-KC
workSheet.get_Range("G" + currentRow.ToString(), "G" + (currentRow + j.Value.Count - 1).ToString()).Merge(false);
//合併儲存格-尺寸
workSheet.get_Range("H" + currentRow.ToString(), "H" + (currentRow + j.Value.Count - 1).ToString()).Merge(false);
}
//紀錄泡泡拿來判斷，當出現相同泡泡又要插圖片時只差一個圖片
int tempBalloon = 0, count = 0;
foreach (Com_Dimension k in j.Value)
{
//紀錄泡泡拿來判斷，當出現相同泡泡又要插圖片時只插一個圖片
tempBalloon = k.ballon;
status = Excel_CommonFun.MappingDimenData(k, workSheet, currentRow, dimenColumn);
if (!status)
{
//MessageBox.Show("MappingDimenData時發生錯誤，請聯繫開發工程師");
return(false);
}
workRange[currentRow, instrumentColumn] = k.instrument;
workRange[currentRow, productColumn] = k.productName;
workRange[currentRow, methodColumn] = k.excelType;
if (k.excelType != "SelfCheck")
{
workRange[currentRow, sizeColumn] = k.size;
workRange[currentRow, freqColumn] = k.freq;
}
else
{
workRange[currentRow, sizeColumn] = k.selfCheck_Size;
workRange[currentRow, freqColumn] = k.selfCheck_Freq;
}
count++;
if (count == j.Value.Count)
{
//當量具資料都插好了之後再插入SPC
if (j.Value[0].spcControl != null & j.Value[0].spcControl != "")
{
currentRow = currentRow + 1;
workRange[currentRow, spcControlColumn] = k.spcControl;
workRange[currentRow, methodColumn] = "Software";
//合併儲存格-SPC
workSheet.get_Range("I" + currentRow, "K" + currentRow).Merge(false);
}
if (k.keyChara.Contains("cax"))
{
Range tempRange = (Range)workSheet.get_Range("G" + (currentRow - j.Value.Count + 1).ToString());
float left, top, rowHeight;
rowHeight = Convert.ToSingle(tempRange.RowHeight);
left = Convert.ToSingle(tempRange.Left) + 10;
if (j.Value.Count == 1)
{
top = Convert.ToSingle(tempRange.Top) + Convert.ToSingle(rowHeight / 2 - 5);
}
else
{
top = Convert.ToSingle(tempRange.Top) + Convert.ToSingle((rowHeight * j.Value.Count) / 3);
}
//MessageBox.Show(workRange.Column.ToString());//表示第幾欄
//MessageBox.Show(workRange.ColumnWidth.ToString());//該欄的寬度
workSheet.Shapes.AddPicture(k.keyChara, Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue, left,
top, 10, 10);
}
else
{
workRange[currentRow, keyChara] = k.keyChara;
}
}
currentRow = currentRow + 1;
}
}
//合併儲存格-OP1
workSheet.get_Range("A" + OP1MergeRow, "A" + (currentRow - 1).ToString()).Merge(false);
//合併儲存格-OP2
workSheet.get_Range("B" + OP1MergeRow, "B" + (currentRow - 1).ToString()).Merge(false);
}
/*
* foreach (DB_CPValue i in sDB_CPValue)
* {
* workRange = (Range)workSheet.Cells;
* workRange[currentRow, op1Column] = i.Op1;
* workRange[currentRow, op2Column] = i.Op2;
* foreach (Com_Dimension y in i.comDimension)
* {
* workRange[currentRow, ballonColumn] = y.ballon;
* workRange[currentRow, productColumn] = y.productName;
* workRange[currentRow, keyChara] = y.keyChara;
* status = Excel_CommonFun.MappingDimenData(y, workSheet, currentRow, dimenColumn);
* if (!status)
* {
* MessageBox.Show("MappingDimenData時發生錯誤，請聯繫開發工程師");
* return false;
* }
* workRange[currentRow, instrumentColumn] = y.instrument;
* workRange[currentRow, methodColumn] = y.excelType;
* currentRow = currentRow + 1;
* int balloonNum = y.ballon;
* if (y.ballon == balloonNum)
* {
* workSheet.get_Range("D16", "D17").Merge(false);
* workSheet.get_Range("D:D", Type.Missing).HorizontalAlignment = XlVAlign.xlVAlignCenter;
* workSheet.get_Range("D:D", Type.Missing).VerticalAlignment = XlVAlign.xlVAlignDistributed;
* }
* }
* }
*/
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)
{
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);
}
