예제 #1
0
 public void ReplaceIf()
 {
     var f1 = new FieldRadioBT("If value is", new string[] { "Inferior", "Superior" }, "Compare type", 0);
     var f2 = new FieldNumber("To", "Value to compare with", 0);
     var f3 = new FieldNumber("Replace with", "Replacement value", 0);
     new InputBox(_excelapp, "Replace if", f1, f2, f3).Show(delegate {
         double bound = f2.Value ?? 0;
         double replacement = f3.Value ?? 0;
         if (f1.Value == 1) {
             new CellsOperator(_range).ForEachValue(null, delegate(ref object value) {
                 if (value is double && (double)value > bound)
                     value = replacement;
                 else if (value is decimal && (decimal)value > (decimal)bound)
                     value = replacement;
             });
         } else {
             new CellsOperator(_range).ForEachValue(null, delegate(ref object value) {
                 if (value is double && (double)value < bound)
                     value = replacement;
                 else if (value is decimal && (decimal)value < (decimal)bound)
                     value = replacement;
             });
         }
     });
 }
예제 #2
0
 public void FrequencyChartDiscete()
 {
     var f0 = new FieldRange("Data selection", null, _range, false);
     var f1 = new FieldRadioBT("Order by", new string[] { "Name", "Freq Asc", "Freq Desc" }, null, 0, true);
     new InputBox(_excelapp, "Create frequency chart", f0, f1).Show(delegate {
         var rgsel = f0.Value;
         var orderByFrequencyAsc = f1.Value == 1;
         var orderByFrequencyDesc = f1.Value == 2;
         var freq = Frequency.ComputeDisceteFrequencies(rgsel);
         if (orderByFrequencyAsc)
             freq.SortByFrequenceAsc();
         else if (orderByFrequencyDesc)
             freq.SortByFrequenceDesc();
         var ch = AddColumnClusteredChart(freq.Values, freq.Frequencies, null, "Value", "Frequency");
     });
 }
예제 #3
0
 public void Add()
 {
     var f1 = new FieldText("Characters", "Characters to add", "");
     var f2 = new FieldRadioBT("", new string[] { "Left", "Rigth" }, "Position", 0, true);
     new InputBox(_excelapp, "Add", f1, f2).Show(delegate {
         string str = f1.Value;
         bool left = f2.Value == 0;
         new CellsOperator(_range).ForEachValue(null, delegate(ref object value) {
             if (value is string) {
                 if (left)
                     value = str + (string)value;
                 else
                     value = (string)value + str;
             }
         });
     });
 }
예제 #4
0
 public void Add()
 {
     var f0 = new FieldNumber("Value", "Value", 0d);
     var f1 = new FieldRadioBT("Operation", new string[] { "Add", "Subtract" }, null, 0);
     new InputBox(_excelapp, "Add value", f0, f1).Show(delegate {
         bool add = f1.Value == 0;
         double val = f0.Value ?? 0;
         new CellsOperator(_range).ForEachValue(null, delegate(ref object value) {
             if (value is double) {
                 if (add)
                     value = (double)value + val;
                 else
                     value = (double)value - val;
             }
         });
     });
 }
예제 #5
0
 public void Pad()
 {
     var f1 = new FieldText("Character", "Character to add", "0", "^.$", "One character only");
     var f2 = new FieldNumber("Length", "Total length", 10d, 1, 1000);
     var f3 = new FieldRadioBT("Insert position", new[] { "Left", "Rigth" }, "Insert position", 0);
     new InputBox(_excelapp, "Padding", f1, f2, f3).Show(delegate {
         char char_ = f1.Value[0];
         int len = (int)(f2.Value ?? 0);
         if (f3.Value == 0) {
             new CellsOperator(_range).ForEachValue("@", delegate(ref object value) {
                 value = value.ToString().PadLeft(len, char_);
             });
         } else if (f3.Value == 1) {
             new CellsOperator(_range).ForEachValue("@", delegate(ref object value) {
                 value = value.ToString().PadRight(len, char_);
             });
         }
     });
 }
예제 #6
0
 public void Multiply()
 {
     var f0 = new FieldNumber("Value", "Value", 0d);
     var f1 = new FieldRadioBT("Operation", new string[] { "Multiply", "Divide" }, null, 0);
     var f2 = new FieldNumber("Rounding", "Number of digit after the decimal point (empty=disabled)", (double?)null, 0);
     new InputBox(_excelapp, "Multiply / Divide", f0, f1, f2).Show(delegate {
         bool multiply = f1.Value == 0;
         double val = f0.Value ?? 0;
         int round = (int)(f2.Value ?? -1);
         new CellsOperator(_range).ForEachValue(null, delegate(ref object value) {
             if (value is double) {
                 if (multiply)
                     value = (double)value * val;
                 else
                     value = (double)value / val;
                 if (round != -1)
                     value = Math.Round((double)value, round);
             }
         });
     });
 }
예제 #7
0
        public void FrequencyTableDiscete()
        {
            var f0 = new FieldRange("Data selection", null, _range, false);
            var f1 = new FieldRadioBT("Order by", new string[] { "Value", "Freq Asc", "Freq Desc" }, null, 0, true);
            var inputbox = new InputBox(_excelapp, "Create frequency chart", f0, f1);
            inputbox.RestoreSelectionOnExit = false;
            inputbox.Show(delegate {
                var rgsel = f0.Value;
                var orderByFrequencyAsc = f1.Value == 1;
                var orderByFrequencyDesc = f1.Value == 2;
                var freq = Frequency.ComputeDisceteFrequencies(rgsel);
                if (orderByFrequencyAsc)
                    freq.SortByFrequenceAsc();
                else if (orderByFrequencyDesc)
                    freq.SortByFrequenceDesc();

                var nbValues = freq.Frequencies.Length;
                var table = new object[nbValues + 1, 2];
                table[0, 0] = "Value";
                table[0, 1] = "Frequence";
                for (int i = 0; i < nbValues; i++) {
                    table[i + 1, 0] = freq.Values[i];
                    table[i + 1, 1] = freq.Frequencies[i];
                }
                AddTableToNewSheet(table);
            });
        }
예제 #8
0
 public void Truncate()
 {
     var f1 = new FieldNumber("Length", "Maximum length for the text", 256d, 0, 1000);
     var f2 = new FieldRadioBT("Position", new[] { "Left", "Rigth" }, "Position", 0);
     new InputBox(_excelapp, "Truncate", f1, f2).Show(delegate {
         int maxlen = (int)(f1.Value ?? 0);
         if (f2.Value == 0)
             new CellsOperator(_range).ForEachValue(null, delegate(ref object value) {
                 if (value is string)
                     if (((string)value).Length > maxlen)
                         value = ((string)value).Substring(0, maxlen);
             });
         else
             new CellsOperator(_range).ForEachValue(null, delegate(ref object value) {
                 if (value is string)
                     if (((string)value).Length > maxlen)
                         value = ((string)value).Substring(((string)value).Length - maxlen);
             });
     });
 }
예제 #9
0
 public void Split()
 {
     if (_range.Areas.Count != 1 || _range.Areas[1].Columns.Count != 1) {
         MsgBox.ShowError(_excelapp, "Select one column only.");
         return;
     }
     var f0 = new FieldRadioBT("Type", new string[] { "Separator", "Regex" }, null, 0, true);
     var f1 = new FieldText("Separator", "String separator", "", true);
     var f2 = new FieldText("Regex pattern", "Regex pattern", @"[^ ]+", true);
     f0.ValueChanged += (sender, e) => {
         f1.Control.Enabled = f0.Value == 0;
         f2.Control.Enabled = f0.Value == 1;
     };
     f2.Control.Enabled = false;
     new InputBox(_excelapp, "Replace (Regex)", f0, f1, f2).Show(delegate {
         bool useSeparator = f0.Value == 0;
         var range = (Excel.Range)_range.Areas[1].Columns[1];
         try {
             _excelapp.ScreenUpdating = false;
             Undo.AddUndoArea(range);
             range.Copy();
             var data = Clipboard.GetText();
             _excelapp.CutCopyMode = 0;
             var res = new StringBuilder();
             if (useSeparator) {
                 var separator = f1.Value;
                 res.Append(data.Replace(separator, "\t"));
             } else {
                 string pattern = f2.Value;
                 var rows = data.Split(new string[] { "\r\n" }, StringSplitOptions.None);
                 int nbRows = rows.Length;
                 for (int r = 0; r < nbRows; r++) {
                     var matches = Regex.Matches(rows[r], pattern);
                     int nbMatch = matches.Count;
                     for (int i = 0; i < nbMatch; i++) {
                         if (i != 0) res.Append('\t');
                         res.Append(matches[i].Value);
                     }
                     res.Append("\r\n");
                 }
             }
             Clipboard.SetText(res.ToString());
             range.PasteSpecial();
             Clipboard.Clear();
             (_excelapp.ActiveWindow.RangeSelection).NumberFormat = "";
         } catch { throw; } finally {
             _excelapp.ScreenUpdating = true;
         }
     });
 }
예제 #10
0
        public void ToSQLinsert()
        {
            Excel.Range dataRange; string[] headers;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var f1 = new FieldText("Table name", "Name of the table", _range.ListObject == null ? "TABLENAME" : _range.ListObject.Name);
            var f2 = new FieldText("Fields", "Fields name separated by a semicolon", string.Join(";", headers));
            var f3 = new FieldText("Null value", "Null expression", "NULL", true);
            var f4 = new FieldText("DateTime", "DateTime format", "yyyy-MM-ddTHH:mm:ss", true);
            var f5 = new FieldRadioBT("Insert type", new[] { "SingleInsert", "MultipleInsert" }, "Type of insert", 0, true);
            new InputBox(_excelapp, "Copy to SQL INSERT", f1, f2, f3, f4, f5).Show(delegate {
                var rows = new List<string>();
                var tablename = f1.Value;
                var columns = f2.Value.Replace(';', ',');
                var nullValue = f3.Value;
                var datetimeformat = f4.Value;
                var singleinsert = f5.Value == 0;
                var multipleinsert = f5.Value == 1;
                const bool unionall = false;

                var row = new StringBuilder();
                new CellsOperator(dataRange).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
                    if (isFirstCol == false)
                        row.Append(",");
                    if (value == null)
                        row.Append(nullValue);
                    else if (value is string)
                        row.Append("'" + ((string)value).Replace("'", "''") + "'");
                    else if (value is bool)
                        row.Append((bool)value ? "TRUE" : "FALSE");
                    else if (value is System.DateTime)
                        row.Append("'" + ((System.DateTime)value).ToString(datetimeformat) + "'");
                    else
                        row.Append(value);
                    if (isLastCol) {
                        rows.Add(row.ToString());
                        row.Length = 0;
                    }
                }
                );

                var sql = new StringBuilder();
                if (!string.IsNullOrEmpty(columns))
                    columns = "(" + columns + ")";
                if (singleinsert) {
                    sql.Append("INSERT INTO " + tablename + " VALUES\r\n");
                    var nbRow = rows.Count;
                    for (var r = 0; r < rows.Count; r++) {
                        if (r == (nbRow - 1))
                            sql.Append("(" + rows[r] + ");");
                        else
                            sql.Append("(" + rows[r] + "),\r\n");
                    }
                } else if (multipleinsert) {
                    foreach (var s in rows)
                        sql.Append("INSERT INTO " + tablename + columns + " VALUES(" + s + ");\r\n");
                } else if (unionall) {
                    //sql.Append("INSERT INTO " + tablename + "\r\n");
                    //int nbRow = rows.Count;
                    //for (int r = 0; r < rows.Count; r++) {
                    //    if (r == (nbRow - 1))
                    //        sql.Append("SELECT " + rows[r] + ";");
                    //    else
                    //        sql.Append("SELECT " + rows[r] + "\r\nUNION ALL");
                    //}
                }
                if (sql.Length != 0)
                    System.Windows.Forms.Clipboard.SetText(sql.ToString());
            });
        }
예제 #11
0
        public void Columns()
        {
            var defaultTemplate = new StringBuilder();
            var nbCol = _range.Columns.Count;
            for (int i = 1; i <= nbCol; i++)
                defaultTemplate.Append("\\" + i);

            var f0 = new FieldRadioBT("Use", new string[] { "Delimiter", "Template" }, "Select delimiter or template", 0, true);
            var f1 = new FieldText("Delimiter", "Delimiter to add between columns", "", true);
            var f2 = new FieldText("Template", "Template - \\n is the cell text at column n", defaultTemplate.ToString(), true);
            f0.ValueChanged += (sender, e) => {
                f1.Control.Enabled = f0.Value == 0;
                f2.Control.Enabled = f0.Value == 1;
            };
            f2.Control.Enabled = false;
            new InputBox(_excelapp, "Copy merged columns", f0, f1, f2).Show(delegate {
                bool useDeleimiter = f0.Value == 0;
                var delimiter = f1.Value;
                var pattern = f2.Value;
                try {
                    _excelapp.ScreenUpdating = false;
                    System.Windows.Forms.Clipboard.Clear();
                    var range = _excelapp.Intersect(_range, _range.Worksheet.UsedRange);
                    if (range == null) return;
                    range.Copy();
                    var data = System.Windows.Forms.Clipboard.GetText();
                    _excelapp.CutCopyMode = 0;
                    var res = new StringBuilder();
                    if (useDeleimiter) {
                        res.Append(data.Replace("\t", delimiter));
                    } else {
                        var rows = data.TrimEnd('\r', '\n').Split(new string[] { "\r\n" }, StringSplitOptions.None);
                        foreach (string row in rows) {
                            var cells = row.Split('\t');
                            string strRow = pattern;
                            for (int i = 0; i < cells.Length; i++)
                                strRow = strRow.Replace("\\" + (i + 1), cells[i]);
                            res.Append(strRow + "\r\n");
                        }
                    }
                    System.Windows.Forms.Clipboard.SetText(res.ToString());
                } catch { throw; } finally {
                    _excelapp.ScreenUpdating = true;
                }
            });
        }
예제 #12
0
 public void Round()
 {
     var f0 = new FieldRadioBT("", new string[] { "Nearest", "Down", "Up" }, "", 0);
     var f1 = new FieldNumber("Precision", "Number of digit after the decimal point", 0, 0, 15);
     new InputBox(_excelapp, "Round", f0, f1).Show(delegate {
         int precision = (int)(f1.Value ?? 0);
         double raise = Math.Pow(10, precision);
         double down = Math.Pow(10, -1 * precision);
         if (f0.Value == 1) {
             new CellsOperator(_range).ForEachValue(null, delegate(ref object value) {
                 if (value is double)
                     value = Math.Floor((double)value * raise) * down;
                 if (value is decimal)
                     value = Math.Floor((decimal)value * (decimal)raise) * (decimal)down;
             });
         } if (f0.Value == 2) {
             new CellsOperator(_range).ForEachValue(null, delegate(ref object value) {
                 if (value is double)
                     value = Math.Ceiling((double)value * raise) * down;
                 if (value is decimal)
                     value = Math.Ceiling((decimal)value * (decimal)raise) * (decimal)down;
             });
         } else {
             new CellsOperator(_range).ForEachValue(null, delegate(ref object value) {
                 if (value is double)
                     value = Math.Round((double)value, precision);
                 else if (value is decimal)
                     value = Math.Round((decimal)value, precision);
             });
         }
     });
 }