To view a printable version of this article in a PDF viewer, click here for PDF.

Introduction

Sometimes you run into a situation where table column names are very long and the data is very short. An example would be an engineering application with mostly numeric data (for example, MaximumIceLoad for a column of type Int). If you have several columns of such data, you end up with a very wide sheet and very little data. This requires the user to do an inordinate amount of scrolling to see their data. Fortunately, FpSpread allows ColumnHeader WordWrap if there are embedded spaces in the Label. The example Visual Basic .NET code shown below demonstrates how to use regular expressions to split column or row names (or both) when the capitalization changes.

If you are new to regular expressions, there is a good tutorial at www.regular-expressions.info/dotnet.html.

The regular expression pattern (?<=[a-z])(?=[A-Z])|(?<=[A-Z])(?=[A-Z]) matches either capitalization changes or consecutive capital letters. As shown below, the name "MaximumIceLoad" would return "Maximum Ice Load" while "IWorkHard" would return "I Work Hard". Note that the code cannot differentiate intentional consecutive capital letters. "WeUseSQLServer" would return "We Use S Q L Server" and "WorldWarII" would return "World War I I". If you do not need the consecutive capital letter check, use this string instead (?<=[a-z])(?=[A-Z]) which only matches when changing from a lower-case letter to an upper-case letter.

Disclaimers

Here are some disclaimers for the example code shown below:

1. You must have the Imports statement.
2. You should not have to set any additional references.
3. The regular expression in the code matches either:
   a. Capitalization changes from lower-case to upper-case or
   b. Consecutive capital letters.
4. All consecutive capital letters are split. For example, "IWorkInIT" produces "I Work In I T";
   and "WorldWarII" produces "World War I I".

Code

Here is the first version, which handles all the alphabetic characters.

[Visual Basic]

Imports System.Text.RegularExpressions
' Example
Private Function SplitLabel(ByVal text As String) As String
    Dim options As RegexOptions = RegexOptions.Multiline
    Return Regex.Replace(text, "(?<=[a-zA-Z])(?=[A-Z])", " ", options)
End Function 'SplitLabel

FarPoint.Win.Spread.Column.Label = SplitLabel(FarPoint.Win.Spread.Column.Label)

FarPoint.Win.Spread.Row.Label = SplitLabel(FarPoint.Win.Spread.Row.Label)



[C#]

using System.Text.RegularExpressions;
//Example
private string SplitLabel(string text)
{
    RegexOptions options = RegexOptions.Multiline;
    return Regex.Replace(text, "(?<=[a-zA-Z])(?=[A-Z])", " ", options);
}

FarPoint.Win.Spread.Column.Label = SplitLabel(FarPoint.Win.Spread.Column.Label);

FarPoint.Win.Spread.Row.Label = SplitLabel(FarPoint.Win.Spread.Row.Label);


Here is a second version, which handles numeric characters.
For example, "MyColumn2LongName" becomes "My Column2 Long Name".

[Visual Basic]

Private Function SplitLabel(ByVal text As String) As String
    Dim options As RegexOptions = RegexOptions.Multiline
    Return Regex.Replace(text, "(?<=\w)(?=[A-Z])", " ", options)
End Function 'SplitLabel


[C#]

private string SplitLabel(string text)
{
    RegexOptions options = RegexOptions.Multiline;
    return Regex.Replace(text, "(?<=\\w)(?=[A-Z])", " ", options);
}


Here is a third version, which handles underscores (_) and hyphens (-).

[Visual Basic]

Private Function SplitLabel(ByVal text As String, _
   Optional ByVal removeUnderscores As Boolean = False, _
   Optional ByVal removeDashes As Boolean = False) As String
   Dim options As RegexOptions = RegexOptions.Multiline
   If removeUnderscores Then text = text.Replace("_"c, " ")
   If removeDashes Then text = text.Replace("-"c, " ")
   Return Regex.Replace(text, "(?<=\w)(?=[A-Z])", " ", options)
End Function 'SplitLabel


[C#]

private string SplitLabel(string text, bool removeUnderscores, bool removeDashes)
{
    RegexOptions options = RegexOptions.Multiline;
    if (removeUnderscores) {
        text = text.Replace('_', " ");
    }
    if (removeDashes) {
        text = text.Replace('-', " ");
    }
    return Regex.Replace(text, "(?<=\\w)(?=[A-Z])", " ", options);
}


You can only use one of these three in the same file or you get an overloads error because the optional parameters are ignored by the compile when determining uniqueness for a method signature. To the compiler all three functions are viewed as taking a string parameter and returning a string so it considers all three of them to be equivalent.

To view a printable version of this article in a PDF viewer, click here for PDF.

© 2005-2006 FarPoint Technologies, Inc. All rights reserved. Spread, Spread for Web Forms, and Spread for Windows Forms are trademarks of FarPoint Technologies, Inc. Other brand and product names are trademarks or registered trademarks of their respective holders.