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