/** Presents the given cell feed as a map from row, column pair to CellEntry. */ private void refreshCachedData() throws IOException, ServiceException { CellQuery cellQuery = new CellQuery(backingEntry.getCellFeedUrl()); cellQuery.setReturnEmpty(true); this.cellFeed = spreadsheetService.getFeed(cellQuery, CellFeed.class); // A subtlety: Spreadsheets row,col numbers are 1-based whereas the // cellEntries array is 0-based. Rather than wasting an extra row and // column worth of cells in memory, we adjust accesses by subtracting // 1 from each row or column number. cellEntries = new CellEntry[rows][columns]; for (CellEntry cellEntry : cellFeed.getEntries()) { Cell cell = cellEntry.getCell(); cellEntries[cell.getRow() - 1][cell.getCol() - 1] = cellEntry; } }
public ImportClient(String username, String password, int itemsPerBatch, String spreadsheetName) throws Exception { ITEMS_PER_BATCH = itemsPerBatch; factory = FeedURLFactory.getDefault(); service = new SpreadsheetService("Props-2-Xls"); service.setUserCredentials(username, password); service.setProtocolVersion( SpreadsheetService.Versions .V1); // bug workaround! http://code.google.com/p/gdata-java-client/issues/detail?id=103 spreadsheet = getSpreadsheet(spreadsheetName); backingEntry = spreadsheet.getDefaultWorksheet(); CellQuery cellQuery = new CellQuery(backingEntry.getCellFeedUrl()); cellQuery.setReturnEmpty(true); CellFeed cellFeed = service.getFeed(cellQuery, CellFeed.class); }
List<List<Object>> getRowsOfCells( SpreadsheetService service, WorksheetEntry worksheet, int startRow, // 1-based int rowCount) throws IOException, ServiceException { URL cellFeedUrl = worksheet.getCellFeedUrl(); int minRow = startRow; int maxRow = Math.min(worksheet.getRowCount(), startRow + rowCount - 1); int cols = worksheet.getColCount(); int rows = worksheet.getRowCount(); CellQuery cellQuery = new CellQuery(cellFeedUrl); cellQuery.setMinimumRow(minRow); cellQuery.setMaximumRow(maxRow); cellQuery.setMaximumCol(cols); cellQuery.setMaxResults(rows * cols); cellQuery.setReturnEmpty(false); CellFeed cellFeed = service.query(cellQuery, CellFeed.class); List<CellEntry> cellEntries = cellFeed.getEntries(); List<List<Object>> rowsOfCells = new ArrayList<List<Object>>(rowCount); for (CellEntry cellEntry : cellEntries) { Cell cell = cellEntry.getCell(); if (cell != null) { int row = cell.getRow() - startRow; int col = cell.getCol() - 1; while (row >= rowsOfCells.size()) { rowsOfCells.add(new ArrayList<Object>()); } List<Object> rowOfCells = rowsOfCells.get(row); while (col >= rowOfCells.size()) { rowOfCells.add(null); } rowOfCells.set(col, cell.getValue()); } } return rowsOfCells; }