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