public static void GetSheet(string getArea, string spreadsheetid) { UserCredential credential; using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) { string credPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData); credential = GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result; Console.WriteLine("Credential file saved to: " + credPath); } // færdig med at gemme // Laver Google Sheets API service. var service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); // Definerer hvad den skal kigge efter i vores sheet String spreadsheetId = spreadsheetid; String getRange = getArea; SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, getRange); ValueRange response = request.Execute(); IList <IList <Object> > values = response.Values; // checker om den celle den der i ikke er tom if (values != null && values.Count > 0) { int i = 0; range = values.Count; // laver en todimensionelle liste om til en enkelt dimensionel array foreach (var row in values) { try { checkedState[i] = values[i][2]; processList[i] = values[i][1]; gamesList[i] = values[i][0]; Console.WriteLine(processList[i]); File.AppendAllText(programPath, values[i][0] + "," + values[i][1] + "," + values[i][2] + ";"); i++; } catch (Exception c) { Console.WriteLine(c.Message); } } } // udskriver hvis der er ikke er noget data i det sheet der er blevet defineret else { Console.WriteLine("No data found."); } }
public static void LoadDatabase() { //DinnCards = JsonConvert.DeserializeObject<List<DinnCard>>(File.ReadAllText("DinnCards.json")); UserCredential credential; Console.WriteLine($"{DateTime.Now} - Updating DINN Card Database from Google Sheets."); using (var stream = new FileStream("credentials.json", FileMode.Open, FileAccess.Read)) { string credPath = "token.json"; 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, }); String spreadsheetId = "1YoOdowodbPv_QGvuvPcwvGA1yqyZjZBmuY0TlbUi56I"; string range = "Master Card List!A:J"; SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range); ValueRange response = request.Execute(); IList <IList <Object> > values = response.Values; try { DinnCards.Clear(); for (int i = 0; i < values.Count; i++) { if (i == 0) { //Skip first row as headers; continue; } try { var card = new DinnCard(); card.Name = values[i][0].ToString(); card.Type = values[i][1].ToString(); var attribute1 = values[i][2].ToString(); if (attribute1.Length > 1) { card.Attribute1 = attribute1; } var attribute2 = values[i][3].ToString(); if (attribute2.Length > 1) { card.Attribute2 = attribute2; } var attribute3 = values[i][4].ToString(); if (attribute3.Length > 1) { card.Attribute3 = attribute3; } var heartValueS = values[i][5].ToString(); var basePowerS = values[i][6].ToString(); if (heartValueS.Length > 0) { card.HeartValue = Int32.Parse(heartValueS); } if (basePowerS.Length > 0) { card.BasePower = Int32.Parse(basePowerS); } card.cardText = values[i][7].ToString(); card.Released = values[i][8].ToString(); card.ImageURL = values[i][9].ToString(); DinnCards.Add(card); } catch (Exception ex) { Console.WriteLine(ex); } } } catch (Exception ex) { Console.WriteLine(ex); } }
// Specifies rows to be added to the Miniature table of the Azure SQL database. /* Parameters: * range1: an int that contains the first row to read. * range2: an int that contains the last row to read. * Output: * Each row is added into the database and a successful attempt is printed to the console. * Returns: * None */ static void AddToMiniBase(SqlConnection connection, int range1, int range2) { // Create a GoogleCrendetial Object so we can use our key to connect to the API and manipulate the Google Sheet. GoogleCredential credential; // Read the google key (json file) to allow us to get credentials for read access. using (var stream = new FileStream("boreal-freedom-312803-75784bd90c97.json", FileMode.Open, FileAccess.Read)) { credential = GoogleCredential.FromStream(stream).CreateScoped(Scopes); } // Takes the credential and application name service = new SheetsService(new Google.Apis.Services.BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = applicationName, }); var range = $"{sheet}!{range1}:{range2}"; var request = service.Spreadsheets.Values.Get(spreadSheetID, range); var response = request.Execute(); var values = response.Values; // Declare the columns of the Miniature table so they can be inserted string miniatureName; string company; string material; int SKU; string sculpter; string genre; int scale; string DnDSize; // Checks if the reponse was null and if there are entries at all. If not, output each row and their their values. if (values != null & values.Count > 0) { foreach (var row in values) { // Each variable must be converted before being saved as they are seen as being a List Object miniatureName = (string)row[0]; company = (string)row[1]; material = (string)row[2]; SKU = Convert.ToInt32((string)row[3]); sculpter = (string)row[4]; genre = (string)row[5]; scale = Convert.ToInt32((string)row[6]); DnDSize = (string)row[7]; // The addstring that is will be submitted to the Azure database. Uses a format string to place all the values into the string. string addString = String.Format(@"INSERT INTO Miniature (mName, mCompany, mMaterial, mSKU, mSculpter, mGenre, mScaleMM, mDnDSize, mIsPainted) VALUES ('{0}', '{1}', '{2}', {3}, '{4}', '{5}', '{6}', '{7}', 0);", miniatureName, company, material, SKU, sculpter, genre, scale, DnDSize); Console.WriteLine(addString); Submit_Tsql_NonQuery(connection, "1-Insert Miniature", addString); } } else { Console.WriteLine("No data found."); } }
public IActionResult SearchPage() { //SpreadsheetsService myService = new SpreadsheetsService("CoinsOrganizer"); //myService.setUserCredentials("*****@*****.**", "POBEDAnotabt9865122879"); //SpreadsheetQuery query = new SpreadsheetQuery(); //SpreadsheetFeed feed = myService.Query(query); //Console.WriteLine("Your spreadsheets: "); //foreach (SpreadsheetEntry entry in feed.Entries) //{ // Console.WriteLine(entry.Title.Text); //} DeleteAllOrders(); string[] Scopes = { SheetsService.Scope.SpreadsheetsReadonly }; string ApplicationName = "Google Sheets API .NET Quickstart"; UserCredential credential; using (var stream = new FileStream("credentials.json", FileMode.Open, FileAccess.Read)) { string credPath = "token.json"; credential = GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result; Console.WriteLine("Credential file saved to: " + credPath); } // Create Google Sheets API service. var service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); // Define request parameters. String spreadsheetId = "13Hh_x_kU9BhBryuIwwGvB6r0PpztjZaVu8y5-5BbeTk"; String range = "'Висилка'!A909:I971"; SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range); // Prints the names and majors of students in a sample spreadsheet: // https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit ValueRange response = request.Execute(); IList <IList <Object> > values = response.Values; if (values != null && values.Count > 0) { //Console.WriteLine("Name, Major"); foreach (var row in values) { OrderModel orders = new OrderModel(); if (row.Count == 9) { var index = int.Parse(row[8].ToString() == "." ? "0" : row[8].ToString()); var title = row[0].ToString(); var paid = row[1].ToString() == "т"; double price = 0d; var isDollar = row[4].ToString().Contains("$"); var isZloty = row[4].ToString().Contains("zł"); //if (row[4].ToString() != "") //{ // price = double.Parse(row[4].ToString().Replace("$.", "").Replace(",", "").Replace(" zł", "")); //} var email = row[3].ToString(); var nickname = row[2].ToString(); double soldprice = 0d; if (row[4].ToString() != "") { soldprice = double.Parse(row[4].ToString().Replace(" zł", "").Replace(",", "").Replace("$", "")); } var trackNumber = row[5].ToString(); var orderdetails = row[6].ToString() + " " + row[7].ToString(); //double dollarPrice = 0d; //if (row[11].ToString() != "") //{ // dollarPrice = double.Parse(row[11].ToString().Replace("$", "").Replace(",", "")); //} //var englishName = row[12].ToString(); //bool isInStock = row[7].ToString() != "#N/A"; //var avers = row[1].ToString(); //var revers = row[2].ToString(); //if (avers == "foto") //{ // avers = string.Empty; //} //if (revers == "foto") //{ // revers = string.Empty; //} orders = new OrderModel() { NickName = nickname, IsPaid = paid, TrackNumber = trackNumber, OrderDetails = orderdetails, Email = email, OrderId = index, Name = title, SalePrice = soldprice, SaleCurrency = isZloty ? "zł" : "$", WhereSold = isZloty ? "Allegro" : "Ebay", Link = string.Empty //CoinId = index, //Name = title, //Cost = price, //Link = link, //ZlotyPrice = allegroPrice, //PolishName = allegroName, //DollarPrice = dollarPrice, //EnglishName = englishName, //IsInStock = isInStock, //AversFotoLink = avers, //ReversFotoLink = revers }; } else { } // Print columns A and E, which correspond to indices 0 and 4. //Console.WriteLine("{0}, {1}", row[0], row[4]); if (orders.Link == null) { } if (orders.OrderId != 1018 && orders.OrderId != 0) { using (var db = new BusinessLogic.BusinessLogic()) db.AddOrder(Mapper.Map <OrderBL>(orders)); } } } //else //{ // Console.WriteLine("No data found."); //} //Console.Read(); return(View()); }
private void SendGoogleExcel(PageData PageData) { string[] Scopes = { SheetsService.Scope.Spreadsheets }; //應用程式的名字需要英文 string ApplicationName = "Get Google SheetData with Google Sheets API"; UserCredential credential; var folder = System.Web.HttpContext.Current.Server.MapPath("/App_Data/MyGoogleStorage"); credential = GoogleWebAuthorizationBroker.AuthorizeAsync( new ClientSecrets { ClientId = "117990626740-rptck4cro3bpbu3u7da3t4qlr20i3rsl.apps.googleusercontent.com", ClientSecret = "zcFr6UCqdX-jo29QFogCcyf1" }, Scopes, "user", CancellationToken.None, new FileDataStore(folder)).Result; // Create Google Sheets API service. var service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName }); // Define request parameters. String spreadsheetId = "106Y2tmI4RV3tJN_Ri4Xc91R3CZ1158GBJstlhfExjew"; //String range = "工作表1!A:B"; String range = "工作表1"; var valueRange = new ValueRange(); var oblist = new List <object>() { DateTime.UtcNow.AddHours(8).ToString("yyyy/MM/dd HH:mm:ss"), //時間 PageData.gcroup, //社青 PageData.group, //CA202.信豪牧區-彥伯小組 PageData.Ename, //流大丹 PageData.Phone, //0919963322 PageData.Gmail, //[email protected] PageData.gender, //男生 PageData.ClothesSize, //S PageData.Course //生命突破 }; valueRange.Values = new List <IList <object> > { oblist }; valueRange.MajorDimension = "Rows"; //Rows or Columns SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(valueRange, spreadsheetId, range); request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED; var appendReponse = request.Execute(); }
public IActionResult PostIndex() { UserCredential credential; string[] Scopes = { SheetsService.Scope.Spreadsheets }; var ClientSecret = new ClientSecrets(); ClientSecret.ClientId = _configuration["installed:client_id"]; ClientSecret.ClientSecret = _configuration["installed:client_secret"]; string credPath = "token.json"; credential = GoogleWebAuthorizationBroker.AuthorizeAsync( ClientSecret, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result; Console.WriteLine("Credential file saved to: " + credPath); var service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = _configuration["ApplicationName"], }); // Puts Names Into SpreadSheet var valueRange = new ValueRange(); string lastName = Request.Form["lastName"]; string firstName = Request.Form["firstName"]; var objectList = new List <object>() { lastName, firstName }; valueRange.Values = new List <IList <object> > { objectList }; var appendRequest = service.Spreadsheets.Values.Append(valueRange, _configuration["SpreadSheetId"], _configuration["Range"]); appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED; appendRequest.Execute(); //Gets Names Put into Spreadsheet SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(_configuration["SpreadSheetId"], _configuration["Range"]); ValueRange response = request.Execute(); IList <IList <object> > values = response.Values; if (values != null && values.Count > 0) { string[,] stringArray = new string[values.Count, values[0].Count]; for (int i = 0; i < values.Count; i++) { IList <object> row = values[i]; for (int j = 0; j < row.Count; j++) { stringArray[i, j] = row[j].ToString(); } } //ViewData["People"] = stringArray; ViewData["PersonLastName"] = lastName; ViewData["PersonFirstName"] = firstName; } return(View()); }
private static void Main(string[] args) { UserCredential credential; using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) { string credPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-import.json"); 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 = "LoFGatekeeper-Import", }); List <Hashtable> items = new List <Hashtable>(); { String spreadsheetId = "1sOtWKPPJKgJo4m6RSg7V5kResfP00eph-cUBuKCrPf8"; var spreadsheet = service.Spreadsheets .Values .Get(spreadsheetId, "Gate Shift Roster!A2:G"); spreadsheet.DateTimeRenderOption = SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum.FORMATTEDSTRING; spreadsheet.ValueRenderOption = SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum.UNFORMATTEDVALUE; var values = spreadsheet.Execute().Values; var enUS = new CultureInfo("en-US"); List <IList <object> > ilists = new List <IList <object> >(); foreach (var item in values) { DateTime temp; if (DateTime.TryParseExact((string)item[0], "dddd, MMMM d", enUS, DateTimeStyles.AssumeLocal, out temp)) { continue; } if ((string)item[0] == "Task" || (string)item[3] == "TBD") { continue; } ilists.Add(item); } Directory.SetCurrentDirectory(Path.Combine("..", "..", "data")); using (var db = new LiteDB.LiteDatabase(@"LoFData.db")) { { var collection = db.GetCollection <VolunteerTimeclockEntry>("volunteerHours"); collection.Delete(row => true); } var shifts = ilists.Select(item => new ScheduledVolunteerShift { Task = $"{item[0]}", Begins = DateTime.ParseExact($"{item[1]}", "MM-dd-yyyy HH:mm", enUS).ToUniversalTime(), Ends = DateTime.ParseExact($"{item[2]}", "MM-dd-yyyy HH:mm", enUS).ToUniversalTime(), PreferredName = $"{item[3]}".ToLowerInvariant(), VolunteerId = (string)item[item.Count - 1], BurnerName = item.Count == 7 ? ((string)item[5]).ToLowerInvariant() : null }).ToList(); // variable isolation { var collection = db.GetCollection <ScheduledVolunteerShift>("volunteerShifts"); collection.Delete(row => true); collection.InsertBulk(shifts); } var volunteers = shifts.GroupBy(arg => arg.VolunteerId) .Select(arg => new Volunteer { Id = arg.Key, ScheduledRefList = arg.Select(inner => inner.Id).ToArray(), PreferredName = arg.Select(inner => inner.PreferredName).First(), BurnerName = arg.Select(inner => inner.BurnerName).First() }); // variable isolation { var collection = db.GetCollection <Volunteer>("volunteers"); collection.Delete(row => true); var attendees = db.GetCollection <Attendee>("attendees"); foreach (var volunteer in volunteers) { var attendee = attendees.FindById(volunteer.Id); if (!string.IsNullOrWhiteSpace(volunteer.BurnerName)) { attendee.BurnerName = volunteer.BurnerName; } try { if (!string.IsNullOrWhiteSpace(volunteer.PreferredName) && attendee.Name.FirstName != volunteer.PreferredName) { attendee.Name.NickName = volunteer.PreferredName; } } catch { Console.WriteLine(JsonConvert.SerializeObject(volunteer, Formatting.Indented)); Console.WriteLine(JsonConvert.SerializeObject(attendee, Formatting.Indented)); } attendees.Update(attendee); } collection.InsertBulk(volunteers); } Console.WriteLine(JsonConvert.SerializeObject(volunteers, Formatting.Indented)); } } Console.WriteLine(); }
public SpreadsheetMapper(SheetsService sheetsService, ILogger <SpreadsheetMapper <TMapTo> > logger) { _sheetsService = sheetsService; _logger = logger; }
public void MyBindSheetData(string sortStr) { int rowCnt = 3; DataTable dt = new DataTable(); // Define the columns of the table. dt.Columns.Add(new DataColumn("Row", typeof(Int32))); dt.Columns.Add(new DataColumn("Location", typeof(String))); dt.Columns.Add(new DataColumn("SKU", typeof(String))); dt.Columns.Add(new DataColumn("QTY", typeof(Double))); SheetsService service = GetServiceAccount(); string range = mySheet + "!A" + rowCnt + ":C"; // I start on A3 because the sheet has blank fields in A1-A2 (throws exception) // string range = mySheet + "!A5:C100"; // alternate selection SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range); ValueRange response = request.Execute(); IList <IList <object> > values = response.Values; // read data until cells are null after last record which throws exception. If any cells in the sheet are blank, an exception is // thrown and reading stops. Make sure the sheet has no blank fields in the data being read. A blank field will stop the reading. // If the read does NOT return all your records, the next record in the list probably has a blank field. if (values != null && values.Count > 0) { foreach (var row in values) { try // try is NOT needed if you limit the range to cells with NO blank fields, A5:C100 instead of A5:C { DataRow dr = dt.NewRow(); dr[0] = rowCnt.ToString(); dr[1] = row[0].ToString(); dr[2] = row[1].ToString(); dr[3] = row[2].ToString(); dt.Rows.Add(dr); rowCnt++; } catch (Exception) { break; // exception thrown for blank cells or EOF } } } else { DataRow dr = dt.NewRow(); dr[0] = 0; dr[1] = "No data found."; dr[2] = "No data found."; dr[3] = 0.0; dt.Rows.Add(dr); } service.Dispose(); DataView dv = new DataView(dt); // use DataView to sort records then bind to the view instead of the table dv.Sort = sortStr; MySheetGrid.DataSource = dv; // MySheetGrid.DataSource = dt; // comment this line out if using the DataView for sorting MySheetGrid.DataBind(); dt.Dispose(); }
static void Main(string[] args) { UserCredential credential; using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) { string credPath = System.Environment.GetFolderPath( System.Environment.SpecialFolder.Personal); credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json"); credential = GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result; Console.WriteLine("Credential file saved to: " + credPath); } // Create Google Sheets API service. var service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); // Define request parameters. String spreadsheetId = "1decwKsk9kd8FqJP5nAVwAcKoaUYvwe9DFAZNEE5gjPQ"; String range = "Ark1!A2:C999"; SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range); // Prints the names and majors of students in a sample spreadsheet: // https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit ValueRange response = request.Execute(); IList <IList <Object> > values = response.Values; int i = 0; if (File.Exists(programPath)) { File.Delete(programPath); } if (values != null && values.Count > 0) { foreach (var row in values) { // Print columns A and E, which correspond to indices 0 and 4. Console.WriteLine("{0}, {1}, {2}", values[i][0], values[i][1], values[i][2]); File.AppendAllText(programPath, values[i][0] + "," + values[i][1] + "," + values[i][2] + ";"); i++; } } else { Console.WriteLine("Error! CODE 2: Could not find data in Sheet or Sheet does not exists"); Console.Read(); } using (WebClient webclient = new WebClient()) { webclient.Credentials = new NetworkCredential("focuslock.dk", "bagebe"); webclient.UploadFile("ftp://ftp.focuslock.dk/ftp/Programs.begeba", programPath); Console.Read(); } }
public static async Task Main(string[] args) { var config = new ConfigurationBuilder() .AddJsonFile("appSettings.json", true, true) .AddUserSecrets <Program>() .Build(); var tableStorage = new AzureTableStorage(config["StorageAccountConnectionString"]); var storageAccount = CloudStorageAccount.Parse(config["StorageAccountConnectionString"]); var globalSearchTermRepository = new GlobalSearchTermRepository(); var searchClient = new SearchServiceClient("sw5esearch", new SearchCredentials(config["SearchKey"])); var clientSecrets = new ClientSecrets { ClientId = config["GoogleApiClientId"], ClientSecret = config["GoogleApiClientSecret"] }; var googleCredential = await GoogleWebAuthorizationBroker.AuthorizeAsync( clientSecrets, Scopes, "user", CancellationToken.None); // Create Google Sheets API service. var sheetsService = new SheetsService(new BaseClientService.Initializer { HttpClientInitializer = googleCredential, ApplicationName = ApplicationName }); var serviceProvider = new ServiceCollection() .AddSingleton <ITableStorage>(tableStorage) .AddSingleton(storageAccount) .AddSingleton(globalSearchTermRepository) .AddSingleton(searchClient) .AddSingleton(sheetsService) .BuildServiceProvider(); var languages = config["Languages"].Split(','); if (!languages.Any()) { Console.WriteLine("No languages found."); return; } foreach (var language in languages) { var languageEnum = Enum.TryParse <Language>(language, true, out var parsedLanguage) ? parsedLanguage : Language.None; if (languageEnum == Language.None) { Console.WriteLine($"Language {language} not supported."); return; } var stringsClass = LocalizationFactory.Get(languageEnum); await ParseContent.Parse(serviceProvider.GetService <ITableStorage>(), serviceProvider.GetService <CloudStorageAccount>(), serviceProvider.GetService <GlobalSearchTermRepository>(), stringsClass, serviceProvider.GetService <SearchServiceClient>()); } }
public IActionResult Names() => Ok(SheetsService.GetSheetsName(GoogleSheetsApi.SpreadSheetId));
private static void UpdatGoogleSheetinBatch(IList <IList <Object> > values, string spreadsheetId, string newRange, SheetsService service) { SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(new ValueRange() { Values = values }, spreadsheetId, newRange); request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS; request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED; Console.WriteLine(newRange); var response = request.Execute(); }
public static bool CreateSheetFile(string pathCredential, string pathToken, string pathSpreadsheet, string spreadsheetId, Dictionary <string, string> dicSheetGet) { //return true; var credential = GetCredential(pathCredential, pathToken); if (credential != null) { if (string.IsNullOrEmpty(pathSpreadsheet)) { throw new Exception("Not found pathSpreadsheet"); } if (string.IsNullOrEmpty(spreadsheetId)) { throw new Exception("Not found spreadsheetId"); } if (dicSheetGet == null || dicSheetGet.Count == 0) { throw new Exception("lstSheetGet is empty"); } Dictionary <string, IList <RowData> > dic = new Dictionary <string, IList <RowData> >(); SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer { HttpClientInitializer = credential, ApplicationName = "Google-SheetsSample/0.1", }); SpreadsheetsResource.GetRequest request = sheetsService.Spreadsheets.Get(spreadsheetId); request.Ranges = new List <string>(); request.IncludeGridData = false; // To execute asynchronously in an async method, replace `request.Execute()` as shown: Spreadsheet response = request.Execute(); var lstSheets = new List <string>(); foreach (var sheet in response.Sheets) { lstSheets.Add(sheet.Properties.Title); } foreach (var item in dicSheetGet) { if (!dic.ContainsKey(item.Key) && lstSheets.Contains(item.Key)) { request.Ranges = new List <string> { item.Key + "!" + item.Value }; request.IncludeGridData = true; // To execute asynchronously in an async method, replace `request.Execute()` as shown: response = request.Execute(); dic.Add(item.Key, response.Sheets[0].Data.FirstOrDefault().RowData); } } if (File.Exists(pathSpreadsheet)) { File.Delete(pathSpreadsheet); } using (var package = new ExcelPackage(new FileInfo(pathSpreadsheet))) { foreach (var item in dic) { ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(item.Key); if (item.Value != null && item.Value.Count > 0) { for (int r = 0; r < item.Value.Count; r++) { var row = item.Value[r]; if (row != null && row.Values != null && row.Values.Count > 0) { for (int c = 0; c < row.Values.Count; c++) { if (row.Values[c] != null && row.Values[c].FormattedValue != null) { worksheet.Cells[r + 1, c + 1].Value = row.Values[c].FormattedValue; } } } } } } package.Save(); } return(true); } else { throw new Exception("Not auth 2.0 google"); } }
//as example, THIS sheet will be red here: https://docs.google.com/spreadsheets/d/1JVtVNq8e9tY-ZiLeqGoccS3Pmu-sT-orernUrh-uLrw/edit?usp=sharing internal int?GetLockedFlags(Character char2check) { if (char2check == null) { throw new ArgumentNullException(nameof(char2check)); } ServiceAccountCredential credential; string serviceAccountEmail = "*****@*****.**"; string privatekey = "-----BEGIN PRIVATE KEY-----\nMIIEvwIBADANBgkqhkiG9w0BAQEFAASCBKkwggSlAgEAAoIBAQDZRimJQ8Mu0g68\noVoiWla/oIYqRUQwuwNf1z5BB7XujD6B3uU9q+op+aF25aXhy4oOXXXLAMZIkRiF\nsevJOlpO8YaXkUsrLvF/JWIX8FpEJqmkPs4HWlqn11/Swm4S744Rv/G/aF72VcF/\ni+qy7qZlU0I3dgbqJKmTJ5Xn2/vVVSAjdBCRHqd7xTDNlrwT47AxngLPxuvuqI62\nBCmA+/o/04+m1DNQj6p4M3ZnvgFNqSrNrskS1E4TCuSQuSEF4Vq3n0HW4h+Sz7lj\nvBFCw1o0iweaXj+odtXC1Am+m9AWALOOsP1ka2F2VZK7leKvLl89oN1noKCFtbog\nxwn7csfPAgMBAAECggEAAODIe38DdfKfSyimn6tLD4YplHuyWLFqXtkKoTTi8uuC\npgeN3CXOKqczT9O3Tkwtcpe2DzRCfP+Y9K78UexcVApf2yYFfFIP6MZOZWqlzQlh\nOGKBisPjDLcR96aD1rsyfiQHeFCYb+hbEhwxW3j9SlP5VYrG9jzmn3K1fIS/Aw/Z\nBOlyotEOq+sBpoSmFYz0bFbpBAd3gALUxAYu5y7NNzDnUB7HNbkJpSURaYwhUPJR\nKvmqsnBATYPNSmqiAskn5emGFKhQ2ZrqaQ6db/NlTL2/ZDtpvc2t93wxiXf+be52\n/KK8AfNgxxCI4KdiDQMmaWdAbzetWiIV/BU+6DezsQKBgQD3wowRzWw/Tk+Sbxqd\n0mm3waVaaJhXmZPudAa5gHrl+eQCEKQzyzKhJeQt4LXtUei1pTlCzQm44yEJTtup\ntE3z3aqjmXDMfioUeKcMGBeVYMEJXtqik9DaSeZq6TBF00X5pzkFsTIUk5Vt2wB3\nbpA+rinNxQD8pJTG+kNTnhF+JQKBgQDggA4oT576CkQA2HGo3iFDCfeAQfdk2kNm\neErONdO+TXUfsH5kogG3kdXzfHL23THi+rr028uR2m2MDfkwZFqVXP4214Bnj3xO\nOG6KGn8JC2mW89dW2NcDDi5+bjDvggD7i0WUz3mnZJepOzWccYIvJXKRGvcFBuZO\nIf6rPwwp4wKBgQCFjlnzPskbVxuN1FaEvOhAJiL8mWWF00PrRqBZXujhD9PZSR86\nzE5+j+5wzLFFPOI1CNvVJrIW+FjWq6u9z3Q2AUf66LvlgB0u69sgqdwMqhtk8bzp\n5sSu5ydOemWLPlh6O6qBZwOYE/Z5QZT+zJr1Lu1Z/tiJWC7bFA03Bf5oAQKBgQCS\nMhIGIkOO5NyACHOL2ouikn0AovSuUoyN0Ew2mUr4pIxfRJoqgm3H00qWszZSmJ0C\nCPFyvyeEJdAs4nSiFNgAaHyLzi6qQgBbF9i0SqjrhOkQCl7zCWaLcNLKNovbjLeD\nF6EKVUoNvi4dYJd691glx6ch44N1XJbhzrV/YyFSrQKBgQDB7VDh0yGMcVK+UY6f\n44WZd0qb4pippOEAerojC9YjdmEs0/lfE4ert6LWIqSMriPu2qzskOPOYVALNLHw\n5vTluydZawLAl4/ZKBN8uSrRamn/RPlnaZtiM5bjYF/lFyUVKtDdI52NnLhpeYUj\ntqcwl6hu0vJLasyCtdsWo7W/gA==\n-----END PRIVATE KEY-----\n"; credential = new ServiceAccountCredential( new ServiceAccountCredential.Initializer(serviceAccountEmail) { Scopes = Scopes, //Use the HttpClientFactory ONLY if you are behind a proxy! //HttpClientFactory = new ProxySupportedHttpClientFactory() }.FromPrivateKey(privatekey)); // Create Google Sheets API service. MySheetsService = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = "Chummer", //Use the HttpClientFactory ONLY if you are behind a proxy! //HttpClientFactory = new ProxySupportedHttpClientFactory() }); // Define request parameters. You get the Id when you open the sheet in the browser. // It is shown in the title bar. String spreadsheetId = "1JVtVNq8e9tY-ZiLeqGoccS3Pmu-sT-orernUrh-uLrw"; String range = "TestSheetName!A1:E"; SpreadsheetsResource.ValuesResource.GetRequest request = MySheetsService.Spreadsheets.Values.Get(spreadsheetId, range); ValueRange response = request.Execute(); IList <IList <Object> > values = response.Values; int returnvalue = 0; if (values != null && values.Count > 0) { Console.WriteLine("Text, Value"); foreach (var row in values) { // Print columns A and E, which correspond to indices 0 and 4. Console.WriteLine("{0}, {1}", row[0], row[2]); } string stringvalue = values[0].ToArray()[1].ToString(); int temp; if (Int32.TryParse(stringvalue, out temp)) { returnvalue = temp; } } else { Console.WriteLine("No data found."); } Console.Read(); return(returnvalue); }
public void Dispose() { Service = null; }
public GDataDBRequestFactory(string ServiceAccountEmail, string ServiceAccountCredentialFilePath) { this.SheetsService = AuthenticateServiceAccount(ServiceAccountEmail, ServiceAccountCredentialFilePath); }
public MyPage(SheetsService service) { Service = service; }
static void Main(string[] args) { /// ?? /// Create Excel File Using C# Console Application //SRART// //Enter message and filename for sample string fileName, Sampletext; Console.Write("Enter File Name :"); fileName = Console.ReadLine(); fileName = "FinancialAccountInfo"; Console.Write("Enter text :"); Sampletext = Console.ReadLine(); //Create Excel Application sample object Excel.Application xlSamp = new Microsoft.Office.Interop.Excel.Application(); //Check if Excel is installed if (xlSamp == null) { Console.WriteLine("Excel is not Insatalled"); Console.ReadKey(); return; } //Create a new excel book and sheet Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; //Then add a sample text into first cell xlWorkBook = xlSamp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 2] = Sampletext; //Save the opened excel book to custom location. Dont forget, you have to add to exist location and you cant add to directly C: root. string location = @"\\shadowdc01\m$\Accounting\Dashboard Report\" + fileName + ".xls"; xlWorkBook.SaveAs(location, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlSamp.Quit(); //This is Importent for free memory and excel file. //release Excel Object try { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSamp); xlSamp = null; } catch (Exception ex) { xlSamp = null; Console.Write("Error " + ex.ToString()); } finally { GC.Collect(); } /// ?? /// Create Excel File Using C# Console Application //END// /* * ////** Syncfusion.XlsIO.WinForms * //Create an instance of ExcelEngine * using (ExcelEngine excelEngine = new ExcelEngine()) * { * * //Set the default application version as Excel 2016 * //excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013; * * /// ?? /// Creating a Hello World sample //SRART// * * //New instance of ExcelEngine is created equivalent to launching Microsoft Excel with no workbooks open * //Instantiate the spreadsheet creation engine * //using (ExcelEngine excelEngine = new ExcelEngine()) * //{ * //Instantiate the Excel application object * IApplication application = excelEngine.Excel; * * //Assigns default application version * application.DefaultVersion = ExcelVersion.Excel2013; * * //A new workbook is created equivalent to creating a new workbook in Excel * //Create a workbook with 1 worksheet * IWorkbook workbook = application.Workbooks.Create(1); * * //Access first worksheet from the workbook * IWorksheet worksheet = workbook.Worksheets[0]; * * //Adding text to a cell * worksheet.Range["A2"].Text = "Hello World 4"; * * //Saving the workbook to disk in XLSX format * workbook.SaveAs("Sample2.xlsx"); * * //Closing the workbook * workbook.Close(); * * //Dispose the Excel engine * excelEngine.Dispose(); * //} * * /// ?? /// Creating a Hello World sample //END// * * * } */ UserCredential credential; using (var stream = new FileStream("credentials.json", FileMode.Open, FileAccess.Read)) { // The file token.json stores the user's access and refresh tokens, and is created // automatically when the authorization flow completes for the first time. string credPath = "token.json"; credential = GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result; Console.WriteLine("Credential file saved to: " + credPath); } // Create Google Sheets API service. var service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); // Define request parameters. //String spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"; String spreadsheetId = "1Aip4X4PFZ58it31tcmswoRvfd65sLSAB37JSik7WbOs"; //String range = "Class Data!A2:E"; String range = "Financial Acc Statements!S2:X3"; SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range); // Prints the names and majors of students in a sample spreadsheet: // https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit ValueRange response = request.Execute(); IList <IList <Object> > values = response.Values; if (values != null && values.Count > 0) { Console.WriteLine("AccountsReceivable\tAccountsPayable\tWeeklyDeposits\tCashOnHand\tCreditLineBalance\tProductionHoursWorked"); foreach (var row in values) { // Print columns A and E, which correspond to indices 0 and 5. Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}\t{5}", row[0], row[1], row[2], row[3], row[4], row[5]); } } else { Console.WriteLine("No data found."); } Console.Read(); }
private void ImportDayLog(SheetsService service) { Message("Day log"); string spreadsheetId = System.Configuration.ConfigurationManager.AppSettings["DailySpreadsheetId"]; string inputPath = System.Configuration.ConfigurationManager.AppSettings["DailyFile"]; Message(string.Format("Processing day file {0}", inputPath)); string sheetName = "dayfile"; string range = sheetName + "!A:AT"; var spreadsheet = service.Spreadsheets.Get(spreadsheetId).Execute(); var sheet = spreadsheet.Sheets.FirstOrDefault(x => x.Properties.Title == sheetName); if (sheet == null) { sheet = GoogleSheetUtils.CreateSheet(service, spreadsheetId, sheetName, ref spreadsheet); } if (sheet == null) { Message(string.Format("Unable to get sheet {0}", sheetName)); return; } string inputData; using (var stream = new FileStream(inputPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) using (var reader = new StreamReader(stream)) { inputData = reader.ReadToEnd(); reader.Close(); } if (string.IsNullOrEmpty(inputData)) { Message(string.Format("File empty!")); return; } var inputLines = inputData.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries); Message(string.Format("Loaded lines {0}", inputLines.Length)); var rangeValues = service.Spreadsheets.Values.Get(spreadsheetId, range).Execute(); Message(string.Format("Spreadsheets.Values.Get")); var rangeLines = 0; if (rangeValues.Values != null) { rangeLines = rangeValues.Values.Count; } Message(string.Format("Range lines {0}", rangeLines)); var newData = new List <IList <object> >(); for (int i = rangeLines; i < inputLines.Length; i++) { var splitedInputLine = inputLines[i].Split(";".ToCharArray()); var date = DateTime.ParseExact(splitedInputLine[0], "dd.MM.yy", CultureInfo.InvariantCulture); // var time = DateTime.ParseExact(splitedInputLine[1], "HH:mm", CultureInfo.InvariantCulture); // var timeOnly = time - time.Date; //var values = splitedInputLine.Skip(2).Select(x => decimal.Parse(x)).Cast<object>().ToList(); var values = splitedInputLine.Skip(1).Select(x => x).Cast <object>().ToList(); var data = new List <object>(); data.Add(splitedInputLine[0]); data.Add(decimal.Parse(splitedInputLine[1])); data.Add(decimal.Parse(splitedInputLine[2])); data.Add(splitedInputLine[3]); data.Add(decimal.Parse(splitedInputLine[4])); data.Add(splitedInputLine[5]); data.Add(decimal.Parse(splitedInputLine[6])); data.Add(splitedInputLine[7]); data.Add(decimal.Parse(splitedInputLine[8])); data.Add(splitedInputLine[9]); data.Add(decimal.Parse(splitedInputLine[10])); data.Add(splitedInputLine[11]); data.Add(decimal.Parse(splitedInputLine[12])); data.Add(splitedInputLine[13]); data.Add(decimal.Parse(splitedInputLine[14])); data.Add(decimal.Parse(splitedInputLine[15])); data.Add(decimal.Parse(splitedInputLine[16])); data.Add(decimal.Parse(splitedInputLine[17])); data.Add(splitedInputLine[18]); data.Add(decimal.Parse(splitedInputLine[19])); data.Add(splitedInputLine[20]); data.Add(decimal.Parse(splitedInputLine[21])); data.Add(splitedInputLine[22]); data.Add(decimal.Parse(splitedInputLine[23])); data.Add(decimal.Parse(splitedInputLine[24])); data.Add(decimal.Parse(splitedInputLine[25])); data.Add(splitedInputLine[26]); data.Add(decimal.Parse(splitedInputLine[27])); data.Add(splitedInputLine[28]); data.Add(decimal.Parse(splitedInputLine[29])); data.Add(splitedInputLine[30]); data.Add(decimal.Parse(splitedInputLine[31])); data.Add(splitedInputLine[32]); data.Add(decimal.Parse(splitedInputLine[33])); data.Add(splitedInputLine[34]); data.Add(decimal.Parse(splitedInputLine[35])); data.Add(splitedInputLine[36]); data.Add(decimal.Parse(splitedInputLine[37])); data.Add(splitedInputLine[38]); data.Add(decimal.Parse(splitedInputLine[39])); data.Add(decimal.Parse(splitedInputLine[40])); data.Add(decimal.Parse(splitedInputLine[41])); data.Add(decimal.Parse(splitedInputLine[42])); data.Add(splitedInputLine[43]); data.Add(decimal.Parse(splitedInputLine[44])); data.Add(splitedInputLine[45]); //data.Add(splitedInputLine[1]); //data.AddRange(values); newData.Add(data); } if (newData.Count > 0) { Message("Sending..."); ValueRange vr = new ValueRange(); vr.Values = newData; SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(vr, spreadsheetId, range); request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS; request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW; var response = request.Execute(); Message(string.Format("DONE")); } else { Message(string.Format("No new data.")); } }
public void getQuestion() { UserCredential credential; using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) { string credPath = System.Environment.GetFolderPath( System.Environment.SpecialFolder.Personal); credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json"); credential = GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result; //Console.WriteLine("Credential file saved to: " + credPath); } // Create Google Sheets API service. var service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); // Define request parameters. /* * String spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"; * String range = "Class Data!A1:k"; */ String spreadsheetId = "1FXcRFitIAiWQ_qEmnP0O2nLjFt3PHt_YRFOf_dlKZtg"; //https://docs.google.com/spreadsheets/d/1FXcRFitIAiWQ_qEmnP0O2nLjFt3PHt_YRFOf_dlKZtg/edit#gid=0 //String range = "Class Data!A2:E"; String range = "Sheet1!A1:M"; SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range); // Prints the names and majors of students in a sample spreadsheet: // https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit ValueRange response = request.Execute(); IList <IList <Object> > values = response.Values; if (values != null && values.Count > 0) { bool breakLoop = false; // Console.WriteLine("Date, Time, End Date, End Time, Question, AnswerA, AnswerB, AnswerC, AnswerD, CorrectAnswer, FollowupMessage"); //public DateTime startDate; //public int startHour; //public int startMinutes; //public DateTime endDate; //public int endHour; //public int endMinutes; //public string questionText = null; //public string answer1 = null; //public string answer2 = null; //public string answer3 = null; //public string answer4 = null; //public string correctAnswer = null; //public string followUpMessage = null; //start at 1 to skip header row for (int i = 1; i < values.Count; i++) { var row = values[i]; //// Print columns A to k, which correspond to indices 0 and 4. //Console.WriteLine("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}" // , row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10]); if (breakLoop == false) { //public DateTime startDate; //public int startHour; //public int startMinutes; //public DateTime endDate; //public int endHour; //public int endMinutes; startDate = DateTime.Parse((string)row[0]); startHour = Convert.ToInt32(row[1]); startMinutes = Convert.ToInt32(row[2]); endDate = DateTime.Parse((string)row[3]); endHour = Convert.ToInt32(row[4]); endMinutes = Convert.ToInt32(row[5]); DateTime startDateTime = new DateTime(startDate.Year, startDate.Month, startDate.Day, startHour, startMinutes, 0); DateTime endDateTime = new DateTime(endDate.Year, endDate.Month, endDate.Day, endHour, endMinutes, 0); if (DateTime.Now > startDateTime && DateTime.Now <= endDateTime) { breakLoop = true; //public string questionText = null; //public string answer1 = null; //public string answer2 = null; //public string answer3 = null; //public string answer4 = null; //public string correctAnswer = null; //public string followUpMessage = null; questionText = Convert.ToString(row[6]); answer1 = Convert.ToString(row[7]); answer2 = Convert.ToString(row[8]); answer3 = Convert.ToString(row[9]); answer4 = Convert.ToString(row[10]); correctAnswer = Convert.ToString(row[11]); followUpMessage = Convert.ToString(row[12]); } } } } else { Console.WriteLine("No data found."); } //Console.Read(); //return values; }
private void ImportMainLog(SheetsService service) { Message("Main log"); string spreadsheetId = System.Configuration.ConfigurationManager.AppSettings["spreadsheetId"]; string inputPath = System.Configuration.ConfigurationManager.AppSettings["InputPath"]; string filePrefix = System.Configuration.ConfigurationManager.AppSettings["FilePrefix"]; int sheetIdFrom = int.Parse(System.Configuration.ConfigurationManager.AppSettings["From"]); int sheetIdTo = int.Parse(System.Configuration.ConfigurationManager.AppSettings["To"]); Message(string.Format("From {0} to {1}", sheetIdFrom, sheetIdTo)); var files = System.IO.Directory.EnumerateFiles(inputPath, filePrefix + "*.txt"); Message(string.Format("Found {0} in {1}", files.Count(), inputPath + filePrefix)); foreach (var file in files) { Message(string.Format("Processing {0}", file)); System.IO.FileInfo fi = new FileInfo(file); string sheetName = fi.Name.Replace(filePrefix, "").Replace(".txt", ""); int sheetId = int.Parse(sheetName); if (sheetId < sheetIdFrom || sheetId > sheetIdTo) { continue; } string range = sheetName + "!A:AR"; var spreadsheet = service.Spreadsheets.Get(spreadsheetId).Execute(); var sheet = spreadsheet.Sheets.FirstOrDefault(x => x.Properties.Title == sheetName); if (sheet == null) { sheet = GoogleSheetUtils.CreateSheet(service, spreadsheetId, sheetName, ref spreadsheet); } if (sheet == null) { Message(string.Format("Unable to get sheet {0}", sheetName)); continue; } Message(string.Format("Using sheet {0}", sheetName)); string inputData; using (var stream = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) using (var reader = new StreamReader(stream)) { inputData = reader.ReadToEnd(); reader.Close(); } if (string.IsNullOrEmpty(inputData)) { Message(string.Format("File empty!")); continue; } var inputLines = inputData.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries); Message(string.Format("Loaded lines {0}", inputLines.Length)); var rangeValues = service.Spreadsheets.Values.Get(spreadsheetId, range).Execute(); Message(string.Format("Spreadsheets.Values.Get")); var rangeLines = 0; if (rangeValues.Values != null) { rangeLines = rangeValues.Values.Count; } Message(string.Format("Range lines {0}", rangeLines)); var newData = new List <IList <object> >(); for (int i = rangeLines; i < inputLines.Length; i++) { var splitedInputLine = inputLines[i].Split(";".ToCharArray()); var date = DateTime.ParseExact(splitedInputLine[0], "dd.MM.yy", CultureInfo.InvariantCulture); var time = DateTime.ParseExact(splitedInputLine[1], "HH:mm", CultureInfo.InvariantCulture); var timeOnly = time - time.Date; var values = splitedInputLine.Skip(2).Select(x => decimal.Parse(x)).Cast <object>().ToList(); var data = new List <object>(); data.Add(splitedInputLine[0]); data.Add(splitedInputLine[1]); data.AddRange(values); newData.Add(data); } if (newData.Count > 0) { Message("Sending..."); ValueRange vr = new ValueRange(); vr.Values = newData; SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(vr, spreadsheetId, range); request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS; request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW; var response = request.Execute(); Message(string.Format("DONE")); } else { Message(string.Format("No new data.")); } } }
private void Button1_Click(object sender, EventArgs e) { UserCredential credential; using (var stream = new FileStream("credentials.json", FileMode.Open, FileAccess.Read)) { // The file token.json stores the user's access and refresh tokens, and is created // automatically when the authorization flow completes for the first time. string credPath = "token.json"; credential = GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result; Console.WriteLine("Credential file saved to: " + credPath); } // Create Google Sheets API service. var service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range); // Prints the columns in a sample spreadsheet: ValueRange response = request.Execute(); values = response.Values; var Username = System.Security.Principal.WindowsIdentity.GetCurrent().Name; Username = Username.Substring(Username.Length - 3); if (values != null && values.Count > 0) { int i = 0; foreach (var row in values) { // Print columns A and E, which correspond to indices 0 and 1. if (row[1].Equals(Username)) { MessageBox.Show("Welcome " + row[2] + " " + Username); this.Close(); // switch (row[2]) // { // case "Admin": Console.WriteLine(row[2].ToString()); formPopup frmPopup = new formPopup(); frmPopup.UserRole = row[2].ToString(); frmPopup.Show(); frmPopup.Text = Username; break; // } } i++; Console.WriteLine("{0}, {1}", row[0], row[1]); } if (i == 0) { MessageBox.Show("Your profile is not found"); this.Close(); } } else { Console.WriteLine("No data found.");; } // using (PrincipalContext pc = new PrincipalContext(ContextType.Domain)) // if (pc.ValidateCredentials("zhj", "autumn19")) // { // Debug.WriteLine("Validated"); // /* Check group membership */ // } }
public BatchRequestBuilder(SheetsService service, string spreadsheetId) { this.Requests = new List <Request>(); this.service = service; this.spreadsheetId = spreadsheetId; }
static void Main(string[] args) { string competition; Console.WriteLine("Evaluate Premier League(0) or UEFA(1)?"); var input = Console.ReadLine(); var columnRange = "G"; var onlyTables = false; var lastIndex = 0; var firstIndex = 0; if (input == "1") { competition = UEFA; Console.WriteLine("What is the index of last match?"); lastIndex = Int32.Parse(Console.ReadLine()); } else if (input == "0") { competition = PremierLeague; Console.WriteLine("Do you only want to generate tables? (y)"); if (Console.ReadLine() == "y") { columnRange = "F"; onlyTables = true; } Console.WriteLine("What is the index of last match?"); lastIndex = Int32.Parse(Console.ReadLine()); Console.WriteLine("What is the index of the first team in the first table?"); firstIndex = Int32.Parse(Console.ReadLine()); } else { return; } UserCredential credential; using (var stream = new FileStream("credentials.json", FileMode.Open, FileAccess.Read)) { // The file token.json stores the user's access and refresh tokens, and is created // automatically when the authorization flow completes for the first time. string credPath = "token.json"; credential = GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result; Console.WriteLine("Credential file saved to: " + credPath); } // Create Google Sheets API service. var service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); // Define request parameters. //String range = "Premier League 2018/19!B2:G10"; String range = $"{competition}!B2:{columnRange}{lastIndex}"; SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(Sheet, range); ValueRange response = request.Execute(); IList <IList <Object> > values = response.Values; if (values != null && values.Count > 0) { Table loksi = new Table("Loksi"); Table pato = new Table("Pato"); Table holo = new Table("Holo"); var points = new int[3]; foreach (var row in values) { if (onlyTables) { Match match = new Match(row[0].ToString(), row[1].ToString(), row[4].ToString(), row[2].ToString(), row[3].ToString()); loksi.AddMatch(match.GetTeams()[0], match.GetTeams()[1], match.GetPickById(0)[0], match.GetPickById(0)[1]); pato.AddMatch(match.GetTeams()[0], match.GetTeams()[1], match.GetPickById(2)[0], match.GetPickById(2)[1]); holo.AddMatch(match.GetTeams()[0], match.GetTeams()[1], match.GetPickById(1)[0], match.GetPickById(1)[1]); } else { if (!CheckMatchValidity(row)) { Console.WriteLine(row[0] + " | " + row[1]); continue; } Match match = new Match(row[0].ToString(), row[1].ToString(), row[4].ToString(), row[2].ToString(), row[3].ToString(), row[5].ToString()); var eval = match.Evaluate(); points[0] += eval[0]; points[1] += eval[1]; points[2] += eval[2]; Console.WriteLine(match.ToString() + " || " + eval[0] + " | " + eval[1] + " | " + eval[2]); } } if (onlyTables) { loksi.Sort(); loksi.ShowTable(); WriteTable(service, loksi, firstIndex); Console.WriteLine(); holo.Sort(); holo.ShowTable(); WriteTable(service, holo, firstIndex + 22); Console.WriteLine(); pato.Sort(); pato.ShowTable(); WriteTable(service, pato, firstIndex + 44); } else { Console.WriteLine("Loksi: {0}, Holo: {1}, Pato: {2}", points[0], points[1], points[2]); } } else { Console.WriteLine("No data found."); } Console.Read(); }
public static List <UserDateProgrammingPojo> getDataFromGoogleSheetAPI() { List <UserDateProgrammingPojo> extractedData = null; try { // If modifying these scopes, delete your previously saved credentials // at ~/.credentials/sheets.googleapis.com-dotnet-quickstart.json string[] Scopes = { SheetsService.Scope.SpreadsheetsReadonly }; string ApplicationName = "Google Sheets API .NET Quickstart"; UserCredential credential; using (var stream = new FileStream(FileUtil.credentialsJSONPath, FileMode.Open, FileAccess.Read)) { string credPath = FileUtil.credPathForGoogleSheetApi; credential = GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result; Console.WriteLine("Credential file saved to: " + credPath); } // Create Google Sheets API service. var service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); // Define request parameters. String spreadsheetId = "1CXbZGgYEiUqolQgACSFocOqWoC1kGn_0FadAE35r4Q4"; StringBuilder range = new StringBuilder(); range.Append(getSheetName()); range.Append("!B4:K7"); SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range.ToString()); // Prints the names and majors of students in a sample spreadsheet: // https://docs.google.com/spreadsheets/d/1CXbZGgYEiUqolQgACSFocOqWoC1kGn_0FadAE35r4Q4/edit ValueRange response = request.Execute(); IList <IList <Object> > values = response.Values; if (values != null && values.Count > 0) { int count = 0; extractedData = new List <UserDateProgrammingPojo>(); foreach (var row in values) { count = 0; UserDateProgrammingPojo currentUser = new UserDateProgrammingPojo(); foreach (var currentValue in row) { count++; if (count % 2 == 0) { Console.WriteLine("User="******"Data=" + currentValue); currentUser.Date = currentValue.ToString(); } } } } return(extractedData); } catch (Exception ex) { HandlerErrorsUtil.handlerErrorFromGoogleAPI(ex.Message); return(null); } }
private void ResortSpreadsheet(SheetsService service) { var sheetId = GetSheetIdForTitle(service, "Pixels"); BatchUpdateSpreadsheetRequest reqbody = new BatchUpdateSpreadsheetRequest(); var sortReq = new Request() { SortRange = new SortRangeRequest() { Range = new GridRange() { StartColumnIndex = 0, EndColumnIndex = 12, StartRowIndex = 3, SheetId = sheetId }, SortSpecs = new List <SortSpec>() { new SortSpec() { DimensionIndex = 0, SortOrder = "ASCENDING" } } } }; var formatReq = new Request() { RepeatCell = new RepeatCellRequest() { Cell = new CellData() { UserEnteredFormat = new CellFormat() { HorizontalAlignment = "CENTER" } }, Range = new GridRange() { StartColumnIndex = 1, EndColumnIndex = 2, StartRowIndex = 3, SheetId = sheetId }, Fields = "userEnteredFormat(horizontalAlignment)" } }; IList <Request> requests = new List <Request> { sortReq, formatReq }; reqbody.Requests = requests; SpreadsheetsResource.BatchUpdateRequest sortreq = service.Spreadsheets.BatchUpdate(reqbody, Spreadsheet); sortreq.Execute(); }
public GoogleSheetsAdapter(SheetsService sheetsService, IPlanService planService, IHttpContextAccessor httpContextAccessor) { _sheetsService = sheetsService; _planService = planService; _httpContextAccessor = httpContextAccessor; }
private static void UpdateGoogleSheetInBatch(IList <IList <Object> > values, string spreadsheetId, string newRange, SheetsService service) { SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(new ValueRange() { Values = values }, spreadsheetId, newRange); request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS; request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW; request.Execute(); }
private bool preWork(ref SheetsService service, ref string outputFileName, BackgroundWorker worker, DoWorkEventArgs e, ref int pc) { if (worker.CancellationPending) { e.Cancel = true; } worker.ReportProgress(pc++, "Checking if DB file exists..."); if (!File.Exists(Settings.Default.AccessDbPath)) { string errorText = string.Format("DB file {0} not found!", Settings.Default.AccessDbPath); worker.ReportProgress(0, errorText); LogWriter.Log(errorText); return(false); } if (worker.CancellationPending) { e.Cancel = true; } worker.ReportProgress(pc++, "Checking if output folder exists (if not, trying to create one)..."); try { checkOutputFolder(); } catch (Exception ex) { worker.ReportProgress(0, string.Format( StartFormMessages.Default.CannotCreateOutputFolder, Settings.Default.OutputPath)); LogWriter.Log(ex); return(false); } if (worker.CancellationPending) { e.Cancel = true; } worker.ReportProgress(pc++, "Checking if output folder is writable..."); if (!dirIsWritable(Settings.Default.OutputPath)) { string errorText = string.Format( "Output directory {0} is not writable", Settings.Default.OutputPath); worker.ReportProgress(0, errorText); LogWriter.Log(errorText); return(false); } if (worker.CancellationPending) { e.Cancel = true; } worker.ReportProgress(pc++, "Creating a file in the output folder for the data verify progress logging..."); outputFileName = createFileName( Settings.Default.DataVerifyFileNameFormat, "DataVerifyFileNameFormat", ".txt"); if (outputFileName == string.Empty) { return(false); } if (worker.CancellationPending) { e.Cancel = true; } worker.ReportProgress(pc++, "Attempting to authorize online and create the GoogleSheets service..."); // create service try { service = GSheetsConnector.Connect(); } catch (Exception ex) { worker.ReportProgress(0, "Cannot create Sheets Service"); LogWriter.Log(ex); return(false); } return(true); }