Message-ID: <1918746886.10051.1711710815414.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_10050_1404341288.1711710815414" ------=_Part_10050_1404341288.1711710815414 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Sales Commission Report

Sales Commission Report

Intro

An example of a re= port created with ExcelWriter that compares previous year and year-to-date = sales data for a sales team.

The source Workbook contains a listing of Employees for the fictional co= mpany AdventureWorks. The actual data is marked with two Named Ranges,= "EmployeesHeadings" and "EmployeesData", which will ma= ke it easier to import using ExcelWriter. This data is then used to po= pulate a DataGrid, which will be displayed below.

Code

=20
  public class Commission
    {
        private ExcelApplication xlw;
        private Workbook wb;
        private Worksheet ws, wsChart;
        private Area importedArea;
        private bool ChartsYtd;
        private string chartFontName =3D "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 =3D ytd;

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

            // Open the template workbook&nbs=
p;
            string templatePath =3D @"..=
\..\ExcelTemplateFiles\SalesCommission.xlsx";
            wb =3D xlw.Open(templatePath);

            ws =3D wb.Worksheets[0];

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

            // Import the data into the works=
heet 
            this.PopulateWorksheet();

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

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

            // Save the report by streaming
             //it to the client's browse=
r 
            string reportName;
            if (this.ChartsYtd)
            {
                reportName =3D &quo=
t;SalesCommissionReport-YTD.xlsx";
            }
            else
            {
                reportName =3D &quo=
t;SalesCommissionReport-LastYear.xlsx";
            }
            xlw.Save(wb, @"..\..\ExcelOu=
tputFiles\"+reportName);
        }

        /// <summary> Initialize the colors to be=
 used in
        /// the workbook.  These colors will be ap=
plied
        /// to the chart areas and chart legends.
        /// </summary>
        private void SetupColors()
        {
            // Get the colors from the Workbo=
ok Palette object 
            Palette pal =3D wb.Palette;
            clrChartArea =3D pal.GetClosestCo=
lor(240, 243, 248);
            clrLegend =3D 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 =3D wsChart.CreateAnc=
hor(24, 0, 50, 50);

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

            // Set title, title font, and sha=
dow settings 
            colChart.ChartArea.HasShadow =3D =
true;
            colChart.Title.Text =3D "Com=
mission Payout Comparison";
            colChart.Title.Font.Name =3D char=
tFontName;
            colChart.Title.Font.Size =3D 22;
            colChart.Legend.Font.Name =3D cha=
rtFontName;

            // Set the chart area and legend =
colors.
            //These colors were initialized i=
n the setupColors() method.
            
            colChart.ChartArea.Interior.Foreg=
roundColor =3D clrChartArea;
            colChart.Legend.Interior.Foregrou=
ndColor =3D clrLegend;

            // Create areas for the chart's c=
ategory data, and for the 
            //YTD and last year series object=
s.
            
            Area categoryArea =3D ws.CreateAr=
ea(6, 1, importedArea.RowCount, 1);
            Area seriesAreaLast =3D ws.Create=
Area(6, 5, importedArea.RowCount, 1);
            Area seriesAreaYTD =3D ws.CreateA=
rea(6, 4, importedArea.RowCount, 1);

            // Set category data. This will b=
e the employee names on the X axis. 
            colChart.SeriesCollection.Categor=
yData =3D categoryArea.Dimensions;

            // Create two series objects. &nb=
sp;Last Year and YTD will be displayed
            //as chart columns side-by-side.
            
            Series srsLast =3D colChart.Serie=
sCollection.CreateSeries(seriesAreaLast);
            srsLast.Name =3D "Last Year&=
quot;;
            Series srsYTD =3D colChart.Series=
Collection.CreateSeries(seriesAreaYTD);
            srsYTD.Name =3D "YTD";
        }

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

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

            pieChart.ChartArea.HasShadow =3D =
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 t=
he 5th column. 
                commCol =3D 4;
                pieChart.Title.Text=
 =3D "Commission Distribution - YTD";
                wsChart.Name =3D &q=
uot;Charts - YTD";
            }
            else
            {
                // Last Year data i=
s in the 6th column. 
                commCol =3D 5;
                pieChart.Title.Text=
 =3D "Commission Distribution - Last Year";
                wsChart.Name =3D &q=
uot;Charts - Last Year";
            }

            // Set Chart title font appearanc=
e 
            pieChart.Title.Font.Name =3D char=
tFontName;
            pieChart.Title.Font.Size =3D 22;
            pieChart.Legend.Font.Name =3D cha=
rtFontName;

            // Set chart area and legend shad=
ing.
            //These colors were initialized i=
n the setupColors() method.
            
            pieChart.ChartArea.Interior.Foreg=
roundColor =3D clrChartArea;
            pieChart.Legend.Interior.Foregrou=
ndColor =3D clrLegend;

            // Create Area objects for catego=
ry and series data 
            Area categoryArea =3D ws.CreateAr=
ea(6, 1, importedArea.RowCount, 1);
            Area seriesArea =3D ws.CreateArea=
(6, commCol, importedArea.RowCount, 1);

            // Set the category and series da=
ta 
            pieChart.SeriesCollection.Categor=
yData =3D categoryArea.Dimensions;
            Series commSeries =3D pieChart.Se=
riesCollection.CreateSeries(seriesArea);

            // Show chart leader lines and va=
lues as percentages. 
            commSeries.SettingsPieDoughnut.Sh=
owLeaderLines =3D true;
            commSeries.DataLabels.ContainsVal=
ueAsPercentage =3D true;
            commSeries.DataLabels.Font.Name =
=3D chartFontName;
            commSeries.Line.Visible =3D true;
        }

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

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

            // These arrays are the data to i=
mport 
            object[,] data =3D this.GetData()=
;
            string[] fieldNames =3D { "N=
ame", "SalesYTD", "SalesLastYear" };

            // Import the data into the data =
area. 
            importedArea =3D dataArea.ImportD=
ata(data, fieldNames, dp);

            // Remove extra rows from the wor=
ksheet 
            int lastDataAreaRow =3D dataArea.=
FirstRow + dataArea.RowCount - 1;
            int lastImportedAreaRow =3D impor=
tedArea.FirstRow + importedArea.RowCount - 1;
            while (lastDataAreaRow > lastI=
mportedAreaRow)
            {
                ws.DeleteRow(lastDa=
taAreaRow);
                --lastDataAreaRow;
            }

        }

        /// <summary> ExcelWriter can import data=
 from arrays as well as DataTables.
        /// The data returned by this method will be im=
ported into the worksheet.
        /// </summary>
        /// <returns> 2-D Object array of data
        /// </returns>
        private object[,] GetData()
        {
            object[,] data =3D {{"Ansman=
-Wolfe, Pamela", 2488342.5141, 1927059.1780}, 
                     =
;       {"Blythe, Michael", 2590055.1774, 1750406.=
4785}, 
                     =
;       {"Campbell, David", 1870183.5288, 1371635.=
3158}, 
                     =
;       {"Caro, Fernando", 1958815.8056, 1997186.2=
037}, 
                     =
;       {"Ito, Shu", 1679586.6629, 2073505.9999},&=
nbsp;
                     =
;       {"Mitchell, Linda", 2343461.0492, 1439156.=
0291}, 
                     =
;       {"Pak, Jae", 2568244.0549, 1635823.3967},&=
nbsp;
                     =
;       {"Reiter, Tsvi", 1855106.2631, 1849640.941=
8}, 
                     =
;       {"Saraiva, Jos?", 2153295.1978, 2038234.65=
49},
                     =
;       {"Tsoflias, Lynn", 2486869.8048, 2278548.9=
776}, 
                     =
;       {"Valdez, Rachel", 2160347.3087, 1307949.7=
917}, 
                     =
;       {"Vargar, Garrett", 2088272.9112, 1620276.=
8966}, 
                     =
;       {"Varkey Chudukatil, Ranjit", 2177055.6488=
, 2396539.7601}};
            return data;
        }
    }
=20

Downloads

------=_Part_10050_1404341288.1711710815414--