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;
}
static void Main(string[] args)
{
if (args.Length < 2)
{
Console.WriteLine("Usage: ResourceTool.exe -i/e sample.xlsx");
return;
}
switch (args[0])
{
case "-i":
{
var doc = new SLDocument(args[1]);
doc.SelectWorksheet("Translations");
var stat = doc.GetWorksheetStatistics();
var languages = new List<string>();
for (var i = 3; i <= stat.EndColumnIndex; i++)
{
var lang = doc.GetCellValueAsString(2, i);
var resxName = lang == "en-US" ? "Resources.resx" : string.Format("Resources.{0}.resx", lang);
languages.Add(resxName);
}
for (var j = 0; j < languages.Count; j++)
{
var language = languages[j];
var resxFile = Path.Combine(RootPath, "Properties", language);
var resx = new XmlDocument();
resx.Load(resxFile);
for (var i = 3; i <= stat.EndRowIndex; i++)
{
var key = doc.GetCellValueAsString(i, 1);
Console.WriteLine("{0} {1} {2}", i, key, language);
var value = doc.GetCellValueAsString(i, j + 3);
var node = resx.SelectSingleNode(string.Format(".//data[@name='{0}']", key));
if (node == null)
{
node = resx.CreateElement("data");
var name = resx.CreateAttribute("name");
name.Value = key;
node.Attributes.Append(name);
var xmlspace = resx.CreateAttribute("xml:space");
xmlspace.Value = "preserve";
node.Attributes.Append(xmlspace);
resx.DocumentElement.AppendChild(node);
var valueNode = resx.CreateElement("value");
valueNode.InnerText = value;
node.AppendChild(valueNode);
}
else
{
var valueNode = node.SelectSingleNode("value");
if (valueNode == null)
{
valueNode = resx.CreateElement("value");
node.AppendChild(valueNode);
}
valueNode.InnerText = value;
}
}
resx.Save(resxFile);
}
}
break;
case "-e":
{
if (File.Exists(args[1])) File.Delete(args[1]);
var r = new Regex(@"Resources\.([a-z]{2}-[A-Z]{2})?\.?resx");
var doc = new SLDocument();
doc.AddWorksheet("Translations");
doc.DeleteWorksheet("Sheet1");
doc.SelectWorksheet("Translations");
doc.SetCellValue(1, 1, "Key");
doc.SetCellValue(1, 2, "Comment");
var headerStyle = doc.CreateStyle();
headerStyle.Font.Bold = true;
headerStyle.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightBlue,
System.Drawing.Color.LightBlue);
doc.SetCellStyle(1, 1, headerStyle);
doc.SetCellStyle(1, 2, headerStyle);
var resxFiles = Directory.GetFiles(Path.Combine(RootPath, "Properties"), "*.resx");
var columnIndex = 3;
var keys = new List<string>();
var files = resxFiles.OrderBy(n =>
{
var l = r.Match(Path.GetFileName(n)).Groups[1].Value;
return string.IsNullOrEmpty(l) ? null : n;
});
foreach (var resx in files)
{
var lang = r.Match(Path.GetFileName(resx)).Groups[1].Value;
var english = false;
if (string.IsNullOrEmpty(lang))
{
lang = "en-US";
english = true;
}
var ci = CultureInfo.GetCultureInfo(lang);
doc.SetCellValue(1, columnIndex, ci.EnglishName);
doc.SetCellValue(2, columnIndex, ci.Name);
doc.SetCellStyle(1, columnIndex, headerStyle);
doc.SetColumnWidth(columnIndex, 100);
var rr = new ResXResourceReader(resx);
if (english)
{
rr.UseResXDataNodes = true;
var rowIndex = 3;
foreach (DictionaryEntry entry in rr)
{
var key = (string) entry.Key;
var node = (ResXDataNode) entry.Value;
keys.Add(key);
doc.SetCellValue(rowIndex, 1, key);
doc.SetCellValue(rowIndex, 2, node.Comment);
doc.SetCellValue(rowIndex, columnIndex,
node.GetValue((ITypeResolutionService) null).ToString());
rowIndex++;
}
}
else
{
rr.UseResXDataNodes = true;
foreach (DictionaryEntry entry in rr)
{
var key = (string) entry.Key;
var node = (ResXDataNode) entry.Value;
var index = keys.IndexOf(key);
int rowIndex;
if (index == -1)
{
rowIndex = keys.Count;
keys.Add(key);
doc.SetCellValue(rowIndex, 1, key);
}
else
{
rowIndex = index + 3;
}
doc.SetCellValue(rowIndex, columnIndex,
node.GetValue((ITypeResolutionService) null).ToString());
}
}
columnIndex++;
}
doc.AutoFitColumn(1, 2);
doc.SaveAs(args[1]);
}
break;
default:
Console.WriteLine("Unknown switch {0}", args[0]);
return;
}
}
