Insert HTML-Formatted RichText into Excel Cell in C#

Some time back, one of registered members on our Forum had a requirement to display the value of HTML code in Excel cell. This article is aimed to provide a fine way to manage this issue using Spire.Doc and Spire.XLS.

Main Method:

At present, we have to use Document.LoadHTML() method which is available in Spire.Doc.Document class to load HTML string to a Word document, this way, HTML formatted text will be save in specific paragraphs. Then, get the paragraph with rich text style and return a RichTextString object, save RichText to a specified CellRange. Besides, the paragraph text style must be applied to this CellRange.

Detailed Steps:

Step 1: Create a new Workbook and Word document.

Workbook workbook = new Workbook();
Document doc = new Document();

Step 2: Save the HTML code to StringReader and load the HTML string to Word document.

StringReader sr = new StringReader("<span style=\"border-width:thin;border-color:#FFFFFF;\"><font color=#000000 size=8><b>U = Unchanged rate</b></font></span>");
doc.LoadHTML(sr, XHTMLValidationType.None);

Step 3: Get the formatted text from Word document and save to cell 'A4' in the first worksheet.

foreach (Section section in doc.Sections)
{
    foreach (Paragraph paragraph in section.Paragraphs)
    {
        if (paragraph.Items.Count > 0)
        {
           workbook.Worksheets[0].Range["A4"].RichText.Text += paragraph.Text;
        }   
     }
}

Step 4: Apply text style including font color and font size to cell 'A4'.

int index = 0;

foreach (var item in paragraph.Items)
{
    if (item is Spire.Doc.Fields.TextRange)
    {
        for (int i = index; i < (item as Spire.Doc.Fields.TextRange).Text.Length + index; i++)
        {
            ExcelFont excelFont = workbook.CreateFont();
            excelFont.FontName = (item as Spire.Doc.Fields.TextRange).CharacterFormat.FontName;
            excelFont.Size = (item as Spire.Doc.Fields.TextRange).CharacterFormat.FontSize;
            excelFont.IsBold = (item as Spire.Doc.Fields.TextRange).CharacterFormat.Bold;
            excelFont.IsItalic = (item as Spire.Doc.Fields.TextRange).CharacterFormat.Italic;
            excelFont.Underline = (FontUnderlineType)(item as Spire.Doc.Fields.TextRange).CharacterFormat.UnderlineStyle; excelFont.Color = (item as Spire.Doc.Fields.TextRange).CharacterFormat.TextColor;
            workbook.Worksheets[0].Range["A4"].RichText.SetFont(i, i, excelFont);
        }
    }
    index += (item as Spire.Doc.Fields.TextRange).Text.Length;

}

Step 5: Change the width and height of the row to achieve the best fit.

workbook.Worksheets[0].Range["A4"].AutoFitRows();

Step 6: Save changes to the workbook in a new file.

workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);

HTML-Formatted Text in Excel would be shown as:

Insert HTML-Formatted RichText into Excel Cell in C#

Full Code:

Workbook workbook = new Workbook();
Document doc = new Document();

StringReader sr = new StringReader("<span style=\"border-width:thin;border-color:#FFFFFF;\"><font color=#000000 size=8><b>U = Unchanged rate</b></font></span>");
doc.LoadHTML(sr, XHTMLValidationType.None);

int index = 0;

foreach (Section section in doc.Sections)
{
    foreach (Paragraph paragraph in section.Paragraphs)
    {
        if (paragraph.Items.Count > 0)
        {
            workbook.Worksheets[0].Range["A4"].RichText.Text += paragraph.Text;
            foreach (var item in paragraph.Items)
            {
                if (item is Spire.Doc.Fields.TextRange)
                {
for (int i = index; i < (item as Spire.Doc.Fields.TextRange).Text.Length + index; i++)
{
    ExcelFont excelFont = workbook.CreateFont();
    excelFont.FontName = (item as Spire.Doc.Fields.TextRange).CharacterFormat.FontName;
    excelFont.Size = (item as Spire.Doc.Fields.TextRange).CharacterFormat.FontSize;
    excelFont.IsBold = (item as Spire.Doc.Fields.TextRange).CharacterFormat.Bold;
    excelFont.IsItalic = (item as Spire.Doc.Fields.TextRange).CharacterFormat.Italic;
    excelFont.Underline = (FontUnderlineType)(item as Spire.Doc.Fields.TextRange).CharacterFormat.UnderlineStyle;
    excelFont.Color = (item as Spire.Doc.Fields.TextRange).CharacterFormat.TextColor;
    workbook.Worksheets[0].Range["A4"].RichText.SetFont(i, i, excelFont);
}
                }
                index += (item as Spire.Doc.Fields.TextRange).Text.Length;
            }
        }
    }
}
workbook.Worksheets[0].Range["A4"].AutoFitRows();
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);

Insert HTML-Formatted RichText into Excel Cell in C#

Main Method:

Detailed Steps:

See Also