Re: How to Insert HTML-formatted RichText into Excel 2007 cell?

Hi!

I have to develop simple "report builder". It gets data from MS Sharepoint (SP) List and puts it into Excel 2007 file. The native SP "export to spreadsheet" feature doesn't suit for some reason (it can't make subtitles/subtotals for  queries with "GROUP BY" part). Query to SP List returns RichText data as HTML-formatted string - with using div,p,br,font,color,b,i,... etc. elements and  , &lt:, >, & etc.things. I need to insert it into spreadsheet cell as formatted text. How to do it?

Regards.

 

Since HTML and Open XML are different file formats, what you want to do is format converting. It's never a easy task. Meanwhile, this is not within the goal of the SDK.

I'm curious why you need to keep the HTML format? maybe you could start the report generation from templates with specific formats, which look like the formats in HTML chunk.

btw, could you tell how will the reports be used typically? send, download, archeve, present online...? and any detail information in your user scenarioI want to understand what the users doing with the SDK and what solutions are built upon it. Thanks:)

 

Shuangshuang (MS)

Thanks for Your answer, Shuangshuang.

Of course, I don't wont to keep the HTML format. I need to insert some RichText data into spreadsheet cell. I get this text as HTML-formatted text. MS WSS 2007 returns it in that form. I do a query against MS WSS list (like a database table) and I got RichText field as HTML-formatted text. And I need to translate it to SpreadsheetML. I know, MS WSS can do it. So, it can generate flat spreadsheet with properly converted data. But, unfortuanetely, the flat report doesn't suit for me. I have to do some more complex data processing and some formatting job on the generated spreadsheet. I need to insert some subtitles rows for multilevel GROUPPED-BY queries and some subtotals. The report must be in the Excel 2007 format as the most convenient format for customers. As I know, customers use our reports for printing and for further processing.

Regards.

Hi Antipa,

Your reply is informative, and your requirements make sense. My knowledge tells that there's no way to do it in one step, you need firstly parse the HTML content, then generate the formats/data processing logic by your own.

Hi Antipa,

Try using Regular Expressions: you can clean the html with it.

An example:

Dim RegEx As RegExp
Set RegEx = New RegExp
Dim strHTML As String //the html text that you want to replace
RegEx.Pattern = (.|\n)*? 'place the string inside quotes and greater than and less than characters: this editor filters them and they don't show up.

Go through your recordset and use RegEx.Replace:

do while ...
strHTML = RegEx.Replace(strHTML, "")
end while

Hope it helps

Traveler:

Hi Antipa, Try using Regular Expressions: you can clean the html with it.

Thanks for Your answer, but I don't want to clean HTML. I want to format text in spreadsheet cell as HTML formatting does. So, I need to translate formatting description from HTML to SpreadsheetML.

I remember someone mentioned that Aspose.Word works well for this issue.

Currently, SDK does not support format conversion.