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); } }