Write data in excel from DOORS

Question

I want to open a new excel sheet and write a cell value B1 as "Apples" using Dxl.

I am stuck at the last step of the problem.

1. Open a new excel workbook and get a handle on it. [Works]
2. Open a new worksheet and get a handle on it. [Works]
3. Add a new worksheet and get a handle on it. [Works]
4. Write the cell B1 in the worksheet "Apples". [Does not work]

void oleCheck (string s) { 
   if (!null s) {
      print "An Error occurred: " s
      halt;
   }
}

OleAutoObj objExcel = oleCreateAutoObject("Excel.Application")

// Make it visible
olePut(objExcel, "Visible", true)

OleAutoArgs args = create(); 

// get Application.Workbooks
OleAutoObj objWorkbookCollection= null; 
oleCheck oleGet ( objExcel, "Workbooks", objWorkbookCollection)

// Add a workbook --> Application.Workbooks.add ()
oleCheck oleMethod(objWorkbookCollection, "Add")

// Get a handle to the newly added workbook --> Application.ActiveWorkbook
OleAutoObj objWorkbook = null
oleCheck oleGet (objExcel, "ActiveWorkbook", objWorkbook)

// Get a handle to the Sheets collection --> ActiveWorkbook.Sheets
OleAutoObj objSheetsCollection = null
oleCheck oleGet (objExcel, "Sheets", objSheetsCollection )

// Add a sheet ... ActiveWorkbook.Sheets.Add
oleCheck oleMethod(objSheetsCollection , "Add") 

// Get a handle to the sheet ...
OleAutoObj objSheet = null
oleCheck oleGet (objWorkbook, "ActiveSheet", objSheet)

// Get the Sheets Name
string sSheetName = null; 
oleCheck oleGet (objSheet, "name", sSheetName)


OleAutoObj objcell = sheet.get("Apples",1,2) <--- Does not work

print "New Sheet with name " sSheetName " added!"

Answer 1

Here is an excerpt of a library we use to communicate with Excel. You need to use the "A1" notation with OLE. Note that I did not test this with recent versions of Excel or DOORS. Kudos to http://galactic-solutions.com/

/* 
 * Copied from Enhanced Export to Excel written by Galactic Solutions.
 * Converts an integer value for a column to the proper string value .
 * ex. 1 converts to A
 */
string intToCol(int i) {
    string s = ""
    if ((i>=1) && (i<=256))   {                              // Works for column A -> ZZ
        int d1 = (i-1) / 26;
        int d2 = (i-1) % 26;
        if (d1 > 0) {
            s = charOf( d1-1 + intOf('A')) "";
        }
        s = s charOf( d2 + intOf('A')) "";
    }
    else {
        if (i>256) {
            errorBox("Too many columns");
        }
        else {
            errorBox("Invalid column reference :" i "");
        }
        halt();
    }
    return(s);
}

/*
 * Parses and returns the column from a cell location 
 * (e.g. A51, where 'A' = column and '51' = row) 
 */
 
int getColFromLoc(string loc) {
    if (length(loc) > 1) {
        if (isalpha(loc[0]) && isalpha(loc[1])) {
            return(((intOf(loc[0])-(intOf('A')-1))*26)+(intOf(loc[1])-(intOf('A')-1)));
        } else if(isalpha(loc[0])) {
            return(intOf(loc[0])-(intOf('A')-1));
        }
    } 
    return(-1);
}

/*
 * Parses and returns the row from a cell location 
 * (e.g. A51, where 'A' = column and '51' = row)
 */
int getRowFromLoc(string loc) {
    if (length(loc) > 1) {
        if (isalpha(loc[0]) && isalpha(loc[1])) {
            return(intOf(loc[2:length(loc)]));
        } else if(isalpha(loc[0])) {
            return(intOf(loc[1:length(loc)]));
        }
    }
    return(-1);
}
/*
 * Gets a handle on a specific cell based on the row and column number.
 * Remember, the minimum row and column number in Excel is 1, not 0.  The
 * maximum column number is 256.  Office 97 and 2000 have a limit of 65536
 * rows.  Office XP and 2003 can go beyond this.  However for DOORS this 
 * should not be an issue.
 */
bool getCell(int rowNum, int col) {
   closeIfNonNull(objCell);
   clear(args);
   put(args, intToCol(col) rowNum "");

   checkResult(oleGet(objSheet, cMethodRange, args, objCell));
   
   if(null(objCell)) {
      return(false);
   }
   
   checkResult(oleMethod(objCell, cMethodSelect));
   
   return(true);
}
/*
 * Gets a handle on a specific cell based on a cell location such as A9.
 * Remember, the minimum row and column number in Excel is 1, not 0.  The
 * maximum column number is 256.  Office 97 and 2000 have a limit of 65536
 * rows.  Office XP and 2003 can go beyond this.  However for DOORS this 
 * should not be an issue.
 */
bool getCell(string loc) {
   closeIfNonNull(objCell);      // close any open handle to a cell
   clear(args);                   // clear any current arguments
   put(args, loc "");             // loc is in the format of A9 or B37
   
   checkResult(oleGet(objSheet, cMethodRange, args, objCell));      // get a handle to the cell
   
   if(null(objCell)) {
      errorBox("Unable to get cell object");
      return(false);
   }
   
   checkResult(oleMethod(objCell, cMethodSelect));
   
   return(true);
}

/*
 * This function gets the Value Property of the cell at the passed in row and
 * column numbers.  It will get a handle to the cell before trying to get the
 * value.
 */ 
string getCellValue(int rowNum, int col) {
   string s = "";
   getCell(rowNum, col);   // gets a handle on the desired cell.
   
   // if the handle of a cell was returned, get the value
   if(!null(objCell)) {
      if(!checkResult(oleGet(objCell, cPropertyValue, s))) {
         errorBox("Failed to get cell " intToCol(col) "" rowNum " value");
         return("error");
      }
   }
   else {   // otherwise returns error as the result
      return("error");
   }
   
   return(s);
}
/*
 * This function sets the value of the cell at the passed in row and
 * column numbers.  It will get a handle to the cell before trying 
 * to set the value.
 */ 
bool setCellValue(int rowNum, int col, string s) {
   getCell(rowNum, col);         // get the cell handle
   
   // if the cell handle isn't null, put the value in the cell
   if(!null(objCell)) {
      if(!checkResult(olePut(objCell, cPropertyValue, s))) {
         errorBox("Failed to set cell " intToCol(col) "" rowNum " value");
         return(false);
      }
      
      return(true);
   }
   
   return(false);            // otherwise false;
}

/*
 * This function sets the value of the cell at the passed in row and
 * column numbers.  It will get a handle to the cell before trying 
 * to set the value.
 */ 
bool setCellValue(string loc, string s) {
   getCell(loc);            // get the cell handle
   
   // if the cell handle isn't null, put the value in the cell
   if(!null objCell) {
      if(!checkResult(olePut(objCell, cPropertyValue, s))) {
         errorBox("Failed to set cell " loc " value");
         return(false);
      }
      
      return(true);
   }
   
   return(false);            // otherwise return(false);
}

bool setCellFormula(int rowNum, int col, string function) {
   getCell(rowNum, col);
   
   if(!null(objCell)) {
      if(!checkResult(olePut(objCell, cPropertyFormula, function))) {
         errorBox("Failed to set cell " intToCol(col) "" rowNum " function");
         return(false);
      }
      
      return(true);
   }
   
   errorBox("Null Cell reference in call to setCellFunction");
   return(false);
}

bool setCellFormula(string loc, string function) {
   getCell(loc);
   
   if(!null(objCell)) {
      if(!checkResult(olePut(objCell, cPropertyFormula, function))) {
         errorBox("Failed to set cell " loc " formula");
         return(false);
      }
      
      return(true);
   }
   
   errorBox("Null Cell reference in call to setCellFunction");
   return(false);
}

Edit: You are right, my bad. I focused more on the way how to get a handle to the cell than to setting the value. I added some functions to set cell values or formulas, either by using the row,col notation or by using the A1 notation. The complete file is available at Galactic Solutions in the Download Section.