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 CellsText() { var f1 = new FieldText("Cells delimiter", "Delimiter character", ";", true); var f2 = new FieldCheckBox("Include empty cells", "", false); new InputBox(_excelapp, "Copy joined cells text", f1, f2).Show(delegate { var cellDelimiter = f1.Value; var rowDelimiter = f1.Value; var skipEmptyCells = f2.Value == false; try { _excelapp.ScreenUpdating = false; System.Windows.Forms.Clipboard.Clear(); StringBuilder sb = new StringBuilder(); foreach (Excel.Range area in _range.Areas) { var range = _excelapp.Intersect(area, area.Worksheet.UsedRange); if (range == null) break; range.Copy(); sb.Append(System.Windows.Forms.Clipboard.GetText()); } _excelapp.CutCopyMode = 0; StringBuilder res = new StringBuilder(); int len = sb.Length - 2; for (int i = 0; i < len; i++) { if (sb[i] == '\t') { if (skipEmptyCells && (sb[i + 1] == '\t' || (sb[i + 1] == '\r' && sb[i + 2] == '\n'))) continue; else res.Append(cellDelimiter); } else if (sb[i] == '\r' && sb[i + 1] == '\n') { i++; if (skipEmptyCells && (sb[i + 2] == '\r' || sb[i + 2] == '\t')) continue; else res.Append(rowDelimiter); } else { res.Append(sb[i]); } } System.Windows.Forms.Clipboard.SetText(res.ToString()); } catch { throw; } finally { _excelapp.ScreenUpdating = true; } }); }
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 TrimCharacter() { var f1 = new FieldText("Characters", "Leading or trailing characters to remove", ""); var f2 = new FieldCheckBox("Remove leading characters", "", true); var f3 = new FieldCheckBox("Remove trailing characters", "", true); new InputBox(_excelapp, "Trim characters", f1, f2, f3).Show(delegate { char[] chars = f1.Value.ToCharArray(); bool trimLeft = f2.Value; bool trimRigth = f3.Value; new CellsOperator(_range).ForEachValue(null, delegate(ref object value) { if (value is string) { if (trimLeft && trimRigth) value = ((string)value).Trim(chars); else if (trimLeft) value = ((string)value).TrimStart(chars); else if (trimRigth) value = ((string)value).TrimEnd(chars); } }); }); }
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 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 Time() { var f1 = new FieldTime("Min time", "Minimum time", new System.DateTime(1899, 12, 30), true); var f2 = new FieldTime("Max time", "Maximum time", new System.DateTime(1899, 12, 30).AddSeconds(86399), true); var f3 = new FieldCheckBox("Generate unique values", "", false, true); new InputBox(_excelapp, "Random time generation", f1, f2, f3, _fnb).Show(delegate { System.DateTime minTime = f1.Value, maxTime = f2.Value; var maxSeconds = (int)(maxTime - minTime).TotalSeconds + 1; var random = new Random(); string format = "[$-F400]h:mm:ss AM/PM"; var gen = new Generator(f3.Value, () => minTime.AddSeconds(random.Next(maxSeconds))); 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 String() { var f1 = new FieldText("Pattern", @"\d=0-9 \w=A-Za-z0-9_ \H=0-9A-F", @"[A-F]-\d{3}-\d{3,10}", true); var f2 = new FieldCheckBox("Generate unique values", "", false, true); new InputBox(_excelapp, "Random string generation", f1, f2, _fnb).Show(delegate { if (f1.Value.Length < 1) throw new InputError("Pattern field is empty"); var gen = new RandomString(f1.Value, f2.Value); string format = "@"; 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 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(); }); }); }
public void DateTime() { var f1 = new FieldDate("Min date", "Minimum date", System.DateTime.Now.Date, true); var f2 = new FieldDate("Max date", "Maximum date", System.DateTime.Now.AddMonths(1).Date, true); var f3 = new FieldTime("Min time", "Minimum time", new System.DateTime(1899, 12, 30), true); var f4 = new FieldTime("Max time", "Maximum time", new System.DateTime(1899, 12, 30).AddSeconds(86399), true); var f5 = new FieldCheckBox("Generate unique values", "", false, true); new InputBox(_excelapp, "Random date-time generation", f1, f2, f3, f4, f5, _fnb).Show(delegate { System.DateTime minDate = f1.Value, maxDate = f2.Value; System.DateTime minTime = f3.Value, maxTime = f4.Value; var maxDays = (int)(maxDate - minDate).TotalDays + 1; var maxSeconds = (int)(maxTime - minTime).TotalSeconds + 1; var random = new Random(); string format = _culture.DateTimeFormat.ShortDatePattern + " HH:mm"; var gen = new Generator(f5.Value, () => minDate.AddDays(random.Next(maxDays)).AddSeconds(random.Next(maxSeconds))); 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 Date() { var f1 = new FieldDate("Min date", "Minimum date", System.DateTime.Now.Date, true); var f2 = new FieldDate("Max date", "Maximum date", System.DateTime.Now.AddYears(1).Date, true); var f3 = new FieldCheckBox("Generate unique values", "", false, true); new InputBox(_excelapp, "Random time generation", f1, f2, f3, _fnb).Show(delegate { var minDate = f1.Value; var maxDate = f2.Value; int maxDays = (int)(maxDate - minDate).TotalDays + 1; var unique = f3.Value; string format = "m/d/yyyy"; var random = new Random(); var gen = new Generator(f3.Value, () => minDate.AddDays(random.Next(maxDays))); 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(); }); }); }
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); }); }); }