/// <summary>
/// Retrieves and prints a list feed of the specified worksheet.
/// </summary>
/// <param name="service">an authenticated SpreadsheetsService object</param>
/// <param name="entry">the worksheet to retrieve</param>
/// <param name="reverseRows">true if the rows in the worksheet should
/// be reversed when returned from the server</param>
private static void RetrieveListFeed(SpreadsheetsService service, WorksheetEntry entry,
bool reverseRows)
{
AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
Console.WriteLine();
Console.WriteLine("This worksheet's list feed URL is:");
Console.WriteLine(listFeedLink.HRef);
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
if (reverseRows)
{
query.OrderByPosition = true;
query.Reverse = true;
}
ListFeed feed = service.Query(query);
Console.WriteLine();
Console.WriteLine("Worksheet has {0} rows:", feed.Entries.Count);
foreach (ListEntry worksheetRow in feed.Entries)
{
ListEntry.CustomElementCollection elements = worksheetRow.Elements;
foreach (ListEntry.Custom element in elements)
{
Console.Write(element.Value + "\t");
}
Console.WriteLine();
}
}
public void getValues(string sheetName, DataSet dataSet)
{
var tableName = sheetName;
if (dataSet.Tables[tableName] == null)
{
tableName = sheetName + "_";
}
if (dataSet.Tables[tableName] == null)
{
throw new ArgumentException("Neither " + sheetName + " nor " + sheetName + "_ tables could be found in the passed dataset!!!");
}
DataColumnCollection columnNames = dataSet.Tables[tableName].Columns;
WorksheetEntry entry = (WorksheetEntry)m_worksheets[sheetName];
if (entry == null)
{
string message = sheetName + " not found in " + m_spreadsheetName;
Trace.WriteLine(message);
throw new ArgumentNullException(message);
}
AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
ListFeed feed = m_service.Query(query);
Trace.WriteLine("Found " + feed.Entries.Count + " rows of data in " + sheetName);
if (feed.Entries.Count > 0)
{
ListEntry worksheetRow = (ListEntry)feed.Entries[0];
ListEntry.CustomElementCollection elements = worksheetRow.Elements;
for (int j = 0; j < feed.Entries.Count; ++j)
{
worksheetRow = (ListEntry)feed.Entries[j];
elements = worksheetRow.Elements;
DataRow workRow = dataSet.Tables[tableName].NewRow();
for (int i = 0; i < elements.Count; ++i)
{
string value = "" + elements[i].Value;
Type type = columnNames[i].DataType;
if (type == System.Type.GetType("System.Double") && string.IsNullOrWhiteSpace(value))
{
value = "0";
}
if (type == System.Type.GetType("System.Int32") && string.IsNullOrWhiteSpace(value))
{
value = "0";
}
workRow[columnNames[i].ColumnName] = value;
}
dataSet.Tables[tableName].Rows.Add(workRow);
}
}
return;
}
/// <summary>
/// Converts the gsx: tags to a comma seperated list for parsing
/// Warning: Not robust for handling formulas
/// </summary>
/// <param name="entry">The list entry</param>
/// <returns>The gsx: tags as a comma separated list</returns>
private String ToCommaSeparatedString(ListEntry entry)
{
if (entry != null)
{
String commaSeparated = "";
ListEntry.CustomElementCollection elements = entry.Elements;
for (int i = 0; i < elements.Count; i++)
{
commaSeparated += elements[i].LocalName + "=" + elements[i].Value + ",";
}
return(commaSeparated.Substring(0, commaSeparated.Length - 1));
}
return("");
}
public NameValueCollection getInfo(Boolean debug = false)
{
NameValueCollection parameters = new NameValueCollection();
WorksheetEntry entry = (WorksheetEntry)worksheets["Info"];
AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
ListFeed feed = service.Query(query);
foreach (ListEntry worksheetRow in feed.Entries)
{
ListEntry.CustomElementCollection elements = worksheetRow.Elements;
parameters.Add(elements[0].Value, elements[1].Value);
}
return(parameters);
}
public DataTable getValuesFromSheet(string sheetName)
{
DataTable table = new DataTable();
WorksheetEntry entry = (WorksheetEntry)m_worksheets[sheetName];
if (entry == null)
{
string message = sheetName + " not found in " + m_spreadsheetName;
Trace.WriteLine(message);
throw new ArgumentNullException(message);
}
AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
ListFeed feed = m_service.Query(query);
Trace.WriteLine("Found " + feed.Entries.Count + " rows of data in " + sheetName);
if (feed.Entries.Count > 0)
{
ListEntry worksheetRow = (ListEntry)feed.Entries[0];
ListEntry.CustomElementCollection elements = worksheetRow.Elements;
for (int i = 0; i < elements.Count; ++i)
{
string value = "" + elements[i].Value;
table.Columns.Add(value, System.Type.GetType("System.String"));
}
for (int j = 1; j < feed.Entries.Count; ++j)
{
worksheetRow = (ListEntry)feed.Entries[j];
elements = worksheetRow.Elements;
DataRow workRow = table.NewRow();
for (int i = 0; i < elements.Count; ++i)
{
string value = "" + elements[i].Value;
workRow[i] = value;
}
table.Rows.Add(workRow);
}
}
table.PrimaryKey = new DataColumn[] { table.Columns[0] };
return(table);
}
private void updateUpdateProgress(int roundNumber, Boolean debug_flag = false)
{
WorksheetEntry entry = (WorksheetEntry)worksheets["Update Progress"];
AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
ListFeed feed = service.Query(query);
foreach (ListEntry worksheetRow in feed.Entries)
{
ListEntry.CustomElementCollection elements = worksheetRow.Elements;
String parameterName = elements[0].Value;
if (parameterName.ToLower() == "Rounds Completed".ToLower())
{
int value = int.Parse(worksheetRow.Elements[1].Value);
if (value > roundNumber - 1)
{
worksheetRow.Elements[1].Value = "" + (roundNumber - 1);
worksheetRow.Update();
if (debug_flag)
{
Console.WriteLine("Changing Rounds Completed to = " + (roundNumber - 1));
}
}
}
else if (parameterName.ToLower() == "Draws Completed".ToLower())
{
int value = int.Parse(worksheetRow.Elements[1].Value);
if (value > roundNumber - 1)
{
worksheetRow.Elements[1].Value = "" + (roundNumber - 1);
worksheetRow.Update();
if (debug_flag)
{
Console.WriteLine("Changing Draws Completed to = " + (roundNumber - 1));
}
}
}
}
}
/// <summary>
/// Executes a structured query against the list feed of
/// the specified worksheet.
/// </summary>
/// <param name="service">an authenticated SpreadsheetsService object</param>
/// <param name="entry">the worksheet to query</param>
/// <param name="queryText">the structured query</param>
private static void StructuredQuery(SpreadsheetsService service, WorksheetEntry entry,
string queryText)
{
AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
query.SpreadsheetQuery = queryText;
ListFeed feed = service.Query(query);
Console.WriteLine();
Console.WriteLine("{0} rows matched your query:", feed.Entries.Count);
foreach (ListEntry worksheetRow in feed.Entries)
{
ListEntry.CustomElementCollection elements = worksheetRow.Elements;
foreach (ListEntry.Custom element in elements)
{
Console.Write(element.Value + "\t");
}
Console.WriteLine();
}
}
/// <summary>
/// Sets the list view on the List tab
/// </summary>
/// <param name="feed">The feed providing the data</param>
void SetListListView(ListFeed feed)
{
this.listListView.Items.Clear();
this.listListView.Columns.Clear();
this.editUriTable.Clear();
this.listListView.Columns.Add("", 80, HorizontalAlignment.Center);
AtomEntryCollection entries = feed.Entries;
for (int i = 0; i < entries.Count; i++)
{
ListEntry entry = entries[i] as ListEntry;
ListViewItem item = new ListViewItem();
item.Text = (i + 1).ToString();
if (entry != null)
{
ListEntry.CustomElementCollection elements = entry.Elements;
for (int j = 0; j < elements.Count; j++)
{
item.SubItems.Add(elements[j].Value);
if (listListView.Columns.Count - 2 < j)
{
listListView.Columns.Add(elements[j].LocalName, 80, HorizontalAlignment.Center);
}
}
listListView.Items.Add(item);
this.editUriTable.Add(item.Text, entry);
}
}
}
static NameValueCollection getTeamNames(String spreadsheetname, Boolean debug_flag = false)
{
NameValueCollection names = new NameValueCollection();
SpreadsheetsService service = new SpreadsheetsService("GetNames-v0.1");
service.setUserCredentials("*****@*****.**", "kibitzer");
SpreadsheetQuery query = new SpreadsheetQuery();
query.Title = spreadsheetname;
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count > 0)
{
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
if (debug_flag)
{
Console.WriteLine("Found spreadsheet with name " + spreadsheet.Title.Text);
}
AtomLink link = spreadsheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery wquery = new WorksheetQuery(link.HRef.ToString());
wquery.Title = "Info";
WorksheetFeed wfeed = service.Query(wquery);
WorksheetEntry namesSheet = null;
WorksheetEntry infoSheet = null;
if (wfeed.Entries.Count > 0)
{
infoSheet = wfeed.Entries[0] as WorksheetEntry;
}
else
{
String message = "Cannot find sheet titled Info!!!";
if (debug_flag)
{
Console.WriteLine(message);
}
throw new ArgumentNullException(message);
}
wquery.Title = "Names";
wfeed = service.Query(wquery);
if (wfeed.Entries.Count > 0)
{
namesSheet = wfeed.Entries[0] as WorksheetEntry;
}
else
{
String message = "Cannot find sheet titled Names!!!";
if (debug_flag)
{
Console.WriteLine(message);
}
throw new ArgumentNullException(message);
}
int numTeams = 0;
AtomLink listFeedLink = infoSheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery iquery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed ifeed = service.Query(iquery) as ListFeed;
foreach (ListEntry worksheetRow in ifeed.Entries)
{
ListEntry.CustomElementCollection elements = worksheetRow.Elements;
String parameterName = elements[0].Value;
if (parameterName.ToLower() == "Number of Teams".ToLower())
{
numTeams = int.Parse(elements[1].Value);
if (debug_flag)
{
Console.WriteLine("Num Teams = " + numTeams);
}
}
}
AtomLink cellFeedLink = namesSheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery cquery = new CellQuery(cellFeedLink.HRef.ToString());
cquery.ReturnEmpty = ReturnEmptyCells.yes;
cquery.MinimumColumn = 1;
cquery.MaximumColumn = 2;
cquery.MinimumRow = 2;
cquery.MaximumRow = (uint)(numTeams + 1);
CellFeed cfeed = service.Query(cquery);
for (int i = 0; i < cfeed.Entries.Count; i += 2)
{
CellEntry nameCell = cfeed.Entries[i + 1] as CellEntry;
CellEntry numberCell = cfeed.Entries[i] as CellEntry;
names.Add(nameCell.Cell.Value, numberCell.Cell.Value);
}
}
else
{
String message = "No Spreadsheet with name " + spreadsheetname + " could be found!!!";
if (debug_flag)
{
Console.WriteLine(message);
}
throw new ArgumentNullException(message);
}
return(names);
}
