This example shows how to use Aspose.Chart 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.Chart 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.Chart.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.Chart.

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.

[C#]

 

Add the following code to Main.aspx.cs:

 

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.Chart

        Aspose.Chart.License chartLicense =

                new Aspose.Chart.License();

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

}

 

 

[VB.NET]

 

Add the following code to Main.aspx.vb:

 

 

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.Chart

        Dim chartLicense As Aspose.Chart.License =

                New Aspose.Chart.License()

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

End Sub

 

Notice in the above code how Aspose.Cells and Aspose.Chart have their own namespaces Aspose.Cells and Aspose.Chart 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.

[C#]

 

Add the following code to Main.aspx.cs:

 

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);

}

 

 

[VB.NET]

 

Add the following code to Main.aspx.vb:

 

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.

 

[C#]

 

Add the following code to Main.aspx.cs:

 

/// <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;

}

 

 

[VB.NET]

 

Add the following code to Main.aspx.vb:

 

' <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