Hi,
I am trying to put total datalabel on top of stacked chart. To do this when I added below line, labels are comimg fine but on double click its not going in excel mode its giving error (There isn’t enough memory available to read worksheet)
ser4.DataLabels.Position = LabelPositionType.OutsideEnd;
The slide have embedded OLE object
Thanks
Saurabh
Hi Saurabh,
I have tried to understand the issue shared by you but the information that you have shared is not enough to reproduce the issue on my end. It will be really nice if you may please share a working project code with us for further investigation on our end.
Thanks and Regards,
Ok… find the code sample attached with this reply
Hi Saurabh,
Thanks for sharing the requested information. I have worked with the code snippet shared by you and have been able to reproduce the issue as shared by you. An issue with ID 30416 has been created in our issue tracking system to further investigate that whether the issue belongs to Aspose.Slides for .NET or Aspose.Cells for .NET. We will share further information with you once the investigation is completed and issue is resolved.
We are sorry for your inconvenience,
Hi Mudassir,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Is there any update on this issue? We are using this component in the production environment and the customer in trouble because of this issue. We have license for this component, is there a priority support you provide for licensed customers? Let us know when we can expect a fix or do we have any other alternate solution to go with it
Hi Saurabh,
I like to share that we have investigated the issue and it is in fact not related to Aspose.Slides. The issue has been identified in Aspose.Cells. An issue with 30987 has already been created in our issue tracking system by Aspose.Cells team to resolve the problem. I will request Aspose.Cells support team to update you further on the issue status. Please visit this link for available support options for your kind reference.
Thanks and Regards,
Hi Mudassir,
I used new version of dll you given me (Aspose.Cells for .NET v7.0.1.7) in my application. I am not getting memory error but total labels are not going on top of bar graph.
Functionality of below line is not working in this version of dll.
ser4.DataLabels.Position = LabelPositionType.OutsideEnd;
Saurabh
Hi Saurabh,
I have worked with Aspose.Slides for .NET 5.6.0 and Aspose.Cells for .NET 7.0.1.7 and have no more observed the issue. For your kind reference, I have attached sample project based on your code snippet for your further kind reference. Please share, if the issue still prevail for you.
Thanks and Regards,
Hi Mudassir,
As I already told you, now i am not getting memory error but now i am unable to put total label on top of stacked chart , its coming inside of stacked chart area.
please check the attached screenshot which i have taken from sample project which you have given me in your last reply.
Thanks
Saurabh
Hi Saurabh,
I have observed the inconsistency in image attached and like to share that the generated excel sheet also contains the same effect. The issue seems to be on Aspose.Cells end as Aspose.Slides is only responsible for displaying chart in Ole frame and that it is doing correctly. So, with you permission, I am moving this thread to Aspose.Cells forum, where respective support team will help you further in this regard.
Thanks and Regards,
Hi,
Please try the latest version:
Aspose.Cells for .NET v7.0.1.7 and let us know your results.
If the problem persists, then please provide us you simple sample runnable application replicating the issue along with the source xls/xlsx files if you are using any.
Please remove all your Business, Database and Aspose.Slides dependent code and instead use hardcoded values. It will help us quickly find out and fix the bug at our end.
Hi,
Yesterday I already tried this version. Mudassir given me this version of dll, its not working for me.
Sample code you can find out in my first post of this thread.
Thanks
Saurabh
Okay… Check the attached sample code
Hi,
Thanks for your code. We will let you know after looking into it asap.
Hi,
I have run the following code based on your code. I have removed all Aspose.Slides and your Business related code and used hard coded values. I got the following output image as attached by me.
Now, please highlight all the problems in a screenshot with red circles and provide me xls/xlsx file with your desired chart which you can create manually using Ms-Excel and post here.
PS:
You can also download and correct the out.jpg.xlsx file attached by me manually using Ms-Excel and post here.
Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Drawing;
using System.Drawing.Imaging;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.IO.Compression;
using Aspose.Cells;
using Aspose.Cells.Rendering;
using Aspose.Cells.Charts;
using Aspose.Cells.Pivot;
using Aspose.Cells.Drawing;
namespace Saurabh
{
class Program
{
public static void Run()
{
Program pg = new Program();
pg.OleChartCode();
}
public void OleChartCode()
{
int chartRows = 5;
int chartCols = 5;
Workbook wbChart8 = new Workbook();
//Add an excel chart
int chartSheetIndex8 = AddTotalCostOwnrshpAvgSerWrkLoadExcelChartInWorkbookEx(wbChart8, chartRows, chartCols);
//Set the OLE size of the chart
wbChart8.Worksheets.SetOleSize(10, chartRows, 10, chartCols);
wbChart8.Worksheets[chartSheetIndex8].Charts[0].ChartArea.TextFont.Name = “Arial”;
Bitmap imgChart8 = wbChart8.Worksheets[chartSheetIndex8].Charts[0].ToImage();
//Save the workbook to stream
imgChart8.Save(@“F:\Shak-Data-RW\Downloads\Out.jpg”, ImageFormat.Jpeg);
}
public int AddTotalCostOwnrshpAvgSerWrkLoadExcelChartInWorkbookEx(Workbook wb, int chartRows, int chartCols)
{
//Add a new worksheet to populate cells with data
int dataSheetIdx = wb.Worksheets.Add();
Worksheet dataSheet = wb.Worksheets[dataSheetIdx];
string sheetName = “TotalCostOfOwnershipAvgServer”;
dataSheet.Name = sheetName;
double[][] dblData = new double[4][];
dblData[0] = new double[] { 2.0, 3.5 };
dblData[1] = new double[] { 2.0, 3.5 };
dblData[2] = new double[] { 2.0, 3.5 };
dblData[3] = new double[] { 2.0, 3.5 };
dataSheet.Cells[0, 0].PutValue(@“X\Y”);
dataSheet.Cells[0, 1].PutValue(“Physical”);
dataSheet.Cells[0, 2].PutValue(“Virtualized”);
dataSheet.Cells[1, 0].PutValue(“CapEx”);
dataSheet.Cells[2, 0].PutValue(“OpEx”);
dataSheet.Cells[3, 0].PutValue(“Other”);
for (int i = 1; i < 5; i++)
for (int j = 1; j < 3; j++)
{
try
{
dataSheet.Cells[i, j].PutValue(dblData[i - 1][j - 1]);
}
catch (Exception ex)
{
dataSheet.Cells[i, j].PutValue(dblData[i - 1][j - 1]);
}
}
//Formatting Chart Data
Aspose.Cells.Style style = new Aspose.Cells.Style();
Cells cells = dataSheet.Cells;
Range range = cells.CreateRange(0, 0, 3, 1);
StyleFlag flag = new StyleFlag();
flag.All = true;
style.Font.IsBold = true;
range.ApplyStyle(style, flag);
cells[0, 1].SetStyle(style);
cells[0, 2].SetStyle(style);
Aspose.Cells.Style style1 = new Aspose.Cells.Style();
style1.Font.Color = Color.FromArgb(99, 37, 35);
cells[1, 0].SetStyle(style1);
cells[1, 1].SetStyle(style1);
cells[1, 2].SetStyle(style1);
Aspose.Cells.Style style2 = new Aspose.Cells.Style();
style2.Font.Color = Color.FromArgb(149, 55, 53);
cells[2, 0].SetStyle(style2);
cells[2, 1].SetStyle(style2);
cells[2, 2].SetStyle(style2);
Aspose.Cells.Style style3 = new Aspose.Cells.Style();
style3.Font.Color = Color.FromArgb(217, 150, 148);
cells[3, 0].SetStyle(style3);
cells[3, 1].SetStyle(style3);
cells[3, 2].SetStyle(style3);
//Add a chart sheet
int chartSheetIdx = wb.Worksheets.Add(SheetType.Chart);
Worksheet chartSheet = wb.Worksheets[chartSheetIdx];
chartSheet.Name = “ChartSheet”;
//Add a chart in ChartSheet with data series from DataSheet
int chartIdx = 0;
chartIdx = chartSheet.Charts.Add(Aspose.Cells.Charts.ChartType.ColumnStacked, 0, 0, chartRows, chartCols);
Chart chart = chartSheet.Charts[chartIdx];
chart.SizeWithWindow = true;
for (int rows = 2; rows <= 5; rows++)
chart.NSeries.Add(sheetName + “!B” + rows.ToString() + “:C” + rows.ToString(), false);
Aspose.Cells.Charts.Legend legend = chart.Legend;
legend.Position = Aspose.Cells.Charts.LegendPositionType.Bottom;
chart.PlotArea.Area.Formatting = FormattingType.None;
chart.PlotArea.BackgroundMode = BackgroundMode.Transparent;
legend.Width = 400;
legend.Height = 18;
legend.Border.Style = LineType.Solid;
legend.TextFont.Size = 12;
chart.PlotArea.Border.IsVisible = false;
//Set a text
// chart.ValueAxis.DisplayUnit = DisplayUnitType.Thousands;
chart.ValueAxis.IsDisplayUnitLabelShown = false;
chart.ValueAxis.TickLabels.NumberFormat = System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencySymbol + “#0.0,0”;
chart.SecondValueAxis.TickLabels.NumberFormat = “% 0”;
chart.CategoryAxis.TickLabels.NumberFormat = “#0”;
chart.PlotArea.Area.BackgroundColor = Color.White;
chart.NSeries[0].Area.ForegroundColor = ColorTranslator.FromHtml("#FF4d4d4d");// Color.FromArgb(99, 37, 35);
chart.NSeries[1].Area.ForegroundColor = ColorTranslator.FromHtml("#FF949494");//Color.FromArgb(149, 55, 53);
chart.NSeries[2].Area.ForegroundColor = ColorTranslator.FromHtml("#FFc9c9c9");//Color.FromArgb(217, 150, 148);
chart.NSeries[0].Points[1].Area.ForegroundColor = ColorTranslator.FromHtml("#FF003d79");//Color.FromArgb(37, 64, 97);
chart.NSeries[1].Points[1].Area.ForegroundColor = ColorTranslator.FromHtml("#FF0095d3");//Color.FromArgb(55, 96, 146);
chart.NSeries[2].Points[1].Area.ForegroundColor = ColorTranslator.FromHtml("#FF7fcae9");//Color.FromArgb(149, 179, 215);
chart.NSeries.CategoryData = “=‘TotalCostOfOwnershipAvgServer’!$B$1:$C$1”;
chart.ValueAxis.MajorGridLines.Transparency = .60;
//Set properties of chart title
chart.Title.Text = “Total Cost of Ownership: Average per Server Workload per Hour \n (excluding any desktop workloads in analysis)”;
chart.Title.TextFont.Color = Color.Black;
chart.Title.TextFont.IsBold = true;
chart.Title.TextFont.Size = 12;
chart.Title.TextFont.Name = “Arial”;
chart.ValueAxis.TickLabels.Font.Name = “Arial”;
chart.CategoryAxis.TickLabels.Font.Name = “Arial”;
chart.PlotArea.TextFont.Name = “Arial”;
//Set properties of valueaxis title
Axis valueAxis = chart.ValueAxis;
valueAxis.TickLabels.Font.Size = 10;
valueAxis.TickLabels.Font.Name = “Arial”;
//Set properties of Axis(categoryaxis) title
Axis categoryAxis = chart.CategoryAxis;
categoryAxis.TickLabels.Font.Size = 10;
categoryAxis.TickLabels.Font.Name = “Arial”;
legend.Position = LegendPositionType.Bottom;
Aspose.Cells.Charts.Series ser1 = chart.NSeries[0];
Aspose.Cells.Charts.Series ser2 = chart.NSeries[1];
Aspose.Cells.Charts.Series ser3 = chart.NSeries[2];
Aspose.Cells.Charts.Series ser4 = chart.NSeries[3];
categoryAxis.TickLabelPosition = TickLabelPositionType.Low;
categoryAxis.CategoryType = CategoryType.CategoryScale;
ser4.PlotOnSecondAxis = true;
//ser4.DataLabels.X = 10;
//ser4.DataLabels.Y = -1000;
ser1.GapWidth = 55;
ser2.GapWidth = 55;
ser3.GapWidth = 55;
ser4.GapWidth = 55;
ser4.LegendEntry.IsDeleted = true;
DataLabels datalabels;
for (int i = 0; i < chart.NSeries.Count; i++)
{
datalabels = chart.NSeries[i].DataLabels;
//Set the position of DataLabels
if (i != 3)
datalabels.Position = LabelPositionType.Above;
//else
//{
// ser4.DataLabels.TextHorizontalAlignment = TextAlignmentType.Top;
//}
datalabels.NumberFormat = System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencySymbol + “#0.0,0”;
//Show the category name in the DataLabels
datalabels.ShowCategoryName = false;
//Show the value in the DataLabels
datalabels.ShowValue = true;
//Not show the percentage in the DataLabels
datalabels.ShowPercentage = false;
//Not show the legend key.
datalabels.ShowLegendKey = false;
}
ser1.Name = “CapEx”;
ser2.Name = “OpEx”;
ser3.Name = “Other”;
//ser4.Area.FillFormat.SetType = FormatSetType.None;
ser4.DataLabels.Position = LabelPositionType.OutsideEnd;
ser4.Area.Formatting = FormattingType.None;
//ser4.DataLabels.X = 10;
//ser4.DataLabels.Y = -1000;
chart.SecondValueAxis.IsVisible = false;
chart.PlotArea.Border.IsVisible = false;
chart.PlotArea.Area.Formatting = FormattingType.None;
chart.PlotArea.BackgroundMode = BackgroundMode.Transparent;
chart.PlotArea.TextFont.Size = 10;
chart.PlotArea.TextFont.Name = “Arial”;
chart.PlotArea.Border.Transparency = 1.0;
//Set ChartSheet an active sheet
wb.Worksheets.ActiveSheetIndex = chartSheetIdx;
return chartSheetIdx;
}
}
}
Ouput Image:
Hi mshakeel,
Please check the attachment.
I want totals labels at same position where i have added in excel file. and this chart should be OLEobject when I double click it should go in excel mode and datalabel position should not be change.
Thanks
Saurabh
Hi,
I am interested to know, how did you generate using Ms-Excel. Was this automatically generated by Excel or you manually added Textboxes and calculated Sum.
Manually added.
Hi,
I am afraid, Aspose.Cells can add textboxes inside the chart but can’t calculate the exact position that you want. Rather, Aspose.Cells will give you access to Shape.Left and Shape.Top properties and you will have to adjust the textboxes yourself using these properties.
Please see the following document for your reference.
Working with Controls
<!–[if gte mso 10]>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Calibri","sans-serif";}
<![endif]–><span style=“font-size:11.0pt;font-family:“Calibri”,“sans-serif”;
mso-fareast-font-family:Calibri;mso-fareast-theme-font:minor-latin;mso-ansi-language:
EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>Hi ,
You can add total label in excel by creating total value on secondary y-axis, and then hiding
the formatting for the bars and secondary y-axis.
Thanks
Saurabh