Exemplo n.º 1
-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;
        }