internal CellFeed fetch(string uri) { try { // フィードアドレス設定 FeedQuery singleQuery = new FeedQuery(); singleQuery.Uri = new Uri(uri); AtomFeed feed; SpreadsheetEntry spreadsheet; // フィード取得 feed = service.Query(singleQuery); // 認証エラーはここから if (feed.Entries.Count == 0) { return null; } spreadsheet = (SpreadsheetEntry)feed.Entries[0]; WorksheetFeed wsFeed = spreadsheet.Worksheets; WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0]; // Fetch the cell feed of the worksheet. CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink); cellQuery.MinimumRow = 3; cellQuery.MinimumColumn = 2; cellQuery.MaximumColumn = 4; return service.Query(cellQuery); } catch (InvalidCredentialsException) { // 認証エラー throw; } catch (GDataRequestException) { // Execution of request failed: {address} throw; } }
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 void Parse() { Title = _entry.Title.Text; CellQuery cellQuery = new CellQuery(_entry.CellFeedLink); CellFeed cellFeed = _service.Query(cellQuery); _cellFeed = cellFeed; Dictionary<uint, List<CellEntry>> groupRows = new Dictionary<uint, List<CellEntry>>(); foreach (CellEntry cell in cellFeed.Entries) { if (!groupRows.ContainsKey(cell.Row)) { groupRows[cell.Row] = new List<CellEntry>(); } groupRows[cell.Row].Add(cell); } string category = ""; foreach (KeyValuePair<uint, List<CellEntry>> row in groupRows) { ExcelRow excelRow = new ExcelRow(this,row.Key, row.Value); Rows.Add(excelRow); excelRow.Parse(ref category); } foreach (ExcelRow excelRow in Rows) { if (!string.IsNullOrEmpty(excelRow.OriginalCategory)) { excelRow.Category = ExcelRow.GetNewCategory(excelRow.OriginalCategory, excelRow.Name); } } }
public string GetTotalMoney() { SpreadsheetQuery query = new SpreadsheetQuery(); // Make a request to the API and get all spreadsheets. SpreadsheetFeed feed = _spreadsheetService.Query(query); var _spreadsheet = feed.Entries.FirstOrDefault(s => s.Title.Text == "RTBS 체크카드 사용 내역서"); var spreadsheet = _spreadsheet as SpreadsheetEntry; if (spreadsheet != null) { var worksheet = spreadsheet.Worksheets.Entries.FirstOrDefault() as WorksheetEntry; if (worksheet != null){ CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink); cellQuery.MinimumRow = 2; cellQuery.MinimumColumn = 4; cellQuery.MaximumColumn = 4; CellFeed cellFeed = _spreadsheetService.Query(cellQuery); // Iterate through each cell, printing its value. foreach (CellEntry cell in cellFeed.Entries) { if (cell.InputValue.Contains("=SUM")) { return cell.Value; } } } } return ""; }
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 string GetCellValue(int row, int column) { string cellValue = ""; try { CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink); cellQuery.MinimumRow = (uint)row; cellQuery.MaximumRow = (uint)row; cellQuery.MinimumColumn = (uint)column; cellQuery.MaximumColumn = (uint)column; CellFeed cellFeed = service.Query(cellQuery); foreach (CellEntry cell in cellFeed.Entries) { cellValue = cell.Value.ToString(); } } catch (WebException e) { Debug.LogWarning("GetCellValue WebException: " + e); } catch (Exception e) { Debug.LogWarning("GetCellValue Exception: " + e); } return cellValue; }
public CellFeed GetCellFeed(int sheetNum) { WorksheetFeed wsFeed = spreadSheetEntry.Worksheets; WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[sheetNum]; CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink); CellFeed cellFeed = service.Query(cellQuery); return cellFeed; }
public void CreateScript() { GSheetSettings setting = GSheetUtility.GetSettings (); if (setting == null) return; SpreadsheetsService service = setting.GetService (); WorksheetEntry worksheet = setting.GetWorkSheet (service, manager.SpreadSheetName, manager.WorkSheetName); CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink); CellFeed cellFeed = service.Query(cellQuery); string fieldFormat = setting.FieldTemplate.text; StringBuilder sb = new StringBuilder (); // Iterate through each cell, printing its value. foreach (CellEntry cell in cellFeed.Entries) { if(cell.Row > 1 ) { break; } string fieldType = "string"; if(cell.Value[0] == 'n') { fieldType = "int"; } else if(cell.Value[0] == 'f') { fieldType = "float"; } string fieldScript = fieldFormat.Replace("{FieldName}",cell.Value).Replace("{LowerCaseFieldName}",cell.Value.ToLower()).Replace ("{FieldType}",fieldType); sb.Append (fieldScript); } string dataFormat = setting.DataTemplate.text; string dataScript = dataFormat.Replace ("{WorkSheetName}", manager.WorkSheetName).Replace ("{FieldList}", sb.ToString ()); StringBuilder dataPathSB = new StringBuilder(setting.ScriptPath); if (setting.ScriptPath[setting.ScriptPath.Length - 1] != '/') { dataPathSB.Append("/"); } dataPathSB.Append (manager.WorkSheetName); dataPathSB.Append ("Data.cs"); System.IO.File.WriteAllText (dataPathSB.ToString (), dataScript); string editorFormat = setting.DataEditorTemplate.text; string editorScript = editorFormat.Replace ("{WorkSheetName}", manager.WorkSheetName); StringBuilder editorPathSB = new StringBuilder (setting.EditorScriptPath); if (setting.EditorScriptPath [setting.EditorScriptPath.Length - 1] != '/') { editorPathSB.Append("/"); } editorPathSB.Append (manager.WorkSheetName); editorPathSB.Append ("DataEditor.cs"); System.IO.File.WriteAllText (editorPathSB.ToString (), editorScript); AssetDatabase.Refresh (); }
public static void getDataCellFeed() { WorksheetFeed wsFeed = spreadSheet.Worksheets; WorksheetEntry worksheet = ((WorksheetEntry)wsFeed.Entries[0]); // Fetch the cell feed of the worksheet. CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink); CellFeed cellFeed = spreadSheetsService.Query(cellQuery); // Iterate through each cell, updating its value if necessary. foreach (CellEntry cell in cellFeed.Entries) { // Print the cell's address in A1 notation Console.WriteLine(cell.Title.Text); // Print the cell's address in R1C1 notation Console.WriteLine(cell.Id.Uri.Content.Substring(cell.Id.Uri.Content.LastIndexOf("/") + 1)); // Print the cell's formula or text value Console.WriteLine(cell.InputValue); // Print the cell's calculated value if the cell's value is numeric // Prints empty string if cell's value is not numeric Console.WriteLine(cell.NumericValue); // Print the cell's displayed value (useful if the cell has a formula) Console.WriteLine(cell.Value); } ///////////////////////////////////////////////////////////////////////////////////// // Fetch the cell feed of the worksheet. CellQuery cellQuery2 = new CellQuery(worksheet.CellFeedLink); cellQuery2.MinimumRow = 2; cellQuery2.MinimumColumn = 4; cellQuery2.MaximumColumn = 4; CellFeed cellFeed2 = spreadSheetsService.Query(cellQuery2); Console.WriteLine("/////////////////////////////////////////////////////"); // Iterate through each cell, printing its value. foreach (CellEntry cell in cellFeed2.Entries) { // Print the cell's address in A1 notation Console.WriteLine(cell.Title.Text); // Print the cell's address in R1C1 notation Console.WriteLine(cell.Id.Uri.Content.Substring(cell.Id.Uri.Content.LastIndexOf("/") + 1)); // Print the cell's formula or text value Console.WriteLine(cell.InputValue); } Console.WriteLine("/////////////////////////////////////////////////////"); }
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; }
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 IEnumerable<CellEntry> GetCellsValues(WorksheetEntry worksheet, uint minrow, uint maxrow, uint mincol, uint maxcol) { var cellQuery = new CellQuery(worksheet.CellFeedLink) { MinimumColumn = mincol, MaximumColumn = maxcol, MinimumRow = minrow, MaximumRow = maxrow }; var cellFeed = GetCellFeed(cellQuery); return cellFeed.Entries.OfType<CellEntry>(); }
private void ExportDatablocks(Type datablockType) { var worksheet = (WorksheetEntry) spreadsheet.Worksheets.Entries[0]; IEnumerable<Datablock> datablocks = DatablockManager.Instance.GetDatablocks(datablockType); datablockFields = Datablock.GetFields(datablockType); List<string> headers = datablockFields.Select(f => f.Name).ToList(); headers.Insert(0, "Parent"); headers.Insert(0, "Name"); // Set the worksheet to a single row for our headers worksheet.Cols = (uint) headers.Count; worksheet.Rows = (exportData ? (uint) datablocks.Count() : 0) + 1; worksheet.Update(); if (exportData) { // Fetch the cell feed of the worksheet. var cellQuery = new CellQuery(worksheet.CellFeedLink); cellQuery.ReturnEmpty = ReturnEmptyCells.yes; cellFeed = sheetsAPI.Service.Query(cellQuery); batchRequest = new CellFeed(cellQuery.Uri, sheetsAPI.Service); currentCellIndex = 0; // Set headers for (int index = 0; index < headers.Count; index++) { string cellValue = headers[index]; SetNextCellValue(cellValue); } currentCellIndex = headers.Count; IEnumerable<Datablock> baseDatablocks = datablocks.Where(d => d.Parent == null); // Process the datablocks from the base to their children foreach (Datablock baseDatablock in baseDatablocks) { ProcessChildren(baseDatablock); } sheetsAPI.Service.Batch(batchRequest, new Uri(cellFeed.Batch)); } Debug.Log("Datablocks saved to " + spreadsheet.Title.Text); }
public List<string> GetFirstRow(int sheetNum) { WorksheetFeed wsFeed = spreadSheetEntry.Worksheets; WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[sheetNum]; CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink); cellQuery.MinimumRow = 1; cellQuery.MaximumRow = 1; CellFeed cellFeed = service.Query(cellQuery); List<string> list = new List<string>(); foreach (CellEntry cell in cellFeed.Entries) { list.Add(cell.Value); } return list; }
private static bool RequestUpdateCells(List<ExcelCell> cells) { bool res = true; if (cells.Count > 0) { ExcelTable excelTable = cells[0].GetRow().GetTable(); CellFeed cellFeed = excelTable.GetCellFeed(); CellQuery cellQuery = new CellQuery(excelTable.GetEntry().CellFeedLink); Dictionary<string, CellEntry> cellEntries = CreateEntryCellsMap(ExcelManager.Inst.SpreadsheetsService, cellFeed, cells); CellFeed batchRequest = new CellFeed(cellQuery.Uri, ExcelManager.Inst.SpreadsheetsService); foreach (ExcelCell cell in cells) { CellEntry batchEntry = cellEntries[cell.GetBatchID()]; string inputValue = ""; if (cell.EditTmpValue > 0) { inputValue = cell.EditTmpValue.ToString().Replace(".", ","); } batchEntry.InputValue = inputValue; batchEntry.BatchData = new GDataBatchEntryData(cell.GetBatchID(), GDataBatchOperationType.update); batchRequest.Entries.Add(batchEntry); } // Submit the update CellFeed batchResponse = (CellFeed) ExcelManager.Inst.SpreadsheetsService.Batch(batchRequest, new Uri(cellFeed.Batch)); foreach (CellEntry entry in batchResponse.Entries) { string batchId = entry.BatchData.Id; if (entry.BatchData.Status.Code != 200) { res = false; GDataBatchStatus status = entry.BatchData.Status; Console.WriteLine("{0} failed ({1})", batchId, status.Reason); } else { ExcelCell cell = ExcelRow.GetCellByBatchId(cells, batchId); Debug.Assert(null != cell); cell.Value = cell.EditTmpValue; cell.SetEntry(entry); } } } return res; }
public void PopulateNewWorksheet(OAuth2ClientCredentials clientCredentials, string refreshToken, string spreadsheetTitle, string worksheetTitle, ListEntry.Custom[,] listEntries) { SpreadsheetsService service = GetSpreadsheetService(clientCredentials, refreshToken); SpreadsheetQuery query = new SpreadsheetQuery() { Title = spreadsheetTitle, }; SpreadsheetFeed feed = service.Query(query); if (feed.Entries.Count == 0) throw new SpreadsheetNotFoundException(string.Format("Spreadsheet with title {0} not found", spreadsheetTitle)); WorksheetEntry worksheet = CreateWorksheet(worksheetTitle, (uint)listEntries.GetLength(0), (uint)listEntries.GetLength(1), service, feed); CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink); CellFeed cellFeed = service.Query(cellQuery); for (int i = 0; i < listEntries.GetLength(1); i++) { CellEntry cellEntry = new CellEntry(1, (uint)i + 1, listEntries[0, i].LocalName); service.Insert(cellFeed, cellEntry); } AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null); ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString()); ListFeed listFeed = service.Query(listQuery); for (int i = 1; i < listEntries.GetLength(0); i++) { ListEntry row = new ListEntry(); for (int j = 0; j < listEntries.GetLength(1); j++) { if (listEntries[i, j] != null) { listEntries[i, j].LocalName = listEntries[i, j].LocalName.ToLower().Replace(" ",""); row.Elements.Add(listEntries[i, j]); } } LoggerUtil.LogMessage(string.Format("Adding row for {0}", listEntries[i, 0].Value)); service.Insert(listFeed, row); } }
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 WorksheetEntry CreateWorksheet(SpreadsheetEntry spreadsheet, string name) { var numberOfDays = (uint)DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month); var wsFeed = spreadsheet.Worksheets; var worksheet = new WorksheetEntry(2, numberOfDays) { Title = { Text = name } }; worksheet = _service.Insert(wsFeed, worksheet); //Insert First Line var cellQuery = new CellQuery(worksheet.CellFeedLink); var cellFeed = _service.Query(cellQuery); for (var i = 1; i <= numberOfDays; i++) { cellFeed.Insert(new CellEntry(1, (uint)i, i.ToString(CultureInfo.InvariantCulture))); } return worksheet; }
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()); }
public DictionaryTable Download(string sheetName) { var service = new SpreadsheetsService(applicationName); WorksheetEntry worksheet = GetWorksheetEntres(service).FirstOrDefault(e => e.Title.Text == sheetName) as WorksheetEntry; if (worksheet == null) return null; List<DictionaryItem> retval = new List<DictionaryItem>(); // Fetch the cell feed of the worksheet. CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink); cellQuery.MaximumColumn = 2; CellFeed cellFeed = service.Query(cellQuery); 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()); }
/// <summary> /// Retrieves the cell-based metafeed of the cells within the worksheet. /// </summary> /// <param name="returnEmpty">indicates if a full sheet should be returned</param> /// <returns>The CellsFeed of the cells in this worksheet.</returns> public CellFeed QueryCellFeed(ReturnEmptyCells returnEmpty) { CellQuery query = new CellQuery(this.CellFeedLink); query.ReturnEmpty = returnEmpty; return this.Service.Query(query) as CellFeed; }
public void InsertRecord(WorksheetEntry worksheet, string day, string text) { var cellQuery = new CellQuery(worksheet.CellFeedLink); var cellFeed = _service.Query(cellQuery); uint currentColumn = 0; uint currentRow = 0; foreach (CellEntry c in cellFeed.Entries) { if (currentRow == 0 && c.InputValue == day) { currentColumn = c.Column; currentRow = c.Row; } else if (c.Column == currentColumn) currentRow = c.Row; } if (currentRow >= worksheet.Rows) { worksheet.Rows += 1; worksheet.Update(); } Debug.WriteLine("Inserting on Row:{0}, Column:{1}", currentRow + 1, currentColumn); var cell = new CellEntry(currentRow + 1, currentColumn, text); cellFeed.Insert(cell); }
/// <summary> /// Get cells in the given <paramref name="worksheet" />. /// </summary> /// <param name="worksheet">The <typeparamref name="Google.GData.Spreadsheets.WorksheetEntry" /> from which the wcells should be returned.</param> /// <returns>Returns a <typeparamref name="Google.GData.Spreadsheets.CellEntry" /> array.</returns> public virtual CellEntry[] GetCells(WorksheetEntry worksheet) { if (worksheet == null) { worksheet = GetWorksheets(null)[0]; } var query = new CellQuery(worksheet.CellFeedLink); query.ReturnEmpty = ReturnEmptyCells.yes; this.cellFeed = this.service.Query(query); var result = new CellEntry[this.cellFeed.Entries.Count]; this.cellFeed.Entries.CopyTo(result, 0); return result; }
/// <summary> /// Get cells in the given <paramref name="worksheet" />. /// </summary> /// <param name="query">The <typeparamref name="Google.GData.Spreadsheets.CellQuery" /> which should be executed.</param> /// <returns>Returns a <typeparamref name="Google.GData.Spreadsheets.CellEntry" /> array.</returns> /// <exception cref="System.ArgumentNullException"><paramref name="query"/> is null.</exception> public virtual CellEntry[] GetCells(CellQuery query) { if (query == null) { throw new ArgumentNullException("query"); } this.cellFeed = this.service.Query(query); var result = new CellEntry[this.cellFeed.Entries.Count]; this.cellFeed.Entries.CopyTo(result, 0); return result; }
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 static void GDocSupply(string[] args) { MUs MUList = new MUs(); List<string> blackList = new List<string>(); if (args.Length != 16) { ConsoleLog.WriteLine("Usage: armysupply eLogin ePassword ePin gLogin gPassword document_name sheet_name do_tanks do_food food_q period max_tanks max_health validation_type(none/MU/citizenship) do_init_block"); ConsoleLog.WriteLine("Example: armysupply snab_org 123456 1111 [email protected] 54321 \"Army supply\" \"Sheet1\" true true 5 60 8 300 MU false"); return; } string eLogin = args[1]; string ePassword = args[2]; string ePin = args[3]; string gLogin = args[4]; string gPassword = args[5]; string sDocumentName = args[6]; string sSheetName = args[7]; bool bDoTanks = Convert.ToBoolean(args[8]); bool bDoFood = Convert.ToBoolean(args[9]); int iFoodQ = Convert.ToInt32(args[10]); int iPeriod = Convert.ToInt32(args[11]); int iMaxTanks = Convert.ToInt32(args[12]); int iMaxHP = Convert.ToInt32(args[13]); string sValidation = args[14]; bool bDoInitBlock = Convert.ToBoolean(args[15]); string token = ""; ConsoleLog.WriteLine("Loading document..."); #region Open sheet SpreadsheetsService sheetService = new SpreadsheetsService("EArmy supply"); sheetService.setUserCredentials(gLogin, gPassword); SpreadsheetQuery sheetQuery = new SpreadsheetQuery(); SpreadsheetFeed sheetFeed = sheetService.Query(sheetQuery); SpreadsheetEntry sheetSnab = null; foreach (SpreadsheetEntry entry in sheetFeed.Entries) { if (entry.Title.Text == sDocumentName) { sheetSnab = entry; break; } } if (sheetSnab == null) { ConsoleLog.WriteLine("Document '" + sDocumentName + "' not found"); return; } AtomLink sheetLink = sheetSnab.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null); WorksheetQuery wsQuery = new WorksheetQuery(sheetLink.HRef.ToString()); WorksheetFeed wsFeed = sheetService.Query(wsQuery); WorksheetEntry wsSnab = null; WorksheetEntry wsSettings = null; foreach (WorksheetEntry ws in wsFeed.Entries) { if (ws.Title.Text == sSheetName) { wsSnab = ws; } if (ws.Title.Text == "Settings") { wsSettings = ws; } } if (wsSnab == null) { ConsoleLog.WriteLine("Page '" + sSheetName + "' not found"); return; } if (wsSettings != null) { LoadSettings(sheetService, wsSettings, MUList, blackList); } #endregion #region Login to supplier DonaterBot bt = new DonaterBot( eLogin, eLogin, ePassword, ePin, "Mozilla//4.0 (compatible; MSIE 7.0; Windows NT 6.0)", Globals.BotConfig.AntiGateKey, Globals.BotConfig.precaptchaBufferSize); bt.HttpClient.SetProxy(null, null); bool loggedIn = false; int iTryToConnect = 0; System.Random rnd = new System.Random(); #endregion while (true) { try { ConsoleLog.WriteLine("Loading cells..."); #region Query cells AtomLink cellLink = wsSnab.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null); CellQuery query = new CellQuery(cellLink.HRef.ToString()); query.ReturnEmpty = ReturnEmptyCells.yes; query.MaximumColumn = 13; CellFeed feed = sheetService.Query(query); List<ItemStorage> lines = new List<ItemStorage>(); ItemStorage item = null; #endregion #region Load cells lines.Clear(); foreach (CellEntry curCell in feed.Entries) { if (curCell.Cell.Row < 2) continue; item = lines.Find(l => l.row == curCell.Cell.Row); if (item == null) { item = new ItemStorage(); lines.Add(item); item.row = curCell.Cell.Row; } if (curCell.Cell.Column == 1) // time { item.time = curCell.Cell.Value; } if (curCell.Cell.Column == 2) // login { item.login = curCell.Cell.Value; } if (curCell.Cell.Column == 3) // link to profile { item.personID = curCell.Cell.Value; try { item.personID = item.personID.Substring(item.personID.LastIndexOf('/') + 1); Convert.ToInt64(item.personID); } catch { item.personID = ""; } // Cut ID from link } if (curCell.Cell.Column == 4) // wellness { try { item.wellness = Convert.ToInt32(curCell.Cell.Value); } catch { item.wellness = 0; } } if (curCell.Cell.Column == 5) // needed tanks { try { item.tanks = Convert.ToInt32(curCell.Cell.Value); } catch { item.tanks = 0; } } if (curCell.Cell.Column == 6) // military unit { item.militaryUnit = curCell.Cell.Value; } if (curCell.Cell.Column == 8) // done tanks { item.doneTanksCell = curCell; try { item.doneTanks = Convert.ToInt32(curCell.Cell.Value); } catch { item.wellness = 0; } } if (curCell.Cell.Column == 9) // done food q { try { item.doneFoodQ = Convert.ToInt32(curCell.Cell.Value); } catch { item.doneFoodQ = 0; } item.doneFoodQCell = curCell; } if (curCell.Cell.Column == 10) // done food { item.doneFoodCell = curCell; try { item.doneFood = Convert.ToInt32(curCell.Cell.Value); } catch { item.doneFood = 0; } } if (curCell.Cell.Column == 11) // autocomment cell { item.comment = curCell.Cell.Value; item.commentCell = curCell; } if (curCell.Cell.Column == 12) // tanks limit cell { try { if (!String.IsNullOrEmpty(curCell.Cell.Value)) item.tanksLimit = Convert.ToInt32(curCell.Cell.Value); else item.tanksLimit = -1; } catch { item.tanksLimit = -1; } item.tanksLimitCell = curCell; } if (curCell.Cell.Column == 13) // HP limit cell { try { if (!String.IsNullOrEmpty(curCell.Cell.Value)) item.foodLimit = Convert.ToInt32(curCell.Cell.Value); else item.foodLimit = -1; } catch { item.foodLimit = -1; } item.foodLimitCell = curCell; } } ConsoleLog.WriteLine("Items loaded: " + lines.Count); #endregion #region Parce cells int row = 0; List<ItemStorage> tmpList = new List<ItemStorage>(lines); foreach (ItemStorage testItem in tmpList) { row++; if (string.IsNullOrEmpty(testItem.time)) { lines.Remove(testItem); continue; } if (!string.IsNullOrEmpty(testItem.comment)) { lines.Remove(testItem); continue; } if (string.IsNullOrEmpty(testItem.personID)) { lines.Remove(testItem); testItem.commentCell.Cell.InputValue = "Bad profile"; testItem.commentCell.Update(); continue; } if (blackList.Contains(testItem.login)) { lines.Remove(testItem); testItem.commentCell.Cell.InputValue = "Blacklist"; testItem.commentCell.Update(); continue; } if ((sValidation.ToLower() == "mu") && (String.IsNullOrEmpty(MUList[testItem.militaryUnit]))) { lines.Remove(testItem); testItem.commentCell.Cell.InputValue = "MU not in list"; testItem.commentCell.Update(); continue; } if ((testItem.wellness == 0) && (testItem.tanks == 0)) { lines.Remove(testItem); testItem.commentCell.Cell.InputValue = "Bad wellness/tanks"; testItem.commentCell.Update(); continue; } //if (testItem.tanks > 30) //{ // lines.Remove(testItem); // testItem.commentCell.Cell.InputValue = "Bad tanks"; // testItem.commentCell.Update(); // continue; //} if (tmpList.Find(o => (o.personID == testItem.personID && o.viewedForDuplicate)) != null) { lines.Remove(testItem); testItem.commentCell.Cell.InputValue = "Duplicate record"; testItem.commentCell.Update(); continue; } if (testItem.tanksLimit == -1) { if (bDoInitBlock) { testItem.tanksLimit = 0; } else { testItem.tanksLimit = iMaxTanks; } testItem.tanksLimitCell.Cell.InputValue = testItem.tanksLimit.ToString(); testItem.tanksLimitCell.Update(); } if (testItem.foodLimit == -1) { if (bDoInitBlock) { testItem.foodLimit = 0; } else { testItem.foodLimit = iMaxHP; } testItem.foodLimitCell.Cell.InputValue = testItem.foodLimit.ToString(); testItem.foodLimitCell.Update(); } testItem.viewedForDuplicate = true; ConsoleLog.WriteLine( row + ": " + testItem.time + ";" + testItem.login + ";" + testItem.personID + ";" + testItem.wellness + ";" + testItem.tanks + ";" + testItem.doneTanks + ";" + testItem.doneFoodQ + ";" + testItem.doneFood ); } #endregion #region Log in if (!loggedIn) { iTryToConnect++; if (iTryToConnect > 10) break; ConsoleLog.WriteLine("Trying to login (" + (iTryToConnect).ToString() + ")..."); if (bt.Login()) { ConsoleLog.WriteLine("Logged in!"); iTryToConnect = 0; loggedIn = true; } else { ConsoleLog.WriteLine("Login failed!"); System.Threading.Thread.Sleep(1000); continue; } } #endregion //Process donations foreach (ItemStorage donateItem in lines) { ConsoleLog.WriteLine("Donating to: login="******", id=" + donateItem.personID); #region Calculate quantities int foodQty; if ((bDoFood) && (donateItem.wellness > 0) && (donateItem.doneFoodQ == 0)) { foodQty = Convert.ToInt32(Math.Round((double)((double)Math.Min(donateItem.wellness, donateItem.foodLimit) / (iFoodQ * 2)))); foodQty = foodQty - donateItem.doneFood; } else { foodQty = 0; } int tankQty; if (bDoTanks) { tankQty = Math.Min(donateItem.tanks, donateItem.tanksLimit); tankQty = tankQty - donateItem.doneTanks; } else { tankQty = 0; } if ((foodQty <= 0) && (tankQty <= 0)) { ConsoleLog.WriteLine("Nothing to donate"); continue; } #endregion #region Open pages and validate string srcPage = "http://www.erepublik.com/en/citizen/profile/" + donateItem.personID; bt.CustomRequest(srcPage); if (!bt.GetLastResponse().Contains("career_tab_content")) { ConsoleLog.WriteLine("Profile page loading failed. Try to relogin..."); loggedIn = false; break; } if (bt.GetLastResponse().IndexOf("alt=\"" + donateItem.login.Trim() + "\"", StringComparison.OrdinalIgnoreCase) == -1) { ConsoleLog.WriteLine("Donating login validation failed"); donateItem.commentCell.Cell.InputValue = "Profile != Login"; donateItem.commentCell.Update(); continue; } else { ConsoleLog.WriteLine("Login validated"); } if ((sValidation.ToLower() == "mu") && (bt.GetLastResponse().IndexOf("alt=\"" + MUList[donateItem.militaryUnit.Trim()] + "\"", StringComparison.OrdinalIgnoreCase) == -1)) { ConsoleLog.WriteLine("Donating MU validation failed (" + MUList[donateItem.militaryUnit.Trim()] + ")"); ConsoleLog.WriteLine(bt.GetLastResponse(), "Storage page"); donateItem.commentCell.Cell.InputValue = "Profile != MU"; donateItem.commentCell.Update(); continue; } else { ConsoleLog.WriteLine("MU validated"); } srcPage = "http://economy.erepublik.com/en/citizen/donate/" + donateItem.personID; bt.CustomRequest(srcPage); if (bt.CheckPin(true)) { bt.SubmitPin(); } if (bt.CheckPin(true)) { ConsoleLog.WriteLine("Pin validation failed"); break; } if (!bt.GetLastResponse().Contains("<th colspan=\"4\" valign=\"middle\">Your storage</th>")) { ConsoleLog.WriteLine("Donate page loading failed. Try to relogin..."); loggedIn = false; break; } token = CommonUtils.GetStringBetween( bt.GetLastResponse(), "donate_form[_csrf_token]\" value=\"", "\""); #endregion #region Donate food if (foodQty > 0) { ConsoleLog.WriteLine("Donating food: " + foodQty + " (q" + iFoodQ + ")"); if (bt.DonateItem(foodQty.ToString(), Goods.Food.ToString(), iFoodQ.ToString(), token, srcPage)) //if (true) { ConsoleLog.WriteLine("Donating food success"); donateItem.doneFoodQCell.Cell.InputValue = iFoodQ.ToString(); donateItem.doneFoodQCell.Update(); donateItem.doneFoodCell.Cell.InputValue = (donateItem.doneFood + foodQty).ToString(); donateItem.doneFoodCell.Update(); } else { ConsoleLog.WriteLine("Donating food failed"); //ConsoleLog.WriteLine(bt.GetLastResponse(), "DonateLog.txt"); } ConsoleLog.WriteLine("Wait 5 sec..."); System.Threading.Thread.Sleep(5 * 1000); } else { ConsoleLog.WriteLine("No food needed"); } #endregion #region Donate tanks if (tankQty > 0) { ConsoleLog.WriteLine("Donating tanks: " + tankQty); if (bt.DonateItem(tankQty.ToString(), Goods.Weapon.ToString(), 5.ToString(), token, srcPage)) //if (true) { ConsoleLog.WriteLine("Donating tanks success"); donateItem.doneTanksCell.Cell.InputValue = (donateItem.doneTanks + tankQty).ToString(); donateItem.doneTanksCell.Update(); } else { ConsoleLog.WriteLine("Donating tanks failed"); ConsoleLog.WriteLine(bt.GetLastResponse(), "DonateLog.txt"); } ConsoleLog.WriteLine("Wait 5 sec..."); System.Threading.Thread.Sleep(5 * 1000); } else { ConsoleLog.WriteLine("No tanks needed"); } #endregion } } catch (System.Exception e) { ConsoleLog.WriteLine("Donater error: " + e.Message); ConsoleLog.WriteLine(bt.GetLastResponse(), "Responses.txt"); } ConsoleLog.WriteLine("Waiting for next check"); //break; System.Threading.Thread.Sleep(iPeriod * 1000); } ConsoleLog.WriteLine("It's OK :)"); }
public CellEntry this[uint row, uint column] { get { // let's find the cell foreach (CellEntry entry in this.Entries ) { CellEntry.CellElement cell = entry.Cell; if (cell.Row == row && cell.Column == column) { return entry; } } // if we are here, we need to get the entry from the service string url = CellUri(row, column); CellQuery query = new CellQuery(url); CellFeed feed = this.Service.Query(query) as CellFeed; CellEntry newEntry = feed.Entries[0] as CellEntry; this.Entries.Add(newEntry); // we don't want this one to show up in the batch feed on it's own newEntry.Dirty = false; return newEntry; } }
public string isCanLoginIn() { try { SpreadsheetsService service = new SpreadsheetsService("whatsappshower"); service.setUserCredentials("*****@*****.**", "e4rst6rh"); SpreadsheetQuery query = new SpreadsheetQuery(); SpreadsheetFeed feed = service.Query(query); SpreadsheetEntry whatsAppShowerCerEntry = null; foreach (SpreadsheetEntry entry in feed.Entries) { //Console.WriteLine(entry.Title.Text); if ("whatsAppShowerCer".Equals(entry.Title.Text)) { whatsAppShowerCerEntry = entry; } } if (whatsAppShowerCerEntry == null) { return null; } AtomLink link = whatsAppShowerCerEntry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null); WorksheetQuery worksheetQueryQuery = new WorksheetQuery(link.HRef.ToString()); WorksheetFeed worksheetQueryFeed = service.Query(worksheetQueryQuery); WorksheetEntry whatsAppShowerCerWorksheet = null; foreach (WorksheetEntry worksheet in worksheetQueryFeed.Entries) { if ("1".Equals(worksheet.Title.Text)) { whatsAppShowerCerWorksheet = worksheet; } } if (whatsAppShowerCerWorksheet == null) { return null; } AtomLink cellFeedLink = whatsAppShowerCerWorksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null); CellQuery cellQueryQuery = new CellQuery(cellFeedLink.HRef.ToString()); CellFeed cellQueryFeed = service.Query(cellQueryQuery); bool foundToken = false; foreach (CellEntry curCell in cellQueryFeed.Entries) { if (foundToken) { return curCell.Cell.Value; } if (curCell.Cell.Column == 1) { if (curCell.Cell.Value.Equals(WhatsappProperties.Instance.AppToken)) { foundToken = true; } } } } catch (Exception e) { systemLog.Error(e); } return null; }
protected override void SolveInstance(IGH_DataAccess DA) { //declare input variables to load into AuthToken authToken = new AuthToken(); string sheetName = ""; string worksheet = ""; bool includeBlankCells = false; bool rangeSpecified = false; SpreadsheetRange range = new SpreadsheetRange(); bool rowsColumns = false; bool formulasValues = false; //declare output variables List<string> metaData = new List<string>(); GH_Structure<GH_String> values = new GH_Structure<GH_String>(); GH_Structure<GH_String> addresses = new GH_Structure<GH_String>(); //get data from inputs if (!DA.GetData<AuthToken>("Token", ref authToken)) { return; //exit if no token } if (!authToken.IsValid) { AddRuntimeMessage(GH_RuntimeMessageLevel.Warning, "The provided authentication token appears to be invalid. Try re-running the Google Authenticator component."); return; //exit if invalid token } if (!DA.GetData<string>("Name", ref sheetName)) { return; //exit if no name provided } DA.GetData<string>("Worksheet", ref worksheet); DA.GetData<bool>("Include Blank Cells?", ref includeBlankCells); if (DA.GetData<SpreadsheetRange>("Spreadsheet Range", ref range)) { rangeSpecified = true; } DA.GetData<bool>("Organize by Rows or Columns", ref rowsColumns); DA.GetData<bool>("Read Formulas or Values", ref formulasValues); //set up oAuth parameters OAuth2Parameters parameters = GDriveUtil.GetParameters(authToken); //establish spreadsheetservice SpreadsheetsService service = GDriveUtil.GetSpreadsheetsService(parameters); //get spreadsheet by name SpreadsheetEntry spreadsheet = GDriveUtil.findSpreadsheetByName(sheetName, service); if (spreadsheet == null) { AddRuntimeMessage(GH_RuntimeMessageLevel.Warning, "Specified Spreadsheet not found."); return; } //gather spreadsheet metadata metaData.Add("Spreadsheet Name: " + spreadsheet.Title.Text); metaData.Add("Last Updated: " + spreadsheet.Updated.ToString()); metaData.Add("Worksheets: " + worksheetList(spreadsheet.Worksheets)); //find the specified worksheet, or first one if none specified WorksheetEntry worksheetEntry = null; worksheetEntry = GDriveUtil.findWorksheetByName(worksheet, spreadsheet); if (worksheetEntry == null) { AddRuntimeMessage(GH_RuntimeMessageLevel.Warning, "Specified worksheet not found."); return; } // Fetch the cell feed of the worksheet. CellQuery cellQuery = new CellQuery(worksheetEntry.CellFeedLink); if (rangeSpecified) { if (range.TestValid()) { cellQuery.MinimumColumn = range.startColumn(); cellQuery.MinimumRow = range.startRow(); cellQuery.MaximumColumn = range.endColumn(); cellQuery.MaximumRow = range.endRow(); } else { AddRuntimeMessage(GH_RuntimeMessageLevel.Error, "Invalid Range Specified"); return; } } //passes null values if user wants the blank cells represented, otherwise they are omitted from output. if (includeBlankCells) { cellQuery.ReturnEmpty = ReturnEmptyCells.yes; } //set up cell feed CellFeed cellFeed = service.Query(cellQuery); foreach (CellEntry cell in cellFeed.Entries) //for all the cells in the feed { GH_Path path = new GH_Path(DA.Iteration); //set up data path for data tree uint e = (rowsColumns) ? cell.Row : cell.Column; //decide whether to structure data path by row or column path = path.AppendElement((int)e); string v = (formulasValues) ? cell.InputValue : cell.Value; //decide whether to get the cell formula or the cell value as output values.Append(new GH_String(v), path); //save the value of the cell addresses.Append(new GH_String(cell.Title.Text), path); // save the address of the cell } //set output data DA.SetDataTree(0, values); DA.SetDataTree(1, addresses); DA.SetDataList("Sheet Info", metaData); }
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(); }