EPPlus 自訂擴充功能

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

static class EPPlusExtensions { /// <summary> /// 水平 / 垂直 置中 /// </summary> public static void HorizontalVerticalAlignmentCenter(this OfficeOpenXml.Style.ExcelStyle obj) { obj.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; obj.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; } /// <summary> /// 設定儲存格填滿顏色和格式 /// </summary> public static void SetBackgroundColor(this OfficeOpenXml.Style.ExcelFill obj, System.Drawing.Color color,OfficeOpenXml.Style.ExcelFillStyle excelFillStyle = OfficeOpenXml.Style.ExcelFillStyle.Solid) { obj.PatternType = excelFillStyle; obj.BackgroundColor.SetColor(color); } /// <summary> /// 多列高度設定 /// </summary> public static void Row(this OfficeOpenXml.ExcelWorksheet obj, int StartRow, int EndRow, double Height) { for (int i = StartRow; i <= EndRow; i++) { obj.Row(i).Height = Height; } } /// <summary> /// 多欄寬度設定 /// </summary> public static void Column(this OfficeOpenXml.ExcelWorksheet obj, int StartCol, int EndCol, double Width) { for (int i = StartCol; i < EndCol; i++) { obj.Column(i).Width = Width; } } }

取得各年度週數清單

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

/// <summary> /// 取得該年度週數清單，Key:W601, Value:2015/12/27 ~ 2016/01/02 /// </summary> /// <param name="Year"></param> /// <returns></returns> public List<KeyValuePair<string,string>> WeekList(int Year) // 開始時間 { DateTime dt1 = new DateTime(Year, 1, 1); int week = (int)dt1.DayOfWeek; List<KeyValuePair<string, string>> list = new List<KeyValuePair<string, string>>(); Calendar cal = DateTimeFormatInfo.CurrentInfo.Calendar; dt1 = dt1.AddDays(week * -1); int nweek; StringBuilder sb = new StringBuilder(); while (dt1.Year <= Year) { if (dt1.Year < Year) { list.Add(new KeyValuePair<string,string>("W" + Year % 10 + "01" , dt1.ToString("yyyy/MM/dd" + " ~ " + dt1.AddDays(6).ToString("yyyy/MM/dd")))); } else { nweek = cal.GetWeekOfYear(dt1, CalendarWeekRule.FirstDay, DayOfWeek.Sunday); list.Add(new KeyValuePair<string,string>("W" + dt1.Year % 10 + nweek.ToString("00") , dt1.ToString("yyyy/MM/dd" + " ~ " + dt1.AddDays(6).ToString("yyyy/MM/dd")))); } dt1 = dt1.AddDays(7); } return list; }