Export Rich Text to Excel

I need to export data from DOORS classic modules into Excel XLSX format (not CSV). I need to export the text from DOORS while preserving rich text (like bold, bullets, etc.) and OLE objects (like embedded images).

I have the export configured, except the text that ends up in the Excel spreadsheet cell is the rich text with all markup displayed. This is not what I intended. 

The relevant code:

Buffer richTextToMove = create()
richTextToMove = ""
string richTextToMoveString = ""
//<<populate richTextToMove with the rich text and OLE-infused contents of several objects>>
//like this: richTextToMove += richTextWithOle(o."Object Text" "")
richTextToMoveString = richText(stringOf(richTextToMove))
//<<other basic Excel ole commands>>
olePut(objCell, "Value", richTextToMoveString)
//It seems that I can't output the Buffer itself using the olePut function
//so that is why I pushed it into a string. However, this is clearly my problem
//because the string will contain the rich text markup in plain text and no OLE 
//objects.

Does anyone have some guidance for how to export this way to Excel XLSX format? Thanks in advance!

Re: Export Rich Text to Excel
JoeMarfice - Fri Dec 21 17:59:05 EST 2018

It's clunky, but you can export to RTF or Word, then move everything (via code) into Excel from the RTF file.

I'm sure it can be done more directly, but I haven't gone that route. 

Yet.

Re: Export Rich Text to Excel
davidcs - Mon Dec 24 07:31:34 EST 2018

I've used this in the past. 

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

Re: Export Rich Text to Excel
Bob3 - Mon Dec 24 11:32:41 EST 2018

Thanks for your responses, JoeMarfice and davidcs!

Davidcs, your response set me on a path to use Galactic Solutions Group's Enhanced Excel Exporter as an include file for my script. It greatly accelerated my DXL solution development, but it doesn't export bullets. It treats bulleted paragraphs as if they have no bullets. 

In fact, the only script that I have seen successfully export DOORS RTF bullets to Excel is the encrypted DOORS 9.6 Export to Excel (excel.dxl) script. So I can't see how they do it or what functions they use so that I could #include it and use them without modification.

Does anyone have any tips or tricks for the export of RTF bullets to Excel?

Re: Export Rich Text to Excel
Mike.Scharnow - Tue Dec 25 08:08:32 EST 2018

you can download legacy doors versions including unencrypted excel.dxl at https://www-01.ibm.com/marketing/iwm/iwm/web/pickUrxNew.do?source=swg-tpd

They use OLE Automation, in the core it seems that there' s nothing more than 

string s = text (c,o) // column, object from current view
setCell(row, col, s)


with setCell being essentially


OleAutoObj objCell = null
OleAutoArgs args   = create

clear(args)
put(args, (intToCol col) row "") // 1, 1 -> A1
oleGet(objSheet, "Range",args, objCell)  // set objCell to the cell
olePut(objCell, "Value", s) // put the value of the object to the cell in excel

Re: Export Rich Text to Excel
Bob3 - Wed Dec 26 11:53:14 EST 2018

string s = text (c,o) // column, object from current view
setCell(row, col, s)


with setCell being essentially


OleAutoObj objCell = null
OleAutoArgs args   = create

clear(args)
put(args, (intToCol col) row "") // 1, 1 -> A1
oleGet(objSheet, "Range",args, objCell)  // set objCell to the cell
olePut(objCell, "Value", s) // put the value of the object to the cell in excel

Thanks for your response, Mike.

The problem is that versions of excel.dxl prior to DOORS 9.6 do not export bulleted paragraphs with bullets. Only the encrypted DOORS 9.6+ versions of excel.dxl export bullets correctly. 

At present, I am not aware of any unencrypted example DXL that successfully exports bulleted RTF paragraphs to Excel. 

Re: Export Rich Text to Excel
morast - Mon Jan 07 03:40:41 EST 2019

Correct me if I'm wrong, but I would say that the problem with bulleted lists into Excel is that Excel does not support bulleted lists. A fair reason for exporters to not support it. (I don't think DOORS 9.6+ versions och excel.dxl supports it either, at least not for me :-)

What you can do in DXL is to loop through RichText, Identify bulleted lists and add a bullet character where appropriate. But of course some added characters does not make a bulleted list. 

Moreover this is not a very good idea if you are aiming for a round-trip solution. Then you would have to translate back in your import function and make sure your Excel users follow some strict guidelines for how to create bullets in Excel.  

Re: Export Rich Text to Excel
Bob3 - Mon Jan 07 11:27:07 EST 2019

Morast, thanks for your reply. What you proposed is essentially what I ended up doing.

As I access the Object Text, I process its rich text for RTF bullets and replace the bullet rich text with a plain text bullet character. Here is a snippet of the DXL:

const string bulletRTF = "{\\pntext\\f1\\'B7\\tab}" //DOORS' RTF for bullets
const string bulletSubstitute = "• "
int theOffset
int theLength
string richTextToMoveString = ""
Buffer richTextToMove = create()
richTextToMove = ""

//Loop through objects in module

richTextToMoveString = richText(o."Object Text")

//Loops through rich text from Object Text, replacing all instances of bulletRTF with bulletSubstitute
while (findPlainText(richTextToMoveString, bulletRTF, theOffset, theLength, false)) {
        richTextToMove += richTextToMoveString[0:theOffset-1]
        richTextToMove += bulletSubstitute
        richTextToMoveString = richTextToMoveString[theOffset+theLength:]
}

//Output to Excel using Galactic Solutions Group's Enhanced Excel Exporter as an include file for the script