How to delete datalabels from chart with 0 value

Aspose.Cells Product Family
1

Hi,

Please help me to delete datalabels from chart with 0 value.
check the attachment for clarification.
This chart is OLE based chart.
And please reply ASAP. I am waiting for your reply.
Saurabh

2

Hi,

I think, you will have to remove it from series, remove all values which have 0 value. If you are unable to do it, then please provide me your source xls/xlsx file having this chart.

3

Hi Saurabh,


As you have shared that the chart you are trying to add is Ole based. I am assuming you are using Excel chart then. If this is the case then the issue is related to Aspose.Cells. Please share the code snippet for creating chart and its addition to presentation.

Thanks and Regards.
4

check sample code

5

I am using both aspose.slide as well as aspose.cell.
If you feel this issue is related to aspose.cell. can you move this thread to aspose.cell

6

Ignore my last post … I thought u r asking to move the post… i already shared my code . please check that

7

Sorry forgot to attach presentation.

8

Hi,

Please see the code below, I have deleted the third datapoint and the datalable. You need to make the code generic though.

Please see the source input and output xlsx files and the screenshot.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];


Chart chart = worksheet.Charts[0];


//Hide the 3rd datapoint of both series

int dataPointIndex = 2;


//Access the 3rd datapoint from A series and make it transparent

Area dtPointArea = chart.NSeries[0].Points[dataPointIndex].Area;

dtPointArea.ForegroundColor = Color.White;

dtPointArea.Transparency = 1;


//Access the 3rd datapoint from B series and make it transparent

dtPointArea = chart.NSeries[1].Points[dataPointIndex].Area;

dtPointArea.ForegroundColor = Color.White;

dtPointArea.Transparency = 1;



//Delete the values in cell A5

worksheet.Cells[“A5”].PutValue("");



workbook.Save(filePath + “.out.xlsx”);


Screenshot
9

Hi,

Here is another better approach, I have deleted now cell A5:C5, please see the source, output xlsx files and screenshot.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];


//Delete cell A5,B5 and C5

worksheet.Cells.DeleteRange(4, 0, 4, 2, ShiftType.Up);


workbook.Save(filePath + “.out.xlsx”);
Screenshot:

10

Hi,

Can you tell me please , how can I dynamically know which datapoint value is 0.

Saurabh

11

Hi,

First access your series range using Chart.NSeries[0].Values, it will give you something like this "=Sheet1!$B$3:$B$7"

Now, iterate all the cells in a range B3:B7 and find out the 0 value cells and delete them.

You will have to repeat this for all series found Chart.NSeries property. And also you have to have additional check that you will delete cells only when all the series cells have 0 values.

And to delete the datalable, you need to access the series range using Chart.NSeries[0].XValues, it will again give you range something like this “=Sheet1!$A$3:$A$7” and repeat the same process and delete the datalabel

12
Hi,

If you replace zero as null or do not input zero to cell, the zero datalabels will not be shown.

a) If you do setting the value to Cell by the API, please change your code as following:
for (int i = 1; i < 5; i++)
for (int j = 1; j < 3; j++)
{
try
{
if(dblData[i - 1][j - 1] != 0)
dataSheet.Cells[i, j].PutValue(dblData[i - 1][j - 1]);
}
catch (Exception ex)
{

dataSheet.Cells[i, j].PutValue(dblData[i - 1][j - 1]);
}

}

b) Replace zero with null if you are opening a template file:
Workbook wb = new Workbook(@"D:\FileTemp\source.xlsx");
Chart chart = wb.Worksheets[0].Charts[0];
for (int i = 0; i < chart.NSeries.Count; i++)
{
Series s = chart.NSeries[i];
string v = s.Values;
string[] p = v.Split('!');
string sheetName = p[0].Substring(1);
string rangeName = p[1].Replace("$", "");
Worksheet sheet = wb.Worksheets[sheetName];
Range range = sheet.Cells.CreateRange(rangeName);
foreach (Cell cell in range)
{
if (cell.Type == CellValueType.IsNumeric && cell.DoubleValue == 0)
{
cell.PutValue(null);
}
}
}
wb.Save(@"D:\FileTemp\dest.xlsx");

Hope, this helps.

Thank you.