Read/write values from Excel data source file in CodedUI Test

Reading DataSource and then writing back with updated data will NOT work, because every iteration the test [TestMethod] is executed with the original Data Source. Updated data from last iteration will be overwritten in each iteration. You end up with only one row of updated data from the very last iteration.

Solution: (No real solution unless Microsoft makes changes, but rather a workaround)

Manually make a copy of the Data Source file before running the test, then while still utilizing Data Source,


Scenario SecondCol Result
89       23        Value
11       800       Value
888      111       Value
  1. load data from the copied file
  2. update the Data
  3. overwrite the copied file

The following is an example. data.xlsx is the Excel Data Source file, newdata.xlsx is the copy you make before running tests.

[DataSource("System.Data.Odbc", "Dsn=Excel Files;Driver={Microsoft Excel Driver (*.xls)};dbq=|DataDirectory|\\data.xlsx;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=false", "Sheet1$", DataAccessMethod.Sequential)] // This is working Excel.xlsx connection!!! Excel (ODBC, Dsn)

public void ExcelReadWrite()
    //Open copied Excel file and create Excel object
    string projectName = System.Reflection.Assembly.GetExecutingAssembly().GetName().Name;
    string dir = Environment.CurrentDirectory + @"\..\..\..\";
    string targetFilename = "newdata.xlsx";
    string targetFile = dir + projectName + @"\" + targetFilename;

    object missing = Type.Missing;
    object misValue = System.Reflection.Missing.Value;
    Excel.Application excel = new Excel.Application();
    Excel.Workbook wb = excel.Workbooks.Open(targetFile, true, false);
    Excel.Worksheet ws = wb.Sheets[1];
    Excel.Range wr = ws.UsedRange;
    int rowCount = wr.Rows.Count;
    int colCount = wr.Columns.Count;

    //make some example data update for current iteration
    int row = TestContext.DataRow.Table.Rows.IndexOf(TestContext.DataRow); //current iteration data row. zero base
    int generatedData = Convert.ToInt32(TestContext.DataRow[0]) + Convert.ToInt32(TestContext.DataRow[1]); //read from DataSource
    ws.Cells[row + 2, 3] = generatedData; //Worksheet, not zero base, and header row counts
    // ======================================================
    // Overwrite the copied file without warning
excel.DisplayAlerts = false;
    wb.SaveAs(targetFile, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    wb.Close(missing, missing, missing);

Don’t try to copy the Data Source file within [TestInitialize] as that will be executed before every iteration as well.

The key point is that you actually have to work with 2 separate files, regardless if they are identical at the beginning or not. One is for Data Source, which is read only and drives the iterations; the other is the one you read/write/update. For .csv file, yes it can be in append mode, but for Excel, you have to overwrite the entire file each iteration.


One thought on “Read/write values from Excel data source file in CodedUI Test

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s