Water fall chart

amit_7276 · June 20, 2013, 4:47am

Hi,

I wants to create water fall chart using MSO charts. How can i achive this?

We can do it in PPT using the following steps:

http://www.powerpointinfographics.com/blogs/news/6299090-how-to-create-a-waterfall-chart-in-powerpoint

Thanks,
Amit

mudassir.fayyaz · June 20, 2013, 10:17am

Hi Amit,

I have observed the requirements shared by you and like to share that you can easily generate the Waterfall like chart using Aspose.Slides. The chart is in fact combination of two types of chart series. First is Stacked Column and other is Line with Marker. One can use these two type of series to get the desired result. I have created a sample application for you that will serve the purpose for you. I have also attached the generated presentation for your kind reference. I hope the shared sample will be helpful to you in further pursuance of your requirements.

public static void GenWaterfallChart()

{

//Create Empty Presentation

PresentationEx pres = new PresentationEx();

//Access First Slide

SlideEx slide = pres.Slides[0];

//Add Chart

ChartEx chart = slide.Shapes.AddChart(ChartTypeEx.StackedColumn,50, 50, 500, 400);

// chart.CategoryAxis.IsVisible = true;

//Getting the chart data worksheet

ChartDataCellFactory fact = chart.ChartData.ChartDataCellFactory;

//Add Category Total

chart.ChartData.Categories.Add(fact.GetCell(0, 5, 0, “Total”));

//Renaming Categories

fact.GetCell(0, 1, 0, “North”);

fact.GetCell(0, 2, 0, “East”);

fact.GetCell(0, 3, 0, “South”);

fact.GetCell(0, 4, 0, “West”);

//Removing default 3rd series

chart.ChartData.Series.RemoveAt(2);

//Accessing the First series and setting values

ChartSeriesEx series0 = chart.ChartData.Series[0];

series0.Values[0].Value = 0;

series0.Values[1].Value = 40;

series0.Values[2].Value = 70;

series0.Values[3].Value = 90;

series0.Values.Add(fact.GetCell(0,5,1,0));

series0.Values.Add(fact.GetCell(0, 5, 1, 0));

//Setting First series to No fill

chart.DisplayBlanksAs = DisplayBlanksAsTypeEx.Gap;

series0.Format.Fill.FillType = FillTypeEx.NoFill;

//Setting Target Series values

ChartSeriesEx series = chart.ChartData.Series[1];

series.Values[0].Value = 40;

series.Values[1].Value = 30;

series.Values[2].Value = 20;

series.Values[3].Value = 10;

series.Values.Add(fact.GetCell(0, 5, 2, 100));

series.Format.Fill.FillType = FillTypeEx.Solid;

//Setting Series Fill color and Border color

// series.Format.Fill.SolidFillColor.Color = Color.LightBlue;

series.Format.Fill.SolidFillColor.Color = Color.FromArgb(120, 0, 176, 240);

series.Format.Line.FillFormat.FillType = FillTypeEx.Solid;

series.Format.Line.FillFormat.SolidFillColor.Color = Color.Black;

//Setting Labels and its properties

series.Labels.ShowValue = true;

PortionFormatEx format = series.Labels.TextProperties.Paragraphs[0].ParagraphFormat.DefaultPortionFormat;

format.FillFormat.FillType = FillTypeEx.Solid;

format.FillFormat.SolidFillColor.Color = Color.White;

format.FontHeight = 10;

//Adding new series for Chart Line to make waterfall

int columnIndex = 4;

int id = chart.ChartData.Series.Add(fact.GetCell(0, 0, columnIndex, “Connector 1”), ChartTypeEx.LineWithMarkers);

series = chart.ChartData.Series[id];

series.Values.Add(fact.GetCell(0, 1, columnIndex, 40));

series.Values.Add(fact.GetCell(0, 2, columnIndex, 40));

series.Values.Add(fact.GetCell(0, 3, columnIndex, “”));

series.Values.Add(fact.GetCell(0, 4, columnIndex, “”));

series.Values.Add(fact.GetCell(0, 5, columnIndex, “”));

series.MarkerSymbol = MarkerStyleTypeEx.None;

series.Format.Line.Style = LineStyleEx.Single;

series.Format.Line.DashStyle = LineDashStyleEx.Dash;

series.Format.Line.Width = 1.75;

series.Format.Line.FillFormat.FillType = FillTypeEx.Solid;

series.Format.Line.FillFormat.SolidFillColor.Color = Color.Black;

//Adding 2nd series for Chart Line to make waterfall

columnIndex = 5;

id = chart.ChartData.Series.Add(fact.GetCell(0, 0, columnIndex, “Connector 2”), ChartTypeEx.LineWithMarkers);

series = chart.ChartData.Series[id];

series.Values.Add(fact.GetCell(0, 1, columnIndex, “”));

series.Values.Add(fact.GetCell(0, 2, columnIndex, 70));

series.Values.Add(fact.GetCell(0, 3, columnIndex, 70));

series.Values.Add(fact.GetCell(0, 4, columnIndex, “”));

series.Values.Add(fact.GetCell(0, 5, columnIndex, “”));

series.MarkerSymbol = MarkerStyleTypeEx.None;

series.Format.Line.Style = LineStyleEx.Single;

series.Format.Line.DashStyle = LineDashStyleEx.Dash;

series.Format.Line.Width = 1.75;

series.Format.Line.FillFormat.FillType = FillTypeEx.Solid;

series.Format.Line.FillFormat.SolidFillColor.Color = Color.Black;

//Adding 3rd series for Chart Line to make waterfall

columnIndex = 6;

id = chart.ChartData.Series.Add(fact.GetCell(0, 0, columnIndex, “Connector 3”), ChartTypeEx.LineWithMarkers);

series = chart.ChartData.Series[id];

series.Values.Add(fact.GetCell(0, 1, columnIndex, “”));

series.Values.Add(fact.GetCell(0, 2, columnIndex, “”));

series.Values.Add(fact.GetCell(0, 3, columnIndex, 90));

series.Values.Add(fact.GetCell(0, 4, columnIndex, 90));

series.Values.Add(fact.GetCell(0, 5, columnIndex, “”));

series.MarkerSymbol = MarkerStyleTypeEx.None;

series.Format.Line.Style = LineStyleEx.Single;

series.Format.Line.DashStyle = LineDashStyleEx.Dash;

series.Format.Line.Width = 1.75;

series.Format.Line.FillFormat.FillType = FillTypeEx.Solid;

series.Format.Line.FillFormat.SolidFillColor.Color = Color.Black;

columnIndex = 7;

id = chart.ChartData.Series.Add(fact.GetCell(0, 0, columnIndex, “Connector 4”), ChartTypeEx.LineWithMarkers);

series = chart.ChartData.Series[id];

series.Values.Add(fact.GetCell(0, 1, columnIndex, “”));

series.Values.Add(fact.GetCell(0, 2, columnIndex, “”));

series.Values.Add(fact.GetCell(0, 3, columnIndex, “”));

series.Values.Add(fact.GetCell(0, 4, columnIndex, 100));

series.Values.Add(fact.GetCell(0, 5, columnIndex, 100));

series.MarkerSymbol = MarkerStyleTypeEx.None;

series.Format.Line.Style = LineStyleEx.Single;

series.Format.Line.DashStyle = LineDashStyleEx.Dash;

series.Format.Line.Width = 1.75;

series.Format.Line.FillFormat.FillType = FillTypeEx.Solid;

series.Format.Line.FillFormat.SolidFillColor.Color = Color.Black;

//Setting chart plot area color

chart.PlotArea.Format.Fill.FillType = FillTypeEx.Solid;

chart.PlotArea.Format.Fill.SolidFillColor.Color = Color.LightGray;

//Hiding Legends and Value Axis

chart.HasLegend = false;

chart.ValueAxis.IsVisible = false;

//Hiding Grid Lines

chart.CategoryAxis.MajorGridLines.FillFormat.FillType=FillTypeEx.NoFill;

chart.ValueAxis.MajorGridLines.FillFormat.FillType = FillTypeEx.NoFill;

//Saving Presentation

pres.Write(“D:\Aspose Data\TesChart.pptx”);

}

Many Thanks,

amit_7276 · June 20, 2013, 11:51pm

Hi,

Thanks for your reply. I have one more issue in the output.

After generating the output if i right click on the chart and go for "Edit Data" then it automatically shows the dashed line in the chart. How can i prevent it.

I am attaching the output which is after clicking on "Edit Data" in the chart.

Please let me know how can i do that.

Thanks,

Amit

mudassir.fayyaz · June 21, 2013, 7:44am

Hi Amit,

I have observed the issue shared by you and it seems to be an inconsistency in Aspose.Slides. The ChartEx.DisplayBlankAs is getting set to Zero when chart data is edited. I have created an issue with ID SLIDESNET-34447 in our issue tracking system to further investigate and resolve the issue. This thread has been linked with the issue so that you may be automatically notified once the issue will be resolved.

We are sorry for your inconvenience,

techie.sanjeeva · December 8, 2017, 6:12am

Hi Team,

when I am applying colors to the series, colors are not reflecting, below is my code.
package com.practice.collections;
import java.awt.Color;
import java.util.Map;

import com.aspose.slides.ChartType;
import com.aspose.slides.FillType;
import com.aspose.slides.IChart;
import com.aspose.slides.IChartDataWorkbook;
import com.aspose.slides.IChartSeries;
import com.aspose.slides.ISlide;
import com.aspose.slides.LineStyle;
import com.aspose.slides.MarkerStyleType;
import com.aspose.slides.Presentation;
import com.aspose.slides.SaveFormat;

public class WaterfallChart{

private Color GREEN = new Color( 0 , 130 , 59 );
protected static String excelTemplatePath = "D:\\workspace\\Practice\\";
public static void main( String[ ] args ) {

	
	
	
	Presentation pres = new Presentation();

	//Access First Slide
	ISlide sld = pres.getSlides().get_Item(0);

	//Add Chart
	IChart chart = sld.getShapes().addChart(ChartType.StackedColumn,50, 50, 800, 400);
	
	
	

	IChartDataWorkbook fact = chart.getChartData().getChartDataWorkbook();
	
	//Add Category Total
	
	//Renaming Categories
	fact.getCell(0, 1, 0, "Compare Period");
	fact.getCell(0, 2, 0, "Buying Households");
	fact.getCell(0, 3, 0, "Frequency");
	fact.getCell(0, 4, 0, "Units per Trip");
	fact.getCell(0, 5, 0, "Current Period");


	
	//Adding new series for Chart Line to make waterfall
	int columnIndex = 4;
	IChartSeries	series = chart.getChartData().getSeries().add(fact.getCell(0, 0, 4, "Periods"),chart.getType());
	
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 1, columnIndex, 37.1));//compare period value
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 2, columnIndex, ""));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 3, columnIndex, ""));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 4, columnIndex, ""));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 5, columnIndex, 33.3));//current period value
	
	applySeriesFormatting(  series );
	


	
	columnIndex = 5;
	series = chart.getChartData().getSeries().add(fact.getCell(2, 0, 4, "Base"),chart.getType());
	
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 1, columnIndex, ""));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 2, columnIndex, 37.1-2.5));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 3, columnIndex, 37.1-2.5-2.0));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 4, columnIndex, 37.1-2.5-2.0));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 5, columnIndex, ""));
	
	applySeriesFormatting(  series );
	
	
	
	//Adding 2nd series for Chart Line to make waterfall
	columnIndex = 6;
	series = chart.getChartData().getSeries().add(fact.getCell(4, 0, 6, "Gain"),chart.getType());
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 1, columnIndex, ""));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 2, columnIndex, ""));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 3, columnIndex, ""));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 4, columnIndex, 0.7));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 5, columnIndex, ""));
	
	applySeriesFormatting(  series );
	
	columnIndex = 7;
	series = chart.getChartData().getSeries().add(fact.getCell(6, 0, 8, "loss"),chart.getType());
	
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 1, columnIndex, ""));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 2, columnIndex, 2.7));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 3, columnIndex, 2.0));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 4, columnIndex, ""));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 5, columnIndex, ""));
	
	applySeriesFormatting(  series );
	
	series.getFormat( ).getFill( ).getSolidFillColor( ).getR( );
	
	//Saving Presentation
	System.out.println( "going to start" );
	pres.save(excelTemplatePath + "WaterFall.pptx", SaveFormat.Pptx);
	System.out.println( "completed" );
	}


/*public void applySeriesDataChanges(IChart chart , String gainValue , String lossValue ,
                String key , int dataSeries, ) {
	IChartSeries series = chart.getChartData( ).getSeries( ).get_Item( dataSeries );
	
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 1, columnIndex, 37.1));//compare period value
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 2, columnIndex, ""));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 3, columnIndex, ""));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 4, columnIndex, ""));
	series.getDataPoints().addDataPointForBarSeries(fact.getCell(0, 5, columnIndex, 33.3));//current period value
}*/


private static void applySeriesFormatting( IChartSeries series ) {

	series.getMarker( ).setSymbol( MarkerStyleType.None );
	series.getFormat( ).getLine( ).setStyle( LineStyle.NotDefined );
	series.getFormat( ).getLine( ).setWidth( 1.75 );
	series.getFormat( ).getLine( ).getFillFormat( ).setFillType( FillType.NoFill );
	
}



}

Please help me.

mudassir.fayyaz · December 8, 2017, 4:36pm

@techie.sanjeeva,

I have observed your sample code. In your following sample code line, you are infact setting no Fill to series, which set no fill type to series.

In order to set the fill color for chart series, you nee do set the FillType to solid and then setting the solid fill color to color of your choice.

series.getFormat( ).getLine( ).getFillFormat( ).setFillType( FillType.Solid );
    series.getFormat( ).getLine( ).getFillFormat( ).getSolidFillColor().setColor(Color.BLUE);

I hope this will be understandable.

techie.sanjeeva · December 8, 2017, 5:20pm

Thank you so much mudassir,

How to deal with waterfall chart when we have negative values, I have attached my requirement screen shot,
could you please provide some sample code if you have anything to fill the requirement given in attached screenshot.
and Please let me know how can we hide the Y-Axis values in bar chart

Requirement.PNG (16.3 KB)

mudassir.fayyaz · December 9, 2017, 5:49am

@techie.sanjeeva,

Please try using following sample code on your end for setting the negative chart data point color.

Color inverColor = Color.Red;
Color seriesColor;
 
using (Presentation pres = new Presentation())
{
    IChart chart = p.Slides[0].Shapes.AddChart(ChartType.ClusteredColumn, 100, 100, 400, 300);
    IChartDataWorkbook workBook = chart.ChartData.ChartDataWorkbook;
 
    chart.ChartData.Series.Clear();
    chart.ChartData.Categories.Clear();
 
    // Adding new series
    chart.ChartData.Series.Add(workBook.GetCell(0, 0, 1, "Series 1"), chart.Type);
 
    // Adding new categories
    chart.ChartData.Categories.Add(workBook.GetCell(0, 1, 0, "Category 1"));
    chart.ChartData.Categories.Add(workBook.GetCell(0, 2, 0, "Category 2"));
    chart.ChartData.Categories.Add(workBook.GetCell(0, 3, 0, "Category 3"));
 
    // Take first chart series
    IChartSeries series = chart.ChartData.Series[0];
 
    // Now populating series data
    series.DataPoints.AddDataPointForBarSeries(workBook.GetCell(0, 1, 1, -20));
    series.DataPoints.AddDataPointForBarSeries(workBook.GetCell(0, 2, 1, 50));
    series.DataPoints.AddDataPointForBarSeries(workBook.GetCell(0, 3, 1, -30));
 
    seriesColor = series.GetAutomaticSeriesColor();
 
    series.InvertIfNegative = true;
    series.Format.Fill.FillType = FillType.Solid;
    series.Format.Fill.SolidFillColor.Color = seriesColor;
    series.InvertedSolidFillColor.Color = inverColor;
 
    pres.Save(outPath, SaveFormat.Pptx);
}

aspose.notifier · July 26, 2019, 6:42pm

The issues you have found earlier (filed as SLIDESNET-34447) have been fixed in this update.