/// <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); }