Aspose line Chart removing shade on null data

Aspose.Cells Product Family
1

Hi

I have a line chart that is being created in c# using aspose. The chart contains 2 series and is shaded in between those two lines. The data being used has a gap in between nov and mar which is displayed on the chart, but there is also a shaded area that sinks to 0 at the start of the gap and rises to a value when having real data again. I was wondering how can I remove those lines. In the attachment is a picture of the chart and I have circled the part of the chart I am trying to remove.
2

Hi Arnold,


Thank you for contacting Aspose support.

Please provide the sample application to generate the said chart. Moreover, please either create the data source dynamically in your code or use a spreadsheet containing the data that will serve as source to the chart. We need to investigate if your required feature is supported by Aspose.Cells for .NET APIs and also if it corresponds to the specifications of Excel application.

By the way, have you tried to remove the data lines for blank entries using Excel application?
3

Hi babar

Thanks for the response, what I am trying to do is find a way to remove the drop and rise that is encircled in the chart image provided. I have also provided a sample console application. that picks up the excel sheet template and creates data and saves it to the c:\temp folder.
4

Hi,

Thanks for your posting and using Aspose.Cells.

Please try setting the Chart.PlotEmptyCellsType to one of the following values and see if it fixes your issue.

  • PlotEmptyCellsType.Interpolated
  • PlotEmptyCellsType.NotPlotted
  • PlotEmptyCellsType.Zero

Also I have attached the excel file generated by your code with one of your chart. Please download it and manually fix it using Microsoft Excel and re-attach it and also let us know the steps you take in Microsoft Excel to fix this file. We will then look into it further and let you know the related Aspose.Cells property that can be used for this purpose.

5

I dont understand what you need me to fix?

6

Hi,

Thanks for your questions and considering Aspose.Cells.

I want you to fix manually using Microsoft Excel the shaded lines as you have shown in your previous screenshot


Attachment: Sample.PNG

And then let me know the steps you took in Microsoft Excel to fix them. It will help us investigate the issue and we will be able to provide you a sample code. Please also attach the fixed excel file. Let us know if you still have some confusion or question.

7

Are you saying trying to remove the gray lines that are encircled, because that is what I am trying to ask you.

8

Hi,

Thanks for your posting and using Aspose.Cells.

Please see the following sample code to achieve your requirements. You need to set these properties.

  • Chart.PlotVisibleCells
  • Chart.PlotEmptyCellsType

I have also attached the image showing the chart before and after execution of the code for your reference.

Source excel file used in the code: test.xlsx
Output excel file generated by the code: output.xlsx

C#

Workbook workbook = new Workbook(“test.xlsx”);

Worksheet sheet = workbook.Worksheets[“SampleReport”];

Chart ch = sheet.Charts[0];
ch.PlotVisibleCells = true;
ch.PlotEmptyCellsType = PlotEmptyCellsType.Zero;

workbook.Save(“output.xlsx”);

9

I am trying to remove the silver line that is droping and climbing. The “after” you posted just seems to highlighting it red. Is there an expression or a formula I can write on the series that checks the value set its visibility based on value? The highlighted image posted is what I am trying to remove

10

Hi,

Thanks for your clarification and using Aspose.Cells.

We have looked into this issue further and found that issue was caused because of some unnecessary and unwanted series of type area stacked, once you will remove them, you will have a desired output.

Please see the following code that I applied on test.xlsx and got the output as shown in the image.

I have also attached the output excel file generated by it for your reference.

C#


Workbook workbook = new Workbook(“test.xlsx”);

Worksheet ws = workbook.Worksheets[0];

Chart ch = ws.Charts[0];

for (int i = ch.NSeries.Count -1; i>=0; i-- )
{
Series sr = ch.NSeries[i];

Console.WriteLine(i + ": " + sr.Type);

if(sr.Type == ChartType.AreaStacked)
{
ch.NSeries.RemoveAt(i);
}
}

workbook.Save(“output.xlsx”);

11

Thanks for the help. I implemented the changes but that code delete the series entirely I am trying to delete certain points of the series. Specifically when that value in the series is null or “”.

12

Hi,

Thanks for your feedback and using Aspose.Cells.

Could you please try changing the series type instead of deleting it? Currently, the series type is AreaStacked, please change it to Line and check how it goes.

13

I can give it a try but the stackarea from what I understand is to fill in the gaps between the series. Which is what I need to do. What would be an alternative and can I alter the color on a stack area at certain points by value.

14

Hi,

Thanks for your feedback and using Aspose.Cells.

Instead of deleting the series, you can set it 100% transparent, this way your series will remain there but it will be invisible.

Please check the output excel file attached with this post generated with the following code which sets the first series to 100% transparent.

C#

Workbook workbook = new Workbook(“test.xlsx”);

Worksheet ws = workbook.Worksheets[0];

Chart ch = ws.Charts[0];

Series sr = ch.NSeries[0];
sr.Area.Formatting = FormattingType.Custom;
sr.Area.Transparency = 1.0;

workbook.Save(“output-transparency.xlsx”);

15

Can this be done on specific points on the stackarea. Can I get the xvalue point of that chart

16

Hi,

Thanks for your posting and using Aspose.Cells.

I have tried to achieve your requirements in Microsoft Excel and found that your requirements can be achieved in other charts like Line etc but cannot be achieves in Stack Area chart. Because you cannot select a single point inside it. You will always have to select all series. I have attached one sample excel file for your reference.

Please download it and try it yourself in Microsoft Excel. If you see it is doable in Excel, then let us know the steps.

Please note if something is not doable in Microsoft Excel, then it will not be automatically doable in Aspose.Cells.