Read Data from Excel - Notes By ShariqSP

How to Read Data from Excel in Android Mobile Application Testing

Excel files (XLS/XLSX) are widely used in mobile application testing for managing test data, such as login credentials, form inputs, or large datasets required for testing app functionality. Reading data from Excel requires specific libraries and techniques. Below is a detailed guide with real-time scenarios:

Steps to Read Data from Excel

1. Choose a Library:
   • Use Apache POI for reading and writing Excel files in Java-based projects.
   • For Android, use lightweight libraries like Android POI or JXL (JExcel).
2. Include the Library in Your Project:
   • Add the required dependencies in your build.gradle file.
   • Example for Apache POI:

     
                   implementation 'org.apache.poi:poi:5.2.3'
                   implementation 'org.apache.poi:poi-ooxml:5.2.3'
                             

3. Place the Excel File:
   • Store the Excel file in the assets folder of your Android project.
   • Ensure that the file is accessible and readable during runtime.
4. Write Code to Read Data:
   • Use InputStream to access the Excel file from the assets folder.
   • Parse the file using Apache POI or JExcel to extract the required data.

Sample Code

              import org.apache.poi.ss.usermodel.*;
              import java.io.InputStream;

              public class ExcelReader {
                  public void readExcelData(Context context) {
                      try {
                          // Access the Excel file from assets
                          InputStream inputStream = context.getAssets().open("testdata.xlsx");
                          Workbook workbook = WorkbookFactory.create(inputStream);

                          // Access the first sheet
                          Sheet sheet = workbook.getSheetAt(0);

                          // Iterate through rows and cells
                          for (Row row : sheet) {
                              for (Cell cell : row) {
                                  switch (cell.getCellType()) {
                                      case STRING:
                                          System.out.println("String Value: " + cell.getStringCellValue());
                                          break;
                                      case NUMERIC:
                                          System.out.println("Numeric Value: " + cell.getNumericCellValue());
                                          break;
                                      case BOOLEAN:
                                          System.out.println("Boolean Value: " + cell.getBooleanCellValue());
                                          break;
                                      default:
                                          System.out.println("Other Value: " + cell.toString());
                                  }
                              }
                          }

                          // Close the workbook
                          workbook.close();
                      } catch (Exception e) {
                          e.printStackTrace();
                      }
                  }
              }
                

Real-Time Scenarios

1. Testing Login Functionality:
   • Store multiple sets of login credentials (username, password) in the Excel file.
   • Read each row from the Excel file and perform login attempts.
   • Verify that the app behaves as expected (successful login, error messages, etc.).
2. Form Validation Testing:
   • Store various combinations of form inputs (valid and invalid data) in the Excel file.
   • Automate form submission by reading inputs from the Excel file and validate results.
3. Bulk Data Testing:
   • Test scenarios where the app processes large datasets, such as importing contacts or displaying analytics.
   • Use Excel as the data source and verify the app's performance and accuracy.

Advantages of Using Excel for Test Data

• Easy to manage and edit test data.
• Supports large datasets with structured formatting.
• Widely compatible with automation tools.