Opening and closing of excel files

Is it possible to open and close excel sheets using DXL Scripts. If it is possible, can you please send me the script across.

Thanks
Matthew
Lion29 - Fri Nov 13 03:58:04 EST 2009

Re: Opening and closing of excel files
SystemAdmin - Fri Nov 13 04:22:32 EST 2009

As a starting point look into the file (under your DOORS installation directory)

lib\dxl\standard\export\office\excel.dxl

This is the DOORS standard Excel export (File / Export / Microsoft Office / Excel).

Re: Opening and closing of excel files
Peter_Albert - Fri Nov 13 04:34:16 EST 2009

The key to directly writing to Excel is OLE automatisation. If you just want to export existing views to Excel, I recommend Michael Sutherlands Enhanced Export to Excel program developed for Galactic Soluctions (http://galactic-solutions.com/downloads/GalacticDownloadExcel.htm.

If I remember correctly, it was David Pechacek who initially put all the OLE stuff in a dedicated include file which you can use for your own tailored export to Excel (actually it is put in the two attached files, ole.inc and excel.inc). You can find his original files at Kevin Murphy's site: http://www.baselinesinc.com/?page_id=11. The attached files are somewhat modified by myself in order to add additional functions (adding a sheet to a workbook, setting borders for cell ranges, setting the auto-filter, etc.)

When you use the files, make sure to modify the line #include <../include/ole.inc> in excel.inc such that it matches the place where you put the ole.inc.

Once you have it put in place, you can have a look at e.g. void showColors() in excel.inc to get an idea on how to actually export to Excel. The routine opens an Excel sheet and displays the available colours.

For understanding how it works, it is important to know that the whole set of routines works with global variables, e.g. a call to 'openExcel()' sets the global variable 'objExcel', which you can then use in 'makeVisible(objExcel)'.

Regards,

Peter
Attachments

attachment_14347286_excel_ole.zip

Re: Opening and closing of excel files
tommy3824 - Fri Nov 13 13:47:11 EST 2009

string filename = "somefile.xls" 
    
 OleAutoObj ObjExcel = oleGetAutoObject("Excel.Application") // Connect With Excel
 
 if(null ObjExcel) // Excel not opened
        ObjExcel = oleCreateAutoObject("Excel.Application") // Open Excel
 
 OleAutoObj objWB = null
 OleAutoObj objWBs = null
 OleAutoArgs autoArgs = create
 
 
 olePut(objExcel,"Visible",true) // Make excel visible
 oleGet(objExcel,"Workbooks", objWBS)
 put (autoArgs,filename"")
 oleMethod(objWBS,"Open",autoArgs,objWB)

You can arrange these in functions to make it easier to access and less sloppy. objWB is now the active workbook from the file that was opened. I would add something like...

if(null objWB)
{
    clear autoArgs
        put (autoArgs,1)                // Create new workbook (different numbers are assigned to create different items)
        oleMethod(objWBS,"Add",autoArgs,objWB)
        clear autoArgs
        put(autoArgs,filename"")
        oleMethod(objWB,"SaveAs",autoArgs)
}

which will create the sheet if it doesn't exist and saves it. Then finally restore the memory...

delete autoArgs
objWB = null
objWBS = null
objExcel = null

Re: Opening and closing of excel files
Mathias Mamsch - Sat Nov 14 15:04:28 EST 2009

tommy3824 - Fri Nov 13 13:47:11 EST 2009

string filename = "somefile.xls" 
    
 OleAutoObj ObjExcel = oleGetAutoObject("Excel.Application") // Connect With Excel
 
 if(null ObjExcel) // Excel not opened
        ObjExcel = oleCreateAutoObject("Excel.Application") // Open Excel
 
 OleAutoObj objWB = null
 OleAutoObj objWBs = null
 OleAutoArgs autoArgs = create
 
 
 olePut(objExcel,"Visible",true) // Make excel visible
 oleGet(objExcel,"Workbooks", objWBS)
 put (autoArgs,filename"")
 oleMethod(objWBS,"Open",autoArgs,objWB)

You can arrange these in functions to make it easier to access and less sloppy. objWB is now the active workbook from the file that was opened. I would add something like...

if(null objWB)
{
    clear autoArgs
        put (autoArgs,1)                // Create new workbook (different numbers are assigned to create different items)
        oleMethod(objWBS,"Add",autoArgs,objWB)
        clear autoArgs
        put(autoArgs,filename"")
        oleMethod(objWB,"SaveAs",autoArgs)
}

which will create the sheet if it doesn't exist and saves it. Then finally restore the memory...

delete autoArgs
objWB = null
objWBS = null
objExcel = null

NEVER open Excel like this:

OleAutoObj ObjExcel = oleGetAutoObject("Excel.Application") // Connect With Excel 
 if(null ObjExcel) // Excel not opened
    ObjExcel = oleCreateAutoObject("Excel.Application") // Open Excel

I guess someone wrote this code because he did not know how to prevent Excel from staying active in the background, after he shut it down. And I find it in every Excel library I come across ... :-\

You shall never use an already open Excel, because people work in Excel, and if you "hijack" his Excel with his unsaved 3 hours of work, create some statistics and then shut it down, the user will get very annoyed!

You only use "oleCreateAutoObject" to create a new fresh excel application instance... Then whenever you create an "oleAutoObj" variable, you call "oleCloseAutoObject" to release the OLE reference before you set the variable to null. Then your Excel will close down fine and you won't have to mess around with the users Excel.

Regards, Mathias

Re: Opening and closing of excel files
moheganburnerIII - Fri Oct 21 16:06:44 EDT 2011

I understand the concept, but I still wind up with multiple excel processes running after close. I'm using this:

oleCloseAutoObject wbs
oleCloseAutoObject wb
oleCloseAutoObject ws
oleCloseAutoObject ex
wbs = null 
wb = null
ws = null
ex = null

to close all my oleAutoObj, but Excel still runs in the background. Do I have a syntax problem?

Re: Opening and closing of excel files
Mathias Mamsch - Fri Oct 21 17:42:42 EDT 2011

moheganburnerIII - Fri Oct 21 16:06:44 EDT 2011

I understand the concept, but I still wind up with multiple excel processes running after close. I'm using this:

oleCloseAutoObject wbs
oleCloseAutoObject wb
oleCloseAutoObject ws
oleCloseAutoObject ex
wbs = null 
wb = null
ws = null
ex = null

to close all my oleAutoObj, but Excel still runs in the background. Do I have a syntax problem?

Very common problem. Search for Excel open in the forum you will find several posts about this. In short: It happens, because you do not free ressources correctly. Make sure every OleAutoObj that you allocate is explicitly freed with oleCloseAutoObject. Quit Excel only after you did this, then the process will end. Regards, Mathias

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS