public Vector<Donor> ReadDonors() throws Exception {
if (MyDonorsSheet == null) LoadDonorWorksheet();
Vector<Donor> donors = new Vector<Donor>();
try {
List<WorksheetEntry> worksheets = MyDonorsSheet.getWorksheets();
WorksheetEntry entry = worksheets.get(0);
if (entry == null) return null;
CellFeed cellFeed = MyService.getFeed(entry.getCellFeedUrl(), CellFeed.class);
if (cellFeed == null) return null;
int numCols = cellFeed.getColCount();
int numRows = cellFeed.getRowCount() - 1;
List<CellEntry> cellEntryList = cellFeed.getEntries();
String cells[][] = new String[numRows][numCols];
for (CellEntry cellEntry : cellEntryList) {
int row = cellEntry.getCell().getRow() - 1;
int col = cellEntry.getCell().getCol() - 1;
if (row > 0) {
cells[row - 1][col] = cellEntry.getCell().getValue();
}
}
for (int row = 0; row < numRows; row++) {
Donor donor = new Donor();
donor.DonorID = TrimNonNull(cells[row][0]);
donor.FirstName = TrimNonNull(cells[row][1]);
donor.LastName = TrimNonNull(cells[row][2]);
donor.SpouseName = TrimNonNull(cells[row][3]);
donor.Organization = TrimNonNull(cells[row][4]);
donor.StreetAddress = TrimNonNull(cells[row][5]);
donor.City = TrimNonNull(cells[row][6]);
donor.State = TrimNonNull(cells[row][7]);
donor.Country = TrimNonNull(cells[row][8]);
donor.ZipCode = TrimNonNull(cells[row][9]);
donor.Phone = TrimNonNull(cells[row][10]);
donor.EmailAddress = TrimNonNull(cells[row][11]);
donor.Notes = TrimNonNull(cells[row][12]);
donor.Thanker = TrimNonNull(cells[row][13]);
donors.add(donor);
}
} catch (ServiceException E) {
throw new Exception("Cannot access Huruma House Donors spreadsheet");
}
return donors;
}
public void writeBatchRows(
WorksheetEntry worksheetEntry, List<Map<Integer, Object>> rowList, int rowOffset)
throws IOException, ServiceException {
long startTime = System.currentTimeMillis();
URL cellFeedUrl = worksheetEntry.getCellFeedUrl();
CellFeed cellFeed = spreadsheetService.getFeed(worksheetEntry.getCellFeedUrl(), CellFeed.class);
CellFeed batchRequest = new CellFeed();
logger.info("Get Row Count: " + cellFeed.getRowCount());
int rowToBegin = rowOffset;
addEmptyRows(worksheetEntry, rowList.size());
logger.info("Row To Begin: " + rowToBegin);
// Build list of cell addresses to be filled in
List<CellAddress> cellAddrs = new ArrayList<CellAddress>();
CellAddress cellAddress = new CellAddress();
String formula;
for (Map<Integer, Object> row : rowList) {
for (Map.Entry<Integer, Object> entry : row.entrySet()) {
int column = entry.getKey();
if (!(entry.getValue() instanceof String)) {
formula = entry.getValue().toString();
} else {
formula = (String) entry.getValue();
}
logger.info("********************Column: " + column + "Formula: " + formula);
cellAddress.setCol(column);
cellAddress.setRow(rowToBegin);
cellAddress.setIdString(String.format("R%sC%s", rowToBegin, column));
cellAddrs.add(cellAddress);
for (CellAddress cellAddr : cellAddrs) {
CellEntry batchEntry = new CellEntry(cellAddr.row, cellAddr.col, formula);
batchEntry.setId(String.format("%s/%s", cellFeedUrl.toString(), cellAddr.idString));
BatchUtils.setBatchId(batchEntry, cellAddr.idString);
BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.UPDATE);
logger.fine("Batch Entry: " + batchEntry);
batchRequest.getEntries().add(batchEntry);
batchEntry = null;
}
cellAddrs.clear();
}
// batch per row
if (rowToBegin % 100 == 0) {
long startBatchTime = System.currentTimeMillis();
logger.info("\n\n\nEvery 100 rows batch call: " + rowToBegin);
performBatchUpdate(batchRequest, cellFeedUrl);
batchRequest.getEntries().clear();
logger.info("\n\n ms elapsed for batch: " + (System.currentTimeMillis() - startBatchTime));
}
rowToBegin++;
}
logger.info(
"\n\n\n\nms elapsed to create batch request: " + (System.currentTimeMillis() - startTime));
// for the stragglers
logger.info("\n\n\nLast rows batch call: " + rowToBegin);
performBatchUpdate(batchRequest, cellFeedUrl);
}