C# SpreadsheetsService.Query方法代码示例

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

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

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

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

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

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

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

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

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