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:
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);
