Problem in Area Chart during run time

Sweatha · March 21, 2012, 4:56am

Hi All,

I generated Area chart and got generated successfully only when I hard code cell values in C#.

But when I generate data table and bind to excel using (WorkSheet.Cells.ImportDataTable(dt,true,"A1")) and try to generate Area chart, it is blank.

My code is given below.

Worksheet sheet = workbook.Worksheets[0];

sheet.Cells.ImportDataTable(dtBaseLoad, true, "A1");

int chartIndex = 0;

chartIndex = sheet.Charts.Add(Aspose.Cells.ChartType.Area, 9, 1, 33, 10);

Aspose.Cells.Chart chart = sheet.Charts[chartIndex];

chart.Legend.Position = Aspose.Cells.LegendPositionType.Top;

chart.CategoryAxis.MajorGridLines.IsVisible = false;

chart.Title.Text = "Sales By Region";

chart.Title.TextFont.Color = System.Drawing.Color.Black;

chart.Title.TextFont.IsBold = true;

chart.Title.TextFont.Size = 12;

chart.NSeries.Add("B2:M6", false);

chart.NSeries.CategoryData = "B1:M1";

chart.NSeries.IsColorVaried = true;

chart.CategoryAxis.Title.Text = "Year(2002-2006)";

chart.CategoryAxis.Title.TextFont.Color = System.Drawing.Color.Black;

chart.CategoryAxis.Title.TextFont.IsBold = true;

chart.CategoryAxis.Title.TextFont.Size = 10;

chart.CategoryAxis.AxisBetweenCategories = false;

When I double click on data in excel, then the graph is getting displayed.

What could be the problem and how to resolve it.

Please advise.

Sweatha

shakeel.faiz · March 21, 2012, 5:02am

Hi,

Thanks for your posting and using Aspose.Cells for .NET

Please also provide your data table so that we could be able to run your code.

Please also download and try the latest version:
Aspose.Cells
for .NET v7.1.2.2
and see if it resolves your problem.

Sweatha · March 21, 2012, 5:13am

I couldnt use latest version since my product uses Aspose 4.5 and if I change the version that will be a greater problem. The product developed before 2 years and am restricted to change the version of aspose.

The datatable what am using is,

DataTable dtBaseLoad = new DataTable();

dtBaseLoad.Columns.Add(" ");

dtBaseLoad.Columns.Add("Apr-11");

dtBaseLoad.Columns.Add("May-11");

dtBaseLoad.Columns.Add("June-11");

dtBaseLoad.Columns.Add("July-11");

dtBaseLoad.Columns.Add("Aug-11");

dtBaseLoad.Columns.Add("Sep-11");

dtBaseLoad.Columns.Add("Oct-11");

dtBaseLoad.Columns.Add("Nov-11");

dtBaseLoad.Columns.Add("Dec-11");

dtBaseLoad.Columns.Add("Jan-12");

dtBaseLoad.Columns.Add("Feb-12");

dtBaseLoad.Columns.Add("Mar-12");

dtBaseLoad.Rows.Add("High Stop", 100, 100, 100, 69.34, 70.97, 70.49, 75.56, 81.00, 84.34, 85.86, 85.12, 83.12);

dtBaseLoad.Rows.Add("High Side Caution", 90.00, 90.00, 90.00, 65.26, 66.70, 66.19, 71.45, 77.20, 80.67, 81.88, 81.22, 78.01);

dtBaseLoad.Rows.Add("BL Average Price Achieved", 63.16, 58.56, 57.98, 57.13, 56.35, 57.16, 63.79, 65.32, 66.10, 74.10, 74.10, 74.10);

dtBaseLoad.Rows.Add("BL Market Price Close", 57.55, 56.94, 58.39, 55.25, 53.62, 54.08, 55.00, 58.31, 56.52, 57.65, 58.10, 57.50);

dtBaseLoad.Rows.Add("Low Side Caution", 1, 1, 1, 51.18, 52.23, 51.80, 56.30, 61.31, 64.30, 65.07, 64.59, 61.00);

Workbook wbAreaChart = new Workbook();

Then am importing this datatable to excel by creating new workbook and trying to generate the graph.

Once I open the excel I dont see any graph but when I double click on data on excel, the chart is getting visible.

Itz bit urgent so please help me with a solution to solve this issue.

Many thanks

shakeel.faiz · March 21, 2012, 5:46am

Hi,

Please download and use the latest version:
Aspose.Cells
for .NET v7.1.2.2

Please see the following code and the output xlsx file.

Please see the highlighted red part, where I made a change. If you can use this change with older version, then you don’t need latest version. But if you can’t then, you will have purchase and use the latest version.

C#

DataTable dtBaseLoad = new DataTable();

dtBaseLoad.Columns.Add(" ");

dtBaseLoad.Columns.Add(“Apr-11”);

dtBaseLoad.Columns.Add(“May-11”);

dtBaseLoad.Columns.Add(“June-11”);

dtBaseLoad.Columns.Add(“July-11”);

dtBaseLoad.Columns.Add(“Aug-11”);

dtBaseLoad.Columns.Add(“Sep-11”);

dtBaseLoad.Columns.Add(“Oct-11”);

dtBaseLoad.Columns.Add(“Nov-11”);

dtBaseLoad.Columns.Add(“Dec-11”);

dtBaseLoad.Columns.Add(“Jan-12”);

dtBaseLoad.Columns.Add(“Feb-12”);

dtBaseLoad.Columns.Add(“Mar-12”);

dtBaseLoad.Rows.Add(“High Stop”, 100, 100, 100, 69.34, 70.97, 70.49, 75.56, 81.00, 84.34, 85.86, 85.12, 83.12);

dtBaseLoad.Rows.Add(“High Side Caution”, 90.00, 90.00, 90.00, 65.26, 66.70, 66.19, 71.45, 77.20, 80.67, 81.88, 81.22, 78.01);

dtBaseLoad.Rows.Add(“BL Average Price Achieved”, 63.16, 58.56, 57.98, 57.13, 56.35, 57.16, 63.79, 65.32, 66.10, 74.10, 74.10, 74.10);

dtBaseLoad.Rows.Add(“BL Market Price Close”, 57.55, 56.94, 58.39, 55.25, 53.62, 54.08, 55.00, 58.31, 56.52, 57.65, 58.10, 57.50);

dtBaseLoad.Rows.Add(“Low Side Caution”, 1, 1, 1, 51.18, 52.23, 51.80, 56.30, 61.31, 64.30, 65.07, 64.59, 61.00);

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

sheet.Cells.ImportDataTable(dtBaseLoad, false, 0, 0, true, true);

int chartIndex = 0;

chartIndex = sheet.Charts.Add(Aspose.Cells.Charts.ChartType.Area, 9, 1, 33, 10);

Aspose.Cells.Charts.Chart chart = sheet.Charts[chartIndex];

chart.Legend.Position = Aspose.Cells.Charts.LegendPositionType.Top;

chart.CategoryAxis.MajorGridLines.IsVisible = false;

chart.Title.Text = “Sales By Region”;

chart.Title.TextFont.Color = System.Drawing.Color.Black;

chart.Title.TextFont.IsBold = true;

chart.Title.TextFont.Size = 12;

chart.NSeries.Add(“B2:M6”, false);

chart.NSeries.CategoryData = “B1:M1”;

chart.NSeries.IsColorVaried = true;

chart.CategoryAxis.Title.Text = “Year(2002-2006)”;

chart.CategoryAxis.Title.TextFont.Color = System.Drawing.Color.Black;

chart.CategoryAxis.Title.TextFont.IsBold = true;

chart.CategoryAxis.Title.TextFont.Size = 10;

chart.CategoryAxis.AxisBetweenCategories = false;

workbook.Save(@“F:\Shak-Data-RW\Downloads\output.xlsx”);

Sweatha · March 21, 2012, 5:58am

Thank you so much!! This fixed my problem.

We really love Aspose for its faster response and problem solving!!

Keep Rocking!!

Once again appreciate aspose team.

Sweatha · March 22, 2012, 2:03am

Hi,

Again a small problem am facing now.

If I include the symbol "$" in my data, the report is not coming.

Datatable which I used is given below.

dtBaseLoad.Rows.Add("", "Apr-11", "May-11", "June-11", "July-11", "Aug-11", "Sep-11", "Oct-11", "Nov-11", "Dec-11", "Jan-12", "Feb-12", "Mar-12");
dtBaseLoad.Rows.Add("High Stop", "$100", "$100", "$100", "$69.34", "$70.97", "$70.49", "$75.56", "$81.00", "$84.34", "$85.86", "$85.12", "$83.12");
dtBaseLoad.Rows.Add("High Side Caution","$90.00","$90.00","$90.00","$65.26","$66.70","$66.19","$71.45","$77.20","$80.67","$81.88","$81.22","$78.01");
dtBaseLoad.Rows.Add("BL Average Price Achieved","$63.16","$58.56","$57.98","$57.13","$56.35","$57.16","$63.79","$65.32","$66.10","$74.10","$74.10","$74.10");
dtBaseLoad.Rows.Add("BL Market Price Close","$57.55","$56.94","$58.39","$55.25","$53.62","$54.08","$55.00","$58.31","$56.52","$57.65","$58.10","$57.50");
dtBaseLoad.Rows.Add("Low Side Caution","$10","$10","$10","$51.18","$52.23","$51.80","$56.30","$61.31","$64.30","$65.07","$64.59","$61.00");

As you can see, I included "$" symbol before every data since my "Vertical/Value Axis", needs to show "$" symbol in front of the data.

But If I add this symbol, chart is not displaying.

Help required from your end please.

Waiting for the reply!

Thanks

Sweatha

shakeel.faiz · March 22, 2012, 11:10am

Hi,

Please see the following complete code.

I have also attached the output file and the screenshot for your reference.

The changes in this code start and end with my name.

C#

DataTable dtBaseLoad = new DataTable();

dtBaseLoad.Columns.Add(" “);

dtBaseLoad.Columns.Add(“Apr-11”);

dtBaseLoad.Columns.Add(“May-11”);

dtBaseLoad.Columns.Add(“June-11”);

dtBaseLoad.Columns.Add(“July-11”);

dtBaseLoad.Columns.Add(“Aug-11”);

dtBaseLoad.Columns.Add(“Sep-11”);

dtBaseLoad.Columns.Add(“Oct-11”);

dtBaseLoad.Columns.Add(“Nov-11”);

dtBaseLoad.Columns.Add(“Dec-11”);

dtBaseLoad.Columns.Add(“Jan-12”);

dtBaseLoad.Columns.Add(“Feb-12”);

dtBaseLoad.Columns.Add(“Mar-12”);

dtBaseLoad.Rows.Add(”", “Apr-11”, “May-11”, “June-11”, “July-11”, “Aug-11”, “Sep-11”, “Oct-11”, “Nov-11”, “Dec-11”, “Jan-12”, “Feb-12”, “Mar-12”);

dtBaseLoad.Rows.Add(“High Stop”, “$100”, “$100”, “$100”, “$69.34”, “$70.97”, “$70.49”, “$75.56”, “$81.00”, “$84.34”, “$85.86”, “$85.12”, “$83.12”);

dtBaseLoad.Rows.Add(“High Side Caution”, “$90.00”, “$90.00”, “$90.00”, “$65.26”, “$66.70”, “$66.19”, “$71.45”, “$77.20”, “$80.67”, “$81.88”, “$81.22”, “$78.01”);

dtBaseLoad.Rows.Add(“BL Average Price Achieved”, “$63.16”, “$58.56”, “$57.98”, “$57.13”, “$56.35”, “$57.16”, “$63.79”, “$65.32”, “$66.10”, “$74.10”, “$74.10”, “$74.10”);

dtBaseLoad.Rows.Add(“BL Market Price Close”, “$57.55”, “$56.94”, “$58.39”, “$55.25”, “$53.62”, “$54.08”, “$55.00”, “$58.31”, “$56.52”, “$57.65”, “$58.10”, “$57.50”);

dtBaseLoad.Rows.Add(“Low Side Caution”, “$10”, “$10”, “$10”, “$51.18”, “$52.23”, “$51.80”, “$56.30”, “$61.31”, “$64.30”, “$65.07”, “$64.59”, “$61.00”);

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

sheet.Cells.ImportDataTable(dtBaseLoad, false, 0, 0, true, true);

int chartIndex = 0;

chartIndex = sheet.Charts.Add(Aspose.Cells.Charts.ChartType.Area, 9, 1, 33, 10);

Aspose.Cells.Charts.Chart chart = sheet.Charts[chartIndex];

chart.Legend.Position = Aspose.Cells.Charts.LegendPositionType.Top;

chart.CategoryAxis.MajorGridLines.IsVisible = false;

chart.Title.Text = “Sales By Region”;

chart.Title.TextFont.Color = System.Drawing.Color.Black;

chart.Title.TextFont.IsBold = true;

chart.Title.TextFont.Size = 12;

chart.NSeries.Add(“B2:M6”, false);

chart.NSeries.CategoryData = “B1:M1”;

chart.NSeries.IsColorVaried = true;

chart.CategoryAxis.Title.Text = “Year(2002-2006)”;

chart.CategoryAxis.Title.TextFont.Color = System.Drawing.Color.Black;

chart.CategoryAxis.Title.TextFont.IsBold = true;

chart.CategoryAxis.Title.TextFont.Size = 10;

chart.CategoryAxis.AxisBetweenCategories = false;

//Shakeel Work - Begin

Range rng = sheet.Cells.CreateRange(“B2:M6”);

IEnumerator e = rng.GetEnumerator();

while (e.MoveNext())
{
Cell cell = e.Current as Cell;

string strVal = cell.StringValue.Replace("$", “”);

cell.PutValue(strVal, true);

Style st = cell.GetStyle();
st.Custom = “”$"#,##0";
cell.SetStyle(st);

}

//Shakeel Work - End

workbook.Save(@“F:\Shak-Data-RW\Downloads\output-final.xlsx”);

Screenshot: