private void RemoveEntry(string SpreadSheet, List <object> objectArray)
{
ValueRange values = GetValues(SpreadSheet);
int i = 1;
bool hasBeenSet = false;
foreach (IList <object> row in values.Values.Skip(1))
{
i++;
if (row[0].ToString() == objectArray[0].ToString())
{
hasBeenSet = true; break;
}
}
if (!hasBeenSet)
{
return;
}
string range = $"{SpreadSheet}!A{i}:Z{i}";
ClearValuesRequest valueRange = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest clearRequest = service.Spreadsheets.Values.Clear(valueRange, SpreadSheetID, range);
ClearValuesResponse updateRespond = clearRequest.Execute();
}
public static void UpdateEntry()
{
ReadCart();
var range = $"cart!A:C";
var valueRange = new ValueRange();
var oblist = new List <object>()
{
0
};
valueRange.Values = new List <IList <object> > {
oblist
};
ClearValuesRequest requestBody = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request =
_service.Spreadsheets.Values.Clear(requestBody, SpreadsheetId, range);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
ClearValuesResponse response = request.Execute();
// Data.ClearValuesResponse response = await request.ExecuteAsync();
// TODO: Change code below to process the `response` object:
Console.WriteLine(JsonConvert.SerializeObject(response));
}
private void ClearSpreadsheet()
{
string range = "Server List!A1:A255";
ClearValuesRequest requestBody = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = SheetsService.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range);
ClearValuesResponse response = request.Execute();
}
/// <summary>
/// 清空資料表內容
/// </summary>
private void ClearV4Sheets()
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = "Get Google SheetData with Google Sheets API",
});
// TODO: Assign values to desired properties of `requestBody`:
ClearValuesRequest requestBody = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(requestBody, spreadsheetId, sheetName);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
ClearValuesResponse response = request.Execute();
// Data.ClearValuesResponse response = await request.ExecuteAsync();
}
private static void CreateSheet(SheetsService service, string spreadsheetId, string name)
{
try
{
var addSheetRequest = new AddSheetRequest();
addSheetRequest.Properties = new SheetProperties();
addSheetRequest.Properties.Title = name;
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
AddSheet = addSheetRequest
});
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId);
batchUpdateRequest.Execute();
}
catch
{
try
{
ClearValuesRequest clearValuesRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(clearValuesRequest, spreadsheetId, name + "!$A$1:$YY");
ClearValuesResponse response = request.Execute();
Thread.Sleep(API_THROTTLE_WAIT);
}
catch
{
//sheet doesn`t exist
}
}
finally
{
Thread.Sleep(API_THROTTLE_WAIT);
}
}
//TODO start moving this crap to methods or other objects.
public void MainRun()
{
//TODO MEthod to connect and pull data from SQL
//TODO Method to clear the sheet
//TODO Method to write the header and data
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
UserCredential credential;
using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
credPath = System.IO.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;
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Credential file saved to:\r\n" + credPath + "\r\n");
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Setup the SQL connection
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SqlConnection sqlConnection = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand
{
Connection = sqlConnection,
CommandTimeout = 60,
CommandType = CommandType.Text,
CommandText = select
};
try
{
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Open the SQL connection");
sqlConnection.Open();
}
catch (Exception e)
{
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Whoops we cannot connect to SQL - exception " + e.HResult.ToString());
//Environment.Exit(1);
}
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Please wait while reading data from SQL server");
SqlDataReader reader;
reader = cmd.ExecuteReader();
//API method to clear the sheet of all previous values
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Clear the Sheet");
ClearValuesRequest clearValuesRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest cr = service.Spreadsheets.Values.Clear(clearValuesRequest, sID, dataRange);
// TODO add a try catch statement
ClearValuesResponse clearResponse = cr.Execute();
/*
* //API method to batch update and Delete all the rows
* ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Delete all rows in Sheet");
* DimensionRange dr = new DimensionRange
* {
* Dimension = "ROWS",
* StartIndex = 1000,
* SheetId = 1809337217 //this is a problem
* };
*
* DeleteDimensionRequest ddr = new DeleteDimensionRequest() { Range = dr };
*
* Request r = new Request { DeleteDimension = ddr };
*
* //THIS IS FOR deleteDimension { "requests": [{ "deleteDimension": { "range": { "sheetId": 1809337217, "startIndex": 1}} } ]};
* List<Request> batchRequests = new List<Request>() { r };
*
* BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest() { Requests = batchRequests };
* SpreadsheetsResource.BatchUpdateRequest bRequest = service.Spreadsheets.BatchUpdate(requestBody, sID);
* BatchUpdateSpreadsheetResponse busr = bRequest.Execute();
*/
/* I have added this to the batch method
* //API method to update the header in the Sheet
* ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Write the header to the Sheet");
* IList<object> headerList;
* headerList = header.Split(',');
*
* ValueRange valueRange = new ValueRange { MajorDimension = "ROWS" };
* valueRange.Values = new List<IList<object>> { headerList };
*
* SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, sID, dataRange);
* update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
* UpdateValuesResponse result;
* result = update.Execute();
*/
//API method to batch value update the data in the Sheet
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Write the data to the Sheet");
IList <object> headerList;
headerList = header.Split(',');
ValueRange headerVR = new ValueRange
{
MajorDimension = "ROWS",
Range = "A1",
Values = new List <IList <object> > {
headerList
}
};
ValueRange dataVR = new ValueRange()
{
MajorDimension = "ROWS",
Range = "A2:ZZ",
Values = new List <IList <object> >()
};
//Need a list of ValueRanges for the BatchUpdateValuesRequest API
List <ValueRange> data = new List <ValueRange>
{
headerVR,
dataVR
};
//Build the data 2D List object with SQL data
if (reader.HasRows)
{
Object[] colValues = new Object[reader.FieldCount];
List <IList <object> > valueList = new List <IList <object> >();
var rows = 0;
while (reader.Read())
{
List <object> rowData = new List <object>();
for (int i = 0; i < reader.GetValues(colValues); i++)
{
rowData.Add(colValues[i]);
}
valueList.Add(rowData);
rows++;
}
//after 2D list is built point it to the Values method in the ValueRange object
dataVR.Values = valueList;
}
else
{
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("No rows found");
}
BatchUpdateValuesRequest buvr = new BatchUpdateValuesRequest()
{
ValueInputOption = "RAW",
Data = data
};
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = service.Spreadsheets.Values.BatchUpdate(buvr, sID);
BatchUpdateValuesResponse response = request.Execute();
/*
* //API to append data to sheet. This does not use the batchUpdate
* ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
* // Data is accessible through the DataReader object here.
* ValueRange valueDataRange = new ValueRange() { MajorDimension = "ROWS" };
*
* var dataList = new List<object>();
* valueDataRange.Values = new List<IList<object>> { dataList };
*
* SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = service.Spreadsheets.Values.Append(valueDataRange, sID, dataRange);
* appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
* appendRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
*
* if (reader.HasRows)
* {
* Object[] colValues = new Object[reader.FieldCount];
*
* int throttleCount = 0;
* int cnt = 0;
* while (reader.Read())
* {
* //This logic is flawed. If we get hit by the quota then the data row gets lost the next time this runs.
* dataList.Clear();
* for (int i = 0; i < reader.GetValues(colValues); i++)
* {
* dataList.Add(colValues[i]);
* }
*
* try
* {
* //This is the GOOGLE query Throttle they only allow 100 writes per 100 sec per user
* System.Threading.Thread.Sleep(20);
* AppendValuesResponse appendValueResponse = appendRequest.Execute();
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Writing to Sheet: row{" + cnt.ToString() + "}");
* }
* catch (Exception e)
* {
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Whoa buddy slowdown! Exception " + e.HResult.ToString());
* System.Threading.Thread.Sleep(3000);
* throttleCount++;
* }
* cnt++;
* }
* }
* else
* {
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("No rows found");
* }
*/
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Close reader and SQL");
reader.Close();
sqlConnection.Close();
}
//static volatile bool exit = false;
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:\r\n" + credPath + "\r\n");
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Prints the data from a sample spreadsheet:
// https://docs.google.com/spreadsheets/d/1c54Cy_B43h5-nmE7r6Slvj2w8Pl0XFxgaWpTxO9s9So/edit#gid=0
// Define request parameters.
String spreadsheetId = "1c54Cy_B43h5-nmE7r6Slvj2w8Pl0XFxgaWpTxO9s9So";
// here is the actual data to be sent to sheet
List <object> headerList = new List <object>()
{
"ID", "DTStamp", "DTShiftStart", "ModelNbr", "SerialNbr", "PassFail", "LineNbr", "ShiftNbr", "Computer", "Word40", "Word41", "Word42"
, "Word43", "Word44", "Word45", "Word46", "Word47", "Word48", "Word49", "Word50", "Word51", "Word52", "Word53", "Word54", "Word55", "Word56"
, "Word57", "Word58", "Word59", "Word60", "Word61", "Word62", "Word63", "Word64", "Word65", "Word66", "Word67", "Word68", "Word69", "Word70"
, "Word71", "Word72", "Word73", "Word74", "Word75", "Word76", "Word77", "Word78", "Word79", "Word80"
};
//var dataList = new List<object>();
//Write some data
String writeRange = "WriteData!A1:ZZ";
ValueRange valueRange = new ValueRange {
MajorDimension = "ROWS"
};
Console.WriteLine("Clear the Sheet");
//API method to clear the sheet
ClearValuesRequest clearValuesRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest cr = service.Spreadsheets.Values.Clear(clearValuesRequest, spreadsheetId, writeRange);
ClearValuesResponse clearResponse = cr.Execute();
Console.WriteLine("Delete all rows in Sheet");
//API method to batch update
DimensionRange dr = new DimensionRange
{
Dimension = "ROWS",
StartIndex = 1,
SheetId = 1809337217
};
DeleteDimensionRequest ddr = new DeleteDimensionRequest()
{
Range = dr
};
Request r = new Request {
DeleteDimension = ddr
};
// { "requests": [{ "deleteDimension": { "range": { "sheetId": 1809337217, "startIndex": 1}} } ]};
List <Request> batchRequests = new List <Request>()
{
r
};
BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest()
{
Requests = batchRequests
};
SpreadsheetsResource.BatchUpdateRequest bRequest = service.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
BatchUpdateSpreadsheetResponse busr = bRequest.Execute();
Console.WriteLine("Write the Headers to the Sheet");
//API method to update the sheet
valueRange.Values = new List <IList <object> > {
headerList
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, writeRange);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result;
result = update.Execute();
SqlConnection sqlConnection = new SqlConnection("Data Source=tul-mssql;Initial Catalog=Division;User ID=tqisadmin;Password=admin2k");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandText = "SELECT TOP 1000 [ID],[DTStamp],[DTShiftStart],[ModelNbr],[SerialNbr],[PassFail],[LineNbr],[ShiftNbr],[Computer],[Word40],[Word41],[Word42]" +
",[Word43],[Word44],[Word45],[Word46],[Word47],[Word48],[Word49],[Word50],[Word51],[Word52],[Word53],[Word54],[Word55],[Word56]" +
",[Word57],[Word58],[Word59],[Word60],[Word61],[Word62],[Word63],[Word64],[Word65],[Word66],[Word67],[Word68],[Word69],[Word70]" +
",[Word71],[Word72],[Word73],[Word74],[Word75],[Word76],[Word77],[Word78],[Word79],[Word80] " +
"FROM[Division].[dbo].[asyTestRecords] where LineNbr = 2 and computer = 'LN' and dtstamp > '2/1/2018 5:00' order by dtstamp desc";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection;
Console.WriteLine("Open the SQL connection");
sqlConnection.Open();
cmd.CommandTimeout = 60;
Console.WriteLine("Please wait while reading data from SQL");
reader = cmd.ExecuteReader();
// Data is accessible through the DataReader object here.
ValueRange valueDataRange = new ValueRange()
{
MajorDimension = "ROWS"
};
var dataList = new List <object>();
valueDataRange.Values = new List <IList <object> > {
dataList
};
//API to append data to sheet
SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = service.Spreadsheets.Values.Append(valueDataRange, spreadsheetId, writeRange);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
appendRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
if (reader.HasRows)
{
//Console.WriteLine("{0}",reader.FieldCount);
Object[] colValues = new Object[reader.FieldCount];
int throttleCount = 0;
int cnt = 0;
while (reader.Read())
{
//This logic is flawed. If we get hit by the quota then the data row gets lost the next time this runs.
dataList.Clear();
for (int i = 0; i < reader.GetValues(colValues); i++)
{
dataList.Add(colValues[i]);
}
try
{
//This is the GOOGLE query Throttle they only allow 500 writes per 100 sec
System.Threading.Thread.Sleep(20);
AppendValuesResponse appendValueResponse = appendRequest.Execute();
Console.WriteLine("Writing to Sheet: row{0}", cnt);
}
catch (Exception e)
{
Console.WriteLine("Whoa buddy slowdown {0}", throttleCount);
System.Threading.Thread.Sleep(3000);
throttleCount++;
}
cnt++;
}
}
else
{
Console.WriteLine("No rows found.");
}
// sit here and wait for a while
Console.WriteLine("Done waiting to close reader and SQL");
System.Threading.Thread.Sleep(3000);
reader.Close();
sqlConnection.Close();
}
private static async System.Threading.Tasks.Task CreateSheetAsync(SheetsService service, string spreadsheetId, string name, SheetConfig config)
{
try
{
var addSheetRequest = new AddSheetRequest();
addSheetRequest.Properties = new SheetProperties();
addSheetRequest.Properties.Title = name;
addSheetRequest.Properties.Hidden = config.IsSheetHidden(name);
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
AddSheet = addSheetRequest
});
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId);
await batchUpdateRequest.ExecuteAsync().ConfigureAwait(false);
await Throttler.ThrottleCheck().ConfigureAwait(false);
var repeatCellRequest = new RepeatCellRequest();
repeatCellRequest.Range = new GridRange()
{
SheetId = await GetSheetIdFromSheetNameAsync(service, spreadsheetId, name).ConfigureAwait(false),
StartRowIndex = 0,
StartColumnIndex = 0,
EndColumnIndex = 1000
};
repeatCellRequest.Fields = "userEnteredFormat.numberFormat";
repeatCellRequest.Cell = new CellData()
{
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "TEXT"
}
}
};
batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
RepeatCell = repeatCellRequest
});
batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId);
await batchUpdateRequest.ExecuteAsync().ConfigureAwait(false);
await Throttler.ThrottleCheck().ConfigureAwait(false);
}
catch
{
try
{
ClearValuesRequest clearValuesRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(clearValuesRequest, spreadsheetId, name + "!$A$1:$YY");
ClearValuesResponse response = await request.ExecuteAsync().ConfigureAwait(false);
await Throttler.ThrottleCheck().ConfigureAwait(false);
//remove validations
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request()
{
SetDataValidation = new SetDataValidationRequest()
{
Range = new GridRange()
{
SheetId = await GetSheetIdFromSheetNameAsync(service, spreadsheetId, name).ConfigureAwait(false),
StartRowIndex = 0,
StartColumnIndex = 0,
EndColumnIndex = 1000
}
}
});
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId);
await batchUpdateRequest.ExecuteAsync().ConfigureAwait(false);
await Throttler.ThrottleCheck().ConfigureAwait(false);
}
catch (Exception e)
{
//sheet doesn`t exist
throw new ServiceDirectoryException("Unable to create spreadsheet", e);
}
}
}
public void ExportarJogosJquery()
{
string[] Scopes = { SheetsService.Scope.Spreadsheets };
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/games.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
// Create Google Sheets API service.
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer {
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
string id = "1k7Reqz1ZqGXwr8lTy5Y5r6bX53hxWv4kJSWTs3ptAuc";
GameRepository game = new GameRepository();
PlatformRepository plataforma = new PlatformRepository();
SpreadsheetsResource.GetRequest get = sheetsService.Spreadsheets.Get(id);
Spreadsheet planilhas = get.Execute();
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
foreach (Sheet planilha in planilhas.Sheets)
{
var aba = planilha.Properties.Title;
ClearValuesRequest clearRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(clearRequest, id, aba + "!A1:Z1000");
ClearValuesResponse response = request.Execute();
List <GameView> lista = new List <GameView>();
List <int> plat = new List <int>();
List <object> cabecalho = null;
switch (aba)
{
case "Wishlist":
lista = game.ListarJogosWishlist(new List <int> {
0
});
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
case "Watchlist":
lista = game.ListarJogos(new List <int> {
0
}, 3);
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
default:
int?plataformas = plataforma.GetIdBySigla(aba);
plat = new List <int> {
plataformas.Value
};
lista = game.ListarJogos(plat, 1);
cabecalho = new List <object>()
{
"", "Título"
};
break;
}
string range = aba + "!A1:D" + lista.Count + 1;
List <IList <object> > dados = new List <IList <object> >();
dados.Add(cabecalho);
foreach (GameView jogo in lista)
{
if (cabecalho.Count == 2)
{
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name
});
}
else
{
string data = null;
if (jogo.ReleaseDate != null)
{
data = jogo.ReleaseDate.Value.ToShortDateString();
}
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name, data, String.Join(", ", jogo.Plataformas)
});
}
}
ValueRange valueRange = new ValueRange();
valueRange.Values = dados;
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest = sheetsService.Spreadsheets.Values.Update(valueRange, id, range);
updateRequest.ValueInputOption = valueInputOption;
UpdateValuesResponse resposta = updateRequest.Execute();
Request alignLeftRequest = new Request();
alignLeftRequest.RepeatCell = new RepeatCellRequest();
alignLeftRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignLeftRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 2, EndColumnIndex = 3
};
alignLeftRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "LEFT"
}
};
Request alignCenterRequest = new Request();
alignCenterRequest.RepeatCell = new RepeatCellRequest();
alignCenterRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignCenterRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 0, EndColumnIndex = 1
};
alignCenterRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "Center"
}
};
Request resizeRequest = new Request();
resizeRequest.AutoResizeDimensions = new AutoResizeDimensionsRequest();
resizeRequest.AutoResizeDimensions.Dimensions = new DimensionRange {
SheetId = planilha.Properties.SheetId, Dimension = "COLUMNS", StartIndex = 1, EndIndex = cabecalho.Count
};
BatchUpdateSpreadsheetRequest batch = new BatchUpdateSpreadsheetRequest();
batch.Requests = new List <Request>();
batch.Requests.Add(alignLeftRequest);
batch.Requests.Add(alignCenterRequest);
batch.Requests.Add(resizeRequest);
SpreadsheetsResource.BatchUpdateRequest u = sheetsService.Spreadsheets.BatchUpdate(batch, id);
BatchUpdateSpreadsheetResponse responseResize = u.Execute();
}
}
public void TesteSheet()
{
#region setup
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/games.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
// Create Google Sheets API service.
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer {
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
string id = "1k7Reqz1ZqGXwr8lTy5Y5r6bX53hxWv4kJSWTs3ptAuc";
List <GameView> lista_mock = new List <GameView>();
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste teste teste teste teste teste teste"
});
GameRepository game = new GameRepository();
PlatformRepository plataforma = new PlatformRepository();
#endregion
#region limpar, preencher e formatar abas
SpreadsheetsResource.GetRequest get = sheetsService.Spreadsheets.Get(id);
Spreadsheet planilhas = get.Execute();
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
foreach (Sheet planilha in planilhas.Sheets)
{
var aba = planilha.Properties.Title;
ClearValuesRequest clearRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(clearRequest, id, aba + "!A1:Z1000");
ClearValuesResponse response = request.Execute();
List <GameView> lista = new List <GameView>();
List <int> plat = new List <int>();
List <object> cabecalho = null;
switch (aba)
{
case "Wishlist":
lista = game.ListarJogosWishlist(new List <int> {
0
});
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
case "Watchlist":
lista = game.ListarJogos(new List <int> {
0
}, 3);
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
default:
int?plataformas = plataforma.GetIdBySigla(aba);
plat = new List <int> {
plataformas.Value
};
lista = game.ListarJogos(plat, 1);
cabecalho = new List <object>()
{
"", "Título"
};
break;
}
string range = aba + "!A1:D" + lista.Count + 1;
List <IList <object> > dados = new List <IList <object> >();
dados.Add(cabecalho);
foreach (GameView jogo in lista)
{
if (cabecalho.Count == 2)
{
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name
});
}
else
{
string data = null;
if (jogo.ReleaseDate != null)
{
data = jogo.ReleaseDate.Value.ToShortDateString();
}
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name, data, String.Join(", ", jogo.Plataformas)
});
}
}
ValueRange valueRange = new ValueRange();
valueRange.Values = dados;
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest = sheetsService.Spreadsheets.Values.Update(valueRange, id, range);
updateRequest.ValueInputOption = valueInputOption;
UpdateValuesResponse resposta = updateRequest.Execute();
Request alignLeftRequest = new Request();
alignLeftRequest.RepeatCell = new RepeatCellRequest();
alignLeftRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignLeftRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 2, EndColumnIndex = 3
};
alignLeftRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "LEFT"
}
};
Request alignCenterRequest = new Request();
alignCenterRequest.RepeatCell = new RepeatCellRequest();
alignCenterRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignCenterRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 0, EndColumnIndex = 1
};
alignCenterRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "Center"
}
};
Request resizeRequest = new Request();
resizeRequest.AutoResizeDimensions = new AutoResizeDimensionsRequest();
resizeRequest.AutoResizeDimensions.Dimensions = new DimensionRange {
SheetId = planilha.Properties.SheetId, Dimension = "COLUMNS", StartIndex = 1, EndIndex = cabecalho.Count
};
BatchUpdateSpreadsheetRequest batch = new BatchUpdateSpreadsheetRequest();
batch.Requests = new List <Request>();
batch.Requests.Add(alignLeftRequest);
batch.Requests.Add(alignCenterRequest);
batch.Requests.Add(resizeRequest);
SpreadsheetsResource.BatchUpdateRequest u = sheetsService.Spreadsheets.BatchUpdate(batch, id);
BatchUpdateSpreadsheetResponse responseResize = u.Execute();
}
#endregion
#region deletar aba
// A list of updates to apply to the spreadsheet.
// Requests will be applied in the order they are specified.
// If any request is not valid, no requests will be applied.
/*List<Request> requests = new List<Request>(); // TODO: Update placeholder value.
* var a = new DeleteSheetRequest();
* a.SheetId = 0;
*
* var t = new Request();
* t.DeleteSheet = a;
*
* requests.Add(t);
*
* // TODO: Assign values to desired properties of `requestBody`:
* BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest();
* requestBody.Requests = requests;
*
* SpreadsheetsResource.BatchUpdateRequest req = sheetsService.Spreadsheets.BatchUpdate(requestBody, id);
*
* // To execute asynchronously in an async method, replace `request.Execute()` as shown:
* BatchUpdateSpreadsheetResponse response = req.Execute();*/
#endregion
#region criar planilha
// TODO: Assign values to desired properties of `requestBody`:
/*Spreadsheet requestBody3 = new Spreadsheet();
* var propriedades = new SpreadsheetProperties();
* propriedades.Title = "Games";
* requestBody3.Properties = propriedades;
*
* SpreadsheetsResource.CreateRequest request3 = sheetsService.Spreadsheets.Create(requestBody3);
*
* // To execute asynchronously in an async method, replace `request.Execute()` as shown:
* Spreadsheet response3 = request3.Execute();
* // Data.Spreadsheet response = await request.ExecuteAsync();
*
* // TODO: Change code below to process the `response` object:
* Console.WriteLine(response3);*/
#endregion
}
