protected string GenerateLeaveReport(LeaveReportFilterDTO model)
{
#region Style Definition
SLStyle titleStyle = new SLStyle();
titleStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
titleStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
titleStyle.Font.FontSize = 16;
titleStyle.Font.FontName = "微軟正黑體";
titleStyle.Font.Bold = true;
SLStyle borderStyle = new SLStyle();
borderStyle.SetBottomBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Gray);
borderStyle.SetRightBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Gray);
SLStyle dateRangeStyle = new SLStyle();
dateRangeStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
dateRangeStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
dateRangeStyle.Font.FontSize = 14;
dateRangeStyle.Font.FontName = "微軟正黑體";
SLStyle columnHeaderStyle = new SLStyle();
columnHeaderStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
columnHeaderStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
columnHeaderStyle.Font.FontSize = 12;
columnHeaderStyle.Font.FontName = "微軟正黑體";
SLStyle totalColumnStyle = new SLStyle();
totalColumnStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
totalColumnStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right);
totalColumnStyle.Font.FontSize = 12;
totalColumnStyle.Font.FontName = "微軟正黑體";
totalColumnStyle.SetRightBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, SLThemeColorIndexValues.Dark1Color);
SLStyle holidayTitleStyle = new SLStyle();
holidayTitleStyle.SetFontColor(System.Drawing.Color.Red);
holidayTitleStyle.Font.FontSize = 12;
holidayTitleStyle.Font.FontName = "微軟正黑體";
SLStyle holidayBackgroundStyle = new SLStyle();
holidayBackgroundStyle.Fill.SetPattern(DocumentFormat.OpenXml.Spreadsheet.PatternValues.Solid, System.Drawing.Color.FromArgb(255, 247, 247), System.Drawing.Color.FromArgb(255, 247, 247));
SLStyle rowHeaderStyle = new SLStyle();
rowHeaderStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
rowHeaderStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
rowHeaderStyle.Font.FontSize = 12;
rowHeaderStyle.Font.FontName = "微軟正黑體";
SLStyle totalRowStyle = new SLStyle();
totalRowStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
totalRowStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right);
totalRowStyle.Font.FontSize = 12;
totalRowStyle.Font.FontName = "微軟正黑體";
totalRowStyle.SetBottomBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, SLThemeColorIndexValues.Dark1Color);
SLStyle pmLeaveStyle = new SLStyle();
pmLeaveStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
pmLeaveStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
pmLeaveStyle.Fill.SetPattern(DocumentFormat.OpenXml.Spreadsheet.PatternValues.Solid, System.Drawing.Color.FromArgb(94, 219, 149), System.Drawing.Color.FromArgb(94, 219, 149));
pmLeaveStyle.Font.FontSize = 10;
pmLeaveStyle.Font.FontName = "Arial";
pmLeaveStyle.Font.FontColor = System.Drawing.Color.White;
pmLeaveStyle.Font.Bold = true;
SLStyle amLeaveStyle = new SLStyle();
amLeaveStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
amLeaveStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
amLeaveStyle.Fill.SetPattern(DocumentFormat.OpenXml.Spreadsheet.PatternValues.Solid, System.Drawing.Color.FromArgb(40, 191, 231), System.Drawing.Color.FromArgb(40, 191, 231));
amLeaveStyle.Font.FontSize = 10;
amLeaveStyle.Font.FontName = "Arial";
amLeaveStyle.Font.FontColor = System.Drawing.Color.White;
amLeaveStyle.Font.Bold = true;
SLStyle dayLeaveStyle = new SLStyle();
dayLeaveStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
dayLeaveStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
dayLeaveStyle.Fill.SetPattern(DocumentFormat.OpenXml.Spreadsheet.PatternValues.Solid, System.Drawing.Color.FromArgb(204, 101, 219), System.Drawing.Color.FromArgb(204, 101, 219));
dayLeaveStyle.Font.FontSize = 10;
dayLeaveStyle.Font.FontName = "Arial";
dayLeaveStyle.Font.FontColor = System.Drawing.Color.White;
dayLeaveStyle.Font.Bold = true;
#endregion Style Definition
string returnFilePath = null;
int _dataHeaderColumn = 1;
int _dataHeaderRow = 4;
int _totalHeaderColumn = 2;
int _totalHeaderRow = 5;
List<int> holidayColumnIndex = new List<int>();
try
{
DateTime fromDate = model.fromDate.HasValue ? model.fromDate.Value : DateTime.Today;
DateTime toDate = model.toDate.HasValue ? model.toDate.Value : fromDate.AddDays(6);
if (toDate < fromDate) return null;
Dictionary<string, int> dateColumnIndex = new Dictionary<string, int>();
Dictionary<int, int> technicianRowIndex = new Dictionary<int, int>();
SLDocument document = new SLDocument();
#region Establish Column Index
IHolidayService holidayService = AutoSessionServiceFactory.GetHolidayService(ApplicationSetting.Current.DefaultConnectionString);
List<DateTime> holidays = holidayService.GetHolidays(fromDate, toDate);
DateTime currentDate = fromDate;
int currentColumn = _totalHeaderColumn + 1;
while (currentDate <= toDate)
{
dateColumnIndex.Add(currentDate.ToString("yyyy-MM-dd"), currentColumn);
document.SetCellValue(_dataHeaderRow, currentColumn, currentDate.ToString("dd MMM, ddd"));
document.SetCellStyle(_dataHeaderRow, currentColumn, columnHeaderStyle);
if (holidays.Contains(currentDate) || currentDate.DayOfWeek == DayOfWeek.Sunday)
{
document.SetCellStyle(_dataHeaderRow, currentColumn, holidayTitleStyle);
holidayColumnIndex.Add(currentColumn);
}
currentColumn++;
currentDate = currentDate.AddDays(1);
}
#endregion Establish Column Index
#region Establish Row Index
int currentRow = _totalHeaderRow + 1;
ITechnicianService technicianService = AutoSessionServiceFactory.GetTechnicianService(ApplicationSetting.Current.DefaultConnectionString);
List<LeaveReportTechnicianDTO> technicians = technicianService.GetLeaveReportTechnicians(fromDate, toDate).ToList();
if (technicians != null)
{
foreach (LeaveReportTechnicianDTO technician in technicians)
{
technicianRowIndex.Add(technician.nID, currentRow);
document.SetCellValue(currentRow, _dataHeaderColumn, technician.sName);
document.SetCellStyle(currentRow, _dataHeaderColumn, rowHeaderStyle);
currentRow++;
}
}
#endregion Establish Row Index
if (dateColumnIndex.Count() == 0 || technicianRowIndex.Count() == 0) return null;
#region Title Section
document.MergeWorksheetCells(1, 1, 1, dateColumnIndex.Count() + _totalHeaderColumn);
document.SetCellValue(1, 1, "請假報告表");
document.SetCellStyle(1, 1, titleStyle);
document.SetCellValue(2, 2, "由");
document.SetCellValue(2, 3, fromDate.ToString("yyyy/MM/dd"));
document.SetCellValue(2, 4, "至");
document.SetCellValue(2, 5, toDate.ToString("yyyy/MM/dd"));
document.SetCellStyle(2, 2, 2, 5, dateRangeStyle);
document.SetCellValue(_totalHeaderRow, _totalHeaderColumn, "Total");
#endregion Title Section
#region Fill Leaves
ILeaveService leaveService = AutoSessionServiceFactory.GetLeaveService(ApplicationSetting.Current.DefaultConnectionString);
List<LeaveDTO> leaves = leaveService.GetLeavesByDateRange(fromDate, toDate).ToList();
if (leaves != null && leaves.Count() > 0)
{
foreach (LeaveDTO leave in leaves)
{
int rowIndex = technicianRowIndex[leave.nTechnicianID];
int columnIndex = dateColumnIndex[leave.dDate.ToString("yyyy-MM-dd")];
if (leave.bIsAM.HasValue && leave.bIsAM.Value)
{
document.SetCellValue(rowIndex, columnIndex, "AM");
document.SetCellStyle(rowIndex, columnIndex, amLeaveStyle);
}
else if (leave.bIsPM.HasValue && leave.bIsPM.Value)
{
document.SetCellValue(rowIndex, columnIndex, "PM");
document.SetCellStyle(rowIndex, columnIndex, pmLeaveStyle);
}
else if (leave.bIsFullDay.HasValue && leave.bIsFullDay.Value)
{
document.SetCellValue(rowIndex, columnIndex, "1 Day");
document.SetCellStyle(rowIndex, columnIndex, dayLeaveStyle);
}
}
}
#endregion Fill Leaves
#region Set Up Total Formula
string formulaStringFormat = "=COUNTIF({0}, \"1 Day\") + COUNTIF({0}, \"AM\")*0.5 + COUNTIF({0}, \"PM\")*0.5";
int formulaRow = _totalHeaderRow;
for (int i = _totalHeaderColumn + 1; i <= _totalHeaderColumn + dateColumnIndex.Count; i++)
{
string cellRange = SLConvert.ToCellRange(_totalHeaderRow + 1, i, _totalHeaderRow + technicianRowIndex.Count, i);
document.SetCellValue(formulaRow, i, string.Format(formulaStringFormat, cellRange));
document.SetCellStyle(formulaRow, i, totalRowStyle);
}
int formulaColumn = _totalHeaderColumn;
for (int i = _totalHeaderRow + 1; i <= _totalHeaderRow + technicianRowIndex.Count; i++)
{
string cellRange = SLConvert.ToCellRange(i, _totalHeaderColumn + 1, i, _totalHeaderColumn + dateColumnIndex.Count);
document.SetCellValue(i, formulaColumn, string.Format(formulaStringFormat, cellRange));
document.SetCellStyle(i, formulaColumn, totalColumnStyle);
}
#endregion Set Up Total Formula
#region Draw Borders
document.SetCellStyle(_totalHeaderRow + 1, _totalHeaderColumn + 1, _totalHeaderRow + technicianRowIndex.Count(), _totalHeaderColumn + dateColumnIndex.Count(), borderStyle);
#endregion Draw Bottom Border
#region Fill Holiday Column Background
foreach (int columnIndex in holidayColumnIndex)
{
document.SetCellStyle(_totalHeaderRow + 1, columnIndex, _totalHeaderRow + technicianRowIndex.Count, columnIndex, holidayBackgroundStyle);
}
#endregion Fill Holiday Column Background
#region Auto Fit Column
for (int i = 1; i <= _totalHeaderColumn + dateColumnIndex.Count(); i++)
{
document.AutoFitColumn(i);
}
#endregion Auto Fit Column
#region Freeze Pane
document.FreezePanes(_totalHeaderRow, _totalHeaderColumn);
#endregion Freeze Pane
#region Print Setting
SLPageSettings pageSetting = new SLPageSettings();
pageSetting.PaperSize = SLPaperSizeValues.A4Paper;
pageSetting.Orientation = DocumentFormat.OpenXml.Spreadsheet.OrientationValues.Landscape;
pageSetting.ScalePage(1, 100);
pageSetting.TopMargin = pageSetting.BottomMargin = 0.3;
pageSetting.LeftMargin = pageSetting.RightMargin = 0.3;
document.SetPageSettings(pageSetting);
document.SetDefinedName("Print_Titles", "=Sheet1!$1:$" + _dataHeaderRow, "", "Sheet1");
#endregion Print Setting
returnFilePath = string.Format(@"{0}\LeaveReport_{1}.xlsx", _leaveReportTempFolderPath, DateTime.Now.ToString("yyyyMMddHHmmss"));
document.SaveAs(returnFilePath);
}
catch
{
returnFilePath = null;
}
return returnFilePath;
}
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;
}