public void Run() { // Replace Excel.Workbook with MyWorkbook NetOffice.Core.Default.ObjectActivator.CreateInstance += delegate(Core sender, NetOffice.CoreServices.OnCreateInstanceEventArgs args) { if (args.RequestedFrom.ContractType == typeof(Excel.Workbook)) { args.Replace = new MyWorkbook(); } }; Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; // add and cast book to MyWorkbook MyWorkbook book = application.Workbooks.Add() as MyWorkbook; if (book.Has3Sheets) { Console.WriteLine("Book has 3 sheets."); } application.Quit(); application.Dispose(); HostApplication.ShowFinishDialog(); }
private static void TestExcel() { Console.WriteLine("Test Excel File Utils"); Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; Excel.Tools.Contribution.CommonUtils utils = new Excel.Tools.Contribution.CommonUtils(application); string fileName = utils.File.Combine("C:\\MyFiles", "Test01", Excel.Tools.Contribution.DocumentFormat.Normal); application.Quit(); application.Dispose(); if (utils.ApplicationIs2007OrHigher) { if ("C:\\MyFiles\\Test01.xlsx" != fileName) { throw new Exception("Unexpected excel filename"); } } else { if ("C:\\MyFiles\\Test01.xls" != fileName) { throw new Exception("Unexpected excel filename"); } } }
public void Run() { // this examples shows a special method to ask at runtime for a particular method oder property // morevover you can enable the option NetOffice.Settings.EnableSafeMode. // NetOffice checks(cache supported) for any method or property you call and // throws a EntitiyNotSupportedException if missing // create new instance Excel.Application application = new Excel.ApplicationClass(); // any reference type in NetOffice implements the EntityIsAvailable method. // you check here your property or method is available. // we check the support for 2 properties at runtime bool enableLivePreviewSupport = application.EntityIsAvailable("EnableLivePreview"); bool openDatabaseSupport = application.Workbooks.EntityIsAvailable("OpenDatabase"); string result = "Excel Runtime Check: " + Environment.NewLine; result += "Support EnableLivePreview: " + enableLivePreviewSupport.ToString() + Environment.NewLine; result += "Support OpenDatabase: " + openDatabaseSupport.ToString() + Environment.NewLine; // quit and dispose application.Quit(); application.Dispose(); HostApplication.ShowMessage(result); }
public void Run() { // this is a simple demonstration how to convert unkown types at runtime // start application Excel.Application application = new Excel.ApplicationClass(); application.Visible = false; application.DisplayAlerts = false; foreach (Office.COMAddIn item in application.COMAddIns) { // the application property is an unkown COM object // we need a simple cast at runtime Excel.Application hostApp = item.Application as Excel.Application; // do some sample stuff string hostAppName = hostApp.Name; bool hostAppVisible = hostApp.Visible; } // quit and dispose excel application.Quit(); application.Dispose(); HostApplication.ShowFinishDialog(); }
public static void Main(string[] args) { Excel.Application app = null; try { System.Console.WriteLine("NetOffice CreateInstance Event Concept Test\r\n"); // Use this.Factory.CreateInstance instead in NetOffice Tools COMAddin NetOffice.Core.Default.ObjectActivator.CreateInstance += ObjectActivator_CreateInstance; app = new Excel.ApplicationClass(); Excel.Workbook book = app.Workbooks.Add(); MyCustomWorksheet sheet = book.Sheets[1] as MyCustomWorksheet; sheet.PrintName(); Console.WriteLine("\r\nTest passed"); Console.ReadKey(); } catch (Exception exception) { Console.WriteLine(exception.Message); Console.ReadKey(); } finally { if (null != app) { app.Quit(); app.Dispose(); app = null; } } }
public void Run() { // this example shows you how i still can recieve events from an disposed proxy. // you have to use th Dispose oder DisposeChildInstances method with a parameter. // start application Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; // create new Workbook & attach close event trigger Excel.Workbook book = application.Workbooks.Add(); book.BeforeCloseEvent += new Excel.Workbook_BeforeCloseEventHandler(book_BeforeCloseEvent); // we dispose the instance. the parameter false signals to api dont release the event listener // set parameter to true and the event listener will stopped and you dont get events for the instance // the DisposeChildInstances() method has the same method overload book.Close(); book.Dispose(false); application.Quit(); application.Dispose(); // the application object is ouer root object // dispose them release himself and any childs of application, in this case workbooks and workbook // the excel instance are now removed from process list HostApplication.ShowFinishDialog(); }
internal void Run() { Excel.Application app = null; try { Settings.Default.PerformanceTrace.Alert += new PerformanceTrace.PerformanceAlertEventHandler(PerformanceTrace_Alert); Settings.Default.PerformanceTrace["ExcelApi"].Enabled = true; Settings.Default.PerformanceTrace["ExcelApi"].IntervalMS = 0; app = new Excel.ApplicationClass(); //app = COMObject.Create<Excel.Application>(); app.Visible = true; Contribution.CommonUtils utils = new Contribution.CommonUtils(app, typeof(Form1).Assembly); app.DisplayAlerts = false; Excel.Workbook book = app.Workbooks.Add(); Excel.Worksheet sheet = book.Sheets[1] as Excel.Worksheet; sheet.Cells[1, 1].Value = "This is a sample value"; sheet.Protect(); utils.Dialog.SuppressOnAutomation = false; utils.Dialog.SuppressOnHide = false; utils.Dialog.ShowDiagnostics(true); } catch (Exception exception) { Console.WriteLine(exception.ToString()); } finally { if (null != app) { app.Quit(); app.Dispose(); } } }
public void Run() { // NetOffice instances implements the IClonable interface // and deal with underlying proxies as well Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; Excel.Workbook book = application.Workbooks.Add(); // clone the book Excel.Workbook cloneBook = book.Clone() as Excel.Workbook; // dispose the origin book keep the underlying proxy alive // until the clone is disposed book.Dispose(); // alive and works even the origin book is disposed foreach (Excel.Worksheet sheet in cloneBook.Sheets) { Console.WriteLine(sheet); } application.Quit(); application.Dispose(); HostApplication.ShowFinishDialog(); }
public void Run() { // this examples shows how to use variant types(object in NetOffice) at runtime // the reason for the most variant definitions in office is a more flexible value set.(95%) // here is some code to demonstrate this // start application Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; // create new Workbook and a new named style Excel.Workbook book = application.Workbooks.Add(); Excel.Worksheet sheet = (Excel.Worksheet)book.Worksheets[1]; Excel.Range range = sheet.Cells[1, 1]; Excel.Style myStyle = book.Styles.Add("myUniqueStyle"); // Range.Style is defined as Variant in Excel and as object in NetOffice // You got always an Excel.Style instance if you ask for Excel.Style style = (Excel.Style)range.Style; // and here comes the magic. both sets are valid because the variants was very flexible in the setter range.Style = "myUniqueStyle"; range.Style = myStyle; // Name, Bold, Size are string, bool and double but defined as Variant style.Font.Name = "Arial"; style.Font.Bold = true; // you can also set "true" and it works. variants makes it possible style.Font.Size = 14; // quit & dipose application.Quit(); application.Dispose(); HostApplication.ShowFinishDialog(); }
public void Run() { // In some situations you want use NetOffice with an already running application. // this tutorial shows how its possible. // 1) // // GetActiveInstance take the first instance in memory Excel.Application application = ProxyService.GetActiveInstance <Excel.Application>(); if (null != application) { application.Dispose(); } // 2) // // GetActiveInstances takes all instances in memory var applications = ProxyService.GetActiveInstances <Excel.Application>(); applications.Dispose(); // 3) // // Use special ctor to try access a running application first // and if its failed create a new application application = new Excel.ApplicationClass(new Core(), true); // quit only if its a new application if (!application.FromProxyService) { application.Quit(); } application.Dispose(); // 4) // // Creates instance from interop proxy Type interopType = Type.GetTypeFromProgID("Excel.Application"); object proxy = Activator.CreateInstance(interopType); application = COMObject.Create <Excel.Application>(proxy); application.Quit(); application.Dispose(); HostApplication.ShowFinishDialog(); }
static void Main(string[] args) { Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; application.Visible = false; application.OnDispose += new NetOffice.OnDisposeEventHandler(application_OnDispose); application.Workbooks.Add(); CancelDispose = true; application.Dispose(); // cancel the first dispose CancelDispose = false; application.Quit(); application.Dispose(); }
static void Main(string[] args) { try { // Console.WriteLine("NetOffice Utils Concept Test"); Console.WriteLine("0 Milliseconds trace values is not a bug - its just to fast\r\n"); NetOffice.Settings.Default.PerformanceTrace.Enabled = true; NetOffice.Settings.Default.PerformanceTrace.Alert += new NetOffice.PerformanceTrace.PerformanceAlertEventHandler(PerformanceTrace_Alert); // Criteria 1 // Enable performance trace in excel generaly. set interval limit to 100 to see all actions there need >= 100 milliseconds NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].IntervalMS = 100; // Criteria 2 // Enable additional performance trace for all members of Range in excel. set interval limit to 20 to see all actions there need >=20 milliseconds NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range"].IntervalMS = 20; // Criteria 3 // Enable additional performance trace for WorkSheet Range property in excel. set interval limit to 0 to see all calls anywhere NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].IntervalMS = 0; // Criteria 4 // Enable additional performance trace for Range this[] indexer in excel. set interval limit to 0 to see all calls anywhere NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range", "_Default"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range", "_Default"].IntervalMS = 0; Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; Excel.Workbook book = application.Workbooks.Add(); Excel.Worksheet sheet = book.Sheets.Add() as Excel.Worksheet; for (int i = 1; i <= 5; i++) { Excel.Range range = sheet.Range("A" + i.ToString()); range.Value = "Test123"; range[1, 1].Value = "Test234"; } application.Quit(); application.Dispose(); Console.WriteLine("\r\nTest passed"); Console.ReadKey(); } catch (Exception exception) { Console.WriteLine(exception.Message); Console.ReadKey(); } }
public void Run() { // Any MS-Office application in NetOffice has a custom contribution provider for common tasks // Moreover its available as instance property in NetOffice.Tools.COMAddin // If you have suggestions for the contribution please feel free to contact the project // This tutorial shows only few features in MS-Excel // start excel and disable alerts Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; // Create an instance of excel utils CommonUtils utils = new CommonUtils(application, typeof(Tutorial17).Assembly); // the file part of the utils makes it easier to deal with file extensions depedent on the current version // get default(xls or xlsx) , template with macros(xlt or xltm) - extension and build a valid file path string extensionNormal = utils.File.FileExtension(DocumentFormat.Normal); string extensionTemplateWithMacros = utils.File.FileExtension(DocumentFormat.TemplateMacros); string exampleFilePath = utils.File.Combine("C:\\MyFiles", "MyWorkbook", DocumentFormat.Normal); // the dialog part of the utils allows you to show default dialogs/messageboxes or you own dialogs // dialogs want be suppressed by default if the office application is currently in automation or not visible // you can also trigger the DialogShow and DialogShown event to observe dialog popups // we disable any suppress behavior here utils.Dialog.SuppressOnAutomation = false; utils.Dialog.SuppressOnHide = false; // show a simple message box. Have a look at the last argument. Its a default result and used if the messagebox is not shown. // In this tutorial, excel is in automation and hidden. Remove one or both of the 2 code lines above and the message box is not shown. // We got the default result in this case var userResult = utils.Dialog.ShowMessageBox( "Hello World from NetOffice tutorial", "NO tutorial", OfficeContribution.DialogUtils.Buttons.YesNo, OfficeContribution.DialogUtils.Result.No); application.Quit(); application.Dispose(); HostApplication.ShowFinishDialog(); }
static void Main(string[] args) { Console.WriteLine("Write 1 million cells in excel."); NetOffice.Settings.Default.PerformanceTrace.Alert += new NetOffice.PerformanceTrace.PerformanceAlertEventHandler(PerformanceTrace_Alert); NetOffice.Settings.Default.PerformanceTrace["ExcelApi"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["ExcelApi"].IntervalMS = 20; Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; application.Interactive = false; application.ScreenUpdating = false; application.Workbooks.Add(); Excel.Worksheet workSheet = (Excel.Worksheet)application.Workbooks[1].Worksheets[1]; Excel.Range rangeCells = workSheet.Cells; // row int counter = 0; DateTime startTime = DateTime.Now; for (int i = 1; i <= 10000; i++) { // column for (int y = 1; y <= 100; y++) { Excel.Range range = rangeCells[i, y]; range.Value = "TestValue"; range.Dispose(); counter++; } if (i % 100 == 0) { Console.WriteLine("{0} Cells written. Time elapsed: {1}", counter, DateTime.Now - startTime); } } // quit and dispose application.Quit(); application.Dispose(); Console.WriteLine("Done!"); }
public void Run() { // this example shows you another dispose method: DisposeChildInstances // this means all child proxies from an instance // start application Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; Excel.Workbook book = application.Workbooks.Add(); Excel.Worksheet sheet = (Excel.Worksheet)book.Worksheets.Add(); /* * we have 5 created proxies now in proxy table as follows * * Application * + Workbooks * + Workbook * + Worksheets * + Worksheet */ // we dispose the child instances from book book.DisposeChildInstances(); /* * we have 3 created proxies now, the childs from book are disposed * * Application * + Workbooks * + Workbook */ application.Quit(); application.Dispose(); // the Dispose() call for application release the instance and created childs Workbooks and Workbook HostApplication.ShowFinishDialog(); }
public void Run() { // this example demonstrate the global helper module(static class) // the module is a vba compatibility workarround and contains static methods and properties from the coresponding Application class. // start excel and add a new workbook Excel.Application application = new Excel.ApplicationClass(); application.Visible = false; application.DisplayAlerts = false; application.Workbooks.Add(); // ApplicationModule contains the well known globals and its located in NetOffice.$XXXApi.ModulesLegacy // This helpful to bring code from VBA to NetOffice ActiveCell.Value = "ActiveCellValue"; // quit and dispose excel application.Quit(); application.Dispose(); HostApplication.ShowFinishDialog(); }
public void Run() { // Enable and trigger trace alert NetOffice.Settings.Default.PerformanceTrace.Enabled = true; NetOffice.Settings.Default.PerformanceTrace.Alert += delegate(NetOffice.PerformanceTrace sender, NetOffice.PerformanceTrace.PerformanceAlertEventArgs args) { Console.WriteLine("{0} {1}:{2} in {3} Milliseconds ({4} Ticks)", args.CallType, args.EntityName, args.MethodName, args.TimeElapsedMS, args.Ticks); }; // Criteria 1 // Enable performance trace in excel generaly. set interval limit to 100ms to see all actions there need >= 100 milliseconds NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].IntervalMS = 100; // Criteria 2 // Enable additional performance trace for all members of WorkSheet in excel. set interval limit to 20ms to see all actions there need >=20 milliseconds NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet"].IntervalMS = 20; // Criteria 3 // Enable additional performance trace for WorkSheet Range property in excel. set interval limit to 0ms to see all calls anywhere NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].IntervalMS = 0; // do some stuff Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; Excel.Workbook book = application.Workbooks.Add(); Excel.Worksheet sheet = book.Sheets.Add() as Excel.Worksheet; for (int i = 1; i <= 5; i++) { Excel.Range range = sheet.Range("A" + i.ToString()); range.Value = "Test123"; range[1, 1].Value = "Test234"; } application.Quit(); application.Dispose(); HostApplication.ShowFinishDialog(); }
public void Run() { // NetOffice manages COM Proxies to avoid any kind of memory leaks // and make sure your application instance removes from process list if you want. // start application Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; 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 * with the application object we have 3 created proxies now. the workbooks proxy was created * about application and the book proxy was created about the workbooks. * NetOffice holds the proxies now in a list as follows: * * Application * + Workbooks * + Workbook * * any object in NetOffice implements the IDisposible Interface. * use the Dispose() method to release an object. the method release all created child proxies too. */ application.Quit(); application.Dispose(); /* * the application object is ouer root object * dispose them release himself and any childs of application, in this case workbooks and workbook * the excel instance are now removed from process list */ HostApplication.ShowFinishDialog(); }
public void Run() { // this example demonstrate the NetOffice low-level interface for latebinding calls Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; application.Workbooks.Add(); Excel.Worksheet sheet = (Excel.Worksheet)application.Workbooks[1].Worksheets[1]; Excel.Range sampleRange = sheet.Cells[1, 1]; // we set the COMVariant ColorIndex from Font of ouer sample range with the invoker class Invoker.Default.PropertySet(sampleRange.Font, "ColorIndex", 1); // creates a native unmanaged ComProxy with the invoker an release immediately object comProxy = Invoker.Default.PropertyGet(application, "Workbooks"); Marshal.ReleaseComObject(comProxy); application.Quit(); application.Dispose(); HostApplication.ShowFinishDialog(); }
public void Run() { // Best practice to write own IEnumerable<T> extensions. // See sample extension at the end of these file. // NetOffice spend some extensions on IEnumerable<T> you may know from Linq2Objects. // These extensions take care to free unused/unwanted COM Proxies immediately. // However, the extensions doesnt works like Linq which means calling the result // execute the method on demand. Its just ordinary extensions. Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; application.Workbooks.Add(); // Here we use "First()" and "FirstOrDefault()" and the Invoker extension "Property" because Sheets is an untyped collection Excel.Worksheet sheet = application.Workbooks.First().Sheets.FirstOrDefault(e => e.Property <string>("Name") == "Sheet1") as Excel.Worksheet; if (null != sheet) { sheet.Cells[1, 1].Value = "Test123"; sheet.Cells[5, 5].Value = "Test123"; sheet.Cells[10, 10].Value = "Test123"; // iterate over 10x10 used range and return the 3 cells we filled // Linq2Objects would create 101 new proxies(10x10 + enumerator) here without any chance to free them. // In NetOffice exensions - you have just 4 new managed proxies. var ranges = sheet.UsedRange.Where(e => e.Value != null); // doing the same here again with the tutorial sample extension (scroll down) ranges = sheet.UsedRange.AllCellsWithValues(); } application.Quit(); application.Dispose(); HostApplication.ShowFinishDialog(); }