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++)
                    {
                        Font font = (item as Spire.Doc.Fields.TextRange).CharacterFormat.Font;
                        ExcelFont excelFont = workbook.CreateFont(font);
                        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:

using Spire.Xls;
using Spire.Doc;
using System.IO;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using System.Drawing;

namespace InsertHTML2Excel
{
    class Program
    {
        static void Main(string[] args)
        {
            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++)
                                {
                                    Font font = (item as Spire.Doc.Fields.TextRange).CharacterFormat.Font;
                                    ExcelFont excelFont = workbook.CreateFont(font);
                                    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);
        }
    }
}