Excel Charting - Align Secondary Axis with C# The code for this is linked below, and the general algorithm used for aligning a chart's secondary axis with the primary axis: Get primary divisors Get upper and lower bounds of secondary Get larger ABS(max) or ABS(min) Multiply (max/min) divisor by numbers (1,2,5,10,20,25,30) to find the first multi…

SecondaryAxisAlignment.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IExcel = Microsoft.Office.Interop.Excel;

namespace AccountHoldingsAddIn.Helpers
{
    public class Charting
    {
        double primaryMajor;
        double primaryMax;
        double primaryMin;
        double primaryDivs;

        double positiveDivs;
        double negativeDivs;

        double greaterDiv;
        double greaterSecondary;
        double divMultiplier = 0;

        double secondaryMajor;
        double secondaryMax;
        double secondaryMin;
        double secondaryDivs;

        double[] multipliers = new double[] {.01,.02,.025,.03,.05,.1,.2,.25,.3,.5,1,2,3,5,10,15,20,25,30,50,100,200,500,1000};

        public Charting()
        {
            //null conastructor
        }

        private IExcel.ChartObject GetChartObject(string name, IExcel.Worksheet wks)
        {
            IExcel.ChartObject cht = null;
            try
            {
                cht = wks.ChartObjects(name);
            }
            catch
            {
                //empty catch returns null if item does not exist
            }
            return cht;
        }

        /// <summary>
        /// From secondary access access, get major units, max and minimum
        ///  divide range by division count to get secondary major unit
        /// </summary>
        /// <param name="sourceAxis"></param>
        /// <param name="targetAxis"></param>
        private void Align(IExcel.Axis sourceAxis, IExcel.Axis targetAxis)
        {
            try
            {

                primaryMajor = sourceAxis.MajorUnit;
                primaryMin = sourceAxis.MinimumScale;
                primaryMax = sourceAxis.MaximumScale;

                secondaryMajor = targetAxis.MajorUnit;
                secondaryMin = targetAxis.MinimumScale;
                secondaryMax = targetAxis.MaximumScale;

                primaryDivs = Math.Abs((primaryMax / primaryMajor) + (primaryMin / primaryMajor));
                secondaryDivs = Math.Abs((secondaryMax / secondaryMajor) + (secondaryMin / secondaryMajor));

                if (primaryDivs > secondaryDivs)
                {
                    if (AlignDirectional(targetAxis, sourceAxis))
                    {
                        AlignDirectional(sourceAxis, targetAxis);
                    }
                }
                else 
                {
                    if(AlignDirectional(sourceAxis, targetAxis))
                    {
                        AlignDirectional(targetAxis, sourceAxis);
                    }
                }
            }
            catch
            {

            }
        }

        /// <summary>
        /// From secondary access access, get major units, max and minimum
        ///  divide range by division count to get secondary major unit
        /// </summary>
        /// <param name="sourceAxis"></param>
        /// <param name="targetAxis"></param>
        private Boolean AlignDirectional(IExcel.Axis sourceAxis, IExcel.Axis targetAxis)
        {
            Boolean realign = false;

            try{

                primaryMajor = sourceAxis.MajorUnit;
                primaryMin = sourceAxis.MinimumScale;
                primaryMax = sourceAxis.MaximumScale;

                secondaryMajor = targetAxis.MajorUnit;
                secondaryMin = targetAxis.MinimumScale;
                secondaryMax = targetAxis.MaximumScale;

                if (primaryMax == 0 && secondaryMax != 0)
                {
                    positiveDivs = Math.Abs(secondaryMax / secondaryMajor);
                    realign = true;
                }
                else
                {
                    positiveDivs = Math.Abs(primaryMax / primaryMajor);
                }

                if (primaryMin == 0 && secondaryMin != 0)
                {
                    negativeDivs = Math.Abs(secondaryMin / secondaryMajor);
                    realign = true;
                }
                else
                {
                    negativeDivs = Math.Abs(primaryMin / primaryMajor);
                }
                
                if (positiveDivs > negativeDivs) {
                    greaterDiv = positiveDivs;
                }
                else {
                    greaterDiv = negativeDivs;
                }

                if (Math.Abs(secondaryMax) > Math.Abs(secondaryMin)) {
                    greaterSecondary = secondaryMax;
                }
                else {
                    greaterSecondary = secondaryMin;
                }

                double absSecondaryAxis = Math.Abs(greaterSecondary);

                foreach (double dblMultiplier in multipliers) {
                    if ((greaterDiv * dblMultiplier) >= absSecondaryAxis)
                    {
                        divMultiplier = dblMultiplier;
                        break;
                    }
                }

                if (divMultiplier > 0) {
                    secondaryMin = divMultiplier * negativeDivs;
                    secondaryMax = divMultiplier * positiveDivs;
                    secondaryMajor = divMultiplier;

                    targetAxis.MinimumScale = -secondaryMin;
                    targetAxis.MaximumScale = secondaryMax;
                    targetAxis.MajorUnit = secondaryMajor;
                }
            }
            catch{
            
            }

            return realign;
        }

        public void AlignChartAxes(IExcel.Chart cht)
        {
            if (cht == null) {
                return;
            }

            try {

                IExcel.Axis primaryAxis = GetAxis(cht, IExcel.XlAxisType.xlValue, IExcel.XlAxisGroup.xlPrimary);
                IExcel.Axis secondaryAxis = GetAxis(cht, IExcel.XlAxisType.xlValue, IExcel.XlAxisGroup.xlSecondary);

                if (secondaryAxis == null) {
                    return;
                }
                Align(primaryAxis, secondaryAxis);
            }
            catch {
                //to do 
            }
            finally {
                //cleanup
            }
        }

        private IExcel.Axis GetAxis(IExcel.Chart cht, IExcel.XlAxisType valueType, IExcel.XlAxisGroup axisType)
        {
            try
            {
                return cht.Axes(valueType, axisType);
            }
            catch 
            {
                return null;
            }
        }

    }
}