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;
}
}
private void sheet1_setup()
{
//oSheet = oWB.ActiveSheet; //grab the first worksheet
oSheet = oWB.Sheets.Add();
oSheet.Name = "MaterialsID"; //excel sheet name
objs = oSheet.OLEObjects(); //grab objects (like checkmarks) on that page
////set up excel view window so that it looks cool while populating
//oXL.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview;
//oXL.ActiveWindow.Zoom = 80;
//margins n shit - leave these alone
oSheet.PageSetup.CenterHorizontally = true;
oSheet.PageSetup.CenterVertically = true;
oSheet.PageSetup.TopMargin = .25;
oSheet.PageSetup.BottomMargin = .25;
oSheet.PageSetup.LeftMargin = .25;
oSheet.PageSetup.RightMargin = .25;
//format every cell
oRng = oSheet.Range[oSheet.Cells[firstRow, 1], oSheet.Cells[lastRow, 12]];
oRng.RowHeight = 20;
oRng.ColumnWidth = 8;
oRng.Font.Size = 11;
oRng.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oRng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
for (int i = 6; i <= lastRow; i++)
{
oRng = oSheet.Range[oSheet.Cells[i, 1], oSheet.Cells[i, 8]];
oRng.Merge();
oRng = oSheet.Range[oSheet.Cells[i, 9], oSheet.Cells[i, 10]];
oRng.Merge();
oRng = oSheet.Range[oSheet.Cells[i, 11], oSheet.Cells[i, 12]];
oRng.Merge();
}
oRng = oSheet.Range[oSheet.Cells[6, 1], oSheet.Cells[lastRow, 12]];
oRng.RowHeight = 30;
oRng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;
oRng.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium;
oRng.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium;
//oRng = oSheet1.Range[oSheet1.Cells[4, 12], oSheet1.Cells[lastRow, 13]];
//oRng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
//oRng.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
//oRng.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
}
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;
}
}
private void sheet1_setup()
{
oSheet = oWB.Sheets.Add();
////grab the first worksheet
//oSheet = oWB.ActiveSheet;
objs = oSheet.OLEObjects();
//format page
oSheet.Name = "Traveler";
//oXL.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview;
//oXL.ActiveWindow.Zoom = 80;
oSheet.PageSetup.CenterHorizontally = true;
oSheet.PageSetup.CenterVertically = true;
oSheet.PageSetup.TopMargin = .25;
oSheet.PageSetup.BottomMargin = .25;
oSheet.PageSetup.LeftMargin = .25;
oSheet.PageSetup.RightMargin = .25;
oRng = oSheet.Range[oSheet.Cells[firstRow, 1], oSheet.Cells[lastRow, 13]];
oRng.ColumnWidth = 8;
oRng.RowHeight = 20;
oRng.Font.Size = 11;
oRng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
oRng.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oRng = oSheet.Cells[1, 9];
oRng.ColumnWidth = 5;
oRng = oSheet.Cells[1, 11];
oRng.ColumnWidth = 2;
oRng = oSheet.Cells[1, 12];
oRng.ColumnWidth = 5;
for (int row_number = 4; row_number <= 39; row_number++)
{
oRng = oSheet.Range[oSheet.Cells[row_number, 1], oSheet.Cells[row_number, 8]];
oRng.Merge();
oRng.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
oRng.Value = " ";
}
oRng = oSheet.Range[oSheet.Cells[4, 9], oSheet.Cells[lastRow, 9]];
oRng.Font.Size = 22;
oRng = oSheet.Range[oSheet.Cells[4, 12], oSheet.Cells[lastRow, 12]];
oRng.Font.Size = 22;
oRng = oSheet.Range[oSheet.Cells[1, 1], oSheet.Cells[3, 13]];
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;
oRng = oSheet.Range[oSheet.Cells[1, 1], oSheet.Cells[lastRow, 13]];
oRng.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;
oRng.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium;
oRng.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium;
oRng = oSheet.Range[oSheet.Cells[4, 1], oSheet.Cells[lastRow, 10]];
oRng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng = oSheet.Range[oSheet.Cells[4, 12], oSheet.Cells[lastRow, 13]];
oRng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
}