// grab your spreadsheet's ID / "key" from the URL to access your doc...
// e.g. everything after "key=" in the URL: https://docs.google.com/spreadsheet/ccc?key=0Ak-N8rbAmu7WdGRFdllybTBIaU1Ic0FxYklIbk1vYlE
// make sure stop as soon as you hit an ampersand, those are additional URL parameters we don't need
public static ListFeed GetSpreadsheet(string spreadsheetID)
{
// We need this fake certificate to trick Mono's security to use HTTPS... doesn't work in webplayer's security sandbox
InsecureSecurityCertificatePolicy.Instate();
SpreadsheetsService service = new SpreadsheetsService( "UnityConnect" );
ListQuery listQuery = new ListQuery( "https://spreadsheets.google.com/feeds/list/" + spreadsheetID + "/default/public/values" );
const bool debugTest = false; // change to TRUE to enable debug output
if ( debugTest ) {
ListFeed listFeed = service.Query( listQuery );
Debug.Log( "loaded Google Doc Spreadsheet: " + listFeed.Title.Text );
// Iterate through each row, printing its cell values.
foreach ( ListEntry row in listFeed.Entries ) {
// Print the first column's cell value
Debug.Log( row.Title.Text );
// Iterate over the remaining columns, and print each cell value
foreach ( ListEntry.Custom element in row.Elements ) {
Debug.Log( element.Value );
}
}
}
return service.Query( listQuery );
}
public SpreadSheet()
{
service = new SpreadsheetsService("stock-market");
service.setUserCredentials("shurai", "$gva99void!");
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
SpreadsheetEntry ssentry = (SpreadsheetEntry)feed.Entries[0];
AtomLink sslink = ssentry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery wkquery = new WorksheetQuery(sslink.HRef.ToString());
WorksheetFeed wkfeed = service.Query(wkquery);
WorksheetEntry wkentry = (WorksheetEntry)wkfeed.Entries[0];
listFeedLink = wkentry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
listQuery = new ListQuery(listFeedLink.HRef.ToString());
listFeed = service.Query(listQuery);
Console.WriteLine("Worksheet has {0} rows: ", listFeed.Entries.Count);
foreach (ListEntry worksheetRow in listFeed.Entries)
{
ListEntry.CustomElementCollection elements = worksheetRow.Elements;
foreach (ListEntry.Custom element in elements)
{
Console.Write(element.Value + "\t");
}
Console.WriteLine();
}
}
public ActionResult AllCells()
{
SpreadsheetsService service;
service = new SpreadsheetsService("DevFestEvent");
service.setUserCredentials(
ConfigurationManager.AppSettings["GoogleUser"],
ConfigurationManager.AppSettings["GoogleUserPassword"]);
SpreadsheetQuery q = new SpreadsheetQuery(App.SheetFeedData);
var feed = service.Query(q);
AtomLink l = feed.Entries.First().Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query = new WorksheetQuery(l.HRef.ToString());
WorksheetFeed f = service.Query(query);
foreach (var item in f.Entries)
{
AtomLink cellFeedLink = item.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
var cellfeedlink = cellFeedLink.HRef.ToString();
CellQuery cquery = new CellQuery(cellfeedlink);
CellFeed cfeed = service.Query(cquery);
Console.WriteLine("Cells in this worksheet:");
uint rownum = 2;
foreach (CellEntry curCell in cfeed.Entries)
{
rownum = curCell.Cell.Row;
}
}
return View(f);
}
public IEnumerable<Word> GetWords()
{
if (Enabled)
{
SpreadsheetsService GoogleService = new SpreadsheetsService("LanguageBooster");
// FIX ME: Remove credentials from code.
GoogleService.setUserCredentials("xxx", "xxx");
SpreadsheetQuery SpreadsheetsQuery = new SpreadsheetQuery();
SpreadsheetFeed Spreadsheets = GoogleService.Query(SpreadsheetsQuery);
// Get list of spreadsheets
foreach (SpreadsheetEntry WordsSheet in Spreadsheets.Entries)
{
System.Diagnostics.Trace.WriteLine(WordsSheet.Title.Text);
System.Diagnostics.Trace.WriteLine(WordsSheet.Title.Text);
// Get list of worksheets from spreadsgett
WorksheetFeed Worksheets = WordsSheet.Worksheets;
WorksheetEntry CurrentWorksheet = null;
foreach (WorksheetEntry Worksheet in Worksheets.Entries)
{
CurrentWorksheet = Worksheet;
break;
}
AtomLink CellsLink = CurrentWorksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery CellsQuery = new Google.GData.Spreadsheets.CellQuery(CellsLink.HRef.ToString());
CellFeed Cells = GoogleService.Query(CellsQuery);
Word word = null;
// Load actual table data
foreach (CellEntry CurrentCell in Cells.Entries)
{
if (CurrentCell.Column == 1)
{
word = new Word();
word.Question = CurrentCell.Value;
}
if (CurrentCell.Column == 2)
{
word.Answer = CurrentCell.Value;
System.Diagnostics.Trace.WriteLine(word.Question + " - " + word.Answer);
yield return word;
}
}
}
}
}
private static ListEntry InsertRow(SpreadsheetsService service, WorksheetEntry entry, NameValueCollection parameters)
{
logger.Debug("inserting row...");
AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
ListFeed feed = service.Query(query);
ListEntry newRow = new ListEntry();
foreach(string key in parameters)
{
ListEntry.Custom curElement = new ListEntry.Custom();
curElement.Value = parameters[key];
curElement.LocalName = key;
newRow.Elements.Add(curElement);
}
// add datetime
ListEntry.Custom el = new ListEntry.Custom();
el.Value = parameters["data"];
el.LocalName = DateTime.Now.ToString() ;
newRow.Elements.Add(el);
ListEntry insertedRow = feed.Insert(newRow);
return insertedRow;
}
/// <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 WorksheetEntry GetWorkSheet(SpreadsheetsService service, string spreadSheetName, string workSheetName)
{
if (service == null) {
return null;
}
SpreadsheetQuery query = new SpreadsheetQuery();
query.Title = spreadSheetName;
query.Exact = true;
//Iterate over the results
var feed = service.Query(query);
if (feed.Entries.Count == 0) {
Debug.LogError ("can't find spreadsheet : " + spreadSheetName);
return null;
}
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
WorksheetFeed wsFeed = spreadsheet.Worksheets;
WorksheetEntry worksheet = null;
for (int i=0; i<wsFeed.Entries.Count; i++) {
if(wsFeed.Entries[i].Title.Text == workSheetName) {
worksheet = wsFeed.Entries[i] as WorksheetEntry;
break;
}
}
if (worksheet == null) {
Debug.LogError("can't find worksheet : " + workSheetName);
}
return worksheet;
}
public string GetSpreadsheets(Hashtable State, RadComboBox Spreadsheets)
{
try
{
SpreadsheetsService service = new SpreadsheetsService(State["SelectedApp"].ToString());
GOAuthRequestFactory requestFactory = new GOAuthRequestFactory("wise", "MobiFlex");
requestFactory.ConsumerKey = ConfigurationManager.AppSettings["GoogleAppsKey"];
requestFactory.ConsumerSecret = ConfigurationManager.AppSettings["GoogleAppsSecret"];
service.RequestFactory = requestFactory;
//get all spreadsheets
Google.GData.Spreadsheets.SpreadsheetQuery query = new Google.GData.Spreadsheets.SpreadsheetQuery();
query.OAuthRequestorId = State["CustomerEmail"].ToString();
query.Uri = new Uri("https://spreadsheets.google.com/feeds/spreadsheets/private/full?xoauth_requestor_id=" + State["CustomerEmail"].ToString());
SpreadsheetFeed feed = service.Query(query);
Spreadsheets.Items.Clear();
Spreadsheets.Items.Add(new RadComboBoxItem("Select Spreadsheet ->", "->"));
foreach (SpreadsheetEntry entry in feed.Entries)
{
string spreadsheet_name = entry.Title.Text;
Spreadsheets.Items.Add(new RadComboBoxItem(spreadsheet_name, spreadsheet_name));
}
return "OK";
}
catch (Exception ex)
{
Util util = new Util();
util.LogError(State, ex);
return ex.Message;
}
}
private static void DoWork()
{
service = new SpreadsheetsService("Comwell");
service.RequestFactory = GApi.RequestFactory;
// Make the request to Google
// See other portions of this guide for code to put here...
Console.Write("Retrieving data... ");
ListQuery query = new ListQuery(GApi.SpreadsheetSubmissions, "1", "private", "values");
ListFeed feed = service.Query(query);
Console.WriteLine("complete.");
Console.Write("Processing data... ");
Submission.ProcessSubmissions(feed);
Console.WriteLine("complete.");
Console.ReadLine();
Console.WriteLine("There are " + Submission.Submissions.Count + " submissions data retrieved.");
foreach (Submission sub in Submission.Submissions)
{
Console.WriteLine(sub.ToString());
}
Console.ReadLine();
}
private void button1_Click(object sender, EventArgs e)
{
//////////////////////////////////////////////////////////////////////////////
//// STEP 5: Make an OAuth authorized request to Google
//////////////////////////////////////////////////////////////////////////////
// Initialize the variables needed to make the request
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "TourTracking", parameters);
SpreadsheetsService service = new SpreadsheetsService("TourTracking");
service.RequestFactory = requestFactory;
// Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
SpreadsheetQuery query = new SpreadsheetQuery();
// Make a request to the API and get all spreadsheets.
SpreadsheetFeed feed = service.Query(query);
// Iterate through all of the spreadsheets returned
foreach (SpreadsheetEntry entry in feed.Entries)
{
// Print the title of this spreadsheet to the screen
MessageBox.Show(entry.Title.Text);
}
}
public static Worksheet Read(WorksheetEntry entry, SpreadsheetsService service)
{
Worksheet sheet = new Worksheet(entry.Title.Text, entry.Rows, entry.Cols);
CellQuery cq = new CellQuery(entry.CellFeedLink);
CellFeed feed = service.Query(cq);
foreach (CellEntry cellentry in feed.Entries)
{
Cell cell = new Cell();
double output;
if (Double.TryParse(cellentry.Cell.Value, out output))
{
cell.Type = DataType.Number;
cell.Value = output;
}
else
{
cell.Type = DataType.String;
cell.Value = cellentry.Cell.Value;
}
sheet[cellentry.Cell.Row - 1, cellentry.Cell.Column - 1] = cell;
}
return sheet;
}
public GDataAPI(NameValueCollection parameters)
{
try
{
SpreadsheetsService service = new SpreadsheetsService("post2spreadsheet");
service.setUserCredentials(cardeira.Properties.Settings.Default.gUserName, cardeira.Properties.Settings.Default.gPassword);
Google.GData.Spreadsheets.SpreadsheetQuery query = new Google.GData.Spreadsheets.SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
SpreadsheetEntry entry = null;
foreach (SpreadsheetEntry e in feed.Entries)
{
entry = e;
logger.Debug("Spreadsheet: {0}", entry.Title.Text);
if (entry.Title.Text == cardeira.Properties.Settings.Default.spreadsheetkey)
break;
}
if (entry != null)
InsertRow(service, (WorksheetEntry)entry.Worksheets.Entries[0], parameters);
}
catch (Exception e)
{
logger.ErrorException("error writing to spreadsheet", e);
}
}
public static AtomEntryCollection GetSheetNames(SpreadsheetsService zSpreadsheetService, AtomEntry zSheetEntry)
{
var link = zSheetEntry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
var wsquery = new WorksheetQuery(link.HRef.ToString());
var wsfeed = zSpreadsheetService.Query(wsquery);
return wsfeed.Entries;
}
/// <summary>
/// New Method To get Worksheet
/// </summary>
/// <returns></returns>
public WorksheetEntry GetWorksheet(OAuth2Parameters parameters, string IntegrationName, string SpreadSheetURI, SpreadsheetsService service)
{
SpreadsheetQuery query = new SpreadsheetQuery(SpreadSheetURI);
SpreadsheetFeed feed = service.Query(query);
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
WorksheetFeed wsFeed = spreadsheet.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];
return worksheet;
}
private CellFeed GetCellFeed(SpreadsheetsService service, SpreadsheetEntry spEntry)
{
WorksheetFeed wsFeed = spEntry.Worksheets;
WorksheetEntry wsEntry = (WorksheetEntry)wsFeed.Entries[0];
AtomLink wLink = wsEntry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery cellQuery = new CellQuery(wLink.HRef.ToString());
CellFeed cellFeed = service.Query(cellQuery);
return cellFeed;
}
/// <summary>
/// Prints a list of all the user's spreadsheets, and the
/// list of worksheets that each spreadsheet contains.
/// </summary>
/// <param name="service">an authenticated SpreadsheetsService object</param>
private static void PrintAllSpreadsheetsAndWorksheets(SpreadsheetsService service)
{
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
Console.WriteLine("Your spreadsheets:");
foreach (SpreadsheetEntry entry in feed.Entries)
{
Console.WriteLine("Spreadsheet: {0}", entry.Title.Text);
PrintAllWorksheets(service, entry);
}
}
//
// GET: /Spreadsheet/
public ActionResult Index()
{
SpreadsheetsService service;
service = new SpreadsheetsService("Spreadsheet-GData-Sample-App");
service.setUserCredentials(
ConfigurationManager.AppSettings["GoogleUser"],
ConfigurationManager.AppSettings["GoogleUserPassword"]);
SpreadsheetQuery query = new SpreadsheetQuery();
var feed = service.Query(query);
return View(feed);
}
/// <summary>
/// Prints a list of all worksheets in the specified spreadsheet.
/// </summary>
/// <param name="service">an authenticated SpreadsheetsService object</param>
/// <param name="entry">the spreadsheet whose worksheets are to be retrieved</param>
private static void PrintAllWorksheets(SpreadsheetsService service,
SpreadsheetEntry entry)
{
AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feed = service.Query(query);
Console.WriteLine("Worksheets in " + entry.Title.Text + ":");
foreach (WorksheetEntry worksheet in feed.Entries)
{
allWorksheets.Add(worksheet);
Console.WriteLine(" " + worksheet.Title.Text);
}
Console.WriteLine();
}
public Dictionary<string, Tuple<string, string, string, bool>> SheetDownload(string IntegrationName, OAuth2Parameters paramaters, string SpreadSheetURI)
{
GOAuth2RequestFactory requestFactory = new GOAuth2RequestFactory(null, IntegrationName, paramaters);
SpreadsheetsService service = new SpreadsheetsService(IntegrationName);
string accessToken = paramaters.AccessToken;
service.RequestFactory = requestFactory;
WorksheetEntry worksheet = this.GetWorksheet(paramaters, IntegrationName, SpreadSheetURI, service);
Dictionary<string, Tuple<string, string, string, bool>> OnlineMapList = new Dictionary<string, Tuple<string, string, string, bool>>();
string map = "MapNameError";
string URL = "URL ERROR";
string UserSteamID = "0";
string Note = "No Notes";
bool MapUploadStatus = false;
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
foreach (ListEntry row in listFeed.Entries)
{
map = row.Elements[1].Value;
URL = row.Elements[2].Value;
UserSteamID = row.Elements[3].Value;
Note = row.Elements[4].Value;
if (row.Elements[5].Value == "TRUE")
{
MapUploadStatus = true;
}
else
{
MapUploadStatus = false;
}
if (!OnlineMapList.ContainsKey(map))
{
OnlineMapList.Add(map, new Tuple<string, string, string, bool>(URL, UserSteamID, Note, MapUploadStatus));
}
}
return OnlineMapList;
}
//////////////////////////////////////////////////////////////////////
/// <summary>runs an authentication test</summary>
//////////////////////////////////////////////////////////////////////
[Test] public void GoogleAuthenticationTest()
{
Tracing.TraceMsg("Entering Spreadsheet AuthenticationTest");
SpreadsheetQuery query = new SpreadsheetQuery();
Service service = new SpreadsheetsService(this.ApplicationName);
if (this.userName != null)
{
service.Credentials = new GDataCredentials(this.userName, this.passWord);
}
service.RequestFactory = this.factory;
SpreadsheetFeed feed = service.Query(query) as SpreadsheetFeed;
ObjectModelHelper.DumpAtomObject(feed,CreateDumpFileName("AuthenticationTest"));
service.Credentials = null;
}
private SpreadsheetEntry GetSpreadSheetEntry(SpreadsheetsService service)
{
SpreadsheetQuery spquery = new SpreadsheetQuery();
SpreadsheetFeed spfeed = service.Query(spquery);
SpreadsheetEntry spEntry = null;
foreach (SpreadsheetEntry entry in spfeed.Entries)
{
string fileName = ConfigurationManager.AppSettings["fileNameGoogle"].ToString();
if (entry.Title.Text.Contains(fileName))
{
spEntry = entry;
break;
}
}
return spEntry;
}
//finds a spreadsheet by name, or null if not found.
public static SpreadsheetEntry findSpreadsheetByName(string sheetName, SpreadsheetsService service)
{
SpreadsheetQuery query = new SpreadsheetQuery();
// Make a request to the API and get all spreadsheets.
SpreadsheetFeed feed = service.Query(query);
foreach (SpreadsheetEntry entry in feed.Entries)
{
// return matching spreadsheet
if (entry.Title.Text.Equals(sheetName))
{
return entry;
}
}
return null;
}
public void LogIn(string login, string password)
{
SpreadsheetsService service = new SpreadsheetsService(applicationName);
service.setUserCredentials(login, password);
// Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
SpreadsheetQuery query = new SpreadsheetQuery();
query.Title = applicationName;
try
{
SpreadsheetFeed feed = service.Query(query);
internalCredentials.LogIn(login, password);
}
catch (Exception e)
{
throw new CredentialsException(e.Message);
}
}
public ContentResult CSS()
{
SpreadsheetsService service;
service = new SpreadsheetsService("DevFestEvent");
service.setUserCredentials(
ConfigurationManager.AppSettings["GoogleUser"],
ConfigurationManager.AppSettings["GoogleUserPassword"]);
var cellfeedlink = App.SheetFeedCSS;
CellQuery cquery = new CellQuery(cellfeedlink);
CellFeed cfeed = service.Query(cquery);
string ans = "";
foreach (CellEntry curCell in cfeed.Entries)
{
ans += curCell.Cell.Value;
}
return Content(ans, "text/css");
}
public DictionaryTable Download(string sheetName)
{
SpreadsheetsService service = new SpreadsheetsService(applicationName);
service.setUserCredentials(userName, password);
WorksheetEntry worksheet = this.GetWorksheetEntrees(service).FirstOrDefault(e => e.Title.Text == sheetName);
if (worksheet == null)
return null;
CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
cellQuery.MaximumColumn = 2;
CellFeed cellFeed = service.Query(cellQuery);
List<DictionaryItem> retval = new List<DictionaryItem>();
for (int i = 0; i < cellFeed.Entries.Count; i = i + 2)
{
retval.Add(
new DictionaryItem(new string[] { ((CellEntry)cellFeed.Entries[i]).Value, ((CellEntry)cellFeed.Entries[i + 1]).Value }));
}
return new DictionaryTable(sheetName, retval.ToArray());
}
//Fetch all Spreadsheets
public static ArrayList getSpreadsheetList(string userName, string passWord)
{
ArrayList spreadsheetList = new ArrayList();
SpreadsheetsService service = new SpreadsheetsService("GetSpreadsheetsService");
//You could set it up from DB or config file also. This is not a reccomended way. Just an easy way to show fetching
service.setUserCredentials(userName, passWord);
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
foreach (SpreadsheetEntry entry in feed.Entries)
{
spreadsheetList.Add(entry.Title.Text);
}
return spreadsheetList;
}
public void InsertWorksheetTest()
{
Tracing.TraceMsg("Entering InsertWorksheetTest");
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetsService service = new SpreadsheetsService(this.ApplicationName);
if (this.userName != null)
{
service.Credentials = new GDataCredentials(this.userName, this.passWord);
}
service.RequestFactory = this.factory;
SpreadsheetFeed feed = service.Query(query);
Assert.IsTrue(feed != null, "Need to have a spreadsheet feed");
Assert.IsTrue(feed.Entries != null && feed.Entries.Count > 0, "Need to have one spreadsheet in there");
SpreadsheetEntry entry = feed.Entries[0] as SpreadsheetEntry;
string wUri = entry.WorksheetsLink;
Assert.IsTrue(wUri != null, "Need to have a worksheet feed in the spreadsheet entry");
WorksheetFeed sheets = entry.Worksheets;
// kill all but the default before we start
foreach (WorksheetEntry w in sheets.Entries )
{
if (w.Title.Text != "DefaultSheet")
{
w.Delete();
}
}
WorksheetEntry defEntry = sheets.Entries[0] as WorksheetEntry;
Assert.IsTrue(defEntry!= null, "There should be one default entry in this account/sheet");
Assert.IsTrue(defEntry.Title.Text == "DefaultSheet", "it should be the default sheet");
CellFeed defCells = defEntry.QueryCellFeed();
Assert.IsTrue(defCells != null, "There should be a cell feed for the worksheet");
CellEntry header=null;
foreach (CellEntry cell in defCells.Entries )
{
if (cell.Title.Text == "A1")
{
cell.Cell.InputValue = string.Empty;
cell.Update();
header = cell;
}
}
if (header != null)
{
header.InputValue = "HeaderA";
header.Update();
}
WorksheetEntry newEntry = sheets.Insert(new WorksheetEntry(10, 20, "New Worksheet"));
Assert.IsTrue(newEntry.ColCount.Count == 20, "Column count should be equal 20");
Assert.IsTrue(newEntry.RowCount.Count == 10, "Row count should be equal 10");
Assert.IsTrue(newEntry.Title.Text == "New Worksheet", "Titles should be identical");
CellFeed cells = newEntry.QueryCellFeed(ReturnEmptyCells.yes);
Assert.IsTrue(cells != null, "There should be a cell feed for the new worksheet");
Assert.IsTrue(cells.Entries.Count == 200, "There should be 200 cells");
for (uint row = 1; row <= 10; row++)
{
for (uint col=1; col <= 20; col++)
{
cells[row,col].InputValue = "R"+row+"C"+col;
}
}
cells.Publish();
// try to update just one cell
cells[1,1].InputValue = string.Empty;
cells[1,1].Update();
for (uint row = 1; row <= 10; row++)
{
for (uint col=1; col <= 20; col++)
{
cells[row,col].InputValue = string.Empty;
}
}
cells.Publish();
// cleanup the new worksheet at the end
newEntry.Delete();
}
private static void LoadSettings(
SpreadsheetsService sheetService,
WorksheetEntry wsSettings,
MUs MUList,
List<string> blackList)
{
ConsoleLog.WriteLine("Loading settings...");
#region Query cells
AtomLink cellLink = wsSettings.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery query = new CellQuery(cellLink.HRef.ToString());
query.ReturnEmpty = ReturnEmptyCells.yes;
query.MaximumColumn = 4;
CellFeed feed = sheetService.Query(query);
List<ItemStorage> lines = new List<ItemStorage>();
#endregion
#region Load cells
lines.Clear();
foreach (CellEntry curCell in feed.Entries)
{
if (curCell.Cell.Row < 2) continue;
if (curCell.Cell.Column == 1) // fomMU
{
MUList.formMUs.Add(curCell.Cell.Value);
}
if (curCell.Cell.Column == 2) // erepMU
{
MUList.erepMUs.Add(curCell.Cell.Value);
}
if (curCell.Cell.Column == 4) // BlackList
{
blackList.Add(curCell.Cell.Value);
}
}
ConsoleLog.WriteLine("Setting items loaded: " + lines.Count);
#endregion
ConsoleLog.WriteLine("Loaded MUs: ");
foreach (var mu in MUList.formMUs)
{
if (!String.IsNullOrEmpty(mu))
ConsoleLog.WriteLine(mu + "=" + MUList[mu]);
}
ConsoleLog.WriteLine("Loaded blacklist: ");
foreach (var mu in blackList)
{
if (!String.IsNullOrEmpty(mu))
ConsoleLog.WriteLine(mu);
}
}
public ActionResult MySpreadsheets()
{
SpreadsheetsService service = new SpreadsheetsService("chavp-mybook-1");
service.setUserCredentials("iuityuj@rtrtey5e6yrty", "sdfdsfsdfsdf");
WorksheetQuery query = new WorksheetQuery("0AsqxIqqTdYVidExTN0R0THh6XzlBa01pOURWVGRSMGc", "private", "full");
WorksheetFeed feed = service.Query(query);
List<Row> listData = new List<Row>();
foreach (WorksheetEntry worksheet in feed.Entries)
{
AtomLink cellFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, AtomLink.ATOM_TYPE);
CellQuery cellQuery = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed cellFeed = service.Query(cellQuery);
foreach (CellEntry curCell in cellFeed.Entries)
{
Cell cell = new Cell { Row = curCell.Cell.Row, Index = curCell.Cell.Column, Data = curCell.Cell.Value };
var row = listData.Find(r => r.Index == curCell.Cell.Row);
if (row == null)
{
row = new Row { Index = curCell.Cell.Row };
listData.Add(row);
}
row.CellList.Add(cell);
}
}
return View();
}
//Fetch all worksheets for given Spreadsheet
public static ArrayList getWorksheetList(string userName, string passWord, string spreadSheetName)
{
ArrayList worksheetList = new ArrayList();
SpreadsheetsService service = new SpreadsheetsService(spreadSheetName + "Service");
//You could set it up from DB or config file also. This is not a reccomended way. Just an easy way to show fetching
service.setUserCredentials(userName, passWord);
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
foreach (SpreadsheetEntry entry in feed.Entries)
{
if (entry.Title.Text == spreadSheetName)
{
AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery wquery = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed wfeed = service.Query(wquery);
foreach (WorksheetEntry worksheet in wfeed.Entries)
{
worksheetList.Add(worksheet.Title.Text);
}
}
}
return worksheetList;
}