private ExcelApplication xlw;
        private Workbook wb;
        private Worksheet ws, wsChart;
        private Area importedArea;
        private bool ChartsYtd;
        private string chartFontName = "Garamond";
        private Color clrChartArea, clrLegend;

        /// <summary>
        /// Build the report with ExcelApplication
        /// </summary>
        public void GenerateReport(bool ytd)
        {
            //If ChartsYtd the pie chart will show year to date data
            ChartsYtd = ytd;

            // Create an instance of ExcelApplication
            // and open the template file.
            
            xlw = new ExcelApplication();

            // Open the template workbook 
            string templatePath = @"..\..\Templates\SalesCommission.xlsx";
            wb = xlw.Open(templatePath);

            ws = wb.Worksheets[0];

            // Setup the colors that will be used in the workbook 
            this.SetupColors();

            // Import the data into the worksheet 
            this.PopulateWorksheet();

            // Create a new worksheet to hold the charts.
            // Set the gridline visibilty and tab color.
            
            wsChart = wb.Worksheets.CreateWorksheet("Charts");
            wsChart.ShowGridlines = false;
            wsChart.TabColor = clrLegend;

            // Add the charts 
            this.AddPieChart();
            this.AddColumnChart();

            // Save the report by streaming
             // it to the client's browser 
            string reportName;
            if (this.ChartsYtd)
            {
                reportName = "SalesCommissionReport-YTD.xlsx";
            }
            else
            {
                reportName = "SalesCommissionReport-LastYear.xlsx";
            }
            xlw.Save(wb, @"..\..\Output\"+reportName);
        }

        /// <summary> Initialize the colors to be used in
        /// the workbook.  These colors will be applied
        /// to the chart areas and chart legends.
        /// </summary>
        private void SetupColors()
        {
            // Get the colors from the Workbook Palette object 
            Palette pal = wb.Palette;
            clrChartArea = pal.GetClosestColor(240, 243, 248);
            clrLegend = pal.GetClosestColor(234, 234, 234);
        }

        /// <summary> Add the column chart to the worksheet.</summary>
        private void AddColumnChart()
        {
            // This chart will sit near cell A25 
            Anchor anch = wsChart.CreateAnchor(24, 0, 50, 50);

            // Create the chart 
            Chart colChart = wsChart.Charts.CreateChart(ChartType.Column.Clustered3D, anch);

            // Set title, title font, and shadow settings 
            colChart.ChartArea.HasShadow = true;
            colChart.Title.Text = "Commission Payout Comparison";
            colChart.Title.Font.Name = chartFontName;
            colChart.Title.Font.Size = 22;
            colChart.Legend.Font.Name = chartFontName;

            // Set the chart area and legend colors.
            // These colors were initialized in the setupColors() method.
            
            colChart.ChartArea.Interior.ForegroundColor = clrChartArea;
            colChart.Legend.Interior.ForegroundColor = clrLegend;

            // Create areas for the chart's category data, and for the 
            // YTD and last year series objects.
            
            Area categoryArea = ws.CreateArea(6, 1, importedArea.RowCount, 1);
            Area seriesAreaLast = ws.CreateArea(6, 5, importedArea.RowCount, 1);
            Area seriesAreaYTD = ws.CreateArea(6, 4, importedArea.RowCount, 1);

            // Set category data. This will be the employee names on the X axis. 
            colChart.SeriesCollection.CategoryData = categoryArea.Dimensions;

            // Create two series objects.  Last Year and YTD will be displayed
            // as chart columns side-by-side.
            
            Series srsLast = colChart.SeriesCollection.CreateSeries(seriesAreaLast);
            srsLast.Name = "Last Year";
            Series srsYTD = colChart.SeriesCollection.CreateSeries(seriesAreaYTD);
            srsYTD.Name = "YTD";
        }

        /// <summary> Add a pie chart to the chart worksheet.</summary>
        private void AddPieChart()
        {
            // This chart will sit near cell A1 
            Anchor anch = wsChart.CreateAnchor(0, 0, 50, 50);

            // Create a Pie3D chart 
            Chart pieChart = wsChart.Charts.CreateChart(ChartType.Pie.Pie3D, anch);

            pieChart.ChartArea.HasShadow = true;

            // Determine whether to show YTD or Last Year data
            // based on the value passed in by the user.
            
            int commCol;
            if (ChartsYtd)
            {
                // YTD data is in the 5th column. 
                commCol = 4;
                pieChart.Title.Text = "Commission Distribution - YTD";
                wsChart.Name = "Charts - YTD";
            }
            else
            {
                // Last Year data is in the 6th column. 
                commCol = 5;
                pieChart.Title.Text = "Commission Distribution - Last Year";
                wsChart.Name = "Charts - Last Year";
            }

            // Set Chart title font appearance 
            pieChart.Title.Font.Name = chartFontName;
            pieChart.Title.Font.Size = 22;
            pieChart.Legend.Font.Name = chartFontName;

            // Set chart area and legend shading.
            // These colors were initialized in the setupColors() method.
            
            pieChart.ChartArea.Interior.ForegroundColor = clrChartArea;
            pieChart.Legend.Interior.ForegroundColor = clrLegend;

            // Create Area objects for category and series data 
            Area categoryArea = ws.CreateArea(6, 1, importedArea.RowCount, 1);
            Area seriesArea = ws.CreateArea(6, commCol, importedArea.RowCount, 1);

            // Set the category and series data 
            pieChart.SeriesCollection.CategoryData = categoryArea.Dimensions;
            Series commSeries = pieChart.SeriesCollection.CreateSeries(seriesArea);

            // Show chart leader lines and values as percentages. 
            commSeries.SettingsPieDoughnut.ShowLeaderLines = true;
            commSeries.DataLabels.ContainsValueAsPercentage = true;
            commSeries.DataLabels.Font.Name = chartFontName;
            commSeries.Line.Visible = true;
        }

        /// <summary> Import the data from a 2-D Object array
        /// into the worksheet.
        /// </summary>
        private void PopulateWorksheet()
        {
            DataImportProperties dp = wb.CreateDataImportProperties();
            dp.Truncate = true;

            // The workbook has a named range "DataRange"
            // defined that marks where the data should be imported.
            // Get a reference to this range.
            
            Range dataRange = wb.GetNamedRange("DataRange");
            Area dataArea = dataRange.Areas[0];

            // These arrays are the data to import 
            object[,] data = this.GetData();
            string[] fieldNames = { "Name", "SalesYTD", "SalesLastYear" };

            // Import the data into the data area. 
            importedArea = dataArea.ImportData(data, fieldNames, dp);

            // Remove extra rows from the worksheet 
            int lastDataAreaRow = dataArea.FirstRow + dataArea.RowCount - 1;
            int lastImportedAreaRow = importedArea.FirstRow + importedArea.RowCount - 1;
            while (lastDataAreaRow > lastImportedAreaRow)
            {
                ws.DeleteRow(lastDataAreaRow);
                --lastDataAreaRow;
            }

        }

        /// <summary> ExcelWriter can import data from arrays as well as DataTables.
        /// The data returned by this method will be imported into the worksheet.
        /// </summary>
        /// <returns> 2-D Object array of data
        /// </returns>
        private object[,] GetData()
        {
            object[,] data = {{"Ansman-Wolfe, Pamela", 2488342.5141, 1927059.1780}, 
                            {"Blythe, Michael", 2590055.1774, 1750406.4785}, 
                            {"Campbell, David", 1870183.5288, 1371635.3158}, 
                            {"Caro, Fernando", 1958815.8056, 1997186.2037}, 
                            {"Ito, Shu", 1679586.6629, 2073505.9999}, 
                            {"Mitchell, Linda", 2343461.0492, 1439156.0291}, 
                            {"Pak, Jae", 2568244.0549, 1635823.3967}, 
                            {"Reiter, Tsvi", 1855106.2631, 1849640.9418}, 
                            {"Saraiva, Jos?", 2153295.1978, 2038234.6549},
                            {"Tsoflias, Lynn", 2486869.8048, 2278548.9776}, 
                            {"Valdez, Rachel", 2160347.3087, 1307949.7917}, 
                            {"Vargar, Garrett", 2088272.9112, 1620276.8966}, 
                            {"Varkey Chudukatil, Ranjit", 2177055.6488, 2396539.7601}};
            return data;
        }