Welcome to Telelogic Product Support
  Home Downloads Knowledgebase Case Tracking Licensing Help Telelogic Passport
Telelogic DOORS (steve huntington)
Decrease font size
Increase font size
Topic Title: Exporting Calculated Values to a specific Col/Row in Excel
Topic Summary:
Created On: 19-May-2004 17:59
Status: Post and Reply
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
Quick Reply Quick Reply
Subscribe to this topic Subscribe to this topic
E-mail this topic to someone. E-mail this topic
Bookmark this topic Bookmark this topic
View similar topics View similar topics
View topic in raw text format. Print this topic.
Answer This question was answered by Jonathan Marshall, on Thursday, May 20, 2004 1:20 PM

Answer:
Hi,

This is the example from the DXL manual modified so it works in Excel 97

/*
This function sets a specific property for a specific cell in a
specific Excel Worksheet. If it succeeds it returns null
otherwise it returns an error string
*/
string SetExcelCell(OleAutoObj objSheet,
int xCellLoc,
int yCellLoc,
string property,
string value) {
OleAutoObj objCells = null
OleAutoObj objRange = null
OleAutoArgs autoArgs = create
oleGet(objSheet,"Cells", objCells)
string result = null
put(autoArgs, yCellLoc)
put(autoArgs, xCellLoc)
result = oleGet(objCells, "Item", autoArgs, objRange)
if (result == null){
result = olePut(objRange, property, value)
} else {
print result "\n"
}
return result
} /* SetExcelCell */

OleAutoObj objExcel = oleCreateAutoObject("Excel.Application")
OleAutoArgs autoArgs = create
OleAutoObj objSpreadSheet
OleAutoObj objWorkbooks
OleAutoObj objWorkbook
OleAutoObj objWorksheets
bool excelVisible
/* Make Excel visible to the user */
oleGet(objExcel, "visible", excelVisible)
if (excelVisible == false)
olePut(objExcel,"visible",true)
/* Add new workbook */
oleGet(objExcel,"Workbooks", objWorkbooks)
oleMethod(objWorkbooks,"Add")
oleGet(objExcel,"ActiveWorkbook", objWorkbook)
oleGet(objWorkbook, "Worksheets", objWorksheets)
clear(autoArgs)
put(autoArgs, "Sheet1")
oleGet(objWorksheets,"Item", autoArgs, objSpreadSheet)
/* Activate selected sheet */
oleMethod(objSpreadSheet, "Activate")
SetExcelCell(objSpreadSheet, 2, 2, "Value", (10 ""))
SetExcelCell(objSpreadSheet, 2, 3, "Value", (20 ""))
SetExcelCell(objSpreadSheet, 2, 4, "Value", (30 ""))
SetExcelCell(objSpreadSheet, 2, 5, "Value", (40 ""))
SetExcelCell(objSpreadSheet, 2, 6, "Value", (50 ""))
SetExcelCell(objSpreadSheet, 2, 7, "Formula","=SUM(B2:B6)")


Hope this helps

Jon
 19-May-2004 17:59
User is offline View Users Profile Print this message


Carlton Rubio

Posts: 23
Joined: 22-Jan-2003

Hi All,

I am looking for some guidance to take a series of calculated metrics (performed via DXL) and to export them directly to a specific Row in Excel. The data calculations are a no brainer, but the export functions seem cumbersome. I have looked at the Galactic Solutions Excel Export Script (and their presentation on how it was developed - Kudos to Michael Sutherland great job!!!), but it seemed like overkill for what I need to do.

Basically I want to export a set of metrics as follows each time the script is run (I will store the last row updated as a module attribute):
First Run (Today)
Col1Row1
Second Run (Tomorrow)
Col1Row2
Third Run (some time in future)
Col1Row3
etc..


I was looking for a simple dxl to show me how to open a workbook/worksheet, export my data to a series of specific cells (a row), and then to close it. Am I oversimplifying? Or has someone created a "frame work" DXL like this? Error checking and the like are niceties (so I don't really need them now) but I just need the basics to get started.

Any help would be appreciated.
Thanks
Carlton
Report this to a Moderator Report this to a Moderator
 20-May-2004 10:52
User is offline View Users Profile Print this message


Jonathan Marshall

Posts: 27
Joined: 10-Apr-2003

Answer Answer
Hi,

This is the example from the DXL manual modified so it works in Excel 97

/*
This function sets a specific property for a specific cell in a
specific Excel Worksheet. If it succeeds it returns null
otherwise it returns an error string
*/
string SetExcelCell(OleAutoObj objSheet,
int xCellLoc,
int yCellLoc,
string property,
string value) {
OleAutoObj objCells = null
OleAutoObj objRange = null
OleAutoArgs autoArgs = create
oleGet(objSheet,"Cells", objCells)
string result = null
put(autoArgs, yCellLoc)
put(autoArgs, xCellLoc)
result = oleGet(objCells, "Item", autoArgs, objRange)
if (result == null){
result = olePut(objRange, property, value)
} else {
print result "\n"
}
return result
} /* SetExcelCell */

OleAutoObj objExcel = oleCreateAutoObject("Excel.Application")
OleAutoArgs autoArgs = create
OleAutoObj objSpreadSheet
OleAutoObj objWorkbooks
OleAutoObj objWorkbook
OleAutoObj objWorksheets
bool excelVisible
/* Make Excel visible to the user */
oleGet(objExcel, "visible", excelVisible)
if (excelVisible == false)
olePut(objExcel,"visible",true)
/* Add new workbook */
oleGet(objExcel,"Workbooks", objWorkbooks)
oleMethod(objWorkbooks,"Add")
oleGet(objExcel,"ActiveWorkbook", objWorkbook)
oleGet(objWorkbook, "Worksheets", objWorksheets)
clear(autoArgs)
put(autoArgs, "Sheet1")
oleGet(objWorksheets,"Item", autoArgs, objSpreadSheet)
/* Activate selected sheet */
oleMethod(objSpreadSheet, "Activate")
SetExcelCell(objSpreadSheet, 2, 2, "Value", (10 ""))
SetExcelCell(objSpreadSheet, 2, 3, "Value", (20 ""))
SetExcelCell(objSpreadSheet, 2, 4, "Value", (30 ""))
SetExcelCell(objSpreadSheet, 2, 5, "Value", (40 ""))
SetExcelCell(objSpreadSheet, 2, 6, "Value", (50 ""))
SetExcelCell(objSpreadSheet, 2, 7, "Formula","=SUM(B2:B6)")


Hope this helps

Jon

-------------------------
Jonathan Marshall
EADS Astrium
Report this to a Moderator Report this to a Moderator
 20-May-2004 13:20
User is offline View Users Profile Print this message


Carlton Rubio

Posts: 23
Joined: 22-Jan-2003

Thanks Jonathan,

I was able to use the "Galactic Solutions" Excel Script and the Excel Export from the Doors DXL Library (I "skinny'd" both down). My solution looks very similar to yours. I also like that yours shows how to perform a calculation in Excel.

I am marking your response as the answer, since it contains everything I was looking for.

Much Appreciated
Carlton
PS Which manual are you referring to (I could not find it in the one that comes with Doors 6.0).

Edited: 20-May-2004 at 13:25 by Carlton Rubio
Report this to a Moderator Report this to a Moderator
 21-May-2004 14:20
User is offline View Users Profile Print this message


Jonathan Marshall

Posts: 27
Joined: 10-Apr-2003

Its in the DXL reference manual for both versions 5.2 (pg 370) and 7.0 (pg 524) under the olePut function. I haven't got the manual for v6.0 but I assume its there too.

Jon

-------------------------
Jonathan Marshall
EADS Astrium
Report this to a Moderator Report this to a Moderator
 26-Jul-2006 23:40
User is offline View Users Profile Print this message


Krishna Kandala

Posts: 170
Joined: 8-Jul-2006

Hi All,
I am opening this item for a specific purpose. Let me admit that I am attempting for the first time to understand OLE objects. I was going through most of the items on this topic in this forum. I created an Excel file in the C: drive and I want to record the results from the DOORS module. I am not able to figure out how I can make the OLE connection to the specific Excel file already existing (may be a stupid question). Will someone please post the code with some explanation?
You'll be saving a lot of hardwork for me. Thanks in advance.

- Krishna
Report this to a Moderator Report this to a Moderator
 27-Jul-2006 13:12
User is offline View Users Profile Print this message


Carlton Rubio

Posts: 23
Joined: 22-Jan-2003

Hi,
The first place I looked when I needed to do this was Galactic Solutions Download Page - Enhanced Excel Exporter. The code for that is rather complex but describes how to make the OLE connections you desire. Also if you look at Jonathans response above, it also simplifies things a little more. Finally My solution, morphed into the following code and excel spreadsheet. I also attached another file that I had worked on to allow me to work with DOORS tables in Excel.

Hopefully from all the above you can work through to a solution.

Oh Yeah, I know that I posted some other comments on OLE interaction in other threads, so do a search on questions/responses from me.

Good Luck!
Carlton


Report this to a Moderator Report this to a Moderator
 29-Jul-2006 14:45
User is offline View Users Profile Print this message


Krishna Kandala

Posts: 170
Joined: 8-Jul-2006

Hi,
Thanks for all the information. I am making progress in this matter. I'll come back to this subject when I find myself in a fix that I cannot sort.
- Krishna

Report this to a Moderator Report this to a Moderator
 2-Aug-2006 20:42
User is offline View Users Profile Print this message


Krishna Kandala

Posts: 170
Joined: 8-Jul-2006

Hi All,

Thanks again for all the input and suggestions. I had considerable success in exporting calculated results from DOORS modules to an external Excel file.

- Krishna
Report this to a Moderator Report this to a Moderator
Statistics
20925 users are registered to the Telelogic DOORS forum.
There are currently 0 users logged in.
The most users ever online was 15 on 15-Jan-2009 at 16:36.
There are currently 0 guests browsing this forum, which makes a total of 0 users using this forum.
You have posted 0 messages to this forum. 0 overall.

FuseTalk Standard Edition v3.2 - © 1999-2009 FuseTalk Inc. All rights reserved.