How to render charts in different sheet

Aspose.Cells Product Family
1

Hello,

I am using Aspose.Cells and Aspose.Charts to render chart in my application. I am able to generate excel sheet with my list data and charts in same sheet according to given chartindex and Nseries range. now we have requirement that list will be render in 1st sheet of the workbook and in 2nd sheet there will be charts as per the data of the 1st sheet. In that case how can i add chart in another sheet and give range according to 1st sheet columns? my sample code is as follows.

Workbook workbook = new Workbook(“E:\CRMReportList.xlsx”);

//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

worksheet.Cells.ImportDataTable(dt, true, “A1”);

Worksheet worksheet2 = workbook.Worksheets[1];
int chartIndex = worksheet2.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 10, 0, 25, 10);
Aspose.Cells.Charts.Chart chart = worksheet2.Charts[chartIndex];
chart.NSeries.Add(“B2:C6”, true);//Not sure. Here i want to give range of 1st sheet

worksheet.AutoFitRows();
worksheet.AutoFitColumns();
workbook.Save(“E:\CRMReportList.xlsx”);

Any prompt replies would be appreciated.

Thanks & Regards
Ashish Rajguru

2

Hi Ashish,


Thank you for contacting Aspose support.

You can specify the data range from another worksheet as “worksheet name!data range”. Please check the following piece of code for better elaboration.

C#

//Instantiating a Workbook object
Aspose.Cells.Workbook workbook = new Workbook();

//Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();

//Obtaining the reference of the newly added worksheet by passing its sheet index
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[sheetIndex];

//Adding a sample value to “A1” cell
worksheet.Cells[“A1”].PutValue(50);

//Adding a sample value to “A2” cell
worksheet.Cells[“A2”].PutValue(100);

//Adding a sample value to “A3” cell
worksheet.Cells[“A3”].PutValue(150);

//Adding a sample value to “A4” cell
worksheet.Cells[“A4”].PutValue(200);

//Adding a sample value to “B1” cell
worksheet.Cells[“B1”].PutValue(60);

//Adding a sample value to “B2” cell
worksheet.Cells[“B2”].PutValue(32);

//Adding a sample value to “B3” cell
worksheet.Cells[“B3”].PutValue(50);

//Adding a sample value to “B4” cell
worksheet.Cells[“B4”].PutValue(40);

//Adding a sample value to “C1” cell as category data
worksheet.Cells[“C1”].PutValue(“Q1”);

//Adding a sample value to “C2” cell as category data
worksheet.Cells[“C2”].PutValue(“Q2”);

//Adding a sample value to “C3” cell as category data
worksheet.Cells[“C3”].PutValue(“Y1”);

//Adding a sample value to “C4” cell as category data
worksheet.Cells[“C4”].PutValue(“Y2”);

//Adding a another worksheet
Aspose.Cells.Worksheet chartSheet = workbook.Worksheets[workbook.Worksheets.Add()];

//Adding a chart to the newly added worksheet
int chartIndex = chartSheet.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 5, 0, 15, 5);

//Accessing the instance of the newly added chart
Aspose.Cells.Charts.Chart chart = chartSheet.Charts[chartIndex];

//Adding SeriesCollection (chart data source) to the chart ranging from “A1” cell to “B4”
chart.NSeries.Add(worksheet.Name + “!A1:B4”, true);

//Setting the data source for the category data of SeriesCollection
chart.NSeries.CategoryData = “C1:C4”;

//Saving the Excel file
workbook.Save(“D:\book1.xls”);

Please note. we have tested the code snippet against the latest version of Aspose.Cells for .NET 8.3.1.
3

Hello Raza,

Thanks for the prompt reply. it worked for me. now i am able to generate table/list data and charts in different sheets. Now i have issue with rendering data into cells so as chart as well. I have datatable in which there are 3 columns. 1 column is string and another 2 columns should be integer. I have converted both 2 columns as integer explicitly but i guess while importing whole datatable into sheet it put as string. so because of this my chart is also not generating properly. after double click of my cell, it started reflecting to another sheet. Please refer my code and attached sheet.

//Following list is sharepoint list item collection
ListItemCollection Reportitems = ReportList.GetItems(query);


DataTable table = new DataTable();
table.Columns.Add(“Title”);
table.Columns.Add(“Range”);
table.Columns.Add(“EBITDARange”);

//I am adding sharepoint list item collection to datatable
foreach (ListItem item in Reportitems)
table.Rows.Add(item[“Title”], Convert.ToInt16(item[“Range”].ToString()), Convert.ToInt16(item[“EBITDARange”].ToString()));

Worksheet worksheet = workbook.Worksheets[0];

worksheet.Cells.ImportDataTable(table, true, “A1”);

Please find attached sheet for the imported datatable and charts.


Thanks & Regards
Ashish Rajguru

4

Hi,


Well, you should choose appropriate ImportDataTable() overload so your so called string represented numeric values should be converted to numbers. Well, you may change the line of code:
i.e.,
worksheet.Cells.ImportDataTable(table, true, “A1”);

to:
worksheet.Cells.ImportDataTable(table, true, 0, 0, true, true); //put “true” for the last Boolean parameter “convertStringToNumber”.


Let us know if you still find any issue.

Thank you.