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 Chart Sample

Two Axis Chart Sample

Intro

Create a chart with E= xcelApplication that has two axes: primary axis and secondary axis.

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.

Code

=20
        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

Downloads

------=_Part_9320_1719655632.1711687913814--