Example #1
4
        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;
        }
Example #2
-1
        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;
        }