locked
Any way insert HTML to cell? RRS feed

  • Question

  • I need to add HTML to an excel cell, but that HTML is not necessarily composed of paragraphs (as your example assumes), for example with <BR>, <FONT>, <B>, tables, <UL>, <LI>.

    If I do it directly, the HTML is not interpreted, it's shown like plain text showing the tags.

    If I convert it to a Document with the "LoadHTML", It doesn't generate paragraphs as the example population assumes.

    How can I achieve this?

    thank you
    Wednesday, May 27, 2015 6:38 AM

Answers

  • You could use a macro to export the cells with the HTML code to an HTML document: select the cells first, then use this -

    Sub MakeHTMLFile()
        Dim myFileName As String
        Dim myHTML As String
        Dim FileNum As Integer
        Dim myC As Integer
        Dim myR As Long
        Dim retVal As Variant
        
        'Create filename
        myFileName = ThisWorkbook.Path & "\HTML Preview.html"
        
        'Create the string that is the file contents
        myHTML = "<HTML>"
        myHTML = myHTML & Chr(10) & "<HEAD>"
        myHTML = myHTML & Chr(10) & "<META HTTP-EQUIV=""Content-Type"" CONTENT=""text/html; charset=windows-1252"">"
        myHTML = myHTML & Chr(10) & "<META NAME=""Generator"" CONTENT=""Microsoft Word 97"">"
        myHTML = myHTML & Chr(10) & "</HEAD>"
        myHTML = myHTML & Chr(10) & "<BODY>"
        myHTML = myHTML & Chr(10) & "<a>"
        myHTML = myHTML & Chr(10) & "</p><p>"
        For myR = Selection.Cells(1).Row To Selection.Cells(Selection.Cells.Count).Row
            For myC = Selection.Cells(1).Column To Selection.Cells(Selection.Cells.Count).Column
                myHTML = myHTML & Cells(2, myC).Value & "</p><p>"
                myHTML = myHTML & Cells(myR, myC).Value & "</p><p>"
            Next myC
        Next myR
        myHTML = myHTML & Chr(10) & "</a><br>"
        myHTML = myHTML & Chr(10) & "<FONT SIZE=2></FONT></BODY>"
        myHTML = myHTML & Chr(10) & "</HTML>"
        myHTML = Replace(myHTML, Chr(10), "</p><p>")
        On Error GoTo ErrHandler
        FileNum = FreeFile    ' next free filenumber
        Open myFileName For Output As #FileNum    ' creates the new file
        Print #FileNum, myHTML
        Close #FileNum    ' close the file
        
        MsgBox "File " & myFileName & " was created."
        
        retVal = Shell("C:\Program Files\Internet Explorer" _
        & "\IEXPLORE.EXE " & myFileName, vbNormalFocus)
        
        Exit Sub
        
    ErrHandler:
        MsgBox "Some sort of error occurred while creating file."
        
    End Sub


    Wednesday, May 27, 2015 2:22 PM
  • Try this third party XLS library,use Document.LoadHTML() methodpire.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..You can refer this article :

    Insert HTML-Formatted RichText into Excel Cell in C#

    Here is a demo :

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



    Thursday, May 28, 2015 2:19 AM

All replies

  • You could use a macro to export the cells with the HTML code to an HTML document: select the cells first, then use this -

    Sub MakeHTMLFile()
        Dim myFileName As String
        Dim myHTML As String
        Dim FileNum As Integer
        Dim myC As Integer
        Dim myR As Long
        Dim retVal As Variant
        
        'Create filename
        myFileName = ThisWorkbook.Path & "\HTML Preview.html"
        
        'Create the string that is the file contents
        myHTML = "<HTML>"
        myHTML = myHTML & Chr(10) & "<HEAD>"
        myHTML = myHTML & Chr(10) & "<META HTTP-EQUIV=""Content-Type"" CONTENT=""text/html; charset=windows-1252"">"
        myHTML = myHTML & Chr(10) & "<META NAME=""Generator"" CONTENT=""Microsoft Word 97"">"
        myHTML = myHTML & Chr(10) & "</HEAD>"
        myHTML = myHTML & Chr(10) & "<BODY>"
        myHTML = myHTML & Chr(10) & "<a>"
        myHTML = myHTML & Chr(10) & "</p><p>"
        For myR = Selection.Cells(1).Row To Selection.Cells(Selection.Cells.Count).Row
            For myC = Selection.Cells(1).Column To Selection.Cells(Selection.Cells.Count).Column
                myHTML = myHTML & Cells(2, myC).Value & "</p><p>"
                myHTML = myHTML & Cells(myR, myC).Value & "</p><p>"
            Next myC
        Next myR
        myHTML = myHTML & Chr(10) & "</a><br>"
        myHTML = myHTML & Chr(10) & "<FONT SIZE=2></FONT></BODY>"
        myHTML = myHTML & Chr(10) & "</HTML>"
        myHTML = Replace(myHTML, Chr(10), "</p><p>")
        On Error GoTo ErrHandler
        FileNum = FreeFile    ' next free filenumber
        Open myFileName For Output As #FileNum    ' creates the new file
        Print #FileNum, myHTML
        Close #FileNum    ' close the file
        
        MsgBox "File " & myFileName & " was created."
        
        retVal = Shell("C:\Program Files\Internet Explorer" _
        & "\IEXPLORE.EXE " & myFileName, vbNormalFocus)
        
        Exit Sub
        
    ErrHandler:
        MsgBox "Some sort of error occurred while creating file."
        
    End Sub


    Wednesday, May 27, 2015 2:22 PM
  • Try this third party XLS library,use Document.LoadHTML() methodpire.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..You can refer this article :

    Insert HTML-Formatted RichText into Excel Cell in C#

    Here is a demo :

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



    Thursday, May 28, 2015 2:19 AM