예제 #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;
        }