static void Main(string[] args) { if (args.Length < 2) { Console.WriteLine("Usage: ResourceTool.exe -i/e sample.xlsx"); return; } switch (args[0]) { case "-i": { var doc = new SLDocument(args[1]); doc.SelectWorksheet("Translations"); var stat = doc.GetWorksheetStatistics(); var languages = new List<string>(); for (var i = 3; i <= stat.EndColumnIndex; i++) { var lang = doc.GetCellValueAsString(2, i); var resxName = lang == "en-US" ? "Resources.resx" : string.Format("Resources.{0}.resx", lang); languages.Add(resxName); } for (var j = 0; j < languages.Count; j++) { var language = languages[j]; var resxFile = Path.Combine(RootPath, "Properties", language); var resx = new XmlDocument(); resx.Load(resxFile); for (var i = 3; i <= stat.EndRowIndex; i++) { var key = doc.GetCellValueAsString(i, 1); Console.WriteLine("{0} {1} {2}", i, key, language); var value = doc.GetCellValueAsString(i, j + 3); var node = resx.SelectSingleNode(string.Format(".//data[@name='{0}']", key)); if (node == null) { node = resx.CreateElement("data"); var name = resx.CreateAttribute("name"); name.Value = key; node.Attributes.Append(name); var xmlspace = resx.CreateAttribute("xml:space"); xmlspace.Value = "preserve"; node.Attributes.Append(xmlspace); resx.DocumentElement.AppendChild(node); var valueNode = resx.CreateElement("value"); valueNode.InnerText = value; node.AppendChild(valueNode); } else { var valueNode = node.SelectSingleNode("value"); if (valueNode == null) { valueNode = resx.CreateElement("value"); node.AppendChild(valueNode); } valueNode.InnerText = value; } } resx.Save(resxFile); } } break; case "-e": { if (File.Exists(args[1])) File.Delete(args[1]); var r = new Regex(@"Resources\.([a-z]{2}-[A-Z]{2})?\.?resx"); var doc = new SLDocument(); doc.AddWorksheet("Translations"); doc.DeleteWorksheet("Sheet1"); doc.SelectWorksheet("Translations"); doc.SetCellValue(1, 1, "Key"); doc.SetCellValue(1, 2, "Comment"); var headerStyle = doc.CreateStyle(); headerStyle.Font.Bold = true; headerStyle.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightBlue, System.Drawing.Color.LightBlue); doc.SetCellStyle(1, 1, headerStyle); doc.SetCellStyle(1, 2, headerStyle); var resxFiles = Directory.GetFiles(Path.Combine(RootPath, "Properties"), "*.resx"); var columnIndex = 3; var keys = new List<string>(); var files = resxFiles.OrderBy(n => { var l = r.Match(Path.GetFileName(n)).Groups[1].Value; return string.IsNullOrEmpty(l) ? null : n; }); foreach (var resx in files) { var lang = r.Match(Path.GetFileName(resx)).Groups[1].Value; var english = false; if (string.IsNullOrEmpty(lang)) { lang = "en-US"; english = true; } var ci = CultureInfo.GetCultureInfo(lang); doc.SetCellValue(1, columnIndex, ci.EnglishName); doc.SetCellValue(2, columnIndex, ci.Name); doc.SetCellStyle(1, columnIndex, headerStyle); doc.SetColumnWidth(columnIndex, 100); var rr = new ResXResourceReader(resx); if (english) { rr.UseResXDataNodes = true; var rowIndex = 3; foreach (DictionaryEntry entry in rr) { var key = (string) entry.Key; var node = (ResXDataNode) entry.Value; keys.Add(key); doc.SetCellValue(rowIndex, 1, key); doc.SetCellValue(rowIndex, 2, node.Comment); doc.SetCellValue(rowIndex, columnIndex, node.GetValue((ITypeResolutionService) null).ToString()); rowIndex++; } } else { rr.UseResXDataNodes = true; foreach (DictionaryEntry entry in rr) { var key = (string) entry.Key; var node = (ResXDataNode) entry.Value; var index = keys.IndexOf(key); int rowIndex; if (index == -1) { rowIndex = keys.Count; keys.Add(key); doc.SetCellValue(rowIndex, 1, key); } else { rowIndex = index + 3; } doc.SetCellValue(rowIndex, columnIndex, node.GetValue((ITypeResolutionService) null).ToString()); } } columnIndex++; } doc.AutoFitColumn(1, 2); doc.SaveAs(args[1]); } break; default: Console.WriteLine("Unknown switch {0}", args[0]); return; } }
protected string GenerateServiceChecklist(int? orderID) { #region Style Definition SLStyle companyAddressStyle = new SLStyle(); companyAddressStyle.Font.FontName = "Arial"; companyAddressStyle.Font.FontSize = 10; companyAddressStyle.SetWrapText(true); companyAddressStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center); companyAddressStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left); SLStyle titleStyle = new SLStyle(); titleStyle.Font.FontName = "Arial"; titleStyle.Font.FontSize = 16; titleStyle.Font.Bold = true; titleStyle.SetWrapText(true); titleStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center); titleStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center); SLStyle jobInformationLabelStyle = new SLStyle(); jobInformationLabelStyle.Font.FontName = "Arial"; jobInformationLabelStyle.Font.FontSize = 12; jobInformationLabelStyle.SetWrapText(true); jobInformationLabelStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center); jobInformationLabelStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right); SLStyle jobInformationContentStyle = new SLStyle(); jobInformationContentStyle.Font.FontName = "Arial"; jobInformationContentStyle.Font.FontSize = 12; jobInformationContentStyle.SetWrapText(true); jobInformationContentStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center); jobInformationContentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left); SLStyle itemIndexStyle = new SLStyle(); itemIndexStyle.Font.FontName = "Arial"; itemIndexStyle.Font.FontSize = 11; itemIndexStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Top); itemIndexStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right); itemIndexStyle.Border.BottomBorder.BorderStyle = itemIndexStyle.Border.TopBorder.BorderStyle = itemIndexStyle.Border.LeftBorder.BorderStyle = itemIndexStyle.Border.RightBorder.BorderStyle = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin; itemIndexStyle.Border.BottomBorder.Color = itemIndexStyle.Border.TopBorder.Color = itemIndexStyle.Border.LeftBorder.Color = itemIndexStyle.Border.RightBorder.Color = System.Drawing.Color.Black; SLStyle itemRowHeaderStyle = new SLStyle(); itemRowHeaderStyle.Font.FontName = "Arial"; itemRowHeaderStyle.Font.FontSize = 11; itemRowHeaderStyle.SetWrapText(true); itemRowHeaderStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Top); itemRowHeaderStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left); itemRowHeaderStyle.Border.BottomBorder.BorderStyle = itemRowHeaderStyle.Border.TopBorder.BorderStyle = itemRowHeaderStyle.Border.LeftBorder.BorderStyle = itemRowHeaderStyle.Border.RightBorder.BorderStyle = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin; itemRowHeaderStyle.Border.BottomBorder.Color = itemRowHeaderStyle.Border.TopBorder.Color = itemRowHeaderStyle.Border.LeftBorder.Color = itemRowHeaderStyle.Border.RightBorder.Color = System.Drawing.Color.Black; SLStyle itemContentStyle = new SLStyle(); itemContentStyle.Font.FontName = "Arial"; itemContentStyle.Font.FontSize = 11; itemContentStyle.SetWrapText(true); itemContentStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center); itemContentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left); itemContentStyle.Border.BottomBorder.BorderStyle = itemContentStyle.Border.TopBorder.BorderStyle = itemContentStyle.Border.LeftBorder.BorderStyle = itemContentStyle.Border.RightBorder.BorderStyle = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin; itemContentStyle.Border.BottomBorder.Color = itemContentStyle.Border.TopBorder.Color = itemContentStyle.Border.LeftBorder.Color = itemContentStyle.Border.RightBorder.Color = System.Drawing.Color.Black; SLStyle topBorderStyle = new SLStyle(); topBorderStyle.Border.SetTopBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle bottomBorderStyle = new SLStyle(); bottomBorderStyle.Border.SetBottomBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle leftBorderStyle = new SLStyle(); leftBorderStyle.Border.SetLeftBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle rightBorderStyle = new SLStyle(); rightBorderStyle.Border.SetRightBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle footerLabelStyle = new SLStyle(); footerLabelStyle.Font.FontName = "Arial"; footerLabelStyle.Font.FontSize = 11; footerLabelStyle.Alignment.WrapText = true; footerLabelStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Top); SLStyle remarkStyle = new SLStyle(); remarkStyle.Font.FontName = "Arial"; remarkStyle.Font.FontSize = 11; remarkStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center); remarkStyle.Border.BottomBorder.BorderStyle = remarkStyle.Border.TopBorder.BorderStyle = remarkStyle.Border.LeftBorder.BorderStyle = remarkStyle.Border.RightBorder.BorderStyle = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin; remarkStyle.Border.BottomBorder.Color = remarkStyle.Border.TopBorder.Color = remarkStyle.Border.LeftBorder.Color = remarkStyle.Border.RightBorder.Color = System.Drawing.Color.Black; SLStyle legendLabelStyle = new SLStyle(); legendLabelStyle.Font.FontName = "Arial"; legendLabelStyle.Font.FontSize = 11; legendLabelStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center); SLStyle leftAlignmentStyle = new SLStyle(); leftAlignmentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left); SLStyle rightAlignmentStyle = new SLStyle(); rightAlignmentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right); SLStyle centerAlignmentStyle = new SLStyle(); centerAlignmentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center); SLStyle wrapTextStyle = new SLStyle(); wrapTextStyle.Alignment.WrapText = true; #endregion Style Definition int signatureMaxWidth = 160, signatureMaxHeight = 140; string returnFilePath = null; string logoPath = System.Web.Hosting.HostingEnvironment.MapPath("~/CSS/Layout/Images/logo.png"); double _borderWidth = 0.01; int _valueStartRow = 13; int _valueStartColumn = 4; int _valueEndColumn = 7; int valueEndRow = 13; int currentSheetIndex = 1; Dictionary<Guid, int> subItemRowIndex = new Dictionary<Guid, int>(); IJobFormService jobFormService = AutoSessionServiceFactory.GetJobFormService(ApplicationSetting.Current.DefaultConnectionString); IFormItemService formItemService = AutoSessionServiceFactory.GetFormItemService(ApplicationSetting.Current.DefaultConnectionString); SLDocument document = new SLDocument(); document.RenameWorksheet("Sheet" + currentSheetIndex, "Checklist" + currentSheetIndex); document.SelectWorksheet("Checklist" + currentSheetIndex); try { #region Get Data ServiceChecklistDTO checklist = jobFormService.GetServiceChecklist(orderID.Value); IEnumerable<ServiceChecklistFormItemDTO> formItems = formItemService.GetServiceChecklistFormItems(); #endregion Get Data #region Validation if (checklist == null || checklist.valueGroups == null || checklist.valueGroups.Count() == 0) return "No-Record"; #endregion Validation #region Title Section document.MergeWorksheetCells(2, 1, 4, 4); lock (logoSyncLock) { SLPicture logo = new SLPicture(logoPath); logo.SetPosition(1 + _borderWidth, _borderWidth); document.InsertPicture(logo); } for (int i = 2; i <= 4; i++) { document.MergeWorksheetCells(i, 5, i, 7); } document.SetCellValue(2, 5, "Units A-D, 15/F, Goodman Kwai Chung Logistics Centre, 585-609 Castle Peak Rood, Kwai Chung, N.T., Hong Kong"); document.SetCellValue(3, 5, "香港新界葵涌青山道585-609號嘉民葵涌物流中心15樓A-D室"); document.SetCellValue(4, 5, "電話 Tel: (852) 2494 9455 傳真 Fax: (852) 2481 2051"); document.SetCellStyle(2, 5, 4, 8, companyAddressStyle); document.MergeWorksheetCells(6, 1, 6, 7); document.SetCellValue(6, 1, "Routine Check / Service Checklist" + Environment.NewLine + "例行檢查 / 記錄表"); document.SetCellStyle(6, 1, titleStyle); #endregion Title Section #region Job Information string customerCode = string.IsNullOrEmpty(checklist.sCustomerCode) ? string.Empty : checklist.sCustomerCode; string customerName = checklist.nLanguage == 1 ? string.IsNullOrEmpty(checklist.sCustomerChiName) ? string.Empty : checklist.sCustomerChiName : string.IsNullOrEmpty(checklist.sCustomerName) ? string.Empty : checklist.sCustomerName; string completionDate = checklist.dCompletionDate.HasValue ? checklist.dCompletionDate.Value.ToString("dd-MM-yyyy") : string.Empty; string equipmentType = string.IsNullOrEmpty(checklist.sEquipmentType) ? string.Empty : checklist.sEquipmentType; string equipmentTypeRemark = string.IsNullOrEmpty(checklist.sEquipmentTypeRemark) ? string.Empty : checklist.sEquipmentTypeRemark; string location = string.IsNullOrEmpty(checklist.sWorkingAddress) ? string.Empty : checklist.sWorkingAddress; document.MergeWorksheetCells(7, 1, 8, 2); document.SetCellValue(7, 1, "Name of Customer" + Environment.NewLine + "客戶名稱 :"); document.SetCellStyle(7, 1, jobInformationLabelStyle); document.MergeWorksheetCells(7, 3, 8, 3); document.SetCellValue(7, 3, customerName); document.SetCellStyle(7, 3, jobInformationContentStyle); document.MergeWorksheetCells(7, 4, 7, 5); document.SetCellValue(7, 4, "Job / ACM No. :"); document.SetCellStyle(7, 4, jobInformationLabelStyle); document.MergeWorksheetCells(7, 6, 7, 7); document.SetCellValue(7, 6, customerCode); document.SetCellStyle(7, 6, jobInformationContentStyle); document.MergeWorksheetCells(8, 4, 8, 5); document.SetCellValue(8, 4, "Work Date 工作日期 :"); document.SetCellStyle(8, 4, jobInformationLabelStyle); document.MergeWorksheetCells(8, 6, 8, 7); document.SetCellValue(8, 6, completionDate); document.SetCellStyle(8, 6, jobInformationContentStyle); document.MergeWorksheetCells(9, 1, 9, 2); document.SetCellValue(9, 1, "Location" + Environment.NewLine + "工作地址 :"); document.SetCellStyle(9, 1, jobInformationLabelStyle); document.MergeWorksheetCells(9, 3, 9, 7); document.SetCellValue(9, 3, location); document.SetCellStyle(9, 3, jobInformationContentStyle); document.MergeWorksheetCells(10, 1, 10, 2); document.SetCellValue(10, 1, "Equipment Type" + Environment.NewLine + "設備類別 :"); document.SetCellStyle(10, 1, jobInformationLabelStyle); document.SetCellValue(10, 3, equipmentType); document.SetCellStyle(10, 3, jobInformationContentStyle); document.MergeWorksheetCells(10, 4, 10, 5); document.SetCellValue(10, 4, "Equipment Type Remark" + Environment.NewLine + "設備類別備註 :"); document.SetCellStyle(10, 4, jobInformationLabelStyle); document.MergeWorksheetCells(10, 6, 10, 7); document.SetCellValue(10, 6, equipmentTypeRemark); document.SetCellStyle(10, 6, jobInformationContentStyle); #endregion Job Information #region Checklist Header document.SetCellValue(12, 2, "Check List"); document.SetCellValue(12, 3, "檢查內容"); document.MergeWorksheetCells(12, _valueStartColumn, 12, _valueEndColumn); int currentRow = _valueStartRow; int currentFormItemStartRow = _valueStartRow; foreach (ServiceChecklistFormItemDTO formItem in formItems) { string formItemPrefix = string.IsNullOrEmpty(formItem.sPrefix) ? string.Empty : formItem.sPrefix; string formItemName = string.IsNullOrEmpty(formItem.sName) ? string.Empty : formItem.sName; string formItemEngName = string.IsNullOrEmpty(formItem.sEngName) ? string.Empty : formItem.sEngName; document.SetCellValue(currentRow, 1, formItemPrefix); document.SetCellValue(currentRow, 2, formItemEngName); document.SetCellValue(currentRow, 3, formItemName); if (formItem.subItems != null && formItem.subItems.Count() > 0) { if (formItem.subItems.Count() == 1) { subItemRowIndex.Add(formItem.subItems.ElementAt(0).gID, currentRow); } else { foreach (ServiceChecklistFormSubItemDTO subItem in formItem.subItems) { currentRow++; string subItemPrefix = string.IsNullOrEmpty(subItem.sPrefix) ? string.Empty : subItem.sPrefix; string subItemName = string.IsNullOrEmpty(subItem.sName) ? string.Empty : subItem.sName; string subItemEngName = string.IsNullOrEmpty(subItem.sEngName) ? string.Empty : subItem.sEngName; string subItemSuffix = string.IsNullOrEmpty(subItem.sSuffix) ? string.Empty : subItem.sSuffix; document.SetCellValue(currentRow, 2, string.Format("{0}{1}{2}", string.IsNullOrEmpty(subItemPrefix) ? string.Empty : subItemPrefix + " ", subItemEngName, string.IsNullOrEmpty(subItemSuffix) ? string.Empty : " (" + subItemSuffix + ")")); document.SetCellValue(currentRow, 3, subItem.sName); subItemRowIndex.Add(subItem.gID, currentRow); } } } if (currentRow != currentFormItemStartRow) { document.MergeWorksheetCells(currentFormItemStartRow, 1, currentRow, 1); } currentFormItemStartRow = currentRow + 1; currentRow++; } valueEndRow = currentRow - 1; document.SetCellStyle(_valueStartRow - 1, 1, valueEndRow, 1, itemIndexStyle); document.SetCellStyle(_valueStartRow - 1, 2, valueEndRow, 3, itemRowHeaderStyle); document.SetCellStyle(_valueStartRow - 1, _valueStartColumn, valueEndRow, _valueEndColumn, itemContentStyle); #endregion Checklist Header #region Footer Section #region Checklist Remark int remarkRow = currentRow; string checklistRemark = string.IsNullOrEmpty(checklist.sRemark) ? string.Empty : checklist.sRemark; document.MergeWorksheetCells(currentRow, 1, currentRow, 2); document.MergeWorksheetCells(currentRow, 3, currentRow, 7); document.SetCellValue(currentRow, 1, "Remarks 備註"); document.SetCellValue(currentRow, 3, checklistRemark); #region Set Style SLStyle currentStyle = remarkStyle; currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center); document.SetCellStyle(currentRow, 1, currentRow, 2, currentStyle); currentStyle = remarkStyle; currentStyle.SetWrapText(true); currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left); document.SetCellStyle(currentRow, 3, currentRow, 7, currentStyle); #endregion Set Style #endregion Checklist Remark currentRow++; #region Legend int legendRow = currentRow; document.MergeWorksheetCells(currentRow, 1, currentRow, 2); document.MergeWorksheetCells(currentRow, 3, currentRow, 7); document.SetCellValue(currentRow, 1, "Legend 說明"); document.SetCellValue(currentRow, 3, "OK : OK 良好 R : Require Repair 需維修 L : Replacement 更換 N : Not Applicable 不適用"); #region Set Style currentStyle = legendLabelStyle; currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left); document.SetCellStyle(currentRow, 1, currentRow, 2, currentStyle); currentStyle = legendLabelStyle; currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center); document.SetCellStyle(currentRow, 3, currentRow, 7, currentStyle); #endregion Set Style #endregion Legend currentRow++; int footerBoxStartRow = currentRow; string technicians = string.IsNullOrEmpty(checklist.sTechnicianNameString) ? string.Empty : checklist.sTechnicianNameString; string startTime = checklist.tActualStartTime.HasValue ? checklist.tActualStartTime.Value.ToString(@"hh\:mm") : string.Empty; string endTime = checklist.tActualEndTime.HasValue ? checklist.tActualEndTime.Value.ToString(@"hh\:mm") : string.Empty; string customerOpinion = string.IsNullOrEmpty(checklist.sCustomerOpinion) ? string.Empty : checklist.sCustomerOpinion; string signDate = checklist.dSignDate.HasValue ? checklist.dSignDate.Value.ToString("dd-MM-yyyy") : string.Empty; document.MergeWorksheetCells(currentRow, 1, currentRow, 2); document.SetCellValue(currentRow, 1, "Service Done by 檢查員:"); document.SetCellValue(currentRow, 3, technicians); document.MergeWorksheetCells(currentRow, _valueStartColumn, currentRow, _valueEndColumn); document.SetCellValue(currentRow, _valueStartColumn, "Opinion of Customer(s), if any 客戶意見 :"); currentRow++; document.MergeWorksheetCells(currentRow, 1, currentRow, 2); document.SetCellValue(currentRow, 1, "Work Start Time 工作開始時間:"); document.SetCellValue(currentRow, 3, startTime); document.MergeWorksheetCells(currentRow, _valueStartColumn, currentRow + 1, _valueEndColumn); document.SetCellValue(currentRow, 4, customerOpinion); currentRow++; document.MergeWorksheetCells(currentRow, 1, currentRow, 2); document.SetCellValue(currentRow, 1, "Work Completion Time 工作完成時間:"); document.SetCellValue(currentRow, 3, endTime); currentRow++; int signatureStartRow = currentRow; document.MergeWorksheetCells(currentRow, 1, currentRow + 5, 3); document.SetCellValue(currentRow, 1, "Office use 公司填寫"); document.MergeWorksheetCells(currentRow, _valueStartColumn, currentRow, _valueStartColumn + 1); document.SetCellValue(currentRow, _valueStartColumn, "Authorized Signature & Chop for Work Completion to Satisfaction"); currentRow++; document.MergeWorksheetCells(currentRow, _valueStartColumn, currentRow, _valueStartColumn + 1); document.SetCellValue(currentRow, _valueStartColumn, "以上工作認可完成請客戶蓋章及簽署"); currentRow = currentRow + 4; document.SetCellValue(currentRow, _valueStartColumn + 1, "Date 簽署日期:"); document.MergeWorksheetCells(currentRow, _valueEndColumn - 1, currentRow, _valueEndColumn); document.SetCellValue(currentRow, _valueEndColumn - 1, signDate); document.MergeWorksheetCells(signatureStartRow, _valueEndColumn - 1, signatureStartRow + 4, _valueEndColumn); #region Signature try { if (!string.IsNullOrEmpty(checklist.sSignaturePath)) { string signatureFullPath = ApplicationSetting.Current.RootFolderPath + checklist.sSignaturePath; SLPicture signature = new SLPicture(signatureFullPath); //Get Signature Dimension System.Drawing.Bitmap bm = new System.Drawing.Bitmap(signatureFullPath); 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); } signature.SetPosition(signatureStartRow - 1 + _borderWidth, _valueEndColumn - 2 + _borderWidth); signature.ResizeInPercentage(scale, scale); document.InsertPicture(signature); } } catch { } #endregion Signature document.SetCellStyle(footerBoxStartRow, 1, currentRow, _valueEndColumn, footerLabelStyle); currentStyle = footerLabelStyle; currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left); document.SetCellStyle(footerBoxStartRow, 1, currentRow, 2, currentStyle); document.SetCellStyle(footerBoxStartRow, 3, signatureStartRow - 1, _valueEndColumn, currentStyle); currentStyle = footerLabelStyle; currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right); document.SetCellStyle(signatureStartRow, _valueStartColumn, currentRow, _valueStartColumn + 1, currentStyle); #region Draw Box document.SetCellStyle(footerBoxStartRow, _valueStartColumn, footerBoxStartRow, _valueEndColumn, topBorderStyle); document.SetCellStyle(footerBoxStartRow, _valueStartColumn, currentRow, _valueStartColumn, leftBorderStyle); document.SetCellStyle(footerBoxStartRow, _valueEndColumn, currentRow, _valueEndColumn, rightBorderStyle); document.SetCellStyle(currentRow, _valueStartColumn, currentRow, _valueEndColumn, bottomBorderStyle); #endregion Draw Box #endregion Footer Section #region Print Setting SLPageSettings pageSetting = new SLPageSettings(); pageSetting.PaperSize = SLPaperSizeValues.A4Paper; pageSetting.Orientation = DocumentFormat.OpenXml.Spreadsheet.OrientationValues.Portrait; pageSetting.ScalePage(1, 1); pageSetting.TopMargin = pageSetting.BottomMargin = 0.3; pageSetting.LeftMargin = pageSetting.RightMargin = 0.7; document.SetPageSettings(pageSetting); #endregion Print Setting #region Column Width Setting document.SetColumnWidth(1, 5.0); document.SetColumnWidth(2, 40.0); document.SetColumnWidth(3, 30.0); for (int i = _valueStartColumn; i <= _valueEndColumn; i++) { document.SetColumnWidth(i, 20.0); } #endregion Column Width Setting #region Row Height Setting document.SetRowHeight(2, 30.0); document.SetRowHeight(6, 45.0); document.SetRowHeight(9, 10, 30.0); document.SetRowHeight(remarkRow, 30.0); document.SetRowHeight(legendRow, 30.0); document.SetRowHeight(legendRow + 1, 75.0); document.SetRowHeight(footerBoxStartRow + 1, currentRow, 20.0); document.SetRowHeight(signatureStartRow, 30.0); #endregion Row Height Setting #region Fill Values if (checklist.valueGroups != null && checklist.valueGroups.Count() > 0) { for (int i = 0; i < checklist.valueGroups.Count(); i++) { if (i > 3 && i % 4 == 0) { string activeSheetName = document.GetCurrentWorksheetName(); document.AddWorksheet("Dummy"); //Open New Worksheet currentSheetIndex++; string currentSheetName = "Checklist" + currentSheetIndex; bool isCopySuccess = document.CopyWorksheet(activeSheetName, currentSheetName); document.SelectWorksheet(currentSheetName); document.DeleteWorksheet("Dummy"); //document.CopyCellFromWorksheet(activeSheetName, 1, 1, currentRow, _valueEndColumn, 1, 1, SLPasteTypeValues.Paste); //Clear Value Part for (int c = _valueStartColumn; c <= _valueEndColumn; c++) { for (int r = _valueStartRow; r <= valueEndRow; r++) { document.SetCellValue(r, c, string.Empty); } } } ServiceChecklistValueGroupDTO valueGroup = checklist.valueGroups.ElementAt(i); int columnIndex = (i % 4) + _valueStartColumn; foreach (ServiceChecklistValueDTO value in valueGroup.values) { int rowIndex = subItemRowIndex[value.gFormSubItemID.Value]; document.SetCellValue(rowIndex, columnIndex, value.sValue); } } } #endregion Fill Values returnFilePath = string.Format(@"{0}\ServiceChecklist_{1}.xlsx", _serviceChecklistTempFolderPath, DateTime.Now.ToString("yyyyMMddHHmmss")); document.SaveAs(returnFilePath); } catch (Exception e) { LogManager.Write(e, "RecWomCrm", "System"); returnFilePath = null; } finally { document.Dispose(); } return returnFilePath; }