protected override Task ExecuteAsync(AsyncCodeActivityContext context, SheetsService sheetService)
{
var includeHeaders = IncludeHeaders;
var sheet = SheetName.Get(context);
var startingCell = StartingCell.Get(context);
var dataTable = DataTable.Get(context);
string cellToPassToService;
if (string.IsNullOrWhiteSpace(sheet))
{
cellToPassToService = startingCell;
}
else
{
cellToPassToService = string.Format("{0}!{1}", sheet, startingCell);
}
return(Task.Factory.StartNew <object>(() =>
{
ValueRange requestBody = CreateRequestBodyWithValues(dataTable, includeHeaders);
SpreadsheetsResource.ValuesResource.UpdateRequest request =
sheetService.Spreadsheets.Values.Update(requestBody, SpreadsheetId, cellToPassToService);
//request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
var response = request.Execute();
return response;
}));
}
public override int GetHashCode()
{
var hashCode = 1461567088;
hashCode = hashCode * -1521134295 + StartingCell.GetHashCode();
hashCode = hashCode * -1521134295 + EqualityComparer <Vector3> .Default.GetHashCode(RelativePosition);
hashCode = hashCode * -1521134295 + EqualityComparer <GameObject> .Default.GetHashCode(Type);
hashCode = hashCode * -1521134295 + Number.GetHashCode();
return(hashCode);
}
protected override void Execute(NativeActivityContext context)
{
string workbookFullName = FilePath.Get(context);
string workSheetName = WorksheetName.Get(context);
Range xlRange = null;
bool excelFileVisible = false;
object[,] TwoDimensionalArray = null;
DataTable inputDt = null;
try
{
string workbookName = string.Empty;
// Workbook xlWorkbook = null;
Worksheet xlWorksheet = null;
if (true == NeedToOpen)
{
excelFileVisible = true;
}
if (File.Exists(workbookFullName))
{
ExcelHelper.Shared.Close_OpenedFile(workbookFullName);
ExcelHelper.Shared.GetApp(excelFileVisible).DisplayAlerts = false;
workbookName = Path.GetFileName(workbookFullName);
string worksheetName = WorksheetName.Get(context);
dynamic workBookObject = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, worksheetName, false) != null;
if (false == sheetExist)
{
Status.Set(context, false);
if (NeedToClose == true)
{
workBookObject.Close();
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("Worksheet \"" + worksheetName + "\" does not exist in activity Excel_Row_Write", LogLevel.Error);
}
else
{
xlWorksheet = workBookObject.Sheets[workSheetName];
inputDt = Value.Get(context);
int totalRows = inputDt.Rows.Count;
int totalColumns = inputDt.Columns.Count;
string startingCell = StartingCell.Get(context);
if (null == startingCell)
{
startingCell = "A1";
}
String endingNum = startingCell.Substring(1);
int rangeEnd = totalRows - 1 + int.Parse(endingNum);
string colLetter = ExcelHelper.Shared.ColumnIndexToColumnLetter(totalColumns);
string endingCell = colLetter + rangeEnd;
TwoDimensionalArray = ExcelHelper.Shared.ConvertDataTableToArray(inputDt);
if (null == inputDt)
{
Status.Set(context, false);
}
else
{
xlRange = xlWorksheet.Range[startingCell, endingCell];
xlRange.Value = TwoDimensionalArray;
workBookObject.Save();
Status.Set(context, true);
}
if (true == NeedToClose)
{
workBookObject.Close();
}
if (false == NeedToClose && false == NeedToOpen)
{
workBookObject.Close();
}
if (false == NeedToClose && true == NeedToOpen)
{
workBookObject.Close();
ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
else
{
Status.Set(context, false);
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_Row_Write", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Status.Set(context, false);
Log.Logger.LogData(ex.Message + " in activity Excel_Row_Write", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
protected override void Execute(NativeActivityContext context)
{
string workbookFullName = FilePath.Get(context);
string workSheetName = WorksheetName.Get(context);
ExcelObject.Range xlRange = null;
dynamic workBookObject = null;
DataTable inputDt = null;
try
{
string workbookName = string.Empty;
ExcelObject._Worksheet xlWorksheet = null;
if (File.Exists(workbookFullName))
{
ExcelHelper.Shared.Close_OpenedFile(workbookFullName);
ExcelHelper.Shared.GetApp().DisplayAlerts = false;
workbookName = Path.GetFileName(workbookFullName);
string worksheetName = WorksheetName.Get(context);
workBookObject = ExcelHelper.Shared.GetApp().Workbooks.Open(workbookFullName);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, worksheetName, false) != null;
if (false == sheetExist)
{
Result.Set(context, false);
workBookObject.Close();
if (ExcelHelper.Shared.GetApp().Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("Worksheet \"" + worksheetName + "\" does not exist in activity Excel_Range_Write", LogLevel.Error);
}
else
{
inputDt = Value.Get(context);
xlWorksheet = workBookObject.Sheets[workSheetName];
string startingCell = StartingCell.Get(context);
if (startingCell == null)
{
startingCell = "A1";
}
int totalRows = inputDt.Rows.Count;
int totalColumns = inputDt.Columns.Count;
//String endAlpa = startingCell.Substring(0);
String endAlpa = new String(startingCell.Where(c => Char.IsLetter(c)).ToArray()).ToUpper();
int rangeEndColNum = ExcelHelper.Shared.NumberFromExcelColumn(endAlpa) - 1 + totalColumns;
string colLetter = ExcelHelper.Shared.ColumnIndexToColumnLetter(rangeEndColNum);
String endNum = new String(startingCell.Where(c => Char.IsDigit(c)).ToArray());
int rangeEndNum = int.Parse(endNum) - 1 + totalRows;
string endingCell = colLetter + rangeEndNum;
if (null == inputDt)
{
Result.Set(context, false);
}
else
{
object[,] TwoDimensionalArray = ExcelHelper.Shared.ConvertDataTableToArray(inputDt);
xlRange = xlWorksheet.Range[startingCell, endingCell];
xlRange.Value = TwoDimensionalArray;
Result.Set(context, true);
var range = xlWorksheet.get_Range("A1", "A1");
range.Select();
workBookObject.Save();
}
if (true == NeedToClose)
{
workBookObject.Close();
}
if (false == NeedToClose)
{
workBookObject.Close();
ExcelHelper.Shared.GetApp().Workbooks.Open(workbookFullName);
}
if (ExcelHelper.Shared.GetApp().Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
else
{
Result.Set(context, false);
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_Range_Write", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Result.Set(context, false);
Log.Logger.LogData(ex.Message + " in activity Excel_Range_Write", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
finally
{
ReleaseObject(xlRange);
//if (NeedToClose == true)
//{
// ReleaseObject(workBookObject);
//}
}
}
protected override void Execute(NativeActivityContext context)
{
string workbookFullName = FilePath.Get(context);
string workSheetName = WorksheetName.Get(context);
dynamic result = null;
bool excelFileVisible = false;
string workbookName = string.Empty;
Worksheet xlWorksheet = null;
Range xlRange = null;
try
{
if (true == NeedToOpen)
{
excelFileVisible = true;
}
if (File.Exists(workbookFullName))
{
ExcelHelper.Shared.Close_OpenedFile(workbookFullName);
ExcelHelper.Shared.GetApp(excelFileVisible).DisplayAlerts = false;
workbookName = Path.GetFileName(workbookFullName);
string worksheetName = WorksheetName.Get(context);
string startingCell = StartingCell.Get(context);
dynamic workBookObject = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, worksheetName, false) != null;
if (false == sheetExist)
{
if (NeedToClose == true)
{
workBookObject.Close();
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("Worksheet \"" + worksheetName + "\" does not exist in activity Excel_Read_Single_Row", LogLevel.Error);
}
else
{
// xlWorkbook = ExcelHelper.Shared.GetApp().Workbooks.Open(workbookFullName);
xlWorksheet = workBookObject.Sheets[workSheetName];
int totalColumns = xlWorksheet.UsedRange.Columns.Count;
int totalRows = xlWorksheet.UsedRange.Rows.Count;
string rowLetter = startingCell.Substring(1);
String newStr = startingCell.Substring(1);
Range lastCell = xlWorksheet.Cells[int.Parse(newStr), totalColumns];
if (lastCell.Value2 == null)
{
lastCell = lastCell.End[XlDirection.xlToLeft];
}
int lastColumn = lastCell.Column;
string colLetter = ExcelHelper.Shared.ColumnIndexToColumnLetter(lastColumn);
string endingCell = colLetter + newStr;
xlRange = xlWorksheet.Range[startingCell, endingCell];
result = xlRange.Value;
IEnumerable <object> rs = ((System.Collections.IEnumerable)result).Cast <object>();
Value.Set(context, rs);
if (true == NeedToClose)
{
workBookObject.Close();
}
if (false == NeedToClose && false == NeedToOpen)
{
workBookObject.Close();
}
if (false == NeedToClose && true == NeedToOpen)
{
workBookObject.Close();
ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
else
{
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_Read_Single_Row", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Value.Set(context, null);
Log.Logger.LogData(ex.Message + " in activity Excel_Read_Single_Row", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
finally
{
ReleaseObject(xlRange);
}
}
public override string ToString()
{
return(string.Format("{0}: {1} -> {2}", RobotId, StartingCell.ToString(), EndingCell.ToString()));
}
protected override void Execute(NativeActivityContext context)
{
string workbookFullName = FilePath.Get(context);
string workSheetName = WorksheetName.Get(context);
dynamic result = null;
bool excelFileVisible = false;
string workbookName = string.Empty;
Workbook xlWorkbook = null;
Worksheet xlWorksheet = null;
Range xlRange = null;
try
{
if (true == NeedToOpen)
{
excelFileVisible = true;
}
if (File.Exists(workbookFullName))
{
ExcelHelper.Shared.GetApp(excelFileVisible).DisplayAlerts = false;
workbookName = Path.GetFileName(workbookFullName);
string worksheetName = WorksheetName.Get(context);
string startingCell = StartingCell.Get(context);
dynamic workBookObject = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, worksheetName, false) != null;
if (false == sheetExist)
{
if (NeedToClose == true)
{
workBookObject.Close();
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
Log.Logger.LogData("Worksheet \"" + worksheetName + "\" does not exist in activity Excel_Read_Single_Row", LogLevel.Error);
}
else
{
xlWorkbook = ExcelHelper.Shared.GetApp().Workbooks.Open(workbookFullName);
xlWorksheet = xlWorkbook.Sheets[workSheetName];
int totalColumns = xlWorksheet.UsedRange.Columns.Count;
int totalRows = xlWorksheet.UsedRange.Rows.Count;
string colLetter = ExcelHelper.Shared.ColumnIndexToColumnLetter(totalColumns);
string rowLetter = startingCell.Substring(1);
string endingCell = colLetter + rowLetter;
xlRange = xlWorksheet.Range[startingCell, endingCell];
result = xlRange.Value;
Value.Set(context, result);
if (NeedToClose == true)
{
ExcelHelper.Shared.GetWorkbookByName(workbookName, true).Close();
}
if (ExcelHelper.Shared.GetApp().Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
else
{
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_Read_Single_Row", LogLevel.Error);
}
if (!ContinueOnError)
{
context.Abort();
}
}
catch (Exception ex)
{
Value.Set(context, null);
Log.Logger.LogData(ex.Message + " in activity Excel_Read_Single_Row", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
