Hi,
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,
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”);
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,
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,
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,
Any updates on the below issue. This is opened for long time with no resolution.
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 ,
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,
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