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; } }