Set legend size to certain percentage of the chart size

Aspose.Cells Product Family
1

Hi Team,

I have a requirement to position the legend based on the chart size.

I have a sample bar chart and the snipper is as follows.

 // init workbook
    Workbook wb = new Workbook();
    WorksheetCollection sheets = wb.getWorksheets();
    // init sheets
    int i = sheets.add();
    Worksheet chartSheet = sheets.get(i);
    ChartCollection charts = chartSheet.getCharts();
    Worksheet dataSheet = sheets.get(0);
    Cells dataSheetCells = dataSheet.getCells();
 
    // init chart
    Chart chart = charts.get(charts.add(com.aspose.cells.ChartType.BAR_STACKED, 0, 0, 20, 10));
    SeriesCollection nSeries = chart.getNSeries();
 
    // write data
    dataSheetCells.get(0,1).setValue("Jan 22");
    dataSheetCells.get(0,2).setValue("Feb 22");
    dataSheetCells.get(1,0).setValue("Costco");
    dataSheetCells.get(1,1).setValue(5543);
    dataSheetCells.get(1,2).setValue(6543);
    dataSheetCells.get(2,0).setValue("Walmart");
    dataSheetCells.get(2,1).setValue(3456);
    dataSheetCells.get(2,2).setValue(5643);
    dataSheetCells.createRange(1,0,2,1).setName("Retailer");
    dataSheetCells.createRange(1,1,2,1).setName("Jan");
    dataSheetCells.createRange(1,2,2,1).setName("Feb");
    Series janSeries = nSeries.get(nSeries.add("Jan", true));
    janSeries.setName("Jan sales");
    Series febSeries = nSeries.get(nSeries.add("Feb", true));
    febSeries.setName("Feb Sales");
    nSeries.setCategoryData("Retailer");
     
 
    try {
      wb.save("C:\\Users\\StackedBarTestLegend.xlsx");
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
 
  }

This code generates the chart as below

image.png (33.8 KB)

But now I have to make the legend to occupy 40% of the chart. And this has to be dynamic based on the chart’s size, width and height.

So that legend and chart should look like below

image.png (29.7 KB)

Could you please help me with the code snippet which helps me adjust the legend size to 40% of the chart size?

Attaching the required excel here →
StackedBarTestLegend.zip (10.0 KB)

Thanks in advance.

  • Thilak Babu
2

@Thilakbabu

To dynamically adjust the legend size based on the chart size in Aspose.Cells, you can calculate the dimensions of the chart and set the legend’s position and size accordingly. Here’s a code snippet that demonstrates how to set the legend to occupy 40% of the chart’s width and height:

// Initialize workbook and worksheet
Workbook wb = new Workbook();
WorksheetCollection sheets = wb.getWorksheets();
int i = sheets.add();
Worksheet chartSheet = sheets.get(i);
ChartCollection charts = chartSheet.getCharts();
Worksheet dataSheet = sheets.get(0);
Cells dataSheetCells = dataSheet.getCells();

// Initialize chart
Chart chart = charts.get(charts.add(ChartType.BAR_STACKED, 0, 0, 20, 10));
SeriesCollection nSeries = chart.getNSeries();

// Write data
dataSheetCells.get(0, 1).setValue("Jan 22");
dataSheetCells.get(0, 2).setValue("Feb 22");
dataSheetCells.get(1, 0).setValue("Costco");
dataSheetCells.get(1, 1).setValue(5543);
dataSheetCells.get(1, 2).setValue(6543);
dataSheetCells.get(2, 0).setValue("Walmart");
dataSheetCells.get(2, 1).setValue(3456);
dataSheetCells.get(2, 2).setValue(5643);
dataSheetCells.createRange(1, 0, 2, 1).setName("Retailer");
dataSheetCells.createRange(1, 1, 2, 1).setName("Jan");
dataSheetCells.createRange(1, 2, 2, 1).setName("Feb");
nSeries.add("Jan", true).setName("Jan sales");
nSeries.add("Feb", true).setName("Feb Sales");
nSeries.setCategoryData("Retailer");

// Calculate chart dimensions
double chartWidth = chart.getChartArea().getWidth();
double chartHeight = chart.getChartArea().getHeight();

// Set legend size to 40% of chart size
Legend legend = chart.getLegend();
legend.setWidth(chartWidth * 0.4);
legend.setHeight(chartHeight * 0.4);

// Position the legend (e.g., bottom right)
legend.setX(chart.getChartArea().getX() + chartWidth * 0.6);
legend.setY(chart.getChartArea().getY() + chartHeight * 0.6);

try {
    wb.save("C:\\Users\\StackedBarTestLegend.xlsx");
} catch (Exception e) {
    e.printStackTrace();
}

In this code, we calculate the width and height of the chart and then set the legend’s size to 40% of those dimensions. The legend’s position is set to the bottom right of the chart area. You can adjust the percentages as needed to fit your design requirements.

This solution effectively addresses the need to dynamically size the legend based on the chart dimensions.

Next steps: Implement this code in your project and adjust the legend size and position as necessary to meet your design specifications. If you have any further questions or need additional assistance, feel free to ask!

3

After Adding the code snippet suggested by the bot, legends are not visible anymore

This is how excel got created

image.png (26.7 KB)

So please help with the proper code snippet.

4

@Thilakbabu,

Yes, the code snippet provided by the bot is not valid. We will devise a code snippet to accomplish your task. We will get back to you soon.

5

@Thilakbabu
Please refer to the following example code to implement the requirements. Additionally, since the width ratio is relative to the width of the entire chart, based on the image you provided, we can achieve better results when we set the width ratio to 60%. You can adjust the width percentage according to actual needs. Please refer to the attachment. StackedBarTestLegend_java.zip (9.4 KB)

// init workbook
Workbook wb = new Workbook();
WorksheetCollection sheets = wb.getWorksheets();
// init sheets
int i = sheets.add();
Worksheet chartSheet = sheets.get(i);
ChartCollection charts = chartSheet.getCharts();
Worksheet dataSheet = sheets.get(0);
Cells dataSheetCells = dataSheet.getCells();
 
// init chart
Chart chart = charts.get(charts.add(com.aspose.cells.ChartType.BAR_STACKED, 0, 0, 20, 10));
SeriesCollection nSeries = chart.getNSeries();
 
// write data
dataSheetCells.get(0,1).setValue("Jan 22");
dataSheetCells.get(0,2).setValue("Feb 22");
dataSheetCells.get(1,0).setValue("Costco");
dataSheetCells.get(1,1).setValue(5543);
dataSheetCells.get(1,2).setValue(6543);
dataSheetCells.get(2,0).setValue("Walmart");
dataSheetCells.get(2,1).setValue(3456);
dataSheetCells.get(2,2).setValue(5643);
dataSheetCells.createRange(1,0,2,1).setName("Retailer");
dataSheetCells.createRange(1,1,2,1).setName("Jan");
dataSheetCells.createRange(1,2,2,1).setName("Feb");
Series janSeries = nSeries.get(nSeries.add("Jan", true));
janSeries.setName("Jan sales");
Series febSeries = nSeries.get(nSeries.add("Feb", true));
febSeries.setName("Feb Sales");
nSeries.setCategoryData("Retailer");

chart.getPlotArea().setWidthRatioToChart(0.6);
chart.calculate();
int width = chart.getLegend().getWidthPixel() * 3;
chart.getLegend().setWidthPixel(width);	     

try {
  wb.save(filePath + "StackedBarTestLegend_java.xlsx");
} catch (Exception e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
}

Hope helps a bit.

6

Hi @Thilakbabu
Please try the following code snippet, add it before your save file.

    if (chart.getActualSize()[0] > 200)
    {
	    chart.getPlotArea().setWidthRatioToChart(0.6);
	    chart.getPlotArea().setHeightRatioToChart(0.8);
	    chart.getPlotArea().setXRatioToChart(0);
	    chart.getPlotArea().setYRatioToChart(0.1);
	    
	    chart.getLegend().setWidthRatioToChart(0.4);
	    chart.getLegend().setHeightRatioToChart(0.8);
	    chart.getLegend().setXRatioToChart(0.6);
	    chart.getLegend().setYRatioToChart(0.1);
    }

This is the file I generated, please take a look to see if it is what you need.
StackedBarTestLegend2.zip (8.5 KB)

7

Hi @leoluo

What Aspose cells version is being used here? I am using 24.6 and I am not able to see the setter chart.getPlotArea().setWidthRatioToChart()

Could you please provide equivalent method for 24.6 version ?

Also, Please explain what this if (chart.getActualSize()[0] > 200) means?

Thanks,
Thilak Babu

8

@Thilakbabu
These methods are supported starting from v25.3. If you need to use these methods, we strongly recommend updating to the latest version v25.8. Please refer to the following document.

In addition, Aspose.Cells will not add methods or fix issues in the old version. All of our updates and fixes are based on the latest version. Sorry for any inconvenience caused to you.

9

@Thilakbabu

This condition is used to determine whether the width of the chart is greater than 200px. This is not necessary. You can add corresponding conditional statements according to your needs. Please refer to the API documentation.

10

Hi @Thilakbabu
This line of code is to satisfy a possible need you might have:

 And this has to be dynamic based on the chart’s size, width and height.

“chart.getActualSize()” gets the chart’s length and width.