private static SpreadsheetsService GetUserCredential()
{
SpreadsheetsService sheetService = null;
try
{
string currentAssembly = System.Reflection.Assembly.GetExecutingAssembly().Location;
string currentDirectory = System.IO.Path.GetDirectoryName(currentAssembly);
string keyFilePath = System.IO.Path.Combine(currentDirectory, "Resources\\" + keyFile);
var certificate = new X509Certificate2(keyFilePath, "notasecret", X509KeyStorageFlags.Exportable);
ServiceAccountCredential credential = new ServiceAccountCredential(new
ServiceAccountCredential.Initializer(serviceAccountEmail)
{
Scopes = new[] { "https://spreadsheets.google.com/feeds/" }
}.FromCertificate(certificate));
credential.RequestAccessTokenAsync(System.Threading.CancellationToken.None).Wait();
var requestFactory = new GDataRequestFactory("My App User Agent");
requestFactory.CustomHeaders.Add(string.Format("Authorization: Bearer {0}", credential.Token.AccessToken));
sheetService = new SpreadsheetsService("HOK Project Replicator");
sheetService.RequestFactory = requestFactory;
}
catch (Exception ex)
{
MessageBox.Show("Failed to get user credential.\n" + ex.Message, "Google Spreadsheet : User Credential", MessageBoxButton.OK, MessageBoxImage.Warning);
}
return(sheetService);
}
private GDataRequestFactory GetGDataRequestFactory(byte[] p12key, string password, string serviceAccountEmail)
{
var requestFactory = new GDataRequestFactory(null);
requestFactory.CustomHeaders.Add("Authorization: Bearer " + GetAccountCredential(p12key, password, serviceAccountEmail).Token.AccessToken);
return(requestFactory);
}
public void GDataRequestFactoryConstructorTest()
{
string userAgent = "TestValue"; // TODO: Initialize to an appropriate value
GDataRequestFactory target = new GDataRequestFactory(userAgent);
Assert.IsNotNull(target);
}
private static GoogleMailSettingsService CreateGoogleMailService()
{
// Get your service account email from Google Developer's Console
// Read more: https://developers.google.com/identity/protocols/OAuth2ServiceAccount
const string SERVICE_ACCT_EMAIL = "<YOUR SERVICE KEY>@developer.gserviceaccount.com";
//Generate your .p12 key in the Google Developer Console and associate it with your project.
var certificate = new X509Certificate2("Key.p12", "notasecret", X509KeyStorageFlags.Exportable);
var serviceAccountCredentialInitializer = new ServiceAccountCredential.Initializer(SERVICE_ACCT_EMAIL)
{
User = "******", // A user with administrator access.
Scopes = new[] { "https://apps-apis.google.com/a/feeds/emailsettings/2.0/" }
}.FromCertificate(certificate);
var credential = new ServiceAccountCredential(serviceAccountCredentialInitializer);
if (!credential.RequestAccessTokenAsync(System.Threading.CancellationToken.None).Result)
{
throw new InvalidOperationException("Access token failed.");
}
var requestFactory = new GDataRequestFactory(null);
requestFactory.CustomHeaders.Add("Authorization: Bearer " + credential.Token.AccessToken);
// Replace the name of your domain and the Google Developer project you created...
GoogleMailSettingsService service = new GoogleMailSettingsService("vuwall.com", "signatures");
service.RequestFactory = requestFactory;
return(service);
}
private void CreateTagProxy()
{
GDataRequestFactory requestFactory = (GDataRequestFactory)this.m_tagService.RequestFactory;
/*IWebProxy iProxy = WebRequest.DefaultWebProxy;
* WebProxy myProxy = new WebProxy(iProxy.GetProxy(this.m_albumQuery.Uri));
* myProxy.Credentials = CredentialCache.DefaultCredentials;
* myProxy.UseDefaultCredentials = true;*/
requestFactory.Proxy = this.m_myProxy;
}
public void UserAgentTest()
{
string userAgent = "TestValue"; // TODO: Initialize to an appropriate value
GDataRequestFactory target = new GDataRequestFactory(userAgent); // TODO: Initialize to an appropriate value
string expected = "TestValue"; string actual;
target.UserAgent = expected;
actual = target.UserAgent;
Assert.AreEqual(expected, actual);
}
public void CustomHeadersTest()
{
string userAgent = "TestValue"; // TODO: Initialize to an appropriate value
GDataRequestFactory target = new GDataRequestFactory(userAgent); // TODO: Initialize to an appropriate value
List <string> actual;
actual = target.CustomHeaders;
Assert.IsNotNull(actual);
Assert.IsTrue(actual.Count == 0);
}
public void ProxyTest()
{
string userAgent = "TestValue"; // TODO: Initialize to an appropriate value
GDataRequestFactory target = new GDataRequestFactory(userAgent); // TODO: Initialize to an appropriate value
IWebProxy expected = new WebProxy();
IWebProxy actual;
target.Proxy = expected;
actual = target.Proxy;
Assert.AreEqual(expected, actual);
}
public void KeepAliveTest()
{
string userAgent = "TestValue"; // TODO: Initialize to an appropriate value
GDataRequestFactory target = new GDataRequestFactory(userAgent); // TODO: Initialize to an appropriate value
bool expected = false; // TODO: Initialize to an appropriate value
bool actual;
target.KeepAlive = expected;
actual = target.KeepAlive;
Assert.AreEqual(expected, actual);
}
public void TimeoutTest()
{
string userAgent = "TestValue"; // TODO: Initialize to an appropriate value
GDataRequestFactory target = new GDataRequestFactory(userAgent); // TODO: Initialize to an appropriate value
int expected = 250000; // TODO: Initialize to an appropriate value
int actual;
target.Timeout = expected;
actual = target.Timeout;
Assert.AreEqual(expected, actual);
}
/// <summary>
/// called to set additonal proxies if required. Overloaded on the document service
/// </summary>
/// <param name="proxy"></param>
/// <returns></returns>
protected override void OnSetOtherProxies(IWebProxy proxy)
{
base.OnSetOtherProxies(proxy);
GDataRequestFactory x = this.spreadsheetsService.RequestFactory as GDataRequestFactory;
if (x != null)
{
x.Proxy = proxy;
}
else
{
throw new ArgumentException("Can not set a proxy on the spreadsheet service");
}
}
private CalendarService GAuthenticate()
{
Uri oCalendarUri = new Uri("http://www.google.com/calendar/feeds/[email protected]/private/full");
//Uri oCalendarUri = new Uri("http://www.google.com/calendar/feeds/[email protected]/private/full");
//Initialize Calendar Service
CalendarService oCalendarService = new CalendarService("CalendarSampleApp");
oCalendarService.setUserCredentials(sGoogleUserName, sGooglePassword);
//Use Proxy
GDataRequestFactory oRequestFactory = (GDataRequestFactory)oCalendarService.RequestFactory;
WebProxy oWebProxy = new WebProxy(WebRequest.DefaultWebProxy.GetProxy(oCalendarUri));
oWebProxy.Credentials = CredentialCache.DefaultCredentials;
oWebProxy.UseDefaultCredentials = true;
oRequestFactory.Proxy = oWebProxy;
return(oCalendarService);
}
private void oAuth2()
{
//euHReka
string keyFilePath = Path.Combine(Environment.CurrentDirectory, @"..\..\Key.p12"); // found in developer console
//string keyFilePath = @"D:\PEX\PEX_ALM_JENKINS_INTEGRATION\ALM_JENKINS_INTEGRATION\Key.p12"; // found in developer console
string serviceAccountEmail = "*****@*****.**"; // found in developer console
var certificate = new X509Certificate2(keyFilePath, "notasecret", X509KeyStorageFlags.Exportable);
//create credential using certificate
ServiceAccountCredential credential = new ServiceAccountCredential(new ServiceAccountCredential.Initializer(serviceAccountEmail)
{
Scopes = new[] { "https://spreadsheets.google.com/feeds/" } //this scopr is for spreadsheets, check google scope FAQ for others
}.FromCertificate(certificate));
credential.RequestAccessTokenAsync(System.Threading.CancellationToken.None).Wait(); //request token
var requestFactory = new GDataRequestFactory("AlmJenkinsIntegration");
requestFactory.CustomHeaders.Add(string.Format("Authorization: Bearer {0}", credential.Token.AccessToken));
spreadsheetsService.RequestFactory = requestFactory; //add new request factory to your old service
}
private CalendarService CreateCalendarService()
{
CalendarService calendarService = new CalendarService("RemGen");
if (Settings.Default.UseProxy)
{
GDataRequestFactory requestFactory = (GDataRequestFactory)calendarService.RequestFactory;
WebProxy myProxy = new WebProxy(Settings.Default.ProxyAddress, true);
if (Settings.Default.AuthProxy)
{
myProxy.Credentials = new NetworkCredential(Settings.Default.ProxyUsername, Settings.Default.ProxyPassword);
myProxy.UseDefaultCredentials = false;
}
else
{
myProxy.UseDefaultCredentials = true;
}
requestFactory.Proxy = myProxy;
}
calendarService.setUserCredentials(txtUserName.Text, txtPassword.Password);
return(calendarService);
}
public void Connect(string googleUserName, string googlePassword, string spreadSheetName, int sheetIndex)
{
var certificate = new X509Certificate2(_keyFilePath, "notasecret", X509KeyStorageFlags.Exportable);
var credential = new ServiceAccountCredential(new ServiceAccountCredential.Initializer(_serviceAccountEmail) //create credential using certificate
{
Scopes = new[] { "https://spreadsheets.google.com/feeds/" } //this scopr is for spreadsheets, check google scope FAQ for others
}.FromCertificate(certificate));
credential.RequestAccessTokenAsync(System.Threading.CancellationToken.None).Wait(); //request token
var requestFactory = new GDataRequestFactory("Adipat");
requestFactory.CustomHeaders.Add(string.Format("Authorization: Bearer {0}", credential.Token.AccessToken));
_service = new SpreadsheetsService("You App Name"); //create your old service
_service.RequestFactory = requestFactory; //add new request factory to your old service
// Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
var query = new SpreadsheetQuery();
// Make a request to the API and get all spreadsheets.
var feed = _service.Query(query);
var payearEntry = feed.Entries.Cast <SpreadsheetEntry>().FirstOrDefault(entry => entry.Title.Text == spreadSheetName);
if (payearEntry == null)
{
return;
}
var wsFeed = payearEntry.Worksheets;
var worksheet = (WorksheetEntry)wsFeed.Entries[sheetIndex];
// Define the URL to request the list feed of the worksheet.
_listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
}
// Methods
public void Start(string[] args)
{
// Get credential
var credential = AuthorizationManager <ServiceCredential> .GetCredential(
new ServiceAccountProvider(),
ApiKeyPath,
ApplicationScopes);
// Create request
var requestFactory = new GDataRequestFactory(null);
requestFactory.CustomHeaders.Add("Authorization: Bearer " + ((ServiceAccountCredential)credential).Token.AccessToken);
// Create service using the request
var service = new SpreadsheetsService(null)
{
RequestFactory = requestFactory
};
// Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
string docKey = "1dAa5tHdqkq2brQ5pfoFjiv1GkVxge52bn99Oz2AgrQw";
string gDocsURL = "https://spreadsheets.google.com/feeds/spreadsheets/private/full/{0}";
string docURL = String.Format(gDocsURL, docKey);
SpreadsheetQuery query = new SpreadsheetQuery()
{
Uri = new Uri(docURL)
};
// Make a request to the API and get all spreadsheets.
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count == 0)
{
Console.WriteLine("There are no sheets");
}
SpreadsheetEntry spreadsheet = feed.Entries.SingleOrDefault() as SpreadsheetEntry;
var worksheet = spreadsheet.Worksheets.Entries.SingleOrDefault(e => (e as WorksheetEntry).Title.Text == "Data") as WorksheetEntry;
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
listQuery.SpreadsheetQuery = "type=Sales";
ListFeed listFeed = service.Query(listQuery);
Console.WriteLine(listFeed.Entries.Count);
return;
// Iterate through each row, printing its cell values.
foreach (ListEntry row in listFeed.Entries)
{
// Print the first column's cell value
Console.WriteLine(row.Title.Text);
// Iterate over the remaining columns, and print each cell value
foreach (ListEntry.Custom element in row.Elements)
{
Console.WriteLine(element.Value);
}
}
return;
// Iterate through all of the spreadsheets returned
foreach (SpreadsheetEntry sheet in feed.Entries)
{
// Print the title of this spreadsheet to the screen
Console.WriteLine(sheet.Title.Text);
// Make a request to the API to fetch information about all
// worksheets in the spreadsheet.
var wsFeed = sheet.Worksheets;
// Iterate through each worksheet in the spreadsheet.
foreach (WorksheetEntry entry in wsFeed.Entries)
{
// Get the worksheet's title, row count, and column count.
string title = entry.Title.Text;
var rowCount = entry.Rows;
var colCount = entry.Cols;
// Print the fetched information to the screen for this worksheet.
Console.WriteLine(title + "- rows:" + rowCount + " cols: " + colCount);
//// Create a local representation of the new worksheet.
//WorksheetEntry worksheet = new WorksheetEntry();
//worksheet.Title.Text = "New Worksheet";
//worksheet.Cols = 10;
//worksheet.Rows = 20;
//// 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 NewwsFeed = sheet.Worksheets;
//service.Insert(NewwsFeed, worksheet);
}
}
}
public static SpreadsheetsService GetSpreadsheetService(AuthAccountType useServiceAccount = AuthAccountType.Service)
{
if (_driveService != null)
{
return(_sheetervice);
}
try
{
string ApplicationName = GetApplicationName(AuthAccountType.Developer);
string[] scopes = { SheetsService.Scope.Drive, SheetsService.Scope.Spreadsheets };
if (useServiceAccount == AuthAccountType.Service)
{
var serviceAccountEmail = GetClientEmail(AuthAccountType.Service);
var x509File = SERVICE_ACCOUNT_KEYFILE;
var certificate = new X509Certificate2(x509File, "notasecret", X509KeyStorageFlags.Exportable);
var serviceAccountCredentialInitializer = new ServiceAccountCredential
.Initializer(serviceAccountEmail)
{
Scopes = scopes
}
.FromCertificate(certificate);
var credential = new ServiceAccountCredential(serviceAccountCredentialInitializer);
if (!credential.RequestAccessTokenAsync(CancellationToken.None).Result)
{
throw new InvalidOperationException("Access token request failed.");
}
var requestFactory = new GDataRequestFactory(null);
requestFactory.CustomHeaders.Add("Authorization: Bearer " + credential.Token.AccessToken);
var service = new SpreadsheetsService(ApplicationName)
{
RequestFactory = requestFactory
};
return(_sheetervice = service);
}
else
{
string[] Scopes = { SheetsService.Scope.SpreadsheetsReadonly };
string secretFile = GetAuthFilePath();
UserCredential credential;
using (var stream = new FileStream(secretFile, FileMode.Open, FileAccess.Read))
{
string credPath = GetAuthFilePath();
credPath = Path.Combine(AuthFolder(), GOOGLE_DRIVE_TOKEN_FOLDER);
string userId = ClaimsPrincipal.Current.Identity.Name.Split(new char[] { '@' }, StringSplitOptions.RemoveEmptyEntries)[0];
// save credential file to credPath
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
//var service = new SheetsService(new BaseClientService.Initializer()
//{
// HttpClientInitializer = credential,
// ApplicationName = ApplicationName,
//});
var requestFactory = new GDataRequestFactory(null);
requestFactory.CustomHeaders.Add("Authorization: Bearer " + credential.Token.AccessToken);
// Create Google Sheets API service
var service = new SpreadsheetsService(ApplicationName)
{
RequestFactory = requestFactory
};
return(_sheetervice = service);
}
}
catch (Exception ex)
{
DojoLogger.Error(ex.Message);
return(null);
}
}
public static List <Tuple <string, string> > GetAirBnbPasswordsFromSpreadsheet()
{
string ServiceAccountEmail = Config.I.GoogleServiceAccountEmail;
string x509File = Config.I.X509;
var certificate = new X509Certificate2(x509File, "notasecret", X509KeyStorageFlags.Exportable);
var serviceAccountCredentialInitializer =
new ServiceAccountCredential.Initializer(ServiceAccountEmail)
{
Scopes = new[] { @"https://spreadsheets.google.com/feeds", @"https://docs.google.com/feeds" }
}.FromCertificate(certificate);
var credential = new ServiceAccountCredential(serviceAccountCredentialInitializer);
if (!credential.RequestAccessTokenAsync(System.Threading.CancellationToken.None).Result)
{
throw new InvalidOperationException("Access token request failed.");
}
var requestFactory = new GDataRequestFactory(null);
requestFactory.CustomHeaders.Add("Authorization: Bearer " + credential.Token.AccessToken);
//var service = new SpreadsheetsService(null) { RequestFactory = requestFactory };
SpreadsheetsService service = new SpreadsheetsService("senstay");
service.RequestFactory = requestFactory;
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
//Console.WriteLine("Your spreadsheets:");
SpreadsheetEntry AirPasswords = null;
foreach (SpreadsheetEntry entry in feed.Entries)
{
var TitleStripted = entry.Title.Text + "";
if (TitleStripted.Contains("Airbnb Passwords for Nikita"))
{
AirPasswords = entry;
break;
}
//Console.WriteLine(entry.Title.Text);
}
AtomLink link = AirPasswords.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery queryW = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feedW = service.Query(queryW);
WorksheetEntry Worksheet = null;
foreach (WorksheetEntry worksheet in feedW.Entries)
{
Worksheet = worksheet;
break;
//Console.WriteLine(worksheet.Title.Text);
}
AtomLink cellFeedLink = Worksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery queryC = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed feedC = service.Query(queryC);
//Console.WriteLine("Cells in this worksheet:");
var Passwords = new Dictionary <uint, Tuple <string, string> >();
foreach (CellEntry curCell in feedC.Entries)
{
//Console.WriteLine("Row {0}, column {1}: {2}", curCell.Cell.Row, curCell.Cell.Column, curCell.Cell.Value);
var col = curCell.Cell.Column;
var row = curCell.Cell.Row;
var value = curCell.Cell.Value;
Tuple <string, string> EmailAndPassword = null;
if (Passwords.ContainsKey(row))
{
EmailAndPassword = Passwords[row];
}
else
{
EmailAndPassword = new Tuple <string, string>("", "");
}
if (col == 1)
{
EmailAndPassword = new Tuple <string, string>(value, EmailAndPassword.Item2);
}
else if (col == 2)
{
EmailAndPassword = new Tuple <string, string>(EmailAndPassword.Item1, value);
}
if (!EmailAndPassword.Item1.Contains("@"))
{
continue;
}
Passwords[row] = EmailAndPassword;
}
/*
* foreach (var i in Passwords)
* {
* Console.WriteLine(i.Key + ") " + i.Value.Item1 + " = " + i.Value.Item2);
* }
* //*/
//Console.WriteLine(Passwords.Values);
//Console.ReadLine();
return(GetList(Passwords));
}
public static void SpreadSheetWork()
{
var certificate = new X509Certificate2(@"GoogleDocAccessProject-b63329148447.p12", "notasecret", X509KeyStorageFlags.Exportable);
const string user = "******";
var serviceAccountCredentialInitializer = new ServiceAccountCredential.Initializer(user)
{
Scopes = new[] { "https://spreadsheets.google.com/feeds" }
}.FromCertificate(certificate);
var credential = new ServiceAccountCredential(serviceAccountCredentialInitializer);
if (!credential.RequestAccessTokenAsync(System.Threading.CancellationToken.None).Result)
{
throw new InvalidOperationException("Access token request failed.");
}
var requestFactory = new GDataRequestFactory(null);
requestFactory.CustomHeaders.Add("Authorization: Bearer " + credential.Token.AccessToken);
var service = new SpreadsheetsService("GoogleDocAccessApp")
{
RequestFactory = requestFactory
};
// Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
SpreadsheetQuery query = new SpreadsheetQuery();
// Make a request to the API and get all spreadsheets.
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count == 0)
{
Console.WriteLine("There are no sheets");
}
// Iterate through all of the spreadsheets returned
foreach (SpreadsheetEntry sheet in feed.Entries)
{
// Print the title of this spreadsheet to the screen
Console.WriteLine(sheet.Title.Text);
// Make a request to the API to fetch information about all
// worksheets in the spreadsheet.
WorksheetFeed wsFeed = sheet.Worksheets;
// Iterate through each worksheet in the spreadsheet.
foreach (WorksheetEntry entry in wsFeed.Entries)
{
// Get the worksheet's title, row count, and column count.
string title = entry.Title.Text;
var rowCount = entry.Rows;
var colCount = entry.Cols;
#region "перевод выбранного"
if (title.Contains("перевод выбранного"))
{
// Print the fetched information to the screen for this worksheet.
Console.WriteLine(title + "- rows:" + rowCount + " cols: " + colCount);
//And get a cell based feed:
AtomLink cellFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery cquery = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed cfeed = service.Query(cquery);
Console.WriteLine("Cells in this worksheet:");
string[,] cells = new string[rowCount, colCount];
foreach (CellEntry curCell in cfeed.Entries)
//for (int ri = 0; ri < rowCount; ri++)
// for (int ci = 0; ci < colCount; ci++)
{
//CellEntry curCell = cfeed.Entries.FirstOrDefault(x => x.);
//Console.WriteLine("Row {0}, column {1}: {2}", curCell.Cell.Row, curCell.Cell.Column, curCell.Cell.Value);
cells[curCell.Cell.Row - 1, curCell.Cell.Column - 1] = curCell.Cell.Value;
}
using (var db = new ProductContext())
{
for (int ri = 0; ri < rowCount; ri++)
{
//for (int ci = 0; ci < colCount; ci++)
if (!string.IsNullOrEmpty(cells[ri, 0]) &&
!string.IsNullOrEmpty(cells[ri, 1]) &&
!string.IsNullOrEmpty(cells[ri, 2]) &&
!string.IsNullOrEmpty(cells[ri, 3]))
{
string titleEng = cells[ri, 1];
if (db.Translations.Any(t => t.titleEng == titleEng))
{
db.Translations.RemoveRange(db.Translations.Where(t => t.titleEng == titleEng));
}
db.Translations.Add(new Translation
{
titleEng = cells[ri, 1],
title = cells[ri, 2],
desc = cells[ri, 3]
});
Console.WriteLine("added {0}", cells[ri, 1]);
}
}
db.SaveChanges();
}
// Create a local representation of the new worksheet.
/*
* WorksheetEntry worksheet = new WorksheetEntry();
* worksheet.Title.Text = "New Worksheet";
* worksheet.Cols = 10;
* worksheet.Rows = 20;
*
* // 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 NewwsFeed = sheet.Worksheets;
* service.Insert(NewwsFeed, worksheet);
*/
}
#endregion
#region категории
if (title.Contains("Категории"))
{
// Print the fetched information to the screen for this worksheet.
Console.WriteLine(title + "- rows:" + rowCount + " cols: " + colCount);
//And get a cell based feed:
AtomLink cellFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery cquery = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed cfeed = service.Query(cquery);
Console.WriteLine("Cells in this worksheet:");
string[,] cells = new string[rowCount, colCount];
foreach (CellEntry curCell in cfeed.Entries)
//for (int ri = 0; ri < rowCount; ri++)
// for (int ci = 0; ci < colCount; ci++)
{
//CellEntry curCell = cfeed.Entries.FirstOrDefault(x => x.);
Console.WriteLine("Row {0}, column {1}: {2}", curCell.Cell.Row, curCell.Cell.Column, curCell.Cell.Value);
cells[curCell.Cell.Row - 1, curCell.Cell.Column - 1] = curCell.Cell.Value;
}
using (var db = new ProductContext())
{
for (int ri = 0; ri < rowCount; ri++)
{
//for (int ci = 0; ci < colCount; ci++)
if (!string.IsNullOrEmpty(cells[ri, 0]) &&
!string.IsNullOrEmpty(cells[ri, 1]) &&
!string.IsNullOrEmpty(cells[ri, 2])
)
{
string titleEng = cells[ri, 0];
if (db.Translations.Any(t => t.titleEng == titleEng))
{
db.Translations.RemoveRange(db.Translations.Where(t => t.titleEng == titleEng));
}
db.Translations.Add(new Translation
{
titleEng = cells[ri, 0],
title = cells[ri, 1],
isOurCategory = (cells[ri, 2] == "Наша категория" || cells[ri, 2] == "Наша категория 2"),
keyWords = cells[ri, 3],
antiKeyWords = cells[ri, 4]
});
Console.WriteLine("added {0}", cells[ri, 1]);
}
}
db.SaveChanges();
}
}
#endregion
}
}
}
public static List <AirBnbAccountCredentials> GetAirBnbPasswordsFromSpreadsheet()
{
var serviceAccountEmail = Config.I.GoogleServiceAccountEmail;
var x509File = Config.I.X509;
var certificate = new X509Certificate2(x509File, "notasecret", X509KeyStorageFlags.Exportable);
var serviceAccountCredentialInitializer = new ServiceAccountCredential
.Initializer(serviceAccountEmail)
{
Scopes = new[] {
@"https://spreadsheets.google.com/feeds",
@"https://docs.google.com/feeds"
}
}
.FromCertificate(certificate);
var credential = new ServiceAccountCredential(serviceAccountCredentialInitializer);
if (!credential.RequestAccessTokenAsync(CancellationToken.None).Result)
{
throw new InvalidOperationException("Access token request failed.");
}
var requestFactory = new GDataRequestFactory(null);
requestFactory.CustomHeaders.Add("Authorization: Bearer " + credential.Token.AccessToken);
var service = new SpreadsheetsService("senstay")
{
RequestFactory = requestFactory
};
var query = new SpreadsheetQuery();
var feed = service.Query(query);
SpreadsheetEntry airPasswords = null;
foreach (var atomEntry in feed.Entries)
{
var entry = (SpreadsheetEntry)atomEntry;
var titleStripted = entry.Title.Text + "";
if (!titleStripted.Contains("Airbnb Passwords for Nikita"))
{
continue;
}
airPasswords = entry;
break;
}
if (airPasswords == null)
{
return(new List <AirBnbAccountCredentials>());
}
var link = airPasswords.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
var queryW = new WorksheetQuery(link.HRef.ToString());
var feedW = service.Query(queryW);
var worksheet = feedW.Entries.Cast <WorksheetEntry>().FirstOrDefault(x => x.Title.Text == Config.I.ActualPasswordsSheetTitle);
if (worksheet == null)
{
return(new List <AirBnbAccountCredentials>());
}
var cellFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
var queryC = new CellQuery(cellFeedLink.HRef.ToString());
var feedC = service.Query(queryC);
var passwords = new Dictionary <uint, AirBnbAccountCredentials>();
foreach (var atomEntry in feedC.Entries)
{
var curCell = (CellEntry)atomEntry;
var col = curCell.Cell.Column;
var row = curCell.Cell.Row;
var value = curCell.Cell.Value;
var credentials = passwords.ContainsKey(row) ? passwords[row] : new AirBnbAccountCredentials();
switch (col)
{
case 1:
credentials.Email = value;
break;
case 2:
credentials.Password = value;
break;
case 4:
case 5:
if (!string.IsNullOrEmpty(value))
{
credentials.Proxies.Add(value);
}
break;
}
if (credentials.Email == null || !credentials.Email.Contains("@"))
{
continue;
}
passwords[row] = credentials;
}
return(GetList(passwords));
}
private static ListFeed GetListFeed()
{
var serviceAccountEmail = Config.I.GoogleServiceAccountEmail;
var x509File = Config.I.X509;
var certificate = new X509Certificate2(x509File, "notasecret", X509KeyStorageFlags.Exportable);
var serviceAccountCredentialInitializer = new ServiceAccountCredential
.Initializer(serviceAccountEmail)
{
Scopes = new[] {
@"https://spreadsheets.google.com/feeds",
@"https://docs.google.com/feeds"
}
}
.FromCertificate(certificate);
var credential = new ServiceAccountCredential(serviceAccountCredentialInitializer);
if (!credential.RequestAccessTokenAsync(CancellationToken.None).Result)
{
throw new InvalidOperationException("Access token request failed.");
}
var requestFactory = new GDataRequestFactory(null);
requestFactory.CustomHeaders.Add("Authorization: Bearer " + credential.Token.AccessToken);
var service = new SpreadsheetsService("senstay")
{
RequestFactory = requestFactory
};
var query = new SpreadsheetQuery();
var feed = service.Query(query);
SpreadsheetEntry airPasswords = null;
foreach (var atomEntry in feed.Entries)
{
var entry = (SpreadsheetEntry)atomEntry;
var titleStripted = entry.Title.Text + "";
if (!titleStripted.Contains("Airbnb Passwords for Nikita"))
{
continue;
}
airPasswords = entry;
break;
}
var link = airPasswords.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
var queryW = new WorksheetQuery(link.HRef.ToString());
var feedW = service.Query(queryW);
var worksheet = feedW.Entries.Cast <WorksheetEntry>().FirstOrDefault();
var cellFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
var queryC = new ListQuery(cellFeedLink.HRef.ToString());
return(service.Query(queryC));
}
public void YouTubeInsertTest()
{
Tracing.TraceMsg("Entering YouTubeInsertTest");
YouTubeService service = new YouTubeService("NETUnittests", this.ytDevKey);
if (!string.IsNullOrEmpty(this.ytUser))
{
service.Credentials = new GDataCredentials(this.ytUser, this.ytPwd);
}
GDataRequestFactory factory = service.RequestFactory as GDataRequestFactory;
factory.Timeout = 1000000;
YouTubeEntry entry = new YouTubeEntry();
entry.MediaSource = new MediaFileSource(Path.Combine(this.resourcePath, "test_movie.mov"), "video/quicktime");
entry.Media = new YouTube.MediaGroup();
entry.Media.Description = new MediaDescription("This is a test with and & in it");
entry.Media.Title = new MediaTitle("Sample upload");
entry.Media.Keywords = new MediaKeywords("math");
// entry.Media.Categories
MediaCategory category = new MediaCategory("Nonprofit");
category.Attributes["scheme"] = YouTubeService.DefaultCategory;
entry.Media.Categories.Add(category);
YouTubeEntry newEntry = service.Upload(this.ytUser, entry);
Assert.AreEqual(newEntry.Media.Description.Value, entry.Media.Description.Value, "Description should be equal");
Assert.AreEqual(newEntry.Media.Keywords.Value, entry.Media.Keywords.Value, "Keywords should be equal");
// now change the entry
newEntry.Title.Text = "This test upload will soon be deleted";
YouTubeEntry anotherEntry = newEntry.Update() as YouTubeEntry;
// bugbug in YouTube server. Returns empty category that the service DOES not like on reuse. so remove
ExtensionList a = ExtensionList.NotVersionAware();
foreach (MediaCategory m in anotherEntry.Media.Categories)
{
if (String.IsNullOrEmpty(m.Value))
{
a.Add(m);
}
}
foreach (MediaCategory m in a)
{
anotherEntry.Media.Categories.Remove(m);
}
Assert.AreEqual(newEntry.Media.Description.Value, anotherEntry.Media.Description.Value, "Description should be equal");
Assert.AreEqual(newEntry.Media.Keywords.Value, anotherEntry.Media.Keywords.Value, "Keywords should be equal");
// now update the video
anotherEntry.MediaSource = new MediaFileSource(Path.Combine(this.resourcePath, "test.mp4"), "video/mp4");
anotherEntry.Update();
// now delete the guy again
newEntry.Delete();
}
/// <summary>
/// Query Google Calendar for a user and return the event feed
/// </summary>
/// <param name="email">Email address of the user to query</param>
/// <param name="visibility">Feed Visibility (Public/Private) to query for</param>
/// <param name="projection">Feed projection - type of feed to get</param>
/// <param name="modifiedSince">Last modified time from last check</param>
/// <param name="window">DateTime range to query between</param>
/// <returns>An event feed for the user</returns>
public EventFeed QueryGCal(
string email,
GCalVisibility visibility,
GCalProjection projection,
DateTime modifiedSince,
DateTimeRange window)
{
// Perform mapping on the username if necessary
string user = ConfigCache.MapToExternalDomain(email);
if (log.IsDebugEnabled)
{
log.InfoFormat(
"FeedQuery with parameters: {0}, {1}, {2}, {3} [{4}]",
user,
visibility,
projection,
modifiedSince,
window);
}
StringBuilder sb = new StringBuilder(ConfigCache.GCalAddress);
if (!ConfigCache.GCalAddress.EndsWith("/"))
{
sb.Append("/");
}
sb.AppendFormat("feeds/{0}/", user);
switch (visibility)
{
case GCalVisibility.Public:
sb.Append("public/");
break;
case GCalVisibility.Private:
default:
sb.Append("private/");
break;
}
switch (projection)
{
case GCalProjection.Full:
sb.Append("full");
break;
case GCalProjection.FullNoAttendees:
sb.Append("full-noattendees");
break;
case GCalProjection.Composite:
sb.Append("composite");
break;
case GCalProjection.AttendeesOnly:
sb.Append("attendees-only");
break;
case GCalProjection.FreeBusy:
sb.Append("free-busy");
break;
case GCalProjection.Basic:
default:
sb.Append("basic");
break;
}
EventQuery query = new EventQuery(sb.ToString());
if (projection != GCalProjection.FreeBusy)
{
query.SingleEvents = true;
}
GDataRequestFactory f = (GDataRequestFactory)service.RequestFactory;
f.UseGZip = ConfigCache.EnableHttpCompression;
if (window.Start != DateTime.MinValue)
{
query.StartTime = window.Start;
}
if (window.End != DateTime.MaxValue)
{
query.EndTime = window.End;
}
query.NumberToRetrieve = int.MaxValue; // Make sure we get everything
try
{
return(QueryGCal(query, user, modifiedSince));
}
catch (System.IO.IOException e)
{
// Known problem with .NET 2.0 - Sometimes keep-alive connection is
// closed by a proxy and we need to re-attemp the connection
//
// http://code.google.com/p/google-gdata/wiki/KeepAliveAndUnderlyingConnectionIsClosed
if (e.InnerException.GetType().ToString().Equals("System.Net.Sockets.SocketException"))
{
log.Info(String.Format("Attempt Retry Query after keep-alive termination"));
// One shot retry i case the keep-alive was closed
return(QueryGCal(query, user, modifiedSince));
}
else
{
throw;
}
}
}
