public void Dispose() { if (_service != null) { _service.Dispose(); } }
private void OnConfigurationChange(BotConfiguration newConfiguration, string value) { SheetsService oldService = this.Service; this.InitializeService(); oldService?.Dispose(); }
private bool disposedValue = false; // To detect redundant calls protected virtual void Dispose(bool disposing) { if (!disposedValue) { if (disposing) { service.Dispose(); } disposedValue = true; } }
void Dispose(bool disposing) { if (IsDisposed) { return; } if (disposing) { _service?.Dispose(); _service = null; _credents = null; IsDisposed = true; } }
// removes or clears a row depending on the deleteRows switch setting private void DeleteEntry(string row) { SheetsService service = GetServiceAccount(); if (deleteRows) { /* this code deletes the record (row) completely */ Request RequestBody = new Request() { DeleteDimension = new DeleteDimensionRequest() { Range = new DimensionRange() { SheetId = mySheetID, Dimension = "ROWS", StartIndex = Convert.ToInt32(row) - 1, EndIndex = Convert.ToInt32(row) } } }; List <Request> RequestContainer = new List <Request>(); RequestContainer.Add(RequestBody); BatchUpdateSpreadsheetRequest DeleteRequest = new BatchUpdateSpreadsheetRequest(); DeleteRequest.Requests = RequestContainer; // service.Spreadsheets.BatchUpdate(DeleteRequest, spreadsheetId); SpreadsheetsResource.BatchUpdateRequest Deletion = new SpreadsheetsResource.BatchUpdateRequest(service, DeleteRequest, spreadsheetId); Deletion.Execute(); /* end of delete code */ } else { /* this code clears the entry */ var range = mySheet + "!A" + row + ":F" + row; var requestBody = new ClearValuesRequest(); var deleteRequest = service.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range); var deleteReponse = deleteRequest.Execute(); } service.Dispose(); }
private bool disposedValue = false; // To detect redundant calls protected virtual void Dispose(bool disposing) { if (!disposedValue) { if (disposing) { service.Dispose(); } // TODO: free unmanaged resources (unmanaged objects) and override a finalizer below. // TODO: set large fields to null. service = null; disposedValue = true; } }
/* * private void ReadSheet() * { * int rowCnt = 3; * * SheetsService service = GetServiceAccount(); * // string range = "Inventory By Location!A" + rowCnt + ":C50"; * string range = mySheet + "!A3: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; * * System.Text.StringBuilder HTMLtable = new System.Text.StringBuilder(); * HTMLtable.AppendLine("<table style=\"width:500px; padding:5px;\">"); * HTMLtable.AppendLine("<tr><th>Row</th><th>Location</th><th>SKU</th><th>QTY</th></tr>"); * // 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 * { * HTMLtable.AppendLine("<tr><td>" + rowCnt++ + "</td><td>" + row[0] + "</td><td>" + row[1] + "</td><td>" + row[2] + "</td></tr>"); * } * catch (Exception) * { * break; //stop reading * } * } * } * else * { * HTMLtable.AppendLine("<tr><th>No data found.</th></tr>"); * } * * HTMLtable.AppendLine("</table>"); * MyMessage.Text = HTMLtable.ToString(); * * service.Dispose(); * } */ // Updates a google sheet cell (SheetField), with a new value, eg. UpdateEntry("A0","my value") private void UpdateEntry(string SheetField, string newVal) { SheetsService service = GetServiceAccount(); var range = mySheet + "!" + SheetField; var valueRange = new ValueRange(); var oblist = new List <object>() { newVal }; valueRange.Values = new List <IList <object> > { oblist }; var updateRequest = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range); updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED; var appendReponse = updateRequest.Execute(); service.Dispose(); }
// creates a new row on a google spreadsheet private void CreateEntry() { SheetsService service = GetServiceAccount(); var range = mySheet + "!A:F"; var valueRange = new ValueRange(); var oblist = new List <object>() { LocationBox.Text, SKUbox.Text, QBox.Text, "inserted", "via", "C#" }; valueRange.Values = new List <IList <object> > { oblist }; var appendRequest = service.Spreadsheets.Values.Append(valueRange, spreadsheetId, range); appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED; var appendReponse = appendRequest.Execute(); service.Dispose(); }
public static void Reload() { service?.Dispose(); timer?.Dispose(); if (!Config.sheetsEnabled) { return; } UserCredential credential; using (FileStream stream = new FileStream("credentials.json", FileMode.Open, FileAccess.Read)) { credential = GoogleWebAuthorizationBroker.AuthorizeAsync(GoogleClientSecrets.Load(stream).Secrets, scopes, "SupportBoi", CancellationToken.None, new FileDataStore("token.json", true)).Result; Console.WriteLine("Google credential file saved to 'token.json'"); } // Create Google Sheets API service. service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = "SupportBoi" }); SpreadsheetsResource.GetRequest request = service.Spreadsheets.Get(Config.spreadsheetID); Spreadsheet spreadsheet = request.Execute(); if (spreadsheet == null) { Console.WriteLine(); throw new ArgumentException("ERROR: Could not find a Google Sheets Spreadsheet with provided ID."); } timer = new Timer(RunJobs, null, 1000, Timeout.Infinite); }
private void doDBUpdateWork(BackgroundWorker worker, DoWorkEventArgs e) { int pc = 1; // progress count SheetsService service = null; string outputFileName = string.Empty; bool preWorkResult = preWork(ref service, ref outputFileName, worker, e, ref pc); if (!preWorkResult) { return; } Spreadsheets.Operator[] operators = SpreadsheetsData.Operators; Spreadsheets.SheetsRanges allRanges = SpreadsheetsData.AllRanges; StringBuilder sb = new StringBuilder(); sb.AppendFormat(StartFormMessages.Default.DataVerifyOutputHeaderFormat, DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString()); sb.AppendLine(); if (worker.CancellationPending) { e.Cancel = true; } worker.ReportProgress(pc++, "Creating DB update report file..."); string reportFileName = createFileName( Settings.Default.UpdateReportFileNameFormat, "UpdateReportFileNameFormat", ".txt"); if (reportFileName == string.Empty) { return; } if (worker.CancellationPending) { e.Cancel = true; } worker.ReportProgress(pc++, "Creating DB file backup..."); // create db backup if (Settings.Default.CreateDBBackup) { string dbBackupFileName = createFileName( Settings.Default.DBBackupFilenameFormat, "DBBackupFilenameFormat", Path.GetExtension(Path.GetFileName(Settings.Default.AccessDbPath))); if (dbBackupFileName == string.Empty) { return; } try { File.Copy(Settings.Default.AccessDbPath, dbBackupFileName, true); } catch (Exception ex) { worker.ReportProgress(0, string.Format( StartFormMessages.Default.UnableCreateDBCopy, Settings.Default.AccessDbPath, dbBackupFileName)); LogWriter.Log(ex); return; } } // do the main job and write processing info file if (worker.CancellationPending) { e.Cancel = true; } worker.ReportProgress(pc++, "Starting the data update work:"); try { ControllerUpdate.DoControll(service, operators, allRanges, sb); } catch (DbUpdateException dbuEx) { worker.ReportProgress(0, StartFormMessages.Default.RollbackFailureError); LogWriter.Log(dbuEx); } catch (Exception ex) { string errorMessage = StartFormMessages.Default.UnexpectedExceptionHeader + ex.Message; worker.ReportProgress(0, errorMessage); sb.AppendLine(errorMessage); LogWriter.Log(ex); } finally { service.Dispose(); using (StreamWriter sw = new StreamWriter(outputFileName)) { try { sw.Write(sb); } catch (Exception ex) { worker.ReportProgress(0, string.Format( StartFormMessages.Default.UnableWriteUpdateInfoFile, outputFileName) + ex.Message); LogWriter.Log(ex); } } } // create report StringBuilder sbReport = ReportCreator.CreateTxt( ControllerUpdate.GetInspections()); using (StreamWriter sw = new StreamWriter(reportFileName)) { try { sw.Write(sbReport); } catch (Exception ex) { worker.ReportProgress(0, string.Format( StartFormMessages.Default.UnableWriteUpdateReportFile, reportFileName) + ex.Message); LogWriter.Log(ex); return; } } e.Result = string.Format(StartFormMessages.Default.UpdateDoneMessage, outputFileName); }
public void Dispose() { sheetsService.Dispose(); }
private void doDataVerifyWork(BackgroundWorker worker, DoWorkEventArgs e) { int pc = 1; // progress count SheetsService service = null; string outputFileName = string.Empty; bool preWorkResult = preWork(ref service, ref outputFileName, worker, e, ref pc); if (!preWorkResult) { return; } Spreadsheets.Operator[] operators = SpreadsheetsData.Operators; Spreadsheets.SheetsRanges allRanges = SpreadsheetsData.AllRanges; StringBuilder sb = new StringBuilder(); sb.AppendFormat(StartFormMessages.Default.DataVerifyOutputHeaderFormat, DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString()); sb.AppendLine(); // do the main job and write processing info file if (worker.CancellationPending) { e.Cancel = true; } worker.ReportProgress(pc++, "Starting the data verify work:"); try { // the main work ControllerVerify.DoControll(service, operators, allRanges, sb); } catch (Exception ex) { // all exceptions are caught and logged into sb // this is for some unexpected exception worker.ReportProgress(0, StartFormMessages.Default.UnexpectedExceptionHeader + ex.Message); LogWriter.Log(ex); return; } finally { service.Dispose(); using (StreamWriter sw = new StreamWriter(outputFileName)) { try { sw.Write(sb); } catch (Exception wEx) { worker.ReportProgress(0, string.Format( StartFormMessages.Default.UnableWriteVerifyInfoFile, outputFileName)); LogWriter.Log(wEx); } } } e.Result = string.Format(StartFormMessages.Default.VerifyDoneMessage, outputFileName); }
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(); }
public async Task <int> Extract() { int blin = _settings.BacklinkIndex; int dlin = _settings.DomainIndex; string dom = _settings.SingleDomname; if (dom != null) { dlin = -1; } SheetsService service; string sheet = "Sheet1"; string[] Scopes = { SheetsService.Scope.Spreadsheets }; string ApplicationName = "ValidateLinks"; GoogleCredential credential; using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) { credential = GoogleCredential.FromStream(stream) .CreateScoped(Scopes); } service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); var range = $"{sheet}!A:G"; SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(_settings.OpenDataString, range); var response = request.Execute(); IList <IList <object> > values = response.Values; var dt = new DataTable(); if (values != null && values.Count > 0) { for (int i = 0; i < values.Count; i++) { IList <object> row = values[i]; List <object> f = row.AsEnumerable().ToList <object>(); for (int j = 0; j < f.Count; j++) { if (i == 0) { dt.Columns.Add(f[j].ToString()); } if (i != 0) { if (j == 0) { DataRow dr = dt.NewRow(); dr[j] = f[j]; dt.Rows.Add(dr); } else { try { DataRow dr = dt.Rows[i - 1]; dr[j] = f[j]; } catch { break; } } } } } } else { MessageBox.Show("Error..."); } dt.TableName = "links"; int len = dt.Rows.Count; int clen = dt.Columns.Count; int counter = 0; for (int i = 0; i < len; i++) { if (!string.IsNullOrEmpty(dt.Rows[i][dlin].ToString()) && !string.IsNullOrEmpty(dt.Rows[i][blin].ToString()) && dt.Rows[i][blin].ToString() != "n/a") { var arr = dt.Rows[i].ItemArray; DataBacklinkRow data = new DataBacklinkRow() { Domain = dt.Rows[i][blin].ToString(), Url = dt.Rows[i][dlin].ToString(), Position = counter }; DataListedInfo info = new DataListedInfo() { DataBacklinkRowID = counter, Text = Newtonsoft.Json.JsonConvert.SerializeObject(arr.Where((x, j) => j != dlin && j != blin)) }; await _infoServ.CreateDataListedInfo(info); await _serv.CreateDataBacklinkRow(data); counter++; } } service.Dispose(); return(counter); }
private async Task PublishValues(CancellationToken cancellationToken) { var temperatures = GetTemperatures(); UserCredential credential; using (var stream = new FileStream("credentials.json", FileMode.Open, FileAccess.Read)) { string credPath = "token.json"; credential = await GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", cancellationToken, new FileDataStore(credPath, true)); _logger.LogInformation("Credential file saved to: {0}", credPath); } // Create Google Sheets API service. var service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); string spreadsheetId = "1RCRFRNucJB-fs4jyhIlPMfHnMOIv7xx81h1oeZx0j-g"; // Temperatures ValueRange valueRange = new ValueRange { Values = new List <IList <object> >() }; foreach (var temp in temperatures) { valueRange.Values.Add(new List <object> { temp.Inside, temp.Outside, temp.Timestamp.ToString() }); } var request = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, "A1:C10"); request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW; await request.ExecuteAsync(cancellationToken); // Settings var response = await service.Spreadsheets.Values.Get(spreadsheetId, "E4:E14").ExecuteAsync(cancellationToken); IList <IList <Object> > values = response.Values; if (values != null && values.Count > 0) { _logger.LogInformation("Updating settings...."); var settings = _settingsMgmt.GetSettings(); settings.TargetTemperature = float.Parse(values[0][0].ToString().Replace(".", CultureInfo.CurrentCulture.NumberFormat.CurrencyDecimalSeparator)); settings.PollingInterval = int.Parse(values[1][0].ToString()); settings.PollingSensorsPath = values[2][0].ToString(); settings.PollingTemperatureFile = values[3][0].ToString(); settings.SensorInsideId = values[4][0].ToString(); settings.SensorOutsideId = values[5][0].ToString(); settings.PinCool = int.Parse(values[6][0].ToString()); settings.PinHeat = int.Parse(values[7][0].ToString()); settings.Diff = float.Parse(values[8][0].ToString().Replace(".", CultureInfo.CurrentCulture.NumberFormat.CurrencyDecimalSeparator)); settings.PublishInterval = int.Parse(values[9][0].ToString()); settings.ThermostatInterval = int.Parse(values[10][0].ToString()); _settingsMgmt.UpdateSettings(settings); } // ON-OFF ValueRange valueOnOff = new ValueRange { Values = new List <IList <object> >() }; switch (_thermostatMgmt.CurrentMode) { case Mode.Cool: valueOnOff.Values.Add(new List <object> { "ON" }); valueOnOff.Values.Add(new List <object> { "OFF" }); break; case Mode.Heat: valueOnOff.Values.Add(new List <object> { "OFF" }); valueOnOff.Values.Add(new List <object> { "ON" }); break; default: valueOnOff.Values.Add(new List <object> { "OFF" }); valueOnOff.Values.Add(new List <object> { "OFF" }); break; } request = service.Spreadsheets.Values.Update(valueOnOff, spreadsheetId, "G1:2"); request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW; await request.ExecuteAsync(cancellationToken); service.Dispose(); }
public void Dispose() => service.Dispose();