I have tested your issue with your sample excel file by converting it to html using the latest version: Aspose.Cells for Java (Latest Version) and found the issues in a chart as shown in the screenshot. Please check the screenshot and let us know if these are the issues you are talking about. Or we are missing something? Let us know your feedback.
I have also attached the output html for a reference.
But the code output is wrong. I need to know each points’ xValue and position by call point.getXValue() + “==>” + point.getYValue() + “::::” + point.getShapeXPx() + “@” + point.getShapeYPx()
This chart’s CategoryData is Date.
When chart save to image, MS Excel and Aspose.Cells sort points by CategoryData.
But series.getPoints return points without sort, and they have wrong position!
I understand the following issue. Also if you see the screenshot showing the chart image, it does have problems highlighted in red color.
xValue are double but not datetime.
This chart’s CategoryData is Date.
However, I do not understand this problem. Please give me correct output which you can create manually or clarify it more, how your current console output is wrong? I have shown the console output for your reference.
What this point means? Please clarify it.
2. point’s xValue, yValue, xPx and yPx are wrong, because in chart points sort by first column, not according table data
When chart save to image, MS Excel and Aspose.Cells sort points by CategoryData.
But series.getPoints return points without sort, and they have wrong position!
Thanks for your cooperation in this regard and have a good day.
Thanks for your explanation with simpler excel file and using Aspose.Cells.
We were able to observe this issue and logged it in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.
This issue has been logged as
CELLSJAVA-42043 - Chart points positions are wrong
For your second issue, you can convert double values (OLE Dates which are stored as numbers) into Date objects using the following method.
public Date fromDoubleToDateTime(double OADate)
{
long num = (long) ((OADate * 86400000.0) + ((OADate >= 0.0) ? 0.5 : -0.5));
if (num < 0L) {
num -= (num % 0x5265c00L) * 2L;
}
num += 0x3680b5e1fc00L;
num -= 62135596800000L;
return new Date(num);
}
Here is the console output of the double values taken from your excel file.
Console Output
最大折扣
Sat Jul 12 05:00:00 PKT 2014
Thu Jul 17 05:00:00 PKT 2014
Thu Jul 24 05:00:00 PKT 2014
Wed Aug 20 05:00:00 PKT 2014
Tue Aug 26 05:00:00 PKT 2014
Sat Aug 30 05:00:00 PKT 2014
Fri Sep 05 05:00:00 PKT 2014
Tue Sep 30 05:00:00 PKT 2014
Sat Oct 11 05:00:00 PKT 2014
Wed Oct 22 05:00:00 PKT 2014
Thu Oct 30 05:00:00 PKT 2014
Fri Nov 07 05:00:00 PKT 2014
Fri Nov 14 05:00:00 PKT 2014
Tue Jun 03 05:00:00 PKT 2014
Wed Jun 25 05:00:00 PKT 2014
Sun Jan 05 05:00:00 PKT 2014
Mon Jan 13 05:00:00 PKT 2014
Sun Jan 19 05:00:00 PKT 2014
Wed Jan 22 05:00:00 PKT 2014
Thu Jan 23 05:00:00 PKT 2014
Sun Jan 26 05:00:00 PKT 2014
Tue Jan 28 05:00:00 PKT 2014
Sun Feb 02 05:00:00 PKT 2014
Mon Feb 03 05:00:00 PKT 2014
Tue Feb 04 05:00:00 PKT 2014
Wed Feb 12 05:00:00 PKT 2014
Sun Feb 16 05:00:00 PKT 2014
Mon Feb 24 05:00:00 PKT 2014
Wed Feb 26 05:00:00 PKT 2014
Sun Mar 02 05:00:00 PKT 2014
Sun Mar 09 05:00:00 PKT 2014
Mon Mar 10 05:00:00 PKT 2014
Tue Apr 01 05:00:00 PKT 2014
Sun Apr 06 05:00:00 PKT 2014
Mon Apr 07 05:00:00 PKT 2014
Sun Apr 13 05:00:00 PKT 2014
Mon Apr 14 05:00:00 PKT 2014
Tue Apr 15 05:00:00 PKT 2014
Thu Apr 17 05:00:00 PKT 2014
Sun Apr 20 05:00:00 PKT 2014
Tue Apr 22 05:00:00 PKT 2014
Thu Apr 24 05:00:00 PKT 2014
Mon May 05 05:00:00 PKT 2014
Tue May 06 05:00:00 PKT 2014
Sat Dec 30 05:00:00 PKT 1899
Sat Dec 30 05:00:00 PKT 1899
Sat Dec 30 05:00:00 PKT 1899
For your 2nd issue, we will look into its feasibility and implement it if possible. Once, there is some fix or other news for you, we will share it with you asap.
This issue has been logged as
CELLSJAVA-42046 - Aspose.Cells should return DateTime values instead of double values with getXValue() method
We are afraid, we cannot make the getXValue() return date time value. We must do the same behavior as Excel.
In excel file, the value is double value (OADate) and the order of points is not sorted. You can do it by following code according to your requirement.
Java
Main:
public static void Test1380() throws Exception{
String dir = "D:\\Aspose\\User\\1380\\";
Workbook wb = new Workbook(dir + "日期-数值-EXCEL联合图（有问题）555.xlsx");
Chart chart = wb.getWorksheets().get(0).getCharts().get(0);
chart.calculate();
Series series = chart.getNSeries().get(0);
System.out.println(series.getDisplayName());
ChartPointCollection points = series.getPoints();
int pointCount = points.getCount();
ArrayList listChartPoints = new ArrayList();
for (int i = 0; i < pointCount; i++) {
ChartPoint point = points.get(i);
MyChartPoint myChartPoint = new MyChartPoint(point);
listChartPoints.add(myChartPoint);
}
Collections.sort(listChartPoints);
for (int i = 0; i < listChartPoints.size(); i++) {
MyChartPoint myChartPoint = (MyChartPoint)listChartPoints.get(i);
ChartPoint point = myChartPoint.getChartPoint();
double OADate = Double.parseDouble(point.getXValue().toString());
System.out.println(OADate);
Date date = fromDoubleToDateTime(OADate);
SimpleDateFormat sf=new SimpleDateFormat("M/d/yyyy");
System.out.println(sf.format(date)+ "==>" + point.getYValue()
+ "::::" + point.getShapeXPx() + "@" + point.getShapeYPx());
}
}
public static Date fromDoubleToDateTime(double OADate)
{
long num = (long) ((OADate * 86400000.0) + ((OADate >= 0.0) ? 0.5 : -0.5));
if (num < 0L) {
num -= (num % 0x5265c00L) * 2L;
}
num += 0x3680b5e1fc00L;
num -= 62135596800000L;
return new Date(num);
}
MyChartPoint.cs:
import com.aspose.cells.ChartPoint;
public class MyChartPoint implements Comparable
{
private ChartPoint m_chartPoint;
public MyChartPoint(ChartPoint p){
m_chartPoint = p;
}
public ChartPoint getChartPoint(){
return this.m_chartPoint;
}
@Override
public int compareTo(Object o) {
MyChartPoint other = (MyChartPoint)o;
Object thisObject = this.getChartPoint().getXValue();
Object otherObject = other.getChartPoint().getXValue();
if(thisObject == null && otherObject == null){
return 0;
}
else if(thisObject != null && otherObject == null){
return 1;
}
else if(thisObject == null && otherObject != null){
return -1;
}
double thisXvalue = Double.parseDouble(thisObject.toString());
double otherXvalue = Double.parseDouble(otherObject.toString());
if ( thisXvalue== otherXvalue)
{
return 0;
}
else if (thisXvalue > otherXvalue)
{
return 1;
}
else
{
return -1;
}
}
}
We have looked into this issue further and the code provided to you earlier and found, result is still not proper, so we will fix the issues in next fix. Once, the fix is available for you, we will let you know asap.
xhaixia: