protected override void Execute(NativeActivityContext context)
{
base.Execute(context);
var cell = Cell.Get(context);
var value = Value.Get(context);
var formula = Formula.Get(context);
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(cell);
if (!string.IsNullOrEmpty(formula))
{
range.Formula = formula;
}
else
{
range.Value2 = value;
}
//cleanup();
var sheetPassword = SheetPassword.Get(context);
if (string.IsNullOrEmpty(sheetPassword))
{
sheetPassword = null;
}
if (!string.IsNullOrEmpty(sheetPassword) && worksheet != null)
{
worksheet.Protect(sheetPassword);
}
}
protected override void Execute(CodeActivityContext context)
{
base.Execute(context);
var sheetPassword = SheetPassword.Get(context);
if (string.IsNullOrEmpty(sheetPassword))
{
sheetPassword = null;
}
if (worksheet != null)
{
worksheet.Unprotect(sheetPassword);
}
}
protected override void Execute(CodeActivityContext context)
{
base.Execute(context);
var sheetPassword = SheetPassword.Get(context);
if (string.IsNullOrEmpty(sheetPassword))
{
sheetPassword = null;
}
if (!string.IsNullOrEmpty(sheetPassword) && worksheet != null)
{
worksheet.Protect(sheetPassword, DrawingObjects.Get(context), Contents.Get(context), Scenarios.Get(context),
UserInterfaceOnly.Get(context), AllowFormattingCells.Get(context), AllowFormattingColumns.Get(context), AllowFormattingRows.Get(context),
AllowInsertingColumns.Get(context), AllowInsertingRows.Get(context), AllowInsertingHyperlinks.Get(context), AllowDeletingColumns.Get(context),
AllowDeletingRows.Get(context), AllowSorting.Get(context), AllowFiltering.Get(context), AllowUsingPivotTables.Get(context));
}
}
protected override void Execute(NativeActivityContext context)
{
base.Execute(context);
var cell = Cell.Get(context);
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(cell);
Formula.Set(context, range.Formula);
Range.Set(context, range);
if (this.ResultType == typeof(string))
{
if (range.Value2 == null)
{
context.SetValue(Result, null);
}
try
{
if (range.Value2 != null)
{
context.SetValue(Result, range.Value2.ToString());
}
else
{
context.SetValue(Result, null);
}
}
catch (Exception)
{
context.SetValue(Result, null);
}
}
else
{
context.SetValue(Result, (TResult)range.Value2);
}
var sheetPassword = SheetPassword.Get(context);
if (string.IsNullOrEmpty(sheetPassword))
{
sheetPassword = null;
}
if (!string.IsNullOrEmpty(sheetPassword) && worksheet != null)
{
worksheet.Protect(sheetPassword);
}
}
protected override void Execute(CodeActivityContext context)
{
//Range xlActiveRange = base.worksheet.UsedRange;
var ignoreEmptyRows = (IgnoreEmptyRows != null ? IgnoreEmptyRows.Get(context) : false);
var useHeaderRow = (UseHeaderRow != null? UseHeaderRow.Get(context) : false);
base.Execute(context);
var cells = Cells.Get(context);
Microsoft.Office.Interop.Excel.Range range = null;
if (string.IsNullOrEmpty(cells))
{
range = base.worksheet.UsedRange;
//Range last = base.worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);
//Range range = base.worksheet.get_Range("A1", last);
//int lastUsedRow = range.Row;
//int lastUsedColumn = range.Column;
}
else
{
if (!cells.Contains(":"))
{
throw new ArgumentException("Cell should contain a range dedenition, meaning it should contain a colon :");
}
range = base.worksheet.get_Range(cells);
}
//object[,] valueArray = (object[,])range.Value;
object[,] valueArray = (object[, ])range.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);
if (valueArray != null)
{
var o = ProcessObjects(useHeaderRow, ignoreEmptyRows, valueArray);
System.Data.DataTable dt = o as System.Data.DataTable;
dt.TableName = base.worksheet.Name;
if (string.IsNullOrEmpty(dt.TableName))
{
dt.TableName = "Unknown";
}
DataTable.Set(context, dt);
}
//dt.AsEnumerable();
//string json = Newtonsoft.Json.JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.Indented);
////context.SetValue(Json, JObject.Parse(json));
//context.SetValue(Json, JArray.Parse(json));
if (ClearFormats.Get(context))
{
worksheet.Columns.ClearFormats();
worksheet.Rows.ClearFormats();
}
if (lastUsedColumn != null || lastUsedRow != null)
{
// Unhide All Cells and clear formats
// Detect Last used Row - Ignore cells that contains formulas that result in blank values
//int lastRowIgnoreFormulas = worksheet.Cells.Find(
// "*",
// System.Reflection.Missing.Value,
// XlFindLookIn.xlValues,
// XlLookAt.xlWhole,
// XlSearchOrder.xlByRows,
// XlSearchDirection.xlPrevious,
// false,
// System.Reflection.Missing.Value,
// System.Reflection.Missing.Value).Row;
// Detect Last Used Column - Ignore cells that contains formulas that result in blank values
//int lastColIgnoreFormulas = worksheet.Cells.Find(
// "*",
//System.Reflection.Missing.Value,
// System.Reflection.Missing.Value,
// System.Reflection.Missing.Value,
// XlSearchOrder.xlByColumns,
// XlSearchDirection.xlPrevious,
// false,
// System.Reflection.Missing.Value,
// System.Reflection.Missing.Value).Column;
// Detect Last used Row / Column - Including cells that contains formulas that result in blank values
//int lastColIncludeFormulas = worksheet.UsedRange.Columns.Count;
//int lastColIncludeFormulas = worksheet.UsedRange.Rows.Count;
//range = base.worksheet.UsedRange;
int _lastUsedColumn = worksheet.UsedRange.Columns.Count;
int _lastUsedRow = worksheet.UsedRange.Rows.Count;
if (lastUsedColumn != null)
{
context.SetValue(lastUsedColumn, ColumnIndexToColumnLetter(_lastUsedColumn));
}
if (lastUsedRow != null)
{
context.SetValue(lastUsedRow, _lastUsedRow);
}
}
var sheetPassword = SheetPassword.Get(context);
if (string.IsNullOrEmpty(sheetPassword))
{
sheetPassword = null;
}
if (!string.IsNullOrEmpty(sheetPassword) && worksheet != null)
{
worksheet.Protect(sheetPassword);
}
}
protected override void Execute(NativeActivityContext context)
{
var readPassword = ReadPassword.Get(context);
if (string.IsNullOrEmpty(readPassword))
{
readPassword = null;
}
var writePassword = WritePassword.Get(context);
if (string.IsNullOrEmpty(writePassword))
{
writePassword = null;
}
filename = Filename.Get(context);
officewrap.application.Visible = true;
// officewrap.application.Visible = Visible.Get(context);
if (!string.IsNullOrEmpty(filename))
{
filename = Environment.ExpandEnvironmentVariables(filename);
}
workbook = (Workbook != null ? Workbook.Get(context) : null);
if (!string.IsNullOrEmpty(filename) && workbook != null)
{
if (workbook.FullName.ToLower() != filename.ToLower())
{
try
{
workbook.Close(true);
workbook = null;
Task.Run(() => { GC.Collect(); });
}
catch (Exception)
{
throw;
}
}
}
if (!string.IsNullOrEmpty(filename) && workbook == null)
{
foreach (Microsoft.Office.Interop.Excel.Workbook w in officewrap.application.Workbooks)
{
if (w.FullName == filename)
{
workbook = w;
break;
}
}
if (workbook == null)
{
officewrap.application.DisplayAlerts = false;
//application.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow;
if (System.IO.File.Exists(filename))
{
//workbook = officewrap.application.Workbooks.Open(filename, ReadOnly: false);
workbook = officewrap.application.Workbooks.Open(filename, ReadOnly: false,
Password: readPassword, WriteResPassword: writePassword);
}
else
{
workbook = officewrap.application.Workbooks.Add();
workbook.Activate();
//workbook.SaveCopyAs(filename);
workbook.SaveAs(Filename: filename);
}
officewrap.application.DisplayAlerts = true;
}
}
if (workbook == null)
{
workbook = officewrap.application.ActiveWorkbook;
}
if (workbook == null)
{
}
var _worksheet = (Worksheet != null ? Worksheet.Get(context) : null);
worksheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
if (!string.IsNullOrEmpty(_worksheet))
{
foreach (object obj in workbook.Sheets)
{
Worksheet s = obj as Worksheet;
if (s != null && s.Name == _worksheet)
{
s.Activate();
worksheet = s;
}
}
}
var sheetPassword = SheetPassword.Get(context);
if (string.IsNullOrEmpty(sheetPassword))
{
sheetPassword = null;
}
if (!string.IsNullOrEmpty(sheetPassword) && worksheet != null)
{
worksheet.Unprotect(sheetPassword);
}
//Application.Set(context, application);
Workbook.Set(context, workbook);
}
//public void cleanup()
//{
// //workbook = null;
// //worksheet = null;
// Task.Run(() => { GC.Collect(); });
//}
protected override void Execute(CodeActivityContext context)
{
filename = Filename.Get(context);
officewrap.application.Visible = Visible.Get(context);
if (!string.IsNullOrEmpty(filename))
{
filename = Environment.ExpandEnvironmentVariables(filename);
}
workbook = (Workbook != null ? Workbook.Get(context) : null);
if (!string.IsNullOrEmpty(filename) && workbook != null)
{
if (workbook.FullName.ToLower() != filename.ToLower())
{
try
{
workbook.Close(true);
workbook = null;
Task.Run(() => { GC.Collect(); });
}
catch (Exception)
{
throw;
}
}
}
if (workbook == null)
{
doOpen(context);
}
string _worksheet = (Worksheet != null ? Worksheet.Get(context) : null);
try
{
worksheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
}
catch (Exception)
{
worksheet = null;
workbook = null;
Task.Run(() => { GC.Collect(); });
}
if (worksheet == null)
{
officewrap.Quit();
doOpen(context);
worksheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
}
if (!string.IsNullOrEmpty(_worksheet))
{
bool found = false;
foreach (object obj in workbook.Sheets)
{
Worksheet s = obj as Worksheet;
if (s != null && s.Name == _worksheet)
{
s.Activate();
worksheet = s;
found = true;
break;
}
}
if (!found)
{
worksheet = workbook.Sheets.Add(Type.Missing, workbook.Sheets[workbook.Sheets.Count], 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet) as Microsoft.Office.Interop.Excel.Worksheet;
worksheet.Name = _worksheet;
}
}
if (Workbook != null)
{
Workbook.Set(context, workbook);
}
var sheetPassword = SheetPassword.Get(context);
if (string.IsNullOrEmpty(sheetPassword))
{
sheetPassword = null;
}
if (!string.IsNullOrEmpty(sheetPassword) && worksheet != null)
{
worksheet.Unprotect(sheetPassword);
}
}
protected override void Execute(NativeActivityContext context)
{
base.Execute(context);
var cell = Cell.Get(context);
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(cell);
Formula.Set(context, range.Formula);
Range.Set(context, range);
object value = range.Value;
if (range.Value2 is TResult val2)
{
context.SetValue(Result, val2);
}
else if (range.Value is TResult val)
{
context.SetValue(Result, val);
}
else
{
if (value != null && typeof(TResult) == typeof(bool))
{
if (value is double d)
{
value = (d > 0);
}
if (value is int i)
{
value = (i > 0);
}
if (value is string s)
{
value = (s == "1" || s.ToLower() == "true");
}
}
if (value != null && value.GetType() == typeof(double) && typeof(TResult) == typeof(int))
{
if (value != null)
{
value = int.Parse(value.ToString());
}
if (value == null)
{
value = int.Parse("0");
}
}
if (value != null && value.GetType() == typeof(DateTime) && typeof(TResult) == typeof(string))
{
value = value.ToString();
}
if (value != null && value.GetType() == typeof(int) && typeof(TResult) == typeof(string))
{
value = value.ToString();
}
if (value != null && value.GetType() == typeof(double) && typeof(TResult) == typeof(string))
{
value = value.ToString();
}
if (value != null)
{
context.SetValue(Result, (TResult)value);
}
if (value == null)
{
context.SetValue(Result, default(TResult));
}
}
var sheetPassword = SheetPassword.Get(context);
if (string.IsNullOrEmpty(sheetPassword))
{
sheetPassword = null;
}
if (!string.IsNullOrEmpty(sheetPassword) && worksheet != null)
{
worksheet.Protect(sheetPassword);
}
}
protected override void Execute(CodeActivityContext context)
{
var useHeaderRow = (UseHeaderRow != null ? UseHeaderRow.Get(context) : false);
base.Execute(context);
var dt = DataTable.Get(context);
if (dt == null)
{
throw new ArgumentException("DataTable is null", "DataTable");
}
if (dt.Rows.Count == 0)
{
return;
}
var cells = Cells.Get(context);
Microsoft.Office.Interop.Excel.Range xlRange = null;
if (string.IsNullOrEmpty(cells))
{
xlRange = base.worksheet.UsedRange;
// Find the last real row
if (xlRange.Count > 1)
{
try
{
var nInLastRow = base.worksheet.Cells.Find("*", System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, XlSearchOrder.xlByRows, XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
//// Find the last real column
var nInLastCol = base.worksheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, XlSearchOrder.xlByColumns, XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Column;
// var o = base.worksheet.Cells[nInLastRow, nInLastCol];
var o = base.worksheet.Cells[nInLastRow + 1, 1];
xlRange = o as Range;
// Range last = base.worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);
// xlRange = base.worksheet.get_Range("A1", last);
}
catch (Exception)
{
}
}
}
else
{
xlRange = base.worksheet.get_Range(cells);
}
// var idx = 0;
//Header
if (useHeaderRow)
{
for (var i = 0; i < dt.Columns.Count; i++)
{
xlRange.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
// idx = 1;
var o = base.worksheet.Cells[xlRange.Row + 1, xlRange.Column];
xlRange = o as Range;
}
//Datas
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
for (int r = 0; r < dt.Rows.Count; r++)
{
System.Data.DataRow dr = dt.Rows[r];
for (int c = 0; c < dt.Columns.Count; c++)
{
arr[r, c] = dr[c];
}
}
// Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[topRow, 1];
Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[xlRange.Row + dt.Rows.Count - 1, (xlRange.Column + dt.Columns.Count) - 1];
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(xlRange, c2);
//range.Value = arr;
range.set_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault, arr);
//for (var i = 0; i < dt.Rows.Count; i++)
//{
// for (var j = 0; j < dt.Columns.Count; j++)
// {
// xlRange.Cells[i + idx, j + 1] = dt.Rows[i][j];
// }
//}
var sheetPassword = SheetPassword.Get(context);
if (string.IsNullOrEmpty(sheetPassword))
{
sheetPassword = null;
}
if (!string.IsNullOrEmpty(sheetPassword) && worksheet != null)
{
worksheet.Protect(sheetPassword);
}
}
protected override void Execute(CodeActivityContext context)
{
//Range xlActiveRange = base.worksheet.UsedRange;
var ignoreEmptyRows = (IgnoreEmptyRows != null ? IgnoreEmptyRows.Get(context) : false);
var useHeaderRow = (UseHeaderRow != null ? UseHeaderRow.Get(context) : false);
var guessColumnType = (GuessColumnType != null ? GuessColumnType.Get(context) : false);
base.Execute(context);
var cells = Cells.Get(context);
Microsoft.Office.Interop.Excel.Range range = null;
if (string.IsNullOrEmpty(cells))
{
range = base.worksheet.UsedRange;
}
else
{
if (!cells.Contains(":"))
{
throw new ArgumentException("Cell should contain a range dedenition, meaning it should contain a colon :");
}
range = base.worksheet.get_Range(cells);
}
object[,] valueArray = (object[, ])range.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);
if (valueArray != null)
{
var o = ProcessObjects(range, useHeaderRow, guessColumnType, ignoreEmptyRows, valueArray);
System.Data.DataTable dt = o as System.Data.DataTable;
dt.TableName = base.worksheet.Name;
if (string.IsNullOrEmpty(dt.TableName))
{
dt.TableName = "Unknown";
}
DataTable.Set(context, dt);
}
if (ClearFormats.Get(context))
{
worksheet.Columns.ClearFormats();
worksheet.Rows.ClearFormats();
}
if (lastUsedColumn != null || lastUsedRow != null)
{
//range = base.worksheet.UsedRange;
int _lastUsedColumn = worksheet.UsedRange.Columns.Count;
int _lastUsedRow = worksheet.UsedRange.Rows.Count;
if (lastUsedColumn != null)
{
context.SetValue(lastUsedColumn, ColumnIndexToColumnLetter(_lastUsedColumn));
}
if (lastUsedRow != null)
{
context.SetValue(lastUsedRow, _lastUsedRow);
}
}
var sheetPassword = SheetPassword.Get(context);
if (string.IsNullOrEmpty(sheetPassword))
{
sheetPassword = null;
}
if (!string.IsNullOrEmpty(sheetPassword) && worksheet != null)
{
worksheet.Protect(sheetPassword);
}
}
