wordpress hit counter
Spreadsheet creation in Silverlight - OpenXML Developer - Blog - OpenXML Developer

Spreadsheet creation in Silverlight

Blog

Samples, Demos, and Reference Articles

Spreadsheet creation in Silverlight

  • Comments 4

By Chris Klug

A while back I wrote an article for OpenXMLDeveloper.org, outlining how one could use Open XML to create word processing documents from Silverlight. I personally found this a great way to get information from an application to the user in a format that could be stored locally and perhaps printed. After a search on-line, I found very little in the form of open source libraries. So, I decided to rewrite the code from the article and put it on CodePlex. There was not a whole lot of rewriting to be done, but there were a few little architectural issues I wanted to correct.

Now that the library is available as open source, I have realized that it might be useful to be able to create spreadsheets as well. So this article will go through the steps needed to create a simple spreadsheet implementation based on the CodePlex project. The end result will be an extension that makes it possible to create a spreadsheet document, set the values of different cells and also create tables and formulas. The code is far from an Excel replacement, but once again it should give you the basics and provide an easily extended architecture.

Intro

The goal with this article is, as I mentioned before, to create a relatively simple spreadsheet implementation based on the Ag.OpenXML library off Codeplex. It will result in an application that can create a spreadsheet document with a table and a formula based cell.

Getting started

Before we get started, we have to get the base library. It is available for download at http://agopenxml.codeplex.com. I suggest getting the latest source code. By getting the source code you have the ability to see how the base classes are supposed to be used and also debug the code if needed. The download will contain both the source, and a library called SharpZipLib. As the download is in source code format, you will have to open it in VS and compile it.

Next, start up a new project in VS. It should be of type Silverlight Application and be called something like OpenXML.Silverlight.Spreadsheet.Client. During the creation of the project, you will be requested if you want to add an extra project to host the Silverlight client. This is entirely up to you. In this case, it will work just as well if you choose to dynamically add a page.

When the solution is up and running add a new Silverlight Class library project, then call the project something like OpenXML.Silverlight.Spreadsheet.

In the Silverlight Application project add references to both the Class Library project and the FiftyNine.Ag.OpenXML.Common.dll assembly. In the Silverlight Class Library project add references to FiftyNine.Ag.OpenXML.Common.dll and SharpZipLib.dll.

Now the solution should be good to go. So let’s get started.

The spreadsheet document

Create a new class in the Class Library project, and call it SpreadsheetDocument. As I mentioned in my previous article, all OpenXML documents derive from the Package class. Note that all the code here needs to run in the Silverlight environment so we use the Windows built-in packaging API directly rather than install the Open XML SDK. Let’s change the SpreadsheetDocument class so that it inherits from FiftyNine.Ag.OpenXML.Common.Packaging.Package.

The SpreadsheetDocument needs three parts to function properly. It needs an AppPart, a CorePart and a Workbook. The AppPart and CorePart classes are already available to us in the FiftyNine.Ag.OpenXML.Common.Parts namespace. A small change from the previous article however, is how you create new parts. The Package class exposes a generic method called CreatePart().   It is responsible for creating a part, registering the type and so on, then returning it to the user. So to create the required parts, all we have to do is this:

public SpreadsheetDocument()
{
    Workbook = CreatePart("/xl/workbook.xml");
    AppPart = CreatePart("/docProps/app.xml");
    CorePart = CreatePart("/docProps/core.xml");

    AddRelationship(Workbook);
    AddRelationship(AppPart);
    AddRelationship(CorePart);
}

public WorkbookPart Workbook { get; private set; }
private AppPart AppPart { get; set; }
private CorePart CorePart { get; set; }

As you can see, the Package class also exposes a simple method called AddRelationship(). This will make sure that there is a relationship set up, linking the package to the parts.

That is all there is to it. With those few lines of code, we have created a whole new package type. However, as you might have noticed, we are instantiating an object of type WorkbookPart, which apparently isn’t available. So let’s create that part.

The WorkbookPart

Create a new class called WorkbookPart that inherits from PackagePart. The PackagePart base class gives us all we need to create a part to place inside an OpenXML document. This class has changed a bit since the last article meaning you are no longer allowed to add a constructor to it. Instead, if you need to do something at time of creation you can override the virtual method Initialize. The class also has two abstract properties that need to be overridden; the ContentType and RelationshipType properties of which are unchanged since the last article.  These properties should return strings representing the content type of the package and the type used when creating a relationship to it.

Start by overriding the Initialize method and making sure that the correct namespaces are added when the part is saved. This is easily done by calling the AddRequiredNamespace() method. Every workbook also contains a list of worksheets which are represented by a class called WorksheetPart. So before leaving the initialization, make sure that the workbook contains some predefined Worksheets, just as Excel does it.

As I mentioned before, creation of PackagePart objects needs to be made through the CreatePart() method on the Package class. Luckily, the current Package class is available through the protected property called, not too surprisingly, Package. So creating a WorksheetPart is done like this:

WorksheetPart sheet = Package.CreatePart(partName);

The PackagePart class also has 3 virtual methods that handle the saving of the part. The “rawest” one is the Save() method. The default implementation of it calls SavePart() and then goes on to save the part’s relationships. The default implementation of SavePart() creates a new file in the package and then calls SaveContent(), passing in an XmlWriter.

In our case, only the SaveContent() is really interesting as we only want to save some XML and not actually modify how the part is saved. In the SaveContent() method, start the XML by creating a start element called workbook. Then make sure that all the required namespaces are written to the XML. This is easily done by calling the base class’ WriteRequiredNamespaces() method. After this, it is just a matter of writing the required XML, which in our case is just a matter of writing some definitions of which sheets are part of the workbook. The actual sheets are PackageParts and will therefore be saved separately.

The WorksheetPart

The worksheet part is not very different from the WorkbookPart as such. It inherits from PackagePart, offers the users to add and change content, and overrides SaveContent() to save the XML. It does however have one little caveat;it needs to get hold of the id of the sheet. As this cannot be passed into the constructor, I have chosen to expose a method called SetUp() that takes the id as a parameter, and every time a WorkbookPart creates a new sheet it calls that internal method. I use this pattern every time I need to pass in values. It is hard to enforce the creator of the class to do this, but since this class is always created by my own code I know that this will be done.

The content of the sheet is exposed through cells, referenced by using rows and columns. In my case, I have opted for a solution where the Worksheet class exposes a collection of Row objects where each Row in turn exposes a collection of Cell objects and each cell is responsible for containing a certain type of value. But instead of forcing the user to add a Row or Cell to the collection, I have created a hybrid collection called DictionaryCollection. It is a generic type that will allow the user to access any index in the collection. If there is no object at that index, it creates a new one and returns it to the user. This way, the user can edit cell information without first having to make sure that there is a row and that there is a column in that row and so on. It also exposes an event that is raised whenever an item is created. This is utilized by the Row class to make sure that the Cell object knows its row index. 

The Cell Class

The source code will contain a lot more classes and code than the ones I am going through in this article. But I have tried to choose some of the classes that will show you how to extend the Ag.OpenXML library, so please download the source code and have a look around.

The last class to have a look at is the class that represents a cell in the worksheet. It might seem like a pretty obvious class as it only contains a small value, but it is actually quite complicated as a Cell can contain a lot of different type of values.

The Cell class inherits from a class called IndexedSpreadsheetElement inheriting from SpreadsheetElement, which in turn inherits from OpenXMLElement. OpenXMLElement is the base class for anything that is to be stored inside a PackagePart. It exposes methods and properties needed for this, including a method for saving its data. The SpreadsheetElement overrides the Save() method, making sure that no namespaces prefixing takes place. Most elements, if not all, used in SpreadsheetML are in the same namespace so there is no need to keep adding prefixes when it is enough to set the default namespace. The IndexedSpreadsheetElementsimply adds an index property.

The Cell class exposes a couple of properties for naming the cell as well as its row and column indexes. It also contains 3 interesting properties connected to its value, Type, Value and Formula. The Type is defined as en enum called CellValueType and is responsible for telling the cell how to render its value which is of type object as it can be several different types. Finally, the Formula is a string. These three properties together determine what is to be saved in the XML. The type and Value is read-only and is set by calling SetValue() on the Cell class. There are several overloads to this method and each one of them will store the value in the Value property and determine what CellValueType to use. The Formula is a separate thing and can be set manually at any point. It will override the cell value with a computed value when the document is viewed.

When the Cell is saved, it does so using a big switch clause that determines how to persist the cells value based on the defined CellValueType. Finally it uses a custom method to “serialize” dates as these need to be in Julian format.

Demo Application

Now that the code for the spreadsheet is implemented it is time to have a look at how to use it. As I mentioned initially, the Silverlight Application project needs to reference the Class library project as well as the FiftyNine.Ag.OpenXML.Common.dll assembly. When this is set up, it is time to start using the library we just created. Start by adding a button to the MainPage.xaml file and add a handler to the Click event:


   

After that, it is time to focus on the handler that Visual Studio created in the code behind. The code starts off by creating a new SaveFileDialog and setting some properties to make sure it feels comfortable working with Excel files. As I mentioned in the previous article this part of the functionality actually has to be done in code for different reasons. No MVVM (Model View ViewModel) here:

SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "Excel Document (.xlsx)|*.xlsx|Zip Files (.zip)|*.zip";
dlg.DefaultExt = ".xlsx";
if (dlg.ShowDialog() == true)
{

Next, a new SpreadsheetDocument is created and some of the document properties set:

SpreadsheetDocument doc = new SpreadsheetDocument();
doc.ApplicationName = "SilverSpreadsheet";
doc.Creator = "Chris Klug";
doc.Company = "My Company";

After that, a feature that has not been shown in this article will be used. Spreadsheet documents offer the ability to store strings in an external SharedStrings part. These can then be referenced in several cells without taking up unnecessary space. So a couple of external strings are set up, and the reference to then stored for future use:

SharedStringDefinition str1 = doc.Workbook.SharedStrings.AddString("Column 1");
SharedStringDefinition str2 = doc.Workbook.SharedStrings.AddString("Column 2");
SharedStringDefinition str3 = doc.Workbook.SharedStrings.AddString("Column 3");

The SharedStringDefinition objects are then used to set the value of the first 3 cells on the first row:

doc.Workbook.Sheets[0].Sheet.Rows[0].Cells[0].SetValue(str1);
doc.Workbook.Sheets[0].Sheet.Rows[0].Cells[1].SetValue(str2);
doc.Workbook.Sheets[0].Sheet.Rows[0].Cells[2].SetValue(str3);

Next, 4 more rows are populated with some data:

doc.Workbook.Sheets[0].Sheet.Rows[1].Cells[0].SetValue("Value 1");
doc.Workbook.Sheets[0].Sheet.Rows[1].Cells[1].SetValue(1);
doc.Workbook.Sheets[0].Sheet.Rows[1].Cells[2].SetValue(1001);

doc.Workbook.Sheets[0].Sheet.Rows[2].Cells[0].SetValue("Value 2");
doc.Workbook.Sheets[0].Sheet.Rows[2].Cells[1].SetValue(2);
doc.Workbook.Sheets[0].Sheet.Rows[2].Cells[2].SetValue(1002);

doc.Workbook.Sheets[0].Sheet.Rows[3].Cells[0].SetValue("Value 3");
doc.Workbook.Sheets[0].Sheet.Rows[3].Cells[1].SetValue(3);
doc.Workbook.Sheets[0].Sheet.Rows[3].Cells[2].SetValue(1003);

doc.Workbook.Sheets[0].Sheet.Rows[4].Cells[0].SetValue("Value 4");
doc.Workbook.Sheets[0].Sheet.Rows[4].Cells[1].SetValue(4);
doc.Workbook.Sheets[0].Sheet.Rows[4].Cells[2].SetValue(1004);

Finally, these cells are all used to create a new table in the document. This is easily done by calling the AddTable() method on the corresponding Worksheet. It creates a new table with a specific name, based on the top left and bottom right cell of the area to turn into a table. Next, I set the names of the columns to the same value as the cells on the top row:

TablePart table = doc.Workbook.Sheets[0].Sheet.AddTable("My Table", "My Table", doc.Workbook.Sheets[0].Sheet.Rows[0].Cells[0], doc.Workbook.Sheets[0].Sheet.Rows[4].Cells[2]);
table.TableColumns[0].Name = str1.String;
table.TableColumns[1].Name = str2.String;
table.TableColumns[2].Name = str3.String;

Unfortunately, the column names are too long so the width of the columns need to be changed slightly. This can be done through setting the column sizes, by calling the AddColumnSizeDefinition() method on the Worksheet. It takes two parameters defining the first and last column to be affected, as well as the width to use:

doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(0, 3, 20);

The last requirement is to add an auto sum cell beneath the last column of the table. Just set the Formula of the cell, in this case, I add a “Sum:” text to the cell in front of it first:

doc.Workbook.Sheets[0].Sheet.Rows[5].Cells[1].SetValue("Sum:");
doc.Workbook.Sheets[0].Sheet.Rows[5].Cells[2].Formula = "SUM(" + doc.Workbook.Sheets[0].Sheet.Rows[1].Cells[2].CellName + ":" + doc.Workbook.Sheets[0].Sheet.Rows[4].Cells[2].CellName + ")";

All that is left now is to save the document. This is done in just the same way as described in the previous article, the only difference is that the names of the class and interface have changed. A new ZipStreamProvider is created, passing in the Stream returned from the SaveFileDialogs OpenFile() method. 

using (IStreamProvider storage = new ZipStreamProvider(dlg.OpenFile()))
{
    doc.Save(storage);
}

All there is to do now is to run the example and express loads of joy when the spreadsheet is generated.

The download attached to this article contains both the source and test page that will run the precompiled code here so that you can try out the sample before diving in.

Attachment: OpenXML.Silverlight.Spreadsheet.zip
  • Thank you for this post really interesting. Are you sure this is the good attachment ? It seems to be the attachment of your previous post.

    Thank you for helping,

    Guillaume Rochart
  • Hi
    I think the posting is great and this is just what I am looking for, but the attached files are not for the excel project, if you could send a link to the attachments I would be most grateful.
  • Hello Team

    After having a deep study of material available here, I think you people can help me. I have been assigned a task in which I need to export my data in excel spreadsheet. Along with that data, I need to insert an Image in the same spreadsheet. I need to do this task in silverlight. Can you please help me out how to do it.

    Thanks in advance
    Sumit Rathee
  • Pingback from  Silverlight spreadsheet | Askdarla

Page 1 of 1 (4 items)