Summary

 

This document is designed to provide the necessary information how we can extract data from some data source to a splendid grid like control, paste a chart in it and export the report with graph to MS Excel for making analysis, comparisons and printing.

 

Contents

 

Here is a quick look what the article is all about:

• Overview
• The Aspose Components
• Designing the Interface
• Retrieving Data from an XML File
• Filling the Worksheet of the Aspose.Grid.Web control with Data
• Formatting Data in the Cells
• Creating Chart based on the Source Range using Aspose.Chart component
• Producing the formatted Report (.XLS File) with Graph using Aspose.Cells component
• Running the Application
• Conclusion

 

Note: I demonstrate a project which will perform all the above tasks using the APIs of Aspose.Grid.Web, Aspose.Chart and Aspose.Cells components. For better understanding, the step by step screen shots are provided.

 

Overview

 

There are certain web scenarios that demand both Reporting and Presentations, a combination of parts or objects that can work together well. The article explains how easy it is to design and generate stylish excel reports dynamically in WYSIWYG manner. It exports data from an XML file (You may also utilize other data sources) to Aspose.Grid.Web control which provides you the real environment that allows you to apply rich and appealing format to data and calculate formula results like MS Excel. It also generates a sophisticated chart based on the Worksheet source data using Aspose.Chart component and pastes the chart image into the Sales Report. Finally, the excel report with graph attached is saved to disk using Aspose.Cells component.

 

This article includes the source code and fully featured demo project for such functionality.

It allows the users with a detailed perceptive on how to create a business report to input data into a worksheet of the grid and apply some formatting to the cells in the rows and columns, embed a graph based on the source range of data before saving the excel report to the disk.

 

The Aspose Components

I use three of Aspose's components to perform the task with ease. Aspose, The .NET and Java Component Publisher, provides a variety of feature-rich components. Aspose provides a great line of .NET and Java components. Trusted by thousands of customers worldwide, the products include File Format Components, Reporting Products, Visual Components and Utility Components that allow to programmatically open, modify, generate, save, merge, convert etc. documents in various formats including DOC, RTF, WordML, HTML, PDF, XLS, SpreadsheetML, Tab Delimited, CSV,  PPT, SWF, EMF,WMF, MPX, MPD and other formats.

 

I would take this opportunity to introduce three of these components to you which have been used in this quest.

 

 

Aspose.Grid

 

Aspose.Grid is a total grid solution. Aspose.Grid comes packaged with two different GUI .NET components (Aspose.Grid.Desktop and Aspose.Grid.Web): one to support desktop applications and other to support web applications. Both versions are equally matched in order to make implementing in either platform a snap. Aspose.Grid.Web provides the ability to import from and export to Excel spreadsheets. So anyone familiar with Excel (even end-users) can design the look and feel of a grid. Aspose.Grid.Web also offers an easy to use, feature-rich API which provides developers with complete control over the look, feel and behavior of their grid. To know more about the product, its features and for a programmer's guide, please check the summary of Features List , Aspose.Grid.Web Decumentation and online featured Demos. You may download its evaluation version for free.

 

Aspose.Chart

 

Aspose.Chart is a .Net business and financial charting component which enables .Net applications to create 21 different types of charts including: Area, Bar, Bubble, Contour, Candlestick, Cube, Curve, CurveArea, Doughnut, Gantt, HighLowClose, Line, OpenHighLowClose, Pareto, Pie, Pyramid, Point, Radar, Scatter, Step, Surface, as well as many variants of these types.  Aspose.Chart also supports many powerful effects including: 3D rendering, framed borders, anti-aliasing, transparency, gradient, custom drawing and chart combinations. The component provides feature-rich API for the developers. To know more about the product, its features and for a programmer's guide, please check the summary of Features List , Aspose.Chart Decumentation and online featured Demos.  You may download its evaluation version for free.

 

Aspose.Cells

 

Aspose.Cells is an Excel spreadsheet reporting component that enables you to read and write Excel spreadsheets without utilizing Microsoft Excel to be installed either on the client or server side. Aspose.Cells is a feature rich component that offers much more than just basic exporting of data. With Aspose.Cells developers can export data, format spreadsheets in every detail and at every level, import images, import charts, create charts, manipulate charts, stream Excel data, save in various formats including XLS, CSV, SpreadsheetML, TabDelimited, TXT, XML (Aspose.Pdf integrated) and many more. Aspose.Cells offers an easy to use, feature-rich API for the programmers. It has a huge list of features. To know more about the product, its features and for a programmer's guide, please check the summary of Features List , Aspose.Cells Decumentation and online featured Demos.  You may download its evaluation version for free.

 

Designing the Interface

We start creating a new Asp.Net web application in Visual Studio.Net.

 

 

I Add Reference to the three components i.e. Aspose.Grid.Web.dll, Aspose.Chart.dll and Aspose.Cells.dll to the project first. I place some control on the page and set their properties, i.e. a drop down list, a command button and a label. I then place Aspose.Grid.Web control (GridWeb) to it from the toolbox, since after adding references to the three components, the GridWeb control is appeared on toolbox. The other two components (Aspose.Chart and Aspose.Cells) are just libraries, only get referenced to the project.

 

 

 

I also create two folders "file" and "images", add "Products.xml" and "chart.gif" to these folders respectively. The xml file is a data source file from which the data will be extracted to fill the GridWeb worksheet. The image file will provide an image for a custom button placed on the GridWeb control.

 

 

 

I, now, create a custom command button. I simply right-click on the GridWeb control and click "Custom Command Buttons…" option.

 

 

 

 

It will activate Custom Command Button editor, the editor allows you to create custom command image buttons with tool tip attached. I specify the values for some properties of the button e.g., Command (Name) -> "btnChart", ImageUrl -> give the path to the image file ("chart.gif") and ToolTip -> give the tool tip.

 

 

 

So, the custom command button is added as you may see it (encircled with red color) in the following screen shot.

 

 

 

 

Finally, I set some Font attributes (bold) for the label and command button. I also adjust the size of the controls to get the final look.

 

 

 

Retrieving Data from an XML File

 

Following is XML file structure used in the project.

 

XML File Structure

 

<?xml version="1.0" standalone="yes"?>

<SalesData>

  <Products>

    <ProductName>Data</ProductName>

    <QuantityPerUnit>Data</QuantityPerUnit>

    <CategoryName>Data</CategoryName>

    <UnitPrice>Data</UnitPrice>

    <Sale>Data</Sale>

  </Products>

.........

</SalesData>

 

First, I retrieve distinct values from CategoryName in the XML file to fill the drop down list. Following is the code in Page_Load event handler.

 

[C#]

 

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

{

if (!IsPostBack)

{

              // Uncomment the code below when you have purchased license

              // for Aspose.Grid.Web, Aspose.Chart and Aspose.Cells. You need

              // to deploy the licenses in the same folder as your executable,

      // alternatively you can add the license files as an embedded

      // resource to your project.

              //

              // // Set the license for Aspose.Grid.Web

              // Aspose.Grid.Web.License gridwebLicense = new

              // Aspose.Grid.Web.License();

              // gridwebLicense.SetLicense("Aspose.Grid.lic");

              //

              // // Set the license for Aspose.Chart

              // Aspose.Chart.License chartLicense = new

              // Aspose.Chart.License();

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

              //

              // // Set the license for Aspose.Cells

              // Aspose.Cells.License cellsLicense = new

              // Aspose.Cells.License();

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

                                                                                                               

              //Create a DataSet object.

              DataSet ds = new DataSet();

              //Get the Virtual Folder Path.

              string path = MapPath(".");                                                                                   

              //Reads XML data from xml file into DataSet object.

              ds.ReadXml(path + "\\file\\Products.xml");

              //Call the custom method to obtain distinct values from

              //CategoryName field and store data into an object array.

              object [] drs = GetDistinctValues(ds.Tables[0],"CategoryName");

              //Fill the drop down list with distinct field items.

              for(int i = 0;i<drs.Length;i++)

              {

                            DropDownList1.Items.Add(drs[i].ToString()); 

              }

}

}

 

//This method is used to filter distinct values from CategoryName

//field in the datatable.

private object[] GetDistinctValues(DataTable dtable, string colName)

{

              //Create a Hashtable object.

              Hashtable hTable = new Hashtable();

              //Loop through the datatable rows and add distinct values to

              //Hashtable object minimizing the duplicates in the field.

              foreach (DataRow drow in dtable.Rows)

              if(!hTable.ContainsKey(drow[colName]))

              hTable.Add(drow[colName], string.Empty);

              //Create an object array based on the distinct key values

              //of the Hashtable object.

              object[] objArray = new object[hTable.Keys.Count]; 

              //Copy the disctinct values to fill the array.

              hTable.Keys.CopyTo(objArray, 0);  

              //Return the array object.

              return objArray;  

}

 

 

[VB.NET]

 

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

 

If Not IsPostBack Then

                 ' Un-comment the code below when you have purchased license

      ' for Aspose.Grid.Web, Aspose.Chart and Aspose.Cells. You need

      ' to deploy the licenses in the same folder as your executable,

      ' alternatively you can add the license files as an embedded

      ' resource to your project.

      '

      ''  Set the license for Aspose.Grid.Web

      ' Dim gridwebLicense As Aspose.Grid.Web.License = New

      ' Aspose.Grid.Web.License()

      ' gridwebLicense.SetLicense("Aspose.Grid.lic")

      '

      ''  Set the license for Aspose.Chart

      ' Dim chartLicense As Aspose.Chart.License = New

      ' Aspose.Chart.License()

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

      '

      ''  Set the license for Aspose.Cells

      ' Dim cellsLicense As Aspose.Cells.License = New

      ' Aspose.Cells.License()

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

 

     'Create a DataSet object.

     Dim ds As DataSet = New DataSet

     'Get the Virtual Folder Path.

     Dim path As String = MapPath(".")

     'Reads XML data from xml file into DataSet object.

     ds.ReadXml(path + "\file\Products.xml")

     'Call the custom method to obtain distinct values from CategoryName

     'field and store data into an object array.

     Dim drs() As Object = GetDistinctValues(ds.Tables(0), "CategoryName")

     'Fill the drop down list with distinct field items.

     Dim i As Integer

     For i = 0 To drs.Length - 1

     DropDownList1.Items.Add(Convert.ToString(drs(i)))

     Next

 

End If

 

 

End Sub

 

'This method is used to filter distinct values from CategoryName

'field in the datatable.

Private Function GetDistinctValues(ByVal dtable As DataTable, ByVal colName As String) As Object

 

     'Create a Hashtable object.

     Dim hTable As Hashtable = New Hashtable

     'Loop through the datatable rows and add distinct values to

     'Hashtable object minimizing the duplicates in the field.

     Dim drow As DataRow

     For Each drow In dtable.Rows

         If Not hTable.ContainsKey(drow(colName)) Then

         hTable.Add(drow(colName), String.Empty)

         End If

     Next

     'Create an object array based on the distinct key values

     'of the Hashtable object.

     Dim objArray(hTable.Keys.Count) As Object

     'Copy the disctinct values to fill the array.

     hTable.Keys.CopyTo(objArray, 0)

     'Return the array object.

     Return (objArray)

 

 

End Function

 

 

 

Filling the Worksheet of the Aspose.Grid.Web control with Data

 

I use some API of the GridWeb control to fill a worksheet with data from the source XML file. I write code in the command button (labeled "Show Report"</