Rectangular arrows on chart are not getting removed

Aspose.Cells Product Family
1

Hi,


I am using Aspose.cells to create a bar chart. Everything is fine except that I am not able to remove black arrow heads which surround the four corners of the chart.

I have attached the ppt and excel for it. It is not coming in excel. It is coming only in ppt.
I am using the following code:

JSONObject yaxis = json.getJSONObject(“sampleSizeObject”)
.getJSONObject(“samplesize”);
int len = yaxis.length();

List name1 = new ArrayList();
List color = new ArrayList();
List val = new ArrayList();

for (int i = len - 1; i >= 0; i–) {
JSONObject j = json.getJSONObject(“chart”).getJSONArray(“series”)
.getJSONObject(i);
name1.add(j.getString(“name”));
color.add(j.getJSONArray(“data”).getJSONObject(0).getString(“color”));
val.add(j.getJSONArray(“data”).getJSONObject(0).getInt(“y”));
}

String[] cellsName = new String[2 * len];
int k = 0;
for (int i = 0; i < len; i++) {
for (int j = 0; j < 2; j++) {
String cellname = CellsHelper.cellIndexToName(i, j);
cellsName[k] = cellname;
k++;
}
}

String[] name = new String[len];
for (int i = 0; i < len; i++) {
name[i] = name1.get(i);
}

int[] cellsValue = new int[len];
for (int i = 0; i < len; i++) {
cellsValue[i] = val.get(i);
}

// Add a new worksheet to populate cells with data
int dataSheetIndex = wb.getWorksheets().add();
Worksheet dataSheet = wb.getWorksheets().get(dataSheetIndex);
String sheetName = dataSheetName;
dataSheet.setName(sheetName);
int k1 = 0;
int l = 0;
int j = 0;
for (int i = 0; i < len * 2; i++) {
Cells cells = dataSheet.getCells();

String cellName = cellsName[k1];

if (i % 2 == 0) {
String cV = name[l];
cells.get(cellName).setValue(cV);
// cells.get(cellName).setHtmlString("
"+cV+"
");
l++;

}

else {
int cellValue = cellsValue[j];
cells.get(cellName).setValue(cellValue);
j++;
}

k1++;
}

int WorksheetIndex = wb.getWorksheets().add(SheetType.CHART);
Worksheet chartSheet = wb.getWorksheets().get(WorksheetIndex);
chartSheet.setName(chartSheetName);
int chartSheetIdx = chartSheet.getIndex();

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

Axis vA = chart.getValueAxis();
Line m = vA.getMajorGridLines();
m.setVisible(false);
chart.getValueAxis().setVisible(false);
vA.getMinorGridLines().setVisible(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.getValueAxis().getTickLabels().setAutoScaleFont(false);
chart.getCategoryAxis().getTickLabels().setAutoScaleFont(false);
chart.getCategoryAxis().getTickLabels().getFont().setNormalizeHeights(true);
chart.getCategoryAxis().getTickLabels().getFont().setSize(10);
chart.getCategoryAxis().getTickLabels().getFont().setName(“Arial”);
chart.getCategoryAxis().getTickLabels().setRotationAngle(0);
chart.setShowLegend(false);
chart.setRightAngleAxes(false);
chart.getChartObject().getLineFormat().setVisible(false);
chart.getChartObject().getLineFormat().setTransparency(1.0);
chart.setRectangularCornered(false); //I am using this property but it is not working.
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().setBackground(BackgroundMode.TRANSPARENT);
chart.getPlotArea().getBorder().setVisible(false);
SeriesCollection nSeries = chart.getNSeries();
nSeries.add(sheetName + “!B1:B” + len, true);
String paramType = json.getJSONObject(“across”).get(“name”).toString();
if (paramType.equals(BrandTrackerConstant.BRANDS) || paramType
.equals(BrandTrackerConstant.CRYSTAL_SEGMENTS)) {
nSeries.setCategoryData(sheetName + “!C1:C” + len);
}
else
{
nSeries.setCategoryData(sheetName + “!A1:A” + len);
}
for (int i = 0; i < len; i++) {
chart.getNSeries()
.get(0)
.getPoints()
.get(i)
.getArea()
.setForegroundColor(
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())); // getArea().setForegroundColor();
chart.getNSeries().get(0).getPoints().get(i).getBorder().setVisible(false);
}

wb.getWorksheets().setActiveSheetIndex(chartSheetIdx);

DataLabels datalabels;

for (int i = 0; i < chart.getNSeries().getCount(); i++) {
datalabels = chart.getNSeries().get(i).getDataLabels();
// Set the position of DataLabels
datalabels.setPosition(LabelPositionType.OUTSIDE_END);
// Show the category name in the DataLabels
datalabels.setCategoryNameShown(false);
// Show the value in the DataLabels
datalabels.setValueShown(true);
// Not show the percentage in the DataLabels
datalabels.setShowPercentage(false);
// Not show the legend key.
datalabels.setLegendKeyShown(false);
datalabels.getFont().setSize(10);
datalabels.getFont().setName(“Arial”);
datalabels.setAutoScaleFont(false);
datalabels.setNumberFormat(“0\%”);

}

Please help in this regard.
2

Hi Arpit,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest version: Aspose.Cells for Java 8.3.0 and see if it makes any difference at your end.

Basically, you are adding your Excel file as an OLE object and therefore you need the image of the Chart. If we convert your Chart into image using the latest version, no rectangle appears inside the image. These rectangles are not inside the chart image but they are outside the chart image. It appears to be the problem of Aspose.Slides.

I have attached the output chart image generated with the following code for your reference.

Java


String filePath = “F:\Shak-Data-RW\Downloads\Worksheet+in+barChart.xls”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(“ChartSheet”);


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


chart.toImage(“output.png”, ImageFormat.getPng());


Please isolate this problem and verify if it is Aspose.Cells or Aspose.Slides problem. You can create your Excel file manually using Microsoft Excel and then insert it inside PPT using Aspose.Slides to find out if it is an issue of Aspose.Slides or not.
3

Hi Shakeel,


Thanks for the reply. I am using latest version of aspose.cells at my end but still I am getting the same issue.

I am also using chart.toImage as follows but still not able to resolve the issue:
ByteArrayOutputStream imageStream = new ByteArrayOutputStream();
Presentationpres = new P resentation(template);
ISlide sld = pres.getSlides().get_Item(0);
Workbook wb = new Workbook();
wb.getWorksheets().get(chartSheetIndex).getCharts().get(0)
.toImage(imageStream, new ImageOrPrintOptions());
ByteArrayOutputStream bout = new ByteArrayOutputStream();
wb.save(bout, com.aspose.cells.SaveFormat.EXCEL_97_TO_2003);

and then I am Adding Excel Chart In Presentation
protected void AddExcelChartInPresentation(Presentation pres, ISlide sld,
byte[] wbArray, byte[] imgChart, Integer chartX, Integer chartY,
Integer chartWidth, Integer chartHeight) throws Exception
{
double oleHeight = pres.getSlideSize().getSize().getHeight();
// double oleWidth = pres.getSlideSize().getSize().getWidth();
Workbook wb = new Workbook();

wb.open(new ByteArrayInputStream(wbArray),
com.aspose.cells.SaveFormat.EXCEL_97_TO_2003);

IOleObjectFrame oof = sld.getShapes().addOleObjectFrame(chartX, chartY,
chartWidth, chartHeight, “Excel.Sheet.8”, wbArray);
oof.getLineFormat().getFillFormat().setFillType(FillType.NoFill);
oof.getLineFormat().setStyle(LineStyle.ThinThin);
oof.getLineFormat().setWidth(0);
oof.getLineFormat().getFillFormat().getSolidFillColor()
.setColor(java.awt.Color.white);
oof.getSubstitutePictureFormat()
.getPicture()
.setImage(
pres.getImages().addImage(
new ByteArrayInputStream(imgChart)));
}

Kindly help in this regard.
4

Hi Arpit,

Thanks for your posting and using Aspose.Cells and Aspose.Slides.

Please try to isolate this problem.

First save the image on disk and find how it looks like.

byte[] imgChart

inside this method

protected void AddExcelChartInPresentation(Presentation pres, ISlide sld,
byte[] wbArray, byte[] imgChart, Integer chartX, Integer chartY,
Integer chartWidth, Integer chartHeight) throws Exception

when saved on disk it should look like this output.png

That is, it should be without any rectangular corners.

Now insert it inside your PowerPoint with Aspose.Slides and rectangular corners should appear. In that case, it means it is the issue of Aspose.Slides not Aspose.Cells.

These rectangular corners are not inside the image, because when you save image on disk, you don’t see them. So it means, they only appear inside the OLE Object Frame not inside the chart image.

Besides, you can insert any other image too and see if rectangular corners appear in your OLE Object Frame or not.

5

Hi,


From our side we think that this is a problem of Aspose.Slides.

When we copy the excel chart generated by aspose cells directly on ppt , no corners appear.
But when we do the same by Aspose.Slides Java , these corners appear.

Kindly help in this regard.

Thanks.
6

Hi Arpit,


I have observed the issue concerning to Aspose.Slides end. I request you to please share a working sample code for generating Aspose.Cells chart and embedding that as Ole frame in PowerPoint presentation using Aspose.Slides. The sample that has been shared unfortunately cannot be used as its getting chart data using JSON string from some source. Please share a working sample code with hard coded data that is reproducing issue on your end. We will try our best to help you further in this regard after investigation.

Secondly, I also like to share that Aspose.Slides also provide the MSO chart support and you can add similar chart using Aspose.Slides as well. Please visit this documentation section for your kind reference in this regard.

Many Thanks,

7

Hi,


This is the code modified as asked.


int len = 6;

List name1 = new ArrayList();
List color = new ArrayList();
List val = new ArrayList();

for (int i = len - 1; i >= 0; i–) {
name1.add(“aa”);
val.add(40);
}

String[] cellsName = new String[2 * len];
int k = 0;
for (int i = 0; i < len; i++) {
for (int j = 0; j < 2; j++) {
String cellname = CellsHelper.cellIndexToName(i, j);
cellsName[k] = cellname;
k++;
}
}

String[] name = new String[len];
for (int i = 0; i < len; i++) {
name[i] = name1.get(i);
}

int[] cellsValue = new int[len];
for (int i = 0; i < len; i++) {
cellsValue[i] = val.get(i);
}

// Add a new worksheet to populate cells with data
int dataSheetIndex = wb.getWorksheets().add();
Worksheet dataSheet = wb.getWorksheets().get(dataSheetIndex);
String sheetName = dataSheetName;
dataSheet.setName(sheetName);
int k1 = 0;
int l = 0;
int j = 0;
for (int i = 0; i < len * 2; i++) {
Cells cells = dataSheet.getCells();

String cellName = cellsName[k1];

if (i % 2 == 0) {
String cV = name[l];
cells.get(cellName).setValue(cV);
// cells.get(cellName).setHtmlString("
"+cV+"
");
l++;

}

else {
int cellValue = cellsValue[j];
cells.get(cellName).setValue(cellValue);
j++;
}

k1++;
}

int WorksheetIndex = wb.getWorksheets().add(SheetType.CHART);
Worksheet chartSheet = wb.getWorksheets().get(WorksheetIndex);
chartSheet.setName(chartSheetName);
int chartSheetIdx = chartSheet.getIndex();

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

Axis vA = chart.getValueAxis();
Line m = vA.getMajorGridLines();
m.setVisible(false);
chart.getValueAxis().setVisible(false);
vA.getMinorGridLines().setVisible(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.getValueAxis().getTickLabels().setAutoScaleFont(false);
chart.getCategoryAxis().getTickLabels().setAutoScaleFont(false);
chart.getCategoryAxis().getTickLabels().getFont().setNormalizeHeights(true);
chart.getCategoryAxis().getTickLabels().getFont().setSize(10);
chart.getCategoryAxis().getTickLabels().getFont().setName(“Arial”);
chart.getCategoryAxis().getTickLabels().setRotationAngle(0);
chart.setShowLegend(false);
chart.setRightAngleAxes(false);
chart.getChartObject().getLineFormat().setVisible(false);
chart.getChartObject().getLineFormat().setTransparency(1.0);
chart.setRectangularCornered(false); //I am using this property but it is not working.
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().setBackground(BackgroundMode.TRANSPARENT);
chart.getPlotArea().getBorder().setVisible(false);
SeriesCollection nSeries = chart.getNSeries();
nSeries.add(sheetName + “!B1:B” + len, true);
nSeries.setCategoryData(sheetName + “!A1:A” + len);

for (int i = 0; i < len; i++) {
chart.getNSeries()
.get(0)
.getPoints()
.get(i)
.getArea()
.setForegroundColor(
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())); // getArea().setForegroundColor();
chart.getNSeries().get(0).getPoints().get(i).getBorder().setVisible(false);
}

wb.getWorksheets().setActiveSheetIndex(chartSheetIdx);

DataLabels datalabels;

for (int i = 0; i < chart.getNSeries().getCount(); i++) {
datalabels = chart.getNSeries().get(i).getDataLabels();
// Set the position of DataLabels
datalabels.setPosition(LabelPositionType.OUTSIDE_END);
// Show the category name in the DataLabels
datalabels.setCategoryNameShown(false);
// Show the value in the DataLabels
datalabels.setValueShown(true);
// Not show the percentage in the DataLabels
datalabels.setShowPercentage(false);
// Not show the legend key.
datalabels.setLegendKeyShown(false);
datalabels.getFont().setSize(10);
datalabels.getFont().setName(“Arial”);
datalabels.setAutoScaleFont(false);
datalabels.setNumberFormat(“0\%”);

}


Please note that rectangular corners are still coming.
Kindly help
Thanks
8

Hi Arpit,

Thanks for your sample code and using Aspose APIs.

After executing your sample code, we found that it is the issue of Aspose.Cells because it is generating the image of the chart with rectangular corners. We will look into it further and log it in our database so that this issue could be fixed at the earliest.

9

Hi Arpit,

Thanks for using Aspose.Cells.

We have investigated this issue further and found the culprit line in your code. Please see this code snippet.

chart.setRectangularCornered(false); //I am using this property but it is not working.
Please comment this line, because you have specified false as parameter, it means you are asking Aspose.Cells to render round corners of the chart. You should specify it true, so that Aspose.Cells should render right rectangular corners.

So the correct thing is, either you should comment this line or use this code.

chart.setRectangularCornered(true);

Please see the full runnable code here. Please focus on the bold green part of the code. I have also attached the output Excel file and the generated chart image for your reference.

Java

int len = 6;


List name1 = new ArrayList();

List color = new ArrayList();

List val = new ArrayList();


for (int i = len - 1; i >= 0; i–) {

name1.add(“aa”);

val.add(40);

}


String[] cellsName = new String[2 * len];

int k = 0;

for (int i = 0; i < len; i++) {

for (int j = 0; j < 2; j++) {

String cellname = CellsHelper.cellIndexToName(i, j);

cellsName[k] = cellname;

k++;

}

}


String[] name = new String[len];

for (int i = 0; i < len; i++) {

name[i] = name1.get(i);

}


int[] cellsValue = new int[len];

for (int i = 0; i < len; i++) {

cellsValue[i] = val.get(i);

}


Workbook wb = new Workbook(FileFormatType.XLSX);


// Add a new worksheet to populate cells with data

int dataSheetIndex = wb.getWorksheets().add();

Worksheet dataSheet = wb.getWorksheets().get(dataSheetIndex);

String sheetName = “DataSheet”;

dataSheet.setName(sheetName);


int k1 = 0;

int l = 0;

int j = 0;

for (int i = 0; i < len * 2; i++) {

Cells cells = dataSheet.getCells();


String cellName = cellsName[k1];


if (i % 2 == 0) {

String cV = name[l];

cells.get(cellName).setValue(cV);

// cells.get(cellName).setHtmlString("
"+cV+"
");

l++;


}


else {

int cellValue = cellsValue[j];

cells.get(cellName).setValue(cellValue);

j++;

}


k1++;

}


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.BAR, 0, 35, 0, 15);

Chart chart = chartSheet.getCharts().get(chIndex);


Axis vA = chart.getValueAxis();

Line m = vA.getMajorGridLines();

m.setVisible(false);

chart.getValueAxis().setVisible(false);

vA.getMinorGridLines().setVisible(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.getValueAxis().getTickLabels().setAutoScaleFont(false);

chart.getCategoryAxis().getTickLabels().setAutoScaleFont(false);

chart.getCategoryAxis().getTickLabels().getFont()

.setNormalizeHeights(true);

chart.getCategoryAxis().getTickLabels().getFont().setSize(10);

chart.getCategoryAxis().getTickLabels().getFont().setName(“Arial”);

chart.getCategoryAxis().getTickLabels().setRotationAngle(0);

chart.setShowLegend(false);

chart.setRightAngleAxes(false);

chart.getChartObject().getLineFormat().setVisible(false);

chart.getChartObject().getLineFormat().setTransparency(1.0);


//Comment this line, because you have specified false, it means

//you are asking Aspose.Cells to render round corners

//You should specify it true, so that Aspose.Cells should render right rectangular corner.


// chart.setRectangularCornered(false); // I am using this property but it

// is not working.

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().setBackground(BackgroundMode.TRANSPARENT);

chart.getPlotArea().getBorder().setVisible(false);

SeriesCollection nSeries = chart.getNSeries();

nSeries.add(sheetName + “!B1:B” + len, true);

nSeries.setCategoryData(sheetName + “!A1:A” + len);



// for (int i = 0; i < len; i++) {

// chart.getNSeries()

// .get(0)

// .getPoints()

// .get(i)

// .getArea()

// .setForegroundColor(

// 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())); // getArea().setForegroundColor();

// chart.getNSeries().get(0).getPoints().get(i).getBorder()

// .setVisible(false);

// }



wb.getWorksheets().setActiveSheetIndex(chartSheetIdx);


DataLabels datalabels;


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

datalabels = chart.getNSeries().get(i).getDataLabels();

// Set the position of DataLabels

datalabels.setPosition(LabelPositionType.OUTSIDE_END);

// Show the category name in the DataLabels

datalabels.setCategoryNameShown(false);

// Show the value in the DataLabels

datalabels.setValueShown(true);

// Not show the percentage in the DataLabels

datalabels.setShowPercentage(false);

// Not show the legend key.

datalabels.setLegendKeyShown(false);

datalabels.getFont().setSize(10);

datalabels.getFont().setName(“Arial”);

datalabels.setAutoScaleFont(false);

datalabels.setNumberFormat(“0\%”);


}


//Save the output Excel file

wb.save(“output.xlsx”);


//Save the chart image

ImageOrPrintOptions opts = new ImageOrPrintOptions();

opts.setImageFormat(ImageFormat.getJpeg());


chart.calculate();

chart.toImage(“output.jpg”, opts);

10

Thank you so much.

It works fine now.
11

Hi Arpit,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved now. Let us know if you encounter any other issue, we will be glad to look into it and help you further.