com.google.gdata.data.spreadsheet.WorksheetEntry Java Examples

private void updateWorksheetWithAllItems(WorksheetEntry worksheet, String[] header, List<ReportItem> reportItems, int numberOfRows, int numberOfColumns, String reportSpreadsheetName)
        throws BatchInterruptedException, MalformedURLException, IOException, ServiceException
{
    URL cellFeedUrl = worksheet.getCellFeedUrl();
    CellFeed cellFeed = spreadsheetService.getFeed(cellFeedUrl, CellFeed.class);

    Map<String, CellEntry> cellEntries = prepareBatchByQueringWorksheet(cellFeedUrl, numberOfRows, numberOfColumns);

    int startingRow = 1;
    int rowsInBatch = ((Double) Math.ceil((double)numberOfRows / Properties.googleSheetsBatchUploadSizeSplitFactor.get())).intValue();
    int endingRow = rowsInBatch;
    for (int i = 0; i < Properties.googleSheetsBatchUploadSizeSplitFactor.get(); i++) {
        CellFeed batchRequest = createBatchRequest(header, reportItems, startingRow, endingRow, numberOfColumns, cellEntries);
        Link batchLink = cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM);
        CellFeed batchResponse = spreadsheetService.batch(new URL(batchLink.getHref()), batchRequest);
        boolean isSuccess = checkResults(batchResponse);
        logger.info((isSuccess ? "Batch operations successful: " : "Batch operations failed: ") + reportSpreadsheetName + " " + worksheet.getTitle().getPlainText() + " starting row: " + startingRow +", through row: " + endingRow);
        if(endingRow==startingRow) break;
        startingRow = startingRow + rowsInBatch;
        endingRow = Math.min(numberOfRows, endingRow + rowsInBatch);
    }
}
 

private static ColumnInfo[] getGSpreadHeaders(Connection connection,
                                              String sheetName) throws SQLException {
    WorksheetEntry currentWorksheet;
    List<ColumnInfo> columns = new ArrayList<ColumnInfo>();

    if (!(connection instanceof TGSpreadConnection)) {
        throw new SQLException("Invalid connection type");
    }
    currentWorksheet = getCurrentWorkSheetEntry((TGSpreadConnection) connection, sheetName);
    if (currentWorksheet == null) {
        throw new SQLException("Worksheet '" + sheetName + "' does not exist");
    }
    CellFeed cellFeed = getGSpreadCellFeed((TGSpreadConnection) connection, currentWorksheet);
    for (CellEntry cell : cellFeed.getEntries()) {
        if (!getCellPosition(cell.getId()).startsWith("R1")) {
            break;
        }
        ColumnInfo column =
                new ColumnInfo(cell.getTextContent().getContent().getPlainText());
        column.setTableName(sheetName);
        column.setSqlType(cell.getContent().getType());
        column.setId(getColumnIndex(cell.getId()) - 1);
        columns.add(column);
    }
    return columns.toArray(new ColumnInfo[columns.size()]);
}
 

public GSpreadResultSet retrieveData() throws Exception {
	URL worksheetUrl = this.getConfig().generateWorksheetFeedURL();
	WorksheetFeed feedw = this.getConfig().getFeed(worksheetUrl, WorksheetFeed.class);
	WorksheetEntry worksheetEntry = feedw.getEntries().get(this.getWorksheetNumber() - 1);			
	CellFeed feedc = this.getConfig().getFeed(worksheetEntry.getCellFeedUrl(), CellFeed.class);			
	List<CellEntry> entries = feedc.getEntries();			
	GSpreadResultSet grs = new GSpreadResultSet();
	
	/* store the data */
	for (CellEntry entry : entries) {
		grs.addCell(this.getPosStringFromId(entry.getId()), 
				entry.getTextContent().getContent().getPlainText());				
	}
	
	return grs;
}
 

private String getLatestWorksheetId(SpreadsheetEntry spreadsheet) throws Throwable
{
    String worksheetId = null;
    List<WorksheetEntry> worksheets = spreadsheet.getWorksheets();
    Date reportCurrentDate = new Date(Long.MIN_VALUE);
    for (WorksheetEntry worksheet : worksheets) {
        String title = worksheet.getTitle().getPlainText();
        if(title.equals("instructions") || title.equals("Sheet1") || title.startsWith("diff_") || title.startsWith("ignore_")) continue;
        Date date = dateUtil.getDateFromString(title);
        if (date.after(reportCurrentDate)){
            reportCurrentDate = date;
            worksheetId = title;
        }
    }
    return worksheetId;
}
 

private WorksheetEntry getCurrentWorksheetEntry(TGSpreadConnection conn) throws SQLException {
    WorksheetEntry currentWorksheetEntry = null;
    SpreadsheetEntry currentSpreadsheetEntry =
            conn.getSpreadSheetFeed().getEntries().get(0);
    WorksheetFeed worksheetFeed =
            conn.getFeedProcessor().getFeed(
                    currentSpreadsheetEntry.getWorksheetFeedUrl(), WorksheetFeed.class);
    for (WorksheetEntry worksheetEntry : worksheetFeed.getEntries()) {
        if (this.getTableName().equals(worksheetEntry.getTitle().getPlainText())) {
            currentWorksheetEntry = worksheetEntry;
            break;
        }
    }
    return currentWorksheetEntry;
}
 

/**
 * Gets the worksheet url.
 * 
 * @param spreadsheetService the spreadsheet service
 * @param spreadsheetId the spreadsheet id
 */
private URL getWorksheetUrl(SpreadsheetService spreadsheetService, String spreadsheetId)
    throws IOException, ServiceException {
  if (isCancelled()) {
    return null;
  }
  URL url = new URL(String.format(Locale.US, GET_WORKSHEETS_URI, spreadsheetId));
  WorksheetFeed feed = spreadsheetService.getFeed(url, WorksheetFeed.class);
  List<WorksheetEntry> worksheets = feed.getEntries();

  if (worksheets.size() > 0) {
    return worksheets.get(0).getListFeedUrl();
  }
  return null;
}
 

private WorksheetEntry getWorksheet(SpreadsheetEntry spreadsheet, String worksheetId) throws Throwable
{
    List<WorksheetEntry> worksheets = spreadsheet.getWorksheets();
    for (WorksheetEntry worksheet : worksheets) {
        String title = worksheet.getTitle().getPlainText();
        if (title.equalsIgnoreCase(worksheetId))
            return worksheet;
    }
    return null;
}
 

private void addNewWorksheet(SpreadsheetEntry spreadsheet, String worksheetId, int numberOfRows, int numberOfColumns) throws Throwable
{
    if (spreadsheet != null) {
        WorksheetEntry worksheet = new WorksheetEntry();
        worksheet.setTitle(new PlainTextConstruct(worksheetId));
        worksheet.setRowCount(numberOfRows);
        worksheet.setColCount(numberOfColumns);

        URL worksheetFeedUrl = spreadsheet.getWorksheetFeedUrl();
        spreadsheetService.insert(worksheetFeedUrl, worksheet);
    }
}
 

public void updateReport(String worksheetId, String[] reportHeader, List<ReportItem> reportItems, boolean isDetailReport) throws Throwable, IOException, ServiceException
{
    String reportSpreadsheetName = getReportName(isDetailReport);
    SpreadsheetEntry spreadsheet = getSpreadsheet(reportSpreadsheetName );

    int numberOfRows = reportItems.size() + 1;
    int numberOfColumns = reportHeader.length;

    addNewWorksheet(spreadsheet, worksheetId, numberOfRows, numberOfColumns);

    WorksheetEntry worksheet = getWorksheet(spreadsheet, worksheetId);
    if (worksheet != null) {
        updateWorksheetWithAllItems(worksheet, reportHeader, reportItems, numberOfRows, numberOfColumns, reportSpreadsheetName);
    }
}
 

private List<String> extractWorksheetData(SpreadsheetEntry spreadsheet, String worksheetId, String[] header) throws Throwable, IOException, ServiceException
{
    List<String> returnValue = Lists.newArrayList();
    WorksheetEntry worksheet = getWorksheet(spreadsheet, worksheetId);
    if (worksheet != null) {
        ListFeed listFeed = getListFeedForWorksheet(worksheet);
        returnValue = getAllEntries(listFeed, header);
    }
    return returnValue;
}
 

public void extractReport(Report report, boolean isDetailReport) throws Throwable
{
    List<ReportItem> reportItems = null;
    String spreadsheetName = getReportName(isDetailReport);
    SpreadsheetEntry spreadsheet = getSpreadsheet(spreadsheetName);
    String worksheetId = getLatestWorksheetId(spreadsheet);
    WorksheetEntry worksheet = getWorksheet(spreadsheet, worksheetId);
    if (worksheet != null) {
        List<CellEntry> cellEntries = getCellsForWorksheet(worksheet).getEntries();
        Map<String, String> header = getHeader(cellEntries);
        reportItems = getReport(cellEntries, header, isDetailReport);
    }
    report.setItems(reportItems);
    report.setDate(worksheetId);
}
 

public Map<Integer, TitleWithQueries> extractTitlesWithQueries(String worksheetId) throws Throwable, IOException, ServiceException
{
    Map<Integer, TitleWithQueries> titlesWithQueries = null;
    SpreadsheetEntry spreadsheet = getSpreadsheet(Properties.inputQueriesSheet.get());
    WorksheetEntry worksheet = getWorksheet(spreadsheet, worksheetId);
    if (worksheet != null) {
        List<CellEntry> cellEntries = getCellsForWorksheet(worksheet).getEntries();
        Map<String, String> header = getHeader(cellEntries);
        titlesWithQueries = getTitlesWithQueries(cellEntries, header, worksheetId);
    }
    return titlesWithQueries;
}
 

public static WorksheetEntry getCurrentWorkSheetEntry(TGSpreadConnection connection,
                                                      String sheetName) throws SQLException {
    SpreadsheetEntry spreadsheetEntry = connection.getSpreadSheetFeed().getEntries().get(0);
    WorksheetQuery worksheetQuery =
            TDriverUtil.createWorkSheetQuery(spreadsheetEntry.getWorksheetFeedUrl());
    WorksheetFeed worksheetFeed = connection.getFeedProcessor().getFeed(worksheetQuery,
                                                                        WorksheetFeed.class);
    for (WorksheetEntry entry : worksheetFeed.getEntries()) {
        if (sheetName.equals(entry.getTitle().getPlainText())) {
            return entry;
        }
    }
    return null;
}
 

/**
 * Extracts out the header elements of the spreadsheet entry that is being queried.
 *
 * @param currentWorkSheet Worksheet being queried
 * @return Map containing the header names and their indices
 * @throws SQLException Is thrown if an error occurs while extracting the spreadsheet
 *                      cell feed
 */
private ColumnInfo[] extractHeaders(WorksheetEntry currentWorkSheet) throws
        SQLException {
    List<ColumnInfo> headers = new ArrayList<ColumnInfo>();

    /* If hasHeader property is set to false, populate header map with column names following
     * the format 'COLUMN' + 'i' where i corresponds to the column id */
    if (!((TConnection) getConnection()).hasHeader()) {
        int maxColumns = ((TConnection) getConnection()).getMaxColumns();
        for (int i = 1; i < maxColumns + 1; i++) {
            headers.add(new ColumnInfo(i, Constants.COLUMN + i,
                    currentWorkSheet.getTitle().getPlainText(), -1, i));
        }
        return headers.toArray(new ColumnInfo[headers.size()]);
    }

    CellFeed cellFeed = TDriverUtil.getGSpreadCellFeed((TGSpreadConnection) getConnection(), currentWorkSheet);
    for (CellEntry cell : cellFeed.getEntries()) {
        if (!TDriverUtil.getCellPosition(cell.getId()).startsWith("R1")) {
            break;
        }
        int columnIndex = TDriverUtil.getColumnIndex(cell.getId());
        headers.add(new ColumnInfo(
                columnIndex, cell.getTextContent().getContent().getPlainText(),
                currentWorkSheet.getTitle().getPlainText(), Types.VARCHAR, columnIndex));
    }
    return headers.toArray(new ColumnInfo[headers.size()]);
}
 

private boolean isWorkSheetExists(TGSpreadConnection conn) {
    WorksheetFeed worksheetFeed = conn.getWorksheetFeed();
    for (WorksheetEntry worksheet : worksheetFeed.getEntries()) {
        if (this.getTableName().equals(worksheet.getTitle().getPlainText())) {
            return true;
        }
    }
    return false;
}
 

public static ListFeed getListFeed(TGSpreadConnection connection,
                                   WorksheetEntry currentWorkSheet) throws SQLException {
    ListQuery listQuery = new ListQuery(currentWorkSheet.getListFeedUrl());
    return connection.getFeedProcessor().getFeed(listQuery, ListFeed.class);
}
 

public static CellFeed getGSpreadCellFeed(TGSpreadConnection connection,
                                   WorksheetEntry currentWorkSheet) throws SQLException {
    CellQuery cellQuery = new CellQuery(currentWorkSheet.getCellFeedUrl());
    return connection.getFeedProcessor().getFeed(cellQuery, CellFeed.class);
}
 

public void populateData() throws SQLException {
    int tmp = -1;

    TGSpreadConnection gsConnection = (TGSpreadConnection) getConnection();
    WorksheetFeed workSheetFeed = gsConnection.getWorksheetFeed();
    if (workSheetFeed == null) {
        throw new SQLException("Work sheet feed it not initialized properly and is null");
    }
    List<WorksheetEntry> workSheets = workSheetFeed.getEntries();
    for (WorksheetEntry workSheet : workSheets) {
        DataRow dataRow = null;
        CellFeed cellFeed = TDriverUtil.getGSpreadCellFeed((TGSpreadConnection) getConnection(), workSheet);

        ColumnInfo[] headers = this.extractHeaders(workSheet);
        DataTable result = new FixedDataTable(workSheet.getTitle().getPlainText(), headers);
        for (CellEntry cell : cellFeed.getEntries()) {
            int rowId = TDriverUtil.getRowIndex(cell.getId());
            if (tmp != rowId && rowId != 1) {
                if (dataRow != null) {
                    result.addRow(this.fillUpEmptyCells(dataRow, headers));
                }
                dataRow = new DataRow(rowId - 1);
                tmp = rowId;
            }
            int columnId = TDriverUtil.getColumnIndex(cell.getId());
            if (columnId > headers.length) {
                continue;
            }
            if (rowId != 1 && dataRow != null) {
                DataCell dataCell =
                        new DataCell(TDriverUtil.getColumnIndex(cell.getId()),
                                cell.getContent().getType(),
                                cell.getTextContent().getContent().getPlainText());

                dataRow.addCell(dataCell.getColumnId(), dataCell);
            }
        }
        /* adding the last row of the sheet */
        if (dataRow != null) {
            result.addRow(this.fillUpEmptyCells(dataRow, headers));
        }
        this.getData().put(result.getTableName(), result);
    }
}
 

private CellFeed getCellsForWorksheet(WorksheetEntry worksheet) throws IOException, ServiceException
{
    URL cellFeedUrl = worksheet.getCellFeedUrl();
    CellFeed cellFeed = spreadsheetService.getFeed(cellFeedUrl, CellFeed.class);
    return cellFeed;
}
 

private ListFeed getListFeedForWorksheet(WorksheetEntry worksheet) throws IOException, ServiceException
{
    URL listFeedUrl = worksheet.getListFeedUrl();
    ListFeed cellFeed = spreadsheetService.getFeed(listFeedUrl, ListFeed.class);
    return cellFeed;
}
 

/**
 * Deletes Google Spreadsheets row.
 * 
 * @param context the context
 * @param accountName the account name
 * @param trackName the track name
 * @return true if deletion is success.
 */
public static boolean deleteSpreadsheetsRow(
    Context context, String accountName, String trackName) {
  try {
    // Get spreadsheet Id
    List<File> files = searchSpreadsheets(context, accountName);
    if (files == null || files.size() == 0) {
      return false;
    }
    String spreadsheetId = files.get(0).getId();

    // Get spreadsheet service
    SpreadsheetService spreadsheetService = new SpreadsheetService(
        "MyTracks-" + SystemUtils.getMyTracksVersion(context));
    Credential credential = new Credential(BearerToken.authorizationHeaderAccessMethod());
    credential.setAccessToken(
        SendToGoogleUtils.getToken(context, accountName, SendToGoogleUtils.SPREADSHEETS_SCOPE));
    spreadsheetService.setOAuth2Credentials(credential);

    // Get work sheet
    WorksheetFeed worksheetFeed = spreadsheetService.getFeed(new URL(
        String.format(Locale.US, SendSpreadsheetsAsyncTask.GET_WORKSHEETS_URI, spreadsheetId)),
        WorksheetFeed.class);
    Iterator<WorksheetEntry> worksheetEntryIterator = worksheetFeed.getEntries().iterator();
    while (worksheetEntryIterator.hasNext()) {
      WorksheetEntry worksheetEntry = (WorksheetEntry) worksheetEntryIterator.next();
      String worksheetTitle = worksheetEntry.getTitle().getPlainText();
      if (worksheetTitle.equals(SPREADSHEETS_WORKSHEET_NAME)) {
        URL url = worksheetEntry.getListFeedUrl();
        Iterator<ListEntry> listEntryIterator = spreadsheetService.getFeed(url, ListFeed.class)
            .getEntries().iterator();
        while (listEntryIterator.hasNext()) {
          ListEntry listEntry = (ListEntry) listEntryIterator.next();
          String name = listEntry.getCustomElements().getValue(SPREADSHEETS_TRANCK_NAME_COLUMN);
          if (name.equals(trackName)) {
            listEntry.delete();
            return true;
          }
        }
      }
    }
  } catch (Exception e) {
    Log.e(TAG, "Unable to delete spreadsheets row.", e);
  }
  return false;
}
 

private static URL getFeedUrl(WorkSheetEntryEnum entryEnum) {
    WorksheetEntry entry = worksheets.get(entryEnum.index());
    return entry.getListFeedUrl();
}