public static void GenerateActivityList(
string templatePath,
ProgramActivity[] activities,
string destinationFilePath,
bool convertToPDF)
{
if (!File.Exists(templatePath) || !ExcelHelper.Instance.Connect()) return;
try
{
var sourceWorkBook = ExcelHelper.Instance.ExcelObject.Workbooks.Open(templatePath);
Worksheet workSheet = sourceWorkBook.Worksheets["Program Activities"];
var start = activities.First().Time;
var end = activities.Last().Time;
var title = string.Format("Program Activity For {0}", activities.First().Day.Station.Name);
var dateRange = string.Format("Between {0} and {1} from {2} to {3}", start.ToString("MM/dd/yyyy"), end.ToString("MM/dd/yyyy"), start.ToString("hh:mmtt"), end.ToString("hh:mmtt"));
workSheet.PageSetup.CenterHeader = "&12&B" + title + (char)13 + dateRange;
var sheduleGenrated = DateTime.Now;
workSheet.PageSetup.CenterFooter = "&11Generated" + (char)13 + sheduleGenrated.ToString("MM/dd/yy h:mm tt");
var range = workSheet.Range["Date"];
var firstRow = range.Row + 1;
var lastRow = firstRow;
var firstColumn = range.Column;
range = workSheet.Range["Type"];
var lastColumn = range.Column;
var rows = new List<object[]>();
foreach (var activity in activities)
{
var cells = new List<object>();
cells.Add(activity.Date.ToString("MM/dd/yyyy"));
cells.Add(activity.Date.ToString("ddd"));
cells.Add(activity.Time.ToString("hh:mmtt"));
cells.Add(activity.Program);
cells.Add(activity.HouseNumber);
cells.Add(activity.Episode);
cells.Add(activity.Type);
rows.Add(cells.ToArray());
lastRow++;
}
if (lastRow > firstRow)
lastRow--;
if (rows.Count > 0)
{
var values = new object[rows.Count, rows[0].Length];
for (var i = 0; i < rows.Count; i++)
for (int j = 0; j < rows[0].Length; j++)
values[i, j] = rows[i][j];
range = workSheet.Range[GetColumnLetterByIndex(firstColumn) + firstRow + ":" + GetColumnLetterByIndex(lastColumn) + lastRow];
range.Value2 = values;
range.Rows.AutoFit();
range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous;
range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous;
if (convertToPDF)
sourceWorkBook.ExportAsFixedFormat(Filename: destinationFilePath, Type: XlFixedFormatType.xlTypePDF);
else
sourceWorkBook.SaveAs(destinationFilePath, XlFileFormat.xlWorkbookNormal);
}
sourceWorkBook.Close();
}
catch { }
finally
{
ExcelHelper.Instance.Disconnect();
}
}