C# Excel.Range Set a cell Value with HTML data

Question

I need to find a way to set the excel cell value with html and have it display with the correct formatting. We load data from our database that sometimes contains html data with text mark up or html borders. So I need to be able to tell excel that it is html data to render or convert the html to excel mark up or strip the html mark up, but that is not ideal.

var range = (Excel.Range)worksheet.Cells[rowIndex, columnIndex];
range.Value = "<b>This is <i>html text</i></b> that <font>could contain</font> any type of html;<br/>";

C#, so vba script won't really help. I tagged it in, maybe I can convert it or at least see the concept. — Leon van Wyk
– Leon van Wyk, Commented Jul 15, 2015 at 6:05

Community · Accepted Answer · 2017-05-23 11:58:16Z

1

I do not have enought reputation to post this as a comment but here it goes: Take a close look at HTML Text with tags to formatted text in an Excel cell There is a solution in VBA that works and it can be translated into c# with a little bit of effort :)

static void Main(string[] args)
    {
        object misValue = System.Reflection.Missing.Value;
        Type excelApplication = Type.GetTypeFromProgID("Excel.Application");
        dynamic excel = Activator.CreateInstance(excelApplication);
        dynamic xlWorkBook = excel.Workbooks.Add(misValue);
        dynamic SourceSheets = xlWorkBook.Worksheets[1];

        Type IeType = Type.GetTypeFromProgID("InternetExplorer.Application");
        dynamic Ie = Activator.CreateInstance(IeType);
        SourceSheets.Range("A1").Value = "<b>This is <i>html text</i></b> that <font>could contain</font> any type of html;<br/>";
        Ie.Visible = false;
        Ie.Navigate("about:blank");
        Ie.document.body.InnerHTML = SourceSheets.Range("A1").Value;
        Ie.document.body.createtextrange.execCommand("Copy");
        SourceSheets.Paste(SourceSheets.Range("A1"));
        Ie.Quit();
        xlWorkBook.SaveAs("test1", misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
    }

this is really a basic translation. You have to save/dispose your object properly after but it works for what you are trying to do. I just notice while trying the "allow access" of the internet explorer for the copy/paste bin but i am sure there is a work arround for that.

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Jul 13, 2015 at 19:43

Daneau

1,1059 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Leon van Wyk Over a year ago

That solution is pure vba, do you know how to do that within c#?

Daneau Over a year ago

@Leon van Wyk I just did the translation. You will need to adjust it to your application for it to work properly (save/dispose of your COM object)

Leon van Wyk Over a year ago

thank you so much, I'll give it a try and come back to this post with the outcome.

Collectives™ on Stack Overflow

C# Excel.Range Set a cell Value with HTML data

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related