Tilman
235 lines
8.0 KiB
Java
235 lines
8.0 KiB
Java
/* 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;
|
|
|
|
import sample.util.SimpleCommandLineParser;
|
|
import com.google.gdata.client.spreadsheet.FeedURLFactory;
|
|
import com.google.gdata.client.spreadsheet.SpreadsheetQuery;
|
|
import com.google.gdata.client.spreadsheet.SpreadsheetService;
|
|
import com.google.gdata.client.spreadsheet.WorksheetQuery;
|
|
import com.google.gdata.data.Link;
|
|
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.data.spreadsheet.WorksheetFeed;
|
|
|
|
import java.io.BufferedReader;
|
|
import java.io.FileReader;
|
|
import java.net.URL;
|
|
import java.util.List;
|
|
import java.util.regex.Pattern;
|
|
|
|
/**
|
|
* An application that serves as a sample to show how the SpreadsheetService
|
|
* can be used to import delimited text file to a spreadsheet.
|
|
*
|
|
*
|
|
*/
|
|
public class ImportClient {
|
|
|
|
private SpreadsheetService service;
|
|
|
|
private FeedURLFactory factory;
|
|
|
|
public ImportClient() throws Exception {
|
|
factory = FeedURLFactory.getDefault();
|
|
service = new SpreadsheetService("gdata-sample-spreadsheetimport");
|
|
}
|
|
|
|
/**
|
|
* Creates a client object for which the provided username and password
|
|
* produces a valid authentication.
|
|
*
|
|
* @param username the Google service user name
|
|
* @param password the corresponding password for the user name
|
|
* @throws Exception if error is encountered, such as invalid username and
|
|
* password pair
|
|
*/
|
|
public ImportClient(String username, String password) throws Exception {
|
|
this();
|
|
service.setUserCredentials(username, password);
|
|
}
|
|
|
|
/**
|
|
* Gets the SpreadsheetEntry for the first spreadsheet with that name
|
|
* retrieved in the feed.
|
|
*
|
|
* @param spreadsheet the name of the spreadsheet
|
|
* @return the first SpreadsheetEntry in the returned feed, so latest
|
|
* spreadsheet with the specified name
|
|
* @throws Exception if error is encountered, such as no spreadsheets with the
|
|
* name
|
|
*/
|
|
public SpreadsheetEntry getSpreadsheet(String spreadsheet)
|
|
throws Exception {
|
|
|
|
SpreadsheetQuery spreadsheetQuery
|
|
= new SpreadsheetQuery(factory.getSpreadsheetsFeedUrl());
|
|
spreadsheetQuery.setTitleQuery(spreadsheet);
|
|
SpreadsheetFeed spreadsheetFeed = service.query(spreadsheetQuery,
|
|
SpreadsheetFeed.class);
|
|
List<SpreadsheetEntry> spreadsheets = spreadsheetFeed.getEntries();
|
|
if (spreadsheets.isEmpty()) {
|
|
throw new Exception("No spreadsheets with that name");
|
|
}
|
|
|
|
return spreadsheets.get(0);
|
|
}
|
|
|
|
/**
|
|
* Get the WorksheetEntry for the worksheet in the spreadsheet with the
|
|
* specified name.
|
|
*
|
|
* @param spreadsheet the name of the spreadsheet
|
|
* @param worksheet the name of the worksheet in the spreadsheet
|
|
* @return worksheet with the specified name in the spreadsheet with the
|
|
* specified name
|
|
* @throws Exception if error is encountered, such as no spreadsheets with the
|
|
* name, or no worksheet wiht the name in the spreadsheet
|
|
*/
|
|
public WorksheetEntry getWorksheet(String spreadsheet, String worksheet)
|
|
throws Exception {
|
|
|
|
SpreadsheetEntry spreadsheetEntry = getSpreadsheet(spreadsheet);
|
|
|
|
WorksheetQuery worksheetQuery
|
|
= new WorksheetQuery(spreadsheetEntry.getWorksheetFeedUrl());
|
|
|
|
worksheetQuery.setTitleQuery(worksheet);
|
|
WorksheetFeed worksheetFeed = service.query(worksheetQuery,
|
|
WorksheetFeed.class);
|
|
List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
|
|
if (worksheets.isEmpty()) {
|
|
throw new Exception("No worksheets with that name in spreadhsheet "
|
|
+ spreadsheetEntry.getTitle().getPlainText());
|
|
}
|
|
|
|
return worksheets.get(0);
|
|
}
|
|
|
|
/**
|
|
* Clears all the cell entries in the worksheet.
|
|
*
|
|
* @param spreadsheet the name of the spreadsheet
|
|
* @param worksheet the name of the worksheet
|
|
* @throws Exception if error is encountered, such as bad permissions
|
|
*/
|
|
public void purgeWorksheet(String spreadsheet, String worksheet)
|
|
throws Exception {
|
|
|
|
WorksheetEntry worksheetEntry = getWorksheet(spreadsheet, worksheet);
|
|
CellFeed cellFeed = service.getFeed(worksheetEntry.getCellFeedUrl(),
|
|
CellFeed.class);
|
|
|
|
List<CellEntry> cells = cellFeed.getEntries();
|
|
for (CellEntry cell : cells) {
|
|
Link editLink = cell.getEditLink();
|
|
service.delete(new URL(editLink.getHref()));
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Inserts a cell entry in the worksheet.
|
|
*
|
|
* @param spreadsheet the name of the spreadsheet
|
|
* @param worksheet the name of the worksheet
|
|
* @param row the index of the row
|
|
* @param column the index of the column
|
|
* @param input the input string for the cell
|
|
* @throws Exception if error is encountered, such as bad permissions
|
|
*/
|
|
public void insertCellEntry(String spreadsheet, String worksheet,
|
|
int row, int column, String input) throws Exception {
|
|
|
|
URL cellFeedUrl = getWorksheet(spreadsheet, worksheet).getCellFeedUrl();
|
|
|
|
CellEntry newEntry = new CellEntry(row, column, input);
|
|
|
|
service.insert(cellFeedUrl, newEntry);
|
|
}
|
|
|
|
/**
|
|
* Prints the usage of this application.
|
|
*/
|
|
private static void usage() {
|
|
System.out.println("Usage: java ImportClient --username [user] "
|
|
+ "--password [pass] --filename [file] --spreadsheet [name] "
|
|
+ "--worksheet [name] --delimiter [regex]");
|
|
System.out.println("\nA simple application that uses the provided Google\n"
|
|
+ "Account username and password to locate the\n"
|
|
+ "spreadsheet and worksheet in user's Google\n"
|
|
+ "Spreadsheet account, and import the provided\n"
|
|
+ "delimited text file into the worksheet.");
|
|
}
|
|
|
|
/**
|
|
* Main entry point. Parses arguments and creates and invokes the
|
|
* ImportClient.
|
|
*/
|
|
public static void main(String[] args) throws Exception {
|
|
|
|
SimpleCommandLineParser parser = new SimpleCommandLineParser(args);
|
|
String username = parser.getValue("username", "user", "u");
|
|
String password = parser.getValue("password", "pass", "passwd", "pw", "p");
|
|
String filename = parser.getValue("filename", "file", "f");
|
|
String spreadsheet = parser.getValue("spreadsheet", "s");
|
|
String worksheet = parser.getValue("worksheet", "w");
|
|
String delimiter = parser.getValue("delimiter", "delimit", "d");
|
|
boolean help = parser.containsKey("help", "h");
|
|
if (help || (username == null) || (password == null)
|
|
|| (spreadsheet == null) || (worksheet == null)
|
|
|| (delimiter == null)) {
|
|
usage();
|
|
System.exit(1);
|
|
}
|
|
|
|
ImportClient client = new ImportClient(username, password);
|
|
|
|
client.purgeWorksheet(spreadsheet, worksheet);
|
|
|
|
Pattern delim = Pattern.compile(delimiter);
|
|
|
|
BufferedReader reader = null;
|
|
try {
|
|
reader = new BufferedReader(new FileReader(filename));
|
|
String line = reader.readLine();
|
|
int row = 0;
|
|
while (line != null) {
|
|
|
|
// Break up the line by the delimiter and insert the cells
|
|
String[] cells = delim.split(line, -1);
|
|
for (int col = 0; col < cells.length; col++) {
|
|
client.insertCellEntry(spreadsheet, worksheet,
|
|
row + 1, col + 1, cells[col]);
|
|
}
|
|
|
|
// Advance the loop
|
|
line = reader.readLine();
|
|
row++;
|
|
}
|
|
} catch (Exception e) {
|
|
throw e;
|
|
} finally {
|
|
if (reader != null) {
|
|
reader.close();
|
|
}
|
|
}
|
|
}
|
|
}
|