Shading between lines chart type.

[Kevin Burton]

Currently I have consturcted a chart that shows a line for each column. The
chart has a median column, upper 80% column, upper 95% column, lower 80%
column, and a lower 95% column. This works just fine using
Excel.

XlChartType.xlLine

Now I would like a line showing the median, with shading up to the upper 80%
and down to the lower 80% of one color, and shading from median to upper 95%
and down to lower 95% another color. Since the upper 95% is always greater
than upper 80% the shading showing the 95% levels would only show up between
the 80% and 95% levels. The xl3DStacked type almost gets me there but the 3D
takes up too much room and there seems to be multiple colors that I don't
want. xlLineStacked seems to change the display of the data in a way that I
am unfamiliar with. Maybe "stacked" assumes that the columns are deltas. I
don't know. Any suggestions for getting the chart look that I described?
Simply put I want to be able to shade between lines. Alternatively if I had
more control over the stock type of chart where I could specify multiple
levels of the bar variance rather than just a high low etc.

Thank you.

 

[Luke M]

Here's an article that talks about filling between lines. It might point you
in the right direction.
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=590

Also, Jon Peltier has several examples of chart filling, that you might want
to look at:
http://peltiertech.com/Excel/Charts/BackgroundFill.html

 

[Kevin Burton]

Maybe this isn't the right forum. I originally went to the VSTO forum but
they directed me here.

But I am having a hard time translating the Excel instructions into code. I
have the code:

try
{
ws.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange,

ws.get_Range(((Excel.Range)ws.Cells[baseRow + 2, baseColumn + 1]),
((Excel.Range)ws.Cells[baseRow + projectedSalesList.Count + 2, baseColumn +
6])),
wb.missing,
Excel.XlYesNoGuess.xlYes,
wb.missing).Name = tableName;
if (!string.IsNullOrEmpty(tableFormat))
{
ws.ListObjects[tableName].TableStyle = tableFormat;
}
else
{
ws.ListObjects[tableName].TableStyle = "TableStyleLight1";
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
}
// Try to remove the previous chart
const string chartName = "ProjectedSalesDetailChart";
try
{
Excel.ChartObject chartObject = ws.ChartObjects(chartName)
as Excel.ChartObject;
if (chartObject != null)
{
chartObject.Delete();
}
}
catch (Exception)
{
// Eat the exception
// The chart probably doesn't exist
}
try
{
Excel.Shape shape =
ws.Shapes.AddChart(Excel.XlChartType.xlLine, wb.missing, wb.missing,
wb.missing, wb.missing);
shape.Name = chartName;
Excel.Chart chart = shape.Chart;
if (chart != null)
{

chart.SetSourceData(ws.ListObjects.get_Item(tableName).Range, wb.missing);
((Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary)).TickLabelPosition =
Excel.XlTickLabelPosition.xlTickLabelPositionHigh;
chart.HasTitle = true;
chart.ChartTitle.Text = heading;
chart.ChartArea.Width = chart.ChartArea.Width * 3;
chart.ChartArea.Height = chart.ChartArea.Height * 3;
chart.ApplyLayout(3, wb.missing);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
}

So if I follow the instructions right I want to create a combination chart.
Which one of the steps above defines a series and a type? It seems that I
want to create a normal line series and on the same chart I want to create an
area series on the delta on the same chart. Let me get that far. Thank you.

Kevin