public String getReport1Title(ReportSetViewModel model) { string ret = ""; var qry = db.WBSEventC.Where(x => x.CreateDateTime.Value.CompareTo(model.CreateDTStart) >= 0 && x.CreateDateTime.Value.CompareTo(model.CreateDTEnd) <= 0); if (model.CGroupID != "" && model.CGroupID != null) //客戶群 { ret += db.CustomerGroup.Where(x => x.CGroupID == model.CGroupID).Select(x => x.CGroupShortName).First() + "-"; } if (model.CustomerRef != "" && model.CustomerRef != null) //客戶簡稱 { int temp = Int32.Parse(model.CustomerRef); ret += db.Customer.Where(x => x.CustomerRef == temp).Select(x => x.ShortName).First() + "-"; } ret += "客戶叫修服務報表"; if (model.RType == 1) { ret += "-內部檢核"; } ret += "(叫修日期:" + String.Format("{0:00}", model.CreateDTStart.Year) + "/" + String.Format("{0:00}", model.CreateDTStart.Month) + "/" + String.Format("{0:00}", model.CreateDTStart.Day) + "~" + String.Format("{0:00}", model.CreateDTEnd.Year) + "/" + String.Format("{0:00}", model.CreateDTEnd.Month) + "/" + String.Format("{0:00}", model.CreateDTEnd.Day) + ")"; return ret; }
public List<Report1ViewModel> getReport1(ReportSetViewModel model) { List<Report1ViewModel> ret = new List<Report1ViewModel>(); model.CreateDTEnd = model.CreateDTEnd.AddHours(23).AddMinutes(59).AddSeconds(59); var qry = db.WBSEventC.Where(x => x.CreateDateTime.Value.CompareTo(model.CreateDTStart) >= 0 && x.CreateDateTime.Value.CompareTo(model.CreateDTEnd) <= 0); if(model.EventClass == "C01&M01") qry = qry.Where(x => x.EventClass == "C01" || x.EventClass == "M01"); else qry = qry.Where(x => x.EventClass == model.EventClass); if (model.CaseClass == 1) //只找母單 qry = qry.Where(x => x.ChildCaseID == 1); if (model.CGroupID != "" && model.CGroupID != null) //客戶群 { qry = qry.Where(x => x.WBSEventM.Customer1.GroupID == model.CGroupID); } if (model.CloseSel == 1) //結案檢核:已結案 { qry = qry.Where(x => x.WBSEventM.CaseMStatus == "9"); } else if (model.CloseSel == 2) //結案檢核:未結案 { qry = qry.Where(x => x.WBSEventM.CaseMStatus != "9"); } if (model.CustomerRef != "" && model.CustomerRef != null) //客戶簡稱 { int temp = Int32.Parse(model.CustomerRef); qry = qry.Where(x => x.WBSEventM.CustomerRef == temp); } if (model.ArriveSel == 1) //到達檢核:準時 { } else if (model.ArriveSel == 2) //到達檢核:逾時 { } if (model.ProductClassID != null && model.ProductClassID != 0) //客戶簡稱 { qry = qry.Where(x => x.ProductClass == model.ProductClassID); } foreach (WBSEventC c in qry) { Report1ViewModel v = new Report1ViewModel(); v.CustomerSName = c.WBSEventM.Customer1.ShortName; v.CustomerRef = c.WBSEventM.CustomerRef; v.CustomerMNum = ""; v.CreateDate = String.Format("{0:00}", c.CreateDateTime.Value.Year) + "/" + String.Format("{0:00}", c.CreateDateTime.Value.Month) + "/" + String.Format("{0:00}", c.CreateDateTime.Value.Day); v.CreateTime = String.Format("{0:00}", c.CreateDateTime.Value.Hour) + ":" + String.Format("{0:00}", c.CreateDateTime.Value.Minute); //v.CreateDate = c.CreateDateTime.Value.ToString("yyyy/MM/dd"); //v.CreateTime = c.CreateDateTime.Value.ToString("HH:mm"); if (c.ExpectArriveSW == "1") v.ExpectArriveSWStr = "合約規定"; else if (c.ExpectArriveSW == "2") v.ExpectArriveSWStr = "建議期望"; else if (c.ExpectArriveSW == "3") v.ExpectArriveSWStr = "客戶指定"; else if (c.ExpectArriveSW == "4") v.ExpectArriveSWStr = "自派新單"; else { v.ExpectArriveSWStr = "自行約定"; } v.ProductNote = c.ProductClass1.Note; v.ProductClass = c.ProductClass; v.ItemNote = c.ItemClass1.Note; v.ItemClass = c.ItemClass; v.QuestionDescription = c.QuestionDescription; v.QuestionNote = c.QuestionClass1.Note; v.QuestionClass = c.QuestionClass; v.ServiceReport = c.ServiceReport; if(c.ServiceCreateUserID != null) v.ServiceUserName = c.UserData8.UserName; v.WBSCaseNumberM = c.WBSEventM.CaseClass + c.WBSEventM.CaseDate + "-"; if (c.WBSEventM.CaseCode < 10) v.WBSCaseNumberM += "0000" + c.WBSEventM.CaseCode.ToString(); else if (c.WBSEventM.CaseCode < 100 && c.WBSEventM.CaseCode >= 10) v.WBSCaseNumberM += "000" + c.WBSEventM.CaseCode.ToString(); else if (c.WBSEventM.CaseCode < 1000 && c.WBSEventM.CaseCode >= 100) v.WBSCaseNumberM += "00" + c.WBSEventM.CaseCode.ToString(); else if (c.WBSEventM.CaseCode < 10000 && c.WBSEventM.CaseCode >= 1000) v.WBSCaseNumberM += "0" + c.WBSEventM.CaseCode.ToString(); else v.WBSCaseNumberM += c.WBSEventM.CaseCode.ToString(); v.WBSCaseNumberC = (c.ChildCaseID < 10) ? ("0" + c.ChildCaseID.ToString()) : c.ChildCaseID.ToString(); v.isClosedStr = "未結案"; v.ClosedMinute = ""; if (c.WBSEventM.CaseMStatus == "9") { v.isClosedStr = "已結案"; System.TimeSpan diff1 = c.WBSEventM.CloseDT.Value.Subtract(c.CreateDateTime.Value); int d = diff1.Days; int h = d * 24 + diff1.Hours; int m = h * 60 + diff1.Minutes; v.ClosedMinute = m.ToString(); //int min = Int32.Parse(diff1.ToString("%d")); } v.ArriveDate = ""; v.ArriveTime = ""; v.ArriveMinute = ""; if (c.ServiceArriveDT != null) { v.ArriveDate = String.Format("{0:00}", c.ServiceArriveDT.Value.Year) + "/" + String.Format("{0:00}", c.ServiceArriveDT.Value.Month) + "/" + String.Format("{0:00}", c.ServiceArriveDT.Value.Day); v.ArriveTime = String.Format("{0:00}", c.ServiceArriveDT.Value.Hour) + ":" + String.Format("{0:00}", c.ServiceArriveDT.Value.Minute); //v.ArriveDate = c.ServiceArriveDT.Value.ToString("yyyy/MM/dd"); //v.ArriveTime = c.ServiceArriveDT.Value.ToString("HH:mm"); System.TimeSpan diff1 = c.ServiceArriveDT.Value.Subtract(c.CreateDateTime.Value); int d = diff1.Days; int h = d * 24 + diff1.Hours; int m = h * 60 + diff1.Minutes; //if (diff1.Days > 0) // v.ArriveMinute += diff1.Days.ToString() + "日"; //if (diff1.Days > 0 || diff1.Hours > 0) // v.ArriveMinute += diff1.Hours.ToString() + "時"; //v.ArriveMinute += diff1.Minutes.ToString() + "分"; v.ArriveMinute = m.ToString(); } v.LeaveDate = ""; v.LeaveTime = ""; v.LeaveMinute = ""; if (c.ServiceLeaveDT != null) { v.LeaveDate = String.Format("{0:00}", c.ServiceLeaveDT.Value.Year) + "/" + String.Format("{0:00}", c.ServiceLeaveDT.Value.Month) + "/" + String.Format("{0:00}", c.ServiceLeaveDT.Value.Day); v.LeaveTime = String.Format("{0:00}", c.ServiceLeaveDT.Value.Hour) + ":" + String.Format("{0:00}", c.ServiceLeaveDT.Value.Minute); //v.LeaveDate = c.ServiceLeaveDT.Value.ToString("yyyy/MM/dd"); //v.LeaveTime = c.ServiceLeaveDT.Value.ToString("HH:mm"); System.TimeSpan diff1 = c.ServiceLeaveDT.Value.Subtract(c.CreateDateTime.Value); int d = diff1.Days; int h = d * 24 + diff1.Hours; int m = h * 60 + diff1.Minutes; v.LeaveMinute = m.ToString(); } //v.ArriveOnTimeRequirement = ""; v.isArriveOnTime = ""; v.isLeaveOnTime = ""; //v.LeaveOnTimeRequirement = ""; if (db.SystemSetting.Where(x => x.EventClass == model.EventClass).Count() > 0) { var qry2 = db.SystemSetting.Where(x => x.EventClass == model.EventClass).First(); v.ArriveOnTimeRequirement = model.EventClass + "-" + qry2.SugArriveMin.ToString() + "分"; v.LeaveOnTimeRequirement = model.EventClass + "-" + qry2.SugFinishMin.ToString() + "分"; if(v.ArriveMinute != "") { if(Int32.Parse(v.ArriveMinute) <= qry2.SugArriveMin) { v.isArriveOnTime = "系統準時"; if (model.ArriveSel == 2) continue; } else { v.isArriveOnTime = "系統未準時"; if (model.ArriveSel == 1) continue; } } if (v.LeaveMinute != "") { if (Int32.Parse(v.LeaveMinute) <= qry2.SugFinishMin) { v.isLeaveOnTime = "系統準時"; if (model.LeaveSel == 2) continue; } else { v.isLeaveOnTime = "系統未準時"; if (model.LeaveSel == 1) continue; } } } else { var qry2 = db.SystemSetting.Where(x => x.isSysDefault == true).First(); v.ArriveOnTimeRequirement = "系統-" + qry2.SugArriveMin.ToString() + "分"; v.LeaveOnTimeRequirement = "系統-" + qry2.SugFinishMin.ToString() + "分"; if (v.ArriveMinute != "") { if (Int32.Parse(v.ArriveMinute) <= qry2.SugArriveMin) { v.isArriveOnTime = "系統準時"; if (model.ArriveSel == 2) continue; } else { v.isArriveOnTime = "系統未準時"; if (model.ArriveSel == 1) continue; } } if (v.LeaveMinute != "") { if (Int32.Parse(v.LeaveMinute) <= qry2.SugFinishMin) { v.isLeaveOnTime = "系統準時"; if (model.LeaveSel == 2) continue; } else { v.isLeaveOnTime = "系統未準時"; if (model.LeaveSel == 1) continue; } } } if (model.ArriveSel != 0) { if (v.ArriveMinute == "") continue; } if (model.LeaveSel != 0) { if(v.LeaveMinute == "") continue; } v.ClosedOnLineStr = "否"; //線上結案 if (c.ServiceLeaveDT == null && c.QuestionFinishSW == "Y") { v.ClosedOnLineStr = "是"; v.isLeaveOnTime = "系統準時"; v.LeaveMinute = "0"; v.LeaveTime = v.CreateTime; v.LeaveDate = v.CreateDate; v.ServiceUserName = c.UserData.UserName; } ret.Add(v); } return ret; }
//public ActionResult Report_Create(ReportSetViewModel model, string ReportIndex) public void Report_Create(ReportSetViewModel model, string ReportIndex) { ViewBag.ReportIndex = ReportIndex; string ReportNo = "ReportTemplate1"; if (ReportIndex.Equals("1")) { ReportNo = "ReportTemplate1"; } string strHtml = ""; string RepName = ""; var wb = new XLWorkbook(); switch (ReportIndex) { #region "客戶叫修服務報表" case "1": string RegStatusName = ""; if (model.RType == 0) { RegStatusName = ""; } else { RegStatusName = "-內部檢核"; } RepName = "客戶叫修服務報表" + RegStatusName; wb = Report1_Create(model, RepName); break; #endregion default: return; } // 匯出檔名 var browser = Request.Browser.Browser; var exportFileName = browser.Equals("Firefox", StringComparison.OrdinalIgnoreCase) ? RepName + ".xls" : HttpUtility.UrlEncode(RepName + ".xls", Encoding.UTF8); Response.AddHeader( "Content-Disposition", string.Format("attachment;filename={0}", exportFileName)); //wb.SaveAs(Response.OutputStream); wb.SaveAs(Response.OutputStream); //return wb.SaveAs(Response.OutputStream); //return File(Response.OutputStream, "application/vnd.ms-excel", RepName + ".xls"); }
private XLWorkbook Report1_Create(ReportSetViewModel model, string RepName) { var workbook = new XLWorkbook(); var Data = reportRespositiory.getReport1(model); #region 叫-叫修(工作表1) var sheet = workbook.Worksheets.Add("叫-叫修"); int row = 3; int col = 15; double[] Colwidth = { 5, 11, 15, 20, 20, 11, 10, 11, 10, 11, 10, 11, 5, 16, 16}; double[] Colwidth2 = { 5, 11, 15, 20, 20, 11, 10, 11, 10, 11, 10, 11, 5, 16, 16, 5, 11, 16, 15, 15, 15, 12, 15, 19, 14, 19, 12, 7, 15, 20}; if(model.RType == 0) { for (int i = 0; i < Colwidth.Length; i++) { sheet.Column(i + 1).Width = Colwidth[i]; } } else { for (int i = 0; i < Colwidth2.Length; i++) { sheet.Column(i + 1).Width = Colwidth2[i]; } } sheet.Style.Font.FontName = "微軟正黑體"; sheet.Style.Font.FontSize = 12; sheet.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; sheet.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; sheet.PageSetup.SetRowsToRepeatAtTop(1, 4); sheet.PageSetup.SetColumnsToRepeatAtLeft(1, 11); var row1 = sheet.Row(1); var row2 = sheet.Row(2); //var row3 = sheet.Row(3); row1.Style.Font.FontSize = 14; row1.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; row1.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; row2.Style.Font.FontSize = 14; row2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; row2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; //row3.Style.Font.FontSize = 14; //row3.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //row3.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; sheet.PageSetup.PageOrientation = XLPageOrientation.Portrait; sheet.PageSetup.PaperSize = XLPaperSize.A4Paper; sheet.PageSetup.Margins.Bottom = 0.4; sheet.PageSetup.Margins.Top = 0.4; sheet.PageSetup.Margins.Left = 0.2; sheet.PageSetup.Margins.Right = 0.2; sheet.PageSetup.CenterHorizontally = true; sheet.Row(3).Height = 50; sheet.Cell(3, 1).Value = "序號"; sheet.Cell(3, 2).Value = "客戶簡稱"; //sheet.Cell(3, 3).Style.Alignment.WrapText = true; sheet.Cell(3, 3).Value = "產品大類"; sheet.Cell(3, 4).Value = "問題描述"; sheet.Cell(3, 5).Value = "處理說明"; sheet.Cell(3, 6).Value = "叫修日期"; sheet.Cell(3, 7).Value = "叫修時間"; sheet.Cell(3, 8).Value = "到修日期"; sheet.Cell(3, 9).Value = "到修時間"; sheet.Cell(3, 10).Value = "修復日期"; sheet.Cell(3, 11).Value = "修復時間"; sheet.Cell(3, 12).Value = "維護人員"; sheet.Cell(3, 13).Value = ""; sheet.Cell(3, 14).Style.Alignment.WrapText = true; sheet.Cell(3, 14).Value = "叫修-到場時間\r\n(分)(開單-到達)"; sheet.Cell(3, 15).Style.Alignment.WrapText = true; sheet.Cell(3, 15).Value = "叫修-完修時間\r\n(分)(開單-解決)"; if (model.RType != 0) { sheet.Cell(3, 16).Value = ""; sheet.Cell(3, 17).Value = "線上排除"; sheet.Cell(3, 18).Value = "客戶維修編號"; sheet.Cell(3, 19).Value = "產品大類"; sheet.Cell(3, 20).Value = "設備分類"; sheet.Cell(3, 21).Value = "問題分類"; sheet.Cell(3, 22).Style.Alignment.WrapText = true; sheet.Cell(3, 22).Value = "到達方式\r\n(自行約定)"; sheet.Cell(3, 23).Style.Alignment.WrapText = true; sheet.Cell(3, 23).Value = "是否準時到場\r\n(1.合約2.系統)"; sheet.Cell(3, 24).Style.Alignment.WrapText = true; sheet.Cell(3, 24).Value = "準時到場判斷基礎\r\n(開單-到達 01單)"; sheet.Cell(3, 25).Value = "是否準時修復"; sheet.Cell(3, 26).Style.Alignment.WrapText = true; sheet.Cell(3, 26).Value = "準時完修判斷基礎\r\n(開單-解決 01單)"; sheet.Cell(3, 27).Value = "單號"; sheet.Cell(3, 28).Value = "子單"; sheet.Cell(3, 29).Value = "結案否(內控)"; sheet.Cell(3, 30).Style.Alignment.WrapText = true; sheet.Cell(3, 30).Value = "結案所需時間(分)\r\n(內控)(開單-結案)"; col = 30; } if (Data.Count() == 0) { var row4 = sheet.Row(4); row4.Style.Font.FontSize = 14; row4.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; row4.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; sheet.Range(4, 1, 4, col).Merge(true); sheet.Cell(4, 1).Value = "查無資料"; row++; } else { int c = 1; foreach (var o in Data) { row++; sheet.Cell(row, 1).Value = c; sheet.Cell(row, 2).Value = o.CustomerSName; sheet.Cell(row, 3).Value = o.ProductNote; sheet.Cell(row, 4).Value = o.QuestionDescription; sheet.Cell(row, 5).Value = o.ServiceReport; sheet.Cell(row, 6).Value = o.CreateDate; sheet.Cell(row, 7).Style.DateFormat.Format = "hh:mm"; sheet.Cell(row, 7).Value = o.CreateTime; sheet.Cell(row, 8).Value = o.ArriveDate; sheet.Cell(row, 9).Style.DateFormat.Format = "hh:mm"; sheet.Cell(row, 9).Value = o.ArriveTime; sheet.Cell(row, 10).Value = o.LeaveDate; sheet.Cell(row, 11).Style.DateFormat.Format = "hh:mm"; sheet.Cell(row, 11).Value = o.LeaveTime; sheet.Cell(row, 12).Value = o.ServiceUserName; sheet.Cell(row, 13).Value = ""; sheet.Cell(row, 14).Value = o.ArriveMinute; sheet.Cell(row, 15).Value = o.LeaveMinute; if (model.RType != 0) { sheet.Cell(row, 16).Value = ""; sheet.Cell(row, 17).Value = o.ClosedOnLineStr; sheet.Cell(row, 18).Value = o.CustomerMNum; sheet.Cell(row, 19).Value = o.ProductNote; sheet.Cell(row, 20).Value = o.ItemNote; sheet.Cell(row, 21).Value = o.QuestionNote; sheet.Cell(row, 22).Value = o.ExpectArriveSWStr; sheet.Cell(row, 23).Value = o.isArriveOnTime; sheet.Cell(row, 24).Value = o.ArriveOnTimeRequirement; sheet.Cell(row, 25).Value = o.isLeaveOnTime; sheet.Cell(row, 26).Value = o.LeaveOnTimeRequirement; sheet.Cell(row, 27).Value = o.WBSCaseNumberM; sheet.Cell(row, 28).Value = o.WBSCaseNumberC; sheet.Cell(row, 29).Value = o.isClosedStr; sheet.Cell(row, 30).Value = o.ClosedMinute; } c++; } var range3 = sheet.Range(4, 1, row, col); range3.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; range3.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; range3.Style.Alignment.WrapText = true; } var range2 = sheet.Range(3, 1, row, col); range2.Style.Border.OutsideBorder = XLBorderStyleValues.Thick; range2.Style.Border.InsideBorder = XLBorderStyleValues.Thick; range2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; range2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; sheet.Range(1, 1, 1, col).Merge(true); sheet.Range(2, 1, 2, col).Merge(true); //sheet.Range(3, 1, 3, col).Merge(true); //sheet.Cell(3, 1).Value = data.ProgramScript; sheet.Range(3, 1, 3, col).Style.Fill.BackgroundColor = XLColor.Yellow; sheet.Range(3, 16, 3, col).Style.Fill.BackgroundColor = XLColor.GreenPigment; sheet.Cell(1, 1).Value = "NSMMS-客戶叫修服務系統"; string title = ""; title = RepName; sheet.Cell(2, 1).Value = reportRespositiory.getReport1Title(model); //sheet.Cell(3, 1).Value = data.RegScript; string str = "製表日期:" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now); sheet.Range(row + 1, 1, row + 1, col).Merge(true); sheet.Cell(row + 1, 1).Value = str; sheet.Row(row + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; #endregion #region 叫號維護統計(工作表2) var Data2 = reportRespositiory.getReport1S2(Data); var sheet2 = workbook.Worksheets.Add("叫號維護統計"); int s2row = 4; int s2col = 7; double[] s2Colwidth = { 16, 16, 20, 6, 6, 6, 6}; for (int i = 0; i < s2Colwidth.Length; i++) { sheet2.Column(i + 1).Width = s2Colwidth[i]; } sheet2.Style.Font.FontName = "微軟正黑體"; sheet2.Style.Font.FontSize = 12; sheet2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; sheet2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; sheet2.PageSetup.SetRowsToRepeatAtTop(1, 4); sheet2.PageSetup.SetColumnsToRepeatAtLeft(1, 11); var s2row1 = sheet2.Row(1); var s2row2 = sheet2.Row(2); var s2row3 = sheet2.Row(3); s2row1.Style.Font.FontSize = 14; s2row1.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; s2row1.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; s2row2.Style.Font.FontSize = 14; s2row2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; s2row2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; s2row3.Style.Font.FontSize = 14; s2row3.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; s2row3.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; sheet2.PageSetup.PageOrientation = XLPageOrientation.Portrait; sheet2.PageSetup.PaperSize = XLPaperSize.A4Paper; sheet2.PageSetup.Margins.Bottom = 0.4; sheet2.PageSetup.Margins.Top = 0.4; sheet2.PageSetup.Margins.Left = 0.2; sheet2.PageSetup.Margins.Right = 0.2; sheet2.PageSetup.CenterHorizontally = true; //sheet.Row(4).Height = 31.5; sheet2.Cell(4, 1).Value = "產品大類"; sheet2.Cell(4, 2).Value = "設備分類"; //sheet.Cell(4, 3).Style.Alignment.WrapText = true; sheet2.Cell(4, 3).Value = "問題分類"; sheet2.Cell(4, 4).Value = "數量"; sheet2.Cell(4, 5).Value = "小計"; sheet2.Cell(4, 6).Value = "合計"; sheet2.Cell(4, 7).Value = "總計"; if (Data2.Count() == 0) { var row5 = sheet2.Row(5); row5.Style.Font.FontSize = 14; row5.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; row5.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; sheet2.Range(5, 1, 5, s2col).Merge(true); sheet2.Cell(5, 1).Value = "查無資料"; s2row++; } else { int c = 1; foreach (var o in Data2) { s2row++; sheet2.Cell(s2row, 1).Value = o.ProductNote; sheet2.Cell(s2row, 2).Value = o.ItemNote; sheet2.Cell(s2row, 3).Value = o.QuestionNote; sheet2.Cell(s2row, 4).Value = o.Quantity; sheet2.Cell(s2row, 5).Value = o.Sum1; sheet2.Cell(s2row, 6).Value = o.Sum2; sheet2.Cell(s2row, 7).Value = o.Sum3; c++; } var range3 = sheet2.Range(5, 1, s2row, s2col); range3.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; range3.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; range3.Style.Alignment.WrapText = true; } var s2range2 = sheet2.Range(4, 1, s2row, s2col); s2range2.Style.Border.OutsideBorder = XLBorderStyleValues.Thick; s2range2.Style.Border.InsideBorder = XLBorderStyleValues.Thick; s2range2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; s2range2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; sheet2.Range(1, 1, 1, s2col).Merge(true); sheet2.Range(2, 1, 2, s2col).Merge(true); sheet2.Range(3, 1, 3, s2col).Merge(true); //sheet.Cell(3, 1).Value = data.ProgramScript; sheet2.Range(4, 1, 4, s2col).Style.Fill.BackgroundColor = XLColor.Gray; sheet2.Cell(1, 1).Value = "依設定出來 (08:00-19:00 11HR * 22天)"; sheet2.Cell(2, 1).Value = String.Format("{0:00}", model.CreateDTStart.Year) + "/" + String.Format("{0:00}", model.CreateDTStart.Month) + "/" + String.Format("{0:00}", model.CreateDTStart.Day) + "~" + String.Format("{0:00}", model.CreateDTEnd.Year) + "/" + String.Format("{0:00}", model.CreateDTEnd.Month) + "/" + String.Format("{0:00}", model.CreateDTEnd.Day) + "多媒體系統叫修狀況統計表"; sheet2.Cell(3, 1).Value = "(TOT:開單-解決)"; //str = "製表日期:" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now); sheet2.Range(s2row + 1, 1, s2row + 1, s2col).Merge(true); sheet2.Cell(s2row + 1, 1).Value = str; sheet2.Row(s2row + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; #endregion #region 統計(工作表3) var Data3 = reportRespositiory.getReport1S3(Data); var sheet3 = workbook.Worksheets.Add("統計"); int s3row = 1; int s3col = 1; List<String> ProductList = Data.OrderBy(x=>x.ProductClass).Select(x => x.ProductNote).Distinct().ToList(); //double[] s2Colwidth = { 16, 16, 20, 6, 6, 6, 6 }; sheet3.Column(1).Width = 22; for (int i = 1; i < ProductList.Count() + 1; i++) { sheet3.Column(i + 1).Width = 16; s3col++; } //sheet3.Style.Font.FontName = "微軟正黑體"; //sheet3.Style.Font.FontSize = 12; sheet3.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; sheet3.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; sheet3.PageSetup.SetRowsToRepeatAtTop(1, 4); sheet3.PageSetup.SetColumnsToRepeatAtLeft(1, 11); sheet3.PageSetup.PageOrientation = XLPageOrientation.Portrait; sheet3.PageSetup.PaperSize = XLPaperSize.A4Paper; sheet3.PageSetup.Margins.Bottom = 0.4; sheet3.PageSetup.Margins.Top = 0.4; sheet3.PageSetup.Margins.Left = 0.2; sheet3.PageSetup.Margins.Right = 0.2; sheet3.PageSetup.CenterHorizontally = true; sheet3.Cell(1, 1).Value = " 產品大類\r\n客戶簡稱"; sheet3.Cell(1, 1).Style.Alignment.WrapText = true; for (int i = 1; i < ProductList.Count() + 1; i++) { sheet3.Cell(1, i + 1).Value = ProductList[i - 1].ToString() + "\r\n叫修"; sheet3.Cell(1, i + 1).Style.Alignment.WrapText = true; } if (Data3.Count() == 0) { var row6 = sheet3.Row(2); row6.Style.Font.FontSize = 14; row6.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; row6.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; sheet3.Range(2, 1, 2, s3col).Merge(true); sheet3.Cell(2, 1).Value = "查無資料"; s3row++; } else { int c = 1; foreach (var o in Data3) { s3row++; sheet3.Cell(s3row, 1).Value = o.CustomerSName; for (int m = 0, n = 2; m < o.sum.Count(); m++, n++) { sheet3.Cell(s3row, n).Value = o.sum[m]; } c++; } var range3 = sheet3.Range(2, 1, s3row, s3col); range3.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; range3.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; range3.Style.Alignment.WrapText = true; } var s3range2 = sheet3.Range(1, 1, s3row, s3col); s3range2.Style.Border.OutsideBorder = XLBorderStyleValues.Thick; s3range2.Style.Border.InsideBorder = XLBorderStyleValues.Thick; s3range2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; s3range2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; sheet3.Cell(1, 1).Style.Border.DiagonalBorder = XLBorderStyleValues.Thin; sheet3.Cell(1, 1).Style.Border.DiagonalDown = true; sheet3.Cell(1, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; sheet3.Cell(1, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; sheet3.Range(s3row + 1, 1, s3row + 1, s3col).Merge(true); sheet3.Cell(s3row + 1, 1).Value = str; sheet3.Row(s3row + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; #endregion return workbook; }
public ActionResult Report1_Read([DataSourceRequest] DataSourceRequest request, string CustomerRef, string CGroupID, DateTime CreateDTStart, DateTime CreateDTEnd, string ProductClassID, string EventClass, int ArriveSel, int LeaveSel, int CloseSel, int CaseClass, int RType) { ReportSetViewModel v = new ReportSetViewModel(); v.ArriveSel = ArriveSel; v.CaseClass = CaseClass; v.CGroupID = CGroupID; v.CloseSel = CloseSel; //if (CreateDTEnd != null) v.CreateDTEnd = CreateDTEnd; //if (CreateDTStart != null) v.CreateDTStart = CreateDTStart; v.CustomerRef = CustomerRef; v.EventClass = EventClass; v.LeaveSel = LeaveSel; if (ProductClassID == "") v.ProductClassID = 0; else v.ProductClassID = Int32.Parse(ProductClassID); v.RType = RType; return Json(reportRespositiory.getReport1(v).ToDataSourceResult(request)); }