private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
_excelApplication = new Excel.Application();
_excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = _excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = workBook.Worksheets[1];
// we need some data to display
Excel.Range dataRange = PutSampleData(workSheet);
// create a nice diagram
Excel.ChartObject chart = workSheet.ChartObjects().Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
// save the book
string fileExtension = GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example05{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
_excelApplication.Quit();
_excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
private void button1_Click(object sender, EventArgs e)
{
// we enable the event support
LateBindingApi.Core.Settings.EnableEvents = true;
// start excel and turn off msg boxes
_excelApplication = new Excel.Application();
_excelApplication.DisplayAlerts = false;
_excelApplication.Visible = true;
/*
* we register some events. note: the event trigger was called from excel, means an other Thread
* remove the Quit() call below and check out more events if you want
* you can get event notifys from various objects: Application or Workbook or Worksheet for example
*/
_excelApplication.NewWorkbookEvent += new Excel.AppEvents_NewWorkbookEventHandler(excelApplication_NewWorkbook);
_excelApplication.WorkbookBeforeCloseEvent += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(excelApplication_WorkbookBeforeClose);
_excelApplication.WorkbookActivateEvent += new Excel.AppEvents_WorkbookActivateEventHandler(excelApplication_WorkbookActivate);
_excelApplication.WorkbookDeactivateEvent += new Excel.AppEvents_WorkbookDeactivateEventHandler(excelApplication_WorkbookDeactivate);
_excelApplication.SheetActivateEvent += new Excel.AppEvents_SheetActivateEventHandler(excelApplication_SheetActivate);
_excelApplication.SheetDeactivateEvent += new Excel.AppEvents_SheetDeactivateEventHandler(excelApplication_SheetDeactivate);
// add a new workbook add a sheet and close
Excel.Workbook workBook = _excelApplication.Workbooks.Add();
workBook.Worksheets.Add();
workBook.Close();
_excelApplication.Quit();
_excelApplication.Dispose();
}
/// <summary>
/// returns the valid file extension for the instance. for example ".xls" or ".xlsx"
/// </summary>
/// <param name="application">the instance</param>
/// <returns>the extension</returns>
private static string GetDefaultExtension(Excel.Application application)
{
double Version = Convert.ToDouble(application.Version);
if (Version >= 120.00)
return ".xlsx";
else
return ".xls";
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
_excelApplication = new Excel.Application();
_excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = _excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = workBook.Worksheets[1];
/*do background color for cells*/
string listSeperator = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator;
// draw the face
string rangeAdressFace = string.Format("$C10:$M10{0}$C30:$M30{0}$C11:$C30{0}$M11:$M30", listSeperator);
workSheet.get_Range(rangeAdressFace).Interior.Color = ToDouble(Color.DarkGreen);
string rangeAdressEyes = string.Format("$F14{0}$J14", listSeperator);
workSheet.get_Range(rangeAdressEyes).Interior.Color = ToDouble(Color.Black);
string rangeAdressNoise = string.Format("$G18:$I19", listSeperator);
workSheet.get_Range(rangeAdressNoise).Interior.Color = ToDouble(Color.DarkGreen);
string rangeAdressMouth = string.Format("$F26{0}$J26{0}$G27:$I27", listSeperator);
workSheet.get_Range(rangeAdressMouth).Interior.Color = ToDouble(Color.DarkGreen);
// border the face with the border arround method
workSheet.get_Range(rangeAdressFace).BorderAround(XlLineStyle.xlDashDot, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexNone, Color.BlueViolet.ToArgb());
workSheet.get_Range(rangeAdressEyes).BorderAround(XlLineStyle.xlDashDot, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexNone, Color.BlueViolet.ToArgb());
workSheet.get_Range(rangeAdressNoise).BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexNone, Color.BlueViolet.ToArgb());
// border explicitly
workSheet.get_Range(rangeAdressMouth).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
workSheet.get_Range(rangeAdressMouth).Borders[XlBordersIndex.xlEdgeBottom].Weight = 4;
workSheet.get_Range(rangeAdressMouth).Borders[XlBordersIndex.xlEdgeBottom].Color = ToDouble(Color.Black);
// save the book
string fileExtension = GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example01{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
_excelApplication.Quit();
_excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
private void button1_Click(object sender, EventArgs e)
{
try
{
// start excel and turn off msg boxes
_excelApplication = new Excel.Application();
_excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = _excelApplication.Workbooks.Add();
// add new global Code Module
VBIDE.VBComponent globalModule = workBook.VBProject.VBComponents.Add(Vbext_ComponentType.vbext_ct_StdModule);
globalModule.Name = "MyNewCodeModule";
// add a new procedure to the modul
globalModule.CodeModule.InsertLines(1, "Public Sub HelloWorld(Param as string)\r\n MsgBox \"Hello World!\" & vbnewline & Param\r\nEnd Sub");
// create a click event trigger for the first worksheet
int linePosition = workBook.VBProject.VBComponents[2].CodeModule.CreateEventProc("BeforeDoubleClick", "Worksheet");
workBook.VBProject.VBComponents[2].CodeModule.InsertLines(linePosition + 1, "HelloWorld \"BeforeDoubleClick\"");
// display info in the worksheet
workBook.Worksheets[1].Cells[2, 2].Value = "This workbook contains dynamic created VBA Moduls and Event Code";
workBook.Worksheets[1].Cells[5, 2].Value = "Open the VBA Editor to see the code";
workBook.Worksheets[1].Cells[8, 2].Value = "Do a double click to catch the BeforeDoubleClick Event from this Worksheet.";
// save the book
string fileExtension = GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example07{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
catch (System.Reflection.TargetInvocationException throwedException)
{
string message = string.Format("An error is occured.{0}ExceptionTrace:{0}", Environment.NewLine);
Exception exception = throwedException;
while (null != exception)
{
message += string.Format("{0}{1}", exception.Message, Environment.NewLine);
exception = exception.InnerException;
}
MessageBox.Show(message);
}
finally
{
// close excel and dispose reference
_excelApplication.Quit();
_excelApplication.Dispose();
}
}
void IDTExtensibility2.OnConnection(object Application, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
{
try
{
_excelApplication = new Excel.Application(null, Application);
}
catch (Exception throwedException)
{
string details = string.Format("{1}{1}Details:{1}{1}{0}", throwedException.Message, Environment.NewLine);
MessageBox.Show("An error occured." + details, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// returns the valid file extension for the instance. for example ".xls" or ".xlsx"
/// </summary>
/// <param name="application">the instance</param>
/// <returns>the extension</returns>
public static string GetDefaultExtension(Excel.Application application)
{
double Version = Convert.ToDouble(application.Version);
if (Version >= 120.00)
{
return(".xlsx");
}
else
{
return(".xls");
}
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
_excelApplication = new Excel.Application();
_excelApplication.DisplayAlerts = false;
_excelApplication.ScreenUpdating = false;
// add a new workbook
Excel.Workbook workBook = _excelApplication.Workbooks.Add();
// we use the first sheet as summary sheet and remove the 2 last sheets
Excel.Worksheet summarySheet = workBook.Worksheets[1];
workBook.Worksheets[3].Delete();
workBook.Worksheets[2].Delete();
// we get the data & perform the report
_report = new SalesReport(_yearToReport, _monthToReport);
_report.Proceed();
// we create named styles for the range.Style property
CreateStorageAndRankingStyle(workBook, "StorageAndRanking");
CreateMonthStyle(workBook, "MonthInfos");
CreateMonthStyle(workBook, "YearTotalInfos");
// write product sheets
Excel.Worksheet productSheet = null;
foreach (SalesReportProduct itemProduct in _report.Products)
{
productSheet = workBook.Worksheets.Add();
ProceedProductWorksheet(productSheet, itemProduct);
productSheet.Move(Missing.Value, workBook.Worksheets[workBook.Worksheets.Count]);
}
// write summary sheet
ProceedSummaryWorksheet(_report, workBook, summarySheet, productSheet);
summarySheet.get_Range("$A2").Select();
// save the book
string fileExtension = Helper.GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example10{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
_excelApplication.Quit();
_excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
_excelApplication = new Excel.Application();
_excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = _excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = workBook.Worksheets[1];
workSheet.Cells[1, 1].Value = "these sample shapes was dynamicly created by code.";
// create a star
Excel.Shape starShape = workSheet.Shapes.AddShape(MsoAutoShapeType.msoShape32pointStar, 10, 50, 200, 20);
// create a simple textbox
Excel.Shape textBox = workSheet.Shapes.AddTextbox(MsoTextOrientation.msoTextOrientationHorizontal, 10, 150, 200, 50);
textBox.TextFrame.Characters().Text = "text";
textBox.TextFrame.Characters().Font.Size = 14;
// create a wordart
Excel.Shape textEffect = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect14, "WordArt", "Arial", 12,
MsoTriState.msoTrue, MsoTriState.msoFalse, 10, 250);
// create text effect
Excel.Shape textDiagram = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect11, "Effect", "Arial", 14,
MsoTriState.msoFalse, MsoTriState.msoFalse, 10, 350);
// save the book
string fileExtension = GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example04{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
_excelApplication.Quit();
_excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application application = new Excel.Application();
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Range sampleRange = application.Workbooks[1].Worksheets[1].Cells[1, 1];
// we set the COMVariant ColorIndex from Font of ouer sample range with the invoker class
// sometimes its easier than using COMVariant.ToCOMVariant Method or no overload for the underlying target type exists
Invoker.PropertySet(sampleRange.Font, "ColorIndex", 1);
// creates a native unmanaged ComProxy
object comProxy = Invoker.MethodReturn(application, "Workbooks");
Marshal.ReleaseComObject(comProxy);
application.Quit();
application.Dispose();
}
private void button1_Click(object sender, EventArgs e)
{
Office.CommandBar commandBar;
Office.CommandBarPopup commandBarPop;
Office.CommandBarButton commandBarBtn;
// first we enable the event support
LateBindingApi.Core.Settings.EnableEvents = true;
// start excel and turn off msg boxes
_excelApplication = new Excel.Application();
_excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = _excelApplication.Workbooks.Add();
// add a commandbar popup
commandBarPop = (Office.CommandBarPopup)_excelApplication.CommandBars["Worksheet Menu Bar"].Controls.Add(
MsoControlType.msoControlPopup, Missing.Value, Missing.Value, Missing.Value, true);
commandBarPop.Caption = "commandBarPopup";
#region a lot of words, how to access the picture
/*
* you can see we use an own icon via .PasteFace()
* is not possible from outside process boundaries to use the PictureProperty directly
* the reason for is IPictureDisp: http://support.microsoft.com/kb/286460/de
* its not important is early or late binding or managed or unmanaged, the behaviour is always the same
* For example, a COMAddin running as InProcServer and can access the Picture Property
* Use the IconConverter.cs class from this project to convert a image to IPictureDisp
*/
#endregion
#region CommandBarButton
// add a button to the popup
commandBarBtn = (Office.CommandBarButton)commandBarPop.Controls.Add(MsoControlType.msoControlButton);
commandBarBtn.Style = MsoButtonStyle.msoButtonIconAndCaption;
commandBarBtn.Caption = "commandBarButton";
Clipboard.SetDataObject(this.Icon.ToBitmap());
commandBarBtn.PasteFace();
commandBarBtn.ClickEvent += new Office._CommandBarButtonEvents_ClickEventHandler(commandBarBtn_Click);
#endregion
#region Create a new toolbar
// add a new toolbar
commandBar = _excelApplication.CommandBars.Add("MyCommandBar", MsoBarPosition.msoBarTop, false, true);
commandBar.Visible = true;
// add a button to the toolbar
commandBarBtn = (Office.CommandBarButton)commandBar.Controls.Add(MsoControlType.msoControlButton);
commandBarBtn.Style = MsoButtonStyle.msoButtonIconAndCaption;
commandBarBtn.Caption = "commandBarButton";
commandBarBtn.FaceId = 3;
commandBarBtn.ClickEvent += new Office._CommandBarButtonEvents_ClickEventHandler(commandBarBtn_Click);
// add a dropdown box to the toolbar
commandBarPop = (Office.CommandBarPopup)commandBar.Controls.Add(MsoControlType.msoControlPopup);
commandBarPop.Caption = "commandBarPopup";
// add a button to the popup, we use an own icon for the button
commandBarBtn = (Office.CommandBarButton)commandBarPop.Controls.Add(MsoControlType.msoControlButton);
commandBarBtn.Style = MsoButtonStyle.msoButtonIconAndCaption;
commandBarBtn.Caption = "commandBarButton";
Clipboard.SetDataObject(this.Icon.ToBitmap());
commandBarBtn.PasteFace();
commandBarBtn.ClickEvent += new Office._CommandBarButtonEvents_ClickEventHandler(commandBarBtn_Click);
#endregion
#region Create a new ContextMenu
// add a commandbar popup
commandBarPop = (Office.CommandBarPopup)_excelApplication.CommandBars["Cell"].Controls.Add(
MsoControlType.msoControlPopup, Missing.Value, Missing.Value, Missing.Value, true);
commandBarPop.Caption = "commandBarPopup";
// add a button to the popup
commandBarBtn = (Office.CommandBarButton)commandBarPop.Controls.Add(MsoControlType.msoControlButton);
commandBarBtn.Style = MsoButtonStyle.msoButtonIconAndCaption;
commandBarBtn.Caption = "commandBarButton";
commandBarBtn.FaceId = 9;
commandBarBtn.ClickEvent += new Office._CommandBarButtonEvents_ClickEventHandler(commandBarBtn_Click);
#endregion
#region Display info
workBook.Worksheets[1].Cells[2, 2].Value = "this excel instance contains 3 custom menus";
workBook.Worksheets[1].Cells[3, 2].Value = "the main menu, the toolbar menu and the cell context menu";
workBook.Worksheets[1].Cells[4, 2].Value = "in this case the menus are temporaily created";
workBook.Worksheets[1].Cells[5, 2].Value = "they are not persistant and needs no unload event or something like this";
workBook.Worksheets[1].Cells[6, 2].Value = "you can also create persistant menus if you want";
#endregion
// make visible & set buttons
_excelApplication.Visible = true;
button1.Enabled = false;
button2.Enabled = true;
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn Application msg boxes
_excelApplication = new Excel.Application();
_excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = _excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = workBook.Worksheets[1];
/* some kind of numerics */
// the given thread culture in all latebinding calls are stored in Core.Settings.
// you can change the culture. default is en-us.
CultureInfo cultureInfo = LateBindingApi.Core.Settings.ThreadCulture;
string Pattern1 = string.Format("0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator);
string Pattern2 = string.Format("#{1}##0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator, cultureInfo.NumberFormat.CurrencyGroupSeparator);
workSheet.get_Range("A1").Value = "Type";
workSheet.get_Range("B1").Value = "Value";
workSheet.get_Range("C1").Value = "Formatted " + Pattern1;
workSheet.get_Range("D1").Value = "Formatted " + Pattern2;
int integerValue = 532234;
workSheet.get_Range("A3").Value = "Integer";
workSheet.get_Range("B3").Value = integerValue;
workSheet.get_Range("C3").Value = integerValue;
workSheet.get_Range("C3").NumberFormat = Pattern1;
workSheet.get_Range("D3").Value = integerValue;
workSheet.get_Range("D3").NumberFormat = Pattern2;
double doubleValue = 23172.64;
workSheet.get_Range("A4").Value = "double";
workSheet.get_Range("B4").Value = doubleValue;
workSheet.get_Range("C4").Value = doubleValue;
workSheet.get_Range("C4").NumberFormat = Pattern1;
workSheet.get_Range("D4").Value = doubleValue;
workSheet.get_Range("D4").NumberFormat = Pattern2;
float floatValue = 84345.9132f;
workSheet.get_Range("A5").Value = "float";
workSheet.get_Range("B5").Value = floatValue;
workSheet.get_Range("C5").Value = floatValue;
workSheet.get_Range("C5").NumberFormat = Pattern1;
workSheet.get_Range("D5").Value = floatValue;
workSheet.get_Range("D5").NumberFormat = Pattern2;
Decimal decimalValue = 7251231.313367m;
workSheet.get_Range("A6").Value = "Decimal";
workSheet.get_Range("B6").Value = decimalValue;
workSheet.get_Range("C6").Value = decimalValue;
workSheet.get_Range("C6").NumberFormat = Pattern1;
workSheet.get_Range("D6").Value = decimalValue;
workSheet.get_Range("D6").NumberFormat = Pattern2;
workSheet.get_Range("A9").Value = "DateTime";
workSheet.get_Range("B10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.FullDateTimePattern;
workSheet.get_Range("C10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.LongDatePattern;
workSheet.get_Range("D10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.ShortDatePattern;
workSheet.get_Range("E10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.LongTimePattern;
workSheet.get_Range("F10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.ShortTimePattern;
// DateTime
DateTime dateTimeValue = DateTime.Now;
workSheet.get_Range("B11").Value = dateTimeValue;
workSheet.get_Range("B11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.FullDateTimePattern;
workSheet.get_Range("C11").Value = dateTimeValue;
workSheet.get_Range("C11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.LongDatePattern;
workSheet.get_Range("D11").Value = dateTimeValue;
workSheet.get_Range("D11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.ShortDatePattern;
workSheet.get_Range("E11").Value = dateTimeValue;
workSheet.get_Range("E11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.LongTimePattern;
workSheet.get_Range("F11").Value = dateTimeValue;
workSheet.get_Range("F11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.ShortTimePattern;
// string
workSheet.get_Range("A14").Value = "String";
workSheet.get_Range("B14").Value = "This is a sample String";
workSheet.get_Range("B14").NumberFormat = "@";
// number as string
workSheet.get_Range("B15").Value = "513";
workSheet.get_Range("B15").NumberFormat = "@";
// set colums
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
workSheet.Columns[3].AutoFit();
workSheet.Columns[4].AutoFit();
// save the book
string fileExtension = GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example03{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
_excelApplication.Quit();
_excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
_excelApplication = new Excel.Application();
_excelApplication.DisplayAlerts = false;
// dont show dialogs with an invisible excel
_excelApplication.Visible = true;
// add a new workbook
Excel.Workbook workBook = _excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = workBook.Worksheets[1];
// show selected window and display user clicks ok or cancel
bool returnValue = false;
RadioButton radioSelectButton = GetSelectedRadioButton();
switch (radioSelectButton.Text)
{
case "xlDialogAddinManager":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogAddinManager].Show();
break;
case "xlDialogFont":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogFont].Show();
break;
case "xlDialogEditColor":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogEditColor].Show();
break;
case "xlDialogGallery3dBar":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogGallery3dBar].Show();
break;
case "xlDialogSearch":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogSearch].Show();
break;
case "xlDialogPrinterSetup":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogPrinterSetup].Show();
break;
case "xlDialogFormatNumber":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogFormatNumber].Show();
break;
case "xlDialogApplyStyle":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogApplyStyle].Show();
break;
default:
throw (new Exception("Unkown dialog selected."));
}
string message = string.Format("The dialog returns {0}.", returnValue);
MessageBox.Show(this, message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
// close excel and dispose reference
_excelApplication.Quit();
_excelApplication.Dispose();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application application = new Excel.Application();
application.DisplayAlerts = false;
application.Workbooks.Add();
/*
* COMVariant is a LateBindingApi defined Type as substitute for the COM Datatype Variant.
*
* To work with COMVariant use there following properties:
* object UnderlyingObject - the mapped original object
* bool IsCOMProxy - UnderlyingObject is a COM Proxy or native type
* string TypeName - the name of UnderlyingObject type
*
* You can cast the COMVariant directly in another LateBindingApi Type, see the following example
*/
/* Example: */
/* the property Selection from Excel.Application is defined as COMVariant */
/* It can have multiple types at runtime what is currently selected, a worksheet, a range, a window or other*/
// select worksheet for example
application.Workbooks[1].Worksheets[1].Select();
COMVariant myVariant = application.Selection;
if (null != myVariant)
{
switch (myVariant.TypeName)
{
case "Worksheet":
Excel.Worksheet sheet = (Excel.Worksheet)myVariant;
break;
case "Range":
Excel.Range range = (Excel.Range)myVariant;
break;
}
}
/* Another example: */
/* GetOpenFileName returns a COMVariant there can be a string or a boolean in case of user clicks cancel */
myVariant = application.GetOpenFilename("Text Files (*.txt), *.txt");
if (null != myVariant)
{
string name = myVariant.TypeName;
object underlyingObject = myVariant.UnderlyingObject;
string message = string.Format("GetOpenFilename returns a {0}\r\n{1}", name, underlyingObject);
MessageBox.Show(this, message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
/* Last example: */
/* a lot of scalar properties defined as variant in excel (the reason is still unkown)*/
/* use the multiple overload helper function ToCOMVariant */
/* Note: a other way to handle multiple types in a variable is the Invoker, see Tutorial03 */
Excel.Range sampleRange = application.Workbooks[1].Worksheets[1].Cells[1, 1];
int colorIndex = (int)sampleRange.Font.ColorIndex.UnderlyingObject;
sampleRange.Font.ColorIndex = COMVariant.ToCOMVariant(colorIndex + 1);
/*
* COMObject inherited COMVariant and is a LateBindingApi defined Type as substitute for a unkown Dispatch Interface aka System._ComProxy in .NET.
* All LateBindingApi WrapperClasses inherites COMObject
* You can cast COMObject directly in another LateBindingApi Type, see the following example
* To work with COMObject use the following properties:
* object UnderlyingObject - the mapped original System._ComProxy object
* string TypeName - the name of UnderlyingObject type
*/
foreach (Office.COMAddIn item in application.COMAddIns)
{
/* COMAddIn.Application is defined as COMObject*/
string name = item.Application.TypeName;
Excel.Application parentApplication = (Excel.Application)item.Application;
}
application.Quit();
application.Dispose();
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
_excelApplication = new Excel.Application();
_excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = _excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = workBook.Worksheets[1];
// font action
workSheet.get_Range("A1").Value = "Arial Size:8 Bold Italic Underline";
workSheet.get_Range("A1").Font.Name = "Arial";
workSheet.get_Range("A1").Font.Size = 8;
workSheet.get_Range("A1").Font.Bold = true;
workSheet.get_Range("A1").Font.Italic = true;
workSheet.get_Range("A1").Font.Underline = true;
workSheet.get_Range("A1").Font.Color = Color.Violet.ToArgb();
workSheet.get_Range("A3").Value = "Times New Roman Size:10";
workSheet.get_Range("A3").Font.Name = "Times New Roman";
workSheet.get_Range("A3").Font.Size = 10;
workSheet.get_Range("A3").Font.Color = Color.Orange.ToArgb();
workSheet.get_Range("A5").Value = "Comic Sans MS Size:12 WrapText";
workSheet.get_Range("A5").Font.Name = "Comic Sans MS";
workSheet.get_Range("A5").Font.Size = 12;
workSheet.get_Range("A5").WrapText = true;
workSheet.get_Range("A5").Font.Color = Color.Navy.ToArgb();
// HorizontalAlignment
workSheet.get_Range("A7").Value = "xlHAlignLeft";
workSheet.get_Range("A7").HorizontalAlignment = XlHAlign.xlHAlignLeft;
workSheet.get_Range("B7").Value = "xlHAlignCenter";
workSheet.get_Range("B7").HorizontalAlignment = XlHAlign.xlHAlignCenter;
workSheet.get_Range("C7").Value = "xlHAlignRight";
workSheet.get_Range("C7").HorizontalAlignment = XlHAlign.xlHAlignRight;
workSheet.get_Range("D7").Value = "xlHAlignJustify";
workSheet.get_Range("D7").HorizontalAlignment = XlHAlign.xlHAlignJustify;
workSheet.get_Range("E7").Value = "xlHAlignDistributed";
workSheet.get_Range("E7").HorizontalAlignment = XlHAlign.xlHAlignDistributed;
// VerticalAlignment
workSheet.get_Range("A9").Value = "xlVAlignTop";
workSheet.get_Range("A9").VerticalAlignment = XlVAlign.xlVAlignTop;
workSheet.get_Range("B9").Value = "xlVAlignCenter";
workSheet.get_Range("B9").VerticalAlignment = XlVAlign.xlVAlignCenter;
workSheet.get_Range("C9").Value = "xlVAlignBottom";
workSheet.get_Range("C9").VerticalAlignment = XlVAlign.xlVAlignBottom;
workSheet.get_Range("D9").Value = "xlVAlignDistributed";
workSheet.get_Range("D9").VerticalAlignment = XlVAlign.xlVAlignDistributed;
workSheet.get_Range("E9").Value = "xlVAlignJustify";
workSheet.get_Range("E9").VerticalAlignment = XlVAlign.xlVAlignJustify;
// setup rows and columns
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].ColumnWidth = 25;
workSheet.Columns[3].ColumnWidth = 25;
workSheet.Columns[4].ColumnWidth = 25;
workSheet.Columns[5].ColumnWidth = 25;
workSheet.Rows[9].RowHeight = 35;
// save the book
string fileExtension = GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example02{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
_excelApplication.Quit();
_excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
