I can able to represent Labels on slide’s in pptx by creating invisible bar in Java with aspose-slides whereas to do the same in Excel I couldn’t able to create invisible bar with aspose-cells. Please, have a look on attachments below.
Please help me with the steps to generate invisible bar with aspose-cells and any different approach to represent Labels on Chart.
pptx file with labels.
Invisible-Bars in ppt.png (73.5 KB)
Please create your desired chart in MS Excel manually, zip the Excel file and provide us here, we will check and help you on how to do it via Aspose.Cells APIs.
@Amjad_Sahi
Thanks for your quick reply.
Actually, I don’t have an excel file with labels on invisible bar. I just want to create an excel same as in ppt. Please find the ppt file attached
Please let me know the options available and provide an example.
ppt with labels.zip (31.2 KB)
I tried to mimic the same chart in MS Excel manually but could not do that. We need an Excel file containing your desired chart in it. So, kindly do the needful and provide the template Excel file containing your desired chart. We will check and help you soon.
See the following sample code to accomplish your task. I have used your template file for source data for the new chart I created. You may refer to the code segment and may add/update your desired code accordingly for your needs.
e.g.
Sample code:
//Instantiating a Workbook object
Workbook workbook = new Workbook("f:\\files\\stacked_vertical+label.xlsx");
//Obtaining the reference of the first worksheet
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(0);
ChartCollection charts = sheet.getCharts();
//Adding a chart to the worksheet
int chartIndex = charts.add(ChartType.COLUMN_STACKED,23,1,43,17);
Chart chart = charts.get(chartIndex);
//Adding NSeries (chart data source) to the chart ranging from "A1" cell to "B3"
SeriesCollection serieses = chart.getNSeries();
serieses.add("DataSheet0!C2:E5", true);
//set the range of category axis values
serieses.setCategoryData("DataSheet0!B2:B5");
//set names of the series
for ( int i = 0 ;i < serieses.getCount(); i ++ )
{
Series series = serieses.get(i);
series.setName(workbook.getWorksheets().get(1).getCells().get(0,i+2).getValue().toString());
}
// get the third series
Series series2 = serieses.get(2);
series2.setType(ChartType.COLUMN);
series2.getDataLabels().setShowValue(true);
series2.setPlotOnSecondAxis(true);
Integer value = 320;
short v = value.shortValue();
series2.setGapWidth(v);
Integer value1 = -100;
short v1 = value1.shortValue();
series2.setOverlap(v1);
series2.getArea().setFormatting(FormattingType.NONE);
chart.getSecondValueAxis().setVisible(true);
// Move the legend to the bottom
chart.getLegend().setPosition(LegendPositionType.BOTTOM);
workbook.save("f:\\files\\out1.xlsx");
Hope, this helps a bit.
I tried the sample code with the template. The labels are getting displayed in the new chart. But the are not aligned properly.
Attaching the output file for reference.output1.zip (12.7 KB)
Could you help me with the code changes to display labels on same line.
Please share your sample code (runnable) that you are using. Also, give us your desired chart (in some Excel file) that you are trying to generate, you may create your desired chart in MS Excel manually. We will check and help you through.
I’m using the same above snippet of code that was shared by you earlier.
Input file: stacked_vertical+label.zip (10.7 KB)
This file has the desired chart.
Sample Code for reference :
Workbook workbook = new Workbook("D:\\\\Test-Charts\\\\stacked_vertical+label.xlsx");
//Obtaining the reference of the first worksheet
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(0);
ChartCollection charts = sheet.getCharts();
//Adding a chart to the worksheet
int chartIndex = charts.add(ChartType.COLUMN_STACKED,23,1,43,17);
Chart chart = charts.get(chartIndex);
//Adding NSeries (chart data source) to the chart ranging from "A1" cell to "B3"
SeriesCollection serieses = chart.getNSeries();
serieses.add("DataSheet0!C2:E5", true);
//set the range of category axis values
serieses.setCategoryData("DataSheet0!B2:B5");
//set names of the series
for ( int i = 0 ;i < serieses.getCount(); i ++ )
{
Series series = serieses.get(i);
series.setName(workbook.getWorksheets().get(1).getCells().get(0,i+2).getValue().toString());
}
// get the third series
Series series2 = serieses.get(2);
series2.setType(ChartType.COLUMN);
series2.getDataLabels().setShowValue(true);
series2.setPlotOnSecondAxis(true);
Integer value = 320;
short v = value.shortValue();
series2.setGapWidth(v);
Integer value1 = -100;
short v1 = value1.shortValue();
series2.setOverlap(v1);
series2.getArea().setFormatting(FormattingType.NONE);
chart.getSecondValueAxis().setVisible(true);
// Move the legend to the bottom
chart.getLegend().setPosition(LegendPositionType.BOTTOM);
workbook.save("output2.xlsx");Please try to add the line to your code segment:
…
// get the third series
Series series2 = serieses.get(2);
series2.setType(ChartType.COLUMN);
series2.getDataLabels().setShowValue(true);
series2.getDataLabels().setPosition(LabelPositionType.INSIDE_BASE);
series2.setPlotOnSecondAxis(true);
Integer value = 320;
short v = value.shortValue();
series2.setGapWidth(v);
Integer value1 = -100;
short v1 = value1.shortValue();
series2.setOverlap(v1);
…
Hope, this helps a bit.
Thanks for your response.
This approach is working for both COLUMN_STACKED and BAR_STACKED charts.
Could u help with the changes to make it work for a COLUMN and BAR chart as well.
Could you please share your current sample code (runnable) and template file(s) to show the issue, we will check it soon.
I’m using the same above snippet of code that was shared earlier in this thread.
Input file: multibar+label.zip (11.6 KB)
This file has the desired chart.
See the following sample code for your reference. I used your new file’s data as source data for the Bar chart that I created in the first sheet.
e.g.
Sample code:
Workbook workbook = new Workbook("f:\\files\\multibar+label.xlsx");
//Obtaining the reference of the first worksheet
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(0);
ChartCollection charts = sheet.getCharts();
//Adding a chart to the worksheet
int chartIndex = charts.add(ChartType.BAR,23,1,43,17);
Chart chart = charts.get(chartIndex);
SeriesCollection serieses = chart.getNSeries();
serieses.add("DataSheet!B2:C8", true);
//set the range of category axis values
//serieses.setCategoryData("DataSheet!A2:A8");
//set names of the series
for ( int i = 0 ;i < serieses.getCount(); i ++ )
{
Series series = serieses.get(i);
series.setName(workbook.getWorksheets().get(1).getCells().get(0,i+1).getValue().toString());
}
// get the third series
Series series2 = serieses.get(1);
series2.setType(ChartType.BAR);
series2.getDataLabels().setShowValue(true);
series2.getDataLabels().setPosition(LabelPositionType.OUTSIDE_END);
Integer value = 35;
short v = value.shortValue();
series2.setGapWidth(v);
Integer value1 = 100;
short v1 = value1.shortValue();
series2.setOverlap(v1);
series2.getArea().setFormatting(FormattingType.NONE);
//chart.getSecondValueAxis().setVisible(true);
// Move the legend to the bottom
chart.getLegend().setPosition(LegendPositionType.BOTTOM);
workbook.save("f:\\files\\output2.xlsx");
Let us know if you still find any issue.
Thanks for the response.
I tried the sample code with the template. It is working for BAR and COLUMN charts.
The labels are getting displayed in the new chart. I need them to be in same line as displayed in this template file. multibar+label-new.zip (11.3 KB)
What changes do I need to do to achieve it?
I checked the chart in your newly attached template file. I have tried setting Data labels position, e.g. “Outside End”, “Inside End”, etc. in MS Excel manually but none of them place in the same line. How did you place the labels via MS Excel options? Did you move the labels manually via mouse to be set in the same line? I am afraid, if you have moved/placed the labels manually via mouse pointer or arrow keys, then it will be hard to place the labels via the APIs.
@SriG,
Please see the following sample code for your reference.
Sample code:
Workbook workbook = new Workbook("f:\\files\\multibar+label.xlsx");
//Obtaining the reference of the first worksheet
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(0);
ChartCollection charts = sheet.getCharts();
//Adding a chart to the worksheet
int chartIndex = charts.add(ChartType.BAR,23,1,43,17);
Chart chart = charts.get(chartIndex);
SeriesCollection serieses = chart.getNSeries();
serieses.add("DataSheet!B2:C8", true);
//set the range of category axis values
//serieses.setCategoryData("DataSheet!A2:A8");
//set names of the series
for ( int i = 0 ;i < serieses.getCount(); i ++ )
{
Series series = serieses.get(i);
series.setName(workbook.getWorksheets().get(1).getCells().get(0,i+1).getValue().toString());
}
// get the third series
Series series2 = serieses.get(1);
series2.setType(ChartType.BAR);
series2.getDataLabels().setShowValue(true);
series2.getDataLabels().setPosition(LabelPositionType.OUTSIDE_END);
Integer value = 35;
short v = value.shortValue();
series2.setGapWidth(v);
Integer value1 = 100;
short v1 = value1.shortValue();
series2.setOverlap(v1);
series2.getArea().setFormatting(FormattingType.NONE);
// Move the legend to the bottom
chart.getLegend().setPosition(LegendPositionType.BOTTOM);
chart.calculate();
double maxAxis= Double.parseDouble(String.valueOf(chart.getCategoryAxis().getMaxValue()));
for(int i = 0; i < series2.getPoints().getCount(); i++)
{
ChartPoint point = series2.getPoints().get(i);
double x = chart.getPlotArea().getWidth() * 0.9 ;
point.getDataLabels().setX((int)x);
}
workbook.save("f:\\files\\output2.xlsx");