Created
September 4, 2018 17:10
-
-
Save JamesIgoe/7d86a42c27d85bbc56ebf9a04864af13 to your computer and use it in GitHub Desktop.
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…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment