Hi! Aspose team,
I working on new requirement to add quadrant support for the scatter chart.
I have created in manually in excel and added. Please would you help me like, how we can achieve it using aspose cells for java.
thanks!
Rohan Wankar
Could you please create your chart in MS Excel manually, save the Excel file (containing the chart) and provide us. We will check on how to do it via Aspose.Cells APIs.
Hi! Amjad
I have added manually created excel. I want to add the text fields inside PlotArea of the chart
Please could you help me how we can achieve it through aspose cells for javachart.zip (12.4 KB)
thanks!
Rohan
Thanks for the sample file.
I found you have manually added controls (Textboxes) to the chart. See the document on how to add controls to the chart for your reference.
Hi! Amjad,
Can we add the text area inside plot area of the chart?
I want to add it inside the plot area along with quadrant,
I have completed with quadrant, but want to add the text field for each quadrant on the corner of the PlotArea.
Is it feasible to add text area inside plot area.
thanks!
Rohan
In your Excel file, you have manually added 4 text boxes (with text/label) to the chart (type: Scatter with Markers). Please find some to check and try the examples in the suggested document first then write your own code for your task accordingly. If you still could not accomplish the task, kindly share your sample code (runnable), we will check it soon.
Hi! Amjad,
I have added the sample code to add text box to the PlotArea of the chart, but it is coming inside the chartArea, I want to insert the text box inside the PlotArea of the chart in a generic way based on the plotArea of the chart.
I have added the snippet code for the same.
Please take a look.
thanks!
Rohan
public class HowToCreateBubbleChart {
public static void main(String[] args) throws Exception {
// ExStart:1
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(HowToCreateBubbleChart.class) + "Charts/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of the first worksheet
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(0);
// Adding some sample value to cells
Cells cells = sheet.getCells();
Cell cell = cells.get("A1");
cell.setValue(50);
cell = cells.get("A2");
cell.setValue(100);
cell = cells.get("A3");
cell.setValue(150);
cell = cells.get("B1");
cell.setValue(4);
cell = cells.get("B2");
cell.setValue(20);
cell = cells.get("B3");
cell.setValue(180);
cell = cells.get("C1");
cell.setValue(320);
cell = cells.get("C2");
cell.setValue(110);
cell = cells.get("C3");
cell.setValue(180);
cell = cells.get("D1");
cell.setValue(40);
cell = cells.get("D2");
cell.setValue(120);
cell = cells.get("D3");
cell.setValue(250);
ChartCollection charts = sheet.getCharts();
// Adding a chart to the worksheet
int chartIndex = charts.add(ChartType.BUBBLE, 5, 0, 15, 5);
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("A1:B3", true);
// Set bubble sizes
chart.getNSeries().get(0).setBubbleSizes("B2:D2");
chart.getNSeries().get(0).setXValues("B3:D3");
chart.getNSeries().get(0).setValues("B1:D1");
chart.getPlotArea().setY((int) (chart.getPlotArea().getY() + 0.02));
final float x = !(String.valueOf(chart.getPlotArea().getX()).equals("NaN"))
? chart.getChartObject().getX() * chart.getPlotArea().getX()
: chart.getChartObject().getX();
final float y = !(String.valueOf(chart.getPlotArea().getY()).equals("NaN"))
? chart.getChartObject().getY() * chart.getPlotArea().getY()
: chart.getChartObject().getY();
final float width = !(String.valueOf(chart.getPlotArea().getWidth()).equals("NaN"))
? chart.getChartObject().getWidth() * chart.getPlotArea().getWidth()
: chart.getChartObject().getWidth();
final float height = !(String.valueOf(chart.getPlotArea().getHeight()).equals("NaN"))
? chart.getChartObject().getHeight() * chart.getPlotArea().getHeight()
: chart.getChartObject().getHeight();
final CardPosition plotArea = new CardPosition(x, y, width, height);
TextBox txt = chart.getShapes().addTextBoxInChart(0, 0, 104, 1000);
txt.setText("Quadrant");
txt.getFont().setItalic(true);
txt.getFont().setSize(8);
txt.getFont().setBold(true);
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);
// Saving the Excel file
workbook.save(dataDir + "HToCrBChart_out1.xls");
// ExEnd:1
// Print message
System.out.println("Bubble chart is successfully created.");
}
}
Please note, the unit for width and height of plot area is 1/4000 of chart area. So, you should place the textboxes (taking its height and width in account) with respect to plot area’s x, y and its height/width. See the following sample lines of code that inserts the sample text box inside plot area for your reference and write your logic to insert your custom text boxes around your desired position accordingly:
TextBox txt = chart.getShapes().addTextBoxInChart(chart.getPlotArea().getY()+450,chart.getPlotArea().getX()+585, 300, 650);
txt.setText("Quadrant");
txt.getFont().setItalic(true);
txt.getFont().setSize(8);
txt.getFont().setBold(true);
FillFormat fillformat = txt.getFill();
fillformat.setFillType(FillType.NONE);
// Get the lineformat type of the textbox.
LineFormat lineformat = txt.getLine();
lineformat.setWeight(1);
Hi! Amjad
Could you elaborate this quote?
Basically top/left attributes represent an offset from the upper left corner in units of 1/4000 of the chart area, see API Reference. Please refer to the thread with examples and details for your complete reference.
Hi! Amjad
thanks for the reply, great help! I am able to achieve the textBox inside the plotArea of the chart.
I have one more query, Can we add the color to the bubble based on the quadrant.
I want different colors for the bubble in each quadrant.
Is it achievable?
Please let me know.
thanks!
Rohan
Could you please zip and attach your desired Excel file containing colored bubble based on quadrant color, you may create/update the file in MS Excel manually. We will check it soon.
Hi! Amjad,
I have attached manually created excel.
Please take a look.
thanks!
RohanDifferentBubbleColorForEachQuadrantChart.zip (16.5 KB)
Thanks for the sample file.
You may use Area.setForegroundColor() method to specify your desired color for the particular series of the quadrant.
e.g.,
chart.getNSeries().get(i).getArea().setForegroundColor(com.aspose.cells.Color.getBlue());
See the following sample code segment which changes your existing bubble points color of each series. Refer to the code segment and write your own code accordingly for your needs:
e.g.
Sample code:
...
Chart chart = sheet.getCharts().get(0);
//apply different color to different series points
for(int i = 0; i<chart.getNSeries().getCount();i++)
{
switch (i)
{
case 0://Violet
chart.getNSeries().get(i).getArea().setForegroundColor(com.aspose.cells.Color.getViolet());
break;
case 1://Indigo
chart.getNSeries().get(i).getArea().setForegroundColor(com.aspose.cells.Color.getIndigo());
break;
case 2://Blue
chart.getNSeries().get(i).getArea().setForegroundColor(com.aspose.cells.Color.getBlue());
break;
case 3://Green
chart.getNSeries().get(i).getArea().setForegroundColor(com.aspose.cells.Color.getGreen());
break;
}
........
}
Hope, this helps a bit.
Hi! Amjad
thanks for the reply, but how can we get series based on quadrant & apply color for the bubble.
Because we are creating Series for each bubble.
So I want to apply the color for the bubble based on quadrant.
Please let me know if you need further inputs for the same.
thanks!
You may get the source range for each series using the line of code:
String sourceRange = chart.getNSeries().get(i).getValues();//e.g., =Tabelle1!$F$2:$F$15 as source range (for first series)
The above line will give you source cells range. But since you have used tables/list objects (with specified (table) style), so we might not get shading color of the cells in general way. If you could have set background color for the relevant cell(s) then we can easily pick the shading color for the cell and then specify the same color to series points (shading) color. Alternatively, you may set value for some cell above each table with relevant color name (“Blue”, “Red”, “Green”, etc.) in the template file. Now you may easily evaluate color names and match with relevant constants in code to be applied to each series (area) color (for bubbles) accordingly.