//Fetch all worksheets for given Spreadsheet
public static ArrayList getWorksheetList(string userName, string passWord, string spreadSheetName)
{
ArrayList worksheetList = new ArrayList();
SpreadsheetsService service = new SpreadsheetsService(spreadSheetName + "Service");
//You could set it up from DB or config file also. This is not a reccomended way. Just an easy way to show fetching
service.setUserCredentials(userName, passWord);
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
foreach (SpreadsheetEntry entry in feed.Entries)
{
if (entry.Title.Text == spreadSheetName)
{
AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery wquery = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed wfeed = service.Query(wquery);
foreach (WorksheetEntry worksheet in wfeed.Entries)
{
worksheetList.Add(worksheet.Title.Text);
}
}
}
return(worksheetList);
}
static void Main(string[] args)
{
Environment.CurrentDirectory = Environment.CurrentDirectory.Substring(0, Environment.CurrentDirectory.LastIndexOf("osum", StringComparison.Ordinal)) + @"\osum\osum\Localisation\";
ss.setUserCredentials("*****@*****.**", "osumisawsum");
SpreadsheetFeed feed = ss.Query(new SpreadsheetQuery());
SpreadsheetEntry sheet = null;
//get spreadsheet
foreach (var entry in feed.Entries.OfType <SpreadsheetEntry>())
{
if (entry.Title.Text == "osu!stream localisation")
{
sheet = entry;
break;
}
}
if (sheet == null)
{
Console.WriteLine("failed to get spreadsheet");
Console.ReadLine();
}
//get worksheet
AtomLink link = sheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed wfeed = ss.Query(query);
WorksheetEntry worksheet = wfeed.Entries[0] as WorksheetEntry;
ProcessWorksheet(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()));
}
static void Main(string[] args)
{
SpreadsheetsService myService = new SpreadsheetsService("ContenidoXXVICongreso");
myService.setUserCredentials("*****@*****.**", "Ciruelazo#1");
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = myService.Query(query);
Console.WriteLine("Your spreadsheets:");
foreach (SpreadsheetEntry entry in feed.Entries)
{
Console.WriteLine(entry.Title.Text);
}
/*
* AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
* WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());
* WorksheetFeed feed = myService.Query(query);
* foreach (WorksheetEntry worksheet in feed.Entries)
* {
* Console.WriteLine(worksheet.Title.Text);
* }
*/
}
protected void listView1_SelectedIndexChanged(object sender, EventArgs e)
{
if (listView1.SelectedIndex > -1)
{
spreadsheetName = listView1.SelectedItem.Text;
}
else
{
return;
}
if (spreadsheetName.Length > 0)
{
SpreadsheetsService GoogleExcelService;
GoogleExcelService = new SpreadsheetsService("Spreadsheet-Abhishek-Test-App");
GoogleExcelService.setUserCredentials("*****@*****.**", "Lucknow@");
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed myFeed = GoogleExcelService.Query(query);
foreach (SpreadsheetEntry mySpread in myFeed.Entries)
{
if (mySpread.Title.Text == spreadsheetName)
{
WorksheetFeed wfeed = mySpread.Worksheets;
listView2.Items.Clear();
foreach (WorksheetEntry wsheet in wfeed.Entries)
{
string[] row = { wsheet.Title.Text, wsheet.Cols.ToString(), wsheet.Rows.ToString(), wsheet.Summary.Text };
System.Web.UI.WebControls.ListItem listItem = new System.Web.UI.WebControls.ListItem(wsheet.Title.Text);
listView2.Items.Add(listItem);
}
}
}
}
}
// Start Google Library Calls
public void processLogin(string username, string password)
{
try
{
SpreadsheetsService getAcct = new SpreadsheetsService("GAppADDAutoUser");
getAcct.setUserCredentials(username, password);
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = getAcct.Query(query);
foreach (SpreadsheetEntry entry in feed.Entries)
{
sheetView.Items.Add(entry.Title.Text.ToString());
}
}
catch (Exception e)
{
string loginExc = e.ToString();
if (loginExc.Contains("InvalidCredentials"))
{
MessageBox.Show("You fatfingered the credentials. Try logging in again with the right ones.", "Error (how could you do this to me?)", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
System.Windows.Forms.MessageBox.Show("The following error has occured: " + e.ToString(), "Error argh bah no:", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
loginFailed = true;
loginWorker.CancelAsync();
}
}
/// <summary>
/// Handler for when the Spreadsheets tab becomes the active tab
/// </summary>
/// <param name="sender">Ignored</param>
/// <param name="e">Ignored</param>
void OnEnterSelectSpreadsheetTab(object sender, System.EventArgs e)
{
service.setUserCredentials(this.usernameTextBox.Text, this.passwordTextBox.Text);
SpreadsheetQuery query = new SpreadsheetQuery();
setSpreadsheetListView(service.Query(query));
}
public SpreadsheetProxy(Credentials credentials)
{
spreadsheetService = new SpreadsheetsService("groundfloor-svc1");
spreadsheetService.setUserCredentials(credentials.username, credentials.password);
documentService = new DocumentsService("groundfloor-svc2");
documentService.setUserCredentials(credentials.username, credentials.password);
}
private SpreadsheetsService AuthenticateGoogleUser(string val)
{
SpreadsheetsService service = new SpreadsheetsService("NodeLookupProgram");
service.setUserCredentials(ConfigurationManager.AppSettings["username"].ToString(), ConfigurationManager.AppSettings["passcode"].ToString());
return(service);
}
public SpreadsheetIO(string username, string password)
{
_userName = username;
_password = password;
_service = new SpreadsheetsService("MySpreadsheetIntegration-v1");
_service.setUserCredentials(_userName, _password);
_query = new SpreadsheetQuery();
_feed = _service.Query(_query);
}
protected void button2_Click(object sender, EventArgs e)
{
if (spreadsheetName.Length > 0)
{
if (listView2.SelectedIndex > -1)
{
wrkSheetName = listView2.SelectedItem.Text;
}
else
{
return;
}
SpreadsheetsService GoogleExcelService;
GoogleExcelService = new SpreadsheetsService("Spreadsheet-Abhishek-App");
GoogleExcelService.setUserCredentials("*****@*****.**", "Lucknow@");
// ListQuery query = new ListQuery("0AmYgMIof-5mgdGM2OGxoTmUyc3JRTFlMZ1BTUG5SOVE", "1", "public", "values");
// ListQuery query = new ListQuery("https://docs.google.com/a/laitkor.com/spreadsheet/ccc?key=0AttN4WWVg0qodF9RSG8tOXptV0RwZm1LOWFJQ3g0Mnc#gid=0", "1", "public", "values");
//ListFeed myFeed = GoogleExcelService.Query(query);
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed myFeed = GoogleExcelService.Query(query);
foreach (SpreadsheetEntry mySpread in myFeed.Entries)
{
if (mySpread.Title.Text == spreadsheetName)
{
WorksheetFeed wfeed = mySpread.Worksheets;
foreach (WorksheetEntry wsheet in wfeed.Entries)
{
if (wsheet.Title.Text == wrkSheetName)
{
AtomLink atm = wsheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery Lquery = new ListQuery(atm.HRef.ToString());
ListFeed LFeed = GoogleExcelService.Query(Lquery);
myTable = new DataTable();
DataColumn DC;
foreach (ListEntry LmySpread in LFeed.Entries)
{
DataRow myDR = myTable.NewRow();
foreach (ListEntry.Custom listrow in LmySpread.Elements)
{
DC = myTable.Columns[listrow.LocalName] ?? myTable.Columns.Add(listrow.LocalName);
myDR[DC] = listrow.Value;
}
myTable.Rows.Add(myDR);
}
dataGridView1.DataSource = myTable;
dataGridView1.DataBind();
}
}
}
}
}
//System.Windows.Forms.MessageBox.Show("Data Reading is Completed");
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Data Reading is Completed');", true);
}
public DatabaseClient(string username, string password)
{
var docService = new DocumentsService("database");
docService.setUserCredentials(username, password);
documentService = docService;
var ssService = new SpreadsheetsService("database");
ssService.setUserCredentials(username, password);
spreadsheetService = ssService;
}
public static List <Spreadsheet> ListAll()
{
SpreadsheetsService service = new SpreadsheetsService("ClearCanvas-Workstation-1.0");
service.setUserCredentials("jresnick", "bl00b0lt");
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
return(CollectionUtils.Map <SpreadsheetEntry, Spreadsheet, List <Spreadsheet> >(
feed.Entries,
delegate(SpreadsheetEntry entry) { return new Spreadsheet(service, entry); }));
}
public static Spreadsheet GetByTitle(string title)
{
SpreadsheetsService service = new SpreadsheetsService("ClearCanvas-Workstation-1.0");
service.setUserCredentials("jresnick", "bl00b0lt");
SpreadsheetQuery query = new SpreadsheetQuery();
query.Title = title;
SpreadsheetFeed feed = service.Query(query);
return(feed.Entries.Count > 0 ? new Spreadsheet(service, (SpreadsheetEntry)feed.Entries[0]) : null);
}
public static void SpreadSheetRead()
{
//Authenticate:
SpreadsheetsService myService = new SpreadsheetsService(null);
myService.setUserCredentials("yokolnov.logstream", "klamsi81");
//Get a list of spreadsheets:
SpreadsheetQuery query = new SpreadsheetQuery();
query.Uri = new Uri(@"https://docs.google.com/spreadsheets/d/19qX8B8skvYMRcmgMQhsek2ZHi1fdiyf1LC7wVm7D4L4/edit#gid=0");
SpreadsheetFeed feed = myService.Query(query);
Console.WriteLine("Your spreadsheets: ");
foreach (SpreadsheetEntry entry in feed.Entries)
{
Console.WriteLine(entry.Title.Text);
//Given a SpreadsheetEntry you've already retrieved, you can get a list of all worksheets in this spreadsheet as follows:
AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery wsquery = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed wsfeed = myService.Query(wsquery);
foreach (WorksheetEntry worksheet in wsfeed.Entries)
{
Console.WriteLine(worksheet.Title.Text);
//And get a cell based feed:
AtomLink cellFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery cquery = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed cfeed = myService.Query(cquery);
Console.WriteLine("Cells in this worksheet:");
foreach (CellEntry curCell in feed.Entries)
{
Console.WriteLine("Row {0}, column {1}: {2}", curCell.Cell.Row,
curCell.Cell.Column, curCell.Cell.Value);
}
}
}
}
public void bindgrid()
{
SpreadsheetsService GoogleExcelService;
GoogleExcelService = new SpreadsheetsService("Spreadsheet-Abhishek-App");
GoogleExcelService.setUserCredentials("*****@*****.**", "Lucknow@");
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed myFeed = GoogleExcelService.Query(query);
foreach (SpreadsheetEntry mySpread in myFeed.Entries)
{
if (mySpread.Title.Text == spreadsheetName)
{
WorksheetFeed wfeed = mySpread.Worksheets;
foreach (WorksheetEntry wsheet in wfeed.Entries)
{
if (wsheet.Title.Text == wrkSheetName)
{
AtomLink atm = wsheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery Lquery = new ListQuery(atm.HRef.ToString());
ListFeed LFeed = GoogleExcelService.Query(Lquery);
myTable = new DataTable();
DataColumn DC;
foreach (ListEntry LmySpread in LFeed.Entries)
{
DataRow myDR = myTable.NewRow();
foreach (ListEntry.Custom listrow in LmySpread.Elements)
{
DC = myTable.Columns[listrow.LocalName] ?? myTable.Columns.Add(listrow.LocalName);
myDR[DC] = listrow.Value;
}
myTable.Rows.Add(myDR);
}
dataGridView1.DataSource = myTable;
dataGridView1.DataBind();
}
}
}
}
}
//Fetch all Spreadsheets
public static ArrayList getSpreadsheetList(string userName, string passWord)
{
ArrayList spreadsheetList = new ArrayList();
SpreadsheetsService service = new SpreadsheetsService("GetSpreadsheetsService");
//You could set it up from DB or config file also. This is not a reccomended way. Just an easy way to show fetching
service.setUserCredentials(userName, passWord);
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
foreach (SpreadsheetEntry entry in feed.Entries)
{
spreadsheetList.Add(entry.Title.Text);
}
return(spreadsheetList);
}
public void LogIn(string login, string password)
{
SpreadsheetsService service = new SpreadsheetsService(applicationName);
service.setUserCredentials(login, password);
// Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
SpreadsheetQuery query = new SpreadsheetQuery();
query.Title = applicationName;
try
{
SpreadsheetFeed feed = service.Query(query);
internalCredentials.LogIn(login, password);
}
catch (Exception e)
{
throw new CredentialsException(e.Message);
}
}
public WatchNotify(string googleUserName, string googlePassword, IEmailService mailService)
{
if (String.IsNullOrEmpty(googleUserName) || String.IsNullOrEmpty(googlePassword))
{
throw new ArgumentException("Please provide google credentials");
}
GoogleUserName = googleUserName;
GooglePassword = googlePassword;
MySpreadsheetService = new SpreadsheetsService("AuctionWatchNotify");
MySpreadsheetService.setUserCredentials(GoogleUserName, GooglePassword);
if (!mailService.CheckConnection())
{
throw new ArgumentException("Please pass in a working email service account");
}
MyEmailService = mailService;
}
protected void button1_Click(object sender, EventArgs e)
{
SpreadsheetsService GoogleExcelService;
GoogleExcelService = new SpreadsheetsService("Spreadsheet-Abhishek-Test-App");
GoogleExcelService.setUserCredentials("*****@*****.**", "Lucknow@");
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed myFeed = GoogleExcelService.Query(query);
//if (myFeed == null)
//{
// label4.Text = "Invalid Credentials";
//}
foreach (SpreadsheetEntry mySpread in myFeed.Entries)
{
//string[] row = { mySpread.Title.Text, mySpread.Summary.Text, mySpread.Updated.ToShortDateString() };
System.Web.UI.WebControls.ListItem listItem = new System.Web.UI.WebControls.ListItem(mySpread.Title.Text);
listView1.Items.Add(listItem);
}
}
/// <summary>
/// Creates a new SpreadsheetsService with the user's specified
/// authentication credentials and runs all of the Spreadsheets
/// operations above.
/// </summary>
private static void RunSample()
{
SpreadsheetsService service = new SpreadsheetsService("exampleCo-exampleApp-1");
service.setUserCredentials(userName, password);
// Demonstrate printing all spreadsheets and worksheets.
PrintAllSpreadsheetsAndWorksheets(service);
// Demonstrate retrieving the list feed for a single worksheet,
// with the rows (ordered by position) reversed.
int userChoice = GetUserWorksheetChoice();
WorksheetEntry entry = allWorksheets[userChoice] as WorksheetEntry;
RetrieveListFeed(service, entry, true);
// Demonstrate sending a structured query.
Console.Write("Enter a structured query to execute: ");
string queryText = Console.ReadLine();
StructuredQuery(service, entry, queryText);
// Demonstrate inserting a new row in the worksheet.
ListEntry insertedEntry = InsertRow(service, entry);
// Demonstrate updating the inserted row.
UpdateRow(service, insertedEntry);
// Demonstrate deleting the entry.
insertedEntry.Delete();
// Demonstrate retrieving a cell feed for a worksheet.
RetrieveCellFeed(service, entry);
// Demonstrate a cell range query.
CellRangeQuery(service, entry);
// Demonstrate updating a single cell.
UpdateCell(service, entry);
}
private void updateEhtpSheet(string passyword, string useryword, int indyex)
{
SpreadsheetsService getAcct = new SpreadsheetsService("GAppADDAutoUserEHTP");
getAcct.setUserCredentials(loginUser.Text, loginPassword.Text);
SpreadsheetQuery spreadQuery = new SpreadsheetQuery();
SpreadsheetFeed spreadFeed = getAcct.Query(spreadQuery);
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)spreadFeed.Entries[sheetView.SelectedIndex];
WorksheetFeed wsFeed = spreadsheet.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];
CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
cellQuery.ReturnEmpty = ReturnEmptyCells.yes;
CellFeed cellFeed = getAcct.Query(cellQuery);
foreach (CellEntry cell in cellFeed.Entries)
{
string uriStr = cell.SelfUri.ToString();
if (uriStr.Contains("R" + Convert.ToString(indyex + 2) + "C7"))
{
cell.InputValue = passyword;
cell.Update();
}
if (uriStr.Contains("R" + Convert.ToString(indyex + 2) + "C8"))
{
cell.InputValue = useryword;
cell.Update();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
SpreadsheetsService service = new SpreadsheetsService("Final Attendance");
service.setUserCredentials("*****@*****.**", "carl101894");
SpreadsheetQuery query = new SpreadsheetQuery();
query.Title = "Final Attendance";
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count == 0)
{
// TODO: There were no spreadsheets, act accordingly.
}
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
WorksheetFeed wsFeed = spreadsheet.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
PagedDataSource pds = new PagedDataSource();
var table = new DataTable();
foreach (ListEntry row in listFeed.Entries.Reverse())
{
var roww = table.NewRow();
foreach (ListEntry.Custom element in row.Elements)
{
var column = table.Columns[element.LocalName] ?? table.Columns.Add(element.LocalName);
roww[column] = element.Value;
}
table.Rows.Add(roww);
}
pds.DataSource = table.DefaultView;
pds.AllowPaging = true;
pds.PageSize = 30;
if (Request.QueryString["page"] != null)
{
CurrPage = Convert.ToInt32(Request.QueryString["page"].ToString());
int intPage = (Convert.ToInt32(Request.QueryString["page"]) - 1);
if (intPage < 0)
{
intPage = 0;
}
if (intPage > pds.PageCount)
{
intPage = pds.PageCount - 1;
}
pds.CurrentPageIndex = intPage;
}
else
{
CurrPage = 1;
}
next10 = Convert.ToDouble(getNext10(CurrPage));
prev10 = Convert.ToDouble(getPrev10(CurrPage));
nPageCount = pds.PageCount;
pnPageCount = Convert.ToInt32(nPageCount) - 1;
rptr.DataSource = pds;
rptr.DataSource = pds;
RSPrevPage = CurrPage - 1;
RSNextPage = CurrPage + 1;
if (next10 > pds.PageCount)
{
next10 = pds.PageCount;
}
if (prev10 == 1 & next10 - 1 < 10)
{
start = 1;
}
else
{
start = next10 - 10;
if (Convert.ToInt32(start.ToString().Substring(start.ToString().Length - 1, 1)) > 0)
{
start = Convert.ToDouble(start.ToString().Replace(start.ToString().Substring(start.ToString().Length - 1, 1), "0"));
start = start + 10;
}
}
MyLiteral.Text = CreatePagerLinks(pds, next10, prev10, "View_Gdata.aspx");
}
static void Main(string[] args)
{
//Get credentials
Console.Write("Enter your username:"******"Enter your password:"******"my-service");
service.setUserCredentials(user, password);
ListFeed listFeed = null;
SpreadsheetFeed feed = null;
IProgress <string> p = new Progress <string>(Console.WriteLine);
//Get list of spreadsheets
var getSheets = Task.Run(() =>
{
p.Report("Getting all your Google spreadsheets...");
var query = new SpreadsheetQuery();
feed = service.Query(query);
});
getSheets.Wait();
//Show list of spreadsheets...
foreach (SpreadsheetEntry entry in feed.Entries.OrderBy(x => x.Title.Text))
{
Console.WriteLine(entry.Title.Text);
}
Console.WriteLine("Which spreadsheet would you like to see the contents of?");
var title = Console.ReadLine();
//Get list of spreadsheets
var getInfo = Task.Run(() =>
{
p.Report("Reading rows from spreadsheet");
var query = new SpreadsheetQuery();
query.Title = title;
feed = service.Query(query);
var spreadsheet = feed.Entries.FirstOrDefault() as SpreadsheetEntry;
var wsFeed = spreadsheet.Worksheets;
var worksheet = wsFeed.Entries.FirstOrDefault() as WorksheetEntry;
//Define the URL to request the list feed of the worksheet
var listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
p.Report(string.Format("Spreadsheet table link: {0}", listFeedLink.HRef));
//Rows in spreadsheet
var listQuery = new ListQuery(listFeedLink.HRef.ToString());
listFeed = service.Query(listQuery);
});
getInfo.Wait();
//Iterate through the rows...
foreach (ListEntry row in listFeed.Entries)
{
foreach (ListEntry.Custom element in row.Elements)
{
Console.WriteLine(element.Value);
}
}
//NOTE: google docs always treats the first row as the header row...
Console.WriteLine("Type a new value for the first cell in the first column:");
var newValue = Console.ReadLine();
Console.WriteLine("Updating row data...");
var updateRow = (ListEntry)listFeed.Entries[0];
ListEntry.Custom updateMe = updateRow.Elements[0];
updateMe.Value = newValue;
updateRow.Update();
Console.Read();
}
public formatSheet(string Username, string Password, string Sheet, string Worksheet)
{
dt = new DataTable();
try
{
SpreadsheetsService service = new
SpreadsheetsService("Anachrophobe");
service.setUserCredentials(Username, Password);
SpreadsheetQuery query = new SpreadsheetQuery();
query.Title = Sheet;
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count != 1)
{
return;
}
AtomLink link =
feed.Entries[0].Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel,
null);
WorksheetQuery worksheetQuery = new
WorksheetQuery(link.HRef.ToString());
worksheetQuery.Title = Worksheet;
WorksheetFeed worksheetFeed =
service.Query(worksheetQuery);
if (worksheetFeed.Entries.Count != 1)
{
return;
}
WorksheetEntry worksheet =
(WorksheetEntry)worksheetFeed.Entries[0];
AtomLink listFeedLink =
worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new
ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
// define the table's schema
dt.Columns.Add(new DataColumn("A", typeof(string)));
dt.Columns.Add(new DataColumn("B", typeof(string)));
dt.Columns.Add(new DataColumn("C", typeof(string)));
dt.Columns.Add(new DataColumn("D", typeof(string)));
foreach (ListEntry worksheetRow in listFeed.Entries)
{
string station = string.Empty;
string scanData =
worksheetRow.Elements[0].Value.ToString();
string scanData1 =
worksheetRow.Elements[1].Value.ToString();
string[] arSD = new string[100];
DataRow dr = dt.NewRow();
dr["A"] = scanData;
dr["B"] = scanData1;
dt.Rows.Add(dr);
}
//dgwTabla.DataSource = dt;
}
catch (Exception ex)
{
}
}
static void Main(string[] args)
{
try
{
string Profile = (args.Length > 0) ? args[0] : "default";
Console.WriteLine(Profile);
Logfile = Profile + "_google.log";
Inifile = Profile + ".ini";
if (File.Exists(Inifile) == false)
throw new ArgumentException("Profile is not found - " + Inifile);
Props prop = new Props();
prop.Load(Inifile);
#region get settings
string LOGIN = prop.Get("login");
string PASSWORD = prop.Get("password");
string SPREADSHEET = prop.Get("spreadsheet");
string WORKSHEET = prop.Get("worksheet");
string CSV = prop.Get("csv");
string COLUMNS = prop.Get("columns");
#endregion
#region test settings
if (LOGIN == string.Empty)
throw new ArgumentException(@"Setting 'login' is not found in profile");
if (PASSWORD == string.Empty)
throw new ArgumentException(@"Setting 'password' is not found in profile");
if (SPREADSHEET == string.Empty)
throw new ArgumentException(@"Setting 'spreadsheet' is not found in profile");
if (WORKSHEET == string.Empty)
throw new ArgumentException(@"Setting 'worksheet' is not found in profile");
if (CSV == string.Empty)
throw new ArgumentException(@"Setting 'csv' is not found in profile");
if (COLUMNS == string.Empty)
throw new ArgumentException(@"Setting 'columns' is not found in profile");
if (File.Exists(CSV) == false)
throw new ArgumentException("CSV is not found - " + Inifile);
#endregion
#region spreadsheet get
service = new SpreadsheetsService("SynesisIntegration-v1");
service.setUserCredentials(LOGIN, PASSWORD);
SpreadsheetEntry spreadsheet = null;
SpreadsheetFeed feed = service.Query(new SpreadsheetQuery());
foreach (SpreadsheetEntry item in feed.Entries)
{
if (item.Title.Text == SPREADSHEET)
{
Log.Add("SPREADSHEET is found - " + SPREADSHEET);
spreadsheet = item;
//spreadsheet.SaveToXml(new FileStream("spreadsheet.xml", FileMode.Create));
break;
}//if
}//for
if (spreadsheet == null)
throw new ArgumentException("SPREADSHEET is not found - " + SPREADSHEET);
#endregion
#region worksheet get
WorksheetEntry worksheet = null;
WorksheetFeed wsFeed = spreadsheet.Worksheets;
foreach (WorksheetEntry entry in wsFeed.Entries)
{
if (entry.Title.Text == WORKSHEET)
{
worksheet = entry;
Log.Add("WORKSHEET is found - " + WORKSHEET);
break;
}//if
}//for
if (worksheet == null)
{
Log.Add("WORKSHEET is not found - " + WORKSHEET);
worksheet = new WorksheetEntry();
worksheet.Title.Text = WORKSHEET;
service.Insert(wsFeed, worksheet);
Log.Add("WORKSHEET is added - " + WORKSHEET);
}//if
#endregion
//CLEAR worksheet
worksheet.Rows = 1;
worksheet.Update();
// Fetch the list feed of the worksheet.
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.Content);
ListFeed listFeed = service.Query(listQuery);
//delimiter on extension
char Delim = ';';
if (CSV.EndsWith(".tsv"))
Delim = '\t';
//columns
ssCOLUMNS = COLUMNS.Split(';');
for (int i = 0; i < ssCOLUMNS.Length; i++)
ssCOLUMNS[i] = ssCOLUMNS[i].ToLower();
#region input rows
string[] input = File.ReadAllLines(CSV);
Log.Add("CSV " + CSV + " has " + input.Length + " items");
int CountAdded = 0;
foreach (string s in input)
{
if (AddRow(listFeed, s.Split(Delim)))
CountAdded++;
else
Log.Add("Row is not compatible - " + s);
}//for
Log.Add("Rows inserted - " + CountAdded.ToString());
#endregion
}//try
catch (Exception e)
{
Log.Add(e.Message);
}//catch
finally
{
File.WriteAllLines(Logfile, Log.ToArray());
}//finally
}//function
//
// *************************************************
// **** Try Create() ****
// *************************************************
/// <summary>
/// Try to create a list of BreUserTags objects for each clearing firm found in the file.
/// </summary>
/// <param name="fileName"></param>
/// <param name="createdTags"></param>
/// <returns></returns>
public static bool TryCreate(string fileName, out Dictionary <string, List <BreUserTags> > createdTags, string googleName, string googlePassword, LogHub log)
{
if (Log == null)
{
Log = log;
}
createdTags = null;
// Read the entire file once.
List <string> fileRows = new List <string>();
string filePath = fileName;
try
{
string aLine = string.Empty;
if (!string.IsNullOrEmpty(googleName) && googlePassword != null)
{
SpreadsheetsService myService = new SpreadsheetsService("exampleCo-exampleApp-1");
myService.setUserCredentials(googleName, googlePassword);
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = myService.Query(query);
Log.NewEntry(LogLevel.Minor, "Loading account tags file from the google drive");
foreach (SpreadsheetEntry entry in feed.Entries)
{
if (!entry.Title.Text.Equals(targetFileName))
{
continue;
}
Log.NewEntry(LogLevel.Minor, entry.Title.Text);
WorksheetFeed wsFeed = entry.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = myService.Query(listQuery);
string header = "RCG.number,ABN.number,main,tag,RCG.spoof,ABN.spoof";
fileRows.Add(header);
foreach (ListEntry row in listFeed.Entries)
{
string tagString = string.Empty;
foreach (ListEntry.Custom element in row.Elements)
{
if (tagString != string.Empty)
{
tagString += ",";
}
tagString += element.Value;
//fileRows.Add(element.Value);
}
fileRows.Add(tagString);
}
}
}
else
{
if (!string.IsNullOrEmpty(filePath) && System.IO.File.Exists(filePath))
{
using (System.IO.StreamReader reader = new System.IO.StreamReader(filePath))
{
while ((aLine = reader.ReadLine()) != null)
{
aLine = aLine.Replace("\"", "");
aLine = aLine.Trim();
if (!string.IsNullOrEmpty(aLine))
{
fileRows.Add(aLine);
}
}
reader.Close();
}
}
else
{
Log.NewEntry(LogLevel.Warning, "The filePath is null or empty:{0} and it may not exist on the file server", filePath);
return(false);
}
}
}
catch (Exception e)
{
Log.NewEntry(LogLevel.Warning, "There is an error in loading account tags file, the error information is {0}", e);
return(false);
}
//
// Read headers collecting all clearing firm names.
//
string[] elems = fileRows[0].Split(',');
Dictionary <string, int[]> columnIDs = new Dictionary <string, int[]>();
int[] universalColumnIDs = new int[2];
for (int col = 0; col < elems.Length; ++col)
{
if (elems[col].Contains(".number")) // this is item #0
{
int n = elems[col].IndexOf('.');
string s = elems[col].Substring(0, n).Trim(); // this is a clearing firm name.
if (!columnIDs.ContainsKey(s))
{
columnIDs.Add(s, new int[2]);
}
columnIDs[s][0] = col;
}
else if (elems[col].Contains(".spoof")) // this is item #1
{
int n = elems[col].IndexOf('.');
string s = elems[col].Substring(0, n).Trim(); // this is a clearing firm name.
if (!columnIDs.ContainsKey(s))
{
columnIDs.Add(s, new int[2]);
}
columnIDs[s][1] = col;
}
else if (elems[col].Contains("main"))
{
universalColumnIDs[0] = col;
}
else if (elems[col].Contains("tag"))
{
universalColumnIDs[1] = col;
}
}
// Create return dictionaies, a list of rows for each clearing firm.
createdTags = new Dictionary <string, List <BreUserTags> >(columnIDs.Count);
foreach (string clearingName in columnIDs.Keys)
{
createdTags.Add(clearingName, new List <BreUserTags>());
}
//
// Load each row
//
for (int row = 1; row < fileRows.Count; ++row)
{
elems = fileRows[row].Split(',');
string main = elems[universalColumnIDs[0]].Trim();
string tag = elems[universalColumnIDs[1]].Trim();
foreach (string clearingName in columnIDs.Keys)
{
BreUserTags bre = new BreUserTags(main, tag, elems[columnIDs[clearingName][0]].Trim(), elems[columnIDs[clearingName][1]].Trim());
createdTags[clearingName].Add(bre);
}
}
return(true);
}//TryCreate()
static NameValueCollection getTeamNames(String spreadsheetname, Boolean debug_flag = false)
{
NameValueCollection names = new NameValueCollection();
SpreadsheetsService service = new SpreadsheetsService("GetNames-v0.1");
service.setUserCredentials("*****@*****.**", "kibitzer");
SpreadsheetQuery query = new SpreadsheetQuery();
query.Title = spreadsheetname;
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count > 0)
{
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
if (debug_flag)
{
Console.WriteLine("Found spreadsheet with name " + spreadsheet.Title.Text);
}
AtomLink link = spreadsheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery wquery = new WorksheetQuery(link.HRef.ToString());
wquery.Title = "Info";
WorksheetFeed wfeed = service.Query(wquery);
WorksheetEntry namesSheet = null;
WorksheetEntry infoSheet = null;
if (wfeed.Entries.Count > 0)
{
infoSheet = wfeed.Entries[0] as WorksheetEntry;
}
else
{
String message = "Cannot find sheet titled Info!!!";
if (debug_flag)
{
Console.WriteLine(message);
}
throw new ArgumentNullException(message);
}
wquery.Title = "Names";
wfeed = service.Query(wquery);
if (wfeed.Entries.Count > 0)
{
namesSheet = wfeed.Entries[0] as WorksheetEntry;
}
else
{
String message = "Cannot find sheet titled Names!!!";
if (debug_flag)
{
Console.WriteLine(message);
}
throw new ArgumentNullException(message);
}
int numTeams = 0;
AtomLink listFeedLink = infoSheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery iquery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed ifeed = service.Query(iquery) as ListFeed;
foreach (ListEntry worksheetRow in ifeed.Entries)
{
ListEntry.CustomElementCollection elements = worksheetRow.Elements;
String parameterName = elements[0].Value;
if (parameterName.ToLower() == "Number of Teams".ToLower())
{
numTeams = int.Parse(elements[1].Value);
if (debug_flag)
{
Console.WriteLine("Num Teams = " + numTeams);
}
}
}
AtomLink cellFeedLink = namesSheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery cquery = new CellQuery(cellFeedLink.HRef.ToString());
cquery.ReturnEmpty = ReturnEmptyCells.yes;
cquery.MinimumColumn = 1;
cquery.MaximumColumn = 2;
cquery.MinimumRow = 2;
cquery.MaximumRow = (uint)(numTeams + 1);
CellFeed cfeed = service.Query(cquery);
for (int i = 0; i < cfeed.Entries.Count; i += 2)
{
CellEntry nameCell = cfeed.Entries[i + 1] as CellEntry;
CellEntry numberCell = cfeed.Entries[i] as CellEntry;
names.Add(nameCell.Cell.Value, numberCell.Cell.Value);
}
}
else
{
String message = "No Spreadsheet with name " + spreadsheetname + " could be found!!!";
if (debug_flag)
{
Console.WriteLine(message);
}
throw new ArgumentNullException(message);
}
return(names);
}
public gAccount(string sUser, string sPass)
{
m_oService = new SpreadsheetsService("Generic Spreadsheet-List-Capture");
m_oService.setUserCredentials(sUser + "@gmail.com", sPass);
}
private void ehtpWorker_DoWork(object sender, DoWorkEventArgs e)
{
SpreadsheetsService getAcct = new SpreadsheetsService("GAppADDAutoUserEHTP");
getAcct.setUserCredentials(loginUser.Text, loginPassword.Text);
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed spreadFeed = getAcct.Query(query);
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)spreadFeed.Entries[Convert.ToInt32(e.Argument)];
WorksheetFeed wsFeed = spreadsheet.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];
// No other way to do this loop because Microsoft sucks.
// Full Name
CellQuery fnQuery = new CellQuery(worksheet.CellFeedLink);
fnQuery.MinimumColumn = 2;
fnQuery.MinimumRow = 2;
fnQuery.MaximumColumn = 2;
CellFeed fnFeed = getAcct.Query(fnQuery);
// User Name
CellQuery unQuery = new CellQuery(worksheet.CellFeedLink);
unQuery.MinimumColumn = 8;
unQuery.MinimumRow = 2;
unQuery.MaximumColumn = 8;
CellFeed unFeed = getAcct.Query(unQuery);
// Password
CellQuery pwQuery = new CellQuery(worksheet.CellFeedLink);
pwQuery.MinimumColumn = 7;
pwQuery.MinimumRow = 2;
pwQuery.MaximumColumn = 7;
CellFeed pwFeed = getAcct.Query(pwQuery);
// Managers
CellQuery mgQuery = new CellQuery(worksheet.CellFeedLink);
mgQuery.MinimumColumn = 3;
mgQuery.MinimumRow = 2;
mgQuery.MaximumColumn = 3;
CellFeed mgFeed = getAcct.Query(mgQuery);
// Mailing Lists
CellQuery mlQuery = new CellQuery(worksheet.CellFeedLink);
mlQuery.MinimumColumn = 4;
mlQuery.MinimumRow = 2;
mlQuery.MaximumColumn = 4;
CellFeed mlFeed = getAcct.Query(mlQuery);
// SFA
CellQuery sfaQuery = new CellQuery(worksheet.CellFeedLink);
sfaQuery.MinimumColumn = 5;
sfaQuery.MinimumRow = 2;
sfaQuery.MaximumColumn = 5;
CellFeed sfaFeed = getAcct.Query(sfaQuery);
// Added By
CellQuery abQuery = new CellQuery(worksheet.CellFeedLink);
abQuery.MinimumColumn = 6;
abQuery.MinimumRow = 2;
abQuery.MaximumColumn = 6;
CellFeed abFeed = getAcct.Query(abQuery);
// Timestamp
CellQuery tsQuery = new CellQuery(worksheet.CellFeedLink);
tsQuery.MinimumColumn = 1;
tsQuery.MinimumRow = 2;
tsQuery.MaximumColumn = 1;
CellFeed tsFeed = getAcct.Query(tsQuery);
string[] rowData = new string[9];
// Row count (cast me because I'm special)
int rowCt = (Convert.ToInt32(worksheet.Rows) - 98);
for (int i = 0; i < rowCt; i++)
{
try
{
CellEntry fullName = (CellEntry)fnFeed.Entries[i];
CellEntry passWord = (CellEntry)pwFeed.Entries[i];
CellEntry userName = (CellEntry)unFeed.Entries[i];
CellEntry managerPerson = (CellEntry)mgFeed.Entries[i];
CellEntry mLists = (CellEntry)mlFeed.Entries[i];
CellEntry sfAccess = (CellEntry)sfaFeed.Entries[i];
CellEntry addedBy = (CellEntry)abFeed.Entries[i];
CellEntry timeStamp = (CellEntry)tsFeed.Entries[i];
// this irritates me
rowData[0] = "";
rowData[1] = fullName.Value;
rowData[2] = userName.Value;
rowData[3] = passWord.Value;
rowData[4] = managerPerson.Value;
rowData[5] = mLists.Value;
rowData[6] = sfAccess.Value;
rowData[7] = addedBy.Value;
rowData[8] = timeStamp.Value;
ListViewItem rowLvi = new ListViewItem(rowData);
// cross thread calling ghetto style
this.Invoke(new Action(() => { openSheet.Items.Add(rowLvi); }));
}
catch (Exception oops)
{
string error = oops.ToString();
if (error.Contains("Index was out of range"))
{
CellEntry fullName = (CellEntry)fnFeed.Entries[i];
CellEntry managerPerson = (CellEntry)mgFeed.Entries[i];
CellEntry mLists = (CellEntry)mlFeed.Entries[i];
CellEntry sfAccess = (CellEntry)sfaFeed.Entries[i];
CellEntry addedBy = (CellEntry)abFeed.Entries[i];
CellEntry timeStamp = (CellEntry)tsFeed.Entries[i];
// this irritates me more the second time around
rowData[0] = "";
rowData[1] = fullName.Value;
rowData[2] = "";
rowData[3] = "";
rowData[4] = managerPerson.Value;
rowData[5] = mLists.Value;
rowData[6] = sfAccess.Value;
rowData[7] = addedBy.Value;
rowData[8] = timeStamp.Value;
ListViewItem rowLvi = new ListViewItem(rowData);
// cross thread calling ghetto style
this.Invoke(new Action(() => { openSheet.Items.Add(rowLvi); }));
}
else
{
System.Windows.Forms.MessageBox.Show("The following error has occured: " + oops.ToString(), "Error argh bah no:", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
}
}
}