public ExcelCheckBox(ExcelInterop.Range range, ExcelCheckBoxDefinition definition)
{
OwnerRange = range;
OwnerRange.Value2 = null;
ExcelInterop.Worksheet worksheet = null;
ExcelInterop.OLEObjects oleObjects = null;
ExcelInterop.OLEObject oleObject = null;
try
{
worksheet = OwnerRange.Worksheet;
Name = $"ExcelCB{Interlocked.Increment(ref cpt)}";
oleObjects = worksheet.OLEObjects();
oleObject = oleObjects.Add("Forms.CheckBox.1",
Type.Missing,
true,
false,
Type.Missing,
Type.Missing,
Type.Missing,
OwnerRange.Left + 3,
OwnerRange.Top + 1,
12,
12);
oleObject.Name = Name;
oleObject.Placement = ExcelInterop.XlPlacement.xlMove;
CheckBox = worksheet.GetType().InvokeMember(Name, BindingFlags.Default | BindingFlags.GetProperty, null, worksheet, null) as ExcelForms.CheckBox;
CheckBox.SpecialEffect = ExcelForms.fmButtonEffect.fmButtonEffectSunken;
CheckBox.TripleState = false;
CheckBox.Caption = string.Empty;
CheckBox.BackColor = (int)OwnerRange.Interior.Color;
CheckBox.BackStyle = ExcelForms.fmBackStyle.fmBackStyleTransparent;
oleObject.Interior.ColorIndex = -4142;
CheckBox.AutoSize = false;
oleObject = null;
}
finally
{
if (oleObject != null)
{
ExcelApplication.ReleaseComObject(oleObject);
}
if (oleObjects != null)
{
ExcelApplication.ReleaseComObject(oleObjects);
}
if (worksheet != null)
{
ExcelApplication.ReleaseComObject(worksheet);
}
oleObject = null;
oleObjects = null;
worksheet = null;
}
}
public ExcelButton(ExcelInterop.Range range, ExcelButtonDefinition definition)
{
OwnerRange = range;
OwnerRange.Value2 = null;
ExcelInterop.Worksheet worksheet = null;
ExcelInterop.OLEObjects oleObjects = null;
ExcelInterop.OLEObject obj = null;
try
{
worksheet = OwnerRange.Worksheet;
Name = $"ExcelBtn{Interlocked.Increment(ref cpt)}";
oleObjects = worksheet.OLEObjects();
obj = oleObjects.Add("Forms.CommandButton.1",
Type.Missing,
false,
false,
Type.Missing,
Type.Missing,
Type.Missing,
OwnerRange.Left + definition.X,
OwnerRange.Top + definition.Y,
definition.W == 0 ? OwnerRange.Width : definition.W,
definition.H == 0 ? OwnerRange.Height : definition.H);
obj.Name = Name;
object s = worksheet.GetType().InvokeMember(Name, BindingFlags.Default | BindingFlags.GetProperty, null, worksheet, null);
commandButton = s as ExcelForms.CommandButton;
commandButton.FontName = "Arial";
commandButton.Font.Size = 8;
commandButton.Caption = definition.Label;
//if (excelTemplateDefinition.W == 0 && excelTemplateDefinition.H == 0)
// commandButton.AutoSize = true;
obj.Placement = ExcelInterop.XlPlacement.xlMove;
}
finally
{
if (obj != null)
{
ExcelApplication.ReleaseComObject(obj);
}
if (oleObjects != null)
{
ExcelApplication.ReleaseComObject(oleObjects);
}
if (worksheet != null)
{
ExcelApplication.ReleaseComObject(worksheet);
}
obj = null;
oleObjects = null;
worksheet = null;
}
}
public string RunMacro(string FilePath, string MacroName)
{
string result = "";
try
{
if (MacroName == String.Empty)
{
return("Exception caught - Macro Name shouldn't be empty");
}
result = OpenExcel(FilePath);
if (result == "")
{
Ws.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, excelApp, new Object[] { MacroName });
}
}
catch (Exception e)
{
result = "Exception caught - " + e.Message;
}
return(result);
}
public void SetRangeValue(string range, object[,] value)
{
this.range = (ExcelApp.Range)_currentSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
null, _currentSheet, new object[] { range });
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, range, new object[] { value });
}
public ExcelSortAndFilterButton(ExcelTemplateView templateView)
{
this.View = templateView;
ExcelInterop.Worksheet worksheet = null;
ExcelInterop.Shapes shapes = null;
ExcelInterop.Shape shape = null;
try
{
worksheet = View.ViewSheet;
OwnerRange = View.FirstOutputCell;
Name = $"ExcelBtn{Interlocked.Increment(ref cpt)}";
shapes = worksheet.Shapes;
shape = (ExcelInterop.Shape)shapes.AddOLEObject("Forms.CommandButton.1",
Type.Missing,
false,
false,
Type.Missing,
Type.Missing,
Type.Missing,
OwnerRange.Left,
OwnerRange.Top,
20,
20);
shape.Name = Name;
object s = worksheet.GetType().InvokeMember(Name, BindingFlags.GetProperty, null, worksheet, null);
commandButton = s as ExcelForms.CommandButton;
commandButton.FontName = "Arial";
commandButton.Font.Size = 8;
commandButton.Caption = "S/F";
commandButton.ForeColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
commandButton.Click += () =>
{
using (ExcelMainWindow excelWindow = new ExcelMainWindow(ETKExcel.ExcelApplication.Application.Hwnd))
{
//@@SortAndFilterManagement.DisplaySortAndFilterWindow(excelWindow, View);
}
};
}
finally
{
if (shape != null)
{
ExcelApplication.ReleaseComObject(shape);
}
if (shapes != null)
{
ExcelApplication.ReleaseComObject(shapes);
}
if (worksheet != null)
{
ExcelApplication.ReleaseComObject(worksheet);
}
shape = null;
shapes = null;
worksheet = null;
}
}
