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