Adding invert if negative color is also in the legend entry in Excel chart in .NET

Aspose.Cells Product Family
1

Dear @Amjad_Sahi/ @shakeel.faiz,

My requirement is need to have two legend entry for the same series. Is it possible using the Aspose. For the single series we are applying two colors, when invert if negative apply. So I need both the colors in the legend. How we can achive it? Is there any work around?

I have attached the excel with more description. Thanks in advance.

Regards
Prabu R LEGEND-Issue_img.png (92.4 KB)

2

@prabu759101,

Thanks for providing us some details.

I could not download your attachment (“LEGEND-Issue_img.png”), it is removed or corrupted. Please attach is again here. Also, create an Excel file containing your desired chart manually in MS Excel, save the file and post us here, we will check how to create such a chart (having two legend entries for the same series) via Aspose.Cells APIs.

3

Dear @Amjad_Sahi,

Please find the attachment here. Thanks in advance.

Legend entries.zip (116.8 KB)

LEGEND-Issue_img.png (92.4 KB)

4

@prabu759101,

Thanks for the screenshot and the file.

I requested you earlier to create an Excel file (containing such a chart having two legend entries for the same series) in MS Excel manually and provide the Excel file here. It looks like MS Excel does not support creating two legend entries for the same series at the same time. If MS Excel cannot do that, Aspose.Cells also cannot do that as Aspose.Cells follows MS Excel standards and specifications while creating charts. Also, you have inserted the square symbol in the relevant cell. I think you will surely be getting the alignment problem as the series name is dynamic. I am afraid, if you even cannot accomplish the task manually and accurately, it would be hard for Aspose.Cells to do that.

5

Dear @Amjad_Sahi,

Thank you for the response.

Yes, We can’t do that in Excel Manually also. Just I am asking that, is there any workaround for that.

Thanks.

Regards
Prabu R

6

@prabu759101,

Well, I am afraid, Aspose.Cells can do what you have done manually in MS Excel (i.e., insert the square symbol in the cell and apply formatting to it) but it is not reliable all the time.

7

@Amjad_Sahi,

Thank you for your response.

I am planning to go with the below two options.

Option 1:
Instead of square symbol, can we have square image and place it in template. Will it be reliable.

Option 2:
Create one dummy chart which contain four series with plot area size as zero and showing the legend only oin the chart area. Here the fourth series name is (" ") blank series.

Regards
Prabu R

8

@prabu759101,

After some further evaluation, we think you may add a series that won’t be displayed to represent the invert series for your needs via Aspose.Cells APIs, see the sample code segment and find attached the sample files for your reference:
e.g
Sample code:

private static void TestTmp()
        {
            string dir = "D:/tmp/";
            var wb = new Workbook(dir + "LEGEND-Issue.xlsx");

            Chart c = wb.Worksheets[0].Charts[0];

            int index = c.NSeries.Add("{0}", false);
            Series s = c.NSeries[index];
            s.PlotOnSecondAxis = true;
            s.Area.ForegroundColor = Color.Red;
            s.Name = "Invert";

            wb.Save(dir + "save.xlsx");
        }

Hope, this helps a bit.
files1.zip (32.3 KB)

9

@Amjad_Sahi,

Thank you for the solution. I will try this way and let you know.

Regards
Prabu R

10

@prabu759101,

Sure, take your time to test the solution. Hopefully, it will work fine for your requirements.

11

Thanks @Amjad_Sahi.

Its working fine.

12

@prabu759101,

Good to know that the suggested code works for your needs. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.