Hi,
I am unable to update positive and negative column colors in a waterfall chart created using Aspose 24.x using the below statements mentioned in sample code,
// Series has Up Down Bars
chart.getNSeries().get(0).setHasUpDownBars(true);
// Set the colors of Up and Down Bars
chart.getNSeries().get(0).getUpBars().getArea().setForegroundColor(Color.getGreen());
chart.getNSeries().get(0).getDownBars().getArea().setForegroundColor(Color.getRed());
Sample Code :
String millionPattern = “#0.#,“M””;
Style style = null;
Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(0);
workbook.getSettings().setNumberDecimalSeparator(',');
workbook.getSettings().setNumberGroupSeparator(' ');
System.out.println(workbook.getSettings().getNumberDecimalSeparator());
// Adding some sample value to cells
Cells cells = sheet.getCells();
Cell cell = cells.get("A1");
cell.setValue("ABC");
cell = cells.get("A2");
cell.setValue("DEF");
cell = cells.get("A3");
cell.setValue("GHI");
cell = cells.get("A4");
cell.setValue("PQR");
cell = cells.get("A5");
cell.setValue("XYZ");
cell = cells.get("A6");
cell.setValue("DFS");
cell = cells.get("B1");
cell.setValue(29872744.72);
style = cell.getStyle();
style.setCustom(millionPattern);
cell.setStyle(style);
cell = cells.get("B2");
cell.setValue(-10076585.23);
style = cell.getStyle();
style.setCustom(millionPattern);
cell.setStyle(style);
cell = cells.get("B3");
cell.setValue(9976585.91);
style = cell.getStyle();
style.setCustom(millionPattern);
cell.setStyle(style);
cell = cells.get("B4");
cell.setValue(-8876585.17);
style = cell.getStyle();
style.setCustom(millionPattern);
cell.setStyle(style);
cell = cells.get("B5");
cell.setValue(-19976580.02);
style = cell.getStyle();
style.setCustom(millionPattern);
cell.setStyle(style);
cell = cells.get("B6");
cell.setValue(-55976580.02);
style = cell.getStyle();
style.setCustom(millionPattern);
cell.setStyle(style);
ChartCollection charts = sheet.getCharts();
// Adding a chart to the worksheet
int chartIndex = charts.add(ChartType.WATERFALL, 5, 0, 15, 5);
Chart chart = charts.get(chartIndex);
chart.getNSeries().add("B2:B6", true);
chart.getNSeries().setCategoryData("A2:A6");
chart.getNSeries().get(0).setHasUpDownBars(true);
chart.getNSeries().get(0).getUpBars().getArea().setForegroundColor(Color.getGreen());
chart.getNSeries().get(0).getDownBars().getArea().setForegroundColor(Color.getRed());
// Saving the Excel file
workbook.save("WaterFallTest.xlsx");
This Topic is created by amjad.sahi using Email to Topic tool.
@vinaysrivatsacomm,
You may iterate through series data points to update/change the default column colors in Waterfall chart. Please try the following code snippet to change colors for positive and negative columns for your reference.
e.g.
Sample code:
for (int i = 0; i<chart.getNSeries().get(0).getPoints().getCount(); i++)
{
chart.getNSeries().get(0).getPoints().get(i).getArea().getFillFormat().setFillType(com.aspose.cells.FillType.SOLID);
chart.getNSeries().get(0).getPoints().get(i).getArea().setForegroundColor(com.aspose.cells.Color.getGreen());
if (i==1)
{
chart.getNSeries().get(0).getPoints().get(i).getArea().getFillFormat().setFillType(com.aspose.cells.FillType.SOLID);
chart.getNSeries().get(0).getPoints().get(i).getArea().setForegroundColor(com.aspose.cells.Color.getRed());
}
}
It seems like the feature (Set as Total) is not supported for Waterfall chart as I could not find relevant API or could not accomplish the task via Aspose.Cells for Java API. I have logged a ticket with an id “CELLSJAVA-45842” for it. We will be looking into it and get back to you soon with further updates (once available).
Hi,
This post is a different scenario, I am not able to add a text box to waterfall chart with the given sample code, can you please help, Attaching the sample code below,
public static void main(String[] args) throws Exception {
String millionPattern = “#0.#,"M"”;
Style style = null;
Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(0);
workbook.getSettings().setNumberDecimalSeparator(',');
workbook.getSettings().setNumberGroupSeparator(' ');
// Adding some sample value to cells
Cells cells = sheet.getCells();
Cell cell = cells.get("A1");
cell.setValue("ABC");
cell = cells.get("A2");
cell.setValue("DEF");
cell = cells.get("A3");
cell.setValue("GHI");
cell = cells.get("A4");
cell.setValue("PQR");
cell = cells.get("A5");
cell.setValue("XYZ");
cell = cells.get("A6");
cell.setValue("DFS");
cell = cells.get("B1");
cell.setValue(29872744.72);
style = cell.getStyle();
style.setCustom(millionPattern);
cell.setStyle(style);
cell = cells.get("B2");
cell.setValue(-10076585.23);
style = cell.getStyle();
style.setCustom(millionPattern);
cell.setStyle(style);
cell = cells.get("B3");
cell.setValue(9976585.91);
style = cell.getStyle();
style.setCustom(millionPattern);
cell.setStyle(style);
cell = cells.get("B4");
cell.setValue(-8876585.17);
style = cell.getStyle();
style.setCustom(millionPattern);
cell.setStyle(style);
cell = cells.get("B5");
cell.setValue(-19976580.02);
style = cell.getStyle();
style.setCustom(millionPattern);
cell.setStyle(style);
cell = cells.get("B6");
cell.setValue(-55976580.02);
style = cell.getStyle();
style.setCustom(millionPattern);
cell.setStyle(style);
ChartCollection charts = sheet.getCharts();
// Adding a chart to the worksheet
int chartIndex = charts.add(ChartType.WATERFALL, 5, 0, 15, 5);
Chart chart = charts.get(chartIndex);
chart.getNSeries().add("B2:B6", true);
chart.getNSeries().setCategoryData("A2:A6");
chart.getNSeries().get(0).setHasUpDownBars(true);
chart.getNSeries().get(0).getUpBars().getArea().setForegroundColor(Color.getGreen());
chart.getNSeries().get(0).getDownBars().getArea().setForegroundColor(Color.getRed());
TextBox txt = chart.getShapes().addTextBoxInChart(100, 100, 850, 2500);
txt.setText("Test Data");
txt.getFont().setItalic(true);
txt.getFont().setSize(20);
txt.getFont().setBold(true);
// Get the filformat of the textbox.
FillFormat fillformat = txt.getFill();
fillformat.setFillType(FillType.SOLID);
fillformat.getSolidFill().setColor(Color.getSilver());
// Get the lineformat type of the textbox.
LineFormat lineformat = txt.getLine();
lineformat.setWeight(2);
lineformat.setDashStyle(MsoLineDashStyle.SOLID);
// Saving the Excel file
workbook.save("WaterFallTest.xlsx");
// Print message
System.out.println("Workbook with chart is successfully created.");
}
@vinaysrivatsacomm,
I was able to reproduce the issue as you mentioned by using your sample code segment. I found I could not add TextBox or shape to Waterfall chart as in the output Excel file there was no textbox added on the chart. I also checked, it works fine though for other chart types (e.g., Line chart). It seems adding shapes to Waterfall chart is not currently supported.
We need to evaluate your issue/requirements in details. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSJAVA-45843
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
Hello Amjad,
We found out that if we add the highlighted code in chart1.xml or chartEx1.xml
cx:layoutPr
cx:subtotals
** <cx:idx val=“9”/>**
** </cx:subtotals>**
</cx:layoutPr>
will solve the total Issue, As this was impacting our deliverables, We wanted to know if this can be supported through Aspose Cells.
@vinaysrivatsacomm,
Thank you for sharing your findings and workaround to cope with it. Unfortunately, your issue (“CELLSJAVA-45842”) has not been resolved. We will evaluate it and get back to you soon with updates or an ETA on the issue/feature.
Thanks for your timely response, We would really appreciate if this request can be prioritized.
We Already have Aspose license for Aspose.cells and Aspose.slides java.
Please let us know if you need more details about the license.
@vinaysrivatsacomm,
I have already set the priority from ‘Normal’ to ‘High’ for the ticket. This is what we can do for your ticket at the moment. Please note that we cannot set priority to urgent/critical for the issues as this feasibility is only available for paid support clients.
Hopefully, your issue will be addressed soon. Once we have an update on any of the tickets, we will let you know.
@vinaysrivatsacomm
If you want to set total , please try the following codes:
chart.getNSeries().get(0).getLayoutProperties().setSubtotals ( new int[] { 4 });
It means that the 4th chart point is total.
@vinaysrivatsacomm
We can not insert a shape to the waterfall chart in MS Excel. It should be limitation of Excel that the advance charts since office 2016 do not support adding shapes.
If I inserted shapes to Waterfall chart with API, the generated file is corrupted.
Even I created a column chart with a shape, I could not change chart type in MS Excel.
If you insert a shape into waterfall chart in MS Excel, you will find the shape does not follow the chart when moving the chart.
So now you only can add a shape over the chart to sheet:
sheet.getShapes().addTextBox();
If you has any other clues， please inform us. We will check it soon.
@vinaysrivatsacomm
I’m glad to hear that your problem has been resolved.
If you have any other questions, please feel free to contact us.
Hello,
I am trying to set fonts for Axis labels in a waterfall cell, I have tried with the below code,
Style cellStyle = cell.getStyle();
Font font = cellStyle.getFont();
font.setName("Times New Roman");
cell.setStyle(style);
This doesnot reflect on the charts,Is there an other way to handle this?
@vinaysrivatsacomm,
You are trying to set font style for the cells and not for the chart objects. You need to use TickLabels of Axis to set/change font settings for your needs. See the following sample code segment for your reference.
e.g.
Sample code:
......
//Apply font settings to category axis (x-axis) tick labels
chart.getCategoryAxis().getTickLabels().getFont().setName("Times New Roman");
chart.getCategoryAxis().getTickLabels().getFont().setSize(12);
//Apply font settings to value axis (y-axis) ticklabels
chart.getValueAxis().getTickLabels().getFont().setName("Arial");
chart.getValueAxis().getTickLabels().getFont().setSize(10);
......
Let us know if you still find any issue or have other queries.