Topic
  • 10 replies
  • Latest Post - ‏2016-06-17T10:51:06Z by O.Wilkop
MichaelNugent
MichaelNugent
2 Posts

Pinned topic Opening a Excel Spreadsheet and accessing a CELL in DXL

‏2010-11-12T10:50:41Z |
Dear All,

I want to in DXL:
1. Open an Excel spreadsheet.
2. Access a particular cell within the spreadsheet.
3. Assign the value of the cell to a DOORS attribute.

Any help would be much appreciated.
Updated on 2012-02-09T14:00:26Z at 2012-02-09T14:00:26Z by SystemAdmin
  • Mathias Mamsch
    Mathias Mamsch
    2159 Posts

    Re: Opening a Excel Spreadsheet and accessing a CELL in DXL

    ‏2010-11-14T19:01:03Z  
    There is an example in the DOORS DXL help ("Automation client support") which will do the opposite, i.e. write a value to an excel sheet, but that is pretty much the same. Any luck trying the example?

    Regards, Mathias

    Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS
  • SystemAdmin
    SystemAdmin
    3180 Posts

    Re: Opening a Excel Spreadsheet and accessing a CELL in DXL

    ‏2010-11-18T12:22:33Z  
    Michael,

    Have a look at the attached file. Change the last bit to set the attribute.
  • SpaceApe
    SpaceApe
    11 Posts

    Re: Opening a Excel Spreadsheet and accessing a CELL in DXL

    ‏2011-12-18T15:42:56Z  
    Michael,

    Have a look at the attached file. Change the last bit to set the attribute.
    With following configuration, integer in excel cell is returned as null by the DXL script provided by NilsPalsson about an year back (in 2010).

    1) Office 10
    2) DOORS 9.3

    Any idea what might be the cause? and work-around it?
  • SystemAdmin
    SystemAdmin
    3180 Posts

    Re: Opening a Excel Spreadsheet and accessing a CELL in DXL

    ‏2011-12-19T09:00:24Z  
    • SpaceApe
    • ‏2011-12-18T15:42:56Z
    With following configuration, integer in excel cell is returned as null by the DXL script provided by NilsPalsson about an year back (in 2010).

    1) Office 10
    2) DOORS 9.3

    Any idea what might be the cause? and work-around it?

    Tried the DXL script with Excel 2010. It did work in the situation you described when I used
     

    int iValue = 0
    

     


    in getCellValue instead of the original string variable.

     

     

     

    • Pekka Mäkinen - http://www.softqa.eu/

     

     

    Updated on 2013-12-19T20:54:06Z at 2013-12-19T20:54:06Z by JAntley
  • SystemAdmin
    SystemAdmin
    3180 Posts

    Re: Opening a Excel Spreadsheet and accessing a CELL in DXL

    ‏2012-02-09T14:00:26Z  

    Tried the DXL script with Excel 2010. It did work in the situation you described when I used
     

    <pre class="javascript dw" data-editor-lang="js" data-pbcklang="javascript" dir="ltr">int iValue = 0 </pre>

     


    in getCellValue instead of the original string variable.

     

     

     

    • Pekka Mäkinen - http://www.softqa.eu/

     

     

    Pekka,
    Thanks. It works with integer type variable. I didn't expect excel variable types to match to DXL variable types.

    Thanks again
  • Avinash Singh
    Avinash Singh
    1 Post

    Re: Opening a Excel Spreadsheet and accessing a CELL in DXL

    ‏2013-10-24T06:56:43Z  
    There is an example in the DOORS DXL help ("Automation client support") which will do the opposite, i.e. write a value to an excel sheet, but that is pretty much the same. Any luck trying the example?

    Regards, Mathias

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

    hi  Mathias Mamsch

    new to this forum i need to import values from excel to doors attributes. I can get value from single cell to attribute in doors but problem i am facing is when the cells are MERGED in excel my script stops. if u have any idea on this please let me know. below is where i am getting the cell value from excel code section

    string getCellValue(OleAutoObj objExcelSheet, int row, int col)
    {
     string sValue = null
     OleAutoObj objCell = null
     OleAutoArgs autoArgs = create
     OleAutoObj objShapes

     put(autoArgs,row)                                 // put the row number of excel sheet to args
     put(autoArgs,col)          // put the col number of excel sheet to args
     if(objExcelSheet == null)                         // check for excel sheet is null " no contents in the sheet "
     {
      warningBox("Excel sheet not contains any data it is blank ")
     }
     oleGet(objExcelSheet,"Cells",autoArgs,objCell)        // if data is present then
     
     if (!null objCell)
     {
      // Get the value
      oleGet(objCell,"Value",sValue)                   // fetch the data from cell
      
           
     }
     delete autoArgs
     return sValue                                        // return the fetched data of the cell with specific row and column
    }

    Thanks in advance

  • llandale
    llandale
    3035 Posts

    Re: Opening a Excel Spreadsheet and accessing a CELL in DXL

    ‏2013-10-24T15:04:21Z  

    hi  Mathias Mamsch

    new to this forum i need to import values from excel to doors attributes. I can get value from single cell to attribute in doors but problem i am facing is when the cells are MERGED in excel my script stops. if u have any idea on this please let me know. below is where i am getting the cell value from excel code section

    string getCellValue(OleAutoObj objExcelSheet, int row, int col)
    {
     string sValue = null
     OleAutoObj objCell = null
     OleAutoArgs autoArgs = create
     OleAutoObj objShapes

     put(autoArgs,row)                                 // put the row number of excel sheet to args
     put(autoArgs,col)          // put the col number of excel sheet to args
     if(objExcelSheet == null)                         // check for excel sheet is null " no contents in the sheet "
     {
      warningBox("Excel sheet not contains any data it is blank ")
     }
     oleGet(objExcelSheet,"Cells",autoArgs,objCell)        // if data is present then
     
     if (!null objCell)
     {
      // Get the value
      oleGet(objCell,"Value",sValue)                   // fetch the data from cell
      
           
     }
     delete autoArgs
     return sValue                                        // return the fetched data of the cell with specific row and column
    }

    Thanks in advance

    Did some investigating.  Open this file for an example (or whereever it is on your computer)

    • c:\Program files\IBM\Rational\DOORS\9.3\lib\dxl\standard\export\office\excel.dxl

    Function "setCell()" line #172 suggests if you convert cell "2,4" to "B4" you can use the "Range" property to get your "Cells" range.  It uses function "intToCol()" starting line #140 to convert "2" to "B".

    In any event, if you have to arguments to a property (such as "Cells") you need to give them names.  I could not find the names, but perhaps they are "rowNumber" and "colNumber".

    • put(autoArgs, "rowNumber", row)
    • put(autoArgs, "calNumber", col)

    -Louie

  • pommCannelle
    pommCannelle
    92 Posts

    Re: Opening a Excel Spreadsheet and accessing a CELL in DXL

    ‏2013-10-25T12:36:24Z  

    ( Hi !

    For your next try ... There is a very complete example to deal with the excel automation here : http://www.galactic-solutions.com/downloadsv/GalacticDownloadExcel.htm

    ... I hope there is no offense to provide an external link ! )

     

  • aeggarcia
    aeggarcia
    1 Post

    Re: Opening a Excel Spreadsheet and accessing a CELL in DXL

    ‏2016-06-16T15:25:42Z  
    Pekka,
    Thanks. It works with integer type variable. I didn't expect excel variable types to match to DXL variable types.

    Thanks again

    Hi

    I have been trying migrating the string variable into a int variable as described.

    In the end I get something display but is always "0". I am using DOORS 9.6 and office 10.

    I tried to format the cells to txt, to General or numeric. To be honest it would help a lot to make it work with text.

    Wondering if somebody can help.

    Antonio

  • O.Wilkop
    O.Wilkop
    44 Posts

    Re: Opening a Excel Spreadsheet and accessing a CELL in DXL

    ‏2016-06-17T10:51:06Z  

    Hi

    I have been trying migrating the string variable into a int variable as described.

    In the end I get something display but is always "0". I am using DOORS 9.6 and office 10.

    I tried to format the cells to txt, to General or numeric. To be honest it would help a lot to make it work with text.

    Wondering if somebody can help.

    Antonio

    Try using 

    oleGet(objCell, "Text", sValue)
    

    instead of 

    oleGet(objCell, "Value", sValue)
    

     

    That works for me with both, number or text values in the excel cell.