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