Hi,
You got any solution for that?.
I dont know how to add shape inside plot area.
In my previous post I told you how to add total data label in excel. same logic I am using in my code and from your old version of dll the total label is coming at right place but when I am doing double click its giving error “Not Enough Memory Available”.
Thanks
Saurabh
Hi,
Please see the code below. It is same as I gave it to you before. The changes have been highlighted as red.
Please see the output xlsx file and the output screenshot. It is now working fine.
C#
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 };
//below data should be sum of above, or you can set
the sum formula to the cells
dblData[3] = new double[] { 6.0, 10.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;
//Column type accepts
LabelPositionType.OutsideEnd, but ColumnStacked can’t
ser4.Type = ChartType.Column;
//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;
}
}
}
Output:
Thanks Shakeel…
now its woking for me.