private void BindCheckItem(ref Aspose.Cells.Worksheet sheet, string FormNo, string Bu)
{
//page 格式設定
SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape);
Aspose.Cells.Cells cells = sheet.Cells;
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
NPIMgmt oMgmt = new NPIMgmt("CZ", Bu);
NPI_Standard oStandard = oMgmt.InitialLeaveMgmt();
#region//獲取主表資訊
DataTable dtMaster = oStandard.GetPrelaunchInconformity(FormNo);
if (dtMaster.Rows.Count > 0)
{
int templateIndex = 1; //模板row起始位置
int insertIndexEnCounter = templateIndex + 1; //new row起始位置
cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1);
cells.CopyRows(cells, templateIndex, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行
string url = "http://icm651.liteon.com/WF_PrelaunchReport/";
for (int i = 0; i < dtMaster.Rows.Count; i++)
{
DataRow dr = dtMaster.Rows[i];
string FileName = dr["FileName"].ToString();
cells[i + templateIndex, 0].PutValue(dr["ID"].ToString());
cells[i + templateIndex, 1].PutValue(dr["Dept"].ToString());
cells[i + templateIndex, 2].PutValue(dr["CheckItem"].ToString());
cells[i + templateIndex, 3].PutValue(dr["Description"].ToString());
cells[i + templateIndex, 4].PutValue(dr["Status"].ToString());
cells[i + templateIndex, 5].PutValue(dr["Remark"].ToString());
cells[i + templateIndex, 6].PutValue(dr["Suggestion"].ToString());
cells[i + templateIndex, 7].PutValue(dr["CompleteDate"].ToString().Length > 0 ? Convert.ToDateTime(dr["CompleteDate"].ToString()).ToString("yyyy/MM/dd") : dr["CompleteDate"].ToString());
cells[i + templateIndex, 8].PutValue(dr["UpateUser"].ToString());
cells[i + templateIndex, 9].PutValue(dr["UpdateTime"].ToString().Length > 0 ? Convert.ToDateTime(dr["UpdateTime"].ToString()).ToString("yyyy/MM/dd") : dr["UpdateTime"].ToString());
if (!string.IsNullOrEmpty(FileName))
{
string destFileName = url + dr["AttacheFile"].ToString();
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
cells[i + templateIndex, 10].PutValue(FileName);
cells[i + templateIndex, 10].SetStyle(style);
sheet.Hyperlinks.Add(i + templateIndex, 10, 1, 1, destFileName);
cells.Merge(i + templateIndex, 10, 1, 3);
}
}
}
#endregion
}
/// <summary>
/// 填充頁面數據
/// </summary>
/// <param name="sheet">worksheet</param>
/// <param name="docno">試產主單號</param>
/// <param name="subdocno">試產從單號</param>
private void BindExcel(ref Aspose.Cells.Worksheet sheet, string caseID, string Bu, string Building, string DocNo)
{
//page 格式設定
SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape);
Aspose.Cells.Cells cells = sheet.Cells;
//string logoPath = Page.Server.MapPath("") + "\\log.png";
//sheet.Pictures.Add(0, 0, 4, 10, logoPath);
NPIMgmt oMgmt = new NPIMgmt("CZ", Bu);
NPI_Standard oStandard = oMgmt.InitialLeaveMgmt();
#region//獲取主表資訊
DataTable dtMaster = oStandard.GetDFXInconformity(DocNo, "", "");
//string xmlReason = string.Empty;
//string xmlPMC = string.Empty;
//string xmlResult = string.Empty;
//string xmlReasonDetail = string.Empty;
if (dtMaster.Rows.Count > 0)
{
int templateIndexDFX = 5; //模板row起始位置
int insertIndexEnCounter = templateIndexDFX + 1; //new row起始位置
cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1);
cells.CopyRows(cells, templateIndexDFX, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行
for (int i = 0; i < dtMaster.Rows.Count; i++)
{
DataRow dr = dtMaster.Rows[i];
cells[i + templateIndexDFX, 1].PutValue(dr["ItemType"].ToString());
cells[i + templateIndexDFX, 2].PutValue(dr["Item"].ToString());
cells[i + templateIndexDFX, 3].PutValue(dr["Location"].ToString());
cells[i + templateIndexDFX, 4].PutValue(dr["Requirements"].ToString());
cells[i + templateIndexDFX, 5].PutValue("");
cells[i + templateIndexDFX, 6].PutValue(dr["Compliance"].ToString());
cells[i + templateIndexDFX, 7].PutValue(dr["PriorityLevel"].ToString());
cells[i + templateIndexDFX, 8].PutValue(dr["MaxPoints"].ToString());
cells[i + templateIndexDFX, 9].PutValue(dr["DFXPoints"].ToString());
cells[i + templateIndexDFX, 10].PutValue(dr["Comments"].ToString());
cells[i + templateIndexDFX, 11].PutValue(dr["Actions"].ToString());
cells[i + templateIndexDFX, 12].PutValue(dr["CompletionDate"].ToString().Length > 0 ? Convert.ToDateTime(dr["CompletionDate"].ToString()).ToString("yyyy/MM/dd"):dr["CompletionDate"].ToString());
cells[i + templateIndexDFX, 13].PutValue(dr["Tracking"].ToString());
cells[i + templateIndexDFX, 14].PutValue(dr["Remark"].ToString());
cells[i + templateIndexDFX, 15].PutValue(dr["WriteDept"].ToString());
}
}
#endregion
}
private void BindPFMA(ref Aspose.Cells.Worksheet sheet, string caseID, string Bu, string Building, string DocNo)
{
//page 格式設定
SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape);
Aspose.Cells.Cells cells = sheet.Cells;
//string logoPath = Page.Server.MapPath("") + "\\log.png";
//sheet.Pictures.Add(0, 0, 4, 10, logoPath);
NPIMgmt oMgmt = new NPIMgmt("CZ", Bu);
NPI_Standard oStandard = oMgmt.InitialLeaveMgmt();
#region//獲取主表資訊
DataTable dtMaster = oStandard.GetFMEAInconformity(DocNo, "", "", "");
if (dtMaster.Rows.Count > 0)
{
int templateIndexDFX = 6; //模板row起始位置
int insertIndexEnCounter = templateIndexDFX + 1; //new row起始位置
cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1);
cells.CopyRows(cells, templateIndexDFX, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行
for (int i = 0; i < dtMaster.Rows.Count; i++)
{
DataRow dr = dtMaster.Rows[i];
cells[i + templateIndexDFX, 1].PutValue(dr["Item"].ToString());
cells[i + templateIndexDFX, 2].PutValue(dr["Stantion"].ToString());
cells[i + templateIndexDFX, 3].PutValue(dr["Source"].ToString());
cells[i + templateIndexDFX, 4].PutValue(dr["Source"].ToString());
cells[i + templateIndexDFX, 5].PutValue(dr["PotentialFailureMode"].ToString());
cells[i + templateIndexDFX, 6].PutValue(dr["Loess"].ToString());
cells[i + templateIndexDFX, 8].PutValue(dr["Loess"].ToString());
cells[i + templateIndexDFX, 7].PutValue(dr["Sev"].ToString());
cells[i + templateIndexDFX, 8].PutValue(dr["Occ"].ToString());
cells[i + templateIndexDFX, 9].PutValue(dr["DET"].ToString());
cells[i + templateIndexDFX, 10].PutValue(dr["RPN"].ToString());
cells[i + templateIndexDFX, 11].PutValue(dr["PotentialFailure"].ToString());
cells[i + templateIndexDFX, 12].PutValue(dr["TargetCompletionDate"].ToString().Length > 0 ? Convert.ToDateTime(dr["TargetCompletionDate"].ToString()).ToString("yyyy/MM/dd") : dr["TargetCompletionDate"].ToString());
cells[i + templateIndexDFX, 13].PutValue(dr["ActionsTaken"].ToString());
cells[i + templateIndexDFX, 14].PutValue(dr["ResultsSev"].ToString());
cells[i + templateIndexDFX, 15].PutValue(dr["ResultsOcc"].ToString());
cells[i + templateIndexDFX, 16].PutValue(dr["ResultsDet"].ToString());
cells[i + templateIndexDFX, 17].PutValue(dr["ResultsRPN"].ToString());
cells[i + templateIndexDFX, 18].PutValue(dr["WriteDept"].ToString());
}
}
#endregion
}
private void BindHomePage(ref Aspose.Cells.Worksheet sheet, string caseID, string Bu, string Building, string DocNo)
{
//page 格式設定
SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape);
Aspose.Cells.Cells cells = sheet.Cells;
//string logoPath = Page.Server.MapPath("") + "\\log.png";
//sheet.Pictures.Add(0, 0, 4, 10, logoPath);
NPIMgmt oMgmt = new NPIMgmt("CZ", Bu);
NPI_Standard oStandard = oMgmt.InitialLeaveMgmt();
#region//獲取主表資訊
DataTable dtMaster = oStandard.GetCLCAInconformity(DocNo, "", "");
if (dtMaster.Rows.Count > 0)
{
int templateIndexDFX = 7; //模板row起始位置
int insertIndexEnCounter = templateIndexDFX + 1; //new row起始位置
cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1);
cells.CopyRows(cells, templateIndexDFX, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行
for (int i = 0; i < dtMaster.Rows.Count; i++)
{
DataRow dr = dtMaster.Rows[i];
cells[i + templateIndexDFX, 1].PutValue(dr["PROCESS"].ToString());
cells[i + templateIndexDFX, 2].PutValue(dr["CTQ"].ToString());
cells[i + templateIndexDFX, 3].PutValue(dr["CONTROL_TYPE"].ToString());
cells[i + templateIndexDFX, 4].PutValue(dr["ACT"].ToString());
cells[i + templateIndexDFX, 5].PutValue(dr["RESULT"].ToString());
cells[i + templateIndexDFX, 6].PutValue(dr["DESCRIPTION"].ToString());
cells[i + templateIndexDFX, 7].PutValue(dr["ROOT_CAUSE"].ToString());
cells[i + templateIndexDFX, 8].PutValue(dr["D"].ToString());
cells[i + templateIndexDFX, 9].PutValue(dr["M"].ToString());
cells[i + templateIndexDFX, 10].PutValue(dr["P"].ToString());
cells[i + templateIndexDFX, 11].PutValue(dr["E"].ToString());
cells[i + templateIndexDFX, 12].PutValue(dr["W"].ToString());
cells[i + templateIndexDFX, 13].PutValue(dr["O"].ToString());
cells[i + templateIndexDFX, 14].PutValue(dr["TEMPORARY_ACTION"].ToString());
cells[i + templateIndexDFX, 15].PutValue(dr["CORRECTIVE_PREVENTIVE_ACTION"].ToString());
cells[i + templateIndexDFX, 16].PutValue(dr["COMPLETE_DATE"].ToString().Length > 0 ? Convert.ToDateTime(dr["COMPLETE_DATE"].ToString()).ToString("yyyy/MM/dd") : dr["COMPLETE_DATE"].ToString());
cells[i + templateIndexDFX, 17].PutValue(dr["IMPROVEMENT_STATUS"].ToString());
}
}
#endregion
}
private void BindIssuesList(ref Aspose.Cells.Worksheet sheet, string caseID, string Bu, string Building, string DocNo)
{
//page 格式設定
SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape);
Aspose.Cells.Cells cells = sheet.Cells;
//string logoPath = Page.Server.MapPath("") + "\\log.png";
//sheet.Pictures.Add(0, 0, 4, 10, logoPath);
NPIMgmt oMgmt = new NPIMgmt("CZ", Bu);
NPI_Standard oStandard = oMgmt.InitialLeaveMgmt();
#region//獲取主表資訊
DataTable dtMaster = oStandard.GetIssuesInconformity(DocNo, "", "");
if (dtMaster.Rows.Count > 0)
{
int templateIndexDFX = 6; //模板row起始位置
int insertIndexEnCounter = templateIndexDFX + 1; //new row起始位置
cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1);
cells.CopyRows(cells, templateIndexDFX, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行
for (int i = 0; i < dtMaster.Rows.Count; i++)
{
DataRow dr = dtMaster.Rows[i];
cells[i + templateIndexDFX, 1].PutValue(dr["Items"].ToString());
cells[i + templateIndexDFX, 2].PutValue(dr["STATION"].ToString());
cells[i + templateIndexDFX, 3].PutValue(dr["ISSUE_DESCRIPTION"].ToString());
cells[i + templateIndexDFX, 4].PutValue(dr["FILE_PATH"].ToString());
cells[i + templateIndexDFX, 5].PutValue(dr["ISSUE_LOSSES"].ToString());
cells[i + templateIndexDFX, 6].PutValue(dr["TEMP_MEASURE"].ToString());
cells[i + templateIndexDFX, 7].PutValue(dr["IMPROVE_MEASURE"].ToString());
cells[i + templateIndexDFX, 8].PutValue(dr["CURRENT_STATUS"].ToString());
cells[i + templateIndexDFX, 9].PutValue(dr["TRACKING"].ToString());
cells[i + templateIndexDFX, 10].PutValue(dr["REMARK"].ToString());
}
}
#endregion
}
private void BindHomePage(ref Aspose.Cells.Worksheet sheet, string caseID, string Bu)
{
//page 格式設定
SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape);
Aspose.Cells.Cells cells = sheet.Cells;
NPIMgmt oMgmt = new NPIMgmt("CZ", Bu);
NPI_Standard oStandard = oMgmt.InitialLeaveMgmt();
#region 主檔資訊
DataTable dt = oStandard.GetPrelaunchMaster(caseID);
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
sheet.Replace("{FormNo}", dr["PilotRunNO"].ToString());
sheet.Replace("{Model}", dr["Model"].ToString());
sheet.Replace("{Customer}", dr["Customer"].ToString());
sheet.Replace("{PCBRev}", dr["PCBInRev"].ToString());
sheet.Replace("{Rev}", dr["PLRev"].ToString());
sheet.Replace("{Date}", Convert.ToDateTime(dr["Date"].ToString()).ToString("yyyy/MM/dd"));
sheet.Replace("{TPME}", dr["TP_ME"].ToString());
sheet.Replace("{TPEE}", dr["TP_EE"].ToString());
sheet.Replace("{TPPM}", dr["TP_PM"].ToString());
sheet.Replace("{PM}", dr["PM"].ToString());
sheet.Replace("{PCBORev}", dr["PCBOutRev"].ToString());
sheet.Replace("{Attection}", dr["Notes"].ToString());
sheet.Replace("{WorkOrder}", dr["WorkOrder"].ToString());
}
DataTable dtResult = GetNPIManager(caseID);
DataRow drResult = dtResult.Rows[0];
sheet.Replace("{FinalResult}", drResult["APPROVE_RESULT"].ToString() == "Y" ? "Pass" : "Fail");
#endregion
#region 簽核資訊
DataTable dtApprover = oStandard.GetPrelaunchApproveResult(caseID);
if (dtApprover.Rows.Count > 0)
{
int templateIndex = 17; //模板row起始位置
int insertIndexEnCounter = templateIndex + 1; //new row起始位置
cells.InsertRows(insertIndexEnCounter, dtApprover.Rows.Count - 1);
cells.CopyRows(cells, templateIndex, insertIndexEnCounter, dtApprover.Rows.Count - 1); //複製模板row格式至新行
for (int i = 0; i < dtApprover.Rows.Count; i++)
{
DataRow dr = dtApprover.Rows[i];
cells[i + templateIndex, 0].PutValue(dr["STEP_NAME"].ToString());
cells[i + templateIndex, 1].PutValue(dr["DEPT"].ToString());
cells[i + templateIndex, 2].PutValue(dr["HANDLER"].ToString());
cells[i + templateIndex, 3].PutValue(dr["APPROVE_TIME"].ToString().Length > 0 ? Convert.ToDateTime(dr["APPROVE_TIME"].ToString()).ToString("yyyy/MM/dd") : dr["APPROVE_TIME"].ToString());
cells[i + templateIndex, 4].PutValue(dr["APPROVE_RESULT"].ToString().Length > 0 ? dr["APPROVE_RESULT"].ToString() : "Y");
cells[i + templateIndex, 5].PutValue(dr["APPROVE_REMARK"].ToString());
}
}
#endregion
}