public override void Log(LoggingEntery LE)
{
IniFile Ini = new IniFile(Path.Combine(Environment.CurrentDirectory, "GoogleDocs.ini"));
// nir start
////////////////////////////////////////////////////////////////////////////
// STEP 1: Configure how to perform OAuth 2.0
////////////////////////////////////////////////////////////////////////////
// TODO: Update the following information with that obtained from
// https://code.google.com/apis/console. After registering
// your application, these will be provided for you.
//string CLIENT_ID = "339569043085-6k0io9kdubi7a3g3jes4m76t614fkccr.apps.googleusercontent.com";
// This is the OAuth 2.0 Client Secret retrieved
// above. Be sure to store this value securely. Leaking this
// value would enable others to act on behalf of your application!
//string CLIENT_SECRET = "wWC4Wcb12RbQg4YuGWJtkh4j";
// Space separated list of scopes for which to request access.
//string SCOPE = "https://spreadsheets.google.com/feeds https://docs.google.com/feeds";
// This is the Redirect URI for installed applications.
// If you are building a web application, you have to set your
// Redirect URI at https://code.google.com/apis/console.
//tring REDIRECT_URI = "urn:ietf:wg:oauth:2.0:oob";
////////////////////////////////////////////////////////////////////////////
// STEP 2: Set up the OAuth 2.0 object
////////////////////////////////////////////////////////////////////////////
// OAuth2Parameters holds all the parameters related to OAuth 2.0.
OAuth2Parameters parameters = new OAuth2Parameters();
// Set your OAuth 2.0 Client Id (which you can register at
// https://code.google.com/apis/console).
parameters.ClientId = Ini.IniReadValue(ConnectSection,"ClientID");
// Set your OAuth 2.0 Client Secret, which can be obtained at
// https://code.google.com/apis/console.
parameters.ClientSecret = Ini.IniReadValue(ConnectSection,"ClientSecret");
// Set your Redirect URI, which can be registered at
// https://code.google.com/apis/console.
parameters.RedirectUri = Ini.IniReadValue(ConnectSection,"RedirectURI");
// Set your refresh token
parameters.RefreshToken = Ini.IniReadValue(ConnectSection,"RefreshToken");
parameters.AccessToken = Ini.IniReadValue(ConnectSection,"LastAccessToken");
// Set the scope for this particular service.
parameters.Scope = Ini.IniReadValue(ConnectSection,"Scope");
// Get the authorization url. The user of your application must visit
// this url in order to authorize with Google. If you are building a
// browser-based application, you can redirect the user to the authorization
// url.
//string authorizationUrl = OAuthUtil.CreateOAuth2AuthorizationUrl(parameters);
//Console.WriteLine(authorizationUrl);
//Console.WriteLine("Please visit the URL above to authorize your OAuth "
// + "request token. Once that is complete, type in your access code to "
// + "continue...");
////////////////////////////////////////////////////////////////////////////
// STEP 4: Get the Access Token
////////////////////////////////////////////////////////////////////////////
// Once the user authorizes with Google, the request token can be exchanged
// for a long-lived access token. If you are building a browser-based
// application, you should parse the incoming request token from the url and
// set it in OAuthParameters before calling GetAccessToken().
OAuthUtil.RefreshAccessToken(parameters);//parameters.AccessToken;
Ini.IniWriteValue(ConnectSection,"LastAccessToken",parameters.AccessToken);
// Console.WriteLine("OAuth Access Token: " + accessToken);
////////////////////////////////////////////////////////////////////////////
// STEP 5: Make an OAuth authorized request to Google
////////////////////////////////////////////////////////////////////////////
// Initialize the variables needed to make the request
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "OctoTipPlus", parameters);
SpreadsheetsService myService = new SpreadsheetsService("OctoTipPlus");
myService.RequestFactory = requestFactory;
// nir end
string User = Ini.IniReadValue("UserLogin","User");
string Password = Ini.IniReadValue("UserLogin","Password");
// SpreadsheetsService myService = new SpreadsheetsService("MySpreadsheetIntegration-v1");
//myService.setUserCredentials(User,Password);
SpreadsheetQuery Squery = new SpreadsheetQuery();
string Sender = LE.Sender;
Squery.Title = Sender;
Squery.Exact = true;
SpreadsheetFeed Sfeed;
try
{
Sfeed = myService.Query(Squery);
}
catch (Google.GData.Client.InvalidCredentialsException e)
{
throw(new Exception(string.Format("Credentials error in google acount for user:{0}",User),e));
}
if(Sfeed.Entries.Count == 0)
{
//DriveService service1 = new DriveService();
//service.SetAuthenticationToken(parameters.AccessToken);//.setUserCredentials(User,Password);
//Google.GData.Client.GOAuth2RequestFactory requestf = new Google.GData.Client.GOAuth2RequestFactory(null, "OctoTipPlus",parameters);
OAuthUtil.RefreshAccessToken(parameters);//parameters.AccessToken;
Ini.IniWriteValue(ConnectSection,"LastAccessToken",parameters.AccessToken);
Google.GData.Documents.DocumentsService service = new Google.GData.Documents.DocumentsService("OctoTipPlus");
GOAuth2RequestFactory requestFactory2 =
new GOAuth2RequestFactory(null, "OctoTipPlus", parameters);
service.RequestFactory = requestFactory2;
//service.RequestFactory=requestf;
// Instantiate a DocumentEntry object to be inserted.
Google.GData.Documents.DocumentEntry entry = new Google.GData.Documents.DocumentEntry();
// Set the document title
entry.Title.Text = LE.Sender;
// Add the document category
entry.Categories.Add(Google.GData.Documents.DocumentEntry.SPREADSHEET_CATEGORY);
// Make a request to the API and create the document.
Google.GData.Documents.DocumentEntry newEntry = service.Insert(
Google.GData.Documents.DocumentsListQuery.documentsBaseUri, entry);
Squery = new SpreadsheetQuery();
Squery.Title = Sender;
Squery.Exact = true;
Sfeed = myService.Query(Squery);
}
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)Sfeed.Entries[0];
WorksheetEntry ProtocolWorksheetEntry=null;
AtomLink link = spreadsheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery Wquery = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed Wfeed = myService.Query(Wquery);
foreach (WorksheetEntry worksheet in Wfeed.Entries)
{
if (worksheet.Title.Text==LE.SubSender)
{
ProtocolWorksheetEntry = worksheet;
}
}
if (ProtocolWorksheetEntry==null)
{
// cteate new worksheet
WorksheetEntry worksheet = new WorksheetEntry();
worksheet.Title.Text = LE.SubSender;
worksheet.Cols = 3;
worksheet.Rows = 5;
// Send the local representation of the worksheet to the API for
// creation. The URL to use here is the worksheet feed URL of our
// spreadsheet.
WorksheetFeed wsFeed = spreadsheet.Worksheets;
ProtocolWorksheetEntry = myService.Insert(wsFeed, worksheet);
CellFeed cellFeed= ProtocolWorksheetEntry.QueryCellFeed();
CellEntry cellEntry= new CellEntry (1, 1,DateHeader);
cellFeed.Insert(cellEntry);
cellEntry= new CellEntry (1, 2, MessageHeader);
cellFeed.Insert(cellEntry);
}
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = ProtocolWorksheetEntry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = myService.Query(listQuery);
string Message = string.Format("{0}\n{1}",LE.Title,LE.Message);
// Create a local representation of the new row.
ListEntry row = new ListEntry();
row.Elements.Add(new ListEntry.Custom() { LocalName = DateHeader, Value = DateTime.Now.ToString() });
row.Elements.Add(new ListEntry.Custom() { LocalName = MessageHeader, Value = Message });
// Send the new row to the API for insertion.
myService.Insert(listFeed, row);
}
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 static int[] getColumn(WorksheetEntry ws, char col, int startrange, int stoprange)
{
CellFeed cf = Query(new CellQuery(ws.CellFeedLink));
var res = new int[stoprange-startrange+1];
for (int i = startrange, ind = 0; i <= stoprange; i++, ind++)
{
var cell = cf.Entries.SingleOrDefault(s => s.Title.Text == col + "" + i) as CellEntry;
if (cell != null)
{
try
{
res[ind] = Int32.Parse(cell.Value);
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
Debug.WriteLine("i="+i);
res[ind] = 0;
}
}
else
{
res[ind] = 0;
}
}
return res;
}
public ExcelTable(int day,ExcelDoc doc, WorksheetEntry entry,SpreadsheetsService service)
{
_entry = entry;
_doc = doc;
DayOfWeek = day;
_service = service;
}
private static ListEntry InsertRow(SpreadsheetsService service, WorksheetEntry entry, NameValueCollection parameters)
{
logger.Debug("inserting row...");
AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
ListFeed feed = service.Query(query);
ListEntry newRow = new ListEntry();
foreach(string key in parameters)
{
ListEntry.Custom curElement = new ListEntry.Custom();
curElement.Value = parameters[key];
curElement.LocalName = key;
newRow.Elements.Add(curElement);
}
// add datetime
ListEntry.Custom el = new ListEntry.Custom();
el.Value = parameters["data"];
el.LocalName = DateTime.Now.ToString() ;
newRow.Elements.Add(el);
ListEntry insertedRow = feed.Insert(newRow);
return insertedRow;
}
/// <summary>
/// Retrieves and prints a list feed of the specified worksheet.
/// </summary>
/// <param name="service">an authenticated SpreadsheetsService object</param>
/// <param name="entry">the worksheet to retrieve</param>
/// <param name="reverseRows">true if the rows in the worksheet should
/// be reversed when returned from the server</param>
private static void RetrieveListFeed(SpreadsheetsService service, WorksheetEntry entry,
bool reverseRows)
{
AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
Console.WriteLine();
Console.WriteLine("This worksheet's list feed URL is:");
Console.WriteLine(listFeedLink.HRef);
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
if (reverseRows)
{
query.OrderByPosition = true;
query.Reverse = true;
}
ListFeed feed = service.Query(query);
Console.WriteLine();
Console.WriteLine("Worksheet has {0} rows:", feed.Entries.Count);
foreach (ListEntry worksheetRow in feed.Entries)
{
ListEntry.CustomElementCollection elements = worksheetRow.Elements;
foreach (ListEntry.Custom element in elements) {
Console.Write(element.Value + "\t");
}
Console.WriteLine();
}
}
public IDictionary<string, double> GetAvailabilityFromNow(WorksheetEntry worksheet, IEnumerable<string> professionals = null)
{
var sectionLines = GetSectionLinesPosition(worksheet, "Disponibilidade de horas por profissional e dia");
var minLine = sectionLines.Select(sl => sl.Value).Min();
var maxLine = sectionLines.Select(sl => sl.Value).Max();
var today = DateTime.Now.ToShortDateString();
var dateLines = _spreadsheetFacade.GetCellsValues(worksheet, minLine - 1, minLine, 1, uint.MaxValue).ToList();
var colNow = dateLines.First(dl => dl.Value.Equals(today)).Column;
var result = new Dictionary<string, double>();
for (var i = minLine; i <= maxLine; i++)
{
var i1 = i;
var professional = sectionLines.Where(sl => sl.Value == i1).Select(sl => sl.Key).First().ToLower();
var enumerableProfessionals = professionals?.ToArray() ?? new string[0];
if (!enumerableProfessionals.Any() || enumerableProfessionals.Any(ep => ep.ToLower().Contains(professional) || professional.Contains(ep.ToLower())))
{
var availability = _spreadsheetFacade.GetCellsValues(worksheet, i, i, colNow, uint.MaxValue).Sum(c => double.Parse(c.Value));
result.Add(professional, availability);
}
}
return result;
}
public ListFeed GetListFeed(WorksheetEntry worksheetEntry)
{
AtomLink listFeedLink = worksheetEntry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = _service.Query(listQuery);
return listFeed;
}
public void CreateHeader(WorksheetEntry worksheet, IList<string> cellHeaders)
{
var cellFeed = GetCellFeed(worksheet);
for (var i = 0; i < cellHeaders.Count; i++)
{
var cellEntry = new CellEntry(1, (uint)(i + 1), cellHeaders[i]);
cellFeed.Insert(cellEntry);
}
}
public static void addWorkSheet2(string _workSheetTitle, int _rows, int _cols)
{
WorksheetFeed wsFeed = spreadSheet.Worksheets;
WorksheetEntry worksheet = new WorksheetEntry();
worksheet = ((WorksheetEntry)wsFeed.Entries[0]);
worksheet.Cols = (uint) _cols;
worksheet.Rows = (uint) _rows;
worksheet.Title.Text = _workSheetTitle;
spreadSheetsService.Insert(wsFeed, worksheet);
}
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 CopyCardsToSpreadsheet(WorksheetEntry worksheet, Dictionary<string, DateTime> sprintPeriod)
{
var cards = ExecutionHelper.ExecuteAndRetryOnFail(() => _trelloFacade.GetCards(_trelloBoardId)).ToList();
var i = 0;
var count = cards.Count();
cards.AsParallel().ForAll(card =>
{
i++;
Console.WriteLine("Inserindo cartão ({0}/{1}): {2}", i, count, card.Name);
var row = MountWorksheetRow(card, sprintPeriod);
//TODO: substituir para inserir em lote
ExecutionHelper.ExecuteAndRetryOnFail(() => _spreadsheetFacade.InsertInWorksheet(worksheet, row));
});
}
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 static void addWorkSheet(string _workSheetTitle, int _rows, int _cols)
{
if (workSheetExists(_workSheetTitle))
{
return;
}
// Create a local representation of the new worksheet.
WorksheetEntry worksheet = new WorksheetEntry();
worksheet.Title.Text = _workSheetTitle;
worksheet.ColCount = new ColCountElement((uint)_cols);
worksheet.RowCount = new RowCountElement((uint)_rows);
// Send the local representation of the worksheet to the API for
// creation. The URL to use here is the worksheet feed URL of our
// spreadsheet.
WorksheetFeed wsFeed = spreadSheet.Worksheets;
spreadSheetsService.Insert(wsFeed, worksheet);
}
public IDictionary<string, string> GetReportFromSection(WorksheetEntry worksheet, string sectionName, string professional = null)
{
var i = 1;
var headers = new List<string>();
var addToReport = false;
var report = new Dictionary<string, string>();
var sprintRunningSection = GetSectionLinesPosition(worksheet, sectionName);
var sectionFirstLine = sprintRunningSection.Min(s => s.Value) - 1;
var sectionLastLine = sprintRunningSection.Max(s => s.Value) + 1;
var sectionHeader = _spreadsheetFacade.GetCellsValues(worksheet, sectionFirstLine, sectionFirstLine, 1, uint.MaxValue);
var sectionColumnLastHeader = sectionHeader.Max(s => s.Column);
var reportCells = _spreadsheetFacade.GetCellsValues(worksheet, sectionFirstLine, sectionLastLine, 1, sectionColumnLastHeader);
foreach (var reportCell in reportCells)
{
if (i < sectionColumnLastHeader)
{
headers.Add(reportCell.Value);
}
else
{
if (i % sectionColumnLastHeader == 0) //nova linha
{
var lowerValue = reportCell.Value.ToLower().Trim();
addToReport = string.IsNullOrWhiteSpace(professional) || lowerValue.Contains(professional) || professional.Contains(lowerValue);
if (addToReport)
report["title"] = reportCell.Value;
}
else if (addToReport) //mesma linha
{
report[headers[(int)((i % sectionColumnLastHeader) - 1)]] = reportCell.Value;
}
}
i++;
}
return report;
}
public Dictionary<string, List<string>> GetRowsFromWorksheet(WorksheetEntry worksheet)
{
var dictionary = new Dictionary<string, List<string>>();
// Define the URL to request the list feed of the worksheet.
var listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
var listQuery = new ListQuery(listFeedLink.HRef.ToString());
var listFeed = _service.Query(listQuery);
foreach (ListEntry row in listFeed.Entries)
{
foreach (ListEntry.Custom element in row.Elements)
{
if (String.IsNullOrEmpty(element.Value))
continue;
var actionStart = element.Value.IndexOf("(", StringComparison.Ordinal);
var actionEnd = element.Value.IndexOf(" ", actionStart, StringComparison.Ordinal);
var secondEnd = element.Value.IndexOf(")", actionEnd, StringComparison.Ordinal);
var action = element.Value.Substring(actionStart + 1, actionEnd - actionStart - 1);
var thing = element.Value.Substring(actionEnd + 1, secondEnd - actionEnd - 1);
if (dictionary.ContainsKey(action))
{
dictionary[action].Add(thing);
}
else
{
var list = new List<string> { thing };
dictionary.Add(action, list);
}
}
}
return dictionary;
}
public void Execute()
{
const string worksheetName = "SprintCorrente";
Console.WriteLine("Recuperando alocações dos profissionais...");
var cards = ExecutionHelper.ExecuteAndRetryOnFail(() => _trelloFacade.GetCards(_trelloBoardId));
//recupera variáveis
_worksheet = ExecutionHelper.ExecuteAndRetryOnFail(() => _spreadsheetFacade.GetWorksheet(_spreadsheetId, worksheetName));
var sprintPeriod = ExecutionHelper.ExecuteAndRetryOnFail(() => _sprintRunningHelper.GetSprintPeriod(_worksheet));
var startDate = sprintPeriod["startDate"];
var endDate = sprintPeriod["endDate"];
var firstColumn = ExecutionHelper.ExecuteAndRetryOnFail(() => _spreadsheetFacade.GetCellsValues(_worksheet, 1, uint.MaxValue, 1, 1)).ToList();
var sprintPlanningPos = _sprintRunningHelper.GetSectionLinesPosition(firstColumn, "Relatório de planejamento do sprint");
var sprintRunningPos = _sprintRunningHelper.GetSectionLinesPosition(firstColumn, "Relatório de andamento do sprint");
var sprintAllocationByLabelsPos = _sprintRunningHelper.GetSectionLinesPosition(firstColumn, "Alocações por rótulo");
//
var resultOfAnalysis = AnalyzeCards(cards, startDate, endDate);
UpdateSpreadsheet(resultOfAnalysis, sprintPlanningPos, sprintRunningPos, sprintAllocationByLabelsPos);
}
public Google2uWorksheet(WorksheetEntry in_entry, Service in_service, WorkbookBase in_base)
{
WorksheetEntry = in_entry;
MyWorkbook = in_base;
WorksheetName = WorksheetEntry.Title.Text;
Prefix = "worksheet" + WorksheetName.Replace(' ', '_');
MyService = in_service;
MyCleanupQuery = new CellQuery(in_entry.CellFeedLink);
MyCellQuery = new CellQuery(in_entry.CellFeedLink) {ReturnEmpty = ReturnEmptyCells.yes};
_ComboBoxList = new GUIContent[Convert.ToInt32(SupportedType.Unrecognized)];
var iter = 0;
foreach (var enumval in Enum.GetValues(typeof (SupportedType)))
{
if ((SupportedType) enumval == SupportedType.Unrecognized)
break;
_ComboBoxList[iter] = new GUIContent(Convert.ToString(enumval));
iter++;
}
}
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);
}
private void ConnectTable()
{
// 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);
if (feed.Entries.Count == 0) {
return;
}
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries [0];
WorksheetFeed wsFeed = spreadsheet.Worksheets;
worksheet = (WorksheetEntry)wsFeed.Entries [0];
}
/// <summary>
/// this will reload the list of spreadsheets from the server and reset the
/// currently active spreadsheet/worksheet
/// </summary>
/// <returns></returns>
public void Refresh()
{
Application.sf = null;
Application.se = null;
Application.we = null;
}
public Dictionary<string, uint> GetSectionLinesPosition(WorksheetEntry worksheet, string sectionTitle)
{
return GetSectionLinesPosition(GetFirstColumn(worksheet), sectionTitle);
}
/// <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>
/// Updates multiple cells in the given worksheet.
/// </summary>
/// <param name="worksheet">The <typeparamref name="Google.GData.Spreadsheets.WorksheetEntry" /> in which the cells should be updated.</param>
/// <param name="cellEntries">A list of cellEntries, which contains updated values.</param>
/// <exception cref="System.ArgumentNullException"><paramref name="worksheet"/> is null.</exception>
/// <exception cref="System.ArgumentOutOfRangeException"><paramref name="cellEntries"/> contains 0 elements.</exception>
/// <exception cref="System.NullReferenceException">this.cellFeed is null - that means GetCells() was never called.</exception>
public virtual void UpdateCells(WorksheetEntry worksheet, IList<CellEntry> cellEntries)
{
// Catch some no-go's.
if (worksheet == null)
{
throw new ArgumentNullException("worksheet");
}
if (cellEntries.Count == 0)
{
throw new ArgumentOutOfRangeException("cellEntries.Count");
}
if (cellEntries.Count == 1)
{
UpdateCell(cellEntries[0], cellEntries[0].InputValue);
return;
}
if (this.cellFeed == null)
{
throw new NullReferenceException();
}
// Set up the batch request.
var batchRequest = new CellFeed(new CellQuery(worksheet.CellFeedLink).Uri, this.service);
foreach (var batchEntry in cellEntries)
{
batchEntry.BatchData = new GDataBatchEntryData(GDataBatchOperationType.update);
batchRequest.Entries.Add(batchEntry);
}
// Submit it.
var batchResponse = (CellFeed) this.service.Batch(batchRequest, new Uri(this.cellFeed.Batch));
// Some debug code
#if DEBUG
foreach (CellEntry entry in batchResponse.Entries)
{
System.Diagnostics.Debug.WriteLineIf(entry.BatchData.Status.Code != 200, entry.BatchData.Id + " failed: " + entry.BatchData.Status.Reason);
}
#endif
}
/// <summary>
/// Get Worksheets in the given <paramref name="spreadsheet"/>.
/// </summary>
/// <param name="spreadsheet">The <typeparamref name="Google.GData.Spreadsheets.SpreadsheetEntry" /> from which the worksheets should be returned.</param>
/// <returns>Returns a <typeparamref name="Google.GData.Spreadsheets.WorksheetEntry" /> array.</returns>
public virtual WorksheetEntry[] GetWorksheets(SpreadsheetEntry spreadsheet)
{
if (spreadsheet == null)
{
spreadsheet = GetSpreadsheets()[0];
}
var result = new WorksheetEntry[spreadsheet.Worksheets.Entries.Count];
spreadsheet.Worksheets.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 uint GetHeaderColumnPosition(WorksheetEntry worksheet, Dictionary<string, uint> sectionLines, string columnName)
{
var rowLine = sectionLines.Min(spp => spp.Value) - 1;
var headerSectionLine = _spreadsheetFacade.GetCellsValues(worksheet, rowLine, rowLine, 1, uint.MaxValue).ToList();
return headerSectionLine.Where(h => h.Value.Equals(columnName)).Select(h => h.Column).First();
}
public uint GetHeaderColumnPosition(WorksheetEntry worksheet, string sectionTitle, string columnName)
{
var sectionLines = GetSectionLinesPosition(worksheet, sectionTitle);
return GetHeaderColumnPosition(worksheet, sectionLines, columnName);
}
public List<CellEntry> GetFirstColumn(WorksheetEntry worksheet)
{
return _spreadsheetFacade.GetCellsValues(worksheet, 1, uint.MaxValue, 1, 1).ToList();
}
public Dictionary<string, DateTime> GetSprintPeriod(WorksheetEntry worksheet)
{
var dateFormat = new CultureInfo("pt-BR", false).DateTimeFormat;//TODO: criar método de extensão para DateTime
var strStartDate = _spreadsheetFacade.GetCellValue(worksheet, 2, 2);
var strEndDate = _spreadsheetFacade.GetCellValue(worksheet, 3, 2);
var startDate = Convert.ToDateTime(strStartDate, dateFormat);
var endDate = Convert.ToDateTime(strEndDate, dateFormat);
return new Dictionary<string, DateTime> { { "startDate", startDate }, { "endDate", endDate } };
}
