XMLSpreadGear Tutorial


XMLSpreadGear




Introduction:



This whitepaper describes how to take advantage of the SpreadsheetML to distribute enterprise data via Excel using XML. Sample code below is described in detail in order to demonstrate how XmlSpreadGear may be used to generate xml data in the format which is compatible with MS Excel. This format is known as XML Spread Sheet Schema or XML-SS. For more information on XML – SS click here.


Challenges:


Companies today are using MS Excel as a medium for exchanging reports or any kind of business data. It brings great challenge to application developers to provide “Export to Excel” functionality. There are various ways to transform data from data object (likely a collection/list) to excel. Some of them are mentioned below
• Define Excel workbook as a data source and then use OledbReader/OledbCommand to read and write data from Excel file.
• Use Interope Assemblies (PIA) to read/write data from/to excel file.
Each of these approaches best suited in certain scenarios. However, implementing this kind of functionality using above approaches are very complicated for development and also major threat comes from performance.


Solution:


Today XML is becoming the favorite medium of data interchange. With the increasing popularity of xml, it is became integral part of office applications. Microsoft has provided option to customer who wants to export their data in excel using XML format. This format is known as SpreadsheetML.

XMLSpreadGear is a mediator between application objects and SpreadsheetML. It will do all the hardwork to transform an object to XML which is compatible for opening in Excel. XMLSpreadGear supports basic formatting of reports. This tool also provides functionality for summation and average. Below details will explain you how to use XMLSpreadGear to generate well formatted excel reports.

Generating Excel file using XMLSpreadGear:

Below steps assumes that you are having hands on knowledge on .net and visual studio.

1. Create a new project in Visual Studio.
2. Add reference of XMLSpreadGear.dll in the project.
3. Create a new class and expose some properties. All property types should be primitive types. Every property you want to export need to decorated with CellTemplateAttribute.



public class Data
{

[CellTemplate("My Property 1", AutoFitWidth = true,
CellStyleID = BluePresentationStyle.CellStyleID, Index = 0, StyleID = BluePresentationStyle.ColumnStyleID, Width=100)]
public string MyProperty1 { get; set; }

[CellTemplate("My Property 2", AutoFitWidth = true,
CellStyleID = BluePresentationStyle.CellStyleID, Index = 1, StyleID = BluePresentationStyle.ColumnStyleID, Width = 100)]
public string MyProperty2 { get; set; }
[CellTemplate("My Property 3", AutoFitWidth = true,
CellStyleID = BluePresentationStyle.CellStyleID, Index = 2, StyleID = BluePresentationStyle.ColumnStyleID, Width = 100)]
public string MyProperty3 { get; set; }

[CellTemplate("My Property 4", AutoFitWidth = true,
CellStyleID = BluePresentationStyle.CellStyleID, Index = 3, StyleID = BluePresentationStyle.ColumnStyleID, Width = 100)]
public string MyProperty4 { get; set; }

[CellTemplate("My Property 5", AutoFitWidth = true,
CellStyleID = BluePresentationStyle.CellStyleID, Index = 4, StyleID = BluePresentationStyle.ColumnStyleID, Width = 100)]
public string MyProperty5 { get; set; }

[CellTemplate("My Property 6", AutoFitWidth = true,
CellStyleID = BluePresentationStyle.CellStyleID, Index = 5, StyleID = BluePresentationStyle.ColumnStyleID, Width = 100)]
public string MyProperty6 { get; set; }

[CellTemplate("My Property 7", AutoFitWidth = true,
CellStyleID = BluePresentationStyle.CellStyleID, Index = 6, StyleID = BluePresentationStyle.ColumnStyleID, Width = 100)]
public string MyProperty7 { get; set; }

[CellTemplate("My Property 8", AutoFitWidth = true,
CellStyleID = BluePresentationStyle.CellStyleID, Index = 7, StyleID = BluePresentationStyle.ColumnStyleID, Formula = FormulaType.SUM)]
public int MyProperty8 { get; set; }

[CellTemplate("My Property 9", AutoFitWidth = true,
CellStyleID = BluePresentationStyle.NumberCellStyleID, Index = 8, StyleID = BluePresentationStyle.ColumnStyleID)]
public double MyProperty9 { get; set; }

[CellTemplate("My Property 9", AutoFitWidth = true,
CellStyleID = BluePresentationStyle.DateCellStyleID, Index = 9, StyleID = BluePresentationStyle.ColumnStyleID, Width = 100)]
public string MyProperty10 { get; set; }

}


CellTemplate Attribute provides various properties which we will be discussing later in this article.

4. Add another new class to create collection/list of the data class


class datacollection : List
{

}




5. Populate datacollection class with some Data objects. For the demo purpose we will write for loop to populate datacollection.


datacollection dc = new datacollection();
for (int i = 0; i < 10; i++)
{
Data dt = new Data();
dt.MyProperty1 = "MyProperty1 " + i.ToString();
dt.MyProperty2 = "MyProperty2 " + i.ToString();
dt.MyProperty3 = "MyProperty3 " + i.ToString();
dt.MyProperty4 = "MyProperty4 " + i.ToString();
dt.MyProperty5 = "MyProperty5 " + i.ToString();
dt.MyProperty6 = "MyProperty6 " + i.ToString();
dt.MyProperty7 = "MyProperty7 " + i.ToString();
dt.MyProperty8 = i;
dt.MyProperty9 = i + 1.50;
dt.MyProperty10 = DateTime.Now.Add(new TimeSpan(i, 0, 0, 0)).ToShortDateString();
dc.Add(dt);
}




6. Create new object ExcellProcessor class. You can specify name of the worksheet and name of the author. XMLSpreadGear provides some default styles which you can use for formatting excel report. Formatting capability can be extend to create your own style which I will be covering later in this article.


ExcelProcessor excel = new ExcelProcessor(ExcelStyles.BluePresentationStyle.Style);
excel.WorksheetName = "My book";
excel.AuthorName = "Me";




7. Create a new handler for ProcessComplete.


excel.ProcessComplete += new ProcessHandler(excel_ProcessComplete);
void excel_ProcessComplete(object sender, ProcessHandlerArgs args)
{
args.Xml.Save(@"");
}



8. Add Job to ExcelProcessor and process it. Job takes several other parameters which we will discuss later in this article.


excel.Add(new Job(dc, string.Empty, string.Empty,true));
excel.Process();



XMLSpreadGear Class References:



ExcelProcessor: This is the main class used for transforming data into xml. Its constructor takes styles as a parameter. This class maintains a queue of tasks to be performed. Add() method creates a new task and add in the job queue. While processing it dequeue the job and start transformation process. There are several default styles are included in the XMLSpreadGear. Those styles can be access using ExcelStyles.BluePresentationStyle.Style or ExcelStyles.GrayPresentationStyle.Style.


Methods

Process(): This method starts the process of xml generation.

Add(Job jobItem): Adds a new job in the queue for the xml transformation. It takes Job object as an input parameter.


Properties

WorksheetName: Name of the worksheet.
AuthorName: Name of the Author


Events

ProcessComplete: This event will be triggered by the Process method. This event needs to handle at client side. It will return the generated XmlDocument.


Job: This class is used to create new tasks for the ExcellProcessor. This class provides instruction the ExcelProcessor about the transformation. Depending upon these instructions ExcelProcessor performs the transformation process.


Constructor

Job(string header, string styleID,bool beginGroup): Use this constructor to create Job instance if you want to create Heading for the report.
header: text to display as a heading for report.
styleID: Style to apply on the header.
beginGroup: if it is true it will increase rowindex with three.


Job(IEnumerable data, string header, string headerStyleID, bool beginGroup): Use this constructor to create Job instance if you want to create Details section for the report.
data: data object to create detail section. This can be list or collection.
header: heading of the table. If you don’t want any heading then it can be Empty.
headerStyleID: StyleID for the table heading.
beginGroup: if it is true it will increase rowindex with three.


• CellTemplate: This attribute need to be decorated over the property which need to be transform. It defines the formatting for the particular column and cell.


Properties

Caption: Caption to be used for the property. This will be used as the heading of the column.

AutoFitWidth: Can be set as true/false. If marked as true then width of the column with get adjusted according to the length of text inside the cell it means that this column should be autosized for numeric and date values only. We do not autofit textual values.

If both Width and AutoFitWidth exist, the behavior is as follows:

AutoFitWidth="true" and Width is unspecified: Autofit the column width to fit the content.

AutoFitWidth="true" and Width is specified: Set the column to the specified width and only autofit if the size of the content is larger than the specified width.

AutoFitWidth="false" and Width is unspecified: Use the default column width.

AutoFitWidth="false" and Width is specified: Use the specified width.

CellStyleID: Style to use for formatting content of the cell.

Formula: There are two function specified i.e. SUM, AVERAGE and NONE.
SUM: It will do summation for all values in the column and will display at the end of the table.

AVERAGE: it will take the average of all values in the column and will display at the end of the table.

NONE: this is default. It will not attach any formula with the column.
Hidden True specifies that this column is hidden. False (or omitted) specifies that this column is shown.

Index: Specifies the position of this column within the table.
If this tag is not specified, the first instance has an assumed Index="1". Each additional Column element has an assumed Index that is one higher.

Indices must appear in strictly increasing order. Failure to do so will result in an XML Spreadsheet document that is invalid. Indices do not need to be sequential, however. Omitted indices are formatted with the default style's format.

Indices must not overlap. If duplicates exist, the behavior is unspecified and the XML Spreadsheet document is considered invalid. An easy way to create overlap is through careless use of the Span attribute.

Span: Specifies the number of adjacent columns with the same formatting as this column. When a Span attribute is used, the spanned column elements are not written out.

As mentioned in the Index tag, columns must not overlap. Doing so results in an XML Spreadsheet document that is invalid. Care must be taken with this attribute to ensure that the span does not include another column index that is specified.

StyleID: Specifies a reference to a previously defined ID attribute in a Style tag. This reference indicates that this Style should be used to format this element. If this attribute is not present, the default Style should be applied to this element.

Width: Specifies the width of a column in points. This value must be greater than or equal to 0.
Events

Reference: http://xmlspreadgear.codeplex.com/


Attachments

  • AppliedXMLSpreadGear (33754-9218-AppliedXMLSpredGear.zip)
  • Comments

    No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: