Free Support Forum - aspose.com

Chart Image Mapping

Do charts in Aspose.Cells support image mapping? As in, when a chart is output to a specific file format, can different components of the chart be highlighted or selected/tool-tipped, and if so, where can I find documentation related to how to do that? Thanks for any help that can be provided.

Hi,

Thanks for your posting and considering Aspose.Cells for Java.

I am afraid this feature is not supported. However, Aspose.Cells for Java allows you to take images of your charts and also it allows you to add both text and images hyperlinks.

Could you please provide us your sample xls/xlsx file which you want to achieve programmatically using Aspose.Cells for Java?

We will look into your sample file and then advise you asap.

Thanks for the response. I don’t have a sample xlsx file, but I we’re trying to use Aspose.Cells in order to programmatically create charts based on given input information, and one of the key features would be to be able to tooltip/click different parts of the charts in order to get more information. Also, I meant to mark this thread for the JAVA version, not .NET.

Hi,

Thanks for your input and clarification.

Both .NET and Java features are almost same. I am afraid, this feature is not available in Aspose.Cells for Java.

For a workaround, you can give us your sample xls/xlsx file, we will analyze it using Aspose.Cells API (s) and give you feedback.

However, you can definitely create charts based on your data programmatically using Aspose.Cells.

Hi,


No problem, we will consider it for Aspose.Cells for Java. As Shakeel Faiz requested you, kindly do create an Excel file (based on your needs) manually in MS Excel 2007/2010. Also give us more details plus steps and provide some screen shots (of MS Excel) to demonstrate on how to do it in MS Excel manually. We will surely check out your requirements soon.

Thank you.

Thank you. This feature isn’t inherently in Excel, so I can’t create an Excel sheet that describes the behavior. However, what we essentially need is some sort of XML markup provided in the charting package (or a corresponding object/table that provides this data). Our product is going to consume an output image from the Chart object, and we need to be able to map each value to pixel coordinates in the image in order to provide added functionality such as tooltips. The following is an example of the kind of data we need for every datapoint:




5
rect
2,0,53,59

We are so far very happy with what we’ve seen in Aspose.cells so far, but this is the only feature that we feel is missing. It’s a very big requirement for us, so if there is any way to incorporate this into Aspose.Cell (Java) then it would successfully meet all of our requirements. Please let me know if you need more information, otherwise the wikipedia site for image maps provides a bit more information on this topic. Thank you.

Hi,


Thanks for elaborating your needs and provide more details.

Well, generally, Aspose.Cells follows MS Excel standards, so, if a feature is not available in MS Excel, we may not support it. Also, we have to look into it and investigate it to check its feasibility while analyzing all its aspects. Anyways, I have logged a ticket with an id: CELLSJAVA-40275. Our concerned developer will look into your desired needs and we will get back to you soon. If it is feasible enough, we may support it otherwise I am afraid we have to apologize to you from our end in advance.

Thank you.

Thank you Amjad for creating the ticket. I actually happened to find an example in Excel:



In Excel 2010, when you hover over any part of a chart, you will see a tooltip like in the following screenshot:



This is only possible if Excel 2010 supported image mapping of their charts (if you hover outside of the graph, it will change accordingly). Just wanted to stress that this is a really high value item for us, and we really need this in Aspose.Cells in order to achieve what we’re trying to do. Thank you though for the help that you’ve given, I appreciate it. I hope to hear back from that developer soon.

Hi,

Thanks for your screenshot and using Aspose.Cells.

We have looked into your issue and we think, we can provide the information (position,series name,point index, point value and so) by properties of ChartPoint not by XML.

You can build the xml accordingly by the chart object. We think, it should meet your requirements.

Please let us know your feedback.

Hi Shakeel,
That’s perfect! Providing it by XML was just an example, so via a Charting object would work out just fine for our purposes. Thank you!

Hi,

Thanks for using Aspose.Cells.

Please download and try this fix: Aspose.Cells for Java (Latest Version)

We have created a simple test. Please refer to the attachment with this post 2.

Java
package com.aspose.test;

import java.io.FileWriter; 
import java.io.IOException; 
import java.io.Writer;

import org.dom4j.*;
import org.dom4j.io.XMLWriter; 

import com.aspose.cells.CellsHelper;
import com.aspose.cells.Chart;
import com.aspose.cells.ChartCollection;
import com.aspose.cells.ChartPoint;
import com.aspose.cells.FileFormatType;
import com.aspose.cells.LoadOptions;
import com.aspose.cells.Series;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.aspose.cells.WorksheetCollection;

public class Test40275 {

/**
* @param args
*/
public static void main(String[] args) {
CellsHelper.setFontDir(“C:\Windows\Fonts\”);

Chart2Image();

System.out.println(“Test Done!”);
}
private static void Chart2Image(){
try {
String path = “D:/Aspose/User/0501/Book1.xlsx”; 

LoadOptions load = new LoadOptions();
//load.setLanguageCode(CountryCode.USA);
//load.setRegion(CountryCode.USA);
Workbook workbook = new Workbook(path, load);
WorksheetCollection worksheets = workbook.getWorksheets();

workbook.calculateFormula();

for (int i = 0; i < worksheets.getCount(); i++) {
Worksheet sheet = worksheets.get(i);
if(!sheet.isVisible()){
continue;
}
//if (sheet.getName() != “XY散点图”)
// continue;

//if(sheet.getIndex() != 2)
//continue;

ChartCollection cc = sheet.getCharts();
if (cc != null) {
for (int j = 0; j < cc.getCount(); j++) {
//if (j != 1)
// continue;

Chart chart = cc.get(j);

chart.calculate();

String fileFile = path + “_” + sheet.getName() + “No."
+ Integer.toString(j) + “.xml”;
CreateChartXml(chart, fileFile);


String destFile = “”;

workbook.setFileFormat(FileFormatType.XLSX);
destFile = path + "” + sheet.getName() + “No."
+ Integer.toString(j) + “.png”;
try {
chart.toImage(destFile);

} catch (Exception e) {
e.printStackTrace();
}

destFile = path + "” + sheet.getName() + “_No.”
+ Integer.toString(j) + “.emf”;

try {
//chart.toImage(destFile);

} catch (Exception e) {
e.printStackTrace();
}
}
}
}

} catch (Exception e) {
e.printStackTrace();
}

}

private static void CreateChartXml(Chart c, String fileName){
Document doc = DocumentHelper.createDocument();

Element root = DocumentHelper.createElement(“chart”);

doc.add(root);

int chartWidthPx = c.getChartObject().getWidth();
int chartHeightPx = c.getChartObject().getHeight();

if (c.getTitle().getText() != null && !c.getTitle().getText().equals(""))
{
Element chartTitle = DocumentHelper.createElement(“Shape”);
chartTitle.addAttribute(“objectType”, “ChartTitle”);
chartTitle.addAttribute(“shapeType”, “rect”);

int left = c.getTitle().getX() * chartWidthPx / 4000;
int top = c.getTitle().getY() * chartHeightPx / 4000;
int right = left + c.getTitle().getWidth() * chartWidthPx / 4000;
int bottom = top + c.getTitle().getHeight() * chartHeightPx / 4000;
chartTitle.addAttribute(“coords”, left + “,” + top + “,” + right + “,” + bottom);

chartTitle.addAttribute(“desc”, “Chart Title”);

root.add(chartTitle);
}

for (int i = 0; i < c.getNSeries().getCount(); i++)
{
Series series = c.getNSeries().get(i);
for (int j = 0; j < series.getPoints().getCount(); j++)
{
ChartPoint chartPoint = series.getPoints().get(j);

Element pointNode = DocumentHelper.createElement(“Shape”);
pointNode.addAttribute(“objectType”, “ChartPoint”);
pointNode.addAttribute(“shapeType”, “rect”); 

int left = chartPoint.getShapeX() * chartWidthPx / 4000;
int top = chartPoint.getShapeY() * chartHeightPx / 4000;
int right = left + chartPoint.getShapeWidth() * chartWidthPx / 4000;
int bottom = top + chartPoint.getShapeHeight() * chartHeightPx / 4000;
pointNode.addAttribute(“coords”, left + “,” + top + “,” + right + “,” + bottom);

if(chartPoint.getYValue() != null)
pointNode.addAttribute(“yValue”, chartPoint.getYValue().toString());

pointNode.addAttribute(“desc”, "Series " + series.getDisplayName() + " Point " + j);

root.add(pointNode);
}
}

try { 
Writer fileWriter=new FileWriter(fileName); 
XMLWriter xmlWriter=new XMLWriter(fileWriter); 
xmlWriter.write(doc); 
xmlWriter.close(); 
} 
catch (IOException e) { 
System.out.println(e.getMessage()); 
} 
}
}

First of all, thank you for the response and the quick turnaround! I
have a couple of questions based on the example provided in the
attachment:



1.) I am noticing that the returned coordinates for all 3 of the values
in series 1 are {0,0,0,0}. Is chartPoint.getShapeX() and
chartPoint.getShapeY() returning a pixel value? If so, I don’t know how
{0,0,0,0} would be a valid coordinate for the rectangle as it would
imply that the rectangular bar does not exist, although they clearly
display within the chart. The point of the coordinates is so that we can
use them to develop things such as hover and tooltip actions by knowing
exactly where the bar is. In the crude example below, these coordinate would map to each bar, and thus I don’t understand how {0,0,0,0} can encompass that bar.




2.) Although I arbitrarily labeled these points, I’m sure that each point would be in a specific order of the rectangle (such as bottom-left going counter-clockwise or something like that). What is the order of these points?

3.) What are the values of chartPoint.getShapeX() and
chartPoint.getShapeY() in relation to? As in, are they offsets from the edge of chart or the sheet?

4.) I noticed that in the .xlsx file that was provided, there were 2 charts. Thus, the code should be returning 18 results instead of 9 (as the coordinates for each bar in chart 1 would not match the coordinates for each bar in chart 2 because one is a column graph, and the other is a bar graph). Could you please explain this?

So far, the idea seems to be exactly what we want (we’re not really interested in generating an XML file, as per the example in the given Java class, because the idea of an XML file was just to illustrate the concept. Using the ChartPoint object to get these 4 coordinates for each chart value, as well as the y-value is what we were looking for). Thanks for the great support on this feature for us.

Hi,

Thanks for your comments and sharing your feedback and insight.

It’s good to know that the above code and ChartObject properties are being helpful.

We will look into your mentioned issue descriptions and give you advice asap.

Also, we have logged your comments in our database against the issue id: CELLSJAVA-40275

Once, there is some update for you, we will let you know.

Hi,


Please refer to our previous attachment that illustrates the chart points' position. Usually, the position of Chart object is represented by 4000 units and they offsets from the edge of the chart. By simple calculation, we can get the pixels. E.g. get the left-right corner of the chart point by the following code. With the left-right position, getShapeWidth() and getShapeHeight(), We can get the other positions.

int left = chartPoint.getShapeX() * chartWidthPx / 4000;

int top = chartPoint.getShapeY() * chartHeightPx / 4000;

Also, test product with one xml file for each chart. Every file has 9 chart points.


Thank you.

Hi,

And, please refer the attached image that explains the chart point’s position.

Thank you.

Hi,
Thanks for the explanation. I just got around to trying the attached jar, and I’m noticing a couple of issues:

1.) I keep getting a value of 0 for all 4 of of the properties in ChartPoint for every single ChartPoint in the collection. Is it possible that an older jar was attached? I tried this several times and keep getting the same result.

2.) The chart that I’m trying has a Series with 6 points in it, but I noticed that 9 points were found and printed (although they were empty and each of their properties were 0). I would expect that there would be a ChartPoint for every value of a Series (so in this case 6, and a chart with 20 values would contain 20 ChartPoints) so why are there always 9 ChartPoints?

Thanks for the help.

Hi,


We have logged your feedback.

It will be better if you could provide a sample program/code with your template files to show the two issues, we will check your issue soon.

thank you.

I’m using code similar to the the attachment in this post, where after I read in the Workbook, I pass each chart into the following:

Chart c = sheet.getCharts(i);
for (int j = 0; j < c.getNSeries().getCount(); j++)
{
Series series = c.getNSeries().get(j);
for (int k = 0; k < series.getPoints().getCount(); k++)
{
ChartPoint chartPoint = series.getPoints().get(k);

int left = chartPoint.getShapeX() * chartWidthPx / 4000;
int top = chartPoint.getShapeY() * chartHeightPx / 4000;
int right = left + chartPoint.getShapeWidth() * chartWidthPx / 4000;
int bottom = top + chartPoint.getShapeHeight() * chartHeightPx / 4000;
System.out.println("left: “+left+” top: “+top+” right: “+right+” bottom: "+bottom);
}
}

I get “left: 0 top: 0 right: 0 bottom: 0” printed 9 times for every chart that is passed in.

Hi,

Thanks for using Aspose.Cells.

The position information of chart point is not in excel file directly. Please call Chart.Calculate() method before get the position information.

For the count of points being not as expected, please provide the source excel.

The chart.calculate() seemed to fix the issue that I was having. Also, I was able to figure out the second item, so now that’s a non-issue. We’re very pleased so far with the support that’s been given, thank you!

One question that I have is how would I use this new feature in order to perform mapping of non-bar charts? For example, Pie charts, Scatter, Line etc? Top, left, bottom, and right are perfect for rectangular charts such as bar and column (and we will be incorporating it very shortly), but in terms of Pie and the other charts, I think it gets a bit trickier. Any insight on this would help, even if it means waiting a bit longer for a slight enhancement to map those charts as well. Please do let me know.

Thanks as always for all of the great help.