We are plotting a pie chart and we need to apply custom format to each datapoint for example #,##0.0% and #,##0.000% like these. How can we achieve this format, could you provide a sample code?
To apply custom number formats to each data point’s data label in a pie chart using Aspose.Cells, you can utilize the DataLabels class. Each data point in the series can have its own format specified through the NumberFormat property.
Cause
You may want to display data labels in a specific format to enhance readability and presentation in your pie chart.
Solution
Here’s a sample code snippet demonstrating how to achieve this:
import com.aspose.cells.*;
public class PieChartDataLabels {
public static void main(String[] args) throws Exception {
// Create a new workbook
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
// Add sample data
worksheet.getCells().get("A1").putValue("Category");
worksheet.getCells().get("A2").putValue("A");
worksheet.getCells().get("A3").putValue("B");
worksheet.getCells().get("A4").putValue("C");
worksheet.getCells().get("B1").putValue("Value");
worksheet.getCells().get("B2").putValue(0.1);
worksheet.getCells().get("B3").putValue(0.2);
worksheet.getCells().get("B4").putValue(0.3);
// Add a pie chart
int chartIndex = worksheet.getCharts().add(ChartType.Pie, 5, 0, 15, 5);
Chart chart = worksheet.getCharts().get(chartIndex);
chart.getNSeries().add("B2:B4", true);
chart.getNSeries().setCategoryData("A2:A4");
// Access the DataLabels of the first series
DataLabels dataLabels = chart.getNSeries().get(0).getDataLabels();
dataLabels.setShowValue(true);
dataLabels.setNumberFormat("#,##0.0%"); // Set custom format for the first data point
// Set a different format for the second data point
chart.getNSeries().get(1).getDataLabels().setNumberFormat("#,##0.000%");
// Save the workbook
workbook.save("PieChartWithCustomDataLabels.xlsx");
}
}
Next Steps
You can adjust the NumberFormat property for each data label as needed to fit your specific formatting requirements. After implementing the code, run it to generate the Excel file with the pie chart and custom data labels.
If you have any further questions or need additional assistance, feel free to ask!
Please see the following lines of code (in particular) on how to apply number formatting to data point labels for your reference.
// Access the DataLabels of the first series
DataLabels dataLabels = chart.getNSeries().get(0).getDataLabels();
dataLabels.setShowValue(true);
dataLabels.setNumberFormat("#,##0.0%"); // Set your desired custom format
Here is complete runnable code snippet that works fine as I tested.
// Create a new workbook
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
// Add sample data
worksheet.getCells().get("A1").putValue("Category");
worksheet.getCells().get("A2").putValue("A");
worksheet.getCells().get("A3").putValue("B");
worksheet.getCells().get("A4").putValue("C");
worksheet.getCells().get("B1").putValue("Value");
worksheet.getCells().get("B2").putValue(12.134);
worksheet.getCells().get("B3").putValue(410.268);
worksheet.getCells().get("B4").putValue(1234.375);
// Add a pie chart
int chartIndex = worksheet.getCharts().add(ChartType.PIE, 5, 0, 20, 10);
Chart chart = worksheet.getCharts().get(chartIndex);
chart.getNSeries().add("B2:B4", true);
chart.getNSeries().setCategoryData("A2:A4");
// Access the DataLabels of the first series
DataLabels dataLabels = chart.getNSeries().get(0).getDataLabels();
dataLabels.setShowValue(true);
dataLabels.setNumberFormat("#,##0.0%"); // Set custom format for the first data point
// Save the workbook
workbook.save("d:\\files\\out1.xlsx");
Please find attached the output XLSX file containing the chart having data labels with your desired (custom) numbers formatting.
out1.zip (7.6 KB)
Hope, this helps a bit.
@amjad.sahi ,
Thanks for the reply.
I have shared the desired output file here. Could you please refer that and help me with the code snippet?
I have added three different format for the percentage values.
piechart.zip (9.1 KB)
Thanks for the sample Excel file containing the chart with your desired (numbers) formatting for each data point label. See the following sample code to accomplish your task for your reference.
// Create a new workbook
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
// Add sample data
worksheet.getCells().get("A1").putValue("Category");
worksheet.getCells().get("A2").putValue("A");
worksheet.getCells().get("A3").putValue("B");
worksheet.getCells().get("A4").putValue("C");
worksheet.getCells().get("B1").putValue("Value");
worksheet.getCells().get("B2").putValue(12.134);
worksheet.getCells().get("B3").putValue(410.268);
worksheet.getCells().get("B4").putValue(1234.375);
// Add a pie chart
int chartIndex = worksheet.getCharts().add(ChartType.PIE, 5, 0, 20, 10);
Chart chart = worksheet.getCharts().get(chartIndex);
chart.getNSeries().add("B2:B4", true);
chart.getNSeries().setCategoryData("A2:A4");
Series series = chart.getNSeries().get(0);
for (int num = 0; num < series.getPoints().getCount(); num++) {
switch (num) {
case 0:
DataLabels dataLabels = series.getPoints().get(num).getDataLabels();
dataLabels.setShowPercentage(true);
dataLabels.setNumberFormat("#,##0.0%");
break;
case 1:
dataLabels = series.getPoints().get(num).getDataLabels();
dataLabels.setShowPercentage(true);
dataLabels.setNumberFormat("#,##0.000%");
break;
case 2:
dataLabels = series.getPoints().get(num).getDataLabels();
dataLabels.setShowPercentage(true);
dataLabels.setNumberFormat("#,##0.00%");
break;
default:
dataLabels = series.getPoints().get(num).getDataLabels();
dataLabels.setShowPercentage(true);
dataLabels.setNumberFormat("#,##0.0%");
}
}
// Save the workbook
workbook.save("d:\\files\\out12.xlsx");
out12.zip (7.7 KB)
Hope, this helps a bit.
@VaradS
Please try the following codes to set formats to each data point’s datalabels:
// Access the DataLabels of the first series
DataLabels dataLabels = chart.getNSeries().get(0).getDataLabels();
dataLabels.setShowPercentage(true);
dataLabels.setNumberFormat("#,##0.0%"); // Set custom format for the first data point
DataLabels dataLabels1 = chart.getNSeries().get(0).getPoints().get(0).getDataLabels();
dataLabels1.setNumberFormat("#,##0.0%"); //
DataLabels dataLabels2 = chart.getNSeries().get(0).getPoints().get(1).getDataLabels();
dataLabels2.setNumberFormat("#,##0.000%"); //
DataLabels dataLabels3 = chart.getNSeries().get(0).getPoints().get(2).getDataLabels();
dataLabels3.setNumberFormat("#,##0.00%"); //