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;
}
}
public static byte[] ExportarDetalleProcesoExcel(Func<SLDocument, bool> body)
{
using (var document = new SLDocument())
{
var name = document.GetCurrentWorksheetName();
document.AddWorksheet("DetalleProceso");
document.DeleteWorksheet(name);
document.SelectWorksheet("DetalleProceso");
body(document);
var stream = new MemoryStream();
document.SaveAs(stream);
stream.Position = 0;
var buffer = new byte[stream.Length];
stream.Read(buffer, 0, buffer.Length);
return buffer;
}
}
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;
}
