Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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();
        }
Ejemplo n.º 3
0
 /// <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";
 }
Ejemplo n.º 4
0
        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);
        }
Ejemplo n.º 5
0
        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();
            }
        }
Ejemplo n.º 6
0
 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);
     }
 }
Ejemplo n.º 7
0
        /// <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");
            }
        }
Ejemplo n.º 8
0
        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);
        }
Ejemplo n.º 9
0
        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);
        }
Ejemplo n.º 10
0
        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();
        }
Ejemplo n.º 11
0
        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;
        }
Ejemplo n.º 12
0
        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);
        }
Ejemplo n.º 13
0
        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();
        }
Ejemplo n.º 14
0
        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();
        }
Ejemplo n.º 15
0
        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);
        }