I want to use ".xlsx" format Excel file as Data Source in my hand coding Coded UI test. The best information I get is found in MSDN: Creating a Data-Driven Coded UI Test, which has useful information regarding of various data format as below, but it doesn't mention about the ".xlsx" format.
After some more googling and experiment, I finally managed to get it to work. In order to save time for me and someone who might face the same challenge again, I think it's beneficial to document the connection strings and some key points to pay attention to here as later reference.
The following are the data source strings found in the MSDN page:
Data Source Types and Attributes
- CSV
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.CSV", "|DataDirectory|\\data.csv", "data#csv", DataAccessMethod.Sequential), DeploymentItem("data.csv"), TestMethod]
- Excel
[DataSource("System.Data.Odbc", "Dsn=ExcelFiles;Driver={Microsoft Excel Driver (*.xls)};dbq=|DataDirectory|\\Data.xls;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=true", "Sheet1$", DataAccessMethod.Sequential), DeploymentItem("Sheet1.xls"), TestMethod]
- Test case in Team Foundation Server
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.TestCase", "http://vlm13261329:8080/tfs/DefaultCollection;Agile", "30", DataAccessMethod.Sequential), TestMethod]
- XML
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.XML", "|DataDirectory|\\data.xml", "Iterations", DataAccessMethod.Sequential), DeploymentItem("data.xml"), TestMethod]
- SQL Express
[DataSource("System.Data.SqlClient", "Data Source=.\\sqlexpress;Initial Catalog=tempdb;Integrated Security=True", "Data", DataAccessMethod.Sequential), TestMethod]
I know I can convert the ".xlsx" format into the old format ".xls", but I rather have more options than less.