Forum Discussion

anik_cadieux's avatar
anik_cadieux
Contributor
11 years ago

Is it possible to write data to an Excel file without having Office installed ?

We developped testing scripts using TestComplete on our computer which has Office installed. One of the function writes data to an Excel file. We want the script to run with TestExecute on 10 different machines, depending of the configuration we want to test. 



We know Excel must be installed to run COM objects, as explained in this post: http://smartbear.com/forums/f81/fp24/t89448/problem-in-working-with-excel-via-com-server/ .



Obviously, we don't want to buy an Office licence for each of the virtual machines.



Is there anyone that has found a good solution for this ?

 



  • HKosova's avatar
    HKosova
    Icon for Alumni rankAlumni
    11 years ago
    Hi Anik,



    I can suggest a few options:



    1. Use CSV files (text files with comma-separated values) instead of Excel files. Excel can open CSV files, too.



    2. Use some .NET or Java library that can read/write Excel files. For example:

    .NET - NPOI

    Java - POI, JExcelApi, OpenXLS

    You can connect to external libraries from TestComplete via CLR Bridge and Java Bridge.



    3. Install ACE data provider and work with Excel files as with databases. Here's an example:



    // JScript

    function Test()

    {

      // Before running the script, create a Excel file with some number in cell A1,

      // and specify the file name here:

      var strFileName = "C:\\Work\\Book1.xlsx";



      // Open a database connection to the Excel file

      var conn = Sys.OleObject("ADODB.Connection");

      conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Mode=ReadWrite;"

        + "Data Source='" + strFileName

        + "';Extended Properties='Excel 8.0;HDR=NO';";

      conn.Open();



      // Change the value in the A1 cell

      var strSQL = "UPDATE [Sheet1$A1:A1] SET F1 = 42";

      conn.Execute(strSQL);



      // Append a row at the end

      strSQL = "INSERT INTO [Sheet1$] Values (15);";

      conn.Execute(strSQL);



      conn.Close();

    }

    You can read more about this approach here:

    Working with MS Excel(xls / xlsx) Using MDAC and Oledb

    How to read or write excel file using ACE OLEDB data provider?