private static void SetStyleFromTag(string tag, ExcelRichText settings)
{
switch (tag.ToLower())
{
case "b":
case "strong":
settings.Bold = true;
break;
case "i":
case "em":
settings.Italic = true;
break;
case "u":
settings.UnderLine = true;
break;
case "s":
case "strike":
settings.Strike = true;
break;
case "sup":
settings.VerticalAlign = ExcelVerticalAlignmentFont.Superscript;
break;
case "sub":
settings.VerticalAlign = ExcelVerticalAlignmentFont.Subscript;
break;
case "/b":
case "/strong":
settings.Bold = false;
break;
case "/i":
case "/em":
settings.Italic = false;
break;
case "/u":
settings.UnderLine = false;
break;
case "/s":
case "/strike":
settings.Strike = false;
break;
case "/sup":
case "/sub":
settings.VerticalAlign = ExcelVerticalAlignmentFont.None;
break;
default:
// unsupported HTML, no style change
break;
}
}
protected void AddRichTextNameAndValue(OfficeOpenXml.Style.ExcelRichTextCollection rtfCollection, string name, string value)
{
OfficeOpenXml.Style.ExcelRichText ert = rtfCollection.Add(name);
ert.Bold = false;
ert.Color = System.Drawing.Color.Black;
ert = rtfCollection.Add(value);
ert.Bold = true;
ert.Color = System.Drawing.Color.Blue;
}
/// <summary>
/// Remove the specified item
/// </summary>
/// <param name="Item">The item</param>
public void Remove(ExcelRichText Item)
{
TopNode.RemoveChild(Item.TopNode);
}
/// <summary>
/// Provides basic HTML support by converting well-behaved HTML into appropriate RichText blocks.
/// HTML support is limited, and does not include font colors, sizes, or typefaces at this time,
/// and also does not support CSS style attributes. It does support line breaks using the BR tag.
///
/// This routine parses the HTML into RegEx pairings of an HTML tag and the text until the NEXT
/// tag (if any). The tag is parsed to determine the setting change to be applied to the last set
/// of settings, and if the text is not blank, a new block is added to rich text.
/// </summary>
/// <param name="range"></param>
/// <param name="html">The HTML to parse into RichText</param>
/// <param name="defaultFontName"></param>
/// <param name="defaultFontSize"></param>
public static void SetRichTextFromHtml(ExcelRange range, string html, string defaultFontName, short defaultFontSize)
{
// Reset the cell value, just in case there is an existing RichText value.
range.Value = "";
// Sanity check for blank values, skips creating Regex objects for performance.
if (String.IsNullOrEmpty(html))
{
range.IsRichText = false;
return;
}
// Change all BR tags to line breaks. http://epplus.codeplex.com/discussions/238692/
// Cells with line breaks aren't necessarily considered rich text, so this is performed
// before parsing the HTML tags.
html = System.Text.RegularExpressions.Regex.Replace(html, @"<br[^>]*>", "\r\n", RegexOptions.Compiled | RegexOptions.IgnoreCase);
string tag;
string text;
ExcelRichText thisrt = null;
bool isFirst = true;
// Get all pairs of legitimate tags and the text between them. This loop will
// only execute if there is at least one start or end tag.
foreach (Match m in System.Text.RegularExpressions.Regex.Matches(html, @"<(/?[a-z]+)[^<>]*>([\s\S]*?)(?=</?[a-z]+[^<>]*>|$)", RegexOptions.Compiled | RegexOptions.IgnoreCase))
{
if (isFirst)
{
// On the very first match, set up the initial rich text object with
// the defaults for the text BEFORE the match.
range.IsRichText = true;
thisrt = range.RichText.Add(CleanText(html.Substring(0, m.Index))); // May be 0-length
thisrt.Size = defaultFontSize; // Set the default font size
thisrt.FontName = defaultFontName; // Set the default font name
isFirst = false;
}
// Get the tag and the block of text until the NEXT tag or EOS. If there are HTML entities
// encoded, unencode them, they should be passed to RichText as normal characters (other
// than non-breaking spaces, which should be replaced with normal spaces, they break Excel.
tag = m.Groups[1].Captures[0].Value;
text = CleanText(m.Groups[2].Captures[0].Value);
if (thisrt.Text == "")
{
// The most recent rich text block wasn't *actually* used last time around, so update
// the text and keep it as the "current" block. This happens with the first block if
// it starts with a tag, and may happen later if tags come one right after the other.
thisrt.Text = text;
}
else
{
// The current rich text block has some text, so create a new one. RichText.Add()
// automatically applies the settings from the previous block, other than vertical
// alignment.
thisrt = range.RichText.Add(text);
}
// Override the settings based on the current tag, keep all other settings.
SetStyleFromTag(tag, thisrt);
}
if (thisrt == null)
{
// No HTML tags were found, so treat this as a normal text value.
range.IsRichText = false;
range.Value = CleanText(html);
}
else if (String.IsNullOrEmpty(thisrt.Text))
{
// Rich text was found, but the last node contains no text, so remove it. This can happen if,
// say, the end of the string is an end tag or unsupported tag (common).
range.RichText.Remove(thisrt);
// Failsafe -- the HTML may be just tags, no text, in which case there may be no rich text
// directives that remain. If that is the case, turn off rich text and treat this like a blank
// cell value.
if (range.RichText.Count == 0)
{
range.IsRichText = false;
range.Value = "";
}
}
}
private static void SetStyleFromTag(string tag, ExcelRichText settings)
{
switch (tag.ToLower())
{
case "b":
case "strong":
settings.Bold = true;
break;
case "i":
case "em":
settings.Italic = true;
break;
case "u":
settings.UnderLine = true;
break;
case "s":
case "strike":
settings.Strike = true;
break;
case "sup":
settings.VerticalAlign = ExcelVerticalAlignmentFont.Superscript;
break;
case "sub":
settings.VerticalAlign = ExcelVerticalAlignmentFont.Subscript;
break;
case "/b":
case "/strong":
settings.Bold = false;
break;
case "/i":
case "/em":
settings.Italic = false;
break;
case "/u":
settings.UnderLine = false;
break;
case "/s":
case "/strike":
settings.Strike = false;
break;
case "/sup":
case "/sub":
settings.VerticalAlign = ExcelVerticalAlignmentFont.None;
break;
default:
// unsupported HTML, no style change
break;
}
}
