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
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.
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