I recently created a small library of files to help export data to Excel directly from Silverlight. It does everything client-side to avoid having to make unnecessary trips back to the server. Another bonus is it uses the Open XML Format to create the file, so it doesn’t matter if the Client has Excel installed on their computer.
With the help of Brad Abrams Article here, I was able to get an idea for what I needed to do in order to get all of this to work. However I didn't want to use fixed templates, and my ultimate goal was to create an extended Silverlight Datagrid [See Demo] that would export its contents automatically no matter what the Datagrid's ItemsSource consisted of.
In this post I will briefly describe the files used and a quick example of how to tie the Export Method to a simple button in Silverlight. Below you will find all the source code, including a Silverlight Datagrid I extended to automatically export its contents to an Excel file without any additional work from the developer. I explain how to use this control in my next post Exporting Data From Silverlight Datagrid to Excel.
After adding the code files in the source code below, generating an Excel Document directly from Silverlight client-side is as simple as placing the code below inside a button click event. In a nutshell, your creating a new Excel Workbook, defining its document properties, adding predefined styles for table cells, adding the worksheet, and then adding columns, rows and cell values. After everything is defined and the worksheet is added to the Workbook, you can easily generate the Excel XML File by simply calling the Workbook "Export" method as shown below.
Dim myWorkbook As New Excel.Workbook 'Create a new workbook.
With myWorkbook.DocumentProperties 'Define workbook document properties.
.Author = "Your name"
.LastAuthor = "Last Author's name"
.Created = DateTime.Now
.LastSaved = DateTime.Now
.Company = "Your company"
.Version = "12.00" 'Office 2007
End With
'Create a style for header cells:
Dim HeaderStyle As New Excel.Style
With HeaderStyle
.ID = "HeaderStyle" 'Unique ID of Style.
.Alignment.Vertical = Excel.Styles.VerticalAlignment.Center 'Bottom | Center | Top.
.Alignment.Horizontal = Excel.Styles.HorizontalAlignment.Center 'Center | Left | Right.
.Alignment.WrapText = True 'True | False
.Borders.Add(New Excel.Styles.Border(Excel.Styles.Position.All)) 'All | Left | Right | Top.
.Interior.Color = Colors.Black 'Background color of Cell.
.Font.FontName = Excel.Styles.FontName.Arial 'Most fonts available [can be extended to include more]
.Font.Size = 10 'True | False
.Font.Color = Colors.White 'Single | Double [can be extended to include more]
.Font.Bold = True 'True | False
.Font.Underline = Excel.Styles.Underline.Single 'Single | Double [can be extended to include more]
.Font.Italic = False 'True | False
End With
myWorkbook.Styles.Add(HeaderStyle)
'Create a style for items cells in table:
Dim ItemStyle As New Excel.Style
With ItemStyle
.ID = "ItemStyle"
.Alignment.Vertical = Excel.Styles.VerticalAlignment.Center
.Alignment.Horizontal = Excel.Styles.HorizontalAlignment.Left
.Alignment.WrapText = True
.AddBorder(New Excel.Styles.Border(Excel.Styles.Position.All))
.Font.FontName = Excel.Styles.FontName.Arial
.Font.Size = 10
.Font.Color = Colors.Black
End With
myWorkbook.Styles.Add(ItemStyle)
'Set default style of all other cells.
With myWorkbook.DefaultStyle 'Set default style
.Alignment.Vertical = Excel.Styles.VerticalAlignment.Center
.Alignment.Horizontal = Excel.Styles.HorizontalAlignment.Left
.Font.FontName = Excel.Styles.FontName.Arial
.Font.Size = 10
.Font.Color = Colors.Black
End With
'Create new worksheet.
Dim myWorksheet As New Excel.Worksheet
myWorksheet.Name = "Name of Worksheet"
'Add columns and rows to Worksheet Table.
With myWorksheet.Table
'Adding 5 columns including width properties.
.AddColumn(New Excel.Column(100.0))
.AddColumn(New Excel.Column(300.0))
'Add a header row at row index 0.
.AddRow(New Excel.Row(0))
With .Rows(0)
.AddCell(New Excel.Cell(1, "String", "Column 1", HeaderStyle.ID)) 'Column 1 Cell passes cell index, value type, cell value and optional style id.
.AddCell(New Excel.Cell(2, "String", "Column 2", HeaderStyle.ID, 3)) 'Column 2 Cell passes cell index, value type, cell value, optional style id, and optional merge across.
End With
'Add another row.
.AddRow(New Excel.Row(1))
With .Rows(1)
.AddCell(New Excel.Cell(1, "String", "Column 1 Value", ItemStyle.ID))
.AddCell(New Excel.Cell(2, "Number", "2", ItemStyle.ID, 3)) 'Add number value to second cell [String or Number allowed].
End With
End With
myWorkbook.Worksheets.Add(myWorksheet) 'This examples adds only 1 worksheet [Multiple worksheets possible].
myWorkbook.Export() 'exports the data and creates the excel workbook.
After the Export method is called, the "SaveAs" Dialog Box control appears for the user to enter the file name and location in order to save the file.
The following is a list of files contained in the source code and their purpose:
Excel.DataGrid
Extended Silverlight Datagrid control with an “Export” method for generating an Excel file based on the ItemsSource including the header, columns and row data.
Excel.Workbook
Represents the entire Excel Workbook Object. Contains Document Properties, Workbook Properties, Styles, Default Style and List of Worksheets. Excel Workbook contains “Export” Method for generating Excel XML data file.
Excel.Workbooks.Properties
Contains Workbook properties including WindowHeight, WindowWidth, WindowTopX, WindowTopY, ProtectStructure and ProtectWindows.
Excel.Document.Properties
Contains properties to store the Excel file’s Author, Last Author, Created Date, Last Saved Time, Company and Excel Version.
Excel.Worksheet
Represents Excel Worksheet object containing Worksheet Name and Table Properties.
Excel.Table
Represents the Excel Table object inside the Worksheet. Includes properties for ExpandedColumnCount, ExpandedRowCount, FullColumns, FullRows, DefaultRowHeight, List of existing Columns, and List of existing Rows. The ExpandedColumnCount and ExpandedRowCount is automatically set before worksheet is exported.
Excel.Column
Represents the Excel Column object containing Width and AutoFitWidth Properties.
Excel.Row
Represents the Excel Row object containing row index, AutoFitWidth, Height, optional Style ID and List of existing Cells.
Excel.Cell
Represents the Excel Cell object containing Properties for Cell / Column Index, Value Type, optional Style ID, and optional MergeAcross. The Value Type must be either “String” or “Number”.
Excel.Style
Represents a predefined Style for table cells. Includes StyleID, Name, Alignment (Horizontal & Vertical), Borders, Font and Interior.
Excel.Styles.Alignment
Represents the Vertical and Horizontal Alignment of text inside a table cell, as well as TextWrapping.
Excel.Styles.Border
Represents the border of a table cell, including Position, LineStyle and Weight.
Excel.Styles.Font
Represents the font used in a table cell. Contains properties for FontName, Size, Color, Bold, Italic and Underline. The Excel.Styles.Font is defined within the Excel.Style object.
Excel.Styles.Interior
Represents the interior of a Table Cell containing properties for Color and Pattern. The Excel.Styles.Interior is defined within the Excel.Style object.
Excel.Styles.Enumerations
Contains ennumerations representing various property values.
Position [used for Border]: Top | Bottom | Left | Right | All
HorizontalAlignment: Left | Center | Right
VerticalAlignment: Top | Center | Bottom
LineStyle: Continuous | Dash | Dot | Double | DashDot | DashDotDot | SlantDashDot
FontName: Arial, etc. Underline: Single | Double
That's basically it. Next week I'll post about the Datagrid control I extended in order to automatically handle data exporting from Silverlight to Excel.
Source Code: ExcelSilverlightDemo_10-14-2009.zip (1.77 mb)