Chart Series values in date format

sharanya · April 8, 2014, 7:01am

Hi,

I am using latest version of Aspose.Cells. I need to draw a bar chart with the series values as dates.
I cannot use Sheet data values, instead I have to pass comma separated date values.
My code looks like this -

Workbook wb = new Workbook(“filepath”);
Worksheet ws = wb.WorkSheets[0];
int chartIndex = ws.Charts.Add(ChartType.BarStacked, 2, 2, 10, 5);
Chart chart = ws.Charts[chartIndex];
//Next step is to set chart properties
chart.NSeries.Add("{ 11/30/2013, 12/01/2013, 12/02/2013 }", false);
chart.NSeries.CategoryData="{1,2,3,4};
wb.Save(“filepath”};

The above code doesn’t generate the chart.
Please suggest

Thanks,
Sharanya

amjad.sahi · April 8, 2014, 10:39am

Hi,

Please change your sample codes as following, it works fine:

e.g

Sample code:

Workbook wb = new Workbook();

Worksheet ws = wb.Worksheets[0];

int chartIndex = ws.Charts.Add(ChartType.BarStacked, 2, 2, 10, 5);

Chart chart = ws.Charts[chartIndex];

//Next step is to set chart properties

chart.NSeries.Add("{1,2,3,4}",false);

chart.NSeries.CategoryData = “{“11/30/2013”,“12/01/2013”,“12/02/2013”}”;

wb.Save(“e:\test2\out1.xlsx”);

If you still have any issue or confusion, create your desired chart manually in MS Excel and save the Excel file to provide it here, we will check it soon.

Thank you.

sharanya · April 8, 2014, 12:12pm

Hi,

I tried the code which is given above. But I get error message ‘Excel found unreadable content in ‘filename’. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes’.
The chart is blank in the result sheet.

Attached is the excel sheet with the chart which I am looking into. The date fields(Operation Begin ad Operation Duration) will be entered manually.
Please help me in drawing the chart through aspose code

Thanks,
Sharanya

amjad.sahi · April 8, 2014, 12:28pm

Hi,

I do not find any issue when using my sample code and opening the output Excel file into MS Excel, please find it attached here. Please open the file and let us know if you find any issue/ error message while opening the file into MS Excel.

And, I could not find your attached file here, please attach it here again.

Thank you.

sharanya · April 8, 2014, 11:47pm

Hi,

Sorry for the missing attachment file.

Please find the attached file. Also I would require the series values as date fields.
Please do the needful

Thanks,
Sharanya

amjad.sahi · April 9, 2014, 4:31am

Hi,

Thanks for the template file.

After an initial test, I observed the issue as you mentioned. I tried some constant DateTime values for data series as per your desired chart dynamically but the output file is always corrupted. I tried several variations, e.g put double, single or without quotes etc.around DateTime values but to no avail.
e.g
Sample code:

Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];
int chartIndex = ws.Charts.Add(ChartType.BarStacked, 2, 2, 17, 14);
Chart chart = ws.Charts[chartIndex];
//Next step is to set chart properties
chart.NSeries.Add("{‘Sat, 30-Nov 00:01’,‘Sun, 01-Dec 00:00’,‘Sun, 01-Dec 00:00’, ‘Sun, 01-Dec 00:00’, ‘Sun, 01-Dec 00:00’, ‘Sun, 01-Dec 00:00’, ‘Sun, 01-Dec 00:00’, ‘Sun, 01-Dec 00:00’, ‘Sun, 01-Dec 00:00’}", false);
chart.NSeries.Add("{“0:45:01”,“0:00:00”,“0:00:00”, “0:00:00”, “0:01:14”, “0:00:00”, “0:00:00”, “0:03:19”, “0:03:40”}", false);
chart.NSeries.CategoryData = “{1,2,3,4,5,6,7,8,9,10,11}”;
wb.Save(“e:\test2\out1.xlsx”);

I have logged an investigation ticket with an id “CELLSNET-42544” for your issue. We will look into it soon.

Once we have any update on it. we will let you know here.

Thank you.

sharanya · April 9, 2014, 5:06am

Hi,

Thanks for the reply.
Do you have timeline as when the issue will be resolved?
This is very urgent for us

Thanks,
Sharanya

amjad.sahi · April 9, 2014, 5:20am

Hi,

Well, your issue is still pending for analysis, so we can’t provide an update or ETA at the moment. Once we analyze the issue, we will be in better position to provide an update or ETA if possible. If the issue is not complex, it should be fixed within 3-5 days, if the issue is complex, it might take a couple of weeks or so.

Thank you.

amjad.sahi · April 10, 2014, 2:36am

Hi,

We have evaluated your issue further. Well, Series values data only accept numeric values. So, we must convert DateTime string values to double values before setting them as data series for the chart.

Please see the following simple sample code according to your Test.xlsx file.
e.g
Sample code:

string dir = @"D:\Tmp";

Workbook wb = new Workbook();

Worksheet ws = wb.Worksheets[0];

int chartIndex = ws.Charts.Add(ChartType.BarStacked, 2, 2, 17, 14);

Chart chart = ws.Charts[chartIndex];

string dateStr = "{";

dateStr += DateTime.Parse("2013/11/30 0:01:00").ToOADate() + ",";

dateStr += DateTime.Parse("2013/12/1 0:00:00").ToOADate() + ",";

dateStr += DateTime.Parse("2013/12/1 0:00:00").ToOADate() + ",";

dateStr += DateTime.Parse("2013/12/1 0:00:00").ToOADate() + ",";

dateStr += DateTime.Parse("2013/12/1 0:00:00").ToOADate() + ",";

dateStr += DateTime.Parse("2013/12/1 0:00:00").ToOADate() + ",";

dateStr += DateTime.Parse("2013/12/1 0:00:00").ToOADate() + ",";

dateStr += DateTime.Parse("2013/12/1 0:00:00").ToOADate() + ",";

dateStr += DateTime.Parse("2013/12/1 0:00:00").ToOADate() + ",";

dateStr += DateTime.Parse("2013/12/1 0:01:00").ToOADate() + ",";

dateStr += DateTime.Parse("2013/12/1 0:01:00").ToOADate() + "}";

chart.NSeries.Add(dateStr, false);

dateStr = "{";

dateStr += (DateTime.Parse("0:45:01").ToOADate() -DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:00").ToOADate()-DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:00").ToOADate()-DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:00").ToOADate()-DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:01:14").ToOADate()-DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:00").ToOADate()-DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:00").ToOADate()-DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:03:19").ToOADate()-DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:03:40").ToOADate()-DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:00").ToOADate()-DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:00").ToOADate()-DateTime.Parse("0:0:0").ToOADate()) + "}";

chart.NSeries.Add(dateStr, false);

chart.ValueAxis.TickLabels.NumberFormat = "[$-409]ddd, dd-mmm hh:mm;@";

chart.ChartObject.Width = 1200;

chart.PlotArea.Area.Formatting = FormattingType.Automatic;

chart.PlotArea.Border.FormattingType = ChartLineFormattingType.Automatic;

wb.Save(dir + "\\mytest.xlsx");

Thank you.

sharanya · April 14, 2014, 6:20am

Hi,

I tried the above code. It doesn’t give me the exact output what I wanted.

Please find attached file Sheet2. Column1 and Column2 are the values I want to plot the chart with. There is a chart which is generated using excel chart feature.

There is also out put generated by Aspose(with values taken by first 2 rows). Its not exactly similar to the desired output.

Please suggest me the complete code which gives me the proper output with date fields.

Thanks,
Sharanya

amjad.sahi · April 14, 2014, 7:47am

Hi,

I tried to accomplish the task (as per your desired chart in the template file) but I could not get the desired results initially. I tried the following sample codes, I tried some other variations also but to no avail.

e.g

Sample code:

string dir = @“e:\test2”;

Workbook wb = new Workbook();

Worksheet ws = wb.Worksheets[0];

int chartIndex = ws.Charts.Add(ChartType.BarStacked, 2, 2, 17, 14);

Chart chart = ws.Charts[chartIndex];

string dateStr = “{”;

dateStr += (DateTime.Parse(“06:00:07”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“23:25:05”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“23:33:04”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“23:33:07”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“23:33:08”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“23:53:03”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“23:53:04”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“23:53:06”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “}”;

chart.NSeries.Add(dateStr, false);

dateStr = “{”;

dateStr += (DateTime.Parse(“0:18:08”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“0:28:56”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“0:00:51”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“0:00:24”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“0:00:25”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“0:00:24”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“0:00:21”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“0:03:21”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“0:03:47”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“0:00:58”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “,”;

dateStr += (DateTime.Parse(“0:00:17”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “}”;

dateStr += (DateTime.Parse(“0:03:52”).ToOADate() - DateTime.Parse(“0:0:0”).ToOADate()) + “}”;

chart.NSeries.Add(dateStr, false);

chart.NSeries.CategoryData = “{“a”,“b”,“c”, “d”, “e”, “f”, “g”, “h”, “i”, “j”, “k”, “l”}”;

chart.ValueAxis.TickLabels.NumberFormat = “[$-409]hh:mm:ss”;

chart.ChartObject.Width = 1200;

chart.PlotArea.Area.Formatting = FormattingType.Automatic;

chart.PlotArea.Border.FormattingType = ChartLineFormattingType.Automatic;

wb.Save(dir + “\out1.xlsx”);

Well, we need to evaluate your desired chart thoroughly, we will do it soon. I have logged it against your existing issue “CELLSNET-42544” into our database. Hopefully, we will get back to you with a sample code snippet that should address your issue soon.

Thank you.

sharanya · April 23, 2014, 11:12pm

Hi,

Please let me know the status of this issue

Thanks,
Sharanya

shakeel.faiz · April 24, 2014, 12:45am

Hi Sharanya,

Thanks for your posting and using Aspose.Cells.

We are afraid, there is no update for you at this moment. However, we have logged your comment in our database against this issue. Once, there is some fix or other update for you, we will let you know asap.

sharanya · May 20, 2014, 2:14am

Hi,

What is the status of this issue?

Thanks,
Sharanya

amjad.sahi · May 20, 2014, 2:37am

Hi,

Well, I am afraid, still your issue is not figured out. I have logged your concerns against your issue “CELLSNET-42544” into our database. I have also asked the relevant developer to look into it soon and provide an update or any workaround to cope with your issue.

Once we got the response from the developer, we will let you know immediately here.

Thanks,

aryhant1 · June 9, 2014, 2:17am

Any updates on the below issue. This is opened for long time with no resolution.

The fix is really critical for our project and needs tor be resolved ASAP.

Appreciate your early response

shakeel.faiz · June 9, 2014, 5:37am

Hi Sharanya,

Thanks for your posting and using Aspose.Cells.

We
are afraid, there is no update for you at this moment. However, we have again
logged your comment in our database against this issue. Once, there is
some fix or other update for you, we will let you know asap.

shakeel.faiz · June 9, 2014, 10:07pm

Hi Sharanya,

Thanks for using Aspose.Cells.

Please see the following sample code. We have also attached the output file generated by this code for your reference.

C#

string dir = @"D:\tmp";

Workbook wb = new Workbook(FileFormatType.Xlsx);

Worksheet ws = wb.Worksheets[0];

int chartIndex = ws.Charts.Add(ChartType.BarStacked, 2, 2, 17, 14);

Chart chart = ws.Charts[chartIndex];

//Formats datetime to OADate string

string dateStr = "{";

dateStr += DateTime.Parse("2013/9/15 6:00:07").ToOADate() + ",";

dateStr += DateTime.Parse("2013/9/21 23:25:05").ToOADate() + ",";

dateStr += DateTime.Parse("2013/9/21 23:33:04").ToOADate() + ",";

dateStr += DateTime.Parse("2013/9/21 23:33:07").ToOADate() + ",";

dateStr += DateTime.Parse("2013/9/21 23:33:08").ToOADate() + ",";

dateStr += DateTime.Parse("2013/9/21 23:53:03").ToOADate() + ",";

dateStr += DateTime.Parse("2013/9/21 23:53:04").ToOADate() + ",";

dateStr += DateTime.Parse("2013/9/21 23:53:06").ToOADate() + "}";

int seriesIndex = chart.NSeries.Add(dateStr, false);

Series s1 = chart.NSeries[seriesIndex];

//Sets fill of first series to no fill.

s1.Area.Formatting = FormattingType.None;

//Shows data label of first series and format

s1.DataLabels.ShowValue = true;

s1.DataLabels.NumberFormat = "[$-409]hh:mm:ss";

s1.DataLabels.Position = LabelPositionType.InsideEnd;

//Formats datetime to OADate string.

dateStr = "{";

dateStr += (DateTime.Parse("0:18:08").ToOADate() - DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:28:56").ToOADate() - DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:51").ToOADate() - DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:24").ToOADate() - DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:25").ToOADate() - DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:24").ToOADate() - DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:21").ToOADate() - DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:47").ToOADate() - DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:58").ToOADate() - DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:00:17").ToOADate() - DateTime.Parse("0:0:0").ToOADate()) + ",";

dateStr += (DateTime.Parse("0:03:52").ToOADate() - DateTime.Parse("0:0:0").ToOADate()) + "}";

chart.NSeries.Add(dateStr, false);

//Adds category data

chart.NSeries.CategoryData = "{a,b,c,d,e,f,g,h,i,j,k,l}";

//Formats value axis

chart.ValueAxis.TickLabels.NumberFormat = "[$-409]hh:mm:ss";

//Sets width of chart

chart.ChartObject.Width = 800;

//Sets plotArea

chart.PlotArea.Area.Formatting = FormattingType.Automatic;

chart.PlotArea.Border.FormattingType = ChartLineFormattingType.Automatic;

wb.Save(dir + "\\mytest.xlsx");

sharanya · July 6, 2014, 11:44am

Hi ,

Thanks for the reply.

Attached is the sheet containing the chart generated by Aspose Cells using Time and Duration columns.

The issue is that, though I am setting the Area formatting for the “Time” column(chart.NSeries[1]), its not appearing in the report.

Here is the code:

chart.NSeries[0].Area.Formatting = FormattingType.None;

chart.NSeries[1].Area.Formatting = FormattingType.Automatic;

chart.NSeries[1].Area.FillFormat.Type = FillType.Solid;

chart.NSeries[1].Area.FillFormat.SolidFill.Color = Color.FromArgb(0, 103, 198);

//Set chart’s Y-Axis properties

chart.CategoryAxis.MajorGridLines.IsVisible = true;

chart.CategoryAxis.TickLabels.Font.Size = 10;

chart.CategoryAxis.TickLabelPosition = TickLabelPositionType.NextToAxis;

chart.CategoryAxis.MajorTickMark = TickMarkType.Outside;

//Set chart’s X-Axis properties

chart.ValueAxis.TickLabels.Font.Size = 10;

chart.ValueAxis.TickLabels.NumberFormat = “[$-409]ddd, dd-mmm hh:mm;@”;

chart.ValueAxis.TickLabelPosition = TickLabelPositionType.NextToAxis;

chart.ValueAxis.MinorTickMark = TickMarkType.None;

chart.ValueAxis.MajorTickMark = TickMarkType.Outside;

chart.ValueAxis.MajorGridLines.IsVisible = false;

//Set X-Axis Datalabels’ properties

chart.NSeries[0].DataLabels.ShowValue = true;

chart.NSeries[0].DataLabels.Position = LabelPositionType.InsideEnd;

chart.NSeries[0].DataLabels.TextDirection = TextDirectionType.RightToLeft;

chart.NSeries[0].DataLabels.TextVerticalAlignment = TextAlignmentType.Center;

chart.NSeries[0].DataLabels.NumberFormat = “hh:mm:ss”;

chart.PlotArea.Area.Formatting = FormattingType.Automatic;

chart.ShowLegend = false;

******************************

Please let me know if I miss anything.

Thanks,

Sharanya

shakeel.faiz · July 7, 2014, 4:50am

Hi Sharanya,

Thanks for your posting and using Aspose.Cells.

Please set the FileFormatType to XLSX in Workbook constructor and see if it makes any difference.

e.g

Workbook workbook = new Workbook(FileFormatType.Xlsx);

Please also provide us runnable sample code/project and the actual and expected output xlsx files. You can create expected output xlsx file manually and post it here for our reference. We will look into it and update you asap.