public static void AppendToStandardTableData(StandardTableDataCM dest, StandardTableDataCM source)
{
if (dest == null || dest.Table == null || dest.Table.Count == 0)
{
return;
}
if (source == null || source.Table == null || source.Table.Count == 0)
{
return;
}
var columnSet = new HashSet <string>(dest.GetHeaderRow().Row.Select(x => x.Cell.Key));
for (var i = 1; i < source.Table.Count; ++i)
{
var row = new TableRowDTO();
foreach (var cell in source.Table[i].Row)
{
if (!columnSet.Contains(cell.Cell.Key))
{
continue;
}
row.Row.Add(
new TableCellDTO()
{
Cell = cell.Cell.Clone()
});
}
dest.Table.Add(row);
}
}
public void GetDataListSize_ForTableWithHeaders_ReturnsTheActualNumberOfRows()
{
var table = new StandardTableDataCM {
FirstRowHeaders = true, Table = new List <TableRowDTO> {
new TableRowDTO(), new TableRowDTO()
}
};
Assert.AreEqual(1, Loop_v1.GetDataListSize(Crate.FromContent(string.Empty, table)), $"{nameof(Loop_v1.GetDataListSize)} should return the count of data rows from table with headers");
}
public void GetDataListSize_ForTableWithoutHeaders_ReturnsTableSize()
{
var table = new StandardTableDataCM {
FirstRowHeaders = false, Table = new List <TableRowDTO> {
new TableRowDTO(), new TableRowDTO()
}
};
Assert.AreEqual(2, Loop_v1.GetDataListSize(Crate.FromContent(string.Empty, table)), $"{nameof(Loop_v1.GetDataListSize)} should return the count of all rows from table without headers");
}
private HashSet <string> GetDataProperties(StandardTableDataCM data)
{
if (!data.HasDataRows || data.Table[0].Row.Count == 0)
{
return(null);
}
if (string.IsNullOrWhiteSpace(SpecifiedDataProperties))
{
return(null);
}
return(new HashSet <string>(SpecifiedDataProperties.Split(new[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(x => x.Trim()), StringComparer.InvariantCultureIgnoreCase));
}
private static void WriteTableDataToWorksheet(ExcelWorksheet workSheet, StandardTableDataCM tableCm)
{
var table = tableCm.Table;
for (var row = 0; row < table.Count; row++)
{
var currentRow = table[row].Row;
for (var col = 0; col < currentRow.Count; col++)
{
//CellRange enumerate rows and columns from index 1
workSheet.Cells[row + 1, col + 1].Value = currentRow[col].Cell.Value;
}
}
}
public static byte[] CreateExcelFile(StandardTableDataCM tableCM, string sheetName)
{
var dataTable = ToDataTable(tableCM);
dataTable.TableName = sheetName;
var writer = new ExcelWriter();
using (var stream = new MemoryStream())
{
writer.WriteFile(stream, dataTable);
return(stream.ToArray());
}
}
public override Task Run()
{
var table = new StandardTableDataCM();
for (int i = 0; i < int.Parse(ActivityUI.NumberOfRows.Value); i++)
{
TableRowDTO row;
table.Table.Add(row = new TableRowDTO());
for (int j = 0; j < 5; j++)
{
row.Row.Add(new TableCellDTO
{
Cell = new KeyValueDTO($"Column {j}", $"Cell [{i}, {j}]")
});
}
}
Payload.Add(Crate.FromContent(TableCrateLabel, table));
return(Task.FromResult(0));
}
private KeyValueDTO ExtractFieldFromTable(StandardTableDataCM table, DropDownList chosenCell, DropDownList position)
{
var field = position.Value == ImmediatelyBelowValue?GetValueBelowSelectedCell(table, chosenCell.Value) : GetValueRightToSelectedCell(table, chosenCell.Value);
if (field == null)
{
return(null);
}
if (position.Value == ImmediatelyBelowValue)
{
field.Key = "Value " + ImmediatelyBelowKey + " of " + chosenCell.Value;
}
else
{
field.Key = "Value " + ImmediatelyToRightKey + " of " + chosenCell.Value;
}
return(field);
}
private void PopulateRowData(CrateDescriptionDTO crateDescriptionToProcess, OperationalStateCM.LoopStatus loopData, Crate crateToProcess)
{
string label = GetCrateName(crateDescriptionToProcess);
Payload.RemoveUsingPredicate(a => a.Label == label && a.ManifestType == crateToProcess.ManifestType);
if (crateDescriptionToProcess.ManifestId == (int)MT.StandardTableData)
{
var table = crateToProcess.Get <StandardTableDataCM>();
var rowOfData = table.DataRows.ElementAt(loopData.Index);
var extractedCrate = new StandardTableDataCM(false, new List <TableRowDTO>()
{
rowOfData
});
Payload.Add(Crate.FromContent(label, extractedCrate));
}
else
{
var cloned_crate = CloneCrateAndReplaceArrayWithASingleValue(crateToProcess, "", loopData.Index, GetCrateName(crateDescriptionToProcess));
Payload.Add(cloned_crate);
}
}
public static byte[] RewriteSheetForFile(byte[] existingFile,
StandardTableDataCM tableCM,
string sheetName)
{
using (var memoryStream = new MemoryStream(existingFile))
using (var excelPackage = new ExcelPackage(memoryStream))
{
var workSheet = excelPackage.Workbook.Worksheets[sheetName];
if (workSheet == null)
{
workSheet = excelPackage.Workbook.Worksheets.Add(sheetName);
}
else
{
var targetIndex = workSheet.Index;
excelPackage.Workbook.Worksheets.Delete(workSheet);
workSheet = excelPackage.Workbook.Worksheets.Add(sheetName);
excelPackage.Workbook.Worksheets.MoveBefore(workSheet.Index, targetIndex);
}
WriteTableDataToWorksheet(workSheet, tableCM);
FixPackageStyles(excelPackage);
return(excelPackage.GetAsByteArray());
}
//var dataTable = ToDataTable(tableCM);
//dataTable.TableName = sheetName;
//var writer = new ExcelWriter();
//using (var stream = new MemoryStream())
//{
// stream.Write(existingFile, 0, existingFile.Length);
// stream.Position = 0;
// writer.RewriteSheetForFile(stream, dataTable);
// return stream.ToArray();
//}
}
public override async Task Run()
{
var query = new AsanaTaskQuery()
{
Workspace = ActivityUI.WorkspacesList.Value,
Assignee = ActivityUI.UsersList.Value,
Project = ActivityUI.ProjectsList.Value
};
var tasks = await AClient.Tasks.GetAsync(query);
var dataRows = tasks.Select(t => new TableRowDTO()
{
Row =
{
new TableCellDTO()
{
Cell = new KeyValueDTO("Task name", t.Name)
},
new TableCellDTO()
{
Cell = new KeyValueDTO("Task id", t.Id)
}
}
}).ToList();
var payload = new StandardTableDataCM()
{
Table = dataRows
};
var customPayload = new AsanaTaskListCM()
{
Tasks = tasks.Select(t => Mapper.Map <AsanaTaskCM>(t)).ToList()
};
Payload.Add(RunTimeCrateLabel, payload);
Payload.Add(RunTimeCrateLabelCustomCM, customPayload);
}
private static DataTable ToDataTable(StandardTableDataCM tableCM)
{
if (tableCM == null || tableCM.Table == null || tableCM.Table.Count == 0)
{
throw new ApplicationException("Invalid StandardTableDataCM data.");
}
var dataTable = new DataTable();
var columnIndex = new Dictionary <string, int>();
for (var i = 0; i < tableCM.Table[0].Row.Count; ++i)
{
var cell = tableCM.Table[0].Row[i].Cell;
dataTable.Columns.Add(cell.Key, typeof(string));
columnIndex.Add(cell.Key, i);
}
foreach (var row in tableCM.Table.Skip(1))
{
var dataRow = new object[tableCM.Table[0].Row.Count];
foreach (var cell in row.Row)
{
int columnNumber;
if (columnIndex.TryGetValue(cell.Cell.Key, out columnNumber))
{
dataRow[columnNumber] = cell.Cell.Value;
}
}
dataTable.Rows.Add(dataRow);
}
return(dataTable);
}
public override Task Run()
{
Log($"Table {ActivityPayload.Label} [{ActivityId}] started");
var tableCm = new StandardTableDataCM();
for (int i = 0; i < int.Parse(ActivityUI.NumberOfRows.Value); i++)
{
TableRowDTO row;
tableCm.Table.Add(row = new TableRowDTO());
for (int j = 0; j < 5; j++)
{
row.Row.Add(new TableCellDTO
{
Cell = new KeyValueDTO("Column " + j, $"Cell [{i}, {j}]")
});
}
}
Payload.Add(Crate.FromContent("Table", tableCm));
return(Task.FromResult(0));
}
private Crate <StandardTableDataCM> FilterData(string message, Crate <StandardTableDataCM> data, HashSet <string> dataProperties)
{
//If there is nothing to filter we just return the original data from child activity
if (string.IsNullOrWhiteSpace(message))
{
return(data);
}
var sourceTable = data.Content;
var result = new StandardTableDataCM {
Table = new List <TableRowDTO>(), FirstRowHeaders = sourceTable.FirstRowHeaders
};
//Copy header row
if (sourceTable.FirstRowHeaders && sourceTable.Table.Count > 0)
{
result.Table.Add(sourceTable.Table[0]);
}
foreach (var dataRow in sourceTable.DataRows)
{
var dataRowPropertyValues = GetPropertyValuesFromDataRow(dataRow, dataProperties);
var propertyValueIsFound = false;
foreach (var propertyValue in dataRowPropertyValues)
{
if (message.Contains(propertyValue, StringComparison.InvariantCultureIgnoreCase))
{
propertyValueIsFound = true;
break;
}
}
if (propertyValueIsFound)
{
result.Table.Add(dataRow);
}
}
return(Crate <StandardTableDataCM> .FromContent(data.Label, result));
}
private KeyValueDTO GetValueBelowSelectedCell(StandardTableDataCM table, string selectedCellValue)
{
var rows = table.Table;
//we might have out of bounds exceptions here
try
{
for (var i = 0; i < rows.Count; i++)
{
var cells = rows[i].Row;
for (int j = 0; j < cells.Count; j++)
{
if (cells[j].Cell.Value == selectedCellValue)
{
return(rows[i + 1].Row[j].Cell);
}
}
}
}
catch
{
}
return(null);
}
public override async Task Run()
{
using (var uow = _container.GetInstance <IUnitOfWork>())
{
var selectedObjectId = Guid.Parse(ActivityUI.AvailableObjects.Value);
var mtType = uow.MultiTenantObjectRepository.FindTypeReference(selectedObjectId);
if (mtType == null)
{
throw new ApplicationException("Invalid object selected.");
}
var conditions = JsonConvert.DeserializeObject <List <FilterConditionDTO> >(
ActivityUI.QueryBuilder.Value
);
//////////DIRTY DIRTY NASTY HACK////////////
//cause we can't select upstream value on //
//these controls yet //
foreach (var condition in conditions)
{
if (condition.Value == "FromPayload")
{
foreach (var crate in Payload)
{
// skip system crates
if (crate.IsOfType <OperationalStateCM>() || crate.IsOfType <CrateDescriptionCM>() || crate.IsOfType <ValidationResultsCM>())
{
continue;
}
//GetValue() method is a copy of UpstreamValueExtractorBase<T>.GetValue
var foundValue = GetValue(crate, condition.Field);
if (foundValue != null)
{
condition.Value = Convert.ToString(foundValue);
}
}
}
}
////////////END OF HACK/////////////////////
var manifestType = mtType.ClrType;
var queryBuilder = MTSearchHelper.CreateQueryProvider(manifestType);
var converter = CrateManifestToRowConverter(manifestType);
var foundObjects = queryBuilder
.Query(
uow,
CurrentUserId,
conditions
)
.ToArray();
var searchResult = new StandardTableDataCM();
if (foundObjects.Length > 0)
{
searchResult.FirstRowHeaders = true;
var headerRow = new TableRowDTO();
var properties = uow.MultiTenantObjectRepository.ListTypePropertyReferences(mtType.Id);
foreach (var mtTypeProp in properties)
{
headerRow.Row.Add(
new TableCellDTO()
{
Cell = new KeyValueDTO(mtTypeProp.Name, mtTypeProp.Name)
});
}
searchResult.Table.Add(headerRow);
}
foreach (var foundObject in foundObjects)
{
searchResult.Table.Add(converter(foundObject));
}
Payload.Add(
Crate.FromContent(
RunTimeCrateLabel,
searchResult
)
);
}
await Task.Yield();
}
public StandardPayloadDataCM TransformStandardTableDataToStandardPayloadData(string curObjectType, StandardTableDataCM tableDataMS)
{
var payloadDataMS = new StandardPayloadDataCM()
{
PayloadObjects = new List <PayloadObjectDTO>(),
ObjectType = curObjectType,
};
int staringRow;
TableRowDTO columnHeadersRowDTO = null;
if (tableDataMS.FirstRowHeaders)
{
staringRow = 1;
columnHeadersRowDTO = tableDataMS.Table[0];
}
else
{
staringRow = 0;
}
// Rows containing column names
for (int i = staringRow; i < tableDataMS.Table.Count; ++i) // Since first row is headers; hence i starts from 1
{
try
{
var tableRowDTO = tableDataMS.Table[i];
var fields = new List <FieldDTO>();
int colNumber = (tableDataMS.FirstRowHeaders) ? columnHeadersRowDTO.Row.Count : tableRowDTO.Row.Count;
for (int j = 0; j < colNumber; ++j)
{
var tableCellDTO = tableRowDTO.Row[j];
var listFieldDTO = new FieldDTO()
{
Key = (tableDataMS.FirstRowHeaders) ? columnHeadersRowDTO.Row[j].Cell.Value : tableCellDTO.Cell.Key,
Value = tableCellDTO.Cell.Value
};
fields.Add(listFieldDTO);
}
payloadDataMS.PayloadObjects.Add(new PayloadObjectDTO()
{
PayloadObject = fields
});
}
catch (Exception)
{
//Avoid general failure of the process if there is an error processing individual records in the table
}
}
return(payloadDataMS);
}
/// <summary>
/// Create new spreadsheet file for a given google account, and write data inside the speadsheet
/// </summary>
/// <param name="authorizationTokenId"></param>
/// <param name="spreadsheetName"></param>
/// <param name="worksheetName"></param>
/// <param name="tableData"></param>
/// <returns></returns>
public async Task <string> CreateNewSpreadsheet(Guid authorizationTokenId, string spreadsheetName, string worksheetName, StandardTableDataCM tableData)
{
var googleSheetApi = new GoogleSheet(new GoogleIntegration(ObjectFactory.GetInstance <IRestfulServiceClient>()), new GoogleDrive());
var defaultGoogleAuthToken = GetGoogleAuthToken(authorizationTokenId);
var spreadsheetId = await googleSheetApi.CreateSpreadsheet(spreadsheetName, defaultGoogleAuthToken);
var googleSheets = await googleSheetApi.GetSpreadsheets(defaultGoogleAuthToken);
var spreadsheetUri = googleSheets.FirstOrDefault(x => x.Value == spreadsheetName).Key;
//create worksheet for this new created spreadsheet
var existingWorksheets = await googleSheetApi.GetWorksheets(spreadsheetUri, defaultGoogleAuthToken);
var existingWorksheetUri = existingWorksheets.Where(x => string.Equals(x.Value.Trim(), worksheetName, StringComparison.InvariantCultureIgnoreCase))
.Select(x => x.Key).FirstOrDefault();
if (string.IsNullOrEmpty(existingWorksheetUri))
{
existingWorksheetUri = await googleSheetApi.CreateWorksheet(spreadsheetUri, defaultGoogleAuthToken, worksheetName);
}
await googleSheetApi.WriteData(spreadsheetUri, existingWorksheetUri, tableData, defaultGoogleAuthToken);
return(spreadsheetId);
}
public static StandardTableDataCM ExtractPayloadCrateDataToStandardTableData(Crate crate)
{
if (crate.ManifestType.Id == (int)MT.StandardTableData)
{
return(crate.Get <StandardTableDataCM>());
}
if (crate.ManifestType.Id == (int)MT.FieldDescription)
{
var fields = crate.Get <FieldDescriptionsCM>();
return(new StandardTableDataCM
{
FirstRowHeaders = true,
Table = new List <TableRowDTO>
{
//Keys of fields will become column headers
new TableRowDTO {
Row = fields.Fields.Select(x => new TableCellDTO {
Cell = new FieldDTO(x.Key, x.Key)
}).ToList()
},
new TableRowDTO {
Row = fields.Fields.Select(x => new TableCellDTO {
Cell = x
}).ToList()
}
}
});
}
var tableData = new StandardTableDataCM
{
FirstRowHeaders = true,
Table = new List <TableRowDTO>()
};
var headerIsAdded = false;
var item = CrateStorageSerializer.Default.ConvertToDto(crate);
var token = JToken.Parse(item.Contents.ToString());
var jObject = token as JObject;
if (jObject != null)
{
//check if jObject has some JArray properties
var arrayProperty = jObject.Properties().FirstOrDefault(x => x.Value is JArray);
//check how StandardPayloadDataCM is structured
if (arrayProperty != null)
{
foreach (var arrayItem in arrayProperty.Value)
{
//arrayItem is PayloadObjectDTO which on has an List<FieldDTO>
var innerArrayProperty = ((JObject)arrayItem).Properties().FirstOrDefault(x => x.Value is JArray);
if (innerArrayProperty != null)
{
var headerRow = new TableRowDTO();
var dataRow = new TableRowDTO();
foreach (var innerArrayItem in innerArrayProperty.Value)
{
//try to parse the property as FieldDTO
if (innerArrayItem is JObject)
{
var fieldObj = (JObject)innerArrayItem;
if (fieldObj.Property("key") != null && fieldObj.Property("value") != null)
{
headerRow.Row.Add(new TableCellDTO
{
Cell = new FieldDTO(fieldObj["key"].ToString(), fieldObj["key"].ToString())
});
dataRow.Row.Add(new TableCellDTO
{
Cell =
new FieldDTO(fieldObj["key"].ToString(), fieldObj["value"].ToString())
});
}
}
}
if (!headerIsAdded)
{
tableData.Table.Add(headerRow);
headerIsAdded = true;
}
tableData.Table.Add(dataRow);
}
// StandardFileListCM manifest has structure like this.
else
{
var headerRow = new TableRowDTO();
var dataRow = new TableRowDTO();
foreach (var property in ((JObject)arrayItem).Properties())
{
//try to parse the property as FieldDTO
if (property.Name != null && property.Value != null)
{
headerRow.Row.Add(new TableCellDTO
{
Cell = new FieldDTO(property.Name, property.Name)
});
dataRow.Row.Add(new TableCellDTO
{
Cell =
new FieldDTO(property.Name, property.Value.ToString())
});
}
}
if (!headerIsAdded)
{
tableData.Table.Add(headerRow);
headerIsAdded = true;
}
tableData.Table.Add(dataRow);
}
}
}
else
{
var headerRow = new TableRowDTO();
var dataRow = new TableRowDTO();
foreach (JProperty property in jObject.Properties())
{
//try to parse the property as FieldDTO
if (property.Value is JObject)
{
var fieldObj = (JObject)property.Value;
if (fieldObj.Property("key") != null && fieldObj.Property("value") != null)
{
headerRow.Row.Add(new TableCellDTO {
Cell = new FieldDTO(fieldObj["key"].ToString(), fieldObj["key"].ToString())
});
dataRow.Row.Add(new TableCellDTO {
Cell = new FieldDTO(fieldObj["key"].ToString(), fieldObj["value"].ToString())
});
}
}
else
{
headerRow.Row.Add(new TableCellDTO {
Cell = new FieldDTO(property.Name, property.Name)
});
dataRow.Row.Add(new TableCellDTO {
Cell = new FieldDTO(property.Name, property.Value.ToString())
});
}
}
if (!headerIsAdded)
{
tableData.Table.Add(headerRow);
headerIsAdded = true;
}
tableData.Table.Add(dataRow);
}
}
return(tableData);
}
private async Task <string> AppendOrCreateSpreadsheet(StandardTableDataCM tableToSave)
{
byte[] fileData;
string fileName;
if (ActivityUI.UseNewSpreadsheetOption.Selected)
{
fileData = ExcelUtils.CreateExcelFile(
tableToSave,
ActivityUI.NewWorksheetName.Value
);
fileName = ActivityUI.NewSpreadsheetName.Value;
}
else
{
var existingFileStream = await HubCommunicator.DownloadFile(
int.Parse(ActivityUI.ExistingSpreadsheetsList.Value)
);
byte[] existingFileBytes;
using (var memStream = new MemoryStream())
{
await existingFileStream.CopyToAsync(memStream);
existingFileBytes = memStream.ToArray();
}
fileName = ActivityUI.ExistingSpreadsheetsList.selectedKey;
var worksheetName = ActivityUI.UseNewWorksheetOption.Selected
? ActivityUI.NewWorksheetName.Value
: ActivityUI.ExistingWorksheetsList.selectedKey;
StandardTableDataCM dataToInsert;
if (ActivityUI.UseExistingWorksheetOption.Selected ||
ActivityUI.ExistingWorksheetsList.ListItems.Any(x => x.Key == ActivityUI.NewWorksheetName.Value))
{
var existingData = _excelUtils.GetExcelFile(existingFileBytes, fileName, true, worksheetName);
StandardTableDataCMTools.AppendToStandardTableData(existingData, tableToSave);
dataToInsert = existingData;
}
else
{
dataToInsert = tableToSave;
}
fileData = ExcelUtils.RewriteSheetForFile(
existingFileBytes,
dataToInsert,
worksheetName
);
}
using (var stream = new MemoryStream(fileData, false))
{
if (!fileName.ToUpper().EndsWith(".XLSX"))
{
fileName += ".xlsx";
}
var file = await HubCommunicator.SaveFile(fileName, stream);
Payload.Add(Crate.FromContent("StoredFile", new StandardFileDescriptionCM
{
Filename = file.Id.ToString(), // dirty hack
TextRepresentation = file.OriginalFileName, // another hack
Filetype = ".xlsx"
}));
return(file.CloudStorageUrl);
}
}
public override async Task Run()
{
var eventCrate = Payload.CratesOfType <EventReportCM>().FirstOrDefault()
?.Get <EventReportCM>()
?.EventPayload;
KeyValueListCM changedFiles = null;
if (eventCrate != null)
{
changedFiles = eventCrate
.CrateContentsOfType <KeyValueListCM>(x => x.Label == "ChangedFiles")
.SingleOrDefault();
}
if (changedFiles == null)
{
RequestPlanExecutionTermination("File list was not found in the payload.");
}
if (ActivityUI.AllSpreadsheetsOption.Selected)
{
var rows = new List <TableRowDTO>();
foreach (var changedFile in changedFiles.Values)
{
var row = new TableRowDTO();
row.Row.Add(TableCellDTO.Create(SpreadsheetIdLabel, changedFile.Key));
row.Row.Add(TableCellDTO.Create(SpreadsheetNameLabel, changedFile.Value));
rows.Add(row);
}
var tableData = new StandardTableDataCM(false, rows);
Payload.Add(Crate.FromContent(RunTimeCrateLabel, tableData));
Success();
}
else
{
var hasFileId = changedFiles.Values.Any(x => x.Key == ActivityUI.SpreadsheetList.Value);
if (!hasFileId)
{
RequestPlanExecutionTermination();
}
else
{
var rows = new List <TableRowDTO>();
var changedFile = changedFiles.Values.Where(x => x.Key == ActivityUI.SpreadsheetList.Value).First();
var row = new TableRowDTO();
row.Row.Add(TableCellDTO.Create(SpreadsheetIdLabel, changedFile.Key));
row.Row.Add(TableCellDTO.Create(SpreadsheetNameLabel, changedFile.Value));
rows.Add(row);
var tableData = new StandardTableDataCM(false, rows);
Payload.Add(Crate.FromContent(RunTimeCrateLabel, tableData));
Success();
}
}
}
public Crate CreatePayloadDataCrate(string payloadDataObjectType, string crateLabel, StandardTableDataCM tableDataMS)
{
return(Crate.FromContent(crateLabel, TransformStandardTableDataToStandardPayloadData(payloadDataObjectType, tableDataMS)));
}
private void WriteDataImpl(string spreadsheetUri, string worksheetUri, StandardTableDataCM data, GoogleAuthDTO authDTO)
{
if (String.IsNullOrEmpty(spreadsheetUri))
{
throw new ArgumentNullException("Spreadsheet Uri parameter is required.");
}
if (string.IsNullOrEmpty(worksheetUri))
{
throw new ArgumentNullException("Worksheet Uri parameter is required.");
}
if (data != null && data.Table.Count > 0)
{
int MAX_ROWS = data.Table.Count;
int MAX_COLS = data.Table[0].Row.Count;
SpreadsheetEntry spreadsheet = FindSpreadsheet(spreadsheetUri, authDTO);
if (spreadsheet == null)
{
throw new ArgumentException("Cannot find a spreadsheet", "spreadsheetUri");
}
SpreadsheetsService service = (SpreadsheetsService)spreadsheet.Service;
string worksheetId = worksheetUri.Substring(worksheetUri.LastIndexOf('/') + 1, worksheetUri.Length - (worksheetUri.LastIndexOf('/') + 1));
string spreadSheetId = spreadsheetUri;
if (spreadSheetId.ToLower().Contains("http"))//remove http url
{
spreadSheetId = spreadSheetId.Substring(spreadSheetId.LastIndexOf('/') + 1, spreadSheetId.Length - (spreadSheetId.LastIndexOf('/') + 1));
}
CellQuery cellQuery = new CellQuery(spreadSheetId, worksheetId, "private", "full");
CellFeed cellFeed = service.Query(cellQuery);
// Build list of cell addresses to be filled in
List <CellAddress> cellAddrs = new List <CellAddress>();
for (int row = 0; row < MAX_ROWS; row++)
{
for (int col = 0; col < MAX_COLS; col++)
{
cellAddrs.Add(new CellAddress(row + 1, col + 1, data.Table[row].Row[col].Cell.Value));
}
}
// Prepare the update
// GetCellEntryMap is what makes the update fast.
Dictionary <String, CellEntry> cellEntries = GetCellEntryMap(service, cellFeed, cellAddrs);
CellFeed batchRequest = new CellFeed(cellQuery.Uri, service);
foreach (CellAddress cellAddr in cellAddrs)
{
CellEntry batchEntry = cellEntries[cellAddr.IdString];
batchEntry.InputValue = cellAddr.InputValue;
batchEntry.BatchData = new GDataBatchEntryData(cellAddr.IdString, GDataBatchOperationType.update);
batchRequest.Entries.Add(batchEntry);
}
// Submit the update
CellFeed batchResponse = (CellFeed)service.Batch(batchRequest, new Uri(cellFeed.Batch));
// Check the results
foreach (CellEntry entry in batchResponse.Entries)
{
string batchId = entry.BatchData.Id;
if (entry.BatchData.Status.Code != 200)
{
GDataBatchStatus status = entry.BatchData.Status;
throw new Exception(string.Format("{0} failed ({1})", batchId, status.Reason));
}
}
}
}
public Task WriteData(string spreadsheetUri, string worksheetUri, StandardTableDataCM data, GoogleAuthDTO authDTO)
{
return(Task.Run(() => WriteDataImpl(spreadsheetUri, worksheetUri, data, authDTO)));
}
