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