Creating a chart with Multiple Series

Aspose.Cells Product Family
1
Hi I am trying to create a chart with 2 series (series1 and series2 each with a date for X axis and value for Y axis). I also want to create a trend line for series 1. Here is some sample data

Series 1
Date Value
10/1 30
10/2 40
10/3 20
10/4 50
10/5 60
10/6 24
10/7 35
10/8 45
10/9 55
10/10 25


Series 2
Date Value
10/1 0
10/10 100

I want the chart to look like the attached image. In EXCEL would first create a scatter line chart using Series 1. Then I would add the second series using Add Data and giving the x axis values and Y axis values.
Can you please let me know with sample code how I can do this
thanks
ameerudeen
2

Hi,

Thanks for your posting and using Aspose.Cells.

Such a chart as you have shown in your image as well as trendline is possible to create with Aspose.Cells APIs.

Please provide us a sample excel file containing your chart as you have shown in the image, which you can create manually using Microsoft Excel. We will then look into it using Aspose.Cells APIs and provide you a sample code to create it dynamically with Aspose.Cells APIs. Thanks for your cooperation in this regard.

3

Hi,

I have attached the excel sheet in this mail… Please Check it… Please Send the Chart code to me as early as possible…And Saving option is Word Document for chart…


Thanks

M.Ameerudeen.

4

Hi,

Thanks for your posting and using Aspose.Cells.

Please use the following sample code for your needs. I have created exact replica of your chart. Please check the output excel file generated with this code. However, when I save the chart to pdf or png formats, the chart does not look exact replica. Please check the pdf chart and png chart generated with this code. The code also adds Trendline inside your chart series.

You can also download and execute the Visual Studio sample project attached with this post.

FYI: The chart is created by observing the different values or properties of your existing chart in Debug or Watch or Quick Watch windows. You should always create your chart manually using Microsoft Excel and then observe its different values or properties via Aspose.Cells APIs and then create the replicate of it using Aspose.Cells APIs.

C#

//To get index values
int idx = 0;

//Load your source workbook
Workbook wb = new Workbook(“Chart.xlsx”);

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Add the replica of your excel chart
idx = ws.Charts.Add(ChartType.ScatterConnectedByLinesWithDataMarker, 30, 1, 44, 12);
Chart ch = ws.Charts[idx];

//Add first series and also add trend line inside it
idx = ch.NSeries.Add("=Sheet1!$O$12:$O$21", true);
Series srs = ch.NSeries[idx];
srs.XValues = “=Sheet1!$N$12:$N$21”;

//Add trendline in first series
idx = srs.TrendLines.Add(TrendlineType.Linear);
Trendline tl = srs.TrendLines[idx];

//Add second series
idx = ch.NSeries.Add("=Sheet1!$S$12:$S$13", true);
srs = ch.NSeries[idx];
srs.XValues = “=Sheet1!$R$12:$R$13”;

//Set the different properties of Value Axis
ch.ValueAxis.MinValue = 0;
ch.ValueAxis.MaxValue = 100;
ch.ValueAxis.MinorUnit = 20;
ch.ValueAxis.AxisBetweenCategories = false;

//Make the plot area transparent and clear its color
ch.PlotArea.Area.Transparency = 1;

//Also take the chart image and save as pdf
ch.ToImage(“outChart.png”);
ch.ToPdf(“outChart.pdf”);

//Save the output excel file with the replica of your chart
wb.Save(“outChart.xlsx”);
5
That worked as an example thanks. However, I still need help. Your example assumes that the data is prepopulated in the EXCEL sheet, I know where it starts and ends and and then plots it.

In my real case, I do not know until runtime what data is available until I read it from the database.

Is there a way to just create the chart so I can insert it directly into an Aspose. Word document reading the data from a datatable.

Here is the scenario (Code attached and result word document attached).
I have 2 data tables - one table for each series of data I want to plot.
The X values for both series are dates
The Y values for both series are values to be plotted

I am able to plot the first series and the trend line. However I still have 2 problems:

1) the dates do not appear on the X axis as labels (just 1,2,3..)
2) My second series automatically draw a line for at (x,y) 1,0 to (x,y) 2,90 instead of the first and last date (8,90) as you can see in the data series in the code


If the only way to do this is using an Excel sheet then how do I programmatically insert the data into the Excel Sheet from the datatables, read the range of values for both series , plot them and use the result chart automatically in my word document I am trying to generate

Please help
thanks
ameerudeen
6

Hi,

Thanks for your posting and using Aspose.Cells.

You can create your charts manually using Microsoft Excel and then change its data by importing data to worksheet where your chart data resides. This way, your chart will reflect the new data instead of the old data.

Please see the following sample code, its source excel file, output excel file and screenshot for your reference.

Please note, I have obtained the new data from worksheet into a data table, but you can get it from database.

For importing and exporting data to/from worksheets, please see these articles. You can also put a cell value using Cell.PutValue() method.

Import Export data from document
Add and Retrieve Data

C#

//Load your source workbook which contains your chart
Workbook wb = new Workbook(“source.xlsx”);

//Access the chart sheet and data sheet.
Worksheet wsChartSheet = wb.Worksheets[“ChartSheet”];
Worksheet wsDataSheet = wb.Worksheets[“DataSheet”];

//Export your data from data sheet into a data table
//You can get this data from DataBase
DataTable dt = wsDataSheet.Cells.ExportDataTable(0, 0, 12, 3, true);
dt.TableName = “myData”;

//Now import your data to your chart sheet data
wsChartSheet.Cells.ImportDataTable(dt, true, 0, 0, false);

//Save excel file to disk
wb.Save(“output.xlsx”, SaveFormat.Xlsx);
7
Hi Shakeel
Thanks for your help.
But this is not answering my question and does not give me what I need.
Please see what I need described below.

Your example assumes the same number of items in all 3 series. This is not my situation. I have only 2 items in one of the series and the other 2 can have multiple items. However, all series have the same start and end for the X axis although they may not have the same number of items in the series



I have re-attached your sample EXCEL with the chart I need. Please see the datasheet and the chart sheet where I have color coded each series.
Please help.

If you look at this example

1) I have 3 series (T1,T2 and T3) . They all have an X Axis value and a Y axis Value
(Your example assumed a Y axis value only)

2) The number of items in each of the series is not the same. Series T1 has only 2 items while series T2 and T3 has the same number of items.

3) The end points of all series are the same (They all have X axis value of 11)

I need the chart to display as shown. T1 should be a line from (X,Y) (1,0)
to (X,Y) (11,90)

This way the line will go from left to right (Scatter Line Chart)

thanks
ameerudeen






8

Hi,

Thanks for your elaboration of the issue as well as using Aspose.Cells.

I have updated all the series with the random numbers using the following code. Please check its output excel file and see if it fits your needs. Let us know your feedback.

C#

//load your source workbook
Workbook wb = new Workbook("NewoutputForAspose.xlsx");

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Create random number object
Random rnd = new Random();

//Update the values of all the series with random numbers
for(int i=1; i<12; i++)
{
Cell cell=null;
int num = 0;

//Update the series T1Y
if(i<3)
{
cell = ws.Cells[i, 1];
num = rnd.Next(1, 100);
cell.PutValue(num);
}

//Update the series T2Y
cell = ws.Cells[i, 3];
num = rnd.Next(1, 100);
cell.PutValue(num);

//Update the series T3Y
cell = ws.Cells[i, 5];
num = rnd.Next(1, 100);
cell.PutValue(num);

}

//Save the workbook in xlsx format
wb.Save(“output.xlsx”, SaveFormat.Xlsx);
9

Hi,


Here we save workbook to xlsx file format… I want this work book to word document file format…
How do i save that…
Please Reply me,

Thanks

With Regards,
M.Ameerudeen.

10

Hi,

Thanks for your posting and using Aspose.Cells.

You cannot save workbook into word document using Aspose.Cells APIs. What you can do is to get your worksheet image or chart image and then insert it into a word document using Aspose.Words APIs.

Besides, you can save your workbook into pdf format and then from pdf to word transformation can be done using Aspose.Pdf APIs.

Similarly, you can save your workbook into html format and then html to word transformation can be done using Aspose.Words APIs.

Another way is, you insert your whole workbook into word document as OLE object using Aspose.Words APIs.

For comprehensive information about all these APIs, please see their respective Product Pages .

Please see the following sample code, its source excel file and the output images generated by it. The code generates the image of whole worksheet and just chart alone. Both images are attached with this post for your reference.

C#

Workbook wb = new Workbook(“sample.xlsx”);
Worksheet ws = wb.Worksheets[0];

//Get the full worksheet image
ImageOrPrintOptions opts = new ImageOrPrintOptions();
opts.ImageFormat = ImageFormat.Png;
opts.OnePagePerSheet = true;

//You can also save to memory stream instead of file
SheetRender sr = new SheetRender(ws, opts);
sr.ToImage(0, “out.png”);

//Or you can just take the image of your chart
Chart ch = ws.Charts[0];
ch.ToImage(“chart.png”);
11

Hi,


Please
check the attached code segment. This sample imports DataTable into worksheet, create
charts (e.g Scatter chart and Line chart), generates chart images, and then
inserts them into Word document.

As the
point count of the 2 series is not the same, the type source data “Date” must
be DataTime. Create it by dtRep1.Columns.Add(“Date”, typeof(DateTime));

Thank you.