Sergey Talalaev (SQAdotBY) has contributed this wonderful article on using a generic class for all your Excel data-operations needs. Thanks SQAdotBY, for you hard work, and willingness to share it with the QTP community! This article is copyrighted to Sergey.
An English translation is available here.
Универсальный класс для работы с данными.
Содержание:
1. Введение.
2. Excel data manipulation.
2.1. Основные задачи.
2.2. Предварительные шаги.
2.3. Со стороны функциональной библиотеки.
2.4. Со стороны тестового скрипта.
3. Выводы.
1. Введение
Влияние мирового кризиса к сожалению сказалось и на моей персоне и мне потребовалось срочно и по возможности глубоко изучить новый для себя фреймворк – Quick Test Professional.
У меня уже был опыт работы с продуктами HP Mercury, но он относился к предыдущей линейке, я имею в виду WinRunner. Естественно я ожидал некоторого сходства в процессах скриптостроения и организации самого фреймворка. Поэтому по свежим следам постараюсь изложить замеченные мною интересные моменты и привести свои примеры реализации некоторых функций.
2. Работа с Excel-данными
Еще работая с WinRunner, я убедился, что встроенная реализация Excel хранилища не настолько гибка, как мне бы хотелось. Поэтому, как всегда, я приготовился к миграции своих процедур для работы с Excel. И был немного удивлен отсутствию встроенных средств работы с БД. Но это задача вполне по силам, когда за плечами вся мощь VB.
2.1. Основные задачи
Итак, для начала определимся, зачем нам это нужно.
Я всегда выступал за то, чтобы подготовка тестовых данные для автоматических тестов была максимально упрощена с одной стороны, и, по возможности, исключала возможность ошибок при вводе с другой.
Эти трудносовместимые вещи отлично реализуются посредством встроенной в Excel валидации входных данных и также прекрасно уничтожаются попыткой редактирования Excel таблицы напрямую из QTP.
Более того уничтожается также любое кастомное форматирование, без которого нормально читаемая таблица превращается в клетчатый текст.
Было до вмешательства из QTP:
Стало после редактирования из QTP:
Чтобы избежать подобных казусов я давно применяю прямую вычитку из Excel файлов в массивы, используя для этого стандартные ODBC источники. Данная техника успешно прижилась уже на следующих тестовых фреймворках: Rational Robot, IBM Rational Functional Tester, WinRunner и надеюсь QTP
Помимо решения вышеперечисленных проблем мы избегаем также серьезного, на мой взгляд, ограничения по использованию одной таблицы для одного Excel sheet.
2.2. Предварительные шаги
В Excel документах имеется функциональность которая позволяет выделять значимые для пользователя подмножества ячееек в специальные структуры. Такие структуры называемые “именованными диапазонами” обеспечивают возможность обращаться к ним к ним через логические имена.
Кроме того (что гораздо более значимо для нас) такие диапазоны становятся видны как стандартные ODBC таблицы из внешних приложений.
Итак, для оформления требуемой совокупности ячеек в качестве “именованного диапазона” необходимо выполнить следющую последовательность действий:
- выделить все ячейки, планируемые для оформления в именованный диапазон
- создать именованный диапазон через “Define name” диалог (Formulas > Define Name)
или напрямую введя имя диапазона в Navigation Bar.
Для проверки корректности вновь созданного именованного диапазона – выделите все ячейки диапазона и проверьте значение в Navigation Bar. Он должен содержать логическое имя вместо A1 нотации.
Важно отметить одно требование обязательно при использовании именованных диапазонов в качестве источника данных:
- первая строка нашего диапазона должна содержать имена столбцов, а не данные
2.3. Со стороны функциональной библиотеки
После всех подготовительных шагов осталось совсем немного поработать руками, а точнее пописать код.
Раз уж QTP 9.5 предоставил нам замечательную возможность работать c “почти” объектами – грех было бы ей не воспользоваться. Поэтому весь наш функционал мы гордо завернем в класс с благозвучным названием TestData.
Стоит напомнить, что QTP не видит напрямую классы, объявленные во внешних библиотеках, поэтому для каждого класса должна присутствовать функция создания экземпляра класса, в данном случае – CreateTestData
Кроме того мы должны иметь возможность инициировать наш класс не только через загрузку из Excel источника но и напрямую из кода. Именно для этих целей появились два метода: SetData и GetData
<span style="color: #0000ff;">Public</span> <span style="color: #0000ff;">Function</span> CreateTestData ()
<span style="color: #0000ff;">Set</span> CreateTestData = <span style="color: #0000ff;">new</span> TestData
<span style="color: #0000ff;">End</span> <span style="color: #0000ff;">Function</span>
<span style="color: #0000ff;">Class</span> TestData
<span style="color: #0000ff;">Private</span> mTestTable()
<span style="color: #0000ff;">Private</span> <span style="color: #0000ff;">Sub</span> Class_Initialize()
<span style="color: #0000ff;">End</span> <span style="color: #0000ff;">Sub</span>
<span style="color: #0000ff;">Private</span> <span style="color: #0000ff;">Sub</span> Class_Terminate()
<span style="color: #0000ff;">Erase</span> mTestTable
<span style="color: #0000ff;">End</span> <span style="color: #0000ff;">Sub</span>
<span style="color: #008000;">\' @Documentation Initiates TestData object with external data</span>
<span style="color: #008000;">\'-----------------------------------------------------------</span>
<span style="color: #0000ff;">Public</span> <span style="color: #0000ff;">Sub</span> SetData (DataArr())
<span style="color: #0000ff;">Dim</span> i,j
<span style="color: #0000ff;">ReDim</span> mTestTable(UBound(DataArr, 1), UBound(DataArr, 2))
<span style="color: #0000ff;">For</span> i=0 <span style="color: #0000ff;">to</span> UBound(DataArr, 1)
<span style="color: #0000ff;">For</span> j=0 <span style="color: #0000ff;">to</span> UBound(DataArr, 2)
mTestTable(i,j) = DataArr(i,j)
<span style="color: #0000ff;">Next</span>
<span style="color: #0000ff;">Next</span>
<span style="color: #0000ff;">End</span> <span style="color: #0000ff;">Sub</span>
<span style="color: #008000;">\' @Documentation Extracts data from TestData object</span>
<span style="color: #008000;">\'---------------------------------------------------</span>
<span style="color: #0000ff;">Public</span> <span style="color: #0000ff;">Sub</span> GetData(DataArr())
<span style="color: #0000ff;">Dim</span> i,j
<span style="color: #0000ff;">ReDim</span> DataArr(UBound(mTestTable, 1), UBound(mTestTable, 2))
<span style="color: #0000ff;">For</span> i=0 <span style="color: #0000ff;">to</span> UBound(mTestTable, 1)
<span style="color: #0000ff;">For</span> j=0 <span style="color: #0000ff;">to</span> UBound(mTestTable, 2)
DataArr(i,j) = mTestTable(i,j)
<span style="color: #0000ff;">Next</span>
<span style="color: #0000ff;">Next</span>
<span style="color: #0000ff;">End</span> Sub
<span style="color: #0000ff;">End</span> <span style="color: #0000ff;">Class</span>
Итак, мы вплотную подобрались к центральной части нашего функционала – вычитке данных из Excel источника.
За данную часть функционала отвечают два взаимосвязанных метода: GetArrayFromStore и LoadFromStore
Первый позволяет нам выгрузить вычитанные данные во внешний массив, минуя наш класс, а второй наоборот – инициирует наш класс вычитанными данными.
<span style="color: #008000;">\' @Documentation Extracts test data from the Excel store</span>
<span style="color: #008000;">\'------------------------------------------------------</span>
<span style="color: #0000ff;">Public</span> <span style="color: #0000ff;">Sub</span> GetArrayFromStore(Arr(), TableName, StoreName)
<span style="color: #0000ff;">Dim</span> Connection
<span style="color: #0000ff;">Dim</span> i, j, fieldcount, rowsfetched
<span style="color: #0000ff;">Dim</span> ArrTemp()
<span style="color: #0000ff;">Dim</span> retcode
<span style="color: #0000ff;">Dim</span> RepPath, BufStr
<span style="color: #0000ff;">Set</span> Connection = CreateObject(<span style="color: #006080;">"ADODB.Connection"</span>)
Connection.ConnectionString = <span style="color: #006080;">"DBQ="</span> + StoreName + _
<span style="color: #006080;">";Driver={Microsoft Excel Driver (*.xls)}"</span> + _
<span style="color: #006080;">";DriverId=790;FIL=excel 8.0;MaxBufferSize=2048"</span> + _
<span style="color: #006080;">";MaxScanRows=8;PageTimeout=5;ReadOnly=1"</span> + _
<span style="color: #006080;">";SafeTransactions=0;Threads=3;UserCommitSync=Yes"</span>
Connection.Open
<span style="color: #0000ff;">Set</span> ConnRs = CreateObject(<span style="color: #006080;">"ADODB.Recordset"</span>)
ConnRs.CursorType = 3
<span style="color: #0000ff;">Call</span> ConnRs.Open(<span style="color: #006080;">"select * from "</span> + TableName, Connection)
fieldcount = ConnRs.Fields.Count
<span style="color: #0000ff;">Redim</span> ArrTemp(fieldcount - 1, 0)
<span style="color: #008000;">\'column names adding</span>
<span style="color: #0000ff;">for</span> j=0 <span style="color: #0000ff;">to</span> fieldcount - 1
ArrTemp(j,0) = ConnRs.Fields(j).Name
<span style="color: #0000ff;">next</span>
rowsfetched = 0
ConnRs.MoveFirst()
<span style="color: #0000ff;">While</span> <span style="color: #0000ff;">not</span> ConnRs.Eof
rowsfetched = rowsfetched + 1
<span style="color: #0000ff;">Redim</span> <span style="color: #0000ff;">Preserve</span> ArrTemp(fieldcount - 1, rowsfetched)
<span style="color: #0000ff;">for</span> j=0 <span style="color: #0000ff;">to</span> fieldcount - 1
ArrTemp(j, rowsfetched + i) = ConnRs(j).value
<span style="color: #0000ff;">next</span>
ConnRs.MoveNext()
<span style="color: #0000ff;">Wend</span>
Connection.Close
<span style="color: #008000;">\'matrix transposition</span>
<span style="color: #0000ff;">Redim</span> Arr(UBound(ArrTemp,2), UBound(ArrTemp,1))
<span style="color: #0000ff;">for</span> i=LBound(ArrTemp,1) <span style="color: #0000ff;">to</span> UBound(ArrTemp,1)
<span style="color: #0000ff;">for</span> j=LBound(ArrTemp,2) <span style="color: #0000ff;">to</span> UBound(ArrTemp,2)
Arr(j,i) = ArrTemp(i,j)
<span style="color: #0000ff;">next</span>
<span style="color: #0000ff;">next</span>
<span style="color: #0000ff;">End</span> <span style="color: #0000ff;">Sub</span>
<span style="color: #008000;">\' @Documentation Loads test data from Excel store to the TestData object</span>
<span style="color: #008000;">\'-----------------------------------------------------------------------</span>
<span style="color: #0000ff;">Public</span> <span style="color: #0000ff;">Sub</span> LoadFromStore (TableName, StoreName)
<span style="color: #0000ff;">call</span> GetArrayFromStore(mTestTable, TableName, StoreName)
<span style="color: #0000ff;">End</span> Sub
Как вы можете заметить, ничего сверхъественного в реализации данного функционала нет. Более того очевидно, что с небольшими модификациями такую же процедуру можно применять для вычитки данных из любого ODBC источника.
Нам осталось добавить несколько сервисных методов, чтобы наш класс стал действительно удобным в использовании: GetCellByIndex, GetCellByName, ColumnCount, RowCount
<span style="color: #008000;">\' @Documentation Gets cell value by row-column indexes</span>
<span style="color: #008000;">\'------------------------------------------------------</span>
<span style="color: #0000ff;">Public</span> <span style="color: #0000ff;">Function</span> GetCellByIndex (RowIndex, ColumnIndex)
GetCellByIndex = mTestTable(RowIndex+1, ColumnIndex)
<span style="color: #0000ff;">End</span> <span style="color: #0000ff;">Function</span>
<span style="color: #008000;">\' @Documentation Gets cell value by column name and row index</span>
<span style="color: #008000;">\'-------------------------------------------------------------</span>
<span style="color: #0000ff;">Public</span> <span style="color: #0000ff;">Function</span> GetCellByName (ColumnName, RowIndex)
<span style="color: #0000ff;">Dim</span> j
<span style="color: #0000ff;">for</span> j = 0 <span style="color: #0000ff;">to</span> UBound(mTestTable,2)
<span style="color: #0000ff;">If</span> (ColumnName = mTestTable(0,j)) <span style="color: #0000ff;">Then</span>
<span style="color: #0000ff;">Exit</span> <span style="color: #0000ff;">For</span>
<span style="color: #0000ff;">End</span> <span style="color: #0000ff;">if</span>
<span style="color: #0000ff;">next</span>
GetCellByName = mTestTable(RowIndex+1, j)
<span style="color: #0000ff;">End</span> <span style="color: #0000ff;">Function</span>
<span style="color: #008000;">\' @Documentation Returns number of columns into the test data table</span>
<span style="color: #008000;">\'------------------------------------------------------------------</span>
<span style="color: #0000ff;">Public</span> <span style="color: #0000ff;">Function</span> ColumnCount()
ColumnCount = UBound(mTestTable, 2)
<span style="color: #0000ff;">End</span> <span style="color: #0000ff;">Function</span>
<span style="color: #008000;">\' @Documentation Returns number of rows into the test data table</span>
<span style="color: #008000;">\'---------------------------------------------------------------</span>
<span style="color: #0000ff;">Public</span> <span style="color: #0000ff;">Function</span> RowCount()
RowCount = UBound(mTestTable, 1) - 1
<span style="color: #0000ff;">End</span> Function
2.4. Со стороны тестового скрипта
Как же будет выглядеть минимально необходимый набор операций для инициализации нашего класса и вычитки данных на стороне скрипта.
<span style="color: #0000ff;">Dim</span> TestArr() ‘Dynamic array
<span style="color: #0000ff;">Dim</span> TestD
<span style="color: #0000ff;">Set</span> TestD = CreateTestData()
Сall TestD.LoadFromStore(<span style="color: #006080;">"NamedRange"</span>, <span style="color: #006080;">"C:\ExcelStore.xls"</span>)
Сall TestD.GetArrayFromStore(TestArr, <span style="color: #006080;">"NamedRange"</span>, <span style="color: #006080;">"C:\ExcelStore.xls"</span>)
‘<span style="color: #0000ff;">Call</span> TestD.GetArrayFromURL(TestArr, <span style="color: #006080;">"OrgChart@"</span>+ <span style="color: #006080;">"C:\ExcelStore.xls"</span>)
<span style="color: #0000ff;">Call</span> TestD.SetData(TestArr)
<span style="color: #0000ff;">Erase</span> TestArr
<span style="color: #0000ff;">Call</span> TestD.GetData(TestArr)
Print TestArr(1,1)
Print TestD.GetCellByIndex(2,2)
Print TestD.GetCellByName(<span style="color: #006080;">"Name"</span>, 2)
Print TestD.RowCount
Print TestD.ColumnCount
Обращаю ваше внимание, что массив (если вы решите выгружать данные из TestData объекта) должен быть динамическим, а не статическим.
Для более глубокого понимания функционала предлагаю пройтись по нему в режиме отладки и внимательно проконтролировать весь процесс.
Вы также наверняка заметили одну закомментированную функцию, я имею в виду GetArrayFromURL.
Я использую данную функцию для удобной реализации вложенных данных. В этом случае на уровне Excel каждая строка должна содержать дополнительную колонку, в которой хранится URL связанного массива данных.
Предлагаю вам реализовать данную функциональность самостоятельно.
3. Выводы
Возможно, кого-то, во время прочтения статьи, не покидало ощущение бесполезности реализации дополнительной функциональности имея стандартные средства работы с Eхcel. Частично я уже объяснял причины необходимости этого выше (Основные задачи). Но кроме этого хотелось бы еще добавить в копилку знаний несколько замечаний.
В силу особенностей моей специализации (функциональное автоматизированное тестирование) мне не удается работать с каким-то одним фреймворком. Тем не менее, всегда очень хочется использовать предыдущие наработки и обустроить свою работу максимально комфортно. Как оказалось это совсем не сложно и позволяет в итоге работать в привычных комфортных условиях, максимально используя уже проверенные схемы.
Recent Comments