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;
}
// TODO: Hyperlink cell range
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="HyperlinkType">The type of hyperlink.</param>
/// <param name="Address">The URL for web pages, the file path for existing files, a cell reference (such as Sheet1!A1 or Sheet1!A1:B5), a defined name or an email address. NOTE: Do NOT include the "mailto:" portion for email addresses.</param>
/// <param name="Display">The display text. Set null or an empty string to use the default.</param>
/// <param name="ToolTip">The tooltip (or screentip) text. Set null or an empty string to ignore this.</param>
/// <param name="OverwriteExistingCell">True to overwrite the existing cell value with the hyperlink display text. False otherwise.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool InsertHyperlink(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip, bool OverwriteExistingCell)
{
if (RowIndex < 1 || RowIndex > SLConstants.RowLimit) return false;
if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit) return false;
SLHyperlink hl = new SLHyperlink();
hl.IsNew = true;
hl.Reference = new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex);
switch (HyperlinkType)
{
case SLHyperlinkTypeValues.EmailAddress:
hl.IsExternal = true;
hl.HyperlinkUri = string.Format("mailto:{0}", Address);
hl.HyperlinkUriKind = UriKind.Absolute;
break;
case SLHyperlinkTypeValues.FilePath:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
// assume if it starts with ../ or ./ it's a relative path.
hl.HyperlinkUriKind = Address.StartsWith(".") ? UriKind.Relative : UriKind.Absolute;
break;
case SLHyperlinkTypeValues.InternalDocumentLink:
hl.IsExternal = false;
hl.Location = Address;
break;
case SLHyperlinkTypeValues.Url:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
hl.HyperlinkUriKind = UriKind.Absolute;
break;
}
if (Display == null)
{
hl.Display = Address;
}
else
{
if (Display.Length == 0)
{
hl.Display = Address;
}
else
{
hl.Display = Display;
}
}
if (ToolTip != null && ToolTip.Length > 0) hl.ToolTip = ToolTip;
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
SLCell c;
SLStyle style;
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
style = new SLStyle();
if (c.StyleIndex < listStyle.Count) style.FromHash(listStyle[(int)c.StyleIndex]);
else style.FromHash(listStyle[0]);
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());
if (OverwriteExistingCell)
{
// in case there's a formula
c.CellFormula = null;
c.DataType = CellValues.SharedString;
c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
}
// else don't have to do anything
slws.Cells[pt] = c.Clone();
}
else
{
c = new SLCell();
style = new SLStyle();
style.FromHash(listStyle[0]);
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());
c.DataType = CellValues.SharedString;
c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
slws.Cells[pt] = c.Clone();
}
slws.Hyperlinks.Add(hl);
return true;
}