Coloring Keywords in your Excel Worksheet

I came up with this nifty code snippet this afternoon that regrettably wasn't suitable for my purposes (the space it needed to fit into limits line length to only 40 characters wide you see, shades of the C= 64 composite monitor.)  However, that makes it no less nifty (property name identifiers must have clarity!) so I thought I would share it anyway since it really does highlight a particularly interesting new feature we have added.

Our development team revamped the Infragistics.Excel class library in NetAdvantage for Windows Forms (a 2007 Vol. 2 new feature) and NetAdvantage for ASP.NET (a 2007 Vol. 3 new feature) and one of the features added was the ability to apply formatted text within cells.  I know, it's easy to miss this, but there were so many new features in these releases that I suspect there are probably still .NET developers out there using our controls with Excel who aren't aware they now have this power.

One way you can use these new FormattedString and FormattedStringFont classes is to apply their especially rich formatting to text exported from our WebGrid and WinGrid controls. For example, you can colorize or bold specific keywords or search terms for your users within an exported .XLS workbook that they can download from your Web page.

From the beginning, you would attach a CellExported event handler to the WebGridExcelExporter control in your OnInit override:

protected override void OnInit( EventArgs e)
{
   base.OnInit(e);
   this.Exporter1.CellExported += new CellExportedEventHandler( Exporter1_CellExported);
}

You also always need some way to trigger the export, this is conventionally handled by putting a Button onto your page or form and wiring up it's Click event handler to call one of the Export methods of the WebGridExcelExporter.

protected void Button1_Click( object sender, EventArgs e)
{
   this.Exporter1.Export( this.Grid1);
}

The CellExported event handler will be easy because I'm going to factor all of the colorizing logic into its own method. If you are new to handling events for the WebGridExcelExporter, however, one of the lines of code (highlighted below) you'll use frequently is dereferencing the CellExportedEventArgs to get the current WorksheetCell object.  The EventArgs you receive during export inherit from our ExcelExportEventArgs class, so you will have these properties available to you within any export event handler.

private void Exporter1_CellExported( object sender, CellExportedEventArgs e)
{
   WorksheetCell cell = e.CurrentWorksheet.Rows[ e.CurrentRowIndex].Cells[ e.CurrentColumnIndex];
   this.ColorByKeyword( cell, "sales", Color.Red);
}

The logic I wanted to show-off to you is contained in this ColorByKeyword method of mine.  In it I loop through the unformatted text representation of the WorksheetCell's value searching for the passed-in keyword substring.  If I find at least one match, then I create a FormattedString wrapper object around the cell's unformatted text and assign it back to the WorksheetCell's value.  Note that this step is important, I can't really use the FormattedString until it is attached back into the whole Infragistics.Excel object graph of interconnected cell, font and formatting objects and you'll receive an InvalidOperationException if you try working with it prematurely.

You can think of the FormattedStringFont as representing a range within a FormattedString that I want to apply a special font formatting to (and of course, font formatting means you can affect color, size, weight or boldness, italics, underscore, strikethrough, etc.).  Using one of the GetFont methods on FormattedString I pick out the range of text using a startingIndex and characterCount corresponding to the keyword ("sales") I want to colorize and I set the FormattedStringFont's Color property to my desired color (red).

private void ColorByKeyword( WorksheetCell cell, string keyword, Color newColor)
{
   int keywordIndex = 0;
   FormattedString fmtStr = null;
   string text = cell.Value.ToString( );
   while ( ( keywordIndex = text.IndexOf( keyword, keywordIndex ) ) >= 0 ) {
      if ( fmtStr == null ) {
         fmtStr = new FormattedString( text);
         cell.Value = fmtStr;
      }
      FormattedStringFont fnt = fmtStr.GetFont( keywordIndex, keyword.Length);
      fnt.Color = newColor;
      keywordIndex = keywordIndex + 1;
   }
}

In Excel this might produce a worksheet that looks like this,