Forum Discussion

hina's avatar
hina
Contributor
3 years ago

Reading from excel was working before updating to Testcomplete 15.40.421

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's avatar
    npaisley
    Staff
    3 years ago

    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. 

  • npaisley's avatar
    npaisley
    Staff
    3 years ago

    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's avatar
      hina
      Contributor
      3 years ago

      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

      • Marsha_R's avatar
        Marsha_R
        Moderator
        3 years ago

        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's avatar
    npaisley
    Staff
    3 years ago

    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. 😉