Из Excel в JSON. Универсальные методы для формирования тела запроса из Excel книги для API тестов (Java)

Недавно нужно было написать API автотесты — запросы Post с большим количеством параметров в теле, в том числе вложенные JSON объекты, массивы , массивы JSON объектов. Многие параметры не обязательные, а значит — большое количество наборов тестовых данных.

Дано: Датапровайдер — для многократного запуска тестов, тестовые данные в таблице Excel.

Что бы сформировать тело первого запроса пришлось создать несколько классов, через сеттеры присваивать значения переменным в классе и из базового класса формировать JSON, который и использовался как тело запроса. И вдруг оказалось, что все написанное никак не получится переиспользовать для других эндпоинтов. Тогда и решил написать метод, который будет превращать таблицу Excel в JSON объект без всяких там классов и правок в коде. Нужно только придерживаться некоторых правил при составлении таблицы.

Итак! Для получения данных из таблицы использовал, как обычно, Fillo. Название столбца будет ключом, значения в столбце, собственно, значениями ключа в запросе. Строка таблицы — один набор тестовых данных.

Должно быть как-то так на входе:

На выходе:

[{"shop_name": "shop#1", "сity": "Charkiv"}, {"shop_name": "shop#2", "сity": "Kyiv"}, {"shop_name": "shop#3", "сity": "Dnipro"}]

Для начала, метод для получения ВСЕХ данных из Excel листа в виде массива мапов. Раньше использовал для похожих задач для вебтестов. Метод принимает две строки: путь к Excel файлу и название листа. Это единственные константы, которые вам придется захардкодить, все остальное управление формированием тела запроса выполняется из Excel файла. Я создаю для этого класс констант и там, если необходимо, меняю значение, которое потом используется во всех тестах.

    public List fromExcelToListOfMaps (String docPath, String sheet)  {         PATH_TO_EXCEL_DOC = docPath;         Fillo fillo = new Fillo();         Connection connection;         List<Map<String, String>> listOfMaps = new ArrayList<>();         try {            /* getting all the data from excel file */             connection = fillo.getConnection(PATH_TO_EXCEL_DOC);             Recordset recordset = connection.executeQuery("Select * From "                     +sheet+" ");           /* getting column names*/             ArrayList<String> keys = recordset.getFieldNames();             int size = keys.size();                /* creating array of maps*/             while (recordset.next()) {                 Map<String, String> values = new LinkedMap<>();                 for (int i = 0; i  <size; i++) {                      String key = keys.get(i);                     String value = recordset.getField(key);                     values.put(key, value);                  }                 listOfMaps.add(values);              }             recordset.close();             connection.close();         } catch (FilloException e) {             e.printStackTrace();         }         return listOfMaps;      } 

результат:

[{shop_name=shop#1, сity=Charkiv}, {shop_name=shop#2, сity=Kyiv}, {shop_name=shop#3, сity=Dnipro}]

Превратить мапу в JSON с помощью GSON можно за одно действие, но тут оказалось, что сервер не принимает пустые ключи, поэтому их нужно выпилить в процессе. Следующий метод принимает на вход массив из метода fromExcelToListOfMaps , удаляет ключи без значений и возвращает массив JSON строк.

    public List<String> dataToListOfJson(List<Map<String, String>> listOfMaps){         Gson gson = new Gson();         List<String> listOfJson = new ArrayList<>();         for (int i=0; i<listOfMaps.size(); i++) {             /*             * getting each map collection             * */             Map<String,String> row = listOfMaps.get(i);             /*             * deleting empty values             */             row.entrySet().removeIf(y -> (y.getValue().equals("")));             /*             * Turn map without empty fields to json             */             String requestBody = gson.toJson(row);             listOfJson.add(requestBody);         }         return listOfJson;     }

На входе:

После первого метода:

[{shop_name=shop#1, сity=Charkiv},

{shop_name=, сity=Kyiv},

{shop_name=shop#3, сity=}]

На выходе:

[{«shop_name»:»shop#1″,»сity»:»Charkiv»},

{«сity»:»Kyiv»},

{«shop_name»:»shop#3″}]

В таком виде уже можно отправлять результат в датапровайдер, но у меня значения не только типа String. А как объяснить методу, что вот это вот boolean, а вот это Integer и кавычки тут не нужны, а там вообще массив JSON объектов. И в коде указывать нельзя, получится не универсально. Решил в название столбцов добавить ключевые слова:

1. string — нет ключевого слова

2. boolean — bool

3. float — flo

4. int — int

Ключевое слово отделяется от имени ключа двумя звездочками (**). Теперь, после проверки на пустые значения, выполняется проверка на наличия ключевого слова в названии столбца, и, если это слово есть и соответствует одному из приведенных выше, создается новый ключ (отбрасывается ключевое слово и звездочки) и ему присваивается значение с приведением к нужному типу данных. Выглядит примерно так:

            public static final String FLOAT_VAL = "flo";             public static final String BOOLEAN_VAL  = "bool";                                                           /*work with Float*/                  else if (keyWord.equals(FLOAT_VAL)){                     value = value.replace(",", ".");                     JsonElement a = gson.toJsonTree(Float.parseFloat(value));                     jsElemMap.add(key.substring(x+2), a);                 }                 /*work with Boolean*/                  else if (keyWord.equals(BOOLEAN_VAL)){                     JsonElement a = gson.toJsonTree(Boolean.parseBoolean(value));                     jsElemMap.add(key.substring(x+2), a);

после чего из мапы выпиливаются все пары в ключе которых присутствуют ** . Т.е. если написать в таблице ключевое слово с ошибкой, то эта колонка в запрос не попадет.

В результате

На входе:

на выходе:

[{"shop_name":"shop#1","сity":"Charkiv","employee_qty":3,"Sunday_off":true}, {"shop_name":"shop#2","сity":"Kyiv","employee_qty":3,"Sunday_off":true}, {"shop_name":"shop#3","сity":"Dnipro","employee_qty":2,"Sunday_off":false}]

Конечно работать с разными типами данных в Map<String, String> не получится, поэтому все результаты пересобираются в переменную типа JsonObject.

Дальше нужно было добавить ключи со значениями класса JsonObject. Конечно можно просто в Excel ячейку записать что-то типа {«salary»: 25.0,»bonus»: 4.75}, но ведь у меня уже есть метод, который который соберет JSON из листа Excel. Нужно только указать лист и строку которая будет использоваться. Получилось чуть-чуть рекурсии. Ключевое слово:

5. JsonObject — Если в качестве значения ключа нужен JsonObject, то ключевое слово должно совпадать с названием Excel листа, из которого берутся данные (добавил метод получающий все имена листов Excel файла в массив). Значение в ячейке (тип int) — номер строки в этом листе (нумерация с нуля).

/*work with JsonObjects */                     /*If Excel file contains sheet which name equals keyWord */                      else if (excelSheetNames(PATH_TO_EXCEL_DOC).contains(keyWord)){                          /*get this sheet as list of json*/                          List aa = dataToListOfJson2(fromExcelToListOfMaps(PATH_TO_EXCEL_DOC,keyWord));                         /*and add one of json as value of current key*/                         jsElemMap.add(key.substring(x+2), gson.toJsonTree(aa.get(Integer.parseInt(value))));                      }

В результате на входе:

На выходе:

[{"Id":0,"First_Name":"Wasja","Last_Name":"Smith","Age":19,"Gender":"m","paycheck":{"salary":20.0}}, {"Id":1,"First_Name":"Tanja","Last_Name":"Johnson","Age":25,"Gender":"f","paycheck":{"salary":20.0}}, {"Id":250,"First_Name":"Ighor","Last_Name":"Williams","Age":42,"Gender":"m","paycheck":{"salary":25.0,"bonus":4.75}}, {"Id":13,"First_Name":"Masha","Last_Name":"Brown","Age":19,"Gender":"f","paycheck":{"salary":25.0,"bonus":4.75}}, {"Id":4,"First_Name":"Olja","Last_Name":"Davis","Age":18,"Gender":"f","paycheck":{"salary":33.3,"bonus":5.8}}, {"Id":40,"First_Name":"Oleg","Last_Name":"Miller","Age":25,"Gender":"m","paycheck":{"bonus":100.0}}, {"Id":6,"First_Name":"Kolja","Last_Name":"Wilson","Age":21,"Gender":"m","paycheck":{"salary":33.3,"bonus":5.8}}, {"Id":7,"First_Name":"Andrew","Last_Name":"Moore","Age":20,"Gender":"m","paycheck":{"salary":25.0,"bonus":4.75}}]

Остались массивы. Наверное, стоило написать логику с нуля, но я поленился и решил из JsonObject и выбранной строки просто все значения перенести в List<Object> . Написал отдельный метод. На вход принимает путь к Excel файлу, имя листа и номер строки (нумерация с нуля)

    public List arrayVal(String pathToExcel, String sheetName, int rowNumber){           List<JsonObject> x = dataToListOfJson2(fromExcelToListOfMaps(pathToExcel, sheetName));         JsonObject dataForArray = x.get(rowNumber);         List<Object> arr= new LinkedList<>();         for(String key : dataForArray.keySet()){             arr.add(dataForArray.get(key));         }         return arr;     }

Ключевое слово:

6. List<Object> — arr. Имя столбца должно совпадать с именем Excel листа, из которого будут браться значения для массива. Значение в ячейке (тип int) — номер строки. Имя столбцов в Excel листе из которого будут набираться данные в массив значения не имеют, но они не должны повторяться (Fillo не обработает) и для всех столбцов должно быть одинаковое ключевое слово. (для данных типа String ключевое слово не нужно).

В результате как-то так на входе:

На выходе:

[{"shop_name":"shop#1","сity":"Charkiv","income_per_month":[78500,22222,2222,159]}, {"shop_name":"shop#2","сity":"Kyiv","income_per_month":[56900,11111,987456,6423,98741]}, {"shop_name":"shop#3","сity":"Dnipro","income_per_month":[12694,33333,111111]}]

Вот основной класс с методами:

package api;  import com.codoid.products.exception.FilloException; import com.codoid.products.fillo.Connection; import com.codoid.products.fillo.Fillo; import com.codoid.products.fillo.Recordset; import com.google.gson.Gson; import com.google.gson.JsonElement; import com.google.gson.JsonObject; import org.apache.commons.collections4.map.LinkedMap;  import java.util.*;  import static api.ApiConstants.ApiBodyConstants.*; import static jdk.nashorn.internal.objects.NativeString.toLowerCase;   public class ApiBase {      public static String PATH_TO_EXCEL_DOC = "";      public List fromExcelToListOfMaps (String docPath, String sheet)  {         PATH_TO_EXCEL_DOC = docPath;         Fillo fillo = new Fillo();         Connection connection;         List<Map<String, String>> listOfMaps = new ArrayList<>();         try {            /* getting all the data from excel file */             connection = fillo.getConnection(docPath);             Recordset recordset = connection.executeQuery("Select * From "                     +sheet+" ");           /* getting column names*/             ArrayList<String> keys = recordset.getFieldNames();             int size = keys.size();                /* creating array of maps*/             while (recordset.next()) {                 Map<String, String> values = new LinkedMap<>();                 for (int i = 0; i  <size; i++) {                      String key = keys.get(i);                     String value = recordset.getField(key);                     values.put(key, value);                  }                 listOfMaps.add(values);              }             recordset.close();             connection.close();         } catch (FilloException e) {             e.printStackTrace();         }         return listOfMaps;      }       public List<String> dataToListOfJson(List<Map<String, String>> listOfMaps){         Gson gson = new Gson();         List<String> listOfJson = new ArrayList<>();         for (int i=0; i<listOfMaps.size(); i++) {             /*             * getting each map collection             * */             Map<String,String> row = listOfMaps.get(i);             /*             * deleting empty values             */             row.entrySet().removeIf(y -> (y.getValue().equals("")));              /*deleting empty values in a longer way*/  //            for (Map.Entry<String, String> entry: row.entrySet()) { //                String key = entry.getKey(); //                String value = entry.getValue(); //                if(value.equals("")){ //                    row.remove(key); //                } //            }             /*             * Turn map without empty fields to json             */             String requestBody = gson.toJson(row);             listOfJson.add(requestBody);         }         return listOfJson;     }        public List<JsonObject> dataToListOfJson2(List<Map<String, String>> listOfMaps){         Gson gson = new Gson();         List<JsonObject> listOfJson = new ArrayList<>();          for (int i=0; i<listOfMaps.size(); i++) {             /*              * getting each map collection              * */             Map<String,String> row = listOfMaps.get(i);             JsonObject jsElemMap = new JsonObject();              /*              * deleting keys that have empty values              */             row.entrySet().removeIf(y -> (y.getValue().equals("")));              /*iteration map to change value classes and add them to json object */              for (Map.Entry<String, String> entry: row.entrySet()) {                 String key = entry.getKey();                 String value = entry.getValue();                 if (key.indexOf("**")>0) {                     int x =  key.indexOf("**");                     String keyWord = toLowerCase(key.substring(0, x));                      /*work with Integer*/                     if (keyWord.equals(INT_VAL)){                         JsonElement a = gson.toJsonTree(Integer.parseInt(value));                         jsElemMap.add(key.substring(x+2), a);                     }                     /*work with Float*/                      else if (keyWord.equals(FLOAT_VAL)){                         value = value.replace(",", ".");                         JsonElement a = gson.toJsonTree(Float.parseFloat(value));                         jsElemMap.add(key.substring(x+2), a);                     }                     /*work with Boolean*/                      else if (keyWord.equals(BOOLEAN_VAL)){                         JsonElement a = gson.toJsonTree(Boolean.parseBoolean(value));                         jsElemMap.add(key.substring(x+2), a);                     }                                                                                         /*work with array*/                      else if (keyWord.equals(ARRAY_VAL)&& excelSheetNames(PATH_TO_EXCEL_DOC).contains(key.substring(x+2))){                         List arr = arrayVal(PATH_TO_EXCEL_DOC, key.substring(x+2), Integer.parseInt(value));                         JsonElement a = gson.toJsonTree(arr);                         jsElemMap.add(key.substring(x+2), a);                     }                                                                                                             /*work with JsonObjects */                     /*If Excel file contains sheet which name equals keyWord */                      else if (excelSheetNames(PATH_TO_EXCEL_DOC).contains(keyWord)){                          /*get this sheet as list of json*/                          List aa = dataToListOfJson2(fromExcelToListOfMaps(PATH_TO_EXCEL_DOC,keyWord));                         /*and add one of json as value of current key*/                         jsElemMap.add(key.substring(x+2), gson.toJsonTree(aa.get(Integer.parseInt(value))));                      }                 }else{                     /*If there is no keyWord*/                     jsElemMap.add(key, gson.toJsonTree(value) );                 }              }             /* remove all keys with keyword */             row.entrySet().removeIf(y -> (y.getKey().contains("**")));              /*              * Turn map without empty fields to json              */              listOfJson.add(jsElemMap);         }          return listOfJson;     }         public List arrayVal(String pathToExcel, String sheetName, int rowNumber){           List<JsonObject> x = dataToListOfJson2(fromExcelToListOfMaps(pathToExcel, sheetName));         JsonObject dataForArray = x.get(rowNumber);         List<Object> arr= new LinkedList<>();         for(String key : dataForArray.keySet()){             arr.add(dataForArray.get(key));         }         return arr;     }                    /*getting names of sheets to list*/     public List<String> excelSheetNames(String filePath){         Fillo fillo = new Fillo();         Connection connection =null;         List<String> names = new ArrayList<>();         try {             connection = fillo.getConnection(filePath);             names = connection.getMetaData().getTableNames();             connection.close();          } catch (FilloException filloException) {             connection.close();             filloException.printStackTrace();         }         return names;     }    } 

В общем по функционалу на этом все, осталось заполнить таблицу. Оказалось, что указывать номера строк, находящихся на другом листе, не очень-то и удобно, постоянно какая-то путаница. Тут мне на помощь пришли неправильные ключевые слова. Если в имени столбца присутствует две звездочки (**), но текст перед ** не соответствует одному из ключей, то этот столбец в запросе игнорируется, и в него можно писать комментарии, добавлять нумерацию, или выводить результаты теста. Я ставил четыре звездочки, чтобы сходу видеть, что в запрос не попадет. Таблицы стали выглядеть как-то так:

Осталось передать все это в датапровайдер и превратить в классический двухмерный массив строк.

    @DataProvider(name = "Request2")     public Object[][] request2() {         List<JsonObject> listOfJson = base.dataToListOfJson2(base                 .fromExcelToListOfMaps(PATH_TO_EXCEL_DOC,SHEET_NAME));         String[][] arrayOfJson = new String[listOfJson.size()][1];         for(int i = 0; i< listOfJson.size(); i++){             arrayOfJson[i][0] = listOfJson.get(i).toString();         }         return arrayOfJson;     }

И дальше в тест:

    @Test(dataProvider = "Request2")     public void exampleTest1_3 (String request){         given()                 .spec(requestPostEvents(request))                 .when()                 .log().body()                 .post();     }

Тело первого теста:

Теперь, в случае если будут добавлены, удалены или изменены параметры, или тестовые данные, мне даже не нужно будет заглядывать в код, только подправить табличку. Как и все универсальное, в обращении не очень удобно, но привыкнуть можно.

Все это писалось в отдельном проекте, клонировать можно здесь.

Можно подправить файл Data.xlsx под ваши нужды и позапускать тесты из класса ExampleTests , что бы посмотреть, получите ли вы то, что вам нужно. Если вы начинаете новый проект с API (+WEB) тестами, это можно использовать как шаблон.

Если нужно применить в существующем проекте, но совсем не хочется разбираться как это устроено, то скопируйте пакеты api и файл Data.xlsx в соответствующие разделы вашего проекта на Maven + TestNG, при необходимости добавьте нужные зависимости и пишите тесты в классе ExampleTests.

Собираюсь потихоньку перетянуть сюда все универсальные методы, которые использовал — пусть все лежит в одном месте! Надеюсь, что информация поможет другим начинающим автоматизаторам).