{"id":2047,"date":"2017-02-05T16:02:19","date_gmt":"2017-02-05T16:02:19","guid":{"rendered":"http:\/\/codethataint.com\/blog\/?p=2047"},"modified":"2017-02-05T16:06:11","modified_gmt":"2017-02-05T16:06:11","slug":"reading-excel-sheet","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/reading-excel-sheet\/","title":{"rendered":"Reading Excel Sheet"},"content":{"rendered":"<p><strong>JARs to be added<\/strong><\/p>\n<ul>\n<li>poi-3.8.jar<\/li>\n<li>poi-examples-3.8.jar<\/li>\n<li>poi-excelant-3.8.jar <\/li>\n<li>poi-ooxml-3.8.jar<\/li>\n<li>poi-ooxml-schemas-3.8.jar<\/li>\n<li>poi-scratchpad-3.8.jar <\/li>\n<li>dom4j-1.6.1.jar<\/li>\n<\/ul>\n<p><strong>Reading Excel Sheet<\/strong><\/p>\n<pre class=\"brush: java; title: ; notranslate\" title=\"\">\r\nimport org.apache.poi.ss.usermodel.*;\r\nimport org.apache.poi.hssf.usermodel.*;\r\nimport org.apache.poi.xssf.usermodel.*;\r\nimport org.apache.poi.ss.util.*;\r\nimport org.apache.poi.ss.usermodel.*;\r\nimport java.io.*;\r\n \r\nclass ExcelReader {\r\n  \r\n  def readData() {\r\n        def path = &quot;D:\\\\Test.xlsx&quot;;\r\n        InputStream inputStream = new FileInputStream(path);\r\n        Workbook workbook = WorkbookFactory.create(inputStream);\r\n        Sheet sheet = workbook.getSheetAt(0);\r\n                      \r\n        Iterator rowIterator = sheet.rowIterator();\r\n        rowIterator.next()\r\n        Row row;                       \r\n        def rowsData = &#x5B;]\r\n        while(rowIterator.hasNext()) {\r\n             row = rowIterator.next()\r\n             def rowIndex = row.getRowNum()\r\n             def colIndex;\r\n             def rowData = &#x5B;]\r\n             for (Cell cell : row) {\r\n                 colIndex = cell.getColumnIndex()\r\n                  rowData&#x5B;colIndex] = cell.getRichStringCellValue().getString();\r\n             }                    \r\n             rowsData &lt;&lt; rowData\r\n         }\r\n         rowsData\r\n  }\r\n }\r\n \r\ndef groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)\r\ndef myTestCase = context.testCase\r\n \r\nExcelReader excelReader = new ExcelReader();\r\nList rows = excelReader.readData();\r\ndef d = &#x5B;]\r\nIterator i = rows.iterator();\r\nwhile( i.hasNext()){\r\n         d = i.next();\r\n         myTestCase.setPropertyValue(&quot;From&quot;, d&#x5B;0])\r\n         myTestCase.setPropertyValue(&quot;To&quot;, d&#x5B;1])       \r\n         testRunner.runTestStepByName( &quot;ConversionRate&quot;)\r\n}\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>JARs to be added poi-3.8.jar poi-examples-3.8.jar poi-excelant-3.8.jar poi-ooxml-3.8.jar poi-ooxml-schemas-3.8.jar poi-scratchpad-3.8.jar dom4j-1.6.1.jar Reading Excel Sheet import org.apache.poi.ss.usermodel.*; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.xssf.usermodel.*; import org.apache.poi.ss.util.*; import org.apache.poi.ss.usermodel.*; import java.io.*; class ExcelReader { def readData() { def path = &quot;D:\\\\Test.xlsx&quot;; InputStream inputStream = new FileInputStream(path); Workbook workbook = WorkbookFactory.create(inputStream); Sheet sheet = workbook.getSheetAt(0); Iterator rowIterator = sheet.rowIterator(); rowIterator.next() Row&hellip; <a href=\"https:\/\/codethataint.com\/blog\/reading-excel-sheet\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[222],"tags":[],"class_list":["post-2047","post","type-post","status-publish","format-standard","hentry","category-groovy"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/2047","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/comments?post=2047"}],"version-history":[{"count":3,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/2047\/revisions"}],"predecessor-version":[{"id":2050,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/2047\/revisions\/2050"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=2047"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=2047"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=2047"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}