-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathCellDemo.java
More file actions
511 lines (457 loc) · 17.8 KB
/
CellDemo.java
File metadata and controls
511 lines (457 loc) · 17.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
/* Copyright (c) 2008 Google Inc.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package sample.spreadsheet.cell;
import sample.util.SimpleCommandLineParser;
import com.google.gdata.client.spreadsheet.CellQuery;
import com.google.gdata.client.spreadsheet.FeedURLFactory;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.BaseEntry;
import com.google.gdata.data.Link;
import com.google.gdata.data.batch.BatchOperationType;
import com.google.gdata.data.batch.BatchStatus;
import com.google.gdata.data.batch.BatchUtils;
import com.google.gdata.data.spreadsheet.CellEntry;
import com.google.gdata.data.spreadsheet.CellFeed;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.data.spreadsheet.WorksheetEntry;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ServiceException;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.net.URL;
import java.util.List;
/**
* Using this demo, you can see how GData can read and write to individual cells
* based on their position or send a batch of update commands in one HTTP
* request.
*
* Usage: java CellDemo --username [user] --password [pass]
*/
public class CellDemo {
/** The message for displaying the usage parameters. */
private static final String[] USAGE_MESSAGE = {
"Usage: java CellDemo --username [user] --password [pass] ", ""};
/** Welcome message, introducing the program. */
private static final String[] WELCOME_MESSAGE = {
"This is a demo of the cells feed!", "",
"Using this interface, you can read/write to your spreadsheet's cells.",
""};
/** Help on all available commands. */
private static final String[] COMMAND_HELP_MESSAGE = {
"Commands:",
" load "
+ "[[select a spreadsheet and worksheet]]",
" list [[shows all cells]]",
" range minRow maxRow minCol maxCol [[rectangle]]",
" set row# col# formula [[sets a cell]]",
" example: set 1 3 =R1C2+1",
" search adam [[full text query]]",
" batch [[batch request]]",
" exit"};
/** Our view of Google Spreadsheets as an authenticated Google user. */
private SpreadsheetService service;
/** The URL of the cells feed. */
private URL cellFeedUrl;
/** The output stream. */
private PrintStream out;
/** A factory that generates the appropriate feed URLs. */
private FeedURLFactory factory;
/**
* Constructs a cell demo from the specified spreadsheet service, which is
* used to authenticate to and access Google Spreadsheets.
*
* @param service the connection to the Google Spradsheets service.
* @param outputStream a handle for stdout.
*/
public CellDemo(SpreadsheetService service, PrintStream outputStream) {
this.service = service;
this.out = outputStream;
this.factory = FeedURLFactory.getDefault();
}
/**
* Log in to Google, under the Google Spreadsheets account.
*
* @param username name of user to authenticate (e.g. yourname@gmail.com)
* @param password password to use for authentication
* @throws AuthenticationException if the service is unable to validate the
* username and password.
*/
public void login(String username, String password)
throws AuthenticationException {
// Authenticate
service.setUserCredentials(username, password);
}
/**
* Displays the given list of entries and prompts the user to select the index
* of one of the entries. NOTE: The displayed index is 1-based and is
* converted to 0-based before being returned.
*
* @param reader to read input from the keyboard
* @param entries the list of entries to display
* @param type describes the type of things the list contains
* @return the 0-based index of the user's selection
* @throws IOException if an I/O error occurs while getting input from user
*/
private int getIndexFromUser(BufferedReader reader, List entries, String type)
throws IOException {
for (int i = 0; i < entries.size(); i++) {
BaseEntry entry = (BaseEntry) entries.get(i);
System.out.println("\t(" + (i + 1) + ") "
+ entry.getTitle().getPlainText());
}
int index = -1;
while (true) {
out.print("Enter the number of the spreadsheet to load: ");
String userInput = reader.readLine();
try {
index = Integer.parseInt(userInput);
if (index < 1 || index > entries.size()) {
throw new NumberFormatException();
}
break;
} catch (NumberFormatException e) {
System.out.println("Please enter a valid number for your selection.");
}
}
return index - 1;
}
/**
* Uses the user's credentials to get a list of spreadsheets. Then asks the
* user which spreadsheet to load. If the selected spreadsheet has multiple
* worksheets then the user will also be prompted to select what sheet to use.
*
* @param reader to read input from the keyboard
* @throws ServiceException when the request causes an error in the Google
* Spreadsheets service.
* @throws IOException when an error occurs in communication with the Google
* Spreadsheets service.
*
*/
public void loadSheet(BufferedReader reader) throws IOException,
ServiceException {
// Get the spreadsheet to load
SpreadsheetFeed feed = service.getFeed(factory.getSpreadsheetsFeedUrl(),
SpreadsheetFeed.class);
List spreadsheets = feed.getEntries();
int spreadsheetIndex = getIndexFromUser(reader, spreadsheets,
"spreadsheet");
SpreadsheetEntry spreadsheet = feed.getEntries().get(spreadsheetIndex);
// Get the worksheet to load
if (spreadsheet.getWorksheets().size() == 1) {
cellFeedUrl = spreadsheet.getWorksheets().get(0).getCellFeedUrl();
} else {
List worksheets = spreadsheet.getWorksheets();
int worksheetIndex = getIndexFromUser(reader, worksheets, "worksheet");
WorksheetEntry worksheet = (WorksheetEntry) worksheets
.get(worksheetIndex);
cellFeedUrl = worksheet.getCellFeedUrl();
}
System.out.println("Sheet loaded.");
}
/**
* Sets the particular cell at row, col to the specified formula or value.
*
* @param row the row number, starting with 1
* @param col the column number, starting with 1
* @param formulaOrValue the value if it doesn't start with an '=' sign; if it
* is a formula, be careful that cells are specified in R1C1 format
* instead of A1 format.
* @throws ServiceException when the request causes an error in the Google
* Spreadsheets service.
* @throws IOException when an error occurs in communication with the Google
* Spreadsheets service.
*/
public void setCell(int row, int col, String formulaOrValue)
throws IOException, ServiceException {
CellEntry newEntry = new CellEntry(row, col, formulaOrValue);
service.insert(cellFeedUrl, newEntry);
out.println("Added!");
}
/**
* Prints out the specified cell.
*
* @param cell the cell to print
*/
public void printCell(CellEntry cell) {
String shortId = cell.getId().substring(cell.getId().lastIndexOf('/') + 1);
out.println(" -- Cell(" + shortId + "/" + cell.getTitle().getPlainText()
+ ") formula(" + cell.getCell().getInputValue() + ") numeric("
+ cell.getCell().getNumericValue() + ") value("
+ cell.getCell().getValue() + ")");
}
/**
* Shows all cells that are in the spreadsheet.
*
* @throws ServiceException when the request causes an error in the Google
* Spreadsheets service.
* @throws IOException when an error occurs in communication with the Google
* Spreadsheets service.
*/
public void showAllCells() throws IOException, ServiceException {
CellFeed feed = service.getFeed(cellFeedUrl, CellFeed.class);
for (CellEntry entry : feed.getEntries()) {
printCell(entry);
}
}
/**
* Shows a particular range of cells, limited by minimum/maximum rows and
* columns.
*
* @param minRow the minimum row, inclusive, 1-based
* @param maxRow the maximum row, inclusive, 1-based
* @param minCol the minimum column, inclusive, 1-based
* @param maxCol the maximum column, inclusive, 1-based
* @throws ServiceException when the request causes an error in the Google
* Spreadsheets service.
* @throws IOException when an error occurs in communication with the Google
* Spreadsheets service.
*/
public void showRange(int minRow, int maxRow, int minCol, int maxCol)
throws IOException, ServiceException {
CellQuery query = new CellQuery(cellFeedUrl);
query.setMinimumRow(minRow);
query.setMaximumRow(maxRow);
query.setMinimumCol(minCol);
query.setMaximumCol(maxCol);
CellFeed feed = service.query(query, CellFeed.class);
for (CellEntry entry : feed.getEntries()) {
printCell(entry);
}
}
/**
* Performs a full-text search on cells.
*
* @param fullTextSearchString a full text search string, with space-separated
* keywords
* @throws ServiceException when the request causes an error in the Google
* Spreadsheets service.
* @throws IOException when an error occurs in communication with the Google
* Spreadsheets service.
*/
public void search(String fullTextSearchString) throws IOException,
ServiceException {
CellQuery query = new CellQuery(cellFeedUrl);
query.setFullTextQuery(fullTextSearchString);
CellFeed feed = service.query(query, CellFeed.class);
out.println("Results for [" + fullTextSearchString + "]");
for (CellEntry entry : feed.getEntries()) {
printCell(entry);
}
}
/**
* Writes (to stdout) a list of the entries in the batch request in a human
* readable format.
*
* @param batchRequest the CellFeed containing entries to display.
*/
private void printBatchRequest(CellFeed batchRequest) {
System.out.println("Current operations in batch");
for (CellEntry entry : batchRequest.getEntries()) {
String msg = "\tID: " + BatchUtils.getBatchId(entry) + " - "
+ BatchUtils.getBatchOperationType(entry) + " row: "
+ entry.getCell().getRow() + " col: " + entry.getCell().getCol()
+ " value: " + entry.getCell().getInputValue();
System.out.println(msg);
}
}
/**
* Returns a CellEntry with batch id and operation type that will tell the
* server to update the specified cell with the given value. The entry is
* fetched from the server in order to get the current edit link (for
* optimistic concurrency).
*
* @param row the row number of the cell to operate on
* @param col the column number of the cell to operate on
* @param value the value to set in case of an update the cell to operate on
*
* @throws ServiceException when the request causes an error in the Google
* Spreadsheets service.
* @throws IOException when an error occurs in communication with the Google
* Spreadsheets service.
*/
private CellEntry createUpdateOperation(int row, int col, String value)
throws ServiceException, IOException {
String batchId = "R" + row + "C" + col;
URL entryUrl = new URL(cellFeedUrl.toString() + "/" + batchId);
CellEntry entry = service.getEntry(entryUrl, CellEntry.class);
entry.changeInputValueLocal(value);
BatchUtils.setBatchId(entry, batchId);
BatchUtils.setBatchOperationType(entry, BatchOperationType.UPDATE);
return entry;
}
/**
* Prompts the user for a set of operations and submits them in a batch
* request.
*
* @param reader to read input from the keyboard.
*
* @throws ServiceException when the request causes an error in the Google
* Spreadsheets service.
* @throws IOException when an error occurs in communication with the Google
* Spreadsheets service.
*/
public void processBatchRequest(BufferedReader reader)
throws IOException, ServiceException {
final String BATCH_PROMPT = "Enter set operations one by one, "
+ "then enter submit to send the batch request:\n"
+ " set row# col# value [[add a set operation]]\n"
+ " submit [[submit the request]]";
CellFeed batchRequest = new CellFeed();
// Prompt user for operation
System.out.println(BATCH_PROMPT);
String operation = reader.readLine();
while (!operation.startsWith("submit")) {
String[] s = operation.split(" ", 4);
if (s.length != 4 || !s[0].equals("set")) {
System.out.println("Invalid command: " + operation);
operation = reader.readLine();
continue;
}
// Create a new cell entry and add it to the batch request.
int row = Integer.parseInt(s[1]);
int col = Integer.parseInt(s[2]);
String value = s[3];
CellEntry batchOperation = createUpdateOperation(row, col, value);
batchRequest.getEntries().add(batchOperation);
// Display the current entries in the batch request.
printBatchRequest(batchRequest);
// Prompt for another operation.
System.out.println(BATCH_PROMPT);
operation = reader.readLine();
}
// Get the batch feed URL and submit the batch request
CellFeed feed = service.getFeed(cellFeedUrl, CellFeed.class);
Link batchLink = feed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM);
URL batchUrl = new URL(batchLink.getHref());
CellFeed batchResponse = service.batch(batchUrl, batchRequest);
// Print any errors that may have happened.
boolean isSuccess = true;
for (CellEntry entry : batchResponse.getEntries()) {
String batchId = BatchUtils.getBatchId(entry);
if (!BatchUtils.isSuccess(entry)) {
isSuccess = false;
BatchStatus status = BatchUtils.getBatchStatus(entry);
System.out.println("\n" + batchId + " failed (" + status.getReason()
+ ") " + status.getContent());
}
}
if (isSuccess) {
System.out.println("Batch operations successful.");
}
}
/**
* Reads and executes one command.
*
* @param reader to read input from the keyboard
* @return false if the user quits, true on exception
*/
public boolean executeCommand(BufferedReader reader) {
for (String s : COMMAND_HELP_MESSAGE) {
out.println(s);
}
System.err.print("Command: ");
try {
String command = reader.readLine();
String[] parts = command.trim().split(" ", 2);
String name = parts[0];
String parameters = parts.length > 1 ? parts[1] : "";
if (name.equals("list")) {
showAllCells();
} else if (name.equals("load")) {
loadSheet(reader);
} else if (name.equals("search")) {
search(parameters);
} else if (name.equals("range")) {
String[] s = parameters.split(" ", 4);
showRange(Integer.parseInt(s[0]), Integer.parseInt(s[1]), Integer
.parseInt(s[2]), Integer.parseInt(s[3]));
} else if (name.equals("set")) {
String[] s = parameters.split(" ", 3);
setCell(Integer.parseInt(s[0]), Integer.parseInt(s[1]), s[2]);
} else if (name.equals("batch")) {
processBatchRequest(reader);
} else if (name.startsWith("q") || name.startsWith("exit")) {
return false;
} else {
out.println("Unknown command.");
}
} catch (Exception e) {
// Show *exactly* what went wrong.
e.printStackTrace();
}
return true;
}
/**
* Starts up the demo and prompts for commands.
*
* @param username name of user to authenticate (e.g. yourname@gmail.com)
* @param password password to use for authentication
* @throws AuthenticationException if the service is unable to validate the
* username and password.
*/
public void run(String username, String password)
throws AuthenticationException {
for (String s : WELCOME_MESSAGE) {
out.println(s);
}
BufferedReader reader = new BufferedReader(
new InputStreamReader(System.in));
// Login and prompt the user to pick a sheet to use.
login(username, password);
try {
loadSheet(reader);
} catch (IOException e) {
e.printStackTrace();
} catch (ServiceException e) {
e.printStackTrace();
}
while (executeCommand(reader)) {
}
}
/**
* Runs the demo.
*
* @param args the command-line arguments
* @throws AuthenticationException if the service is unable to validate the
* username and password.
*/
public static void main(String[] args) throws AuthenticationException {
SimpleCommandLineParser parser = new SimpleCommandLineParser(args);
String username = parser.getValue("username", "user", "u");
String password = parser.getValue("password", "pass", "p");
boolean help = parser.containsKey("help", "h");
if (help || username == null || password == null) {
usage();
System.exit(1);
}
CellDemo demo = new CellDemo(new SpreadsheetService("Cell Demo"),
System.out);
demo.run(username, password);
}
/**
* Prints out the usage.
*/
private static void usage() {
for (String s : USAGE_MESSAGE) {
System.out.println(s);
}
for (String s : WELCOME_MESSAGE) {
System.out.println(s);
}
}
}