I have wrote a script for reading a cell value of Excel but it doesn't. It tells me that it's the "sValue" is blank. I don't know why.
# include <utils/ole.inc>
#include <utils/doctools/itfutil.inc>
checkPlatform "Microsoft Excel"
// Open a file in Excel
OleAutoObj openExcelFile(string sFileName, bool bVisible)
{
OleAutoObj oleWorkbooks = null
OleAutoObj oleExcel = null
OleAutoArgs autoArgs = create
oleExcel = oleGetAutoObject("C:/Users/86913/Desktop/Tempory/test1.xls")
if (null oleExcel)
{
oleExcel = oleCreateAutoObject("Excel.Application")
if(null oleExcel)
{
errorBox("Unable to open excel application")
}
}
// olePut (oleExcel, "Visible", bVisible)
// Get workbooks and open file
OleAutoObj oleWorkbook
clear autoArgs
put(autoArgs, sFileName)
oleGet(oleExcel,"Workbooks", oleWorkbooks)
oleMethod(oleWorkbooks,"Open",autoArgs,oleWorkbook)
delete autoArgs
return oleWorkbook
}
OleAutoObj excelGetWorksheet(OleAutoObj oleExcel, int iSheetNumber)
{
OleAutoObj objExcelSheet = null
string sSheetNumber = "Sheet" iSheetNumber ""
print sSheetNumber
OleAutoArgs autoArgs = create
put(autoArgs ,sSheetNumber)
oleGet(oleExcel,"WorkSheets", autoArgs , objExcelSheet)
if (null objExcelSheet){
ack "Unable to get workbooks collection"
}
oleMethod(objExcelSheet, "Activate")
delete autoArgs
return objExcelSheet
}
string intToCol(int i) {
int p
if (i < 26) {
char a = 'A'
p = intOf a
p = p + (i-1)
a = charOf p
return a ""
} else {
ack "Too many columns"
halt
}
}
string getCellValue(OleAutoObj objExcelSheet, int iRow, int iCol)
{
string sValue
OleAutoObj objCell = null
OleAutoArgs autoArgs = create
put(autoArgs, (intToCol iCol) iRow "")
//-----------------------------------------------------------
// put(autoArgs,iRow)
// put(autoArgs,iCol)
// put(autoArgs, "rowNumber", iRow)
// put(autoArgs, "calNumber", iCol)
// oleGet(objExcelSheet,"Cells",autoArgs,objCell)
//-----------------------------------------------------------
clear(autoArgs)
put(autoArgs, (intToCol iCol) iRow "")
OleAutoObj objRange = null
oleGet( objExcelSheet, "Range", autoArgs, objRange )
oleMethod( objRange, "Select" )
checkRes(oleGet(objExcelSheet, cMethodRange,autoArgs, objCell))
if(objExcelSheet == null) // check for excel sheet is null " no contents in the sheet "
{
warningBox("Excel sheet not contains any data it is blank ")
}
// checkRes(oleGet(objExcelSheet, "Cells",autoArgs,objCell))
//checkRes(oleGet(objExcelSheet,"Cells",autoArgs,objCell))//error here
if (!null objCell)
{
// Get the value
checkRes(oleGet(objCell,"Value",sValue))
if (sValue ==null)
{
warningBox("The sValue is blank ")
}
}
else
warningBox("The cell is null! ")
delete autoArgs
return sValue
}
//close excel
void excelQuit(OleAutoObj oleExcel)
{
oleMethod(oleExcel, "Quit")
oleCloseAutoObject(oleExcel)
}
// Main
string path="C:/Users/86913/Desktop/Tempory/test1.xls"
OleAutoObj oleExcel = openExcelFile(path,true)
OleAutoObj objExcelSheet = excelGetWorksheet(oleExcel,1)
string s = null
s = getCellValue(objExcelSheet,3, 3)
print s "\n"
print s "Row 1, Column 1: " s "\n"
//s = getCellValue(objExcelSheet, 3, 3)
print s "Row 2, Column 3: " s "\n"
excelQuit(oleExcel)
#EngineeringRequirementsManagement#Sustainability#SupportMigration#Support#DOORS