Scatter Chart draws empty cells as (0-0) value

Aspose.Slides Product Family
1

Hi,
We have been evaluating Aspose.Slides for .NET and are finding it to be an excellent product and we are looking forward to using it regularly. During our evaluation, however, we discovered the following anomaly and wish to verify whether it is an issue with Aspose.Slides for .NET or a coding mistake by us.

Referring to the attached images.
We built a slide with a scatter chart automatically using C#.NET to call Aspose.Slides. When we go in and delete some of the chart data entries leaving the cells blank, those entries are drawn as points with values (0,0).

Doing the same exact thing in a chart built by hand in Power Point yields a different, expected behavior - the chart line segments for the blank data entries are not drawn at all.

Thanks for any help on this,
-Mark R.

2

Hi Mark R,

Thank you for posting.

I have observed your comments and like to request you to please share with us the generated presentation file, and the source code to reproduce this issue, so that we may investigate it further to help you out.

Best Regards,

3

Attached is presentation file.
Here is the code that generated it:

Presentation presentation = new Presentation();

// ===================================================
// Set up slide
// ===================================================
ISlide slide = presentation.Slides[0];
float slideHeight = presentation.SlideSize.Size.Height; //540 is 7.5 inches - 72 per inch
float slideWidth = presentation.SlideSize.Size.Width; //720 is 10 inches - 72 per inch

// ===================================================
// Set up the chart
// ===================================================
IChart chart = slide.Shapes.AddChart(ChartType.ScatterWithStraightLines, presentation.SlideSize.Size.Width / 2 - 300, 100, 600, 400);
chart.Height = 425.0F;
chart.Width = 700.0F;
chart.X = (slideWidth - chart.Width) / 2.0F;
chart.Y = 80.0F;

// ===================================================
// Chart Title
// ===================================================
chart.HasTitle = true;
chart.ChartTitle.AddTextFrameForOverriding("");
chart.ChartTitle.TextFrameForOverriding.Paragraphs[0].Portions[0].Text = "Aspose Slides .NET";
chart.ChartTitle.Width = chart.Width;
chart.ChartTitle.Height = 25.0F;
chart.ChartTitle.X = (chart.Width - chart.ChartTitle.Width) / 2.0F;
chart.ChartTitle.Y = 0.0F;

// ===================================================
// Chart Plot Area
// ===================================================
chart.PlotArea.Format.Fill.FillType = Aspose.Slides.FillType.Solid;
chart.PlotArea.Format.Fill.SolidFillColor.Color = Color.White;
chart.PlotArea.Height = 0.8F;
chart.PlotArea.Width = 0.7F;
chart.PlotArea.X = 0.07F;
chart.PlotArea.Y = 0.1F;

// ===================================================
// X-Axis Formatting
// ===================================================
IAxis xAxis = chart.Axes.HorizontalAxis;
xAxis.HasTitle = true;
xAxis.Title.AddTextFrameForOverriding("");
xAxis.Title.TextFrameForOverriding.Paragraphs[0].Portions[0].Text = "Time(mS)";

xAxis.IsAutomaticMinorUnit = false;
xAxis.IsAutomaticMajorUnit = false;
xAxis.MajorUnit = 0.5;
xAxis.MinorUnit = 0.1;

xAxis.IsAutomaticMaxValue = false;
xAxis.IsAutomaticMinValue = false;
xAxis.MaxValue = 7.0;
xAxis.MinValue = 0.0;

xAxis.CrossType = CrossesType.Custom;
xAxis.CrossAt = -999.99F;

xAxis.MajorGridLinesFormat.Line.FillFormat.FillType = Aspose.Slides.FillType.Solid;
xAxis.MajorGridLinesFormat.Line.FillFormat.SolidFillColor.Color = Color.Gray;
xAxis.MajorGridLinesFormat.Line.Width = 1.0;

xAxis.MinorGridLinesFormat.Line.FillFormat.FillType = Aspose.Slides.FillType.Solid;
xAxis.MinorGridLinesFormat.Line.FillFormat.SolidFillColor.Color = Color.LightGray;
xAxis.MinorGridLinesFormat.Line.Width = 0.5;

xAxis.IsNumberFormatLinkedToSource = true;


// ===================================================
// Y-Axis Formatting
// ===================================================
IAxis yAxis = chart.Axes.VerticalAxis;
yAxis.HasTitle = true;
yAxis.Title.AddTextFrameForOverriding("");
yAxis.Title.TextFrameForOverriding.Paragraphs[0].Portions[0].Text = "Voltage(V)";

yAxis.IsAutomaticMinorUnit = false;
yAxis.IsAutomaticMajorUnit = false;
yAxis.MajorUnit = 0.5;
yAxis.MinorUnit = 0.1;

yAxis.IsAutomaticMaxValue = false;
yAxis.IsAutomaticMinValue = false;
yAxis.MaxValue = 1.5;
yAxis.MinValue = -1.5;

yAxis.CrossType = CrossesType.Custom;
yAxis.CrossAt = -999.99F;

yAxis.MajorGridLinesFormat.Line.FillFormat.FillType = Aspose.Slides.FillType.Solid;
yAxis.MajorGridLinesFormat.Line.FillFormat.SolidFillColor.Color = Color.Gray;
yAxis.MajorGridLinesFormat.Line.Width = 1.0;

yAxis.MinorGridLinesFormat.Line.FillFormat.FillType = Aspose.Slides.FillType.Solid;
yAxis.MinorGridLinesFormat.Line.FillFormat.SolidFillColor.Color = Color.LightGray;
yAxis.MinorGridLinesFormat.Line.Width = 0.5;

yAxis.IsNumberFormatLinkedToSource = true;

// ===================================================
// Load the chart series with trace data
// ===================================================
// Get the chart data worksheet
IChartDataWorkbook fact = chart.ChartData.ChartDataWorkbook;
int worksheet = 0; //Set the default chart data worksheet index
chart.ChartData.Series.Clear(); //Delete demo series

int col = 0;

IChartSeries series = chart.ChartData.Series.Add(fact.GetCell(worksheet, 0, col, "Sin(x)"), chart.Type);
chart.ChartData.ChartDataWorkbook.GetCell(worksheet, 0, col).Value = "Sin(x)";
chart.ChartData.ChartDataWorkbook.GetCell(worksheet, 0, col + 1).Value = "Time(Sec)";

series.Marker.Symbol = MarkerStyleType.Circle;
series.Marker.Size = Convert.ToInt32(1.25);
if (series.Marker.Size < 2) series.Marker.Size = 2;

// Generate first part of sine wave
for (int row = 1; row <= 15; row++)
{
    double x = Convert.ToDouble(row - 1) / 10.0;
    double y = Math.Sin(x);
    series.DataPoints.AddDataPointForScatterSeries(fact.GetCell(worksheet, row, col, x), fact.GetCell(worksheet, row, col + 1, y));
}

// Generate empty cells
for (int row = 16; row <= 18; row++)
{
    series.DataPoints.AddDataPointForScatterSeries(fact.GetCell(worksheet, row, col, ""), fact.GetCell(worksheet, row, col + 1, ""));
}

// Generate second part of sine wave
for (int row = 19; row <= 71; row++)
{
    double x = Convert.ToDouble(row - 1) / 10.0;
    double y = Math.Sin(x);
    series.DataPoints.AddDataPointForScatterSeries(fact.GetCell(worksheet, row, col, x), fact.GetCell(worksheet, row, col + 1, y));
}

// ===================================================
// Chart Legend
// ===================================================
FontData fd1 = new FontData("Calibri"); // Define new fonts
chart.Legend.TextFormat.PortionFormat.LatinFont = fd1; // Assign new fonts to portion
chart.Legend.TextFormat.PortionFormat.FontBold = NullableBool.True; // Set font to Bold
chart.Legend.TextFormat.PortionFormat.FontItalic = NullableBool.False; // Set font to not Italic
chart.Legend.TextFormat.PortionFormat.FillFormat.FillType = Aspose.Slides.FillType.Solid; // Set font color
chart.Legend.TextFormat.PortionFormat.FillFormat.SolidFillColor.Color = Color.Black;

chart.Legend.TextFormat.PortionFormat.FontHeight = 10.0F;
if (chart.Legend.Entries.Count >= 20) chart.Legend.TextFormat.PortionFormat.FontHeight = 7.0F;

chart.Legend.Width = 0.2F;
chart.Legend.Height = chart.Legend.Entries.Count * chart.Legend.TextFormat.PortionFormat.FontHeight;
chart.Legend.X = 0.8F;
chart.Legend.Y = 0.5F - (chart.Legend.Height / 2.0F);
chart.Legend.TextFormat.ParagraphFormat.Alignment = TextAlignment.Center;


// ===================================================
// Save the presentation
// ===================================================
presentation.Save(@"chart.pptx", SaveFormat.Pptx);
System.Diagnostics.Process.Start(@"chart.pptx");
4

Hi Mark R,

I have observed your comments and like to request you to please try using the below sample code on your end to serve the purpose. This code inserts a null value instead of an empty one, and a property DisplayBlanksAs has been set. I have also attached the generated presentation for your kind reference.

Presentation presentation = new Presentation();

// ===================================================

// Set up slide

// ===================================================

ISlide slide = presentation.Slides[0];

float slideHeight = presentation.SlideSize.Size.Height; //540 is 7.5 inches - 72 per inch

float slideWidth = presentation.SlideSize.Size.Width; //720 is 10 inches - 72 per inch

// ===================================================

// Set up the chart

// ===================================================

IChart chart = slide.Shapes.AddChart(ChartType.ScatterWithStraightLines, presentation.SlideSize.Size.Width / 2 - 300, 100, 600, 400);

chart.Height = 425.0F;

chart.Width = 700.0F;

chart.X = (slideWidth - chart.Width) / 2.0F;

chart.Y = 80.0F;

// ===================================================

// Chart Title

// ===================================================

chart.HasTitle = true;

chart.ChartTitle.AddTextFrameForOverriding("");

chart.ChartTitle.TextFrameForOverriding.Paragraphs[0].Portions[0].Text = "Aspose Slides .NET";

chart.ChartTitle.Width = chart.Width;

chart.ChartTitle.Height = 25.0F;

chart.ChartTitle.X = (chart.Width - chart.ChartTitle.Width) / 2.0F;

chart.ChartTitle.Y = 0.0F;

// ===================================================

// Chart Plot Area

// ===================================================

chart.PlotArea.Format.Fill.FillType = Aspose.Slides.FillType.Solid;

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

chart.PlotArea.Height = 0.8F;

chart.PlotArea.Width = 0.7F;

chart.PlotArea.X = 0.07F;

chart.PlotArea.Y = 0.1F;

// ===================================================

// X-Axis Formatting

// ===================================================

IAxis xAxis = chart.Axes.HorizontalAxis;

xAxis.HasTitle = true;

xAxis.Title.AddTextFrameForOverriding("");

xAxis.Title.TextFrameForOverriding.Paragraphs[0].Portions[0].Text = "Time (mS)";

xAxis.IsAutomaticMinorUnit = false;

xAxis.IsAutomaticMajorUnit = false;

xAxis.MajorUnit = 0.5;

xAxis.MinorUnit = 0.1;

xAxis.IsAutomaticMaxValue = false;

xAxis.IsAutomaticMinValue = false;

xAxis.MaxValue = 7.0;

xAxis.MinValue = 0.0;

xAxis.CrossType = CrossesType.Custom;

xAxis.CrossAt = -999.99F;

xAxis.MajorGridLinesFormat.Line.FillFormat.FillType = Aspose.Slides.FillType.Solid;

xAxis.MajorGridLinesFormat.Line.FillFormat.SolidFillColor.Color = Color.Gray;

xAxis.MajorGridLinesFormat.Line.Width = 1.0;

xAxis.MinorGridLinesFormat.Line.FillFormat.FillType = Aspose.Slides.FillType.Solid;

xAxis.MinorGridLinesFormat.Line.FillFormat.SolidFillColor.Color = Color.LightGray;

xAxis.MinorGridLinesFormat.Line.Width = 0.5;

xAxis.IsNumberFormatLinkedToSource = true;

// ===================================================

// Y-Axis Formatting

// ===================================================

IAxis yAxis = chart.Axes.VerticalAxis;

yAxis.HasTitle = true;

yAxis.Title.AddTextFrameForOverriding("");

yAxis.Title.TextFrameForOverriding.Paragraphs[0].Portions[0].Text = "Voltage (V)";

yAxis.IsAutomaticMinorUnit = false;

yAxis.IsAutomaticMajorUnit = false;

yAxis.MajorUnit = 0.5;

yAxis.MinorUnit = 0.1;

yAxis.IsAutomaticMaxValue = false;

yAxis.IsAutomaticMinValue = false;

yAxis.MaxValue = 1.5;

yAxis.MinValue = -1.5;

yAxis.CrossType = CrossesType.Custom;

yAxis.CrossAt = -999.99F;

yAxis.MajorGridLinesFormat.Line.FillFormat.FillType = Aspose.Slides.FillType.Solid;

yAxis.MajorGridLinesFormat.Line.FillFormat.SolidFillColor.Color = Color.Gray;

yAxis.MajorGridLinesFormat.Line.Width = 1.0;

yAxis.MinorGridLinesFormat.Line.FillFormat.FillType = Aspose.Slides.FillType.Solid;

yAxis.MinorGridLinesFormat.Line.FillFormat.SolidFillColor.Color = Color.LightGray;

yAxis.MinorGridLinesFormat.Line.Width = 0.5;

yAxis.IsNumberFormatLinkedToSource = true;

// ===================================================

// Load the chart series with trace data

// ===================================================

// Get the chart data worksheet

IChartDataWorkbook fact = chart.ChartData.ChartDataWorkbook;

int worksheet = 0; //Set the default chart data worksheet index

chart.ChartData.Series.Clear(); //Delete demo series

int col = 0;

IChartSeries series = chart.ChartData.Series.Add(fact.GetCell(worksheet, 0, col, "Sin(x)"), chart.Type);

chart.ChartData.ChartDataWorkbook.GetCell(worksheet, 0, col).Value = "Sin(x)";

chart.ChartData.ChartDataWorkbook.GetCell(worksheet, 0, col + 1).Value = "Time (Sec)";

series.Marker.Symbol = MarkerStyleType.Circle;

series.Marker.Size = Convert.ToInt32(1.25);

if (series.Marker.Size < 2) series.Marker.Size = 2;

// Generate first part of sine wave

for (int row = 1; row <= 15; row++)

{

    double x = Convert.ToDouble(row - 1) / 10.0;

    double y = Math.Sin(x);

    series.DataPoints.AddDataPointForScatterSeries(fact.GetCell(worksheet, row, col, x), fact.GetCell(worksheet, row, col + 1, y));

}

// Generate empty cells

for (int row = 16; row <= 18; row++)

{

    series.DataPoints.AddDataPointForScatterSeries(fact.GetCell(worksheet, row, col, ""), fact.GetCell(worksheet, row, col + 1, null));

}

// Generate second part of sine wave

for (int row = 19; row <= 71; row++)

{

    double x = Convert.ToDouble(row - 1) / 10.0;

    double y = Math.Sin(x);

    series.DataPoints.AddDataPointForScatterSeries(fact.GetCell(worksheet, row, col, x), fact.GetCell(worksheet, row, col + 1, y));

}

// ===================================================

// Chart Legend

// ===================================================

FontData fd1 = new FontData("Calibri"); // Define new fonts

chart.Legend.TextFormat.PortionFormat.LatinFont = fd1; // Assign new fonts to portion

chart.Legend.TextFormat.PortionFormat.FontBold = NullableBool.True; // Set font to Bold

chart.Legend.TextFormat.PortionFormat.FontItalic = NullableBool.False; // Set font to not Italic

chart.Legend.TextFormat.PortionFormat.FillFormat.FillType = Aspose.Slides.FillType.Solid; // Set font color

chart.Legend.TextFormat.PortionFormat.FillFormat.SolidFillColor.Color = Color.Black;

chart.Legend.TextFormat.PortionFormat.FontHeight = 10.0F;

if (chart.Legend.Entries.Count >= 20) chart.Legend.TextFormat.PortionFormat.FontHeight = 7.0F;

chart.Legend.Width = 0.2F;

chart.Legend.Height = chart.Legend.Entries.Count * chart.Legend.TextFormat.PortionFormat.FontHeight;

chart.Legend.X = 0.8F;

chart.Legend.Y = 0.5F - (chart.Legend.Height / 2.0F);

chart.Legend.TextFormat.ParagraphFormat.Alignment = TextAlignment.Center;

chart.DisplayBlanksAs = DisplayBlanksAsType.Gap;

// ===================================================

// Save the presentation

// ===================================================

presentation.Save(@"D:\ChartAllGood_16.3.0.pptx", SaveFormat.Pptx);

I hope this will be helpful. Please share if I may help you further in this regard.

Best Regards,

5

Adnan Ahmad,
I have tested it and it works exactly as we hoped. Thanks for your very quick responses on this!

-Mark R.