(This question was formerly titled "C# / WPF : Going from Excel Interop "Range" to WPF "FlowDocument"" however I've made progress on that front that allows me to restrict my question. I'm leaving the original question below so existing answers will still make sense.)
I'm using Office Interop to read the contents of cells in an Excel worksheet. Some of those cells contain Rich Text (for example some words are italicized but not the whole cell) and I would like to capture them as RTF so I can then display them into WPF controls.
I have been able to obtain the RTF contents of cells using the clipboard API, where I use Excel Interop to copy a Range of one cell to the clipboard, and then read the clipboard, like so:
// Step 1 : retrieve the RTF from the clipboard as a string
string txt = Clipboard.GetText(TextDataFormat.Rtf);
// Step 2 : create a FlowDocument object and a TextRange object:
FlowDocument doc = new FlowDocument();
TextRange tr = new TextRange(doc.ContentStart, doc.ContentEnd);
// Step 3 : convert the clipboard string to a stream
byte[] byteArray = Encoding.ASCII.GetBytes(txt);
MemoryStream stream = new MemoryStream(byteArray);
// Step 4 : load that stream into TextRange
tr.Load(stream, DataFormats.Rtf);
If I then assign "doc" to the Document property of, say, a RichTextBox control, it'll display the content of the Excel cell with the exact same formatting as Excel does, down to colored words and font sizes.
However, this is extremely slow. It may take minutes to load a thousand cells that way, even if most are empty.
So here's my updated question : clearly Excel has a mechanism for returning the RTF content of an Excel cell, otherwise my Clipboard code couldn't work. But is there are more efficient way than the Clipboard to exploit that mechanism ? Ideally through Interop ?
Original question :
This may be an unusual question but as I'm quite new to C#, WPF and Interop, I might be going about things the wrong way so don't hesitate to offer a better approach. Here's what I'm trying to do :
I'm coding a WPF application that uses Office Interop to grab the contents of cells from an Excel worksheet. That content is text which may contain some formatting (for example some words are in bold, others are in italics). The application then displays that content in a "FlowDocumentScrollViewer" control on its GUI.
I want this "FlowDocumentScrollViewer" control to render the content from the Excel cell exactly as it appears in Excel, with formatting and everything.
The best I've managed so far is to display the cell's content without any formatting. Here's how this works : I use Office Interop to read a Range of cells from the worksheet and take their Value2 property. Value2 is of type "object". Then I create a FlowDocument object out of it, like so:
FlowDocument doc = new FlowDocument();
Paragraph p = new Paragraph(new Run(Variable_containing_a_Value2.ToString()));
doc.Blocks.Add(p);
And then I store this FlowDocument into the "FlowDocumentScrollViewer" Document property.
Now since I'm using "ToString()" on the Value2 I'm not surprised that any formatting information this object might contain disappears past this point.
My problem is, I haven't been able to find a way to create that FlowDocument, from that Value2 object, that preserves formatting.
Now, I know there has to be a way to get that information through, because when I copy my Excel cell and paste it in Word, for example, then the formatting is carried through. I just don't know how.
Help me Obiwans, you're my only hope, as even Google has failed me.