コード例 #1
0
		public SettingsManager()
		{
			WallbinViewSettings = new WallbinViewSettings();
			WallbinButtonsSettings = new WallbinButtonsSettings();
			CalendarViewSettings = new CalendarViewSettings();
			ProgramScheduleSettings = new ProgramScheduleSettings();
			EmailBinSettings = new EmailBinSettings();
			LinkLaunchSettings = new LinkLaunchSettings();
			KeyWordFilters = new KeyWordFileFilters();
		}
コード例 #2
0
		public static void GenerateWeekSchedule(
			string templatePath,
			Day[][] days,
			string destinationFilePath,
			bool convertToPDF,
			bool landscape,
			ProgramScheduleSettings outputSettings)
		{
			if (!File.Exists(templatePath) || !ExcelHelper.Instance.Connect()) return;
			try
			{
				var destinationWorkBook = ExcelHelper.Instance.ExcelObject.Workbooks.Add();

				for (var i = 1; i < 3; i++)
					try
					{
						destinationWorkBook.Worksheets[i].Delete();
					}
					catch
					{
						break;
					}

				var worksheetIndex = 1;
				var sheduleGenrated = DateTime.Now;

				foreach (var weekDays in days)
				{
					var sourceWorkBook = ExcelHelper.Instance.ExcelObject.Workbooks.Open(templatePath);
					Worksheet workSheet = sourceWorkBook.Worksheets["Week"];

					var title = string.Format("{0} - Weekly Program Schedule", weekDays.FirstOrDefault().Station.Name);
					var dateRange = string.Format("Week of {0}", weekDays.FirstOrDefault().Date.ToString("MMMM d, yyyy"));
					workSheet.PageSetup.CenterHeader = String.Format("&\"{0}{2}\"&{1}", outputSettings.HeaderFont.Name, outputSettings.HeaderFont.Size.ToString(), outputSettings.HeaderFont.Bold ? ",bold" : string.Empty) + title + (char)13 + dateRange;

					workSheet.PageSetup.CenterFooter = String.Format("&\"{0}{2}\"&{1}", outputSettings.FooterFont.Name, outputSettings.FooterFont.Size.ToString(), outputSettings.FooterFont.Bold ? ",bold" : string.Empty) + "Schedule Generated" + (char)13 + sheduleGenrated.ToString("MM/dd/yy h:mm tt");

					var range = workSheet.Range["Data"];
					range.Font.Name = outputSettings.BodyFont.Name;
					range.Font.Size = outputSettings.BodyFont.Size;
					range.Font.Bold = outputSettings.BodyFont.Bold;
					range.Font.Italic = outputSettings.BodyFont.Italic;

					if (outputSettings.UsePrimeTimeSpecialFontSize)
					{
						{
							var firstColumn = workSheet.Range["day1"].Column;
							var lastColumn = workSheet.Range["day6"].Column;

							var firstRow = workSheet.Range["day1"].Row + 1;
							var time = new DateTime(1, 1, 1, 5, 0, 0);
							while (!(time.Hour == outputSettings.WeekPrimeTimeStart.Hour && time.Minute == outputSettings.WeekPrimeTimeStart.Minute))
							{
								time = time.AddMinutes(30);
								firstRow++;
							}

							var lastRow = firstRow;
							while (!(time.Hour == outputSettings.WeekPrimeTimeEnd.Hour && time.Minute == outputSettings.WeekPrimeTimeEnd.Minute))
							{
								time = time.AddMinutes(30);
								lastRow++;
							}

							range = workSheet.Range[GetColumnLetterByIndex(firstColumn) + firstRow + ":" + GetColumnLetterByIndex(lastColumn) + lastRow];
							range.Font.Name = outputSettings.BodyFont.Name;
							range.Font.Size = outputSettings.PrimeTimeSpecialFontSize;
							range.Font.Bold = outputSettings.BodyFont.Bold;
							range.Font.Italic = outputSettings.BodyFont.Italic;
						}

						{
							var firstColumn = workSheet.Range["day7"].Column;
							var lastColumn = workSheet.Range["day7"].Column;

							var firstRow = workSheet.Range["day7"].Row + 1;
							var time = new DateTime(1, 1, 1, 5, 0, 0);
							while (!(time.Hour == outputSettings.SundayPrimeTimeStart.Hour && time.Minute == outputSettings.SundayPrimeTimeStart.Minute))
							{
								time = time.AddMinutes(30);
								firstRow++;
							}

							var lastRow = firstRow;
							while (!(time.Hour == outputSettings.SundayPrimeTimeEnd.Hour && time.Minute == outputSettings.SundayPrimeTimeEnd.Minute))
							{
								time = time.AddMinutes(30);
								lastRow++;
							}

							range = workSheet.Range[GetColumnLetterByIndex(firstColumn) + firstRow + ":" + GetColumnLetterByIndex(lastColumn) + lastRow];
							range.Font.Name = outputSettings.BodyFont.Name;
							range.Font.Size = outputSettings.PrimeTimeSpecialFontSize;
							range.Font.Bold = outputSettings.BodyFont.Bold;
							range.Font.Italic = outputSettings.BodyFont.Italic;
						}
					}

					var values = new object[48, 7];
					for (var j = 0; j < 48; j++)
						for (var i = 0; i < 7; i++)
							values[j, i] = weekDays[i].ProgramActivities[j].Program;
					workSheet.Range["Data"].Value2 = values;

					for (var i = 0; i < 7; i++)
					{
						range = workSheet.Range["day" + (i + 1)];
						range.Formula = weekDays[i].Date.ToString(landscape ? "dddd M/d" : "ddd M/d");
						var columnIndex = range.Column;
						var rowIndex = range.Row + 1;
						var programName = string.Empty;
						var firstRow = 0;
						for (var r = 0; r < 48; r++)
						{
							object value = workSheet.Range[GetColumnLetterByIndex(columnIndex) + (rowIndex + r)].Value;
							var currentProgramName = value != null ? value.ToString() : string.Empty;
							if (currentProgramName.Equals(programName)) continue;
							if (!string.IsNullOrEmpty(programName))
								workSheet.Range[GetColumnLetterByIndex(columnIndex) + firstRow + ":" + GetColumnLetterByIndex(columnIndex) + (rowIndex + r - 1)].Merge();
							firstRow = rowIndex + r;
							programName = currentProgramName;
						}
					}

					for (var i = 0; i < 7; i++)
					{
						range = workSheet.Range["day" + (i + 1)];
						var columnIndex = range.Column;
						var r = 0;
						object value;
						do
						{
							value = null;
							var rowIndex = 0;
							var rowRange = range.Offset[r];
							try
							{
								value = rowRange.Formula;
								rowIndex = rowRange.Row;
							}
							catch { }
							if (value != null)
							{
								var programName = string.Empty;
								var firstColumn = 0;
								for (var j = i; j < 7; j++)
								{
									object nextValue = null;
									try
									{
										nextValue = rowRange.Offset[ColumnOffset: j].Value;
									}
									catch { }
									var currentProgramName = nextValue != null ? nextValue.ToString() : string.Empty;
									if (currentProgramName.Equals(programName)) continue;
									if (!string.IsNullOrEmpty(programName))
										workSheet.Range[GetColumnLetterByIndex(firstColumn) + rowIndex + ":" + GetColumnLetterByIndex(columnIndex + j - 1) + rowIndex].Merge();
									firstColumn = columnIndex + j;
									programName = currentProgramName;
								}
							}
							r++;
						} while (value != null && r <= 48);
					}

					var dataRange = workSheet.Range["Data"];

					dataRange.Rows.AutoFit();

					//set correct page breaks
					var fisrtColumnNumber = workSheet.Range["day1"].Column;
					var lastColumnNumber = workSheet.Range["day7"].Column;
					var pageBreaks = workSheet.HPageBreaks;
					for (var i = 1; i <= pageBreaks.Count; i++)
					{
						var currentBreakRow = pageBreaks[i].Location.Row;
						var newBreakRow = currentBreakRow;
						var beforeRange = pageBreaks[i].Location;
						for (var j = fisrtColumnNumber; j <= lastColumnNumber; j++)
						{
							Range cellRange = workSheet.Cells[currentBreakRow, j];
							if (!cellRange.MergeCells) continue;
							if (beforeRange.Row > cellRange.MergeArea.Row)
							{
								beforeRange = cellRange.MergeArea;
								newBreakRow = beforeRange.Row;
							}
						}
						if (newBreakRow != currentBreakRow)
							workSheet.HPageBreaks.Add(beforeRange);
					}

					workSheet.Copy(After: destinationWorkBook.Worksheets[worksheetIndex]);
					worksheetIndex++;
					workSheet = destinationWorkBook.Worksheets[worksheetIndex];
					workSheet.Name = weekDays.FirstOrDefault().Date.ToString("MMddyy") + "-" + weekDays.LastOrDefault().Date.ToString("MMddyy");
					sourceWorkBook.Close();
				}

				destinationWorkBook.Worksheets[1].Delete();
				if (convertToPDF)
					destinationWorkBook.ExportAsFixedFormat(Filename: destinationFilePath, Type: XlFixedFormatType.xlTypePDF);
				else
					destinationWorkBook.SaveAs(destinationFilePath, XlFileFormat.xlWorkbookNormal);

				destinationWorkBook.Close();
			}
			catch { }
			finally
			{
				ExcelHelper.Instance.Disconnect();
			}
		}