Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Mon Jan 25, 2021 2:02 pm

Hi everyone :)

I want to position the legend of the chart in the upper right corner.

With the
Code: Select all
LegendPositionType.Corner
option I don't get what I want because I lose space at the top. What I want is that the legend stays in the X axis as if I indicated
Code: Select all
LegendPositionType.Right
but that it goes up and is placed in the beginning of the plotting area.

I have also tried to give it the X and Y position manually but it is not a solution that I like because this way I have to set a width and a height; and then we start to depend on the width of the text of each series and the number of them. Any ideas?

Thanks.

blastdoman
 
Posts: 27
Joined: Thu Jul 02, 2020 7:06 am

Tue Jan 26, 2021 3:39 am

Hello,

Thanks for your inquiry.
To help us better understand your requirement and provide you with an accurate response, please provide us with your source Excel file, your full code as well as the output you expect. You could send them to us (support@e-iceblue.com) via email. Thanks in advance for your assistance.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Tue Jan 26, 2021 8:18 am

I want to position the legend as shown in the attached image. When I change the X and Y properties of the legend I have to indicate also a width and a height to the legend because if not, it is not displayed. Therefore, I already have to control the width of the texts of each series and the number of these to know the width and height that I have to indicate. Is there any other way to position the legend as I want?

Thank you.
Attachments
LeyendaPosicionamiento.jpg
LeyendaPosicionamiento.jpg (89.05 KiB) Viewed 921 times

blastdoman
 
Posts: 27
Joined: Thu Jul 02, 2020 7:06 am

Tue Jan 26, 2021 10:04 am

Hello,

Thanks for your response.
According to your code and your picture, I did an initial test but found that when setting “LegendPositionType.Corner” or “LegendPositionType.Right”, the chart legend position of the generated file is correct, not as you described. Below is my full test code, and I also attached my output file.
Code: Select all
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Range["A1"].Value = "A";
            sheet.Range["B1"].Value = "B";
            sheet.Range["C1"].Value = "C";
            sheet.Range["D1"].Value = "D";
            sheet.Range["E1"].Value = "E";
            sheet.Range["F1"].Value = "F";

            sheet.Range["A2"].NumberValue = 6000;
            sheet.Range["A3"].NumberValue = 8000;
            sheet.Range["A4"].NumberValue = 9000;
            sheet.Range["A5"].NumberValue = 8500;
            sheet.Range["A6"].NumberValue = 3000;
            sheet.Range["A7"].NumberValue = 5500;

            sheet.Range["B2"].NumberValue = 5000;
            sheet.Range["B3"].NumberValue = 7000;
            sheet.Range["B4"].NumberValue = 8000;
            sheet.Range["B5"].NumberValue = 7500;
            sheet.Range["B6"].NumberValue = 4000;
            sheet.Range["B7"].NumberValue = 4500;

            sheet.Range["C2"].NumberValue = 6500;
            sheet.Range["C3"].NumberValue = 8500;
            sheet.Range["C4"].NumberValue = 9500;
            sheet.Range["C5"].NumberValue = 9000;
            sheet.Range["C6"].NumberValue = 4000;
            sheet.Range["C7"].NumberValue = 5500;

            sheet.Range["D2"].NumberValue = 7000;
            sheet.Range["D3"].NumberValue = 9000;
            sheet.Range["D4"].NumberValue = 10000;
            sheet.Range["D5"].NumberValue = 9500;
            sheet.Range["D6"].NumberValue = 5000;
            sheet.Range["D7"].NumberValue = 3500;

            sheet.Range["E2"].NumberValue = 7000;
            sheet.Range["E3"].NumberValue = 9000;
            sheet.Range["E4"].NumberValue = 10000;
            sheet.Range["E5"].NumberValue = 9500;
            sheet.Range["E6"].NumberValue = 7000;
            sheet.Range["E7"].NumberValue = 8500;

            sheet.Range["F2"].NumberValue = 7000;
            sheet.Range["F3"].NumberValue = 9000;
            sheet.Range["F4"].NumberValue = 10000;
            sheet.Range["F5"].NumberValue = 9500;
            sheet.Range["F6"].NumberValue = 8000;
            sheet.Range["F7"].NumberValue = 8500;

            Chart chart = sheet.Charts.Add(ExcelChartType.Line);
            chart.DataRange = sheet.Range["A1:F7"];
            chart.SeriesDataFromRange = false;

            chart.LeftColumn = 1;
            chart.TopRow = 10;
            chart.RightColumn = 8;
            chart.BottomRow = 30;
            chart.ChartArea.Border.Weight = ChartLineWeightType.Medium;
            chart.ChartArea.Border.Color = Color.SandyBrown;

            chart.ChartTitle = "Chart";
            chart.PrimaryCategoryAxis.Title = "Parts";
            chart.PrimaryCategoryAxis.Font.Color = Color.Blue;
            chart.PrimaryValueAxis.Title = "Amounts";
            chart.PrimaryValueAxis.HasMajorGridLines = false;

            //Chart Legend
            chart.Legend.Position = LegendPositionType.Corner;
            //chart.Legend.X = 3400;   //I also verified that it is feasible to set only the X and Y values without setting the width and height of the legend
            //chart.Legend.Y = 150;

            workbook.SaveToFile("ExcelLineChart.xlsx", ExcelVersion.Version2010);


In order to help us further investigate so that we can provide you with the corresponding solution, please provide more detailed information, including your input file (if any) and your full test code (or a runnable project). Thanks in advance.

Sincerely,
Brian
E-iceblue support team
Attachments
ExcelLineChart.zip
(6.35 KiB) Downloaded 125 times
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Tue Jan 26, 2021 11:12 am

Thanks Brian.

Why is the legend positioned higher than the plotting area if I do it this way?

Code: Select all
      Workbook workbook = new Workbook();
      Worksheet sheet = workbook.Worksheets[0];

      Chart chart = sheet.Charts.Add();
      chart.SeriesDataFromRange = false;

      chart.ChartTitle = "BALANCE DE ENERGÍA";

      //Escribir los valores
      sheet.Range["A1"].Value = "A";
      sheet.Range["B1"].Value = "B";
      sheet.Range["C1"].Value = "C";
      sheet.Range["D1"].Value = "D";

      sheet.Range["A2"].NumberValue = 6000;
      sheet.Range["A3"].NumberValue = 8000;
      sheet.Range["A4"].NumberValue = 9000;
      sheet.Range["A5"].NumberValue = 8500;
      sheet.Range["A6"].NumberValue = 3000;
      sheet.Range["A7"].NumberValue = 5500;

      sheet.Range["B2"].NumberValue = 5000;
      sheet.Range["B3"].NumberValue = 7000;
      sheet.Range["B4"].NumberValue = 8000;
      sheet.Range["B5"].NumberValue = 7500;
      sheet.Range["B6"].NumberValue = 4000;
      sheet.Range["B7"].NumberValue = 4500;

      sheet.Range["C2"].NumberValue = 1000;
      sheet.Range["C3"].NumberValue = 5000;
      sheet.Range["C4"].NumberValue = 3000;
      sheet.Range["C5"].NumberValue = 7500;
      sheet.Range["C6"].NumberValue = 2000;
      sheet.Range["C7"].NumberValue = 3500;

      sheet.Range["D2"].NumberValue = 8000;
      sheet.Range["D3"].NumberValue = 7000;
      sheet.Range["D4"].NumberValue = 1500;
      sheet.Range["D5"].NumberValue = 2500;
      sheet.Range["D6"].NumberValue = 500;
      sheet.Range["D7"].NumberValue = 7500;

      ChartSerie cs = chart.Series.Add();
      cs.Name = "SERIE 1";
      cs.CategoryLabels = sheet.Range[2, 1, 7, 1];
      cs.Values = sheet.Range[2, 2, 7, 2];
      cs.SerieType = ExcelChartType.ScatterLine;

      ChartSerie cs2 = chart.Series.Add();
      cs2.Name = "SERIE 2";
      cs2.CategoryLabels = sheet.Range[2, 1, 7, 1];
      cs2.Values = sheet.Range[2, 3, 7, 3];
      cs2.SerieType = ExcelChartType.ScatterLine;

      ChartSerie cs3 = chart.Series.Add();
      cs3.Name = "SERIE 3";
      cs3.CategoryLabels = sheet.Range[2, 1, 7, 1];
      cs3.Values = sheet.Range[2, 4, 7, 4];
      cs3.SerieType = ExcelChartType.ScatterLine;

      //Unidades de los valores de cada eje
      chart.PrimaryCategoryAxis.Title = "HOLA";
      chart.PrimaryValueAxis.Title = "ADIOS";

      //Posicionar la gráfica
      chart.LeftColumn = 10;
      chart.TopRow = 10;
      chart.RightColumn = 26;
      chart.BottomRow = 50;

      chart.Legend.Position = LegendPositionType.Corner;

      workbook.SaveToFile("ExcelLineChart.xlsx", ExcelVersion.Version2016);
Attachments
ExcelLineChart.zip
(8 KiB) Downloaded 126 times

blastdoman
 
Posts: 27
Joined: Thu Jul 02, 2020 7:06 am

Tue Jan 26, 2021 11:49 am

One more thing. Testing your code I noticed that if you change the DataRange to A1:D7 it already works wrong and the plotting area drops below the legend. It only works fine if the DataRange is A1:E7 or A1:F7. All other combinations work wrong.

blastdoman
 
Posts: 27
Joined: Thu Jul 02, 2020 7:06 am

Tue Jan 26, 2021 3:19 pm

Testing with Excel I have seen that it also behaves in the same way. Sometimes it lowers the plotting area and sometimes it does not. I don't understand the reason but that's the way it is.

Sorry for the inconvenience.

blastdoman
 
Posts: 27
Joined: Thu Jul 02, 2020 7:06 am

Wed Jan 27, 2021 7:34 am

Hello,

Thanks for your feedback.
After many tests, I found that the relative position of the legend and the plotting area will be affected by the chart width and height. For the Excel file you provided, if you manually adjust the chart size to an appropriate value in Microsoft Excel, then you can make the legend and plot area almost at the same horizontal height, as shown below. You can verify this on your side.
screenshot.png
screenshot.png (56.69 KiB) Viewed 889 times

Therefore, you can try to adjust the chart position in the code. I have verified that when setting like the following code, and the position of the legend in the resulting file is exactly what you expect.
Code: Select all
            chart.LeftColumn = 10;
            chart.TopRow = 10;
            chart.RightColumn = 16;
            chart.BottomRow = 25;


Also, for the code I provided above, when changing the DataRange to A1:D7, you need to adjust the chart position as follows.

Code: Select all
             chart.LeftColumn = 1;
             chart.TopRow = 10;
             chart.RightColumn = 8;
             chart.BottomRow = 29;


Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Wed Jan 27, 2021 8:13 am

Hi Brian.

Finally, after some testing, I came to the conclusion that the position of the legend with the "Corner" option depended on the size of the chart and the size of the legend. Sometimes it positions the graphic below the legend and sometimes not. As I can't set a fixed size in advance, I will position the legend with the Right option.

Thank you very much.

blastdoman
 
Posts: 27
Joined: Thu Jul 02, 2020 7:06 am

Wed Jan 27, 2021 10:25 am

Okay, thanks for your response. Just feel free to contact us if you encounter any issues related to our products in the future.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Return to Spire.XLS