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; }); } }); }
public Gen(Excel.Application application, CultureInfo culture) { try { _excelapp = application; _culture = culture; _range = application.ActiveWindow.RangeSelection; _singlesel = _range.Columns.Count == 1 && _range.Rows.Count == 1; _fnb = _singlesel ? new FieldNumber("Number of rows", "", 100, 1, true) : null; } catch (Exception ex) { new FrmException(_excelapp, ex).ShowDialog(); } }
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; } }); }); }
public void FrequencyChartContinuous() { var f0 = new FieldRange("Data selection", "Data", _range, false); var f1 = new FieldNumber("Nb of classes", "Number of classes", 10, true); var f2 = new FieldNumber("Class precision", "Number of digits after the decimal point", 0, 0, 15, true); new InputBox(_excelapp, "Create frequency chart", f0, f1, f2).Show(delegate { var rgsel = f0.Value; var nbClasses = (int)(f1.Value ?? 0); var precision = (int)(f2.Value ?? 0); var classes = Frequency.ComputeContinuousFrequencies(rgsel, nbClasses, precision); var title = "min=" + classes.Min.ToString() + " max=" + classes.Max.ToString() + " µ=" + Math.Round(classes.Average, 8).ToString() + " σ=" + Math.Round(classes.StandardDeviation, 8).ToString(); var ch = AddColumnClusteredChart(classes.Names, classes.Values, title, "Class", "Frequency"); }); }
public void Amount() { var f1 = new FieldNumber("Min amount", "Minimum amount", 0d, true); var f2 = new FieldNumber("Max amount", "Maximum amount", 100d, true); var f3 = new FieldCheckBox("Generate unique values", "", false); new InputBox(_excelapp, "Random amount generation", f1, f2, f3, _fnb).Show(delegate { var min = f1.Value ?? 0; var gap = (f2.Value ?? 0) - min; var random = new Random(); string format = Utils.GetCurrencyFormat(_culture.LCID, _culture.NumberFormat.CurrencySymbol, _culture.NumberFormat.CurrencyPositivePattern); var gen = new Generator(f3.Value, () => Math.Round(random.NextDouble() * gap + min, 2)); if (_singlesel) CellsOperator.AddDataToRange(_range, (int)_fnb.Value, format, (ref object value) => { value = gen.Next(); }); else new CellsOperator(_range, true).ForEachValue(format, (ref object value) => { value = gen.Next(); }); }); }
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_); }); } }); }
public void Combination() { var f1 = new FieldText("Set of values", "List of values seperated by a semicolon", "a;b;c", "^.+;.+$", "A minimum of 2 values is required", true); var f2 = new FieldNumber("Length (k)", "Length (k)", 2, 1, true); var f3 = new FieldCheckBox("With permutation : AB => AB,BA...", "", false, true); var f4 = new FieldCheckBox("With repetition : AB => AA,BB,AB...", "", false, true); var f5 = new FieldCheckBox("With sub length : AB => A,B,AB...", "", false, true); new InputBox(_excelapp, "Generate combination", f1, f2, f3, f4, f5).Show(delegate { var values = f1.Value.Split(';'); var n = values.Length; var k = (int)f2.Value; var order = f3.Value; var repeat = f4.Value; var subset = f5.Value; if (k > n) throw new InputError("Length (k) has to be superior or equal to the number of values"); var data = Combinaison.Get(values, k, order, repeat, subset); CellsOperator.AddDataToRange(_range, data); }); }
public void AddValue() { var f1 = new FieldNumber("Years", "Number of years to add", 0d); var f2 = new FieldNumber("Quaters", "Number of quaters to add", 0d); var f3 = new FieldNumber("Months", "Number of months to add", 0d); var f4 = new FieldNumber("Days", "Number of days to add", 0d); var f5 = new FieldNumber("Hours", "Number of hours to add", 0d); var f6 = new FieldNumber("Minutes", "Number of minutes to add", 0d); var f7 = new FieldNumber("Seconds", "Number of seconds to add", 0d); new InputBox(_excelapp, "Add value", f1, f2, f3, f4, f5, f6, f7).Show(delegate { var years = (int)f1.Value; var months = (int)f2.Value * 3 + (int)f3.Value; var ts = new TimeSpan((int)f4.Value, (int)f5.Value, (int)f6.Value, (int)f7.Value); new CellsOperator(_range).ForEachValue(null, delegate(ref object value) { System.DateTime datetime; if (Parser.ToDateTime(value, _culture, out datetime)) value = datetime.AddYears(years).AddMonths(months) + ts; }); }); }
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); } }); }); }
public void FrequencyTableContinuous() { var f0 = new FieldRange("Data selection", "Data", _range, false); var f1 = new FieldNumber("Nb of classes", "Number of classes", 10, true); var f2 = new FieldNumber("Class precision", "Number of digits after the decimal point", 0, 0, 15, true); var inputbox = new InputBox(_excelapp, "Create frequency chart", f0, f1, f2); inputbox.RestoreSelectionOnExit = false; inputbox.Show(delegate { var rgsel = f0.Value; var nbClasses = (int)(f1.Value ?? 0); var precision = (int)(f2.Value ?? 0); var classes = Frequency.ComputeContinuousFrequencies(rgsel, nbClasses, precision); var nbValues = classes.Values.Length; var table = new object[nbValues + 1, 2]; table[0, 0] = "Class"; table[0, 1] = "Frequence"; for (int i = 0; i < nbValues; i++) { table[i + 1, 0] = classes.Names[i]; table[i + 1, 1] = classes.Values[i]; } AddTableToNewSheet(table); }); }
public void Coordinates() { var f1 = new FieldNumber("Latitude min", "Minimum latitude", -90d, -90d, 90d, true); var f2 = new FieldNumber("Latitude max", "Maximum latitude", 90d, -90d, 90d, true); var f3 = new FieldNumber("Longitude min", "Maximum longitude", -180d, -180d, 180d, true); var f4 = new FieldNumber("Longitude max", "Minimum longitude", 180d, -180d, 180d, true); var f5 = new FieldCheckBox("Generate unique values", "", false, true); new InputBox(_excelapp, "Generate GPS coordinates", f1, f2, f3, f4, f5, _fnb).Show(delegate { var precision = 6; double minLat = f1.Value ?? 0; double gapLat = (f2.Value ?? 0) - minLat; double minLon = f3.Value ?? 0; double gapLon = (f4.Value ?? 0) - minLon; var unique = f5.Value; var random = new Random(); string format = "@"; var genLat = new Generator(unique, () => Math.Round(random.NextDouble() * gapLat + minLat, precision)); var genLon = new Generator(unique, () => Math.Round(random.NextDouble() * gapLon + minLon, precision)); if (_singlesel) CellsOperator.AddDataToRange(_range, (int)_fnb.Value, format, (ref object value) => { value = "'" + genLat.Next().ToString() + " " + genLon.Next().ToString(); }); else new CellsOperator(_range, true).ForEachValue(format, (ref object value) => { value = "'" + genLat.Next().ToString() + " " + genLon.Next().ToString(); }); }); }
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); }); } }); }
private void ToUnit(string title, int indexFrom, string format, Item[] Items) { if (indexFrom == -1) { string firstFormat = (string)((Excel.Range)_range.Cells[1, 1]).NumberFormat; if (firstFormat != "General" && firstFormat != "@") { for (int i = 0; i < Items.Length; i++) { if (firstFormat == string.Format(format, Items[i].Format)) { indexFrom = i; break; } } } } var f0 = new FieldList<Item>("From unit", "From unit", Items, indexFrom, true); var f1 = new FieldList<Item>("To unit", "To unit", Items, -1, true); var f2 = new FieldNumber("Rounding", "Number of digit after the decimal point (empty=disabled)", (double?)null, 0, 15); var f3 = new FieldCheckBox("Add symbol", "Add the currency symbol", true, true); new InputBox(_excelapp, title, f0, f1, f2, f3).Show(delegate { Item ItemFrom = (Item)f0.Value; Item ItemTo = (Item)f1.Value; int precision = (int)(f2.Value ?? -1); string cellFormat = f3.Value ? string.Format(format, ItemTo.Format) : ""; new CellsOperator(_range).ForEachValue(cellFormat, delegate(ref object value) { ItemFrom.ToUnit(ItemTo, _culture, ref value); if (value is double && precision != -1) value = Math.Round((double)value, precision); }); }); }
public void Lorem() { var f1 = new FieldNumber("Length", "Number of words", 10d, 0d, 5000d, true); new InputBox(_excelapp, "Random words generation", f1, _fnb).Show(delegate { var lorem = new RandomWords((int)(f1.Value ?? 0)); string format = "@"; CellsOperator.CellWriter writer = (ref object value) => { value = lorem.Next(); }; if (_singlesel) CellsOperator.AddDataToRange(_range, (int)_fnb.Value, format, writer); else new CellsOperator(_range, true).ForEachValue(format, writer); }); }
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); }); }); }
public void ToCurrency() { //var xmldoc = LoadXmlFile(@"http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml"); var xmldoc = LoadXmlFile(@"http://finance.yahoo.com/webservice/v1/symbols/allcurrencies/quote"); if (xmldoc == null) return; var currencies = ParseCurrencies(xmldoc); //Try to figure out the first cell currency int indexFrom = GetCellCurrencyIndex((Excel.Range)_range.Cells[1, 1], currencies); var f0 = new FieldList<CurrencyItem>("From currency", "From currency", currencies, indexFrom, true); var f1 = new FieldList<CurrencyItem>("To currency", "To currency", currencies, -1, true); var f2 = new FieldNumber("Rate", "Conversion rate", 0); var f3 = new FieldNumber("Rounding", "Number of digit after the decimal point (empty=disabled)", (double?)null, 0, 15, true); var f4 = new FieldCheckBox("Add symbol", "Add the metric symbol", true, true); EventHandler indexChanged = (sender, e) => { if (f0.Value == null || f1.Value == null) return; f2.Value = Math.Round(((CurrencyItem)f1.Value).Rate / ((CurrencyItem)f0.Value).Rate, 4); f2.Info = "Rate from Yahoo finance at " + (f0.Value.Time > f1.Value.Time ? f1.Value.ToDateTimeString() : f0.Value.ToDateTimeString()); }; ((ComboBox)f0.Control).SelectedIndexChanged += indexChanged; ((ComboBox)f1.Control).SelectedIndexChanged += indexChanged; new InputBox(_excelapp, "To Currency", f0, f1, f2, f3, f4).Show(delegate { int lcid = ((CurrencyItem)f0.Value).LCID; char decimalSeparator = ((CurrencyItem)f0.Value).DecimalCharacter; decimal rate = (decimal)(f2.Value ?? 0); int precision = (int)(f3.Value ?? -1); string format = f4.Value ? ((CurrencyItem)f1.Value).Format : null; new CellsOperator(_range).ForEachValue(format, delegate(ref object value) { decimal number; if (Parser.ToCurrency(value, decimalSeparator, out number)) { if (precision == -1) value = number * rate; else value = Math.Round(number * rate, precision); } }); }); }
public void Number() { var f1 = new FieldNumber("Min number", "Minimum number", 0d, true); var f2 = new FieldNumber("Max number", "Maximum number", 1000d, true); var f3 = new FieldNumber("Precision", "Precision for floating numbers", 0d, 0d, 15, true); var f4 = new FieldCheckBox("Generate unique values", "", false, true); new InputBox(_excelapp, "Random number generation", f1, f2, f3, f4, _fnb).Show(delegate { var min = f1.Value ?? 0; var gap = f2.Value ?? double.MaxValue - min; var precision = (int)(f3.Value ?? 0); var unique = f4.Value; var random = new Random(); string format = null; var gen = new Generator(f4.Value, () => Math.Round(random.NextDouble() * gap + min, precision)); if (_singlesel) CellsOperator.AddDataToRange(_range, (int)_fnb.Value, format, (ref object value) => { value = gen.Next(); }); else new CellsOperator(_range, true).ForEachValue(format, (ref object value) => { value = gen.Next(); }); }); }