Please, find below the code, and the attachment in the next post has a screenshot for the results. If the date string is retrieved from excel ,str2date does not work EmpDate is empty. On the other hand, if the string is provided directly, str2date works properly and EmpDate2 has the correct value.
static void test(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row=1;
str sheetName,Empname;
date EmpDate,EmpDate2;
str sEmpDate,sEmpDate2;
;
#define.filename(@'C:\temp\EmpVacBalance.xlsx')
sheetName="Sheet1";
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(#filename);
}
catch (Exception::Error)
{
throw error("File not found");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromName(sheetName);
cells = worksheet.cells();
//Iterate through cells and get the values
do
{
try
{
//Incrementing the row line to next Row
row++;
Empname=cells.item(row, 2).value().bStr();
info(Empname);
sEmpDate=cells.item(row, 3).value().bStr();
EmpDate=str2Date(sEmpDate,123);
info(strFmt("%1",sEmpDate));
info(strFmt("%1",EmpDate));
sEmpDate2="04/04/1993";
EmpDate2=str2Date(sEmpDate2,123);
info(strFmt("%1",sEmpDate2));
info(strFmt("%1",EmpDate2));
}
catch
{
error("error in this row");
}
// Loads the next row into the variant type and validating that its is empty or not
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
// quits the application
application.quit();
}
