/// <summary> /// Add a pivot table into worksheet. /// </summary> /// <param name="name">Document name.</param> /// <param name="sheetName">Worksheet name.</param> /// <param name="createPivotTableRequest">Pass an object of type CreatePivotTableRequest</param> /// <param name="folder">Document folder.</param> /// <param name="storage">The document storage.</param> /// <returns>PivotTableResponse object</returns> public PivotTableResponse AddAPivotTableIntoWorksheet(string name, string sheetName, CreatePivotTableRequest createPivotTableRequest, string folder, string storage = "") { // put cells/{name}/worksheets/{sheetName}/pivottables?appSid={appSid}&storage={storage}&folder={folder} string apiUrl = string.Format(@"cells/{0}/worksheets/{1}/pivottables?storage={2}&folder={3}", name, sheetName, storage, folder); JObject jObject = JObject.Parse(ServiceController.Put(apiUrl, AppSid, AppKey, JsonConvert.SerializeObject(createPivotTableRequest))); PivotTableResponse pivotTableResponse = jObject.ToObject<PivotTableResponse>(); return pivotTableResponse; }
public void Cells_PivotTable_Tests() { try { storageService.File.CopyFile(Utils.CloudStorage_Input_Folder + "/cells-sample.xlsx", Utils.CloudStorage_Output_Folder + "/cells-sample.xlsx"); string name = "cells-sample.xlsx"; PivotTablesResponse pivotTablesResponse = cellsService.PivotTable.GetWorksheetPivottablesInfo(name, "sheet6", Utils.CloudStorage_Output_Folder); PivotTableResponse pivotTableResponse = cellsService.PivotTable.GetWorksheetPivottableInfoByIndex(name, "sheet6", 0, Utils.CloudStorage_Output_Folder); cellsService.PivotTable.DeleteWorksheetPivotTableByIndex(name, "sheet6", 0, Utils.CloudStorage_Output_Folder); cellsService.PivotTable.DeleteWorksheetPivotTables(name, "sheet6", Utils.CloudStorage_Output_Folder); CreatePivotTableRequest createPivotTableRequest = new CreatePivotTableRequest(); createPivotTableRequest.Name = "Test Pivot Table"; createPivotTableRequest.SourceData = "A1:C7"; createPivotTableRequest.DestCellName = "H10"; createPivotTableRequest.UseSameSource = true; createPivotTableRequest.PivotFieldRows = new List<int>(); createPivotTableRequest.PivotFieldRows.Add(1); createPivotTableRequest.PivotFieldColumns = new List<int>(); createPivotTableRequest.PivotFieldColumns.Add(1); createPivotTableRequest.PivotFieldData = new List<int>(); createPivotTableRequest.PivotFieldData.Add(1); cellsService.PivotTable.AddAPivotTableIntoWorksheet(name, "sheet7", createPivotTableRequest, Utils.CloudStorage_Output_Folder); PivotTableFieldRequest pivotTableFieldRequest = new PivotTableFieldRequest(); pivotTableFieldRequest.Data = new List<int>(); pivotTableFieldRequest.Data.Add(1); pivotTableFieldRequest.Data.Add(2); cellsService.PivotTable.AddPivotFieldIntoIntoPivotTable(name, "sheet6", 0, "Row", pivotTableFieldRequest, Utils.CloudStorage_Output_Folder); WorkbookStyleResponse workbookStyleResponse = cellsService.WorksheetColumns.ReadCellStyleInfo(name, "sheet6", "A8", Utils.CloudStorage_Output_Folder); cellsService.PivotTable.UpdateCellStyleForPivotTable(name, "sheet6", 0, 1, 1, workbookStyleResponse.Style, Utils.CloudStorage_Output_Folder); cellsService.PivotTable.UpdateStyleForPivotTable(name, "sheet6", 0, workbookStyleResponse.Style, Utils.CloudStorage_Output_Folder); storageService.File.DownloadFile(Utils.CloudStorage_Output_Folder + "/cells-sample.xlsx", "d:\\cells-sample.xlsx"); } catch (Exception ex) { Assert.Fail(ex.Message); } }