Chart Series values in date format

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.

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

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.



Hi,

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

Thanks,
Sharanya

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.

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.

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

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:07”).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:07”).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.

Hi,

Please let me know the status of this issue

Thanks,
Sharanya

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.

Hi,

What is the status of this issue?

Thanks,
Sharanya

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,

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

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.

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:07").ToOADate() + ",";

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

dateStr += DateTime.Parse("2013/9/21 23:33:07").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: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");


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

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.

Hi,


Attached is the zip file which has the input, output excel files and the project. The output chart from the project file is in the first sheet and the expected output is in the second sheet of the output excel file.

There are 2 charts generated for the 2 sets of data. Both the charts have issues.

The issues are -

1. Sometimes the chart starts from NULL date (00-Jan 1900), though the date is of correct format (As seen in the first chart)

2. The duration column data series is not shown as in the “Expected” sheet.(In both the charts)

Please suggest

Thanks,
Sharanya

Hi Sharanya,

Thanks for your posting and using Aspose.Cells.

I have created your expected chart using the following code with the latest version: Aspose.Cells
for .NET v8.1.1.3
.

You can use this code and employ it in your project.

I have attached the source xls file used in this code, output xls file with your expected chart generated by this code and screenshot for your reference.

C#


//Create workbook object from source file

Workbook workbook = new Workbook(“source.xls”);


//Access the first worksheet

Worksheet worksheet = workbook.Worksheets[0];


//Chart should span in this range

CellArea area = CellArea.CreateCellArea(“B10”, “AA27”);


//Create BarStacked chart in the above range

int idx = worksheet.Charts.Add(ChartType.BarStacked, area.StartRow, area.StartColumn, area.EndRow, area.EndColumn);

Chart chart = worksheet.Charts[idx];


//Add Chart Series

chart.NSeries.Add(“=Expected!$A$1:$B$8”, true);


//Make the first series invisible

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

chart.NSeries[0].Border.IsVisible = false;


//Make the second series with custom color and make its border invisible

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

chart.NSeries[1].Area.ForegroundColor = Color.FromArgb(192, 80, 77);

chart.NSeries[1].Border.IsVisible = false;


//Make the plot area white

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

chart.PlotArea.Area.ForegroundColor = Color.White;


//Delete the legend entries

chart.Legend.LegendEntries[0].IsDeleted = true;

chart.Legend.LegendEntries[1].IsDeleted = true;


//Set the font of the tick labels of category axis

chart.CategoryAxis.TickLabels.Font.Name = “Calibri”;

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


//Set the font of the tick labels of value axis

chart.ValueAxis.TickLabels.Font.Name = “Calibri”;

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


//Save the workbook as output file

workbook.Save(“output.xls”);


The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan