title: Java-TestNG : Read an excel file, assert and write back to the excel file through TestNG & Apache POI
body: <p>I am working on a POC with Selenium Java and TestNG using Apache POI. I have an excel sheet in which Column A contains urls, Column B contains a status (yes or no) whether to read/execute with the corresponding url or not.I want to save back the execution status Pass/Fail in a Column C.</p>
  
  <h2>My excel sheet "TestData.xlsx" <a href="https://i.stack.imgur.com/JlaAN.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JlaAN.png" alt="looks like this"></a>.</h2>
  
  <h2>My calling file "read_write_excel.java" is as follows:</h2>
  
  <pre><code>package Read_Write_Excel;
  import org.testng.annotations.Test;
  import libraries.read_excel_data;
  public class read_write_excel 
  {
  
      String path = "C:\\SeleniumUtilities\\excelData\\TestData.xlsx";
      @Test
      public void read_excel() throws Exception
      {
          read_excel_data excel = new read_excel_data(path);
          excel.read_data(0);
      }
  }
  </code></pre>
  
  <h2>Reading the excel sheet through "read_excel_data.java" as follows:</h2>
  
  <pre><code>package libraries;
  
  import java.io.File;
  import java.io.FileInputStream;
  import java.io.FileNotFoundException;
  import java.io.FileOutputStream;
  import java.io.IOException;
  
  import org.apache.poi.xssf.usermodel.XSSFRow;
  import org.apache.poi.xssf.usermodel.XSSFSheet;
  import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  
  public class read_excel_data 
  {
  
      XSSFWorkbook workbook;
      XSSFSheet sheet;
      String location;
      int SheetNo;
      write_excel_data write_excel;
      public read_excel_data(String excelPath)
          {
              location = excelPath;
              try {
                  File src = new File(excelPath);
                  FileInputStream fis = new FileInputStream(src);
                  workbook = new XSSFWorkbook(fis);
                  write_excel = new write_excel_data(location);
  
              } catch (Exception e) {
                  System.out.println(e.getMessage());
              }
          }
  
  
      public void read_data(int sheetNumber) throws Exception
      {
          SheetNo = sheetNumber;
          sheet = workbook.getSheetAt(sheetNumber);
          int rows = sheet.getLastRowNum();
          System.out.println("Total number of Rows to read : "+(rows+1));
          for(int i=0;i&lt;=rows;i++)
          {
              XSSFRow now_row = sheet.getRow(i);
              if(now_row.getCell(1).getStringCellValue().equalsIgnoreCase("yes"))
              {
                  String data = now_row.getCell(0).getStringCellValue();
                  System.out.println("Data from Row "+i+" is: "+data);
                  String status = assertion.asserting(data);
                  System.out.println("Status is : "+status);
                  write_excel.write_data(SheetNo,i,status);
              }
          }
  
          workbook.close();
      }
  
  }
  </code></pre>
  
  <h2>Assertions are done in "assertion.java" like:</h2>
  
  <pre><code>package libraries;
  public class assertion 
  {
  
      public static String asserting(String url)
      {
          String result;
          if(url.contains("com"))
              {
                  result = "Pass";
                  System.out.println("Result is : "+result);
                  return result;
              }
          else
              {
                  result = "Fail";
                  System.out.println("Result is : "+result);
                  return result;
              }
  
      }
  }
  </code></pre>
  
  <h2>Writing back to the excel file through "write_excel_data.java" like:</h2>
  
  <pre><code>package libraries;
  import java.io.File;
  import java.io.FileInputStream;
  import java.io.FileNotFoundException;
  import java.io.FileOutputStream;
  
  import org.apache.poi.xssf.usermodel.XSSFRow;
  import org.apache.poi.xssf.usermodel.XSSFSheet;
  import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  
  public class write_excel_data 
  {
  
      File des;
      XSSFSheet sheet;
      XSSFWorkbook wb;
      public write_excel_data(String location)
      {
          try {
              des = new File(location);
              FileInputStream fis = new FileInputStream(des);
              wb = new XSSFWorkbook(fis);
  
  
          } catch (Exception e) {
              System.out.println(e.getMessage());
          }
  
      }
  
      public void write_data(int sheetNo,int i, String status) throws Exception
      {
          sheet = wb.getSheetAt(sheetNo);
          int rows = sheet.getLastRowNum();
          System.out.println("Total number of Rows to write : "+(rows+1));
          sheet.getRow(i).createCell(2).setCellValue(status);
          FileOutputStream fos = new FileOutputStream(des);
          wb.write(fos);
          wb.close();
      }
  }
  </code></pre>
  
  <p>The issue I am facing are:</p>
  
  <ol>
  <li><p>Sometimes I run into a NullPointerException</p></li>
  <li><p>Sometime the Excel sheet gets corrupted and doesn't writes at all.</p></li>
  </ol>
  
  <p>Can you please let me know:</p>
  
  <ol>
  <li><p>Is my approach correct?</p></li>
  <li><p>Any suggestion for betterment?</p></li>
  <li><p>If I am doing any mistakes here.</p></li>
  </ol>
  
  <hr>
  
  <p>Update -1:</p>
  
  <p>Latest Stacktrace:</p>
  
  <pre><code>[TestNG] Running:
    C:\Users\DEDLY\AppData\Local\Temp\testng-eclipse--1619303069\testng-customsuite.xml
  
  Total number of Rows to read : 3
  Data from Row 0 is: a.com
  Result is : Pass
  Status is : Pass
  Total number of Rows to write : 3
  Data from Row 1 is: b.com
  Result is : Pass
  Status is : Pass
  Total number of Rows to write : 3
  [Utils] Attempting to create C:\Users\DEDLY\workspace\SeleniumJavaTestNG\test-output\Default suite\Default test.xml
  [Utils]   Directory C:\Users\DEDLY\workspace\SeleniumJavaTestNG\test-output\Default suite exists: true
  FAILED: read_excel
  org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException: Rule M2.4 exception : this error should NEVER happen, if so please send a mail to the developers team, thanks !
      at org.apache.poi.openxml4j.opc.internal.ContentTypeManager.getContentType(ContentTypeManager.java:342)
      at org.apache.poi.openxml4j.opc.internal.ContentTypeManager.removeContentType(ContentTypeManager.java:255)
      at org.apache.poi.openxml4j.opc.OPCPackage.removePart(OPCPackage.java:943)
      at org.apache.poi.openxml4j.opc.PackagePart.getOutputStream(PackagePart.java:522)
      at org.apache.poi.xssf.usermodel.XSSFWorkbook.commit(XSSFWorkbook.java:1546)
      at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:332)
      at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:195)
      at libraries.write_excel_data.write_data(write_excel_data.java:38)
      at libraries.read_excel_data.read_data(read_excel_data.java:51)
      at Read_Write_Excel.read_write_excel.read_excel(read_write_excel.java:15)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
      at java.lang.reflect.Method.invoke(Unknown Source)
      at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:100)
      at org.testng.internal.Invoker.invokeMethod(Invoker.java:646)
      at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:811)
      at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1137)
      at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:129)
      at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:112)
      at org.testng.TestRunner.privateRun(TestRunner.java:753)
      at org.testng.TestRunner.run(TestRunner.java:607)
      at org.testng.SuiteRunner.runTest(SuiteRunner.java:368)
      at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:363)
      at org.testng.SuiteRunner.privateRun(SuiteRunner.java:321)
      at org.testng.SuiteRunner.run(SuiteRunner.java:270)
      at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
      at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
      at org.testng.TestNG.runSuitesSequentially(TestNG.java:1284)
      at org.testng.TestNG.runSuitesLocally(TestNG.java:1209)
      at org.testng.TestNG.runSuites(TestNG.java:1124)
      at org.testng.TestNG.run(TestNG.java:1096)
      at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:132)
      at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:236)
      at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:81)
  
  
  ===============================================
      Default test
      Tests run: 1, Failures: 1, Skips: 0
  ===============================================
  
  
  ===============================================
  Default suite
  Total tests run: 1, Failures: 1, Skips: 0
  ===============================================
  </code></pre>
  
  <hr>
  
  <p>Update - 2:</p>
  
  <p>When I try to open the "TestData.xlsx" manually, I get the following error as <a href="https://i.stack.imgur.com/7cePe.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7cePe.png" alt="attached"></a>.</p>