Display formatted text in Excel

Greetings experts!

I need to export formatted strings from DOORS to Excel using automation client support. I have strings in DOORS that need to be partially highlighted or bold once they're in an Excell cell. For example, "The system shall operate safely."

I have explored doing this with RTF and HTML, but Excel dutifully puts all the RTF codes or HTML tags in the cell without rendering it as anything but plain text: "< html >The system < b >shall< /b > operate safely< /html >"

I'm using olePut(GetCell(rowCount, columnCount), "Value", myText) where myText is an RTF string or a plain text string with HTML tags in it. 

Does anyone have any ideas or guidance on how to get formatted text to display properly in Excel cells?

Re: Display formatted text in Excel
Wolfgang Uhr - Sat Aug 13 17:14:49 EDT 2016

Yes, there exists a concept to exchange data. In a past project I've writte some code to handle it, but you should know. It is a something larger project.

There is no valid open format to exchange this data and therefore you have tou use doors functions to split the richtext in text snippets. Therefore you should use the datatype "RichText".

The string "The system shall operate safely." is split in the parts "The system ", "shall" and " operate safely." and refering to the manual, you can get the information if the snippet is formated in bold, cursive and more.

Based on this information you have to feed a VBA-Script. The root script you can find if you fill an excel cell whith a string, mark a text snippet in bold while runnig the macro recorder.

This generates the VBA snippet:

    ActiveCell.FormulaR1C1 = "123 dadsf dsdsd"
    With ActiveCell.Characters(Start:=1, Length:=4).Font
        .Name = "Calibri"
        .FontStyle = "Standard"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With ActiveCell.Characters(Start:=5, Length:=5).Font
        .Name = "Calibri"
        .FontStyle = "Fett"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With

Some lines you can drop (and of course you can replace '.FontStyle = "Fett"' by '.Bold = True'), but this are the macro commands you have to execute.

Some years ago, I've written a script, which opens an empty Excel document, creates an internal macro-page, adds a set of public routines and the I could use this routines, to fill the cells. After I have finished the job the macro page is deleted and the export is done.

For a complete export which provides a permant update, you should assume that you have to write ob about 5.000 lines of code.

Re: Display formatted text in Excel
PekkaMakinen - Sun Aug 14 05:48:13 EDT 2016

Read through the code for Enhanced Export to Excel how is performs this - this exporter exports also RTF to Excel

Link http://galactic-solutions.com/downloads/GalacticDownloadExcel.htm

Re: Display formatted text in Excel
Bob3 - Thu Aug 18 00:46:29 EDT 2016

Thank you for your response, Wolfgang.

Re: Display formatted text in Excel
Bob3 - Thu Aug 18 01:01:32 EDT 2016

Pekka, your post got me going in the right direction. 

The basic idea is that I needed to use a series of OleAutoObj variables in DXL to step into Excel's properties that define formatting for cell contents. 

Here is how it works:

        OleAutoArgs objArgBlock = create
        OleAutoObj objExcelChars = null
        OleAutoObj objExcelFont = null
        OleAutoObj theCell = GetCell(rowCount, columnCount)
        int position
        int theOffset
        int searchForLength
        
        //Search for a string here, ID its location within a larger string and store in theOffset
        //Unimportant code omitted
        
        //Fills the structure that holds the starting character and the length of characters that is to be manipulated
        put( objArgBlock, "Start", position + theOffset + 1 )
        put( objArgBlock, "Length", searchForLength )
                        
        //Keep track of position in overall cell contents while seeking matches
        //Unimportant code omitted

        //Gets a handle on Excel's ActiveCell.Characters property
        objExcelChars = null
        resultExcel = oleGet( theCell, "Characters", objArgBlock, objExcelChars ) //The objArgBlock specifies the exact characters within the cell that are to be formatted
        clear( objArgBlock )

        //Gets a handle on Excel's ActiveCell.Characters.Font property
        objExcelFont = null
        resultExcel = oleGet( objExcelChars, "Font", objExcelFont ) //Note how objExcelChars is handed off from the previous oleGet command to this one

        //Sets Excel's property ActiveCell.Characters.Font.FontStyle to Bold
        resultExcel = olePut(objExcelFont, "FontStyle", "Bold") //Note how objExcelFont is handed off from the previous oleGet command to this olePut() command
        
        //Sets Excel's property ActiveCell.Characters.Font.Underline to xlUnderlineStyleSingle
        resultExcel = olePut(objExcelFont, "Underline", "2")
                
        //Pare down searchThis for the next iteration
        //Unimportant code omitted

The result in Excel looks like this: 

Re: Display formatted text in Excel
Wolfgang Uhr - Thu Aug 18 07:40:26 EDT 2016

        OleAutoArgs objArgBlock = create
        OleAutoObj objExcelChars = null
        OleAutoObj objExcelFont = null
        OleAutoObj theCell = GetCell(rowCount, columnCount)
        int position
        int theOffset
        int searchForLength
        
        //Search for a string here, ID its location within a larger string and store in theOffset
        //Unimportant code omitted
        
        //Fills the structure that holds the starting character and the length of characters that is to be manipulated
        put( objArgBlock, "Start", position + theOffset + 1 )
        put( objArgBlock, "Length", searchForLength )
                        
        //Keep track of position in overall cell contents while seeking matches
        //Unimportant code omitted

        //Gets a handle on Excel's ActiveCell.Characters property
        objExcelChars = null
        resultExcel = oleGet( theCell, "Characters", objArgBlock, objExcelChars ) //The objArgBlock specifies the exact characters within the cell that are to be formatted
        clear( objArgBlock )

        //Gets a handle on Excel's ActiveCell.Characters.Font property
        objExcelFont = null
        resultExcel = oleGet( objExcelChars, "Font", objExcelFont ) //Note how objExcelChars is handed off from the previous oleGet command to this one

        //Sets Excel's property ActiveCell.Characters.Font.FontStyle to Bold
        resultExcel = olePut(objExcelFont, "FontStyle", "Bold") //Note how objExcelFont is handed off from the previous oleGet command to this olePut() command
        
        //Sets Excel's property ActiveCell.Characters.Font.Underline to xlUnderlineStyleSingle
        resultExcel = olePut(objExcelFont, "Underline", "2")
                
        //Pare down searchThis for the next iteration
        //Unimportant code omitted

If this script slows down your doors to much, have a look onto my solution.