Message-ID: <1417285698.9321.1711687913814.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_9320_1719655632.1711687913814" ------=_Part_9320_1719655632.1711687913814 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
Two-axis charts can add a lot to the visual presentation of your report.= This demo shows revenue and cost data as columns, so they can easily= be compared to each other for a specific time period. The profit, ca= lculated by an Excel formula on the revenue and cost figures, is displayed = as a line so that the trend is clear.
public class TwoAxisChart { private ExcelApplication xlw; private Workbook wb; private Worksheet wsData; private Worksheet wsBalance; private Chartsheet wsTwoAxisChart; private Chartsheet wsPieChart; private bool ProfitLine =3D true; /// <summary> /// Build the report with ExcelApplication /// </summary> public void GenerateReport() { //Create an instance of ExcelAppl= ication and // open the template workbook. xlw =3D new ExcelApplication(); //Open the template workbook = ; string templatePath =3D @"..= \..\ExcelTemplateFiles\twoAxisTemplate.xlsx"; wb =3D xlw.Open(templatePath); //Get references to two existing = worksheets // in the workbook. wsData =3D wb.Worksheets["Fi= nData"]; wsBalance =3D wb.Worksheets["= ;BalanceSheet"]; //Create the Two Axis chart this.CreateTwoAxisChart(ProfitLin= e); //Create the pie chart this.CreatePieChart= (); wb.Worksheets.MoveS= heet(wsPieChart, 0); //Move the sheets in the workbook= wb.Worksheets.MoveSheet(wsTwoAxis= Chart, 0); //Save the report xlw.Save(wb, @"..\..\ExcelOu= tputFiles\TwoAxisChart_output.xlsx"); } /// <summary> Add a pie chart to the wsPi= eChart Chartsheet.</summary> private void CreatePieChart() { //Create a chart sheet for the pi= e chart. // Name the sheet "PieChartS= heet" and insert it // as the first worksheet in the = workbook. wsPieChart =3D wb.Worksheets.Creat= eChartsheet(ChartType.Pie.StandardPie, "PieChartSheet", 0); Chart chrtPie =3D wsPieChart.Char= t; //Set category data string categoryFormula =3D "= BalanceSheet!A8:A13,BalanceSheet!A17:A18"; chrtPie.SeriesCollection.Category= Data =3D categoryFormula; //Create a series object string seriesFormula =3D "Ba= lanceSheet!B8:B13,BalanceSheet!B17:B18"; Series srsAssets =3D chrtPie.Seri= esCollection.CreateSeries(seriesFormula); srsAssets.Name =3D "Assets&q= uot;; //Show data labels on the pie cha= rt as percentages srsAssets.DataLabels.ContainsValu= eAsPercentage =3D true; } /// <summary> Create a two-axis column/li= ne chart on a new chartsheet.</summary> /// <param name=3D"bProfitLine">= ;If true, add the second axis to the chart.</param> private void CreateTwoAxisChart(bool ProfitLine= ) { //Create a chartsheet with a clus= tered column chart. // Name it "ChartSheet"= and make it the first worksheet // in the workbook. wsTwoAxisChart =3D wb.Worksheets.= CreateChartsheet(ChartType.Column.Clustered, "ChartSheet", 0); Chart chrtTwoAxis =3D wsTwoAxisCh= art.Chart; //Set category data and assign it= to the primary axis chrtTwoAxis.SeriesCollection.Cate= goryData =3D "FinData!C3:F3"; //Create two series objects in th= e chart. // One for revenue and one for co= st. Series srsRevenue =3D chrtTwoAxis= .SeriesCollection.CreateSeries("FinData!C4:F4"); srsRevenue.NameFormula =3D "= FinData!A4"; Series srsCost =3D chrtTwoAxis.Se= riesCollection.CreateSeries("FinData!C5:F5"); srsCost.NameFormula =3D "Fin= Data!A5"; //Create some colors to stylize t= he chart. Color clrRed =3D wb.Palette.GetCl= osestColor(255,48,58); Color clrBlue =3D wb.Palette.GetC= losestColor(89,69,255); Color clrLighterBlue =3D wb.Palet= te.GetClosestColor(51, 155, 232); //Set the color of the bars of th= e revenue and cost series objects. srsRevenue.Interior.ForegroundCol= or =3D clrRed; srsCost.Interior.ForegroundColor = =3D clrBlue; //Add Profit Line // Add a third series to di= splay profit. Series srsProfit =3D chrtTwoAxis.= SeriesCollection.CreateSeries("FinData!C6:F6"); //Display as a standard line, and= set it to // the secondary axis. srsProfit.ChartType =3D ChartType= .Line.StandardLine; srsProfit.AxisType =3D AxisType.S= econdary; srsProfit.NameFormula =3D "F= inData!A6"; //Change the appearance of the li= ne chart's // data point markers. Set = the type and color. srsProfit.DataPointMarker.MarkerT= ype =3D DataPointMarker.ShapeType.X; srsProfit.DataPointMarker.Backgro= undColor =3D clrLighterBlue; srsProfit.DataPointMarker.Foregro= undColor =3D clrLighterBlue; //Change the color and weight of = the line itself. srsProfit.Line.Visible =3D true; srsProfit.Line.Color =3D clrLight= erBlue; srsProfit.Line.Weight =3D ChartLi= ne.LineWeight.Medium; //Set the title of the chart and = set the title font ChartText chrtTitle =3D chrtTwoAx= is.Title; chrtTitle.Formula =3D "FinDa= ta!A1"; chrtTitle.Font.Name =3D "Tre= buchet"; chrtTitle.Font.Size =3D 14; chrtTitle.Font.Bold =3D true; } }=20