This example shows how to use Aspose.Report for .NET and Aspose.Cells to create a chart from data stored in an Excel spreadsheet. This task requires only a few lines of code, but this article runs for several pages as it includes detailed explanations and screenshots.

The following concepts are demonstrated:

• License Aspose components.
• Retrieve data from a Microsoft Excel file into a DataTable.
• Create a chart from data in a DataTable.
• Combine different chart types (bar and line) on the same chart.
• Serve the chart as an image in an HTML page.

 

Source Data in Microsoft Excel

In this example, the table in an Excel spreadsheet contains the number of sales and total sales value (in millions) for apartments sold in Auckland CBD over several of years.

 

 

Test Chart in Microsoft Excel

To test the data and to get a better idea of what we want to achieve, I created a chart in Microsoft Excel. This is not the end result of our work, it is just a chart we created in Microsoft Excel for illustration purposes.

Note, it is not possible to use Aspose.Cells to draw a chart image like this. We will use Aspose.Cells to extract data from the Excel spreadsheet and use Aspose.Report for .NET to create the chart image.

In this chart, Number of Sales per year is shown as blue bars and associated with the primary Y axis on the left. Total Sales Volume per year is shown as a red curve and associated with the secondary Y axis on the right. The chart also has a title at the top and a legend at the bottom.

 

 

Create the Project in Visual Studio .NET

1. In Visual Studio, click File/New/Project and select ASP.NET Web Application project.
2. Give a name to the project, I called it Aspose.Report.Integration.Cells. Click OK.
3. Add references to Aspose.Chart and Aspose.Cells components.
4. Delete the WebForm1.aspx created by default.

The end result should look like this:

 

 

Add Main Form to the Project

1. Add a new Web Form called Main.aspx by clicking on the Project/Add Web Form menu.
2. Drag an Image control from the Toolbox on to the form.
3. Name the control ChartImage in the Properties window.
4. Clear image width and height values in the Properties window, this will stop the HTML image from scaling the chart to a preset size.

The end result should look like this:

 

 

License Aspose Components

Normally, Aspose components can be used without a license in evaluation mode. When in evaluation mode, Aspose components inject a watermark text into their output, but some of the components also insert random garbage text into the output.

For the purposes of this demo, we need to use Aspose.Cells with a license, otherwise it will inject random garbage text into the spreadsheet and it will damage our data table and cause the code to fail intermittently because the data values could no longer be parsed by Aspose.Report for .NET.

I normally copy the license file(s) into the Bin folder of the application, then I can pass just a file name without a path into the SetLicense method.

Add the following code to Main.aspx.cs:

[C#]

 

private void LicenseAsposeComponents()

{

    //License Aspose.Cells

    //Note we cannot really run this demo without

    //Aspose.Cells license since if it is in

    //evaluation mode it will inject random

    //garbage into the spreadsheet and it will

    //damage our data.

    Aspose.Cells.License cellsLicense =

            new Aspose.Cells.License();

    cellsLicense.SetLicense("Aspose.Cells.lic");

 

    //License Aspose.Report for .NET

    Aspose.Report.License chartLicense =

            new Aspose.Report.License();

    chartLicense.SetLicense("Aspose.Report.lic");

}

 

Add the following code to Main.aspx.vb:

[VB.NET]

 

Private Sub LicenseAsposeComponents()

 

    'License Aspose.Cells

    'Note we cannot really run this demo without

    'Aspose.Cells license since if it is in

    'evaluation mode it will inject random

    'garbage into the spreadsheet and it will

    'damage our data.

    Dim cellsLicense As Aspose.Cells.License =

            New Aspose.Cells.License()

    cellsLicense.SetLicense("Aspose.Cells.lic")

 

    'License Aspose.Report for .NET

    Dim chartLicense As Aspose.Report.License =

            New Aspose.Report.License()

    chartLicense.SetLicense("Aspose.Report.lic")

End Sub

 

Notice in the above code how Aspose.Cells and Aspose.Report for .NET have their own namespaces Aspose.Cells and Aspose.Report for .NET respectively and how both of the components have their own License classes that need to be initialized.

 

Extract Data From an Excel Spreadsheet

Aspose.Cells allows to open, modify and save files in Microsoft Excel format. We use Aspose.Cells to open a spreadsheet file and programmatically retrieve data we need to build our chart.

Add the following code to Main.aspx.cs:

[C#]

 

using Aspose.Cells;

...

 

private DataTable GetAucklandApartmentSalesData()

{

    //Load an Excel spreadsheet using Aspose.Cells.

    Workbook workbook = new Workbook();

    string excelFile = Path.Combine(MapPath("."),

            "Aspose.Chart.Integration.Cells.xls");

    workbook.Open(excelFile);

 

    //This worksheet contains the data table we

    //want to create a chart for.

    Worksheet ws = workbook.Worksheets["Sales Data"];

 

    //Table starts in this row.

    const int StartRow = 2;

    //Table starts in this column.

    const int StartColumn = 0;       

    //Total 3 columns in the table.

    const int ColumnCount = 3;       

 

    //Detect last data row using

    //Aspose.Cells functions.

    int dataEndRow =

      ws.Cells.EndCellInColumn(StartColumn).Row;

 

    //The best approach is to get the data from

    //the spreadsheet as a DataTable and then

    //bind Series to it. Alternatively you can

    //iterate through the cells of the

    //spreadsheet in a loop and

    //add data points one by one.

    int rowCount = dataEndRow - StartRow + 1;

    return ws.Cells.ExportDataTable(StartRow,

       StartColumn, rowCount, ColumnCount, true);

}

 

Add the following code to Main.aspx.vb:

[VB.NET]

 

Imports Aspose.Cells

 

'...

 

Private Function GetAucklandApartmentSalesData() As DataTable

 

    'Load an Excel spreadsheet using Aspose.Cells.

    Dim workbook As Workbook = New Workbook()

    Dim excelFile As String = Path.Combine(MapPath("."),

            "Aspose.Chart.Integration.Cells.xls")

    workbook.Open(excelFile)

 

    'This worksheet contains the data table we

    'want to create a chart for.

    Dim ws As Worksheet = workbook.Worksheets("Sales Data")

 

    'Table starts in this row.

    Const StartRow As Integer = 2

    'Table starts in this column.

    Const StartColumn As Integer = 0

    'Total 3 columns in the table.

    Const ColumnCount As Integer = 3

 

    'Detect last data row using

    'Aspose.Cells functions.

    Dim dataEndRow As Integer =

    ws.Cells.EndCellInColumn(StartColumn).Row()

 

    'The best approach is to get the data from

    'the spreadsheet as a DataTable and then

    'bind Series to it. Alternatively you can

    'iterate through the cells of the

    'spreadsheet in a loop and

    'add data points one by one.

    Dim rowCount As Integer = dataEndRow - StartRow + 1

    Return ws.Cells.ExportDataTable(StartRow,

       StartColumn, rowCount, ColumnCount, True)

End Function

 

See comments in the code above. Here are some additional tips:

• I saved the spreadsheet with the data as Aspose.Chart.Integration.Cells.xls into the project folder, therefore I use MapPath(".") to create a full file name for Aspose.Cells to be able to load the file.
• The indexes of rows and columns in Aspose.Cells are 0-based and I defined the constants to make the code more readable.
• Usually, our code would not know upfront many rows are in the data table in the spreadsheet, therefore we use the Cells.EndCellInColumn method to find the last occupied cell. This lets us find out how many rows are in the table.
• The whole business of extracting data is encapsulated into a function that returns a DataTable. The DataTable is actually created by a very useful ExportDataTable method provided by Aspose.Cells. The returned DataTable has three columns: Year, Number of Sales and Total Sales Value.

 

Create a Chart From DataTable

To keep things modular, I created another function CreateAucklandApartmentSalesChart. This function takes a DataTable and returns a Chart object ready for rendering.

Add the following code to Main.aspx.cs:

 

[C#]

 

/// <summary>

/// The table needs to contain three columns: Year, Number of

/// Sales, Total Sales Volume.

/// </summary>

private Chart CreateAucklandApartmentSalesChart(DataTable table)

{

    //Create the chart

    Chart chart = new Chart();

    chart.Width = 600;

    chart.Height = 450;

    chart.SmoothingMode = SmoothingMode.Default;

 

    Title title = new Title();

    title.Text = "Auckland CBD Apartment Sales";

    chart.Titles.Add(title);

 

    ChartArea chartArea = chart.ChartArea;

 

    //Specify the legend

    chartArea.LegendBox.LegendPositionType =

            LegendPositionType.Bottom;

    chartArea.LegendBox.LayoutType = LayoutType.Row;

 

    //Specify formatting for the Y axes

    chartArea.AxisY.IsMajorGridVisible = false;

    chartArea.AxisY.DefaultLabel.Format = "G0";

    chartArea.AxisY2.IsMajorGridVisible = false;

    chartArea.AxisY2.DefaultLabel.Format = "C0";

 

 

    //Create the Number of Sales series.

    Series salesCountSeries = new Series();

    salesCountSeries.Name = "Number of sales (LH)";

    salesCountSeries.ChartType = ChartType.Bar;

 

    //Load data from the data table and

    //add the series to the chart.

    salesCountSeries.DataPoints.DataBindXY(table.Rows,

            "Year", table.Rows, "Number of Sales");

    chart.SeriesCollection.Add(salesCountSeries);

 

 

    //Create the Total Sales Value

    //series and add to the chart.

    Series salesValueSeries = new Series();

    salesValueSeries.Name = "Total sales, $m (RH)";

    salesValueSeries.ChartType = ChartType.Curve;

    salesValueSeries.IsPrimaryAxisY = false;

    salesValueSeries.DefaultDataPoint.BorderWidth = 10;

    salesValueSeries.DefaultDataPoint.Color = Color.Red;

 

    //Load data from the data table and

    //add the series to the chart.

    salesValueSeries.DataPoints.DataBindXY(table.Rows,

            "Year", table.Rows, "Total Sales Value");

    chart.SeriesCollection.Add(salesValueSeries);

 

 

    //Format the X axis. Ideally this should

    //be done before series are added.

    Axis axisX = chartArea.AxisX;

    axisX.IsMajorGridVisible = false;

    axisX.DefaultLabel.Format = "G0";

    axisX.MinorTickMark.IsVisible = false;

    //Lets use manual settings for the X axis here.

    axisX.IsAutoCalc = false;

    axisX.Interval = 1;

 

    //This is somewhat a hack to give the labels

    //on the X axis the look that we want.

    int minYear = Convert.ToInt32(

       table.Rows[0].ItemArray[0]);

    chartArea.AxisX.Minimum = minYear - 1;

    int maxYear = Convert.ToInt32(

       table.Rows[table.Rows.Count - 1].ItemArray[0]);

    axisX.Maximum = maxYear + 1;

    axisX.AxisLabels[0].Text = "";

    axisX.AxisLabels[axisX.AxisLabels.Count - 1].Text = "";

 

    return chart;

}

 

Add the following code to Main.aspx.vb:

[VB.NET]

 

' <summary>

' The table needs to contain three columns: Year, Number of

' Sales, Total Sales Volume.

' </summary>

Private Function CreateAucklandApartmentSalesChart(ByVal table As DataTable) As Chart

 

    'Create the chart

    Dim chart As Chart = New Chart()

    chart.Width = 600

    chart.Height = 450

    chart.SmoothingMode = SmoothingMode.Default

 

    Dim title As Title = New Title()

    title.Text = "Auckland CBD Apartment Sales"

    chart.Titles.Add(title)

 

    Dim chartArea As ChartArea = chart.ChartArea

 

    'Specify the legend

    chartArea.LegendBox.LegendPositionType =

    LegendPositionType.Bottom()

    chartArea.LegendBox.LayoutType = LayoutType.Row

 

    'Specify formatting for the Y axes

    chartArea.AxisY.IsMajorGridVisible = False

    chartArea.AxisY.DefaultLabel.Format = "G0"

    chartArea.AxisY2.IsMajorGridVisible = False

    chartArea.AxisY2.DefaultLabel.Format = "C0"

 

 

    'Create the Number of Sales series.

    Dim salesCountSeries As Series = New Series()

    salesCountSeries.Name = "Number of sales (LH)"

    salesCountSeries.ChartType = ChartType.Bar

 

    'Load data from the data table and

    'add the series to the chart.

    salesCountSeries.DataPoints.DataBindXY(table.Rows,

            "Year", table.Rows, "Number of Sales")

    chart.SeriesCollection.Add(salesCountSeries)

 

 

    'Create the Total Sales Value

    'series and add to the chart.

    Dim salesValueSeries As Series = New Series()

    salesValueSeries.Name = "Total sales, $m (RH)"

    salesValueSeries.ChartType = ChartType.Curve

    salesValueSeries.IsPrimaryAxisY = False

    salesValueSeries.DefaultDataPoint.BorderWidth = 10

    salesValueSeries.DefaultDataPoint.Color = Color.Red

 

    'Load data from the data table and

    'add the series to the chart.

    salesValueSeries.DataPoints.DataBindXY(table.Rows,

            "Year", table.Rows, "Total Sales Value")

    chart.SeriesCollection.Add(salesValueSeries)

 

 

    'Format the X axis. Ideally this should

    'be done before series are added.

    Dim axisX As Axis = chartArea.AxisX

    axisX.IsMajorGridVisible = False

    axisX.DefaultLabel.Format = "G0"

    axisX.MinorTickMark.IsVisible = False

    'Lets use manual settings for the X axis here.

    axisX.IsAutoCalc = False

    axisX.Interval = 1

 

    'This is somewhat a hack to give the labels

    'on the X axis the look that we want.

    Dim minYear As Integer = CType(table.Rows(0).ItemArray(0), Int32)

    chartArea.AxisX.Minimum = minYear - 1

    Dim maxYear As Integer = CType(table.Rows(table.Rows.Count – 1).ItemArray(0),Int32)

    axisX.Maximum = maxYear + 1

    axisX.AxisLabels(0).Text = ""

    axisX.AxisLabels(axisX.AxisLabels.Count - 1).Text = ""

 

    Return chart

 

End Function

 

See comments in the code above. Here are some additional tips:

• I specified number format with no fraction for the primary Y axis and currency formatting with no fraction for the secondary Y axis.
• See how DataBindXY is used to load data points from a DataTable into the Series object. DataBindXY can load data from any data source that implements the IEnumerable interface. In this example, DataTable.Rows is a DataRowCollection that implements the IEnumerable interface. We pass the rows collection and the names of the columns to use for the X and Y values (for example column "Year" contains the X values).
• Normally, Aspose.Report for .NET can automatically determine suitable interval, maximum and minimum values for an axis from the data points (like for Y axes in this example), but in this particular example I decided to override and specify the X axis properties manually.

 

Render the Chart Image

Once you have a Chart object, it is easy to render the image of the chart. Just use the GetChartImage to retrieve a Bitmap object or use one of the Chart.Save methods to save the image into a file or stream. In our example, however, we need to render the chart as an image in an HTML page and this makes things a little trickier.

As you probably know, an image in HTML is represented by the <IMG> element with the "src" attribute that specifies the URL where to get the image from. If the image is a disk file, then it can be included like this: <IMG src=images/hp0.gif/>.

We could save the chart image to a file and then include the URL to the file in the HTML output, but there could be multiple users requesting different charts at the same time. Also, the image of the chart is generated dynamically only to satisfy a particular request and often no longer needed after the page is displayed. Clearly, it would be better if we can avoid saving all those chart images to files.

The solution is to specify an ASP.NET Web Form as a source URL for the image and make that page to generate the image and return it in an HTTP response.

To create a web form for generating chart images:

1. Click Project/Add Web Form, name the form ChartImage.aspx, click OK.
2. In ChartImage.aspx.cs add the following code:

[C#]

 

private void Page_Load(object sender, System.EventArgs e)

{

    Chart chart = (Chart)Session["Chart"];

    if (chart == null)

            return;

 

    //Save the chart image into a memory stream.

    MemoryStream ms = new MemoryStream();

    chart.Save(ms, ImageFormat.Png);

                   

    //Save the chart image from the

    //memory stream to the response.

    Response.Clear();

    Response.ContentType = "image/png";

    ms.WriteTo(Response.OutputStream);

}

 

 

[VB.NET]

 

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

 

    Dim chart As Chart = CType(Session("Chart"), Chart)

    If (chart Is Nothing) Then

        Exit Sub

    End If

 

    'Save the chart image into a memory stream.

    Dim ms As MemoryStream = New MemoryStream()

    chart.Save(ms, ImageFormat.Png)

 

    'Save the chart image from the

    'memory stream to the response.

    Response.Clear()

    Response.ContentType = "image/png"

    ms.WriteTo(Response.OutputStream)

 

End Sub

The above code executes when ChartImage.aspx is requested. If the session contains a variable called "Chart", then it is casted to a Chart object. In order to send the chart image to the browser, the image is saved in PNG format into a MemoryStream first, then appropriate content type is set and the image is written to the HTTP response stream.

 

Putting it All Together

All you need to do is add the following code to Main.aspx.cs:

[C#]

 

private void Page_Load(object sender, System.EventArgs e)

{

    //In this demo the license is set in Page_Load

    //method (on every load of the page),

    //but apparently this is waste of processor

    //time, you can do that only once

    //in Global.asax in Application_Start.

    LicenseAsposeComponents();

 

    DataTable table = GetAucklandApartmentSalesData();

 

    Chart chart =

            CreateAucklandApartmentSalesChart(table);

 

    //We use Session to pass the chart object

    //to the page that will be rendering it.

    Session["Chart"] = chart;

    //On this page we have an HTML image

    //control, specify that the image

    //source is ChartImage.aspx page.

    ChartImage.ImageUrl = "ChartImage.aspx";

}

 

 

[VB.NET]

 

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

 

    'In this demo the license is set in Page_Load

    'method (on every load of the page),

    'but apparently this is waste of processor

    'time, you can do that only once

    'in Global.asax in Application_Start.

    LicenseAsposeComponents()

 

    Dim table As DataTable = GetAucklandApartmentSalesData()

 

    Dim chart As Chart =

    CreateAucklandApartmentSalesChart(table)

 

    'We use Session to pass the chart object

    'to the page that will be rendering it.

    Session("Chart") = chart

    'On this page we have an HTML image

    'control, specify that the image

    'source is ChartImage.aspx page.

    ChartImage.ImageUrl = "ChartImage.aspx"

 

End Sub

This code just executes all the steps that we have prepared. The last step is to store the Chart object in the Session so the ChartImage.aspx will find it. We also set the ImageUrl property of the ChartImage control so it will output <IMG src=”ChartImage.aspx”/> in HTML and make the browser request the ChartImage web form. This could have been done in the Properties window, but I did it in code just to highlight it.

The resulting chart was created by Aspose.Report for .NET using data retrieved by Aspose.Cells from a Microsoft Excel spreadheet: