Two axis column chart problem

Aspose.Cells Product Family
1

Hi,

I have following problem with creating column chart:

static void Main(string[] args)
{

Workbook workbook = new Workbook();

int sheetIndex = workbook.Worksheets.Add();

Worksheet worksheet = workbook.Worksheets[sheetIndex];

Style styl = new Style();

styl.Number = 15;

worksheet.Cells["A3"].PutValue("Series 1");

worksheet.Cells["A5"].PutValue("Series 2");

worksheet.Cells["A7"].PutValue("Series 3");

worksheet.Cells["A9"].PutValue("Series 4");

worksheet.Cells["B3"].PutValue(1.44);

worksheet.Cells["C3"].PutValue(0.2);

worksheet.Cells["D3"].PutValue(3.6);

worksheet.Cells["E3"].PutValue(4.5);

worksheet.Cells["F3"].PutValue(5.17);

worksheet.Cells["B5"].PutValue(1.1);

worksheet.Cells["C5"].PutValue(1.2);

worksheet.Cells["D5"].PutValue(3.5);

worksheet.Cells["E5"].PutValue(4.5);

worksheet.Cells["F5"].PutValue(5.7);

worksheet.Cells["B7"].PutValue(1.4);

worksheet.Cells["C7"].PutValue(1.32);

worksheet.Cells["D7"].PutValue(5.0);

worksheet.Cells["E7"].PutValue(4.9);

worksheet.Cells["F7"].PutValue(3.7);

worksheet.Cells["B9"].PutValue(1.4);

worksheet.Cells["C9"].PutValue(1.2);

worksheet.Cells["D9"].PutValue(3);

worksheet.Cells["E9"].PutValue(4.5);

worksheet.Cells["F9"].PutValue(5.7);

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Line, 10, 10, 20, 20);

Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];

chart.NSeries.Add("A2:F9", false);

chart.NSeries[0].Name = "=A2";//fake

chart.NSeries[0].LegendEntry.IsDeleted = true;

chart.NSeries[0].Type=ChartType.Column;

chart.NSeries[0].PlotOnSecondAxis = true;

chart.NSeries[1].Name = "=A3";

chart.NSeries[1].Type = ChartType.Column;

chart.NSeries[1].PlotOnSecondAxis = false;

chart.NSeries[2].Name = "=A4";//fake

chart.NSeries[2].LegendEntry.IsDeleted = true;

chart.NSeries[2].Type = ChartType.Column;

chart.NSeries[2].PlotOnSecondAxis = false;

chart.NSeries[3].Name = "=A5";

chart.NSeries[3].Type = ChartType.Column;

chart.NSeries[3].PlotOnSecondAxis = true;

chart.NSeries[4].Name = "=A6";//fake

chart.NSeries[4].LegendEntry.IsDeleted = true;

chart.NSeries[4].Type = ChartType.Column;

chart.NSeries[4].PlotOnSecondAxis = false;

chart.NSeries[5].Name = "=A7";

chart.NSeries[5].Type = ChartType.Column;

chart.NSeries[5].PlotOnSecondAxis = true;

chart.NSeries[6].Name = "=A8";//fake

chart.NSeries[6].LegendEntry.IsDeleted = true;

chart.NSeries[6].Type = ChartType.Column;

chart.NSeries[6].PlotOnSecondAxis = true;

chart.NSeries[7].Name = "=A9";

chart.NSeries[7].Type = ChartType.Column;

chart.NSeries[7].PlotOnSecondAxis = false;

chart.SecondValueAxis.IsVisible = true;

chart.SecondValueAxis.CrossType = CrossType.Automatic;

chart.PlotArea.Area.ForegroundColor = Color.White;

workbook.Save("C:\\book1.xlsx");

}

I have no idea how to correctly remove empty (fake) series labels from the legend. The problem also appears when I add another series type (line, area) to chart.

Best regards,

Piotr

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

You are including blank series while creating charts, if you exclude them, it will give you correct output.

Please download and use the latest version:
Aspose.Cells
for .NET v7.3.3.2

Please see the following code. I have attached its output file and the screenshot for your reference.

C#

Workbook workbook = new Workbook();

int sheetIndex = workbook.Worksheets.Add();

Worksheet worksheet = workbook.Worksheets[sheetIndex];

Style styl = new Style();

styl.Number = 15;

worksheet.Cells[“A3”].PutValue(“Series 1”);

worksheet.Cells[“A4”].PutValue(“Series 2”);

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

worksheet.Cells[“A6”].PutValue(“Series 4”);

worksheet.Cells[“B3”].PutValue(1.44);

worksheet.Cells[“C3”].PutValue(0.2);

worksheet.Cells[“D3”].PutValue(3.6);

worksheet.Cells[“E3”].PutValue(4.5);

worksheet.Cells[“F3”].PutValue(5.17);

worksheet.Cells[“B4”].PutValue(1.1);

worksheet.Cells[“C4”].PutValue(1.2);

worksheet.Cells[“D4”].PutValue(3.5);

worksheet.Cells[“E4”].PutValue(4.5);

worksheet.Cells[“F4”].PutValue(5.7);

worksheet.Cells[“B5”].PutValue(1.4);

worksheet.Cells[“C5”].PutValue(1.32);

worksheet.Cells[“D5”].PutValue(5.0);

worksheet.Cells[“E5”].PutValue(4.9);

worksheet.Cells[“F5”].PutValue(3.7);

worksheet.Cells[“B6”].PutValue(1.4);

worksheet.Cells[“C6”].PutValue(1.2);

worksheet.Cells[“D6”].PutValue(3);

worksheet.Cells[“E6”].PutValue(4.5);

worksheet.Cells[“F6”].PutValue(5.7);

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Line, 10, 10, 20, 20);

Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];

chart.NSeries.Add(“A3:F6”, false);

workbook.Save(“book1.xlsx”, SaveFormat.Xlsx);
Screenshot:

3

Hi,

I think you misunderstood me. I create chart according to tutorial (`http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html`). I can’t remove empty series because the columns will be unreadable when I assign them to two value axes.

Best regards,

Piotr

4

Hi,


I think you may try to use chart.Legend.LegendEntries[index].IsDeleted to set to true to remove the legend entry for the empty series in the legend box for your needs.
e.g
chart.Legend.LegendEntries[1].IsDeleted = true;

Thank you.
5

Hi,

I try to generate many charts and I am not able to decide which index in legend I should remove. Excel change the order of data in legend when chart contains two value axes or different types of series (line, area, column). Removing legend labels using chart.Nseries[i]. LegendEntries[1].IsDeleted is only available way.

Best regards,

Piotr

6

Hi,

Please see the following code. It loads source file generated by your code, it then iterates all the series and if the series is blank, it removes that series.

I have written my own function IsSeriesBlank() which decides if the series is blank or it has some values.

I have attached the source file and output file as well as screenshot for your reference. Hopefully, it will be helpful for you.

C#


//This function will return true if the series is not blank

bool IsSeriesBlank(Worksheet sheet, Series series)

{

string[] vals = series.Values.Split(new string[] { “!” }, StringSplitOptions.RemoveEmptyEntries);

string[] vals2 = vals[1].Split(new string[] { “:” }, StringSplitOptions.RemoveEmptyEntries);

string rangeAddress = vals2[1];



Range range = sheet.Cells.CreateRange(rangeAddress);


for (int r = range.FirstRow; r < range.FirstRow + range.RowCount; r++)

{

for (int c = range.FirstColumn; c < range.FirstColumn + range.ColumnCount; c++)

{

Cell cell = sheet.Cells[r, c];


if (cell.StringValue != “”)

return false;

}//column

}//row



return true;


}


void TestRun()

{

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


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


Chart chart = worksheet.Charts[0];


for (int i = chart.NSeries.Count -1; i >=0; i–)

{

if (IsSeriesBlank(worksheet, chart.NSeries[i]) == true)

chart.NSeries.RemoveAt(i);

}


workbook.Save(“output.xlsx”, SaveFormat.Xlsx);


}

Screenshot:
7

Hi,

I don’t want to remove the empty series. I have to generate empty series to make chart more readable. This is popular solution for creating column charts with two value axes (described here: http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html).

Your code removes empty series from chart. Columns assigned to left value axis overlaps on columns assigned to right value axis. I would like to know why removing series name from legend (this way: chart.NSeries[x].LegendEntry.IsDeleted = true;) doesn’t work in code pasted in my first post. Is it my fault?

Best Regards,

Piotr

8

Hi,

Thanks for your feedback.

Please provide me your actual and expected output xls/xlsx files which you can create manually using Ms-Excel 2010 and attach here.

  1. book1.xlsx (generated with your code)
  2. book1_expected.xlsx (expected by you)

We will look into your issue in detail and then get back to you with our sample code or workaround.

If it is some new or broken feature, then we will log it in our database so that it could be fixed in our next releases.
9

Hi,

I add files which you described in previous post.

Best Regards,
Piotr
10

Hi,

Thanks for your sample files.

I have investigated this issue and found it is a bug. Because Aspose.Cells cannot calculate delete legend entry properly. When we delete fake (series) legend entries, it deletes the actual (series) legend entries.

As you stated before, order of legend entries gets changed, so chart.NSeries[i].LegendEntry.IsDeleted = true does not work properly.

We need to investigate this issue and fix it. In order to elaborate this issue precisely, I have created a another similar sample.xlsx file and screenshot for a reference.

We have logged this issue in our database with the issue id: CELLSNET-41179.

We will fix this issue or provide you a workaround if possible and update you asap.

Screenshot:


11

Hi,

Thanks for quick response. I also observed that this problem also occurs in Aspose.Slides.

Best Regards,

Piotr

12
piosid90:

Hi,

Thanks for quick response. I also observed that this problem also occurs in Aspose.Slides.

Best Regards,

Piotr

Hi,

For Aspose.Slides issues, please post your queries on Aspose.Slides Forums.
13

Hi,

We have fixed this issue.

Please download and try this fix: Aspose.Cells for .NET v7.3.3.4 and let us know your feedback.

14

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

This message was posted using Notification2Forum from Downloads module by aspose.notifier.