ReadExcelsAnywhere

To obtain an api key for Google sheets

1. Visit https://console.developers.google.com/apis
2. Create a project and go to Dashboard
3. Enable Google Sheets API
4. Go to Credentials
5. Click Create Credentials and select API key
6. Copy the API key showed in the dialog box.

---

To get access to Excel sheet stored on Sharepoint or OneDrive

1. Visit https://developer.microsoft.com/en-us/graph
2. Login with a valid account
3. Follow the instructions to register your app and make a note of the bearer token
4. https://developer.microsoft.com/en-us/graph/docs/concepts/auth_v2_service
5. Follow below instruction to generate a refresh token
6. https://docs.microsoft.com/en-us/azure/active-directory/develop/active-directory-v2-protocols-oauth-code
7. Refresh will enable user to access the resources without logging in again/ bearer token expires

---

Sample Tests

@Test(enabled = false)
public void testGoogleSheetViaOAuth() throws IOException {

    String googleSheetResourceId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";

    GoogleDriveOAuth googleDriveOAuth = new GoogleDriveOAuth(googleSheetResourceId);
    ReadExcel<GoogleDriveOAuth> readExcel = new ReadExcel<>();

    List<String> sheets = readExcel.getListOfSheets(googleDriveOAuth);
    System.out.println(sheets);
}

@Test
public void testGoogleSheetRead() throws IOException {

    String resourceId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";
    String key = "<api_key>";

    GoogleDriveAPI googleDriveAPI = new GoogleDriveAPI(key,resourceId);
    ReadExcel<GoogleDriveAPI> readExcel = new ReadExcel<>();

    List<String> sheets = readExcel.getListOfSheets(googleDriveAPI);

    System.out.println(sheets);
}

@Test
public void testGoogleSheetReadData() throws IOException {

    //https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0
    String resourceId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";
    String key = "<api_key>";
    String sheetName = "Class Data";

    GoogleDriveAPI googleDriveAPI = new GoogleDriveAPI(key, resourceId);
    ReadExcel<GoogleDriveAPI> readExcel = new ReadExcel<>();

    List<List<Object>> data = readExcel.getExcelData(googleDriveAPI, sheetName, "UsedRange");
    System.out.println(data);

    String[][] dataArray = readExcel.getExcelDataInStringArray(googleDriveAPI,"UsedRange");
    System.out.println(dataArray);

}

@Test
public void testSharePointExcel() throws IOException {
    String applicationId = "bb3435a1-869c-494b-9cb0-793f145dd316";
    String refreshToken = "<refresh_token>";
    String resourceId = "01XI34BXCOA2S5NQIA3JEIFJJTG5AM3KY5";

    MSOffice msOffice = new MSOffice(ExcelLocation.SHAREPOINT, applicationId, resourceId, refreshToken);
    ReadExcel<MSOffice> readExcel = new ReadExcel<>();

    List<String> sheets = readExcel.getListOfSheets(msOffice);
    System.out.println(sheets);

    List<List<Object>> data = readExcel.getExcelData(msOffice, "Salesforce", "UsedRange");
    System.out.println(data);
}

@Test
public void testOneDriveExcel() throws IOException {

    String applicationId = "d1c318de-dcb6-4e35-a1ad-15907c7b8744";
    String refreshToken = "<refresh_token>";

    String resourceId = "d1c318de-dcb6-4e35-a1ad-15907c7b8744";

    MSOffice office = new MSOffice(ExcelLocation.ONE_DRIVE,applicationId, resourceId, refreshToken);
    ReadExcel<MSOffice> readExcel = new ReadExcel<>();

    List<String> sheets = readExcel.getListOfSheets(office);
    System.out.println(sheets);
}

@Test
public void testLocalExcel() throws IOException {
    String localExcelPath = getClass().getClassLoader().getResource("SmokeTests.xlsx").getPath();
    MSOffice office = new MSOffice(ExcelLocation.LOCAL,localExcelPath);
    ReadExcel<MSOffice> readExcel = new ReadExcel<>();

    List<String> sheets = readExcel.getListOfSheets(office);
    System.out.println(sheets);
}