ExcelExporter events

Xceed Grid for WinForms provides events that are raised when the various XML elements in an exported spreadsheet are being written. The following table lists these events:

Event	Description
WritingCell	Raised when a Cell element is being written.
WritingColumn	Raised when a Column element is being written.
WritingRow	Raised when a Row element is being written.
WritingTable	Raised when a Table element is being written.
WritingWorkbook	Raised when a Workbook element is being written.
WritingWorksheet	Raised when a Worksheet element is being written.

The WritingCell event will be demonstrated in this topic. The use of ExcelExporter's CustomStyles property will also be demonstrated. This property holds a collection of ExcelStyle objects that can be used to modify the appearance of elements in the spreadsheet. The code below uses the Northwind database as an example. The style of a cell is changed in the EmployeeID column if it has a value of 4.

Basic steps - C#

To subscribe to the WritingCell event, the following steps must be performed:

Obtain a reference to an ExcelExporter object.
Subscribe to the WritingCell event of the ExcelExporter object using the WritingCellElementEventHandler delegate class
Create a new method that will handle the events that are raised.
Place the desired code in the newly created event handler.

Basic steps - VB.NET

To subscribe to the WritingCell event, the following steps must be performed:

Obtain a reference to an ExcelExporter object.
Subscribe to the WritingCell event of the ExcelExporter object using the AddHandler/AddressOf keywords.
Create a new method that will handle the events that are raised.
Place the desired code in the newly created event handler.

Demonstration

This example assumes that you are in a Windows application.

VB.NET Copy Code

VB.NET	Copy Code
Imports Xceed.Grid.Exporting Dim excelExporter1 As New ExcelExporter() AddHandler excelExporter1.WritingCell, AddressOf ExcelExporter_WritingCell Dim excelStyle1 As New ExcelStyle(Color.Red, Color.White, _ New System.Drawing.Font("Microsoft Sans Serif", 9, _ FontStyle.Bold), _ ContentAlignment.MiddleRight) Dim m_newStyleAdded As String = excelExporter1.CustomStyles.Add(excelStyle1) excelExporter1.Export(Me.gridControl1, "d:\test\spreadsheet1.xml") Private Sub ExcelExporter_WritingCell(ByVal sender As Object, ByVal e As Xceed.Grid.Exporting.WritingCellElementEventArgs) If e.Cell Is Nothing Then Return End If 'We want to highlight a cell in the Employee ID column only if it's employee number 4. If e.Cell.ParentColumn Is GridControl1.Columns("EmployeeID") AndAlso CInt(e.Cell.Value) = 4 Then Dim excelExporter As ExcelExporter = CType(sender, ExcelExporter) If e.Attributes.Contains("StyleID") Then e.Attributes("StyleID").Value = excelExporter.CustomStyles(m_newStyleAdded).ID End If End If End Sub

Imports Xceed.Grid.Exporting

Dim excelExporter1 As New ExcelExporter()
AddHandler excelExporter1.WritingCell, AddressOf ExcelExporter_WritingCell

Dim excelStyle1 As New ExcelStyle(Color.Red, Color.White, _
                                  New System.Drawing.Font("Microsoft Sans Serif", 9, _
                                                           FontStyle.Bold), _
                                  ContentAlignment.MiddleRight)

Dim m_newStyleAdded As String = excelExporter1.CustomStyles.Add(excelStyle1)

excelExporter1.Export(Me.gridControl1, "d:\test\spreadsheet1.xml")

Private Sub ExcelExporter_WritingCell(ByVal sender As Object, ByVal e As Xceed.Grid.Exporting.WritingCellElementEventArgs)

   If e.Cell Is Nothing Then
    Return
   End If

   'We want to highlight a cell in the Employee ID column only if it's employee number 4.
   If e.Cell.ParentColumn Is GridControl1.Columns("EmployeeID") AndAlso CInt(e.Cell.Value) = 4 Then

    Dim excelExporter As ExcelExporter = CType(sender, ExcelExporter)

    If e.Attributes.Contains("StyleID") Then
       e.Attributes("StyleID").Value = excelExporter.CustomStyles(m_newStyleAdded).ID
    End If
   End If
End Sub

C# Copy Code

C#	Copy Code
using Xceed.Grid.Exporting; ExcelExporter excelExporter1 = new ExcelExporter(); excelExporter1.WritingCell += new WritingCellElementEventHandler(ExcelExporter_WritingCell); ExcelStyle excelStyle1 = new ExcelStyle(Color.Red, Color.White, new System.Drawing.Font("Microsoft Sans Serif", 9, FontStyle.Bold), ContentAlignment.MiddleRight); string m_newStyleAdded = excelExporter1.CustomStyles.Add(excelStyle1); excelExporter1.Export(this.gridControl1, @"d:\test\spreadsheet1.xml"); private void ExcelExporter_WritingCell(object sender, Xceed.Grid.Exporting.WritingCellElementEventArgs e) { if (e.Cell == null) return; //We want to highlight a cell in the Employee ID column only if it's employee number 4 if( ( e.Cell.ParentColumn == gridControl1.Columns[ "EmployeeID" ] ) && ( ( int )e.Cell.Value == 4 ) ) { ExcelExporter excelExporter = ( ExcelExporter )sender; if( e.Attributes.Contains( "StyleID" ) ) e.Attributes[ "StyleID" ].Value = excelExporter.CustomStyles[ m_newStyleAdded ].ID; } }

using Xceed.Grid.Exporting;

ExcelExporter excelExporter1 = new ExcelExporter();
excelExporter1.WritingCell += new WritingCellElementEventHandler(ExcelExporter_WritingCell);

ExcelStyle excelStyle1 = new ExcelStyle(Color.Red, Color.White,
                                        new System.Drawing.Font("Microsoft Sans Serif", 9,
                                                                FontStyle.Bold), 
                                        ContentAlignment.MiddleRight);

string m_newStyleAdded = excelExporter1.CustomStyles.Add(excelStyle1);
excelExporter1.Export(this.gridControl1, @"d:\test\spreadsheet1.xml");

private void ExcelExporter_WritingCell(object sender, Xceed.Grid.Exporting.WritingCellElementEventArgs e)
{
   if (e.Cell == null)
    return;

   //We want to highlight a cell in the Employee ID column only if it's employee number 4
   if( ( e.Cell.ParentColumn == gridControl1.Columns[ "EmployeeID" ] )
    && ( ( int )e.Cell.Value == 4 ) )
   {
    ExcelExporter excelExporter = ( ExcelExporter )sender;

    if( e.Attributes.Contains( "StyleID" ) )
       e.Attributes[ "StyleID" ].Value = excelExporter.CustomStyles[ m_newStyleAdded ].ID;
   }
}