Beispiel #1
0
        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);
            }
        }
Beispiel #2
0
        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);
            }
        }
Beispiel #3
0
        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));
            }
        }
Beispiel #4
0
        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);
            }
        }
Beispiel #5
0
        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);
            }
        }
Beispiel #6
0
        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);
        }
Beispiel #7
0
        //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);
            }
        }
Beispiel #8
0
        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);
            }
        }
Beispiel #9
0
        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);
            }
        }
Beispiel #10
0
        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);
            }
        }