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

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 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);
    }
}
 

protected CellFeed createBatchRequest(String[] header, List<ReportItem> reportItems, int startingRow, int endingRow, int numberOfColumns, Map<String, CellEntry> cellEntries)
{
    CellFeed batchRequest = new CellFeed();
    for (int rowIndex = startingRow; rowIndex <= endingRow; rowIndex++) {
        for (int columnIndex = 1; columnIndex <= numberOfColumns; columnIndex++) {
            String id = getR1C1Id(rowIndex, columnIndex);
            CellEntry batchEntry = new CellEntry(cellEntries.get(id));
            String rowHeader = header[columnIndex - 1];
            if (rowIndex == 1) {
                batchEntry.changeInputValueLocal(rowHeader);
            } else
                batchEntry.changeInputValueLocal(reportItems.get(rowIndex - 2).getNamedValues().get(rowHeader));
            BatchUtils.setBatchId(batchEntry, id);
            BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.UPDATE);
            batchRequest.getEntries().add(batchEntry);
        }
    }
    return batchRequest;
}
 

private Map<String, CellEntry> prepareBatchByQueringWorksheet(URL cellFeedUrl, int numberOfRows, int numberOfColumns) throws IOException, ServiceException
{
    CellFeed batchRequest = new CellFeed();
    for (int rowIndex = 1; rowIndex <= numberOfRows; rowIndex++) {
        for (int columnIndex = 1; columnIndex <= numberOfColumns; columnIndex++) {
            String id = getR1C1Id(rowIndex, columnIndex);
            CellEntry batchEntry = new CellEntry(rowIndex, columnIndex, id);
            batchEntry.setId(String.format("%s/%s", cellFeedUrl.toString(), id));
            BatchUtils.setBatchId(batchEntry, id);
            BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.QUERY);
            batchRequest.getEntries().add(batchEntry);
        }
    }

    CellFeed cellFeed = spreadsheetService.getFeed(cellFeedUrl, CellFeed.class);
    CellFeed queryBatchResponse = spreadsheetService.batch(new URL(cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM).getHref()), batchRequest);

    Map<String, CellEntry> cellEntryMap = new HashMap<String, CellEntry>(numberOfColumns);
    for (CellEntry entry : queryBatchResponse.getEntries()) {
        cellEntryMap.put(BatchUtils.getBatchId(entry), entry);
    }
    return cellEntryMap;
}
 

private Map<Integer, TitleWithQueries> getTitlesWithQueries(List<CellEntry> cellEntries, Map<String, String> header, String worksheetId)
{
    Map<Integer, TitleWithQueries> returnValue = Maps.newLinkedHashMap();
    for (CellEntry cell : cellEntries) {
        String column = getColumnFromCellAddress(cell);
        Integer row = getRowFromCellAddress(cell);
        String value = cell.getCell().getValue().trim();
        if (row == 1)
            continue;
        TitleWithQueries titleWithQueries = returnValue.get(row);
        if (titleWithQueries == null)
            titleWithQueries = new TitleWithQueries(worksheetId);
        String headerValue = header.get(column);
        titleWithQueries.setValue(headerValue, value);
        returnValue.put(row, titleWithQueries);
    }
    return returnValue;
}
 

private Map<String, CellEntry> getCellEntryMap(List<SpreadsheetCell> cells) throws
        IOException, ServiceException {

    CellFeed batchRequest = new CellFeed();
    for (SpreadsheetCell cell : cells) {
        CellEntry batchEntry = new CellEntry(cell.row, cell.col, cell.id);
        batchEntry.setId(String.format("%s/%s", data.cellFeedURL.toString(), cell.id));
        BatchUtils.setBatchId(batchEntry, cell.id);
        BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.QUERY);
        batchRequest.getEntries().add(batchEntry);
    }

    CellFeed batchResponse = batchRequest(data.cellBatchURL, batchRequest);

    Map<String, CellEntry> cellEntryMap = new HashMap<String, CellEntry>(cells.size());
    for (CellEntry entry : batchResponse.getEntries()) {
        cellEntryMap.put(BatchUtils.getBatchId(entry), entry);
    }

    return cellEntryMap;
}
 

/**
 * 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()]);
}
 

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 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);
}
 

protected boolean checkResults(CellFeed batchResponse)
{
    boolean isSuccess = true;
    for (CellEntry entry : batchResponse.getEntries()) {
        String batchId = BatchUtils.getBatchId(entry);
        if (!BatchUtils.isSuccess(entry)) {
            isSuccess = false;
            BatchStatus status = BatchUtils.getBatchStatus(entry);
            logger.error(String.format("%s failed (%s) %s", batchId, status.getReason(), status.getContent()));
        }
    }
    return isSuccess;
}
 

private Map<String, String> getHeader(List<CellEntry> cellEntries)
{
    Map<String, String> returnValue = Maps.newLinkedHashMap();
    for (CellEntry cell : cellEntries) {
        String column = getColumnFromCellAddress(cell);
        Integer row = getRowFromCellAddress(cell);
        String value = cell.getCell().getValue();
        if (row == 1)
            returnValue.put(column, value);
    }
    return returnValue;
}
 

private List<ReportItem> getReport(List<CellEntry> cellEntries, Map<String, String> header, boolean isDetailReport)
{
    List<ReportItem> returnValue = Lists.newArrayList();
    int previousRow = 0;
    ReportItem reportItem = null;
    for (CellEntry cell : cellEntries) {
        String column = getColumnFromCellAddress(cell);
        Integer row = getRowFromCellAddress(cell);
        String value = cell.getCell().getValue();
        if (row == 1)
            continue;
        if (previousRow != row) {
            if (row != 1 && reportItem!=null)
                returnValue.add(reportItem);
            if (isDetailReport)
                reportItem = new DetailReportItem();
            else
                reportItem = new SummaryReportItem();
        }
        String headerValue = header.get(column);
        reportItem.setValue(headerValue, value);
        previousRow = row;
    }
    if(reportItem!=null)
        returnValue.add(reportItem);
    return returnValue;
}
 

private String getColumnFromCellAddress(CellEntry cell)
{
    Matcher matcher = VALID_A1_PATTERN.matcher(cell.getTitle().getPlainText());
    while (matcher.find()) {
        return matcher.group(1);
    }
    return null;
}
 

private Integer getRowFromCellAddress(CellEntry cell)
{
    Matcher matcher = VALID_A1_PATTERN.matcher(cell.getTitle().getPlainText());
    while (matcher.find()) {
        return Integer.valueOf(matcher.group(2));
    }
    return null;
}
 

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);
    }
}