CODEWITHSUNDEEP
c#excel
ImageMediaImpact
ImageMediaImpact

Reputation: 31

set x axis units in Excel Chart from Microsoft C#

The following C# code generates a chart with the x-axis month and year automatically set as shown in the first image. Once in Excel, manually changing the following settings:

  1. Chart "Format Axis" -> "Axis Options" -> "Units" -> "Major" values to "1" and "Year"

the second image is generated.

  1. Number Category -> "Date" , "Type" -> "Mar-12"

the third image is generated.

How can these settings be set using C#?

            var chartRange = oSheet.Range[oSheet.Cells[3, 2], oSheet.Cells[3 + rno - 4, 6]];
            var chartContainer = charts.Add(left, top, width, height);
            var lineChart = chartContainer.Chart;

            lineChart.SetSourceData(chartRange);
            lineChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop;

            // barChart.Legend.Clear();    // removing legend

            Excel.Series series = lineChart.SeriesCollection(1) as Excel.Series;
            series.Interior.Color = Color.FromArgb(34, 92, 107);
            series.Border.Weight = 1;
            series.Border.Color = Color.FromKnownColor(KnownColor.Black);

            Excel.Axis axis1 = lineChart.Axes(1);
            axis1.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionNextToAxis;
            Excel.TickLabels tickLabels3 = axis1.TickLabels as Excel.TickLabels;
            tickLabels3.NumberFormat = "mm/yy";


            Excel.Axis axis2 = lineChart.Axes(2);
            Excel.TickLabels tickLabels2 = axis2.TickLabels as Excel.TickLabels;
            //tickLabels2.NumberFormat = "0.0%";


            lineChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;
            lineChart.ChartWizard(Source: chartRange, Title: chartTitle, CategoryTitle: categoryTitle, ValueTitle: valueTitle);
            lineChart.ChartColor = 8;

            axis1.AxisTitle.Orientation = Excel.XlOrientation.xlHorizontal;
            axis1.AxisTitle.Font.Size = 8;
            axis2.AxisTitle.Font.Size = 12;

Chart with Excel-generated scaled month/year x axis

Chart with x-axis as desired

Chart as desired

Upvotes: 1

Views: 976

Answers (1)

ImageMediaImpact
ImageMediaImpact

Reputation: 31

I worked out how to do it:

  1. Color theme is set by adding "ChartColor" attribute: the color is the number of the position in the "Color" control in excel:

             lineChart.SetSourceData(chartRange);
         lineChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop;
         lineChart.ChartColor = 8;

  2. The number format is the "tickLabel3.NumberFormat", the units display is "axis1.BaseUnit", "axis1.MajorUnitScale" and "axis1.MajorUnit" attributes

             Excel.Axis axis1 = lineChart.Axes(1);
         axis1.TickLabelPosition =   Excel.XlTickLabelPosition.xlTickLabelPositionNextToAxis;
         Excel.TickLabels tickLabels3 = axis1.TickLabels as Excel.TickLabels;
         tickLabels3.NumberFormat = "[$-en-US]mmm-yy;@";
         axis1.BaseUnit = Excel.XlTimeUnit.xlMonths;
         axis1.MajorUnitScale = Excel.XlTimeUnit.xlYears;
         axis1.MajorUnit = 1;

Upvotes: 1

Related Questions