To Insert Data into Excel using Office object libray

C# file code
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using Microsoft.Office.Core;
using System.Drawing;
using SAMSBusinessLayer;
using SAMSDataAccessLayer;
using System.IO;
using System.Diagnostics;
public partial class Admin_Report_SamsReport : basePageSessionExpire
{
#region Private_Variable
Microsoft.Office.Interop.Excel.Application excelapp;
Microsoft.Office.Interop.Excel.Workbook wb;
int iSelectedMonth = 0;
int iSelectedYear = 0;
ExecuteSummaryDataFilter objExecuteSummaryDataFilter;
ExecuteSummaryData objExecuteSummaryData;
int iRowNo_for_Executive_Summary_Sheet = 1;
int iRowNo_for_Slide_Views_Sheet = 1;
int iRowNo_for_Fulfillment_Report_Sheet = 1;
string Table_Logins_By_Month_StartRange = "";
string Table_Logins_By_Month_EndRange = "";
string Table_Logins_By_Therapeutic_Area_StartRange = "";
string Table_Logins_By_Therapeutic_Area_EndRange = "";
string Table_Downloads_By_Therapeutic_Area_StartRange = "";
string Table_Downloads_By_Therapeutic_Area_EndRange = "";
string Table_Fulfillment_By_Month_StartRange = "";
string Table_Fulfillment_By_Month_EndRange = "";
string Table_Fulfillment_Type_YTD_StartRange = "";
string Table_Fulfillment_Type_YTD_EndRange = "";
string Table_Fulfillment_By_Month_Totals_StartRange = "";
string Table_Fulfillment_By_Month_Totals_EndRange = "";
string Table_Emails_Sent_to_Customer_Unique_Download_StartRange = "";
string Table_Emails_Sent_to_Customer_Unique_Download_EndRange = "";
string Table_November_Views_Downloads_By_Therapeutic_Area_StartRange = "";
string Table_November_Views_Downloads_By_Therapeutic_Area_EndRange = "";
string Table_Downloads_By_Therapeutic_Area_By_Months_StartRange = "";
string Table_Downloads_By_Therapeutic_Area_By_Months_EndRange = "";
string Table_Slide_Topic_Additions_By_Therapeutic_Area_StartRange = "" ;
string Table_Slide_Topic_Additions_By_Therapeutic_Area__EndRange = "";
string Table_Fulfillment_Numbers_By_User_Group_StartRange = "";
string Table_Fulfillment_Numbers_By_User_Group_EndRange = "";
string Table_Views_By_Therapeutic_Area_StartRange = "";
string Table_Views_By_Therapeutic_Area_EndRange = "";
System.Data.DataTable dtTherapeuticViewDownload;
System.Data.DataTable dtSlideTopicDownloadDetails;
#endregion
#region ##### Slide Views And Downloads sheet #####
private void Show_Slide_Views_And_Downloads_Sheet(Microsoft.Office.Interop.Excel.Worksheet wsSourceData)
{
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ws.Name = "Slide Views and Downloads";
ws.Cells[iRowNo_for_Slide_Views_Sheet, 1] = "Slide Views and Downloads";
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "B" + iRowNo_for_Slide_Views_Sheet).Cells.MergeCells = true;
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "B" + iRowNo_for_Slide_Views_Sheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "B" + iRowNo_for_Slide_Views_Sheet).Font.Name = "Cambria";
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "B" + iRowNo_for_Slide_Views_Sheet).Font.Size = 18;
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "B" + iRowNo_for_Slide_Views_Sheet).Font.Bold = true;
iRowNo_for_Slide_Views_Sheet += 1;
ws.Cells[iRowNo_for_Slide_Views_Sheet, 1] = "Report Date: " + GetReportDate();
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).Cells.MergeCells = true;
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).Font.ColorIndex = 16;
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).Font.Italic = true;
iRowNo_for_Slide_Views_Sheet += 2;

/*************************************************************
* this is for Table- Views By Therapeutic Area
************************************************************/
int iStartRow = iRowNo_for_Slide_Views_Sheet + 1;
double iYTD_Views = Convert.ToDouble(wsSourceData.get_Range("B" + (Convert.ToInt32(Table_Views_By_Therapeutic_Area_EndRange.Substring(1)) + 1), "B" + (Convert.ToInt32(Table_Views_By_Therapeutic_Area_EndRange.Substring(1)) + 1)).Cells.Value2);
ws.Cells[iRowNo_for_Slide_Views_Sheet, 1] = "Views by Therapeutic Area";
ws.Cells[iRowNo_for_Slide_Views_Sheet, 3] = "YTD Views: " + iYTD_Views.ToString();
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "B" + iRowNo_for_Slide_Views_Sheet).Cells.MergeCells = true;
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).Font.Bold = true;
ws.get_Range("C" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).get_Characters(11, 15).Font.ColorIndex = 3;
iRowNo_for_Slide_Views_Sheet += 1;
ws.Cells[iRowNo_for_Slide_Views_Sheet, 1] = "Therapeutic Area";
ws.Cells[iRowNo_for_Slide_Views_Sheet, 2] = GetMonthName(iSelectedMonth);


iRowNo_for_Slide_Views_Sheet += 1;
System.Data.DataTable dtViewsByTherapeuticArea = (System.Data.DataTable)objExecuteSummaryDataFilter.GetViewsByTherapeuticAreaFilter(iSelectedMonth,iSelectedYear);
int J = iRowNo_for_Slide_Views_Sheet;
int k = iRowNo_for_Slide_Views_Sheet - 1;
foreach (DataRow dr in dtViewsByTherapeuticArea.Rows)
{
ws.Cells[J, 1] = dr[0];
ws.Cells[J, 2] = dr[1];
//Going to the next row
J = J + 1;
}
///The Following code is used to set the color of DataBar
Microsoft.Office.Interop.Excel.Databar dt;
dt = (Databar)ws.get_Range("B" + (k + 1), "B" + (J - 1)).FormatConditions.AddDatabar();
Microsoft.Office.Interop.Excel.FormatColor fc = (Microsoft.Office.Interop.Excel.FormatColor)dt.BarColor;
fc.Color = ColorTranslator.ToOle(Color.Orange); //RGB(255,0,0);
iRowNo_for_Slide_Views_Sheet = J;
iRowNo_for_Slide_Views_Sheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + (k), "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Views_By_Therapeutic_Area1";
ws.ListObjects["Table_Views_By_Therapeutic_Area1"].TableStyle = "TableStyleMedium10";
ws.ListObjects["Table_Views_By_Therapeutic_Area1"].ShowTotals = true;
int iEndRow = iRowNo_for_Slide_Views_Sheet - 1;

///For Generating Chart
Microsoft.Office.Interop.Excel.ChartObjects chartObjs = (Microsoft.Office.Interop.Excel.ChartObjects)ws.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject chartObj1 = chartObjs.Add(0, 103, 220, 117);
Microsoft.Office.Interop.Excel.Chart xlChart1 = chartObj1.Chart;
xlChart1.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DPie; //for 3d pie chart.
xlChart1.ChartStyle = 26;
xlChart1.ChartArea.Format.ThreeD.RotationY = 30;
xlChart1.ApplyLayout(6, Microsoft.Office.Interop.Excel.XlChartType.xl3DPie);
xlChart1.SetSourceData(wsSourceData.get_Range(Table_Views_By_Therapeutic_Area_StartRange, Table_Views_By_Therapeutic_Area_EndRange), 2);
xlChart1.HasTitle = false;
xlChart1.ApplyDataLabels(Microsoft.Office.Interop.Excel.XlDataLabelsType.xlDataLabelsShowPercent, "false", "false", "True", "false", "false", "True", "True", "True", " , ");
chartObj1.Height = Convert.ToDouble(ws.get_Range("C" + iStartRow, "D" + iEndRow).Height);
chartObj1.Width = Convert.ToDouble(ws.get_Range("C" + iStartRow, "D" + iEndRow).Width);
chartObj1.Left = Convert.ToDouble(ws.get_Range("C" + iStartRow, "D" + iEndRow).Left);
chartObj1.Top = Convert.ToDouble(ws.get_Range("C" + iStartRow, "D" + iEndRow).Top);

/*******************************************
* Gererating Chart-November Views & Downloads By Therapeutic Area
* *****************************************/
iRowNo_for_Slide_Views_Sheet += 1;//For Above Graph
ws.Cells[iRowNo_for_Slide_Views_Sheet, 1] = GetMonthName(iSelectedMonth) + " Views & Downloads By Therapeutic Area";
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).Cells.MergeCells = true;
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "C" + iRowNo_for_Slide_Views_Sheet).Font.Bold = true;
iStartRow = iRowNo_for_Slide_Views_Sheet + 1;
iEndRow = iRowNo_for_Slide_Views_Sheet + 16;
chartObjs = (Microsoft.Office.Interop.Excel.ChartObjects)ws.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject chartObj2 = chartObjs.Add(0, 202, 320, 212);
Microsoft.Office.Interop.Excel.Chart xlChart2 = chartObj2.Chart;
xlChart2.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DBarStacked;
xlChart2.ChartStyle = 4;
xlChart2.ApplyLayout(4, Microsoft.Office.Interop.Excel.XlChartType.xl3DBarStacked);
xlChart2.SetSourceData(wsSourceData.get_Range(Table_November_Views_Downloads_By_Therapeutic_Area_StartRange, Table_November_Views_Downloads_By_Therapeutic_Area_EndRange), 2);
xlChart2.HasTitle = false;
xlChart2.RightAngleAxes = true;
xlChart2.Legend.Position = Microsoft.Office.Interop.Excel.XlLegendPosition.xlLegendPositionRight;
Microsoft.Office.Interop.Excel.Axes AxCat;
AxCat = (Microsoft.Office.Interop.Excel.Axes)xlChart2.Axes(Type.Missing, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
AxCat.Item(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).HasMajorGridlines = true;
chartObj2.Height = Convert.ToDouble(ws.get_Range("A" + iStartRow, "D" + iEndRow).Height);
chartObj2.Width = Convert.ToDouble(ws.get_Range("A" + iStartRow, "D" + iEndRow).Width);
chartObj2.Left = Convert.ToDouble(ws.get_Range("A" + iStartRow, "D" + iEndRow).Left);
chartObj2.Top = Convert.ToDouble(ws.get_Range("A" + iStartRow, "D" + iEndRow).Top);
ws.get_Range("A" + iRowNo_for_Slide_Views_Sheet, "D" + iRowNo_for_Slide_Views_Sheet).ColumnWidth = 26;
/******************************/
}
#endregion
#region ##### Fulfillment Report sheet #####
private void Show_Fulfillment_Report_Sheet(Microsoft.Office.Interop.Excel.Worksheet wsSourceData)
{
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ws.Name = "Fulfillment Report";
ws.Cells[iRowNo_for_Fulfillment_Report_Sheet, 1] = "Fulfillment Report";
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Cells.MergeCells = true;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Name = "Cambria";
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Size = 18;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Bold = true;
iRowNo_for_Fulfillment_Report_Sheet += 1;
ws.Cells[iRowNo_for_Fulfillment_Report_Sheet, 1] = "Report Date: " + GetReportDate();
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "C" + iRowNo_for_Fulfillment_Report_Sheet).Cells.MergeCells = true;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "C" + iRowNo_for_Fulfillment_Report_Sheet).Font.ColorIndex = 16;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "C" + iRowNo_for_Fulfillment_Report_Sheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "C" + iRowNo_for_Fulfillment_Report_Sheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "C" + iRowNo_for_Fulfillment_Report_Sheet).Font.Italic = true;

/*************************************************************
* this is for Table-Fulfillment Numbers by User Group
************************************************************/
iRowNo_for_Fulfillment_Report_Sheet += 2;
ws.Cells[iRowNo_for_Fulfillment_Report_Sheet, 1] = "Fulfillment Numbers by User Group";
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Cells.MergeCells = true;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Bold = true;
iRowNo_for_Fulfillment_Report_Sheet += 1;
ws.Cells[iRowNo_for_Fulfillment_Report_Sheet, 1] = "Group";
ws.Cells[iRowNo_for_Fulfillment_Report_Sheet, 2] = GetMonthName(iSelectedMonth);
ws.Cells[iRowNo_for_Fulfillment_Report_Sheet, 3] = "YTD";
iRowNo_for_Fulfillment_Report_Sheet += 1;
System.Data.DataTable dtFulfillment_Numbers_UserGroup = (System.Data.DataTable)objExecuteSummaryDataFilter.GetFulfillmentNumbersbyUserGroupFilter(iSelectedMonth,iSelectedYear);
int J = iRowNo_for_Fulfillment_Report_Sheet;
int k = iRowNo_for_Fulfillment_Report_Sheet - 1;
foreach (DataRow dr in dtFulfillment_Numbers_UserGroup.Rows)
{
ws.Cells[J, 1] = dr[0];
ws.Cells[J, 2] = dr[1];
ws.Cells[J, 3] = dr[2];
//Going to the next row
J = J + 1;
}
///The Following code is used to set the color of DataBar
Microsoft.Office.Interop.Excel.Databar dt;
dt = (Databar)ws.get_Range("B" + (k + 1), "C" + (J - 1)).FormatConditions.AddDatabar();
Microsoft.Office.Interop.Excel.FormatColor fc = (Microsoft.Office.Interop.Excel.FormatColor)dt.BarColor;
fc.Color = ColorTranslator.ToOle(Color.Orange); //RGB(255,0,0);
iRowNo_for_Fulfillment_Report_Sheet = J;
iRowNo_for_Fulfillment_Report_Sheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + (k), "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Fulfillment_Numbers_By_User_Group11";
ws.ListObjects["Table_Fulfillment_Numbers_By_User_Group11"].TableStyle = "TableStyleMedium11";
ws.ListObjects["Table_Fulfillment_Numbers_By_User_Group11"].ShowTotals = true;
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("C" + (k), "C" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Fulfillment_Numbers_By_User_Group21";
ws.ListObjects["Table_Fulfillment_Numbers_By_User_Group21"].TableStyle = "TableStyleMedium11";
ws.ListObjects["Table_Fulfillment_Numbers_By_User_Group21"].ShowTotals = true;

iRowNo_for_Fulfillment_Report_Sheet += 1; //For First Table
/*******************************************
* Gererating Chart-Fulfillment Type YTD
* *****************************************/

ws.Cells[iRowNo_for_Fulfillment_Report_Sheet, 1] = "Fulfillment Type YTD";
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Cells.MergeCells = true;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Bold = true;
Microsoft.Office.Interop.Excel.ChartObjects chartObjs = (Microsoft.Office.Interop.Excel.ChartObjects)ws.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject chartObj1 = chartObjs.Add(0, 103, 220, 117);
Microsoft.Office.Interop.Excel.Chart xlChart1 = chartObj1.Chart;
xlChart1.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DPie; //for 3d pie chart.
xlChart1.ChartStyle = 26;
xlChart1.ChartArea.Format.ThreeD.RotationY = 30;
xlChart1.ApplyLayout(6, Microsoft.Office.Interop.Excel.XlChartType.xl3DPie);
xlChart1.SetSourceData(wsSourceData.get_Range(Table_Fulfillment_Type_YTD_StartRange, Table_Fulfillment_Type_YTD_EndRange), 2);
xlChart1.HasTitle = false;
xlChart1.ApplyDataLabels(Microsoft.Office.Interop.Excel.XlDataLabelsType.xlDataLabelsShowPercent, "false", "false", "True", "false", "false", "True", "True", "True", " , ");
int iStartRow = iRowNo_for_Fulfillment_Report_Sheet + 1;
int iEndRow = iRowNo_for_Fulfillment_Report_Sheet + 8;
chartObj1.Height = Convert.ToDouble(ws.get_Range("A" + iStartRow, "C" + iEndRow).Height);
chartObj1.Width = Convert.ToDouble(ws.get_Range("A" + iStartRow, "C" + iEndRow).Width);
chartObj1.Left = Convert.ToDouble(ws.get_Range("A" + iStartRow, "C" + iEndRow).Left);
chartObj1.Top = Convert.ToDouble(ws.get_Range("A" + iStartRow, "C" + iEndRow).Top);
/******************************/
/*******************************************
* Gererating Chart-Email Open/Downloads
* *****************************************/
iRowNo_for_Fulfillment_Report_Sheet += 10;//For Above Graph
ws.Cells[iRowNo_for_Fulfillment_Report_Sheet, 1] = "Email Open/Downloads";
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Cells.MergeCells = true;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Bold = true;

Microsoft.Office.Interop.Excel.ChartObject chartObj2 = chartObjs.Add(0, 353, 220, 212);
Microsoft.Office.Interop.Excel.Chart xlChart2 = chartObj2.Chart;
xlChart2.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DBarStacked; //for 3d pie chart.
xlChart2.ChartStyle = 5;
xlChart2.ApplyLayout(4, Microsoft.Office.Interop.Excel.XlChartType.xl3DBarStacked);
xlChart2.SetSourceData(wsSourceData.get_Range(Table_Emails_Sent_to_Customer_Unique_Download_StartRange, Table_Emails_Sent_to_Customer_Unique_Download_EndRange), 2);
xlChart2.HasTitle = false;
xlChart2.RightAngleAxes = true;
Microsoft.Office.Interop.Excel.Axes AxCat;
AxCat = (Microsoft.Office.Interop.Excel.Axes)xlChart2.Axes(Type.Missing, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
AxCat.Item(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).HasMajorGridlines = true;
iStartRow = iRowNo_for_Fulfillment_Report_Sheet + 1;
iEndRow = iRowNo_for_Fulfillment_Report_Sheet + 14;
chartObj2.Height = Convert.ToDouble(ws.get_Range("A" + iStartRow, "C" + iEndRow).Height);
chartObj2.Width = Convert.ToDouble(ws.get_Range("A" + iStartRow, "C" + iEndRow).Width);
chartObj2.Left = Convert.ToDouble(ws.get_Range("A" + iStartRow, "C" + iEndRow).Left);
chartObj2.Top = Convert.ToDouble(ws.get_Range("A" + iStartRow, "C" + iEndRow).Top);
/******************************/
/*******************************************
* Gererating Table-Slide Sets Requested by Month
* *****************************************/
iRowNo_for_Fulfillment_Report_Sheet += 16;//For Above Graph
ws.Cells[iRowNo_for_Fulfillment_Report_Sheet, 1] = "Slide Sets Requested by Month";
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Cells.MergeCells = true;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "B" + iRowNo_for_Fulfillment_Report_Sheet).Font.Bold = true;
ws.get_Range("A" + iRowNo_for_Fulfillment_Report_Sheet, "H" + iRowNo_for_Fulfillment_Report_Sheet).ColumnWidth = 25;
iRowNo_for_Fulfillment_Report_Sheet += 1;
ws.Cells[iRowNo_for_Fulfillment_Report_Sheet, 1] = "Month";
ws.Cells[iRowNo_for_Fulfillment_Report_Sheet, 2] = "Number of Slide Sets Requested";
iRowNo_for_Fulfillment_Report_Sheet += 1;
System.Data.DataTable dtTable_Slide_Sets_Requested_by_Month = (System.Data.DataTable)objExecuteSummaryDataFilter.GetSlideSetsRequestedbyMonthFilter(iSelectedMonth,iSelectedYear); //Apply Month and Year Value Dynamic is left
J = iRowNo_for_Fulfillment_Report_Sheet;
k = iRowNo_for_Fulfillment_Report_Sheet - 1;
foreach (DataRow dr in dtTable_Slide_Sets_Requested_by_Month.Rows)
{
ws.Cells[J, 1] = dr[0];
ws.Cells[J, 2] = dr[1];
//Going to the next row
J = J + 1;
}
iRowNo_for_Fulfillment_Report_Sheet = J;
iRowNo_for_Fulfillment_Report_Sheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + (k), "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Slide_Sets_Requested_by_Month";
ws.ListObjects["Table_Slide_Sets_Requested_by_Month"].TableStyle = "TableStyleMedium11";
ws.ListObjects["Table_Slide_Sets_Requested_by_Month"].ShowTotals = true;

}
#endregion
#region ##### Page Load #####
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserName"] == null)
{
Response.Redirect("../Login.aspx");
}
else
{
System.Web.UI.WebControls.Label UserName = (System.Web.UI.WebControls.Label)Master.FindControl("lblWelcome");
UserName.Text = Session["UserName"].ToString();
}
HtmlAnchor hp=(HtmlAnchor)Master.FindControl("hyplnkPageTitle");
hp.InnerText="Monthly Report Management";
hp.HRef = "admin/report/SamsReport.aspx";
if (!IsPostBack)
{
GetSelectedValue();
//Session["FileToopen"] = "new";
}
BindUserGroups();
}
#endregion
#region ##### SourceDataSheet #####
private void Show_Source_Data_Sheet()
{
objExecuteSummaryData = new ExecuteSummaryData();
int iRowNo_for_SouceDataSheet = 1;
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
objExecuteSummaryData = new ExecuteSummaryData();
ws.Name = "Source Data";
/*************************************************************
* this is for First Table-Logins By Month
* Here I will left 12 rows for 12 months and then generate next table
************************************************************/

ws.get_Range("A1", "I1").ColumnWidth = 25;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Logins by Months";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Bold = true;
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Month";
ws.Cells[iRowNo_for_SouceDataSheet, 2] = "Logins";
iRowNo_for_SouceDataSheet += 1;
DataSet dsData = objExecuteSummaryData.GetDataForDashboard(iSelectedMonth,iSelectedYear);
int J = iRowNo_for_SouceDataSheet;
int k = iRowNo_for_SouceDataSheet - 1;
foreach (DataRow dr in dsData.Tables[0].Rows)
{
ws.Cells[J, 1] = dr[0];
ws.Cells[J, 2] = dr[1];
//Going to the next row
J = J + 1;
}
iRowNo_for_SouceDataSheet = J;
iRowNo_for_SouceDataSheet += 1;
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + (k), "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "TableLogin";
ws.ListObjects["TableLogin"].TableStyle = "TableStyleMedium9";
ws.ListObjects["TableLogin"].ShowTotals = true;
Table_Logins_By_Month_StartRange = "A" + k;
Table_Logins_By_Month_EndRange = "B" + (J - 1);
/*************************************************************
* this is for Second Table-Logins By Therapeutic Area
************************************************************/
iRowNo_for_SouceDataSheet += 1;


ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Logins By Therapeutic Area";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Bold = true;
iRowNo_for_SouceDataSheet += 1;
Table_Logins_By_Therapeutic_Area_StartRange = "A" + iRowNo_for_SouceDataSheet;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "User Group";
ws.Cells[iRowNo_for_SouceDataSheet, 2] = "YTD Logins";
iRowNo_for_SouceDataSheet += 1;
dsData = objExecuteSummaryData.GetDataSetForLoginsByTherapeuticArea(iSelectedYear);
J = iRowNo_for_SouceDataSheet;
k = iRowNo_for_SouceDataSheet - 1;
foreach (DataRow dr in dsData.Tables[0].Rows)
{
ws.Cells[J, 1] = dr[0];
ws.Cells[J, 2] = dr[1];
//Going to the next row
J = J + 1;
}

iRowNo_for_SouceDataSheet = J;
iRowNo_for_SouceDataSheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + (k), "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "TableLogins_By_Therapeutic_Area";
ws.ListObjects["TableLogins_By_Therapeutic_Area"].TableStyle = "TableStyleMedium9";
ws.ListObjects["TableLogins_By_Therapeutic_Area"].ShowTotals = true;
Table_Logins_By_Therapeutic_Area_StartRange = "A" + k;
Table_Logins_By_Therapeutic_Area_EndRange = "B" + (J - 1);
/*************************************************************
* this is for Table-Downloads by Therapeutic Area
************************************************************/


iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Downloads by Therapeutic Area";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Bold = true;
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Therapeutic Area";
ws.Cells[iRowNo_for_SouceDataSheet, 2] = "YTD Downloads";
iRowNo_for_SouceDataSheet += 1;
System.Data.DataTable dtTherapeuticalArea = objExecuteSummaryData.GetTherapeuticAreaYTD(iSelectedMonth,iSelectedYear);
J = iRowNo_for_SouceDataSheet;
k = iRowNo_for_SouceDataSheet - 1;
foreach (DataRow dr in dtTherapeuticalArea.Rows)
{
ws.Cells[J, 1] = dr[0];
ws.Cells[J, 2] = dr[1];
//Going to the next row
J = J + 1;
}
iRowNo_for_SouceDataSheet = J;
iRowNo_for_SouceDataSheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + (k), "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "TableDownloads_by_Therapeutic_Area";
ws.ListObjects["TableDownloads_by_Therapeutic_Area"].TableStyle = "TableStyleMedium9";
ws.ListObjects["TableDownloads_by_Therapeutic_Area"].ShowTotals = true;

Table_Downloads_By_Therapeutic_Area_StartRange = "A" + k;
Table_Downloads_By_Therapeutic_Area_EndRange = "B" + (J - 1);
/*************************************************************
* this is for Table-Fulfillment by Month
************************************************************/
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Fulfillment by Month";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Bold = true;
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Month";
ws.Cells[iRowNo_for_SouceDataSheet, 2] = "Email";
ws.Cells[iRowNo_for_SouceDataSheet, 3] = "Mail";
ws.Cells[iRowNo_for_SouceDataSheet, 4] = "Mail/Email";
iRowNo_for_SouceDataSheet += 1;
System.Data.DataTable dsDataFulfilment = objExecuteSummaryData.GetFulfillmentbyMonth(iSelectedMonth,iSelectedYear);
J = iRowNo_for_SouceDataSheet;
k = iRowNo_for_SouceDataSheet - 1;
foreach (DataRow dr in dsDataFulfilment.Rows)
{
ws.Cells[J, 1] = dr[0];
ws.Cells[J, 2] = dr[1];
ws.Cells[J, 3] = dr[2];
ws.Cells[J, 4] = dr[3];
//Going to the next row
J = J + 1;
}
iRowNo_for_SouceDataSheet = J;
iRowNo_for_SouceDataSheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + (k), "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "TableFulfillment_by_Month1";
ws.ListObjects["TableFulfillment_by_Month1"].TableStyle = "TableStyleMedium11";
ws.ListObjects["TableFulfillment_by_Month1"].ShowTotals = true;
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("C" + (k), "C" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "TableFulfillment_by_Month2";
ws.ListObjects["TableFulfillment_by_Month2"].TableStyle = "TableStyleMedium11";
ws.ListObjects["TableFulfillment_by_Month2"].ShowTotals = true;
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("D" + (k), "D" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "TableFulfillment_by_Month3";
ws.ListObjects["TableFulfillment_by_Month3"].TableStyle = "TableStyleMedium11";
ws.ListObjects["TableFulfillment_by_Month3"].ShowTotals = true;
Table_Fulfillment_By_Month_StartRange = "A" + k;
Table_Fulfillment_By_Month_EndRange = "D" + (J - 1);
/*************************************************************
* this is for Table-Fulfillment Type YTD
************************************************************/
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Fulfillment Type YTD";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Bold = true;
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Fulfillment Type";
ws.Cells[iRowNo_for_SouceDataSheet, 2] = "Number of Fulfillments";

iRowNo_for_SouceDataSheet += 1;
dsDataFulfilment = GetFullFillTypeYTD();
J = iRowNo_for_SouceDataSheet;
k = iRowNo_for_SouceDataSheet - 1;
foreach (DataRow dr in dsDataFulfilment.Rows)
{
ws.Cells[J, 1] = dr[0];
ws.Cells[J, 2] = dr[1];
//Going to the next row
J = J + 1;
}

iRowNo_for_SouceDataSheet = J;
iRowNo_for_SouceDataSheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + (k), "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Fulfillment_Type_YTD";
ws.ListObjects["Table_Fulfillment_Type_YTD"].TableStyle = "TableStyleMedium11";
ws.ListObjects["Table_Fulfillment_Type_YTD"].ShowTotals = true;
Table_Fulfillment_Type_YTD_StartRange = "A" + k;
Table_Fulfillment_Type_YTD_EndRange = "B" + (J - 1);
/*************************************************************
* this is for Table-Fulfillment by Month Totals
************************************************************/
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Fulfillment by Month Totals";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Bold = true;
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Month";
ws.Cells[iRowNo_for_SouceDataSheet, 2] = "Fulfillment";

iRowNo_for_SouceDataSheet += 1;
dsDataFulfilment = objExecuteSummaryData.GetFulfillmentbyMonth(iSelectedMonth,iSelectedYear);//GetFulfillmentbyMonth();//get data from database.
J = iRowNo_for_SouceDataSheet;
k = iRowNo_for_SouceDataSheet - 1;
foreach (DataRow dr in dsDataFulfilment.Rows)
{
ws.Cells[J, 1] = dr[0];
ws.Cells[J, 2] = dr[4];
//Going to the next row
J = J + 1;
}
iRowNo_for_SouceDataSheet = J;
iRowNo_for_SouceDataSheet += 1;

//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + (k), "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Fulfillment_By_Month_Totals";
ws.ListObjects["Table_Fulfillment_By_Month_Totals"].TableStyle = "TableStyleMedium9";
ws.ListObjects["Table_Fulfillment_By_Month_Totals"].ShowTotals = true;
Table_Fulfillment_By_Month_Totals_StartRange = "A" + k;
Table_Fulfillment_By_Month_Totals_EndRange = "B" + (J - 1);
/*************************************************************
* this is for Table-Emails Sent to Customer / Unique Download
************************************************************/
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Emails Sent to Customer / Unique Download";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "A" + iRowNo_for_SouceDataSheet).Font.Bold = true;
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Month";
ws.Cells[iRowNo_for_SouceDataSheet, 2] = "Emails Sent to Customer";
ws.Cells[iRowNo_for_SouceDataSheet, 3] = "Unique Downloads";


iRowNo_for_SouceDataSheet += 1;
dsDataFulfilment = objExecuteSummaryData.GetEmailReceivedAndDownload(iSelectedMonth,iSelectedYear);
J = iRowNo_for_SouceDataSheet;
k = iRowNo_for_SouceDataSheet - 1;
foreach (DataRow dr in dsDataFulfilment.Rows)
{
ws.Cells[J, 1] = dr[0];
ws.Cells[J, 2] = dr[1];
ws.Cells[J, 3] = dr[2];
//Going to the next row
J = J + 1;
}



iRowNo_for_SouceDataSheet = J;
iRowNo_for_SouceDataSheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + (k), "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Emails_Sent_to_Customer_Unique_Download1";
ws.ListObjects["Table_Emails_Sent_to_Customer_Unique_Download1"].TableStyle = "TableStyleMedium11";
ws.ListObjects["Table_Emails_Sent_to_Customer_Unique_Download1"].ShowTotals = true;
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("C" + (k), "C" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Emails_Sent_to_Customer_Unique_Download2";
ws.ListObjects["Table_Emails_Sent_to_Customer_Unique_Download2"].TableStyle = "TableStyleMedium11";
ws.ListObjects["Table_Emails_Sent_to_Customer_Unique_Download2"].ShowTotals = true;
Table_Emails_Sent_to_Customer_Unique_Download_StartRange = "A" + k;
Table_Emails_Sent_to_Customer_Unique_Download_EndRange = "C" + (J - 1);
/*************************************************************
* this is for Table-Email Open/Download Report Totals YTD
************************************************************/
iRowNo_for_SouceDataSheet += 1;

iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Total Emails Sent to Customer";
ws.Cells[iRowNo_for_SouceDataSheet, 2] = "=SUM(B" + (k + 1) + ":B" + (J - 1) + ")";

iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Total Unique Downloads";
ws.Cells[iRowNo_for_SouceDataSheet, 2] = "=SUM(C" + (k + 1) + ":C" + (J - 1) + ")";
iRowNo_for_SouceDataSheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + (iRowNo_for_SouceDataSheet - 3), "B" + (iRowNo_for_SouceDataSheet - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Email_Open_Download_Report_Totals_YTD";
ws.ListObjects["Table_Email_Open_Download_Report_Totals_YTD"].TableStyle = "TableStyleMedium25";
ws.ListObjects["Table_Email_Open_Download_Report_Totals_YTD"].ShowHeaders = false;
ws.ListObjects["Table_Email_Open_Download_Report_Totals_YTD"].ShowTableStyleRowStripes = true;
ws.Cells[iRowNo_for_SouceDataSheet - 3, 1] = "Email Open/Download Report Totals YTD";
ws.get_Range("A" + (iRowNo_for_SouceDataSheet - 3), "A" + (iRowNo_for_SouceDataSheet - 3)).Font.ColorIndex = 11;
ws.get_Range("A" + (iRowNo_for_SouceDataSheet - 3), "A" + (iRowNo_for_SouceDataSheet - 3)).Font.Name = "Calibri";
ws.get_Range("A" + (iRowNo_for_SouceDataSheet - 3), "A" + (iRowNo_for_SouceDataSheet - 3)).Font.Size = 11;
ws.get_Range("A" + (iRowNo_for_SouceDataSheet - 3), "A" + (iRowNo_for_SouceDataSheet - 3)).Font.Bold = true;

/*************************************************************
* this is for Table- Views By Therapeutic Area
************************************************************/
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Views By Therapeutic Area";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Font.Bold = true;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Cells.MergeCells = true;
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Therapeutic Area";
ws.Cells[iRowNo_for_SouceDataSheet, 2] = "YTD";
ws.Cells[iRowNo_for_SouceDataSheet, 3] = GetMonthName(iSelectedMonth);


iRowNo_for_SouceDataSheet += 1;
System.Data.DataTable dtViewsByTherapeuticArea = (System.Data.DataTable)objExecuteSummaryDataFilter.GetViewsByTherapeuticAreaFilter(iSelectedMonth, iSelectedYear);
J = iRowNo_for_SouceDataSheet;
k = iRowNo_for_SouceDataSheet - 1;
foreach (DataRow dr in dtViewsByTherapeuticArea.Rows)
{
ws.Cells[J, 1] = dr[0];
ws.Cells[J, 2] = dr[2];
ws.Cells[J, 3] = dr[1];
//Going to the next row
J = J + 1;
}
iRowNo_for_SouceDataSheet = J;
iRowNo_for_SouceDataSheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + (k), "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Views_By_Therapeutic_Area1";
ws.ListObjects["Table_Views_By_Therapeutic_Area1"].TableStyle = "TableStyleMedium10";
ws.ListObjects["Table_Views_By_Therapeutic_Area1"].ShowTotals = true;
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("C" + (k), "C" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Views_By_Therapeutic_Area2";
ws.ListObjects["Table_Views_By_Therapeutic_Area2"].TableStyle = "TableStyleMedium10";
ws.ListObjects["Table_Views_By_Therapeutic_Area2"].ShowTotals = true;
Table_Views_By_Therapeutic_Area_StartRange = "A" + k;
Table_Views_By_Therapeutic_Area_EndRange = "C" + (J - 1);
/*************************************************************
* this is for Table-November Views & Downloads By Therapeutic Area
************************************************************/
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = GetMonthName(iSelectedMonth)+" Views & Downloads By Therapeutic Area";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Font.Bold = true;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Cells.MergeCells = true;
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Therapeutic Area";
ws.Cells[iRowNo_for_SouceDataSheet, 2] = "Views";
ws.Cells[iRowNo_for_SouceDataSheet, 3] = "Downloads";


iRowNo_for_SouceDataSheet += 1;
dsDataFulfilment = GetTherapeuticAreaViewDownLoad(iSelectedMonth,iSelectedYear);//get data from database.
J = iRowNo_for_SouceDataSheet;
k = iRowNo_for_SouceDataSheet - 1;
foreach (DataRow dr in dsDataFulfilment.Rows)
{
ws.Cells[J, 1] = dr[0];
ws.Cells[J, 2] = dr[1];
ws.Cells[J, 3] = dr[2];
//Going to the next row
J = J + 1;
}
iRowNo_for_SouceDataSheet = J;
iRowNo_for_SouceDataSheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + (k), "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_November_Views_Downloads_By_Therapeutic_Area1";
ws.ListObjects["Table_November_Views_Downloads_By_Therapeutic_Area1"].TableStyle = "TableStyleMedium10";
ws.ListObjects["Table_November_Views_Downloads_By_Therapeutic_Area1"].ShowTotals = true;
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("C" + (k), "C" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_November_Views_Downloads_By_Therapeutic_Area2";
ws.ListObjects["Table_November_Views_Downloads_By_Therapeutic_Area2"].TableStyle = "TableStyleMedium10";
ws.ListObjects["Table_November_Views_Downloads_By_Therapeutic_Area2"].ShowTotals = true;
Table_November_Views_Downloads_By_Therapeutic_Area_StartRange = "A" + k;
Table_November_Views_Downloads_By_Therapeutic_Area_EndRange = "C" + (J - 1);
/*************************************************************
* this is for Table-Downloads by Therapeutic Area By Months
************************************************************/
iRowNo_for_SouceDataSheet += 1;
ws.Cells[iRowNo_for_SouceDataSheet, 1] = "Downloads by Therapeutic Area By Months";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Font.ColorIndex = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Font.Name = "Calibri";
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Font.Size = 11;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Font.Bold = true;
ws.get_Range("A" + iRowNo_for_SouceDataSheet, "B" + iRowNo_for_SouceDataSheet).Cells.MergeCells = true;
iRowNo_for_SouceDataSheet += 1;
dsDataFulfilment = GetSlideTopicDownloadDetails(iSelectedMonth,iSelectedYear);//get data from database.
int colno = 1;
foreach (DataColumn dc in dsDataFulfilment.Columns)
{
ws.Cells[iRowNo_for_SouceDataSheet, colno] = dc.ColumnName;
colno += 1;
}
iRowNo_for_SouceDataSheet += 1;
J = iRowNo_for_SouceDataSheet;
k = iRowNo_for_SouceDataSheet - 1;
foreach (DataRow dr in dsDataFulfilment.Rows)
{
int colnoCount = 0;
foreach (DataColumn dc1 in dsDataFulfilment.Columns)
{
ws.Cells[J, colnoCount + 1] = dr[colnoCount];
colnoCount += 1;
}
//Going to the next row
J = J + 1;
}

string strLastColumnName = GetExcelColumnName(colno - 1);
iRowNo_for_SouceDataSheet = J;
iRowNo_for_SouceDataSheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + (k), "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Downloads_By_Therapeutic_Area_By_Months1";
ws.ListObjects["Table_Downloads_By_Therapeutic_Area_By_Months1"].TableStyle = "TableStyleMedium9";
ws.ListObjects["Table_Downloads_By_Therapeutic_Area_By_Months1"].ShowTotals = true;
for (int TotalCount = 3; TotalCount < strlastcolumnname =" GetExcelColumnName(TotalCount);" name = "Table_Downloads_By_Therapeutic_Area_By_Months" tablestyle = "TableStyleMedium9" showtotals =" true;" lastinex =" J" table_downloads_by_therapeutic_area_by_months_startrange =" k.ToString();" table_downloads_by_therapeutic_area_by_months_endrange =" lastinex.ToString();" colorindex =" 11;" name = "Calibri" size =" 11;" bold =" true;" mergecells =" true;" dtslidetopicytd =" (System.Data.DataTable)objExecuteSummaryDataFilter.GetSlideTopicAdditionsByTherapeuticAreaFilter(iSelectedYear);" j =" iRowNo_for_SouceDataSheet;" k =" iRowNo_for_SouceDataSheet" j =" J" irowno_for_soucedatasheet =" J;" name = "Table_Slide_Topic_Additions_By_Therapeutic_Area1" tablestyle = "TableStyleMedium9" showtotals =" true;" name = "Table_Slide_Topic_Additions_By_Therapeutic_Area2" tablestyle = "TableStyleMedium9" showtotals =" true;" name = "Table_Slide_Topic_Additions_By_Therapeutic_Area3" tablestyle = "TableStyleMedium9" showtotals =" true;" table_slide_topic_additions_by_therapeutic_area_startrange = "A" table_slide_topic_additions_by_therapeutic_area__endrange = "D" colorindex =" 11;" name = "Calibri" size =" 11;" bold =" true;" mergecells =" true;" dtfulfillment_numbers_usergroup =" (System.Data.DataTable)objExecuteSummaryDataFilter.GetFulfillmentNumbersbyUserGroupFilter(iSelectedMonth,iSelectedYear);" j =" iRowNo_for_SouceDataSheet;" k =" iRowNo_for_SouceDataSheet" j =" J" irowno_for_soucedatasheet =" J;" name = "Table_Fulfillment_Numbers_By_User_Group1" tablestyle = "TableStyleMedium11" showtotals =" true;" name = "Table_Fulfillment_Numbers_By_User_Group2" tablestyle = "TableStyleMedium11" showtotals =" true;" table_fulfillment_numbers_by_user_group_startrange = "A" table_fulfillment_numbers_by_user_group_endrange = "C" monthname = "" monthname = "January" monthname = "February" monthname = "March" monthname = "April" monthname = "May" monthname = "June" monthname = "July" monthname = "August" monthname = "September" monthname = "October" monthname = "November" monthname = "December" dtfftytd =" null;" dtfftytd =" (System.Data.DataTable)objExecuteSummaryData.GetFulfillmentbyMonth(iSelectedMonth,iSelectedYear);" email =" 0;" mail =" 0;" mailemail =" 0;" i =" 0;" dtfftytd1 =" new" rownew1 =" dtfftYTD1.NewRow();" rownew2 =" dtfftYTD1.NewRow();" rownew3 =" dtfftYTD1.NewRow();" dttherapeuticviewdownload =" new" ds =" new" ds =" objExecuteSummaryData.GetSlideTopicAdditionsByTherapeuticArea();"> 0)
{
for (int i = 0; i < secid =" 0;" strtitle = "" strvalue = "" secid =" Convert.ToInt32(ds.Tables[0].Rows[i][" strtitle =" ds.Tables[0].Rows[i][" strvalue =" objExecuteSummaryData.GetCountViewDownload(secID,month,year).ToString();" strarray =" strValue.Split(',');" rownew =" dtTherapeuticViewDownload.NewRow();" dtslidetopicdownloaddetails =" new" ds =" new" ds =" objExecuteSummaryData.GetSlideTopicAdditionsByTherapeuticArea();"> 0)
{
dtSlideTopicDownloadDetails.Columns.Add("Month");
for (int l = 0; l < strvalue = "" j =" 1;" strcountvalue = "" i =" 0;" id =" 0;" id =" Convert.ToInt32(ds.Tables[0].Rows[i][" strcountvalue ="objExecuteSummaryData.GetCountSlideTopicDownload(id," strcountvalue =" strCountValue.Substring(0," strvalue =" strValue.Substring(0," strarrayvalue =" strValue.Split('^');" m =" 0;" strcountarray =" strArrayValue[m].Split(',');" rownew =" dtSlideTopicDownloadDetails.NewRow();" k =" 0;" ws =" (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets.Add(Type.Missing," name = "Executive Summary" chartobjs =" (Microsoft.Office.Interop.Excel.ChartObjects)ws.ChartObjects(Type.Missing);" chartobj =" chartObjs.Add(0," xlchart =" chartObj.Chart;" charttype =" Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;" chartstyle =" 26;" hastitle =" false;" axcat =" (Microsoft.Office.Interop.Excel.Axes)xlChart.Axes(Type.Missing," hasmajorgridlines =" true;" height =" Convert.ToDouble(ws.get_Range(" width =" Convert.ToDouble(ws.get_Range(" left =" Convert.ToDouble(ws.get_Range(" top =" Convert.ToDouble(ws.get_Range(" mergecells =" true;" colorindex =" 11;" name = "Cambria" size =" 18;" bold =" true;" mergecells =" true;" colorindex =" 16;" name = "Calibri" size =" 11;" italic =" true;" mergecells =" true;" colorindex =" 11;" name = "Calibri" size =" 11;" bold =" true;" iytd_logins =" Convert.ToDouble(wsSourceData.get_Range(" itotalmember =" iYTD_Logins" themecolor =" XlThemeColor.xlThemeColorAccent3;" themefont =" XlThemeFont.xlThemeFontMinor;" colorindex =" 2;" columnwidth =" 20;" mergecells =" true;" colorindex =" 11;" name = "Calibri" size =" 11;" bold =" true;" colorindex =" 3;" istartrowofchart =" iRowNo_for_Executive_Summary_Sheet;" iendrowofchart =" iRowNo_for_Executive_Summary_Sheet-1;" iytd_downloads =" Convert.ToDouble(wsSourceData.get_Range(" mergecells =" true;" colorindex =" 11;" name = "Calibri" size =" 11;" bold =" true;" colorindex =" 3;" istartrowofchart =" iRowNo_for_Executive_Summary_Sheet;" iendrowofchart =" iRowNo_for_Executive_Summary_Sheet;" islide_topic_addition_since_launch =" Convert.ToDouble(wsSourceData.get_Range(" mergecells =" true;" mergecells =" true;" colorindex =" 11;" name = "Calibri" size =" 11;" bold =" true;" colorindex =" 3;" istartrowofchart =" iRowNo_for_Executive_Summary_Sheet;" iendrowofchart =" iRowNo_for_Executive_Summary_Sheet-1;" islides_available =" iSlide_Topic_Addition_Since_Launch;" themecolor =" XlThemeColor.xlThemeColorAccent3;" themefont =" XlThemeFont.xlThemeFontMinor;" colorindex =" 2;" mergecells =" true;" mergecells =" true;" colorindex =" 11;" name = "Calibri" size =" 11;" bold =" true;" istartrowofchart =" iRowNo_for_Executive_Summary_Sheet+1;" iendrowofchart =" iRowNo_for_Executive_Summary_Sheet" chartobj1 =" chartObjs.Add(294," xlchart1 =" chartObj1.Chart;" charttype =" Microsoft.Office.Interop.Excel.XlChartType.xl3DPie;" chartstyle =" 26;" rotationy =" 30;" hastitle =" false;" height =" Convert.ToDouble(ws.get_Range(" width =" Convert.ToDouble(ws.get_Range(" left =" Convert.ToDouble(ws.get_Range(" top =" Convert.ToDouble(ws.get_Range(" strms =" ex.Message;" objexecutesummarydata =" new" chartobj1 =" chartObjs.Add(197," xlchart1 =" chartObj1.Chart;" dsdata1 =" objExecuteSummaryData.GetDataForDashboard(iSelectedMonth," charttype =" Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;" hastitle =" false;" insidetop =" 0;" insideleft =" 0;" chartobj1 =" chartObjs.Add(0," xlchart1 =" chartObj1.Chart;" charttype =" Microsoft.Office.Interop.Excel.XlChartType.xl3DColumnClustered;" chartstyle =" 2;" hastitle =" false;" rightangleaxes =" true;" showdatalabelsovermaximum =" true;" position =" Microsoft.Office.Interop.Excel.XlLegendPosition.xlLegendPositionRight;" axcat =" (Microsoft.Office.Interop.Excel.Axes)xlChart1.Axes(Type.Missing," hasmajorgridlines =" true;" height =" (Convert.ToDouble(ws.get_Range(">180)? Convert.ToDouble(ws.get_Range("A" + iStartRow, "E" + iEndRow).Height):180;
chartObj1.Width = Convert.ToDouble(ws.get_Range("A" + iStartRow, "E" + iEndRow).Width);
chartObj1.Left = Convert.ToDouble(ws.get_Range("A" + iStartRow, "E" + iEndRow).Left);
chartObj1.Top = Convert.ToDouble(ws.get_Range("A" + iStartRow, "E" + iEndRow).Top);


}
private void Show_Table_fullfillmentbyMonthTotals(Microsoft.Office.Interop.Excel.Worksheet ws)
{
try
{
int iCountForTable=iRowNo_for_Executive_Summary_Sheet-1;
ws.Cells[iCountForTable, 6] = "Fulfillment by Month Totals";
ws.get_Range("F" + iCountForTable, "G" + iCountForTable).Cells.MergeCells = true;
ws.get_Range("F" + iCountForTable, "G" + iCountForTable).Font.ColorIndex = 11;
ws.get_Range("F" + iCountForTable, "G" + iCountForTable).Font.Name = "Calibri";
ws.get_Range("F" + iCountForTable, "G" + iCountForTable).Font.Size = 11;
ws.get_Range("F" + iCountForTable, "G" + iCountForTable).Font.Bold = true;
iCountForTable += 1;
ws.Cells[iCountForTable, 6] = "Month";
ws.Cells[iCountForTable, 7] = "Fulfillment";
iCountForTable += 1;
System.Data.DataTable dsDataFulfilment = objExecuteSummaryData.GetFulfillmentbyMonth(iSelectedMonth,iSelectedYear);
int J = iCountForTable;
int k = iCountForTable-1;
foreach (DataRow dr in dsDataFulfilment.Rows)
{
ws.Cells[J, 6] = dr[0];
ws.Cells[J, 7] = dr[4];
//Going to the next row
J = J + 1;
}
//The Following code is used to set the color of DataBar
Microsoft.Office.Interop.Excel.Databar dt;
dt = (Databar)ws.get_Range("G" + (k + 1), "G" + (J - 1)).FormatConditions.AddDatabar();
Microsoft.Office.Interop.Excel.FormatColor fc = (Microsoft.Office.Interop.Excel.FormatColor)dt.BarColor;
fc.Color = ColorTranslator.ToOle(Color.Orange); //RGB(255,0,0);
iCountForTable = J;
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("F" + (k), "G" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table1";
ws.ListObjects["Table1"].TableStyle = "TableStyleMedium9";
ws.ListObjects["Table1"].ShowTotals = true;
iRowNo_for_Executive_Summary_Sheet = iCountForTable;

}
catch (Exception ex)
{
string strErr = ex.Message;
}
}
//Following 1 Function By Vijay Sir
private void ShowForthChart_ClusteredColumn(Microsoft.Office.Interop.Excel.Worksheet ws, Microsoft.Office.Interop.Excel.Worksheet wsSourceData, int ColumnNo, Microsoft.Office.Interop.Excel.ChartObjects chartObjs, int RowNo)
{
string strSecondCol = "";
objExecuteSummaryData = new ExecuteSummaryData();
strSecondCol = GetExcelColumnName(ColumnNo);

Microsoft.Office.Interop.Excel.ChartObject chartObj1 = chartObjs.Add(197, 489 , 97, 12);
Microsoft.Office.Interop.Excel.Chart xlChart1 = chartObj1.Chart;
Microsoft.Office.Interop.Excel.Axes AxCat;
Microsoft.Office.Interop.Excel.Axes AxCatSec;
DataSet dsData1 = objExecuteSummaryData.GetDataForDashboard(iSelectedMonth, iSelectedYear);

xlChart1.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
xlChart1.ChartStyle = 34;

xlChart1.ApplyLayout(9, Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered);
xlChart1.HasTitle = false;
xlChart1.HasLegend = false;
xlChart1.PageSetup.HeaderMargin = 0;
xlChart1.PageSetup.FooterMargin = 0;
xlChart1.PageSetup.LeftMargin = 0;
xlChart1.PageSetup.RightMargin = 0;
xlChart1.PlotArea.InsideTop = 0;
xlChart1.PlotArea.InsideLeft = 0;
xlChart1.PlotArea.Left = -10;
xlChart1.PlotArea.Width = 100;
xlChart1.PlotArea.Height = 14;
xlChart1.get_HasAxis(Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
xlChart1.SetSourceData(wsSourceData.get_Range(strSecondCol + Table_Downloads_By_Therapeutic_Area_By_Months_StartRange + ":" + strSecondCol + Table_Downloads_By_Therapeutic_Area_By_Months_EndRange, strSecondCol + Table_Downloads_By_Therapeutic_Area_By_Months_StartRange + ":" + strSecondCol + Table_Downloads_By_Therapeutic_Area_By_Months_EndRange), 2);
AxCat = (Microsoft.Office.Interop.Excel.Axes)xlChart1.Axes(Type.Missing, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
AxCat.Item(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).HasTitle = false;
AxCat.Item(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).Delete();
AxCatSec = (Microsoft.Office.Interop.Excel.Axes)xlChart1.Axes(Type.Missing, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
AxCat.Item(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).HasMajorGridlines = false;
AxCat.Item(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).HasMinorGridlines = false;
AxCatSec.Item(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).HasTitle = false;
AxCatSec.Item(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).Delete();
///For set the Position of Chart
chartObj1.Height = Convert.ToDouble(ws.get_Range("C" + RowNo, "C" + RowNo).Height)-1;
chartObj1.Width = Convert.ToDouble(ws.get_Range("C" + RowNo, "C" + RowNo).Width);
chartObj1.Left = Convert.ToDouble(ws.get_Range("C" + RowNo, "C" + RowNo).Left);
chartObj1.Top = Convert.ToDouble(ws.get_Range("C" + RowNo, "C" + RowNo).Top);


}
private string GetExcelColumnName(int ColumnNo)
{
string strColumnName = "";
try
{
if (ColumnNo == 1)
strColumnName = "A";
else if (ColumnNo == 2)
strColumnName = "B";
else if (ColumnNo == 3)
strColumnName = "C";
else if (ColumnNo == 4)
strColumnName = "D";
else if (ColumnNo == 5)
strColumnName = "E";
else if (ColumnNo == 6)
strColumnName = "F";
else if (ColumnNo == 7)
strColumnName = "G";
else if (ColumnNo == 8)
strColumnName = "H";
else if (ColumnNo == 9)
strColumnName = "I";
else if (ColumnNo == 10)
strColumnName = "J";
else if (ColumnNo == 11)
strColumnName = "K";
else if (ColumnNo == 12)
strColumnName = "L";
else if (ColumnNo == 13)
strColumnName = "M";
else if (ColumnNo == 14)
strColumnName = "N";
else if (ColumnNo == 15)
strColumnName = "O";
}
catch (Exception ex)
{
String strErr = ex.Message;
}
return strColumnName;
}
private void Show_FourthChart_Downloads_by_Therapeutic_Area_JustGraph(Worksheet ws, Worksheet wsSourceData, ChartObjects chartObjs, int iStartRow,int iEndRow)
{
try
{
Microsoft.Office.Interop.Excel.ChartObject chartObj1 = chartObjs.Add(294, 473, 323, 135);
Microsoft.Office.Interop.Excel.Chart xlChart1 = chartObj1.Chart;
xlChart1.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DPie; //for 3d pie chart.
xlChart1.ChartStyle = 26;
xlChart1.ChartArea.Format.ThreeD.RotationY = 30;
xlChart1.ApplyLayout(6, Microsoft.Office.Interop.Excel.XlChartType.xl3DPie);
xlChart1.SetSourceData(wsSourceData.get_Range(Table_Downloads_By_Therapeutic_Area_StartRange, Table_Downloads_By_Therapeutic_Area_EndRange), 2);
xlChart1.HasTitle = false;
xlChart1.ApplyDataLabels(Microsoft.Office.Interop.Excel.XlDataLabelsType.xlDataLabelsShowPercent, "false", "false", "true", "false", "false", "true", "true", "true", " , ");
///For set the Position of Chart
chartObj1.Height = Convert.ToDouble(ws.get_Range("D" + iStartRow, "F" + iEndRow).Height);
chartObj1.Width = Convert.ToDouble(ws.get_Range("D" + iStartRow, "F" + iEndRow).Width);
chartObj1.Left = Convert.ToDouble(ws.get_Range("D" + iStartRow, "F" + iEndRow).Left);
chartObj1.Top = Convert.ToDouble(ws.get_Range("D" + iStartRow, "F" + iEndRow).Top);
}
catch (Exception ex)
{
string strMs = ex.Message;
}
}
private void Show_Table_Downloads_by_Therapeutic_Area(Worksheet ws, Worksheet wsSourceData, ChartObjects chartObjs)
{
try
{
ws.Cells[iRowNo_for_Executive_Summary_Sheet, 1] = "Therapeutic Area";
ws.Cells[iRowNo_for_Executive_Summary_Sheet, 2] = GetMonthName(iSelectedMonth);
ws.Cells[iRowNo_for_Executive_Summary_Sheet, 3] = "Download Trend";
System.Data.DataTable dtTherapeuticalArea = GetSlideTopicDownloadDetails(iSelectedMonth,iSelectedYear);
iRowNo_for_Executive_Summary_Sheet += 1;
int J = iRowNo_for_Executive_Summary_Sheet;
int k = iRowNo_for_Executive_Summary_Sheet - 1;
int ColumnNo = 2;
int ColumnForFows = 1;
DataRow dr = dtTherapeuticalArea.Rows[iSelectedMonth-1];
foreach (DataColumn dc in dtTherapeuticalArea.Columns)
{
if (dc.ColumnName != "Month")
{
ws.Cells[J, 1] = dc.ColumnName;
ws.Cells[J, 2] = dr[ColumnForFows];
//Going to the next row
/***********************************************************
Create charts-Download Trends
**********************************************************/
ShowForthChart_ClusteredColumn(ws, wsSourceData, ColumnNo, chartObjs, J);
J = J + 1;
ColumnNo += 1;
ColumnForFows += 1;
}

}
///The Following code is used to set the color of DataBar
Microsoft.Office.Interop.Excel.Databar dt;
dt = (Databar)ws.get_Range("B" + (k + 1), "B" + (J - 1)).FormatConditions.AddDatabar();
Microsoft.Office.Interop.Excel.FormatColor fc = (Microsoft.Office.Interop.Excel.FormatColor)dt.BarColor;
fc.Color = ColorTranslator.ToOle(Color.Orange); //RGB(255,0,0);
iRowNo_for_Executive_Summary_Sheet = J;
iRowNo_for_Executive_Summary_Sheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + k, "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Downloads_By_Therapeutic_Area";
ws.ListObjects["Table_Downloads_By_Therapeutic_Area"].TableStyle = "TableStyleMedium9";
ws.ListObjects["Table_Downloads_By_Therapeutic_Area"].ShowTotals = true;
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("C" + k, "C" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Downloads_By_Therapeutic_Area1";
ws.ListObjects["Table_Downloads_By_Therapeutic_Area1"].TableStyle = "TableStyleMedium9";
ws.ListObjects["Table_Downloads_By_Therapeutic_Area1"].ShowTotals = true;
}
catch (Exception ex)
{
string strMs = ex.Message;
}

}
private void Show_SecondChart_Usergroup(Microsoft.Office.Interop.Excel.Worksheet ws, Microsoft.Office.Interop.Excel.Worksheet wsSourceData, Microsoft.Office.Interop.Excel.ChartObjects chartObjs, int iStartRow,int iEndRow)
{
objExecuteSummaryData = new ExecuteSummaryData();
int itop=iRowNo_for_Executive_Summary_Sheet;
Microsoft.Office.Interop.Excel.ChartObject chartObj1 = chartObjs.Add(197, 10, 420,200);
Microsoft.Office.Interop.Excel.Chart xlChart1 = chartObj1.Chart;
DataSet dsData1 = objExecuteSummaryData.GetDataForDashboard(iSelectedMonth, iSelectedYear);
xlChart1.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DPie; //for 3d pie chart.
xlChart1.ChartStyle = 26;
xlChart1.ChartArea.Format.ThreeD.RotationY = 30;
//xlChart1.PlotArea.Width = 320;
xlChart1.ApplyLayout(6, Microsoft.Office.Interop.Excel.XlChartType.xl3DPie);
xlChart1.SetSourceData(wsSourceData.get_Range(Table_Logins_By_Therapeutic_Area_StartRange, Table_Logins_By_Therapeutic_Area_EndRange), 2);
xlChart1.HasTitle = false;
xlChart1.ApplyDataLabels(Microsoft.Office.Interop.Excel.XlDataLabelsType.xlDataLabelsShowPercent, "false", "false", "true", "false", "false", "true", "true", "true", " , ");
///For set the Position of Chart
chartObj1.Height = Convert.ToDouble(ws.get_Range("C"+iStartRow , "F"+iEndRow ).Height);
chartObj1.Width = Convert.ToDouble(ws.get_Range("C" + iStartRow, "F" + iEndRow).Width);
chartObj1.Left = Convert.ToDouble(ws.get_Range("C" + iStartRow, "F" + iEndRow).Left);
chartObj1.Top = Convert.ToDouble(ws.get_Range("C" + iStartRow, "F" + iEndRow).Top);

}
private void Show_FormatedTable_Usergroup(Microsoft.Office.Interop.Excel.Worksheet ws)
{
try
{
ws.Cells[iRowNo_for_Executive_Summary_Sheet, 1] = "User Group";
ws.Cells[iRowNo_for_Executive_Summary_Sheet, 2] = GetMonthName(iSelectedMonth);
System.Data.DataTable dtLoginByUserGroup= (System.Data.DataTable)objExecuteSummaryDataFilter.GetLoginByUserGroupDataFilter(iSelectedMonth,iSelectedYear);
iRowNo_for_Executive_Summary_Sheet += 1;
int J = iRowNo_for_Executive_Summary_Sheet;
int k = iRowNo_for_Executive_Summary_Sheet - 1;
foreach (DataRow dr in dtLoginByUserGroup.Rows)
{
ws.Cells[J, 1] = dr[0];
ws.Cells[J, 2] = dr[1];
J = J + 1;
}
///The Following code is used to set the color of DataBar
Microsoft.Office.Interop.Excel.Databar dt;
dt = (Databar)ws.get_Range("B" + (k + 1), "B" + (J - 1)).FormatConditions.AddDatabar();
Microsoft.Office.Interop.Excel.FormatColor fc = (Microsoft.Office.Interop.Excel.FormatColor)dt.BarColor;
fc.Color = ColorTranslator.ToOle(Color.Orange); //RGB(255,0,0);
iRowNo_for_Executive_Summary_Sheet = J;
iRowNo_for_Executive_Summary_Sheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + k, "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Table_Logins_By_UserGroup";
ws.ListObjects["Table_Logins_By_UserGroup"].TableStyle = "TableStyleMedium9";
ws.ListObjects["Table_Logins_By_UserGroup"].ShowTotals = true;
}
catch (Exception ex)
{
string strMs = ex.Message;
}
}
private void Show_Table_Slide_Topics_Added_and_Updated(Microsoft.Office.Interop.Excel.Worksheet ws)
{
try
{
ws.Cells[iRowNo_for_Executive_Summary_Sheet, 1] = "Month";
ws.Cells[iRowNo_for_Executive_Summary_Sheet, 2] = "Slides Added";
ws.Cells[iRowNo_for_Executive_Summary_Sheet, 3] = "Slides Updated";

System.Data.DataTable dtSlide_Added_Updated =(System.Data.DataTable)objExecuteSummaryDataFilter.GetSlideTopicsAddedAndUpdatedFilterData(iSelectedMonth,iSelectedYear);
iRowNo_for_Executive_Summary_Sheet += 1;
int J = iRowNo_for_Executive_Summary_Sheet;
int k = iRowNo_for_Executive_Summary_Sheet - 1;

foreach (DataRow dr in dtSlide_Added_Updated.Rows)
{
ws.Cells[J, 1] = dr[0];
ws.Cells[J, 2] = dr[1];
ws.Cells[J, 3] = dr[2];
J = J + 1;
}
///The Following code is used to set the color of DataBar
Microsoft.Office.Interop.Excel.Databar dt;
dt = (Databar)ws.get_Range("B" + (k + 1), "C" + (J - 1)).FormatConditions.AddDatabar();
Microsoft.Office.Interop.Excel.FormatColor fc = (Microsoft.Office.Interop.Excel.FormatColor)dt.BarColor;
fc.Color = ColorTranslator.ToOle(Color.Orange); //RGB(255,0,0);
iRowNo_for_Executive_Summary_Sheet = J;
iRowNo_for_Executive_Summary_Sheet += 1;
//For Applying TableStyle
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("A" + k, "B" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Show_Table_Slide_Topics_Added_and_Updated1";
ws.ListObjects["Show_Table_Slide_Topics_Added_and_Updated1"].TableStyle = "TableStyleMedium9";
ws.ListObjects["Show_Table_Slide_Topics_Added_and_Updated1"].ShowTotals = true;
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, ws.get_Range("C" + k, "C" + (J - 1)), Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "Show_Table_Slide_Topics_Added_and_Updated2";
ws.ListObjects["Show_Table_Slide_Topics_Added_and_Updated2"].TableStyle = "TableStyleMedium9";
ws.ListObjects["Show_Table_Slide_Topics_Added_and_Updated2"].ShowTotals = true;

}
catch (Exception ex)
{
string strMs = ex.Message;
}

}
#endregion
#region ##### Code From Jaimin For LinkButtons #####
public void BindUserGroups()
{
ExecuteSummaryData objExecuteSummaryData = new ExecuteSummaryData();
try
{
DataSet ds = new DataSet();
DataView dv = new DataView();
ds = (DataSet)objExecuteSummaryData.GetSlideTopicAdditionsByTherapeuticArea();
dv = ds.Tables[0].DefaultView;
dv.Sort = "title";
reptUserGroups.DataSource = dv;
reptUserGroups.DataBind();
}
catch (System.Exception ex)
{
string strMessage = ex.Message;
}
}
public string GetPostBackURL(string ID)
{
string strPostBackURL = "";
string strMonth = "";
string strYear = "";
try
{
strMonth = dpdMonth.SelectedValue.ToString();
strYear = dpdYear.SelectedValue.ToString();
if (strMonth != "" && strYear != "")
{
strPostBackURL = "../GenerateExcel_By_UserGroup.aspx";
strPostBackURL += "?UID=" + ID + "&SM=" + strMonth + "&SY=" + strYear;
}
}
catch (System.Exception ex)
{
string strMessage = ex.Message;
}
return strPostBackURL;
}
public void GetSelectedValue()
{
string[] strcurntDate;
try
{
strcurntDate = DateTime.Now.ToShortDateString().Split('/');
dpdMonth.SelectedValue = strcurntDate[0].ToString();
dpdYear.SelectedValue = strcurntDate[2].ToString();
}
catch (System.Exception ex)
{
string strMessage = ex.Message;
}
}
#endregion
protected void lnkbtnDashboard_Click(object sender, EventArgs e)
{
iSelectedMonth = Convert.ToInt32(dpdMonth.SelectedValue.ToString());
iSelectedYear = Convert.ToInt32(dpdYear.SelectedValue.ToString());
string strFilename = Server.MapPath("..\\TempDownload\\SAMS_"+GetMonthName(iSelectedMonth)+"_"+iSelectedYear+"_Report.xlsx");
Session["FileToopen"] = "SAMS_" + GetMonthName(iSelectedMonth) + "_" + iSelectedYear + "_Report.xlsx";
string[] files = Directory.GetFiles(Server.MapPath("..\\TempDownload\\"));
foreach (string file in files)
{
File.Delete(file);
}
excelapp = new Application();
excelapp.Visible = false;
wb =excelapp.Workbooks.Add(Missing.Value);
objExecuteSummaryDataFilter = new ExecuteSummaryDataFilter();
//wb.Application.Visible = false;
Show_Source_Data_Sheet();
wb.SaveAs(strFilename, Type.Missing,Type.Missing, Type.Missing, Type.Missing,Type.Missing,XlSaveAsAccessMode.xlExclusive,Type.Missing,Type.Missing,Type.Missing,Type.Missing, Missing.Value);
wb.Close(true, Type.Missing, Type.Missing);
excelapp.Quit();
Response.Redirect("Popup.aspx");
}
private string FormatFileName(string strFName)
{
strFName = strFName.Replace("\\", "");
strFName = strFName.Replace("/", "");
strFName = strFName.Replace("'", "");
strFName = strFName.Replace("`", "");
strFName = strFName.Replace("_", "");
strFName = strFName.Replace(" ", "");
strFName = strFName.Replace(".", "");
return strFName;
}
protected void lnkbtnSectionTitle_Click(object sender, CommandEventArgs e)
{
iSelectedMonth = Convert.ToInt32(dpdMonth.SelectedValue.ToString());
iSelectedYear = Convert.ToInt32(dpdYear.SelectedValue.ToString());
int iSectionID = Convert.ToInt32(e.CommandArgument.ToString());
DataSet ds = new DataSet();
objExecuteSummaryData = new ExecuteSummaryData();
ds = (DataSet)objExecuteSummaryData.GetSlideTopicAdditionsByID(iSectionID);
Session["FileToopen"] = FormatFileName(ds.Tables[0].Rows[0][1].ToString()) + "_" + GetMonthName(iSelectedMonth) + "_" + iSelectedYear + "_ Report.xlsx";
string strFilename = Server.MapPath("..\\TempDownload\\" + Session["FileToopen"].ToString());// FormatFileName(ds.Tables[0].Rows[0][1].ToString()) + "_" + GetMonthName(iSelectedMonth) + "_" + iSelectedYear + "_ Report.xlsx");
string[] files = Directory.GetFiles(Server.MapPath("..\\TempDownload\\"));
foreach (string file in files)
{
File.Delete(file);
}
GenerateExcelForSectionTitle objGES = new GenerateExcelForSectionTitle();
excelapp = new Application();
excelapp.Visible = false;
wb = excelapp.Workbooks.Add(Missing.Value);
objGES.GetSectionID(iSectionID, iSelectedMonth, iSelectedYear,wb);
wb.SaveAs(strFilename, Type.Missing, Type.Missing, Type.Missing, false , false , XlSaveAsAccessMode.xlExclusive , false , false , Type.Missing, Type.Missing, Missing.Value);
wb.Close(true, Type.Missing, Type.Missing);
excelapp.Quit();
Response.Redirect("Popup.aspx");
//Response.Redirect("../TempDownload/" + Session["FileToopen"].ToString());
}
private string GetReportDate()
{
string strReportDate = "";
try
{
strReportDate = GetMonthName(iSelectedMonth);
strReportDate = strReportDate + " " + "1 - " + DateTime.DaysInMonth(iSelectedYear, iSelectedMonth).ToString();
strReportDate = strReportDate + ", " + iSelectedYear.ToString();
}
catch (Exception ex)
{
string strError = ex.Message;
}
return strReportDate;
}
#region KillExcelProcess()
public static void KillWordProcess()
{
Process[] aryP1;
aryP1 = Process.GetProcessesByName("EXCEL.EXE");
if (aryP1.Length != 0)
{
string strProcess = aryP1[0].Id.ToString();
aryP1[0].Kill();
}
aryP1 = Process.GetProcessesByName("EXCEL");
if (aryP1.Length != 0)
{
aryP1[0].Kill();
}
}
#endregion
}