Reading from excel was working before updating to Testcomplete 15.40.421

[hina]

Hi,

The code below was working before upgrading to TestComplete 15.40.421.

function test()
{

var ExcelFileName = "C:\\ELink\\Queries\\Dataset_Comparison_Query_Result1.xlsx";

VerifyQueryResults(ExcelFileName);
}

function VerifyQueryResults(ExcelFileName)
{
// Get the sheet of the Excel file
var excelFile = Excel.Open(ExcelFileName);
var excelSheet = excelFile.SheetByTitle("Sheet1");

// Read data from the Excel file
var Difference = excelSheet.Cell(1, 2).Value;

if(Difference == 0)
Log["Event"]("Expected and Actual Datasets are the same");
else
Log["Error"]("Expected and Actual Datasets are not the same");
}

Now the "Difference" is always coming up as "undefined", when I am trying to read from the excel.

Below is the screenshot of the excel:

The "Difference" should be 0. Please help.

Thanks,

Hina

[npaisley]

HI @hina!

May you provide a screenshot of the error you are receiving in your test logs? 

I am on the same version of TC as you and unfortunately after bringing in your code and creating the same spreadsheet, I am seeing 'Expected and Actual Datasets are the same' when running your script. Could your spreadsheet have been updated with you knowing possibly? 

[hina]

Hi Nick,

Thank you for the quick reply. You are right when I create the spreadsheet manually, TestComplete does ready the value 0. But I realized, the spreadsheet I am using is generated by the application I am testing, for some reason when I run the same script, TestComplete doesn't read the value 0 and Difference is set to undefined. But if I resave the same spreadsheet as it is, TestComplete does read the value 0. This is so weird, I am not really sure what to do. Any help would be appreciated.

Thanks,

Hina

[npaisley]

Hey @hina,

Interesting, maybe it's some type of odd formatting that is being applied. Would it be possible for you to attach a copy of the spreadsheet that is generated by your application? 

[Marsha_R]

We had many problems with Excel and TestComplete that turned out to be Excel issues. It was similar to what you are seeing, sometimes you could read the cell and sometimes not. There was a post on the forum recently about not being able to read blank cells and that was a problem too. If we wrote and rewrote to the same sheet, eventually something would get corrupted in the spreadsheet that we couldn't fix and then we had to create a whole new spreadsheet with the exact same data. Our workarounds ended up wasting so much time that we abandoned Excel as a data storage and began using .csv files instead. 

Sometimes we all forget that Excel wasn't designed to do all the things that we try to force it to do. .csv files are just text and won't have hidden, embedded code that can interfere with your data.

[npaisley]

Hi,

I agree with Marsha here. I have seen Excel apply specific formatting to cells, even though they may just be generic strings of data, that does not import well into other apps. But when you resave, or copy the data, it removes that specific formatting and allows apps to ingest the data. I do think if you are able to move to .CSV it would eliminate the potential for odd formatting applied by Excel. 

If I had a nickel for every time it tried to applied scientific formula mapping to my standard integers I would be living in Fiji by now. 😉 

[hina]

Hi Nick,

I have attached the file generated by the application, which Testcomplete can't read.

Also I tried one more thing, I modified the code to add excelFile["Save"](); (was hoping it might fix the issue)

Below is the code:

function VerifyQueryResults(ExcelFileName)
{
// Get the sheet of the Excel file
var excelFile = Excel.Open(ExcelFileName);
var excelSheet = excelFile.SheetByTitle("Sheet1");

Delay(1000);
excelFile["Save"]();

// Read data from the Excel file
var Difference = excelSheet.Cell(1, 2).Value;

if(Difference == 0)
Log["Event"]("Expected and Actual Datasets are the same");
else
Log["Error"]("Expected and Actual Datasets are not the same");
}

But when I run the code above, Testcomplete throws an error below:

Next time when I run the same code, TestComplete throws an error below:

So basically excelFile["Save"](); does not work.

Thanks,

Hina

[npaisley]

Hi @hina,

I appreciate you sharing your file. After some testing I do see something similar. There is something odd about the .xlsx file that is incompatible with TC. 

I was able to duplicate your errors out of the box. Though saving it manually TC was able to use your provided code above without issue. The 'Save' method is not working because there is some incompatibility between the original version of your .xlsx and TC. I have compared yours to a saved copy and unfortunately I don't really see any differences. 

At this point I don't really have an immediate solution beyond what @Marsha_R mentioned. It might be worth it here to move to another file format to handle this data, as Excel and .xlsx files in particular doesn't like to play nice with other applications quite often.