예제 #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);
        }
예제 #2
0
        private Excel.Style CreateYearTotalStyle(Excel.Workbook workBook, string styleName)
        {
            /*
             * borders in styles doesnt realy working, very simple using is possible with the index trick. thats all
             */

            Excel.Style newStyle = workBook.Styles.Add(styleName);
            newStyle.Font.Size = 12;
            newStyle.Borders[(XlBordersIndex)Constants.xlTop].LineStyle = XlLineStyle.xlContinuous;
            newStyle.Borders[(XlBordersIndex)Constants.xlTop].Color     = 0;
            newStyle.Borders[(XlBordersIndex)Constants.xlTop].Weight    = 2;

            newStyle.Borders[(XlBordersIndex)Constants.xlBottom].LineStyle = LateBindingApi.Excel.Enums.XlLineStyle.xlContinuous;
            newStyle.Borders[(XlBordersIndex)Constants.xlBottom].Color     = 0;
            newStyle.Borders[(XlBordersIndex)Constants.xlBottom].Weight    = 2;

            newStyle.Borders[(XlBordersIndex)Constants.xlLeft].LineStyle = LateBindingApi.Excel.Enums.XlLineStyle.xlDouble;
            newStyle.Borders[(XlBordersIndex)Constants.xlLeft].Color     = 0;
            newStyle.Borders[(XlBordersIndex)Constants.xlLeft].Weight    = 2;

            newStyle.Borders[(XlBordersIndex)Constants.xlRight].LineStyle = LateBindingApi.Excel.Enums.XlLineStyle.xlLineStyleNone;
            newStyle.Borders[(XlBordersIndex)Constants.xlRight].Color     = 0;
            newStyle.Borders[(XlBordersIndex)Constants.xlRight].Weight    = 2;

            newStyle.NumberFormat = "#,##0.00 €";

            return(newStyle);
        }
예제 #3
0
        private Excel.Style CreateSummaryStyle(Excel.Workbook workBook, string styleName)
        {
            /*
             * borders in styles doesnt realy working, very simple using is possible with the index trick. thats all
             */
            Excel.Style newStyle = workBook.Styles.Add(styleName);
            newStyle.Font.Size = 12;
            newStyle.Font.Name = "Courier New";
            newStyle.Borders[(XlBordersIndex)Constants.xlTop].LineStyle = XlLineStyle.xlContinuous;
            newStyle.Borders[(XlBordersIndex)Constants.xlTop].Color     = 0;
            newStyle.Borders[(XlBordersIndex)Constants.xlTop].Weight    = 2;

            newStyle.Borders[(XlBordersIndex)Constants.xlBottom].LineStyle = XlLineStyle.xlContinuous;
            newStyle.Borders[(XlBordersIndex)Constants.xlBottom].Color     = 0;
            newStyle.Borders[(XlBordersIndex)Constants.xlBottom].Weight    = 2;

            newStyle.Borders[(XlBordersIndex)Constants.xlLeft].LineStyle = XlLineStyle.xlDouble;
            newStyle.Borders[(XlBordersIndex)Constants.xlLeft].Color     = 0;
            newStyle.Borders[(XlBordersIndex)Constants.xlLeft].Weight    = 2;

            newStyle.Borders[(XlBordersIndex)Constants.xlRight].LineStyle = XlLineStyle.xlLineStyleNone;
            newStyle.Borders[(XlBordersIndex)Constants.xlRight].Color     = 0;
            newStyle.Borders[(XlBordersIndex)Constants.xlRight].Weight    = 2;

            return(newStyle);
        }
예제 #4
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();
        }
예제 #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();
            }
        }
예제 #6
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);
        }
예제 #7
0
        private void ProceedSummaryWorksheet(SalesReport report, Excel.Workbook workBook, Excel.Worksheet summarySheet, Excel.Worksheet afterSheet)
        {
            summarySheet.Name = "Summary";

            Excel.Style matrixStyle = CreateSummaryStyle(workBook, "MatrixStyle");
            ProceedSummaryMatrix(report, summarySheet, matrixStyle);
            ProceedSummaryWorksheetCharts(summarySheet, report.Products.Length + 1);
            ProceedSummaryPrintSettings(summarySheet);
            summarySheet.Columns.AutoFit();// proceed AutoFit before header
            ProceedSummaryWorksheetHeader(summarySheet);

            summarySheet.Select();
        }
예제 #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);
        }
예제 #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);
        }
예제 #10
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;
        }
예제 #11
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);
        }
예제 #12
0
 void excelApplication_WorkbookDeactivate(Excel.Workbook Wb)
 {
     textBoxEvents.BeginInvoke(_updateDelegate, new object[] { "Event WorkbookDeactivate called." });
 }
예제 #13
0
 void excelApplication_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
 {
     textBoxEvents.BeginInvoke(_updateDelegate, new object[] { "Event WorkbookBeforeClose called." });
 }
예제 #14
0
 void excelApplication_NewWorkbook(Excel.Workbook Wb)
 {
     textBoxEvents.BeginInvoke(_updateDelegate, new object[] { "Event NewWorkbook called." });
 }
예제 #15
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();
        }
예제 #16
0
        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application application = new LateBindingApi.Excel.Application();
            application.DisplayAlerts = false;

            /*
             *  LateBindingApi.Excel manages COM Proxies for you to avoid any kind of memory leaks
             *  and make sure your excel instance removes from process list if you want.
             */

            Excel.Workbook book = application.Workbooks.Add();

            /*
             * now we have 2 new COM Proxies created.
             *
             * the first proxy was created while accessing the Workbooks collection from application
             * the second proxy was created by the Add() method from Workbooks and stored now in book
             */


            Excel.Worksheet sheet = book.Worksheets.Add();

            /*
             * we create also 2 new COM Proxies.
             *
             * the first proxy was created while accessing the Worksheets collection from book
             * the second proxy was created by the Add() method from Worksheets and stored now in sheet
             *
             * the 2 proxies was created about the book object and are managed now by the book object
             * they are now 'child proxies' collected in a internal list.
             */


            book.Dispose();
            /* now we have disposed the Workbook, the inner Proxy will be released */
            /* all child proxies there was created about the book are also released now */
            /* this means the 2 proxies there we have created while add a new worksheet */
            /* the local stored variable Excel.Worksheet sheet are not longer valid */


            /*
             * SUMMARY:
             * With LateBindingApi.Excel you have the choice to use Dispose() for any object or doesnt.
             * In case of doesn't dont forget to dispose the application and all child proxies will be released automaticly.
             * It is necessary to understand the demonstrated child proxy concept of LateBindingApi.Excel to avoid any suprises.
             * With these concept you have a powerfull third way. Use child proxies without Dispose() to get more readable & smaller code.
             * Dispose the parent object of your childs and thats all.
             */


            /* another example for efficient using child proxy management */
            book = application.Workbooks.Add();
            foreach (Excel.Worksheet item in book.Worksheets)
            {
                for (int i = 1; i <= 100; i++)
                {
                    item.Cells[1, i].Value = "Hello World";
                }
            }
            book.Dispose();

            /*
             * we create a new workbook with default 3 new worksheets
             * we do a foreach to all worksheets and write 100 times "Hello World" in cells
             * we have created now 300 range proxies 3 worksheet proxies 1 worksheet enumerator proxy and the book proxy
             * the parent object is ouer book, diposed it and the child proxies will be also disposed
             * Note: its not allowed in the MS Interop Assemblies to use an enumerator like these.
             * you must store it in a local variable first and dispose explicitly after using.
             * With LateBindingApi.Excel you can do this without any problems.
             */


            application.Quit();
            application.Dispose();
            /*the application object is ouer root object*/
            /*dispose them will release himself and any childs*/
            /*the excel instance are now removed from process list*/

            MessageBox.Show(this, "Done!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
예제 #17
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);
        }