protected string GenerateServiceReport(int? orderID) { int signatureMaxWidth = 200, signatureMaxHeight = 180; double excelBorderWidth = 0.01; string returnFilePath = null; IOrderService orderService = AutoSessionServiceFactory.GetOrderService(ApplicationSetting.Current.DefaultConnectionString); string templateFilePath = System.Web.Hosting.HostingEnvironment.MapPath("~/ReportTemplate/REC_CRM_Service_Report_Template.xlsx"); try { SLDocument template = new SLDocument(templateFilePath, "Sheet1"); ServiceReportOrderDTO order = orderService.GetServiceReportOrderByID(orderID.Value); string completionDate = order.dCompletionDate.HasValue ? order.dCompletionDate.Value.ToString("yyyy/MM/dd") : string.Empty; string customerName = string.IsNullOrEmpty(order.sCustomerName) ? string.Empty : order.sCustomerName; string customerCode = string.IsNullOrEmpty(order.sCustomerCode) ? string.Empty : order.sCustomerCode; string workingAddress = string.IsNullOrEmpty(order.sAddress) ? string.Empty : order.sAddress; string contactPerson = string.IsNullOrEmpty(order.sContactPersonName) ? string.Empty : order.sContactPersonName; string model = string.IsNullOrEmpty(order.sModel) ? string.Empty : order.sModel; string fault = string.IsNullOrEmpty(order.sFault) ? string.Empty : order.sFault; string remark = string.IsNullOrEmpty(order.sRemark) ? string.Empty : order.sRemark; string repair = string.IsNullOrEmpty(order.sRepair) ? string.Empty : order.sRepair; string report = string.IsNullOrEmpty(order.sReport) ? string.Empty : order.sReport; string reviewerName = string.IsNullOrEmpty(order.sReviewerName) ? string.Empty : order.sReviewerName; string technicians = string.IsNullOrEmpty(order.sTechnicianNameString) ? string.Empty : order.sTechnicianNameString; string startTime = order.tStartTime.HasValue ? order.tStartTime.Value.ToString(@"hh\:mm") : string.Empty; string endTime = order.tEndTime.HasValue ? order.tEndTime.Value.ToString(@"hh\:mm") : string.Empty; string signDate = order.dReportSignDate.HasValue ? order.dReportSignDate.Value.ToString("yyyy/MM/dd") : string.Empty; string customerOpinion = string.IsNullOrEmpty(order.sCustomerOpinion) ? string.Empty : order.sCustomerOpinion; string receipt = string.IsNullOrEmpty(order.sReceipt) ? string.Empty : order.sReceipt; #region Fill Data List<SLDefinedName> definedNames = template.GetDefinedNames(); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "RepairDate"), completionDate); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerName"), customerName); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerCode"), customerCode); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "WorkingAddress"), workingAddress); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "ContactPerson"), contactPerson); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Model"), model); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Fault"), fault); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Remark"), remark); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Repair"), repair); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Report"), report); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Technicians"), technicians); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "StartTime"), startTime); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "EndTime"), endTime); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "ReviewerName"), reviewerName); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "SignDate"), signDate); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerOpinion"), customerOpinion); template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Receipt"), receipt); #region Insert Signature string signaturePath = ApplicationSetting.Current.RootFolderPath + order.sReportSignPath; string signatureCellReference = this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerSignature"); int signatureColumnIndex = SLConvert.ToColumnIndex(signatureCellReference); int signatureRowIndex = int.Parse(signatureCellReference.Substring(1)); try { //Get Signature Dimension System.Drawing.Bitmap bm = new System.Drawing.Bitmap(signaturePath); double pictureHeight = bm.Height; double pictureWidth = bm.Width; bm.Dispose(); int scale = 0; if (pictureHeight / pictureWidth > ((double)signatureMaxHeight) / ((double)signatureMaxWidth)) { scale = (int)(((double)signatureMaxHeight / pictureHeight) * 100.0); } else { scale = (int)(((double)signatureMaxWidth / pictureWidth) * 100.0); } SLPicture pic = new SLPicture(signaturePath); pic.SetPosition(signatureRowIndex - 1 + excelBorderWidth, signatureColumnIndex - 1 + excelBorderWidth); pic.ResizeInPercentage(scale, scale); template.InsertPicture(pic); } catch (Exception ex) { //File path not correct or file not exist } #endregion Insert Signature #endregion Fill Data #region Auto Fit Row for (int i = 9; i <= 13; i++) { template.AutoFitRow(i); } #endregion Auto Fit Row returnFilePath = string.Format(@"{0}\ServiceReport_{1}_{2}.xlsx", _serviceReportTempFolderPath, orderID, DateTime.Now.ToString("yyyyMMddHHmmss")); template.SaveAs(returnFilePath); } catch (Exception e) { LogManager.Write(e, "RecWomCrm", "System"); returnFilePath = null; } return returnFilePath; }