public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
application = new NetOffice.ExcelApi.Application();
application.Visible = true;
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet;
Office.COMAddIn addin = (from a in application.COMAddIns where a.ProgId == "ExcelAddinCSharp.TestAddin" select a).FirstOrDefault();
if(null == addin)
return new TestResult(false, DateTime.Now.Subtract(startTime), "COMAddin ExcelAddinCSharp.TestAddin not found.", null, "");
bool ribbonIsOkay = false;
bool taskPaneIsOkay = false;
if (null != addin.Object)
{
COMObject addinProxy = new COMObject(null, addin.Object);
ribbonIsOkay = (bool)Invoker.PropertyGet(addinProxy, "RibbonUIPassed");
taskPaneIsOkay = (bool)Invoker.PropertyGet(addinProxy, "TaskPanePassed");
addinProxy.Dispose();
}
if( ribbonIsOkay && taskPaneIsOkay)
return new TestResult(true, DateTime.Now.Subtract(startTime), "", null, "");
else
return new TestResult(false, DateTime.Now.Subtract(startTime), string.Format("Ribbon:{0} TaskPane:{1}", ribbonIsOkay, taskPaneIsOkay), null, "");
}
catch (Exception exception)
{
return new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, "");
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// we need some data to display
Excel.Range dataRange = PutSampleData(workSheet);
// create a nice diagram
Excel.ChartObject chart = ((Excel.ChartObjects)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}", _hostApplication.RootDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
_hostApplication.ShowFinishDialog(null, workbookFile);
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
application = new NetOffice.ExcelApi.Application();
application.Visible = true;
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet;
Office.COMAddIn addin = (from a in application.COMAddIns where a.ProgId == "NOTestsMain.ExcelTestAddinCSharp" select a).FirstOrDefault();
if (null == addin || null == addin.Object)
return new TestResult(false, DateTime.Now.Subtract(startTime), "NOTestsMain.ExcelTestAddinCSharp or addin.Object not found.", null, "");
bool addinStatusOkay = false;
string errorDescription = string.Empty;
if (null != addin.Object)
{
COMObject addinProxy = new COMObject(addin.Object);
addinStatusOkay = (bool)Invoker.Default.PropertyGet(addinProxy, "StatusOkay");
errorDescription = (string)Invoker.Default.PropertyGet(addinProxy, "StatusDescription");
addinProxy.Dispose();
}
if (addinStatusOkay)
return new TestResult(true, DateTime.Now.Subtract(startTime), "", null, "");
else
return new TestResult(false, DateTime.Now.Subtract(startTime), string.Format("NOTestsMain.ExcelTestAddinCSharp Addin Status {0}", errorDescription), null, "");
}
catch (Exception exception)
{
return new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, "");
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
public void Disconnect()
{
if (null != _application)
{
_application.Quit();
_application.Dispose();
}
}
public void Disconnect()
{
if (null != _application)
{
_application.Quit();
_application.Dispose();
_application = null;
}
instanceMonitor1.Factory = null;
}
//public ExcelExporter(string txtBox)
//{
// Console.SetOut(new TextBoxWriter(txtBox));
//}
public void GenerateReport()
{
var excelApplication = new Excel.Application();
excelApplication.Workbooks.Add();
excelApplication.DisplayAlerts = false;
var style = excelApplication.ActiveWorkbook.Styles.Add("NewStyle");
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Bold = true;
//Get active worksheet
var sheet = (Excel.Worksheet)excelApplication.ActiveSheet;
AddColumnNames(sheet);
AddExcelHeadingText(sheet);
AddDataRows(sheet);
var filePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), "FinancialReport");
excelApplication.ActiveWorkbook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, false,
false, XlSaveAsAccessMode.xlExclusive);
//CleanUp
excelApplication.ActiveWorkbook.Close();
excelApplication.Quit();
excelApplication.Dispose();
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// create a utils instance, not need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// we need some data to display
Excel.Range dataRange = PutSampleData(workSheet);
// create a nice diagram
Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
// save the book
string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example05", Excel.Tools.DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
HostApplication.ShowFinishDialog(null, workbookFile);
}
protected void FrmClosed()
{
this.ParentForm.Show();
(this.ParentForm as frmChooseTest).LoadTasks();
try
{
switch (this.Test.OfficeApp)
{
case "Word":
Word.Application wordApp = this.Application as Word.Application;
wordApp?.Quit(Word.Enums.WdSaveOptions.wdDoNotSaveChanges);
wordApp?.Dispose();
break;
case "Excel":
Excel.Application excelApp = this.Application as Excel.Application;
excelApp?.Quit();
excelApp?.Dispose();
break;
case "PowerPoint":
break;
}
}
catch (Exception)
{ }
}
public void Run()
{
// NetOffice instances implements the IClonable interface
// and deal with underlying proxies as well
Excel.Application application = new NetOffice.ExcelApi.Application();
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()
{
// Replace Excel.Workbook with MyWorkbook
NetOffice.Core.Default.CreateInstance += delegate(Core sender, Core.OnCreateInstanceEventArgs args)
{
if (args.Instance.InstanceType == typeof(Excel.Workbook))
{
args.Replace = typeof(MyWorkbook);
}
};
Excel.Application application = new Excel.Application();
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();
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
application = new NetOffice.ExcelApi.Application();
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet;
for (int i = 1; i <= 200; i++)
{
sheet.Cells[i, 1].Value = string.Format("Test {0}", i);
sheet.Range(string.Format("$B{0}", i)).Value = 42.3;
}
return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, ""));
}
catch (Exception exception)
{
return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, ""));
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
public void RunExample()
{
// create excel instance
Excel.Application application = new NetOffice.ExcelApi.Application();
// ask the application object for Quit method support
bool supportQuitMethod = application.EntityIsAvailable("Quit");
// ask the application object for Visible property support
bool supportVisbibleProperty = application.EntityIsAvailable("Visible");
// ask the application object for SmartArtColors property support (only available in Excel 2010)
bool supportSmartArtColorsProperty = application.EntityIsAvailable("SmartArtColors");
// ask the application object for XYZ property or method support (not exists of course)
bool supportTestXYZProperty = application.EntityIsAvailable("TestXYZ");
// print result
string messageBoxContent = "";
messageBoxContent += string.Format("Your installed Excel Version supports the Quit Method: {0}{1}", supportQuitMethod, Environment.NewLine);
messageBoxContent += string.Format("Your installed Excel Version supports the Visible Property: {0}{1}", supportVisbibleProperty, Environment.NewLine);
messageBoxContent += string.Format("Your installed Excel Version supports the SmartArtColors Property: {0}{1}", supportSmartArtColorsProperty, Environment.NewLine);
messageBoxContent += string.Format("Your installed Excel Version supports the TestXYZ Property: {0}{1}", supportTestXYZProperty, Environment.NewLine);
MessageBox.Show(messageBoxContent, "EntityIsAvailable Result", MessageBoxButtons.OK, MessageBoxIcon.Information);
// quit and dispose
application.Quit();
application.Dispose();
}
private void Form1_Shown(object sender, EventArgs e)
{
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;
//Settings.Default.PerformanceTrace["ExcelApi", "Application", "DisplayAlerts"].Enabled = true;
//Settings.Default.PerformanceTrace["ExcelApi", "Application", "DisplayAlerts"].IntervalMS = 0;
app = new Excel.Application();
NOTools.Utils.CommonUtils utils = new NOTools.Utils.CommonUtils(app, typeof(Form1).Assembly);
app.DisplayAlerts = false;
utils.Dialog.SuppressOnAutomation = false;
utils.Dialog.SuppressOnHide = false;
utils.Dialog.ShowDiagnostics(true);
}
catch (Exception exception)
{
Console.WriteLine(exception.ToString());
}
finally
{
app.Quit();
app.Dispose();
Close();
}
}
//public ExcelExporter(string txtBox)
//{
// Console.SetOut(new TextBoxWriter(txtBox));
//}
public void GenerateReport()
{
var excelApplication = new Excel.Application();
excelApplication.Workbooks.Add();
excelApplication.DisplayAlerts = false;
var style = excelApplication.ActiveWorkbook.Styles.Add("NewStyle");
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Bold = true;
//Get active worksheet
var sheet = (Excel.Worksheet)excelApplication.ActiveSheet;
AddColumnNames(sheet);
AddExcelHeadingText(sheet);
AddDataRows(sheet);
var filePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), "FinancialReport");
excelApplication.ActiveWorkbook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, false,
false, XlSaveAsAccessMode.xlExclusive);
//CleanUp
excelApplication.ActiveWorkbook.Close();
excelApplication.Quit();
excelApplication.Dispose();
}
public void WriteAction(User user)
{
var excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
var workbook = excelApplication.Workbooks.Open("TridionSnitch");
var sheet = GetOrCreateUserSheet(workbook, user);
AddExcelHeadingText(sheet);
AddAction(sheet, user);
sheet.SaveAs("TridionSnitch");
//sheet.SaveAs("lekkerW");
//
// excelApplication.Save("marloweTest");
excelApplication.Quit();
excelApplication.Dispose();
Console.WriteLine("saved");
}
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.CreateInstance += new Core.OnCreateInstanceEventHandler(Default_CreateInstance);
//Disable NO console for clean view/output
NetOffice.Core.Default.Console.Mode = DebugConsoleMode.None;
app = new Excel.Application();
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 TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
application = new NetOffice.ExcelApi.Application();
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet;
for (int i = 1; i <= 200; i++)
{
sheet.Cells[i, 1].Value = string.Format("Test {0}", i);
sheet.Range(string.Format("$B{0}", i)).Value = 42.3;
}
return new TestResult(true, DateTime.Now.Subtract(startTime), "",null, "");
}
catch (Exception exception)
{
return new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, "");
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// draw back color and perform the BorderAround method
workSheet.Range("$B2:$B5").Interior.Color = ToDouble(Color.DarkGreen);
workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);
// draw back color and border the range explicitly
workSheet.Range("$D2:$D5").Interior.Color = ToDouble(Color.DarkGreen);
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = ToDouble(Color.Black);
workSheet.Cells[1, 1].Value = "We have 2 simple shapes created.";
// save the book
string fileExtension = GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example01{1}", _hostApplication.RootDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
_hostApplication.ShowFinishDialog(null, workbookFile);
}
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.Application();
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();
}
public void RunExample()
{
Excel.Application application = null;
try
{
// activate the DebugConsole. the default value is: ConsoleMode.None
DebugConsole.Mode = ConsoleMode.MemoryList;
// create excel instance
application = new NetOffice.ExcelApi.Application();
application.DisplayAlerts = false;
// we open a non existing file to produce an error
application.Workbooks.Open("z:\\NotExistingFile.exe");
}
catch (Exception)
{
string messages = null;
foreach (string item in DebugConsole.Messages)
{
messages += item + Environment.NewLine;
}
MessageBox.Show(messages, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// quit and dispose
application.Quit();
application.Dispose();
}
}
public ActionResult Index()
{
// start excel and turn off msg boxes
Application excelApplication = new Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
Workbook workBook = excelApplication.Workbooks.Add();
Worksheet workSheet = (Worksheet) workBook.Worksheets[1];
// draw back color and perform the BorderAround method
workSheet.Range("$B2:$B5").Interior.Color = ToDouble(Color.DarkGreen);
workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);
// draw back color and border the range explicitly
workSheet.Range("$D2:$D5").Interior.Color = ToDouble(Color.DarkGreen);
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle
= XlLineStyle.xlDouble;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = ToDouble(Color.Black);
// save the book
string fileExtension = GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example01{1}", "D:", fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
var bytes = System.IO.File.ReadAllBytes(workbookFile);
System.IO.File.Delete(workbookFile);
return File(bytes, "application/vnd.ms-excel", "FileName2" + fileExtension);
}
public void Run()
{
// this examples shows how i can 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 the code to demonstrate this
// start application
Excel.Application application = new Excel.Application();
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 represents 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()
{
// this is a simple demonstration how i can convert unkown types at runtime
// start application
Excel.Application application = new Excel.Application();
application.Visible = false;
application.DisplayAlerts = false;
foreach (Office.COMAddIn item in application.COMAddIns)
{
// the application property is an unkown COM object
// we need a 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();
}
private void Form1_Shown(object sender, EventArgs e)
{
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;
//Settings.Default.PerformanceTrace["ExcelApi", "Application", "DisplayAlerts"].Enabled = true;
//Settings.Default.PerformanceTrace["ExcelApi", "Application", "DisplayAlerts"].IntervalMS = 0;
app = new Excel.Application();
NOTools.Utils.CommonUtils utils = new NOTools.Utils.CommonUtils(app, typeof(Form1).Assembly);
app.DisplayAlerts = false;
utils.Dialog.SuppressOnAutomation = false;
utils.Dialog.SuppressOnHide = false;
utils.Dialog.ShowDiagnostics(true);
}
catch (Exception exception)
{
Console.WriteLine(exception.ToString());
}
finally
{
app.Quit();
app.Dispose();
Close();
}
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// we need some data to display
Excel.Range dataRange = PutSampleData(workSheet);
// create a nice diagram
Excel.ChartObject chart = ((Excel.ChartObjects)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}", _hostApplication.RootDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
_hostApplication.ShowFinishDialog(null, workbookFile);
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
application = new NetOffice.ExcelApi.Application();
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet;
foreach (Excel.Range item in sheet.Range("$A1:$B100"))
item.Value = DateTime.Now;
return new TestResult(true, DateTime.Now.Subtract(startTime), "", null, "");
}
catch (Exception exception)
{
return new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, "");
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
private static void TestExcel()
{
Console.WriteLine("Test Excel File Utils");
Excel.Application application = new Excel.Application();
application.DisplayAlerts = false;
Excel.Tools.Utils.CommonUtils utils = new Excel.Tools.Utils.CommonUtils(application);
string fileName = utils.File.Combine("C:\\MyFiles", "Test01", Excel.Tools.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");
}
}
}
private static void DoTest()
{
_application = new Excel.Application();
Excel.Workbook book = _application.Workbooks.Add();
WaitHandle[] waitHandles = new WaitHandle[3];
Thread thread1 = new Thread(new ParameterizedThreadStart(Thread1Method));
Thread thread2 = new Thread(new ParameterizedThreadStart(Thread2Method));
Thread thread3 = new Thread(new ParameterizedThreadStart(Thread3Method));
ManualResetEvent mre1 = new ManualResetEvent(false);
ManualResetEvent mre2 = new ManualResetEvent(false);
ManualResetEvent mre3 = new ManualResetEvent(false);
waitHandles[0] = mre1;
waitHandles[1] = mre2;
waitHandles[2] = mre3;
thread1.Start(mre1);
thread2.Start(mre2);
thread3.Start(mre3);
WaitHandle.WaitAll(waitHandles);
_application.Quit();
_application.Dispose();
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
application = COMObject.Create <Excel.Application>(COMObjectCreateOptions.CreateNewCore);
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet;
foreach (Excel.Range item in sheet.Range("$A1:$B100"))
{
item.Value = DateTime.Now;
}
return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, ""));
}
catch (Exception exception)
{
return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, ""));
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
public void Write(string filename, int filterindex_UNUSED, IEnumerable <ITransPair> pairs, IColumnDesc[] columns, InlineString.Render render)
{
string tmpname = null;
try
{
tmpname = CreateTempFile(Path.GetTempPath(), ".xml");
new XmlssWriter().Write(tmpname, 0, pairs, columns, render);
var excel = new Excel()
{
Visible = false, Interactive = false, DisplayAlerts = false
};
try
{
var book = excel.Workbooks.Open(tmpname);
book.SaveAs(filename, XlFileFormat.xlOpenXMLWorkbook);
book.Close(false);
}
finally
{
excel.Quit();
excel.Dispose();
}
}
finally
{
if (tmpname != null)
{
File.Delete(tmpname);
}
}
}
public void Run()
{
// start application
Excel.Application application = new Excel.Application();
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();
}
public void RunExample()
{
Excel.Application application = null;
try
{
// activate the DebugConsole. the default value is: ConsoleMode.None
DebugConsole.Mode = ConsoleMode.MemoryList;
// create excel instance
application = new NetOffice.ExcelApi.Application();
application.DisplayAlerts = false;
// we open a non existing file to produce an error
application.Workbooks.Open("z:\\NotExistingFile.exe");
}
catch (Exception)
{
string messages = null;
foreach (string item in DebugConsole.Messages)
messages += item + Environment.NewLine;
MessageBox.Show(messages, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// quit and dispose
application.Quit();
application.Dispose();
}
}
public void Run()
{
// this examples shows how i can 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 the code to demonstrate this
// start application
Excel.Application application = new Excel.Application();
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 represents 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();
}
internal void Test()
{
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.Application();
Utils.CommonUtils utils = new Utils.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 RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// create a utils instance, not need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// we need some data to display
Excel.Range dataRange = PutSampleData(workSheet);
// create a nice diagram
Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
// save the book
string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example05", Excel.Tools.DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
HostApplication.ShowFinishDialog(null, workbookFile);
}
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.Application();
// 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);
}
private static void DoTest()
{
_application = new Excel.Application();
Excel.Workbook book = _application.Workbooks.Add();
WaitHandle[] waitHandles = new WaitHandle[3];
Thread thread1 = new Thread(new ParameterizedThreadStart(Thread1Method));
Thread thread2 = new Thread(new ParameterizedThreadStart(Thread2Method));
Thread thread3 = new Thread(new ParameterizedThreadStart(Thread3Method));
ManualResetEvent mre1 = new ManualResetEvent(false);
ManualResetEvent mre2 = new ManualResetEvent(false);
ManualResetEvent mre3 = new ManualResetEvent(false);
waitHandles[0] = mre1;
waitHandles[1] = mre2;
waitHandles[2] = mre3;
thread1.Start(mre1);
thread2.Start(mre2);
thread3.Start(mre3);
WaitHandle.WaitAll(waitHandles);
_application.Quit();
_application.Dispose();
}
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.Application();
// 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 i can convert unkown types at runtime
// start application
Excel.Application application = new Excel.Application();
application.Visible = false;
application.DisplayAlerts = false;
foreach (Office.COMAddIn item in application.COMAddIns)
{
// the application property is an unkown COM object
// we need a 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 void RunExample()
{
// create excel instance
Excel.Application application = new NetOffice.ExcelApi.Application();
// ask the application object for Quit method support
bool supportQuitMethod = application.EntityIsAvailable("Quit");
// ask the application object for Visible property support
bool supportVisbibleProperty = application.EntityIsAvailable("Visible");
// ask the application object for SmartArtColors property support (only available in Excel 2010)
bool supportSmartArtColorsProperty = application.EntityIsAvailable("SmartArtColors");
// ask the application object for XYZ property or method support (not exists of course)
bool supportTestXYZProperty = application.EntityIsAvailable("TestXYZ");
// print result
string messageBoxContent = "";
messageBoxContent += string.Format("Your installed Excel Version supports the Quit Method: {0}{1}", supportQuitMethod, Environment.NewLine);
messageBoxContent += string.Format("Your installed Excel Version supports the Visible Property: {0}{1}", supportVisbibleProperty, Environment.NewLine);
messageBoxContent += string.Format("Your installed Excel Version supports the SmartArtColors Property: {0}{1}", supportSmartArtColorsProperty, Environment.NewLine);
messageBoxContent += string.Format("Your installed Excel Version supports the TestXYZ Property: {0}{1}", supportTestXYZProperty, Environment.NewLine);
MessageBox.Show(messageBoxContent, "EntityIsAvailable Result", MessageBoxButtons.OK, MessageBoxIcon.Information);
// quit and dispose
application.Quit();
application.Dispose();
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// draw back color and perform the BorderAround method
workSheet.Range("$B2:$B5").Interior.Color = ToDouble(Color.DarkGreen);
workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);
// draw back color and border the range explicitly
workSheet.Range("$D2:$D5").Interior.Color = ToDouble(Color.DarkGreen);
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = ToDouble(Color.Black);
workSheet.Cells[1, 1].Value = "We have 2 simple shapes created.";
// save the book
string fileExtension = GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example01{1}", _hostApplication.RootDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
_hostApplication.ShowFinishDialog(null, workbookFile);
}
public void Build(string outputFile, string className, DateTime reportDate)
{
using (var db = new SamContext())
using (var excel = new NetOffice.ExcelApi.Application())
{
try
{
excel.DisplayAlerts = false;
var wb = excel.Workbooks.Open(_templateFile, false, false);
var ws = (Worksheet)wb.Worksheets.First();
var students = db.Students.Where(s => s.ClassName == className).ToList();
AppendToList(ws, students.Where(s => s.Sex == "Female"), FemaleRow);
AppendToList(ws, students.Where(s => s.Sex == "Male"), MaleRow);
wb.SaveAs(Path.Combine(_startUpPath, "output.xlsx"));
wb.Close();
Process.Start(Path.Combine(_startUpPath, "output.xlsx"));
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
if (excel.Workbooks.Any() == false)
{
excel.Quit();
}
}
}
}
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.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 RunExample()
{
bool isFailed = false;
string workbookFile = null;
Excel.Application excelApplication = null;
try
{
// start excel and turn off msg boxes
excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
excelApplication.Visible = false;
// create a utils instance, not need for but helpful to keep the lines of code low
Excel.Tools.CommonUtils utils = new Excel.Tools.CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
// add new global Code Module
VB.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 from NetOffice!\" & 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
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets[1];
sheet.Cells[2, 2].Value = "This workbook contains dynamic created VBA Moduls and Event Code";
sheet.Cells[5, 2].Value = "Open the VBA Editor to see the code";
sheet.Cells[8, 2].Value = "Do a double click to catch the BeforeDoubleClick Event from this Worksheet.";
// save the book
XlFileFormat fileFormat = GetFileFormat(excelApplication);
workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example07", Excel.Tools.DocumentFormat.Macros);
workBook.SaveAs(workbookFile, fileFormat);
}
catch (System.Runtime.InteropServices.COMException throwedException)
{
isFailed = true;
HostApplication.ShowErrorDialog("VBA Error", throwedException);
}
finally
{
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
if ((null != workbookFile) && (!isFailed))
{
HostApplication.ShowFinishDialog(null, workbookFile);
}
}
}
private void buttonQuitExample_Click(object sender, EventArgs e)
{
_excelApplication.Quit();
_excelApplication.Dispose();
buttonStartExample.Enabled = true;
buttonQuitExample.Enabled = false;
}
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 = Excel.Application.GetActiveInstance();
if (null != application)
{
application.Dispose();
}
// 2)
//
// GetActiveInstances takes all instances in memory
var applications = Excel.Application.GetActiveInstances();
applications.Dispose();
// 3)
//
// Use special ctor to try access a running application first
// and if its failed create a new application
application = new Excel.Application(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();
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// create a utils instance, not need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
// add a new workbook
//Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Workbook workBook = excelApplication.Workbooks.Open(@"C:\Users\Gilbert Perlaza\Dropbox\HPC\MttoApp\Docs\FORMATO EWO EN BLANCO UNIFICADO OT.XLSX");
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
workSheet.Cells[1, 1].Value = "NetOffice Excel Example 04";
// 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);
Excel.Shape rb = workSheet.Shapes.AddFormControl(Excel.Enums.XlFormControl.xlOptionButton, 100, 100, 100, 100);
var sh = workSheet.Shapes;
foreach (var item in sh)
{
if (item.Name.Equals("Option Button 1"))
{
item.ControlFormat.Value = 1;
}
}
rb.TextFrame.Characters().Text = "Hola prueba";
rb.ControlFormat.Value = 0;
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 workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example04", DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show end dialog
HostApplication.ShowFinishDialog(null, workbookFile);
}
public void RunExample()
{
bool isFailed = false;
string workbookFile = null;
Excel.Application excelApplication = null;
try
{
// start excel and turn off msg boxes
excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
excelApplication.Visible = false;
// create a utils instance, not need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
// add new global Code Module
VB.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 from NetOffice!\" & 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
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets[1];
sheet.Cells[2, 2].Value = "This workbook contains dynamic created VBA Moduls and Event Code";
sheet.Cells[5, 2].Value = "Open the VBA Editor to see the code";
sheet.Cells[8, 2].Value = "Do a double click to catch the BeforeDoubleClick Event from this Worksheet.";
// save the book
XlFileFormat fileFormat = GetFileFormat(excelApplication);
workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example07", Excel.Tools.DocumentFormat.Macros);
workBook.SaveAs(workbookFile, fileFormat);
}
catch (System.Runtime.InteropServices.COMException throwedException)
{
isFailed = true;
HostApplication.ShowErrorDialog("VBA Error", throwedException);
}
finally
{
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
if ((null != workbookFile) && (!isFailed))
HostApplication.ShowFinishDialog(null, workbookFile);
}
}
public void Dispose()
{
try
{
WorkBook.Close();
ExcelApp.Quit();
ExcelApp.Dispose();
}
catch { }
}
public Form1()
{
InitializeComponent();
Excel.Application app = new Excel.Application();
app.DisplayAlerts = false;
app.Workbooks.Add();
app.Quit();
app.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.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["ExcelApi"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["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["ExcelApi", "Range"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["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["ExcelApi", "Worksheet", "Range"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["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["ExcelApi", "Range", "_Default"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["ExcelApi", "Range", "_Default"].IntervalMS = 0;
Excel.Application application = new Excel.Application();
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 List<string> GetWorksheetsWithPrefix(string WorkbookPath, string prefix)
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook xlwkbook = excelApplication.Workbooks.Open(WorkbookPath);
List<string> wkshts = GetWorksheetsWithPrefix(xlwkbook, prefix);
excelApplication.Quit();
excelApplication.Dispose();
return wkshts;
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
// start excel and turn off msg boxes
application = new Excel.Application();
application.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = application.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.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);
return new TestResult(true, DateTime.Now.Subtract(startTime), "", null, "");
}
catch (Exception exception)
{
return new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, "");
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// create a utils instance, not need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// draw back color and perform the BorderAround method
workSheet.Range("$B2:$B5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen);
workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);
// draw back color and border the range explicitly
workSheet.Range("$D2:$D5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen);
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = utils.Color.ToDouble(Color.Black);
workSheet.Cells[1, 1].Value = "We have 2 simple shapes created.";
string workbookFile = null;
if (workSheet.EntityIsAvailable("ExportAsFixedFormat"))
{
// save the sheet as PDF
workbookFile = System.IO.Path.Combine(HostApplication.RootDirectory, "Example10.pdf");
workSheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, workbookFile, XlFixedFormatQuality.xlQualityStandard);
}
else
{
// we are sorry - pdf export is not supported in Excel 2003 or below
workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example10", Excel.Tools.DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
}
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
HostApplication.ShowFinishDialog(null, workbookFile);
}
static void Main(string[] args)
{
Excel.Application application = new Excel.Application();
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();
}
public void Run()
{
// start excel and add a new workbook
Excel.Application application = new Excel.Application();
application.Visible = false;
application.DisplayAlerts = false;
application.Workbooks.Add();
// GlobalModule contains the well known globals and is located in NetOffice.ExcelApi.GlobalHelperModules
GlobalModule.ActiveCell.Value = "ActiveCellValue";
// quit and dispose excel
application.Quit();
application.Dispose();
_hostApplication.ShowFinishDialog();
}
static void Main(string[] args)
{
Console.WriteLine("NOToolsTests.ConsoleMonitor2{0}Press any key to start.", Environment.NewLine);
Console.ReadKey();
Console.WriteLine("Running...");
NetOffice.DebugConsole.Default.EnableSharedOutput = true;
Excel.Application application = new Excel.Application();
application.Visible = false;
application.DisplayAlerts = false;
application.Workbooks.Add();
application.Quit();
application.Dispose();
Console.WriteLine("Press any key...");
Console.ReadKey();
}
public void Run()
{
// In some situations you want use NetOffice with an existing proxy, its typical for COM Addins.
// this examples show you how its possible
// we create a native Excel proxy
Type excelType = Type.GetTypeFromProgID("Excel.Application");
object excelProxy = Activator.CreateInstance(excelType);
// we create an Excel Application object with the proxy as parameter,
// excel is now under control by NetOffice
Excel.Application excelApplication = new Excel.Application(null, excelProxy);
excelApplication.Quit();
excelApplication.Dispose();
_hostApplication.ShowFinishDialog();
}
public void Run()
{
// start application
Excel.Application application = new Excel.Application();
application.DisplayAlerts = false;
// create new Workbook
Excel.Workbook book = application.Workbooks.Add();
Excel.Worksheet sheet = (Excel.Worksheet)book.Worksheets[1];
Excel.Range range = sheet.Cells[1, 1];
// Style is defined as Variant in Excel and represents as object in NetOffice
// You can cast them at runtime without problems
Excel.Style style = (Excel.Style)range.Style;
// variant types can be a scalar type at runtime
// another example way to use is
if (range.Style is string)
{
string myStyle = range.Style as string;
}
else if (range.Style is Excel.Style)
{
Excel.Style myStyle = (Excel.Style)range.Style;
}
// Name, Bold, Size are bool but defined as Variant and also converted to object
style.Font.Name = "Arial";
style.Font.Bold = true;
style.Font.Size = 14;
// Please note: the reason for the most variant definition is a more flexible value set.
// the Style property from Range returns always a Style object
// but if you have a new named style created with the name "myStyle" you can set range.Style = myNewStyleObject; or range.Style = "myStyle"
// this kind of flexibility is the primary reason for Variants in Office
// in any case, you dont lost the COM Proxy management from NetOffice for Variants.
// quit & dipose
application.Quit();
application.Dispose();
_hostApplication.ShowFinishDialog();
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// create a utils instance, not need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.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 workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example04", Excel.Tools.DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
HostApplication.ShowFinishDialog(null, workbookFile);
}
