We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Get date time xValue from a chart at each point using Aspose.Cells for Java

Aspose.Cells Product Family
#1 
Workbook wb = new Workbook("C:/日期-数值-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();
for (int i = 0; i < points.getCount(); i++) {
ChartPoint point = points.get(i);
System.out.println(point.getXValue() + "==>" + point.getYValue() + "::::" + point.getShapeXPx() + "@" + point.getShapeYPx());
}

There are two issues.

  1. xValue are double but not datetime.
  2. point’s xValue, yValue, xPx and yPx are wrong, because in chart points sort by first column, not according table data
#2

Hi,


Thanks for your posting and using Aspose.Cells.

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.

Java

Workbook wb = new Workbook(dirPath + “ab.xlsx”);
wb.calculateFormula();
wb.save(dirPath + “output.html”);
#3

HTML is well.


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!
#4

Hi,


Thanks for your posting and using Aspose.Cells.

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.

Console Output
最大折扣
41832.0==>0.0::::59@148
41837.0==>0.2::::81@409
41844.0==>0.0::::98@83
41871.0==>0.2::::106@148
41877.0==>0.0::::108@409
41881.0==>0.0::::117@376
41887.0==>0.0::::122@409
41912.0==>0.0::::136@214
41923.0==>0.2::::139@83
41934.0==>0.2::::141@409
41942.0==>0.2::::163@409
41950.0==>0.15::::174@409
41957.0==>0.0::::196@344
41793.0==>0.15::::201@409
41815.0==>0.25::::212@83
41644.0==>0.2::::231@409
41652.0==>0.0::::234@214
41658.0==>0.25::::294@148
41661.0==>0.2::::308@409
41662.0==>0.0::::310@409
41665.0==>0.025::::327@409
41667.0==>0.0::::330@409
41672.0==>0.15::::332@83
41673.0==>0.25::::338@279
41674.0==>0.0::::346@246
41682.0==>0.0::::351@409
41686.0==>0.0::::357@148
41694.0==>0.05::::387@83
41696.0==>0.0::::390@148
41700.0==>0.25::::466@214
41707.0==>0.0::::526@83
41708.0==>0.15::::572@409
41730.0==>0.2::::586@148
41735.0==>0.0::::605@409
41736.0==>0.0::::679@148
41742.0==>0.0::::695@409
41743.0==>0.0::::706@409
41744.0==>0.25::::723@409
41746.0==>0.1::::791@409
41749.0==>0.125::::821@148
41751.0==>0.0::::851@148
41753.0==>0.2::::873@148
41764.0==>0.25::::895@214
41765.0==>0.2::::914@409
0.0==>0.15::::0@0
0.0==>0.0::::0@0
0.0==>0.11666666666666665::::0@0

#5

OK. The xlsx above is too complex.

Please use this attachment xlsx

Workbook wb = new Workbook(“C:/Users/admin/Desktop/SimpleDate.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();
for (int i = 0; i < points.getCount(); i++) {
ChartPoint point = points.get(i);
System.out.println(point.getXValue() + “==>” + point.getYValue() + “::::” + point.getShapeXPx() + “@”
+ point.getShapeYPx() + “…” + point.getShapeHeightPx());
}

Output is

cc
36528.0==>5.0::::396@84…169
36526.0==>1.0::::83@218…35

But in image you can see the first chart point is 2000/1/1 that should be 36526.0 and yValue is 1. And the position shapeX is wrong.

So there are two issue:

  1. xValue is DateTime but not double.
  2. chart points positions are wrong.
#6

Hi,


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.

External Reference:
What is the equivalent of DateTime.FromOADate() in Java (double to Datetime in Java)

Java

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

#7

In fact I hope Aspose.Cells fix this issue that without change NSeries.points order. I just want Aspose.Cells return correct chart point position.



In 2nd issue, I know I can convert double to DateTime.

But I hope Aspose.Cells return DateTime with getXValue()
In my case, I can’t determine which type of xValue because XLSX files are uploaded by end user.
#8
Hi,

Thanks for your posting and using Aspose.Cells.

xhaixia:
In fact I hope Aspose.Cells fix this issue that without change NSeries.points order. I just want Aspose.Cells return correct chart point position.

I have requested the product team to implement CharPoint.Position property instead of changing series order.

xhaixia:
In 2nd issue, I know I can convert double to DateTime.

But I hope Aspose.Cells return DateTime with getXValue()
In my case, I can't determine which type of xValue because XLSX files are uploaded by end user.

Sure, we will look into it and implement it if feasible. We will update you asap.

#9

Hi,


Thanks for using Aspose.Cells.

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

#10

Hi,

Thanks for your posting and using Aspose.Cells.

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;

}

}

}
#11

Hi,


Thanks for using Aspose.Cells.

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.

#12

Hi,

Please try our latest version/fix: Aspose.Cells for Java v16.11.7 (attached).
Please try
the following sample code with the new fix.
e.g
Sample code:

    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.toImage(dir + “chart.png”);

    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);
    }

    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;
    }
    }

    }

Let us know your feedback.

Thank you.

#13

The issues you have found earlier (filed as CELLSJAVA-42043) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.