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 }