コード例 #1
0
        private void button1_Click(object sender, EventArgs e)
        {
            // we enable the event support
            XlLateBindingApiSettings.EventsEnabled = true;

            // start excel and turn off msg boxes
            excelApplication = new XlApplication();
            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: XlApplication or XlWorkbook or XlWorksheet for example
             */
            excelApplication.NewWorkbook         += new AppEvents_NewWorkbookEventHandler(excelApplication_NewWorkbook);
            excelApplication.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(excelApplication_WorkbookBeforeClose);
            excelApplication.WorkbookActivate    += new AppEvents_WorkbookActivateEventHandler(excelApplication_WorkbookActivate);
            excelApplication.WorkbookDeactivate  += new AppEvents_WorkbookDeactivateEventHandler(excelApplication_WorkbookDeactivate);
            excelApplication.SheetActivate       += new AppEvents_SheetActivateEventHandler(excelApplication_SheetActivate);
            excelApplication.SheetDeactivate     += new AppEvents_SheetDeactivateEventHandler(excelApplication_SheetDeactivate);

            // add a new workbook add a sheet and close
            XlWorkbook workBook = excelApplication.Workbooks.Add();

            workBook.Worksheets.Add();
            workBook.Close();

            excelApplication.Quit();
            excelApplication.Dispose();
        }
コード例 #2
0
        private void button1_Click(object sender, EventArgs e)
        {
            // start excel and turn off msg boxes
            excelApplication = new XlApplication();
            excelApplication.DisplayAlerts = false;

            // add a new workbook
            XlWorkbook  workBook  = excelApplication.Workbooks.Add();
            XlWorksheet workSheet = workBook.Worksheets[1];

            // we need some data to display
            XlRange dataRange = PutSampleData(workSheet);

            // create a nice diagram
            XlChartObject chart = workSheet.ChartObjects.Add(70, 100, 375, 225);

            chart.Chart.SetSourceData(dataRange);

            // save the book
            string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
            string workbookFile  = string.Format("{0}\\Example5{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);
        }
コード例 #3
0
 public Form1()
 {
     InitializeComponent();
     excelApplication = new XlApplication();
     excelApplication.Workbooks.Add();
     excelApplication.Visible = true;
     RefreshView();
 }
コード例 #4
0
        private void button1_Click(object sender, EventArgs e)
        {
            // start excel and turn off msg boxes
            excelApplication = new XlApplication();
            excelApplication.DisplayAlerts = false;

            // add a new workbook
            XlWorkbook  workBook  = excelApplication.Workbooks.Add();
            XlWorksheet 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.Range(rangeAdressFace).Interior.Color = XlConverter.ToDouble(Color.DarkGreen);

            string rangeAdressEyes = string.Format("$F14{0}$J14", listSeperator);

            workSheet.Range(rangeAdressEyes).Interior.Color = XlConverter.ToDouble(Color.Black);

            string rangeAdressNoise = string.Format("$G18:$I19", listSeperator);

            workSheet.Range(rangeAdressNoise).Interior.Color = XlConverter.ToDouble(Color.DarkGreen);

            string rangeAdressMouth = string.Format("$F26{0}$J26{0}$G27:$I27", listSeperator);

            workSheet.Range(rangeAdressMouth).Interior.Color = XlConverter.ToDouble(Color.DarkGreen);


            /*do borderlines for cells*/

            // border the face with the border arround method
            workSheet.Range(rangeAdressFace).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDashDot, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb());
            workSheet.Range(rangeAdressEyes).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDashDot, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb());
            workSheet.Range(rangeAdressNoise).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDouble, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb());

            // border explicitly
            workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].LineStyle = LateBindingApi.Excel.Enums.XlLineStyle.xlDouble;
            workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].Weight    = 4;
            workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].Color     = 400;

            // save the book
            string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
            string workbookFile  = string.Format("{0}\\Example1{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);
        }
コード例 #5
0
        static void Main(string[] args)
        {
            Console.WriteLine("XlLateBinding Performance Test - 5000 Cells.");

            /*
             * start excel and disable messageboxes and screen updating
             */
            XlApplication excelApplication = new XlApplication();

            excelApplication.DisplayAlerts  = false;
            excelApplication.ScreenUpdating = false;

            /*
             *  create new empty worksheet
             */
            excelApplication.Workbooks.Add();
            XlWorksheet sheet = excelApplication.Workbooks[1].Worksheets[1];

            /*
             *  do the test
             */
            DateTime timeStart = DateTime.Now;

            for (int i = 1; i <= 5000; i++)
            {
                // cells property for a sheet in OfficeFor.Net are not supported
                // the reason for all examples use range
                string  rangeAdress = "$A" + i.ToString();
                XlRange cellRange   = sheet.Range(rangeAdress);
                cellRange.Value        = "value";
                cellRange.Font.Name    = "Verdana";
                cellRange.NumberFormat = "@";

                cellRange.BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black);
            }
            DateTime timeEnd     = DateTime.Now;
            TimeSpan timeElapsed = timeEnd - timeStart;

            /*
             * display for user
             */
            string outputConsole = string.Format("Time Elapsed: {0}{1}Press any Key.", timeElapsed, Environment.NewLine);

            Console.WriteLine(outputConsole);
            Console.Read();

            /*
             * write result in logfile
             */
            string logFile       = Path.Combine(Environment.CurrentDirectory, "LateBinding.log");
            string logFileAppend = timeElapsed.ToString() + Environment.NewLine;

            File.AppendAllText(logFile, logFileAppend, Encoding.UTF8);

            excelApplication.Quit();
            excelApplication.Dispose();
        }
コード例 #6
0
        static void Main(string[] args)
        {
            Console.WriteLine("XlLateBinding Performance Test - 10.000 Cells.");

            /*
             * start excel and disable messageboxes and screen updating
             */
            XlApplication excelApplication = new XlApplication();

            excelApplication.DisplayAlerts  = false;
            excelApplication.ScreenUpdating = false;

            /*
             *  create new empty worksheet
             */
            excelApplication.Workbooks.Add();
            XlWorksheet sheet = excelApplication.Workbooks[1].Worksheets[1];

            /*
             *  do the test
             */
            DateTime timeStart = DateTime.Now;

            for (int i = 1; i <= 10000; i++)
            {
                string  rangeAdress = "$A" + i.ToString();
                XlRange cellRange   = sheet.Range(rangeAdress);
                cellRange.Value        = "value";
                cellRange.Font.Name    = "Verdana";
                cellRange.NumberFormat = "@";

                cellRange.WrapText = false;
                XlComment sampleComment = cellRange.AddComment("Sample Comment");
            }
            DateTime timeEnd     = DateTime.Now;
            TimeSpan timeElapsed = timeEnd - timeStart;

            /*
             * display for user
             */
            string outputConsole = string.Format("Time Elapsed: {0}{1}Press any Key.", timeElapsed, Environment.NewLine);

            Console.WriteLine(outputConsole);
            Console.Read();

            /*
             * write result in logfile
             */
            string logFile       = Path.Combine(Environment.CurrentDirectory, "LateBinding.log");
            string logFileAppend = timeElapsed.ToString() + Environment.NewLine;

            File.AppendAllText(logFile, logFileAppend, Encoding.UTF8);

            excelApplication.Quit();
            excelApplication.Dispose();
        }
コード例 #7
0
 public void SetupEventBinding(XlApplication application)
 {
     if (true == XlLateBindingApiSettings.EventsEnabled)
     {
         _application = application;
         IConnectionPointContainer connectionPointContainer = (IConnectionPointContainer)application.COMReference;
         Guid guid = new Guid("{00024413-0000-0000-C000-000000000046}");
         connectionPointContainer.FindConnectionPoint(ref guid, out _connectionPoint);
         _connectionPoint.Advise(this, out _connectionCookie);
     }
 }
コード例 #8
0
        private void button1_Click(object sender, EventArgs e)
        {
            // start excel and turn off msg boxes
            _excelApplication = new XlApplication();
            _excelApplication.DisplayAlerts  = false;
            _excelApplication.ScreenUpdating = false;

            // add a new workbook
            XlWorkbook workBook = _excelApplication.Workbooks.Add();

            // we use the first sheet as summary sheet and remove the 2 last sheets
            XlWorksheet 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
            XlWorksheet productSheet = null;

            foreach (SalesReportProduct itemProduct in _report.Products)
            {
                productSheet = workBook.Worksheets.Add();
                ProceedProductWorksheet(productSheet, itemProduct);
                productSheet.Move(null, workBook.Worksheets[workBook.Worksheets.Count]);
            }

            // write summary sheet
            ProceedSummaryWorksheet(_report, workBook, summarySheet, productSheet);
            summarySheet.Range("$A2").Select();

            // save the book
            string fileExtension = XlConverter.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
        /// <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(XlApplication application)
        {
            double Version = Convert.ToDouble(application.Version);

            if (Version >= 120.00)
            {
                return(".xlsx");
            }
            else
            {
                return(".xls");
            }
        }
コード例 #10
0
        private void button1_Click(object sender, EventArgs e)
        {
            // start excel and turn off msg boxes
            excelApplication = new XlApplication();
            excelApplication.DisplayAlerts = false;

            // add a new workbook
            XlWorkbook  workBook  = excelApplication.Workbooks.Add();
            XlWorksheet workSheet = workBook.Worksheets[1];

            workSheet.Cells(1, 1).Value = "these sample shapes was dynamicly created by code.";

            // create a star
            XlShape starShape = workSheet.Shapes.AddShape(LateBindingApi.Excel.Enums.MsoAutoShapeType.msoShape32pointStar, 10, 50, 200, 20);

            // create a simple textbox
            XlShape textBox = workSheet.Shapes.AddTextbox(LateBindingApi.Excel.Enums.MsoTextOrientation.msoTextOrientationHorizontal, 10, 150, 200, 50);

            textBox.TextFrame.Characters().Text = "text";
            textBox.TextFrame.Characters().Font.Size = 14;

            // create a wordart
            XlShape textEffect = workSheet.Shapes.AddTextEffect(LateBindingApi.Excel.Enums.MsoPresetTextEffect.msoTextEffect14, "WordArt", "Arial", 12, LateBindingApi.Excel.Enums.MsoTriState.msoTrue, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, 10, 250);

            // create text effect
            XlShape textDiagram = workSheet.Shapes.AddTextEffect(LateBindingApi.Excel.Enums.MsoPresetTextEffect.msoTextEffect11, "Effect", "Arial", 14, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, 10, 350);

            // save the book
            string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
            string workbookFile = string.Format("{0}\\Example4{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);
        }
コード例 #11
0
        private void button1_Click(object sender, EventArgs e)
        {
            // start excel and turn off msg boxes
            excelApplication = new XlApplication();
            excelApplication.DisplayAlerts = false;

            // add a new workbook
            XlWorkbook workBook = excelApplication.Workbooks.Add();

            // add new global Code Module
            XlVBComponent globalModule = workBook.VBProject.VBComponents.Add(LateBindingApi.Excel.Enums.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 = XlConverter.GetDefaultExtension(excelApplication);
            string workbookFile  = string.Format("{0}\\Example7{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 IDTExtensibility2.OnConnection(object Application, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
 {
     _application = new XlApplication(null, Application);
 }
コード例 #13
0
        private void button1_Click(object sender, EventArgs e)
        {
            XlCommandBar       commandBar;
            XlCommandBarPopup  commandBarPop;
            XlCommandBarButton commandBarBtn;

            // first we enable the event support
            XlLateBindingApiSettings.EventsEnabled = true;

            // start excel and turn off msg boxes
            excelApplication = new XlApplication();
            excelApplication.DisplayAlerts = false;

            // add a new workbook
            XlWorkbook workBook = excelApplication.Workbooks.Add();

            #region Create a new menu

            // add a commandbar popup
            commandBarPop = (XlCommandBarPopup)excelApplication.CommandBars["Worksheet Menu Bar"].Controls.Add(
                MsoControlType.msoControlPopup, Missing.Value, Missing.Value, Missing.Value, true);
            commandBarPop.Caption = "commandBarPopup";

            // add a button to the popup
            #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
             * is 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

            commandBarBtn         = (XlCommandBarButton)commandBarPop.Controls.Add(MsoControlType.msoControlButton);
            commandBarBtn.Style   = MsoButtonStyle.msoButtonIconAndCaption;
            commandBarBtn.Caption = "commandBarButton";
            Clipboard.SetDataObject(this.Icon.ToBitmap());
            commandBarBtn.PasteFace();
            commandBarBtn.Click += new 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         = (XlCommandBarButton)commandBar.Controls.Add(MsoControlType.msoControlButton);
            commandBarBtn.Style   = MsoButtonStyle.msoButtonIconAndCaption;
            commandBarBtn.Caption = "commandBarButton";
            commandBarBtn.FaceId  = 3;
            commandBarBtn.Click  += new CommandBarButtonEvents_ClickEventHandler(commandBarBtn_Click);

            // add a dropdown box to the toolbar
            commandBarPop         = (XlCommandBarPopup)commandBar.Controls.Add(MsoControlType.msoControlPopup);
            commandBarPop.Caption = "commandBarPopup";

            // add a button to the popup, we use an own icon for the button
            commandBarBtn         = (XlCommandBarButton)commandBarPop.Controls.Add(MsoControlType.msoControlButton);
            commandBarBtn.Style   = MsoButtonStyle.msoButtonIconAndCaption;
            commandBarBtn.Caption = "commandBarButton";
            Clipboard.SetDataObject(this.Icon.ToBitmap());
            commandBarBtn.PasteFace();
            commandBarBtn.Click += new CommandBarButtonEvents_ClickEventHandler(commandBarBtn_Click);

            #endregion

            #region Create a new ContextMenu

            // add a commandbar popup
            commandBarPop = (XlCommandBarPopup)excelApplication.CommandBars["Cell"].Controls.Add(
                MsoControlType.msoControlPopup, Missing.Value, Missing.Value, Missing.Value, true);
            commandBarPop.Caption = "commandBarPopup";

            // add a button to the popup
            commandBarBtn         = (XlCommandBarButton)commandBarPop.Controls.Add(MsoControlType.msoControlButton);
            commandBarBtn.Style   = MsoButtonStyle.msoButtonIconAndCaption;
            commandBarBtn.Caption = "commandBarButton";
            commandBarBtn.FaceId  = 9;
            commandBarBtn.Click  += new 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

            excelApplication.Visible = true;

            button1.Enabled = false;
            button2.Enabled = true;
        }
コード例 #14
0
        private void button1_Click(object sender, EventArgs e)
        {
            // start excel and turn off msg boxes
            excelApplication = new XlApplication();
            excelApplication.DisplayAlerts = false;

            // dont show dialogs with an invisible excel
            excelApplication.Visible = true;

            // add a new workbook
            XlWorkbook  workBook  = excelApplication.Workbooks.Add();
            XlWorksheet 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[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogAddinManager].Show();
                break;

            case "xlDialogFont":

                returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogFont].Show();
                break;

            case "xlDialogEditColor":

                returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogEditColor].Show();
                break;

            case "xlDialogGallery3dBar":

                returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogGallery3dBar].Show();
                break;

            case "xlDialogSearch":

                returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogSearch].Show();
                break;

            case "xlDialogPrinterSetup":

                returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogPrinterSetup].Show();
                break;

            case "xlDialogFormatNumber":

                returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogFormatNumber].Show();
                break;

            case "xlDialogApplyStyle":

                returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.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();
        }
コード例 #15
0
        private void button1_Click(object sender, EventArgs e)
        {
            // start excel and turn off msg boxes
            excelApplication = new XlApplication();
            excelApplication.DisplayAlerts = false;

            // add a new workbook
            XlWorkbook  workBook  = excelApplication.Workbooks.Add();
            XlWorksheet workSheet = workBook.Worksheets[1];

            // some kind of numerics

            string Pattern1 = string.Format("0{0}00", XlLateBindingApiSettings.XlThreadCulture.NumberFormat.CurrencyDecimalSeparator);
            string Pattern2 = string.Format("#{1}##0{0}00", XlLateBindingApiSettings.XlThreadCulture.NumberFormat.CurrencyDecimalSeparator, XlLateBindingApiSettings.XlThreadCulture.NumberFormat.CurrencyGroupSeparator);

            workSheet.Range("A1").Value = "Type";
            workSheet.Range("B1").Value = "Value";
            workSheet.Range("C1").Value = "Formatted " + Pattern1;
            workSheet.Range("D1").Value = "Formatted " + Pattern2;

            int integerValue = 532234;

            workSheet.Range("A3").Value        = "Integer";
            workSheet.Range("B3").Value        = integerValue;
            workSheet.Range("C3").Value        = integerValue;
            workSheet.Range("C3").NumberFormat = Pattern1;
            workSheet.Range("D3").Value        = integerValue;
            workSheet.Range("D3").NumberFormat = Pattern2;

            double doubleValue = 23172.64;

            workSheet.Range("A4").Value        = "double";
            workSheet.Range("B4").Value        = doubleValue;
            workSheet.Range("C4").Value        = doubleValue;
            workSheet.Range("C4").NumberFormat = Pattern1;
            workSheet.Range("D4").Value        = doubleValue;
            workSheet.Range("D4").NumberFormat = Pattern2;

            float floatValue = 84345.9132f;

            workSheet.Range("A5").Value        = "float";
            workSheet.Range("B5").Value        = floatValue;
            workSheet.Range("C5").Value        = floatValue;
            workSheet.Range("C5").NumberFormat = Pattern1;
            workSheet.Range("D5").Value        = floatValue;
            workSheet.Range("D5").NumberFormat = Pattern2;

            Decimal decimalValue = 7251231.313367m;

            workSheet.Range("A6").Value        = "Decimal";
            workSheet.Range("B6").Value        = decimalValue;
            workSheet.Range("C6").Value        = decimalValue;
            workSheet.Range("C6").NumberFormat = Pattern1;
            workSheet.Range("D6").Value        = decimalValue;
            workSheet.Range("D6").NumberFormat = Pattern2;

            workSheet.Range("A9").Value  = "DateTime";
            workSheet.Range("B10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.FullDateTimePattern;
            workSheet.Range("C10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.LongDatePattern;
            workSheet.Range("D10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.ShortDatePattern;
            workSheet.Range("E10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.LongTimePattern;
            workSheet.Range("F10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.ShortTimePattern;

            // DateTime

            DateTime dateTimeValue = DateTime.Now;

            workSheet.Range("B11").Value        = dateTimeValue;
            workSheet.Range("B11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.FullDateTimePattern;

            workSheet.Range("C11").Value        = dateTimeValue;
            workSheet.Range("C11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.LongDatePattern;

            workSheet.Range("D11").Value        = dateTimeValue;
            workSheet.Range("D11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.ShortDatePattern;

            workSheet.Range("E11").Value        = dateTimeValue;
            workSheet.Range("E11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.LongTimePattern;

            workSheet.Range("F11").Value        = dateTimeValue;
            workSheet.Range("F11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.ShortTimePattern;

            // string
            workSheet.Range("A14").Value        = "String";
            workSheet.Range("B14").Value        = "This is a sample String";
            workSheet.Range("B14").NumberFormat = "@";
            // number as string
            workSheet.Range("B15").Value        = "513";
            workSheet.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 = XlConverter.GetDefaultExtension(excelApplication);
            string workbookFile  = string.Format("{0}\\Example3{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);
        }
コード例 #16
0
        private void button1_Click(object sender, EventArgs e)
        {
            // start excel and turn off msg boxes
            excelApplication = new XlApplication();
            excelApplication.DisplayAlerts = false;

            // add a new workbook
            XlWorkbook  workBook  = excelApplication.Workbooks.Add();
            XlWorksheet workSheet = workBook.Worksheets[1];

            // font action
            workSheet.Range("A1").Value          = "Arial Size:8 Bold Italic Underline";
            workSheet.Range("A1").Font.Name      = "Arial";
            workSheet.Range("A1").Font.Size      = 8;
            workSheet.Range("A1").Font.Bold      = true;
            workSheet.Range("A1").Font.Italic    = true;
            workSheet.Range("A1").Font.Underline = true;
            workSheet.Range("A1").Font.Color     = Color.Violet.ToArgb();

            workSheet.Range("A3").Value      = "Times New Roman Size:10";
            workSheet.Range("A3").Font.Name  = "Times New Roman";
            workSheet.Range("A3").Font.Size  = 10;
            workSheet.Range("A3").Font.Color = Color.Orange.ToArgb();

            workSheet.Range("A5").Value      = "Comic Sans MS Size:12 WrapText";
            workSheet.Range("A5").Font.Name  = "Comic Sans MS";
            workSheet.Range("A5").Font.Size  = 12;
            workSheet.Range("A5").WrapText   = true;
            workSheet.Range("A5").Font.Color = Color.Navy.ToArgb();

            // HorizontalAlignment
            workSheet.Range("A7").Value = "xlHAlignLeft";
            workSheet.Range("A7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignLeft;

            workSheet.Range("B7").Value = "xlHAlignCenter";
            workSheet.Range("B7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignCenter;

            workSheet.Range("C7").Value = "xlHAlignRight";
            workSheet.Range("C7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignRight;

            workSheet.Range("D7").Value = "xlHAlignJustify";
            workSheet.Range("D7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignJustify;

            workSheet.Range("E7").Value = "xlHAlignDistributed";
            workSheet.Range("E7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignDistributed;

            workSheet.Range("A9").Value             = "xlVAlignTop";
            workSheet.Range("A9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignTop;

            workSheet.Range("B9").Value             = "xlVAlignCenter";
            workSheet.Range("B9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignCenter;

            workSheet.Range("C9").Value             = "xlVAlignBottom";
            workSheet.Range("C9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignBottom;

            workSheet.Range("D9").Value             = "xlVAlignDistributed";
            workSheet.Range("D9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignDistributed;

            workSheet.Range("E9").Value             = "xlVAlignJustify";
            workSheet.Range("E9").VerticalAlignment = LateBindingApi.Excel.Enums.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 = XlConverter.GetDefaultExtension(excelApplication);
            string workbookFile  = string.Format("{0}\\Example2{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);
        }