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);
}
