Line Chart Marker Symbol Problem

Aspose.Cells Product Family
1
Hi..anyone there? I am using Aspose.cells latest version in java..I am creating a line chart with marker symbols..but the marker symbols in the chart are not matching with marker symbols in the legend..please can u provide any help?
2

Hi Arpit,


Thank you for contacting Aspose support.

I am afraid, it is hard to detect the problem cause unless we review your source code and input spreadsheet (if any). We would request you to re-evaluate your case by using the latest version of Aspose.Cells for Java 8.2.2.3 on your end. In case the problem persists, please provide us your complete source code along with input & output spreadsheets for our review. Please also share a snapshot highlighting the issue for better elaboration. We will quickly analyze the situation on our end to guide you further in this regard.

3

Hi Babar,


I have attached a ppt. In that ppt you can see that the chart marker symbols are not matching with the legend symbols.

If you edit the excel you can see data and chart in separate sheets. Please help in getting same marker symbols in legend and chart. I am also using the latest version of Aspose.cells and Aspose.slides.

I am using the following code:
chart.getNSeries().get(i).getPoints().get(j).getMarker().setMarkerStyle(ChartMarkerType.CIRCLE);
chart.getNSeries().get(i).getPoints().get(j).getMarker().getArea().getFillFormat().setType(FillType.SOLID);
chart.getNSeries().get(i).getPoints().get(j).getMarker().getArea().getFillFormat().getSolidFill().
setColor(Color.fromArgb(java.awt.Color.decode(color.get(i)).getRed(),
java.awt.Color.decode(color.get(i)).getGreen(),
java.awt.Color.decode(color.get(i)).getBlue()));
4

Hi,

Thanks for your posting and using Aspose.Cells.

Kindly provide the Excel file which is inside the abc.ppt file and also provide the fixed Excel file which has been fixed by opening and editing in Microsoft Excel.

Please also provide the sample code which you are using to create this chart so that we could run your code and investigate this issue further.

5

Hi,


I am using the following code:

private int AddExcelChartInWorkbook(Workbook wb, int chartRows,
int chartCols, JSONObject json, String filePath,
List firstRow, List firstCol,
List<List> values, Integer legendX, Integer legendY,
Integer legendHeight, Integer legendWidth, String showMarketingCalendar) throws JSONException {

int dataSheetIndex = wb.getWorksheets().add();
Worksheet dataSheet = wb.getWorksheets().get(dataSheetIndex);
String sheetName = “DataSheet”;
dataSheet.setName(sheetName);

Cells cells = dataSheet.getCells();

for (int i = 0; i < firstRow.size(); i++) {
cells.get(0, i).setValue(firstRow.get(i));
}

firstCol.clear();
firstCol.add("");firstCol.add(“b”);firstCol.add(“c”);firstCol.add(“d”);firstCol.add(“e”);firstCol.add(“f”);
firstCol.add(“g”);
for (int i = 0; i < firstCol.size(); i++) {
cells.get(i, 0).setValue(firstCol.get(i));
}

List color = new ArrayList();
for (int i = 0; i < json.getJSONObject(“chart”).getJSONArray(“series”)
.length(); i++)
{
color.add(json.getJSONObject(“chart”).getJSONArray(“series”).getJSONObject(i).getString(“color”));
for (int j = 0; j < json.getJSONObject(“chart”).getJSONArray(“series”).getJSONObject(i)
.getJSONArray(“data”).length(); j++) {
cells.get(CellsHelper.cellIndexToName(i + 1, j + 1)).setValue(
values.get(i).get(j));
}
}

Double minx=0.0,maxx=0.0;
Long minx10=0l,maxx10=0l;
Integer count=1;
for(List innerList:values)
{
for(Integer dd:innerList)
{
if(count==1)
{
minx=(double)dd;
maxx=(double)dd;
}
if(dd<minx) minx=(double)dd;
if(dd>maxx) maxx=(double)dd;
count++;
}
}
minx10=RoundDown(Math.round(minx));
if(Math.round(minx)==minx10)
{
minx10-=10;
}
maxx10=RoundUp(Math.round(maxx));
if(minx10<0) minx10=0l;
maxx10=(100 - maxx10 % 100) + maxx10;
int WorksheetIndex = wb.getWorksheets().add(SheetType.CHART);
Worksheet chartSheet = wb.getWorksheets().get(WorksheetIndex);
chartSheet.setName(“ChartSheet”);
int chartSheetIdx = chartSheet.getIndex();

int chIndex = chartSheet.getCharts().add(
ChartType.LINE_WITH_DATA_MARKERS, 0, chartRows, 0, chartCols);
Chart chart = chartSheet.getCharts().get(chIndex);

chart.getPlotArea().getArea().setForegroundColor(Color.getWhite());

chart.getChartArea().getArea().setForegroundColor(Color.getWhite());

Axis vA = chart.getValueAxis();
vA.getMajorGridLines().setColor(Color.getLightGray());
chart.getValueAxis().setVisible(true);
chart.getValueAxis().getAxisLine().setColor(Color.getWhite());
chart.getValueAxis().setDisplayUnitLabelShown(true);
chart.getValueAxis().setDisplayUnit(DisplayUnitType.NONE);
chart.getValueAxis().setMinValue(minx10);
chart.getValueAxis().setMaxValue(maxx10);
chart.getValueAxis().getTickLabels().setNumberFormat(“0\%”);
chart.getCategoryAxis().setMajorTickMark(TickMarkType.NONE);
chart.getCategoryAxis().setMinorTickMark(TickMarkType.OUTSIDE);
chart.getValueAxis().getTickLabels().setAutoScaleFont(false);
chart.getCategoryAxis().getTickLabels().setAutoScaleFont(false);
chart.getSeriesAxis().getAxisLine().setColor(Color.getWhite());
chart.getCategoryAxis().getMajorGridLines().setVisible(false);
chart.getCategoryAxis().getMajorGridLines().setColor(Color.getWhite());
chart.getCategoryAxis().getMinorGridLines().setVisible(false);
chart.getCategoryAxis().getMinorGridLines().setColor(Color.getWhite());
chart.getSeriesAxis().getAxisLine().setVisible(false);
chart.getPlotArea().getArea().setForegroundColor(Color.getWhite());
chart.getChartArea().getArea().setForegroundColor(Color.getWhite());

chart.setShowLegend(true);
chart.setRightAngleAxes(false);
chart.getChartObject().getLineFormat().setVisible(false);
chart.getChartObject().getLineFormat().setTransparency(1.0);
chart.setRectangularCornered(false);

chart.getChartArea().getBorder().setVisible(false);
chart.getChartArea().getBorder().setColor(Color.getWhite());
chart.getChartArea().getBorder().setEndType(MsoArrowheadStyle.NONE);
chart.getChartArea().getBorder().setBeginType(MsoArrowheadStyle.NONE);
chart.getChartArea().getBorder().setJoinType(LineJoinType.NONE);

chart.getChartObject().getLineFormat().setForeColor(Color.getWhite());
chart.setWallsAndGridlines2D(false);
chart.getChartObject().getLineFormat().setBackColor(Color.getWhite());
chart.getChartObject().getLineFormat().setWeight(0.0);
chart.getChartArea().setBackgroundMode(BackgroundMode.TRANSPARENT);

chart.getPlotArea().getBorder().setVisible(false);
SeriesCollection nSeries = chart.getNSeries();

nSeries.add(sheetName+ “!B2:”+ CellsHelper.cellIndexToName(
json.getJSONObject(“chart”).getJSONArray(“series”).length(),
json.getJSONObject(“chart”).getJSONArray(“series”).getJSONObject(0).
getJSONArray(“data”).length())
, false);
chart.getNSeries().setCategoryData(
sheetName
+ “!B1:”
+ CellsHelper.cellIndexToName(
0,
json.getJSONObject(“chart”)
.getJSONArray(“series”)
.getJSONObject(0).getJSONArray(“data”)
.length()));
String paramType="";
paramType = json.getJSONObject(“across”).get(“name”).toString();
for (int i = 0; i < chart.getNSeries().getCount(); i++)
{
chart.getNSeries().get(i).getBorder().setWeightPt(2.25);
chart.getNSeries().get(i).getBorder().setStyle(LineType.SOLID);
chart.getNSeries().get(i).getBorder().setColor(
Color.fromArgb(java.awt.Color.decode(color.get(i)).getRed(),
java.awt.Color.decode(color.get(i)).getGreen(),
java.awt.Color.decode(color.get(i)).getBlue()));
for(int j=0;j<chart.getNSeries().get(i).getPoints().getCount();j++)
{
chart.getNSeries().get(i).getPoints().get(j).getMarker().setMarkerStyle(ChartMarkerType.CIRCLE);
chart.getNSeries().get(i).getPoints().get(j).getMarker().getArea().getFillFormat().setType(FillType.SOLID);
chart.getNSeries().get(i).getPoints().get(j).getMarker().getArea().getFillFormat().getSolidFill().
setColor(Color.fromArgb(java.awt.Color.decode(color.get(i)).getRed(),
java.awt.Color.decode(color.get(i)).getGreen(),
java.awt.Color.decode(color.get(i)).getBlue()));
chart.getNSeries().get(i).getPoints().get(j).getMarker().getBorder().setVisible(false);
}
chart.getNSeries().get(i).setName(firstCol.get(i+1));
}
//Set the Legend.
Legend legend = chart.getLegend();
legend.setAutoScaleFont(false);
legend.setAutomaticSize(true);
legend.getFont().setBold(true);
legend.getBorder().setColor(Color.getBlack());
legend.setPosition(LegendPositionType.BOTTOM);
wb.getWorksheets().setActiveSheetIndex(chartSheetIdx);
return chartSheetIdx;
}

I have also attached the excel inside abc.ppt.
In excel also I could not get the same marker symbols.
6

Hi Arpit,

Thanks for your posting and using Aspose.Cells.

Please use the Chart.getNSeries().get(i).getMarker() property to change the marker style of the entire series. It will then match your Series and Legend marker symbols fine.

Please see the following sample code. The code sets the marker style of all series to circle and red color. I have attached the source Excel file used in this code and the output Excel file generated by it and the screenshot showing the output of the code for your reference.

Java


String filePath = “F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(0);


Chart chart = worksheet.getCharts().get(0);


for (int i = 0; i < chart.getNSeries().getCount(); i++)

{

Marker marker = chart.getNSeries().get(i).getMarker();


marker.setMarkerStyle(ChartMarkerType.CIRCLE);

marker.getArea().getFillFormat().setType(FillType.SOLID);

marker.getArea().getFillFormat().getSolidFill().setColor(Color.getRed());

}



workbook.save(“output.xlsx”);

7

Thank you so much…Its working!

8

Hi Arpit,


Thank you for your feedback. It is good to know that you are up & running again. Please feel free to contact us back in case you need our further assistance with Aspose APIs.
9

Hi Shakeel,


In the above code if I use ChartMarkerType.AUTOMATIC

for (int i = 0; i < chart.getNSeries().getCount(); i++)

{

Marker marker = chart.getNSeries().get(i).getMarker();


marker.setMarkerStyle(ChartMarkerType.AUTOMATIC);

marker.getArea().getFillFormat().setType(FillType.SOLID);

marker.getArea().getFillFormat().getSolidFill().setColor(Color.getRed());

}

then I am getting different data markers but these markers are getting
repeated and their color is also changing from red to random colors.
I want all different data markers.
Can u please help.

Thanks,
Arpit.
10

Hi Arpit,


Thank you for contacting us.

Please note, the ChartMarkerType.AUTOMATIC will assign the random marker type for particular series, however the marker color isn’t associated with this field.

Regarding your original concern, you should try setting the marker style and the color for individual series. Please check the following piece of code and the resultant spreadsheet for your reference.

Java

Workbook workbook = new Workbook(“D:/temp/source.xlsx”);
Worksheet worksheet = workbook.getWorksheets().get(0);
Chart chart = worksheet.getCharts().get(0);

Marker marker = chart.getNSeries().get(0).getMarker();
marker.setMarkerStyle(ChartMarkerType.CIRCLE);
marker.getArea().getFillFormat().setType(FillType.SOLID);
marker.getArea().getFillFormat().getSolidFill().setColor(Color.getRed());

marker = chart.getNSeries().get(1).getMarker();
marker.setMarkerStyle(ChartMarkerType.SQUARE_STAR);
marker.getArea().getFillFormat().setType(FillType.SOLID);
marker.getArea().getFillFormat().getSolidFill().setColor(Color.getBlue());

marker = chart.getNSeries().get(2).getMarker();
marker.setMarkerStyle(ChartMarkerType.DIAMOND);
marker.getArea().getFillFormat().setType(FillType.SOLID);
marker.getArea().getFillFormat().getSolidFill().setColor(Color.getYellow());

marker = chart.getNSeries().get(3).getMarker();
marker.setMarkerStyle(ChartMarkerType.SQUARE_X);
marker.getArea().getFillFormat().setType(FillType.SOLID);
marker.getArea().getFillFormat().getSolidFill().setColor(Color.getGreen());

marker = chart.getNSeries().get(4).getMarker();
marker.setMarkerStyle(ChartMarkerType.SQUARE_PLUS);
marker.getArea().getFillFormat().setType(FillType.SOLID);
marker.getArea().getFillFormat().getSolidFill().setColor(Color.getMagenta());

marker = chart.getNSeries().get(5).getMarker();
marker.setMarkerStyle(ChartMarkerType.TRIANGLE);
marker.getArea().getFillFormat().setType(FillType.SOLID);
marker.getArea().getFillFormat().getSolidFill().setColor(Color.getViolet());

/
for (int i = 0; i < chart.getNSeries().getCount(); i++)
{
Marker marker = chart.getNSeries().get(i).getMarker();
// marker.setMarkerStyle(ChartMarkerType.CIRCLE);
marker.setMarkerStyle(ChartMarkerType.AUTOMATIC);
marker.getArea().getFillFormat().setType(FillType.SOLID);
marker.getArea().getFillFormat().getSolidFill().setColor(Color.getRed());
}
/

workbook.save(“D:/output.xlsx”);