Unable to plot time related data on Category(value) axis (i.e, on X axis) using XY Scatter Chart

Aspose.Cells Product Family
,
1

Hi,

I want to plot line chart where X(Category) axis values should be in time with format h:mm and with specific time interval but it is not possible using line chart so I am trying with XY scatter chart but unable to plot the correct time data on Horizontal axis values, always it is showing 0:00 instead of data given. If I try manually in excel I can see the chart plotting correctly for the given data, but when I try it with aspose code I’m unable to get the correct X axis values. Could anyone please help me resolve this issue as it is bit urgent for me.

I need to assign Category Axis Label not base on time range. I need to simply assign some string values. For example…

Category Axis Label [0] = “05:25”
Category Axis Label [0] = “05:45”
Category Axis Label [0] = “06:00”

All those labels are just some string values. I don’t want to refer some data in excel.

Please refer attached docs, please assist me in this.
LineChart.zip (26.3 KB)

2

@Sam0309
By using the sample code you provided for testing, we can reproduce your issue.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-45484
3

@Sam0309,

When you using static string values as chart data, you cannot use the date time string directly as it is. In ms excel, date time values are maintained as numeric values. So, we think you may convert those date time value to double values at first, and then use the double values sequence for chart data. The example code for conversion:

    private static String convertDateTimeValues(String val) throws Exception
    {
        DateFormat df = new SimpleDateFormat("H:mm");
        StringBuilder sb = new StringBuilder(256);
        for(String item : val.split(","))
        {
            sb.append(',');
            sb.append(CellsHelper.getDoubleFromDateTime(new DateTime(df.parse(item)), false));
        }
        return sb.substring(1);
    }
...
        String times = convertDateTimeValues("5:00, 5:27, 5:30, 6:00, 7:00");
...
4

Hi @johnson.shi,

I am able to see the XValues in chart with double values, but I need the XValues to be plotted in time series like “5:20, 6:00…etc”, I tried some solutions but I’m unable to achieve that part. Can you please help me in achieving that. I’m attaching the sample excel document that is generated after converting datetime to double values. And I see the graph is also plotting wrongly, can you please take a look at that and help me with the proper solution.

Sample.zip (19.1 KB)

5

Hi @John.He,

I see that the Issue ID(s): CELLSJAVA-45484 ticket status was changed to ‘Won’t fix’, can you please elaborate with the reason why it can’t be fixed so that I can try some other methods.

Thanks

6

@Sam0309
MS Excel only support double value as the constant data source of the chart.
So these time values are converted as double value , then set double value as scatter chart data source.
You have to set the number format for catergory axis as the following :
chart.CategoryAxis.TickLabels.NumberFormat = “hh:mm”;

The two charts in your attached files are different, one is Scatter, one is Line.
Do you want to create a line chart ?
It’s better that you can create your excepted chart with constant values in MS Excel, then we can check the file and create same chart .

7

@Sam0309
With the following codes, you can get the Line chart in ExpectedOutput.xlsx:

 var wb = new Workbook();
            ChartCollection charts = wb.Worksheets[0].Charts;
            charts.Add(ChartType.Line, 0, 0, 20, 10);

            Chart chart = charts[0];
            chart.NSeries.Add("{250000000,350000000,500000000,600000000,700000000,840000000}",true);
            chart.NSeries.CategoryData = "{\"01:49\",\"01:50\",\"07:08\",\"07:10\",\"07:11\",\"01:04\"}";
            wb.Save(dir + "dest.xlsx");
8

@simon.zhao
I want to create scatter chart with particular time interval in XValues, I’ll attach the sample code and docs that I have created manually in excel using XY scatter chart, I want the same output with using aspose, can you please check those docs and provide me the proper solution.
LineChart.zip (26.3 KB)

9

@simon.zhao
I want the scatter chart in ExpectedOutput.xlsx with 15min interval between the XValues.
For example if 1st point is at ‘01:50PM’ then with 15min interval 2nd point should be ‘02:05PM’, I achieved this manually using excel, can we do it with aspose. Attached is the excel doc manually created with sample data. And here I don’t have data in cells it is given dynamically in aspose.
Scatterchart.zip (22.5 KB)

10

@Sam0309

public static void main(String[] args) throws Throwable {
		Workbook wb = new Workbook();
         ChartCollection charts = wb.getWorksheets().get(0).getCharts();
         charts.add(ChartType.SCATTER_CONNECTED_BY_LINES_WITH_DATA_MARKER, 0, 0, 15, 18);

         Chart chart = charts.get(0);
         chart.getNSeries().add("{12000000,25000000,35000000,15000000,16000001}", true);
         String times = convertDateTimeValues("5:00, 5:27, 5:30, 6:00, 7:00");
         chart.getNSeries().get(0).setXValues(times);
         chart.getCategoryAxis().getTickLabels().setNumberFormat("hh:mm");
         DateFormat df = new SimpleDateFormat("H:mm");
         chart.getCategoryAxis().setMinValue(convertDateTimeValue("5:00"));
         chart.getCategoryAxis().setMajorUnit(convertDateTimeValue("0:05"));
         wb.save(dir + "dest.xlsx");
	}
	 private static double convertDateTimeValue(String val) throws Exception
	 {
		  DateFormat df = new SimpleDateFormat("H:mm");
		  double t = CellsHelper.getDoubleFromDateTime(new DateTime(df.parse(val)), false);
          t = t- Math.floor(t);
          return t;
	 }
	 private static String convertDateTimeValues(String val) throws Exception
	    {
	        DateFormat df = new SimpleDateFormat("H:mm");
	        StringBuilder sb = new StringBuilder(256);
	        for(String item : val.split(","))
	        {
	            sb.append(',');
	            double t = CellsHelper.getDoubleFromDateTime(new DateTime(df.parse(item)), false);
	            t = t- Math.floor(t);
	            sb.append(t);
	        }
	        return "{"+ sb.substring(1) +"}";
	    }
11

@simon.zhao
Thanks for providing the solution it solved my issue
But there is one modification I require
String times = convertDateTimeValues(“04 July 23 5:00, 5:27, 5:30, 6:00, 05 July 23 4:00”);
For some data points I need the Xvalues to be plotted along with date for 1st and last data point in x-axis I need to see both date and time.
I tried changing the number format but it is showing “0 Jan 1990”
chart.getCategoryAxis().getTickLabels().setNumberFormat(“dd MMM yy hh:mm”);
can you please modify solution according to this.

12

@Sam0309,

Could you please create your desired chart in MS Excel manually, save the file and provide us. We will check it soon.

PS. please zip the file prior attaching here.