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 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);
}
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 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 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();
}
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 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 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 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();
}
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 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 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 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 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()
{
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();
}
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);
}
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 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 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)
{
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()
{
// 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();
}
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);
}
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.Application();
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 RunExample()
{
try { File.Delete(fileName); }
catch (Exception ex) { }
try
{
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
workBook.SaveAs(fileName);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
//_hostApplication.ShowFinishDialog(null, fileName);
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
//TODO Log Error Helper
MessageBox.Show("The Excel file could not be created. Please check that you have Microsoft Excel 2003 or Higher Installed", "IQTools", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public string CreateExcel()
{
string returnString = "";
try
{
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
//Add Extension Here
fileName += GetDefaultExtension(excelApplication);
try { File.Delete(fileName); }
catch (Exception ex) { returnString = ex.Message.ToString(); }
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
Excel.Range dataRange = PutSampleData(workSheet);
// save the book
workBook.SaveAs(fileName);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
//_hostApplication.ShowFinishDialog(null, fileName);
return("success");
}
catch (Exception ex)
{
returnString += ex.Message.ToString();
return(returnString);
}
}
static void Main(string[] args)
{
Console.WriteLine("NetOffice Release 1.3 Performance Test - 5000 Cells.");
Console.WriteLine("Write simple text.");
// start excel, and get a new sheet reference
Excel.Application excelApplication = CreateExcelApplication();
Excel.Worksheet sheet = excelApplication.Workbooks.Add().Worksheets.Add() as Excel.Worksheet;
// do test 10 times
List <TimeSpan> timeElapsedList = new List <TimeSpan>();
for (int i = 1; i <= 10; i++)
{
DateTime timeStart = DateTime.Now;
for (int y = 1; y <= 5000; y++)
{
string rangeAdress = "$A" + y.ToString();
sheet.get_Range(rangeAdress).Value = "value";
}
TimeSpan timeElapsed = DateTime.Now - timeStart;
// display info and dispose references
Console.WriteLine("Time Elapsed: {0}", timeElapsed);
timeElapsedList.Add(timeElapsed);
sheet.DisposeChildInstances();
}
// display info & log to file
TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test1-NetOffice.log");
Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine);
Console.Read();
// release & quit
excelApplication.Quit();
excelApplication.Dispose();
}
public void Run()
{
// start application
Excel.Application application = new Excel.Application();
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 of 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()
{
// 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 WriteXlsx(string filename, IEnumerable <IRowData> rows, ColumnDesc[] columns)
{
string tmpname = null;
try
{
tmpname = CreateTempFile(Path.GetTempPath(), ".xml");
using (var output = File.OpenWrite(tmpname))
{
var table = CreateXmlTree(rows, columns);
Transform(table, output, "xmlss");
}
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 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];
// 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);
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 = COMObject.Create <Excel.Application>();
excelApplication.DisplayAlerts = false;
// create a utils instance, no 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 call 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.";
// save the book - utils want build the filename for us
string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example01", DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show end dialog
HostApplication.ShowFinishDialog(null, workbookFile);
}
public void Dispose()
{
switch (_officeApp)
{
case "Excel":
_excelApplication.Dispose();
break;
case "Word":
_wordApplication.Dispose();
break;
case "Outlook":
_outlookApplication.Dispose();
break;
case "Power Point":
_powerpointApplication.Dispose();
break;
case "Access":
_accessApplication.Dispose();
break;
case "Project":
_accessApplication.Dispose();
break;
case "Visio":
_accessApplication.Dispose();
break;
default:
throw new ArgumentOutOfRangeException("officeApp");
}
}
public void Run()
{
// this example demonstrate the NetOffice low-level interface for latebinding calls
Excel.Application application = new Excel.Application();
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()
{
// 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 buttonStartExample_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
excelApplication.Visible = true;
// we register some events. note: the event trigger was called from excel, means another Thread
// you can get event notifys from various objects: Application or Workbook or Worksheet for example
excelApplication.NewWorkbookEvent += new Excel.Application_NewWorkbookEventHandler(excelApplication_NewWorkbook);
excelApplication.WorkbookBeforeCloseEvent += new Excel.Application_WorkbookBeforeCloseEventHandler(excelApplication_WorkbookBeforeClose);
excelApplication.WorkbookActivateEvent += new Excel.Application_WorkbookActivateEventHandler(excelApplication_WorkbookActivate);
excelApplication.WorkbookDeactivateEvent += new Excel.Application_WorkbookDeactivateEventHandler(excelApplication_WorkbookDeactivate);
excelApplication.SheetActivateEvent += new Excel.Application_SheetActivateEventHandler(_excelApplication_SheetActivateEvent);
excelApplication.SheetDeactivateEvent += new Excel.Application_SheetDeactivateEventHandler(_excelApplication_SheetDeactivateEvent);
// add a new workbook, add a sheet and close
Excel.Workbook workBook = excelApplication.Workbooks.Add();
workBook.Worksheets.Add();
workBook.Close();
excelApplication.Quit();
excelApplication.Dispose();
}
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.Application();
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();
}
private void Close(Excel.Application excelApp)
{
excelApp.ActiveWorkbook.Close();
excelApp.Quit();
excelApp.Dispose();
}
public ExportBuildingDataAssessorFileResponse ExportBuildingDataAssessorFile(ExportBuildingDataAssessorFileRequest request) {
ExportBuildingDataAssessorFileResponse response = null;
Excel.Application excelApplication = null;
try {
//Directory.CreateDirectory(Path.GetDirectoryName(request.DataFilePath));
response = new ExportBuildingDataAssessorFileResponse();
//write to excel
using (excelApplication = new Excel.Application()) {
excelApplication.DisplayAlerts = false;
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets[1];
workSheet.Name = string.Format("{0} {1}","BldgData Assessor", DateTime.Now.ToString("yyyyMMdd"));
int counter = 0;
foreach (BuildingDataAssessor buildingDataAssessor in request.BuildingDataAssessors) {
counter++;
if (counter.Equals(1)) {
//add headers
continue;
}
//TODO: add column fields from incoming file
//add details
workSheet.Cells[counter, 1].Value = "hello 33";
workSheet.Cells[counter, 2].Value = "world";
}
//save excel
workBook.SaveAs(request.SourceFilePath);
//quit excel
excelApplication.Quit();
}
//set result
if (File.Exists(request.SourceFilePath)) {
response.Result = new Result() {
IsSuccess = true,
Message = string.Format("{0}\n{1}",
"The data is exported successfully at the following path:",
request.SourceFilePath) };
}
else {
response.Result = new Result() {
IsSuccess = false,
Message = string.Format("{0}\n{1}",
"Fail in exporting data to:",
request.SourceFilePath) };
}
return response;
}
finally {
response = null;
if (excelApplication != null)
excelApplication.Dispose();
excelApplication = null;
}
}
private void ExecuteEvents(Timeline timeline, TimelineHandler handler)
{
try
{
foreach (var timelineEvent in handler.TimeLineEvents)
{
try
{
_log.Trace($"Excel event - {timelineEvent}");
WorkingHours.Is(handler);
if (timelineEvent.DelayBefore > 0)
{
Thread.Sleep(timelineEvent.DelayBefore);
}
if (timeline != null)
{
var processIds = ProcessManager.GetPids(ProcessManager.ProcessNames.Excel).ToList();
if (processIds.Count > timeline.TimeLineHandlers.Count(o => o.HandlerType == HandlerType.Excel))
{
return;
}
}
// start excel and turn off msg boxes
var excelApplication = new Excel.Application
{
DisplayAlerts = false,
Visible = true
};
try
{
excelApplication.WindowState = XlWindowState.xlMinimized;
foreach (var item in excelApplication.Workbooks)
{
item.Windows[1].WindowState = XlWindowState.xlMinimized;
}
}
catch (Exception e)
{
_log.Trace($"Could not minimize: {e}");
}
// create a utils instance, not need for but helpful to keep the lines of code low
var utils = new CommonUtils(excelApplication);
_log.Trace("Excel adding workbook");
// add a new workbook
var workBook = excelApplication.Workbooks.Add();
_log.Trace("Excel adding worksheet");
var 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[(Excel.Enums.XlBordersIndex)XlBordersIndex.xlInsideHorizontal]
.LineStyle = XlLineStyle.xlDouble;
workSheet.Range("$D2:$D5")
.Borders[(Excel.Enums.XlBordersIndex)XlBordersIndex.xlInsideHorizontal]
.Weight = 4;
workSheet.Range("$D2:$D5")
.Borders[(Excel.Enums.XlBordersIndex)XlBordersIndex.xlInsideHorizontal]
.Color = utils.Color.ToDouble(Color.Black);
var writeSleep = ProcessManager.Jitter(100);
Thread.Sleep(writeSleep);
workSheet.Cells[1, 1].Value = "We have 2 simple shapes created.";
var rand = RandomFilename.Generate();
var dir = timelineEvent.CommandArgs[0].ToString();
if (dir.Contains("%"))
{
dir = Environment.ExpandEnvironmentVariables(dir);
}
if (Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
var path = $"{dir}\\{rand}.xlsx";
//if directory does not exist, create!
_log.Trace($"Checking directory at {path}");
var f = new FileInfo(path).Directory;
if (f == null)
{
_log.Trace($"Directory does not exist, creating directory at {f.FullName}");
Directory.CreateDirectory(f.FullName);
}
try
{
if (File.Exists(path))
{
File.Delete(path);
}
}
catch (Exception e)
{
_log.Error($"Excel file delete exception: {e}");
}
_log.Trace($"Excel saving to path - {path}");
workBook.SaveAs(path);
FileListing.Add(path);
Report(handler.HandlerType.ToString(), timelineEvent.Command,
timelineEvent.CommandArgs[0].ToString());
if (timelineEvent.DelayAfter > 0)
{
//sleep and leave the app open
_log.Trace($"Sleep after for {timelineEvent.DelayAfter}");
Thread.Sleep(timelineEvent.DelayAfter - writeSleep);
}
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
excelApplication = null;
workBook = null;
workSheet = null;
try
{
Marshal.ReleaseComObject(excelApplication);
}
catch { }
try
{
Marshal.FinalReleaseComObject(excelApplication);
}
catch { }
GC.Collect();
}
catch (Exception e)
{
_log.Error($"Excel handler exception: {e}");
}
finally
{
Thread.Sleep(5000);
}
}
}
catch (Exception e)
{
_log.Error(e);
}
finally
{
KillApp();
_log.Trace("Excel closing...");
}
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = COMObject.Create <Excel.Application>();
excelApplication.DisplayAlerts = false;
// create a utils instance, no 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];
// 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 = XlHAlign.xlHAlignLeft;
workSheet.Range("B7").Value = "xlHAlignCenter";
workSheet.Range("B7").HorizontalAlignment = XlHAlign.xlHAlignCenter;
workSheet.Range("C7").Value = "xlHAlignRight";
workSheet.Range("C7").HorizontalAlignment = XlHAlign.xlHAlignRight;
workSheet.Range("D7").Value = "xlHAlignJustify";
workSheet.Range("D7").HorizontalAlignment = XlHAlign.xlHAlignJustify;
workSheet.Range("E7").Value = "xlHAlignDistributed";
workSheet.Range("E7").HorizontalAlignment = XlHAlign.xlHAlignDistributed;
// VerticalAlignment
workSheet.Range("A9").Value = "xlVAlignTop";
workSheet.Range("A9").VerticalAlignment = XlVAlign.xlVAlignTop;
workSheet.Range("B9").Value = "xlVAlignCenter";
workSheet.Range("B9").VerticalAlignment = XlVAlign.xlVAlignCenter;
workSheet.Range("C9").Value = "xlVAlignBottom";
workSheet.Range("C9").VerticalAlignment = XlVAlign.xlVAlignBottom;
workSheet.Range("D9").Value = "xlVAlignDistributed";
workSheet.Range("D9").VerticalAlignment = XlVAlign.xlVAlignDistributed;
workSheet.Range("E9").Value = "xlVAlignJustify";
workSheet.Range("E9").VerticalAlignment = XlVAlign.xlVAlignJustify;
// setup rows and columns
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].ColumnWidth = 25;
workSheet.Columns[3].ColumnWidth = 25;
workSheet.Columns[4].ColumnWidth = 25;
workSheet.Columns[5].ColumnWidth = 25;
workSheet.Rows[9].RowHeight = 25;
// save the book - utils want build the filename for us
string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example02", DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show end dialog
HostApplication.ShowFinishDialog(null, workbookFile);
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
// start excel and turn off msg boxes
application = COMObject.Create <Excel.Application>(COMObjectCreateOptions.CreateNewCore);
application.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = application.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.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 = XlHAlign.xlHAlignLeft;
workSheet.Range("B7").Value = "xlHAlignCenter";
workSheet.Range("B7").HorizontalAlignment = XlHAlign.xlHAlignCenter;
workSheet.Range("C7").Value = "xlHAlignRight";
workSheet.Range("C7").HorizontalAlignment = XlHAlign.xlHAlignRight;
workSheet.Range("D7").Value = "xlHAlignJustify";
workSheet.Range("D7").HorizontalAlignment = XlHAlign.xlHAlignJustify;
workSheet.Range("E7").Value = "xlHAlignDistributed";
workSheet.Range("E7").HorizontalAlignment = XlHAlign.xlHAlignDistributed;
// VerticalAlignment
workSheet.Range("A9").Value = "xlVAlignTop";
workSheet.Range("A9").VerticalAlignment = XlVAlign.xlVAlignTop;
workSheet.Range("B9").Value = "xlVAlignCenter";
workSheet.Range("B9").VerticalAlignment = XlVAlign.xlVAlignCenter;
workSheet.Range("C9").Value = "xlVAlignBottom";
workSheet.Range("C9").VerticalAlignment = XlVAlign.xlVAlignBottom;
workSheet.Range("D9").Value = "xlVAlignDistributed";
workSheet.Range("D9").VerticalAlignment = XlVAlign.xlVAlignDistributed;
workSheet.Range("E9").Value = "xlVAlignJustify";
workSheet.Range("E9").VerticalAlignment = XlVAlign.xlVAlignJustify;
// setup rows and columns
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].ColumnWidth = 25;
workSheet.Columns[3].ColumnWidth = 25;
workSheet.Columns[4].ColumnWidth = 25;
workSheet.Columns[5].ColumnWidth = 25;
workSheet.Rows[9].RowHeight = 25;
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];
// 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 = XlHAlign.xlHAlignLeft;
workSheet.Range("B7").Value = "xlHAlignCenter";
workSheet.Range("B7").HorizontalAlignment = XlHAlign.xlHAlignCenter;
workSheet.Range("C7").Value = "xlHAlignRight";
workSheet.Range("C7").HorizontalAlignment = XlHAlign.xlHAlignRight;
workSheet.Range("D7").Value = "xlHAlignJustify";
workSheet.Range("D7").HorizontalAlignment = XlHAlign.xlHAlignJustify;
workSheet.Range("E7").Value = "xlHAlignDistributed";
workSheet.Range("E7").HorizontalAlignment = XlHAlign.xlHAlignDistributed;
// VerticalAlignment
workSheet.Range("A9").Value = "xlVAlignTop";
workSheet.Range("A9").VerticalAlignment = XlVAlign.xlVAlignTop;
workSheet.Range("B9").Value = "xlVAlignCenter";
workSheet.Range("B9").VerticalAlignment = XlVAlign.xlVAlignCenter;
workSheet.Range("C9").Value = "xlVAlignBottom";
workSheet.Range("C9").VerticalAlignment = XlVAlign.xlVAlignBottom;
workSheet.Range("D9").Value = "xlVAlignDistributed";
workSheet.Range("D9").VerticalAlignment = XlVAlign.xlVAlignDistributed;
workSheet.Range("E9").Value = "xlVAlignJustify";
workSheet.Range("E9").VerticalAlignment = XlVAlign.xlVAlignJustify;
// setup rows and columns
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].ColumnWidth = 25;
workSheet.Columns[3].ColumnWidth = 25;
workSheet.Columns[4].ColumnWidth = 25;
workSheet.Columns[5].ColumnWidth = 25;
workSheet.Rows[9].RowHeight = 25;
// save the book
string fileExtension = GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example02{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);
}
private void btnSave_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Do you want to exit and save this test?", "Save Test", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.No)
{
return;
}
this.ucTimer.Stop();
ShowLoading();
try
{
List <bool> markQuestions = new List <bool>();
switch (this.Test.OfficeApp)
{
case "Word":
Word.Application wordApp = this.Application as Word.Application;
wordApp.Quit(Word.Enums.WdSaveOptions.wdSaveChanges);
wordApp.Dispose();
break;
case "Excel":
Excel.Application excelApp = this.Application as Excel.Application;
excelApp.ActiveWorkbook.Close(true);
excelApp.Quit();
excelApp.Dispose();
break;
case "PowerPoint":
break;
}
int indexQuestion = 1;
foreach (var group in this.Test.Questions)
{
foreach (var question in group.Value)
{
if (((this.tablePanelQuestionTitle.Controls["labelQuestion" + indexQuestion.ToString()]) as Label).ForeColor == Color.Blue)
{
markQuestions.Add(true);
}
else
{
markQuestions.Add(false);
}
indexQuestion++;
}
}
this.Task.IsCompleted = false;
this.Task.UsedTime = this.ucTimer.Current;
this.Task.MarkCompletedQuestions.Clear();
this.Task.MarkCompletedQuestions.Add(markQuestions);
Repository.updateTask(this.Task);
CloseLoading();
this.Close();
}
catch (Exception ex)
{
log.Error(ex.Message);
log.Error("Save - " + Test.ToString());
}
}
public void RunExample()
{
// start excel and turn Application 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];
// the given thread culture in all NetOffice calls are stored in NetOffice.Settings.
// you can change the culture of course. Default is en-us.
CultureInfo cultureInfo = NetOffice.Settings.Default.ThreadCulture;
string Pattern1 = string.Format("0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator);
string Pattern2 = string.Format("#{1}##0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator, cultureInfo.NumberFormat.CurrencyGroupSeparator);
workSheet.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 = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.FullDateTimePattern;
workSheet.Range("C10").Value = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.LongDatePattern;
workSheet.Range("D10").Value = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.ShortDatePattern;
workSheet.Range("E10").Value = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.LongTimePattern;
workSheet.Range("F10").Value = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.ShortTimePattern;
// DateTime
DateTime dateTimeValue = DateTime.Now;
workSheet.Range("B11").Value = dateTimeValue;
workSheet.Range("B11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.FullDateTimePattern;
workSheet.Range("C11").Value = dateTimeValue;
workSheet.Range("C11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.LongDatePattern;
workSheet.Range("D11").Value = dateTimeValue;
workSheet.Range("D11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.ShortDatePattern;
workSheet.Range("E11").Value = dateTimeValue;
workSheet.Range("E11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.LongTimePattern;
workSheet.Range("F11").Value = dateTimeValue;
workSheet.Range("F11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.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 workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example03", 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 IEnumerable <ImportDataRowModel> Import(ImportTemplate template, string fileName)
{
NetOffice.ExcelApi.Application _app;
NetOffice.ExcelApi.Workbook _workbook;
NetOffice.ExcelApi.Worksheet _worksheet;
//Excel.Range _range;
// start excel and turn off msg boxes
try
{
_app = new NetOffice.ExcelApi.Application();
_app.DisplayAlerts = false;
_app.Visible = false;
}
catch (Exception exc)
{
throw new Exception("Не удалось подключиться к Excell");
}
// OpenFile
try
{
_workbook = _app.Workbooks.Open(fileName);
}
catch (Exception exc)
{
_app.Quit();
_app.Dispose();
throw new FileNotFoundException(fileName);
}
_worksheet = (NetOffice.ExcelApi.Worksheet)_workbook.Worksheets.FirstOrDefault();
if (_worksheet == null)
{
_app.Quit();
_app.Dispose();
throw new Exception("Книга не найдена");
}
//количество ошибок чтения
int errorCount = 1;
//текущее количество ошибок
int error = 0;
//количество строк для обработки
int countRow = 500;
int endRow;
countRow = template.EndRowNumber - template.StartRowNumber;
endRow = template.EndRowNumber;
string startColumn = GetMinColumn(template);
string endColumn = GetMaxColumn(template);
object[,] values = (object[, ])_worksheet.get_Range(startColumn + template.StartRowNumber, endColumn + endRow).Value2;
int shiftColumn = 0;
if (StringToUIntConvert(startColumn) != 1)
{
//расчитываем сдвиг по горизонтали
shiftColumn = StringToUIntConvert(startColumn) - 1;
}
int articleColNum = StringToUIntConvert(template.ArticleColName) - shiftColumn;
int costBaseColNum = StringToUIntConvert(template.CostBaseColName) - shiftColumn;
int costNativeColNum = StringToUIntConvert(template.CostNativeColName) - shiftColumn;
int descriptionColNum = StringToUIntConvert(template.DescriptionColName) - shiftColumn;
int manufColNum = StringToUIntConvert(template.ManufColName) - shiftColumn;
int modelColNum = StringToUIntConvert(template.ModelColName) - shiftColumn;
int oemColNum = StringToUIntConvert(template.OEMColName) - shiftColumn;
int quantityColNum = StringToUIntConvert(template.QuantityColName) - shiftColumn;
string brandStr = "";
string modelStr = "";
// считываем
List <ImportDataRowModel> rowsFromExcel = new List <ImportDataRowModel>();
for (int i = 1; i <= countRow + 1; i++)
{
if (error > errorCount)
{
break;
}
try
{
int outLevel = Convert.ToInt32(_worksheet.Rows[i + template.StartRowNumber - 1].OutlineLevel);
if (outLevel == 1)
{
brandStr = values[i, modelColNum] != null?Convert.ToString(values[i, modelColNum]) : "";
brandStr = brandStr.TrimEnd(' ').TrimStart(' ');
}
else if (outLevel == 2)
{
modelStr = values[i, modelColNum] != null?Convert.ToString(values[i, modelColNum]) : "";
modelStr = modelStr.TrimEnd(' ').TrimStart(' ');
}
else if (outLevel > 2)
{
string subModelStr = values[i, modelColNum] != null
? Convert.ToString(values[i, modelColNum])
: "";
subModelStr = subModelStr.TrimEnd(' ').TrimStart(' ');
string articleStr = values[i, articleColNum] != null
? Convert.ToString(values[i, articleColNum])
: "";
articleStr = articleStr.TrimEnd(' ').TrimStart(' ');
//если Article не найден то пропускаем строку
if (string.IsNullOrWhiteSpace(articleStr))
{
continue;
}
string priceBaseStr = values[i, costBaseColNum] != null
? Convert.ToString(values[i, costBaseColNum])
: "";
decimal priceBase = ParsingPrice(priceBaseStr);
string priceNativeStr = values[i, costNativeColNum] != null
? Convert.ToString(values[i, costNativeColNum])
: "";
decimal priceNative = ParsingPrice(priceNativeStr);
string descriptionStr = values[i, descriptionColNum] != null
? Convert.ToString(values[i, descriptionColNum])
: "";
descriptionStr = descriptionStr.TrimEnd(' ').TrimStart(' ');
if (descriptionStr == "-2146826246")
{
descriptionStr = "";
}
string manufStr = values[i, manufColNum] != null?Convert.ToString(values[i, manufColNum]) : "";
manufStr = manufStr.TrimEnd(' ').TrimStart(' ');
if (manufStr == "-2146826246")
{
manufStr = "";
}
string oemStr = values[i, oemColNum] != null?Convert.ToString(values[i, oemColNum]) : "";
List <string> oemList = oemParsingToList(oemStr, out oemStr);
string qtyStr = values[i, oemColNum] != null?Convert.ToString(values[i, quantityColNum]) : "";
bool qty = ParsingQty(qtyStr);
rowsFromExcel.Add(new ImportDataRowModel()
{
Brand = brandStr,
Article = articleStr,
PriceBase = priceBase,
PriceNative = priceNative,
Description = descriptionStr,
Manufacturer = manufStr,
Quantity = qty,
Model = modelStr,
SubModel = subModelStr,
OEMs = oemStr,
OEMList = oemList
});
}
}
catch (Exception)
{
error += 1; //увеличиваем счетчик ошибок
continue;
}
error = 0; //если чтение прошло удачно обнуляем
}
//if (error > errorCount)
//{
// throw new Exception("Не верный формат данных");
//}
_workbook.Close();
_app.Quit();
_app.Dispose();
GC.Collect();
return(rowsFromExcel);
}
public WebParserResult ParseWebSite(WebParserConfig parserConfig, WebParserParams parserParam)
{
string fileNameAddin = this.GetParserPath();
string parserConfigName = parserConfig.AddinConfigName;
WebParserResult parserResult = new WebParserResult()
{
ParserStatus = "Ok",
ParserError = ""
};
Excel.Application xlApp = null;
Excel.Workbook xlWb1 = null;
Excel.Workbook xlWb2 = null;
Excel.Worksheet xlSht = null;
try
{
// Запустить Application
xlApp = new Excel.Application();
// Открыть надстройку
xlWb1 = xlApp.Workbooks.Open(fileNameAddin);
// Создать лист для ввода данных
// Excel.Workbook xlWb2 = xlApp.Workbooks.Open(fileNameExcelWorkbook);
xlWb2 = xlApp.Workbooks.Add();
//Excel.Worksheet xlSht = xlWb2.Sheets["Лист1"]; //имя листа в файле
xlSht = xlWb2.Sheets[1] as Excel.Worksheet; //имя листа в файле
//xlApp.Visible = true;
//xlApp.DisplayAlerts = false;
int startRowNumber = parserConfig.StartRowNumber;
// Вывод заголовка
if (startRowNumber > 1)
{
xlSht.Cells[1, parserConfig.DealNumberColumn].Value = "Номер документа";
xlSht.Cells[1, parserConfig.IsTrackColumn].Value = "Обрабатывать";
}
// Заполнение исходных данных на листе
xlSht.Cells[startRowNumber, parserConfig.DealNumberColumn].Value = parserParam.DocumentNumber;
xlSht.Cells[startRowNumber, parserConfig.IsTrackColumn].Value = "Да";
// Запуск парсера
var res = xlApp.Run(@"StartParser", parserConfigName);
if (!String.IsNullOrEmpty((string)res))
{
throw new Exception((string)res);
}
object row1 = xlSht.Cells[startRowNumber, parserConfig.ResultNumberColumn].Value;
if (row1 == null)
{
parserResult.ParserStatus = "Not found";
}
else
{
string col2HyperlinkAddress = xlSht.Cells[startRowNumber, parserConfig.DealNumberHyperlinkColumn].Hyperlinks[1].Address;
string col3 = xlSht.Cells[startRowNumber, parserConfig.DocumentPdfFolderNameColumn].Value.ToString();
string col4 = xlSht.Cells[startRowNumber, parserConfig.DocumentPdfUrlColumn].Value.ToString();
DateTime col5 = (DateTime)xlSht.Cells[startRowNumber, parserConfig.LastDealDateColumn].Value;
parserResult.CardUrl = col2HyperlinkAddress;
parserResult.LastDealDate = col5;
parserResult.DocumentPdfUrl = col4;
parserResult.DocumentPdfFolderName = col3;
parserResult.HasAttachment = string.IsNullOrEmpty(col4) ? false : true;
parserResult.DocumentPfdPath = GetDocumentFullPath(parserConfig, parserResult, fileNameAddin);
}
}
catch (Exception exc)
{
parserResult.ParserStatus = "Error";
parserResult.ParserError = exc.Message;
}
finally
{
if (xlWb2 != null)
{
xlWb2.Saved = true;
}
xlApp.DisposeChildInstances();
if (xlApp != null)
{
xlApp.Quit(); //закрываем Excel
xlApp.Dispose();
}
}
return(parserResult);
}
private void buttonStartExample_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
excelApplication.Visible = true;
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// show selected window and display user clicks ok or cancel
bool returnValue = false;
RadioButton radioSelectButton = GetSelectedRadioButton();
switch (radioSelectButton.Text)
{
case "xlDialogAddinManager":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogAddinManager].Show();
break;
case "xlDialogFont":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogFont].Show();
break;
case "xlDialogEditColor":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogEditColor].Show();
break;
case "xlDialogGallery3dBar":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogGallery3dBar].Show();
break;
case "xlDialogSearch":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogSearch].Show();
break;
case "xlDialogPrinterSetup":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogPrinterSetup].Show();
break;
case "xlDialogFormatNumber":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogFormatNumber].Show();
break;
case "xlDialogApplyStyle":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogApplyStyle].Show();
break;
default:
throw (new Exception("Unkown dialog selected."));
}
string message = string.Format("The dialog returns {0}.", returnValue);
MessageBox.Show(this, message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
}
public bool ExportProductList(IEnumerable <Product> list, ProductExportCriteria criteria)
{
// Abbrechen, wenn es die angegebene Datei schon gibt. if
// (File.Exists(criteria.ExcelFullName)) File.Delete(criteria.ExcelFilename);
int dataStartRow = 16;
var dateCell = "$G$14";
var lastColumn = 65 + criteria.Feldliste.Count;
var lastRow = list.Count();
var app = new Excel.Application();
var book = app.Workbooks.Add();
var sheet = (Excel.Worksheet)book.ActiveSheet;
sheet.Name = $"Preisliste {DateTime.Today.ToShortDateString()}";
var artikelZusatzColumnAddress = string.Empty;
double artikelZusatzColumnWidth = 65;
var cellAddress = string.Empty;
if (list != null && list.Count() > 0)
{
// CPM Logo einfügen
var pic = sheet.Shapes.AddPicture(@"\\Cpm-dc\sage_ncl\catalist\graphics\cpm_kopf.jpg", NetOffice.OfficeApi.Enums.MsoTriState.msoFalse, NetOffice.OfficeApi.Enums.MsoTriState.msoCTrue, 20, 20, -1.0f, -1.0f);
// Kundenadresse und Datum
var range = sheet.Range("$B$10:$D$14");
range.Merge();
range.VerticalAlignment = Excel.Enums.XlVAlign.xlVAlignTop;
range.WrapText = true;
range.Value = criteria.Kundenadresse;
range = sheet.Range(dateCell);
range.Value = $"{DateTime.Now:d}";
// Überschrift
//cell = sheet.Range("B2");
//cell.Value = $"Artikelpreisliste für Firma {criteria.Matchcode} {criteria.Kundennummer}, Stand: {DateTime.Now.ToShortDateString()}";
//cell.Font.Size = 12;
//cell.Font.Bold = true;
//var lastCol = 66 + criteria.Feldliste.Count - 1;
//var mergeThis = sheet.Range($"B2:{(char)lastCol}2");
//mergeThis.Merge();
// Zeilenüberschriften
foreach (var item in criteria.Feldliste)
{
cellAddress = $"{item.Column}{dataStartRow}";
range = sheet.Range(cellAddress);
range.NumberFormat = item.NumberFormat;
range.Value = item.Header;
if (item.Header == "Artikelzusatz")
{
artikelZusatzColumnAddress = cellAddress;
artikelZusatzColumnWidth = item.ColumnWidth;
}
}
// Tabelleninhalt
int rowCount = dataStartRow + 1;
foreach (var artikel in list)
{
if (!artikel.SelectedFlag || (criteria.NurRabattierteFlag && artikel.RabattProzent == 0 && artikel.StaffelpreisInfo == "-"))
{
continue;
}
foreach (var item in criteria.Feldliste)
{
cellAddress = $"{item.Column}{rowCount}";
range = sheet.Range(cellAddress);
range.NumberFormat = item.NumberFormat;
range.Value = artikel.GetType().GetProperty(item.Fieldname).GetValue(artikel, null);
range.VerticalAlignment = Excel.Enums.XlVAlign.xlVAlignTop;
if (item.Fieldname == "Artikelzusatz")
{
range.WrapText = true;
}
}
range.RowHeight = 40;
rowCount += 1;
}
// Die Breite der ersten Spalte (A) auf 20pt (2.14) einstellen
range = sheet.Range("$A:$A");
range.ColumnWidth = 2.14;
// Als Tabelle formatieren
range = sheet.Range($"$B${dataStartRow}:${(char)lastColumn}{rowCount - 1}");
sheet.ListObjects.Add(1, range, Missing.Value, 1).Name = "Preistabelle";
var table = sheet.ListObjects["Preistabelle"];
foreach (var item in table.ListColumns)
{
if (item.DataBodyRange != null)
{
item.DataBodyRange.Font.Size = criteria.DataFontSize;
}
}
table.HeaderRowRange.Font.Size = criteria.HeaderFontSize;
table.HeaderRowRange.Font.Bold = true;
switch (criteria.Tabellenfarbe)
{
case ProductExportCriteria.TableStyles.LightBlue:
table.TableStyle = "TableStyleMedium2";
break;
case ProductExportCriteria.TableStyles.Orange:
table.TableStyle = "TableStyleMedium3";
break;
case ProductExportCriteria.TableStyles.Gray:
table.TableStyle = "TableStyleMedium4";
break;
case ProductExportCriteria.TableStyles.Yellow:
table.TableStyle = "TableStyleMedium5";
break;
case ProductExportCriteria.TableStyles.DarkBlue:
table.TableStyle = "TableStyleMedium6";
break;
case ProductExportCriteria.TableStyles.Green:
table.TableStyle = "TableStyleMedium7";
break;
default:
table.TableStyle = "TableStyleMedium3";
break;
}
// Automatische Spaltenbreiten einstellen
var cols = $"A:{(char)lastColumn}";
var autoFitRange = sheet.UsedRange.Columns[cols];
autoFitRange.EntireColumn.AutoFit();
var col = artikelZusatzColumnAddress.Substring(0, 1);
var artikelZusatzColumn = sheet.Range($"${col}:${col}");
artikelZusatzColumn.ColumnWidth = 65;
// Nach der ersten Spalte sortieren
var sortRange = sheet.Range($"B{dataStartRow}");
table.Sort.SortFields.Add(sortRange);
table.Sort.Header = Excel.Enums.XlYesNoGuess.xlYes;
table.Sort.MatchCase = false;
table.Sort.Orientation = Excel.Enums.XlSortOrientation.xlSortColumns;
table.Sort.SortMethod = Excel.Enums.XlSortMethod.xlPinYin;
table.Sort.Apply();
// Fenster ab Zeile 5 fixieren
sheet.Range($"A{dataStartRow + 1}").Select();
app.ActiveWindow.FreezePanes = true;
app.ActiveWindow.DisplayGridlines = false;
// Fußzeilen einfügen
range = sheet.Range($"$B{rowCount + 3}:$G{rowCount + 22}");
range.Merge();
range.WrapText = true;
range.VerticalAlignment = Excel.Enums.XlVAlign.xlVAlignTop;
var sb = new StringBuilder();
sb.AppendLine($"Alle angegebenen Preise verstehen sich als Nettopreise zuzüglich der gesetzlich gültigen Mehrwertsteuer.");
sb.AppendLine();
sb.AppendLine("Liefer- und Zahlungsbedingungen:");
sb.AppendLine("----------------------------------------------");
sb.AppendLine(criteria.Frachtkosten);
sb.AppendLine("Kosten für Express-, Eilgut- und Kuriersendungen gehen zu Lasten des Empfängers.");
sb.AppendLine();
sb.AppendLine(criteria.Zahlungsbedingungen);
sb.AppendLine("----------------------------------------------------------------------------------------------------------");
sb.AppendLine();
sb.AppendLine("Zwischenzeitliche Änderungen und Irrtümer vorbehalten.");
sb.AppendLine("Es gelten unsere Allgemeinen Geschäfts- und Lieferbedingungen.");
sb.AppendLine();
sb.AppendLine("Wir möchten Sie darauf hinweisen, dass diese Sonderpreisregelung einen fristgerechten Ausgleich unserer");
sb.AppendLine("Rechnungen voraussetzt und jährlich erneut bestätigt werden muss.");
sb.AppendLine();
sb.AppendLine("Für alle hier nicht aufgeführten Artikel gelten unsere aktuellen Listenpreise, sofern nicht aufgrund eines");
sb.AppendLine("vorangegangenen Angebots Sonderpreise vereinbart wurden.");
sb.AppendLine();
sb.AppendLine("Wir freuen uns auf eine angenehme Zusammenarbeit und verbleiben");
sb.AppendLine();
sb.AppendLine("mit freundlichen Grüßen");
sb.AppendLine();
sb.AppendLine("Cut & Print Media GmbH & Co. KG");
range.Value = sb.ToString();
book.SaveAs(criteria.ExcelFullName);
book.Dispose();
app.Quit();
app.Dispose();
}
return(true);
}
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;
application.Visible = true;
application.NewWorkbookEvent += new Excel.Application_NewWorkbookEventHandler(ExcelApplication_NewWorkbook);
application.WorkbookBeforeCloseEvent += new Excel.Application_WorkbookBeforeCloseEventHandler(ExcelApplication_WorkbookBeforeClose);
application.WorkbookActivateEvent += new Excel.Application_WorkbookActivateEventHandler(ExcelApplication_WorkbookActivate);
application.WorkbookDeactivateEvent += new Excel.Application_WorkbookDeactivateEventHandler(ExcelApplication_WorkbookDeactivate);
application.SheetActivateEvent += new Excel.Application_SheetActivateEventHandler(ExcelApplication_SheetActivateEvent);
application.SheetDeactivateEvent += new Excel.Application_SheetDeactivateEventHandler(ExcelApplication_SheetDeactivateEvent);
// add a new workbook add a sheet and close
Excel.Workbook workBook = application.Workbooks.Add();
workBook.Worksheets.Add();
workBook.Close();
if (_newWorkbookEvent && _workbookBeforeCloseEvent && _sheetActivateEvent && _sheetDeactivateEvent && _workbookActivateEvent && _workbookDeactivateEvent)
{
return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, ""));
}
else
{
string errorMessage = "";
if (!_newWorkbookEvent)
{
errorMessage += "NewWorkbookEvent failed ";
}
if (!_workbookBeforeCloseEvent)
{
errorMessage += "WorkbookBeforeCloseEvent failed ";
}
if (!_sheetActivateEvent)
{
errorMessage += "WorkbookActivateEvent failed ";
}
if (!_sheetDeactivateEvent)
{
errorMessage += "WorkbookDeactivateEvent failed ";
}
if (!_workbookActivateEvent)
{
errorMessage += "SheetActivateEvent failed ";
}
if (!_workbookDeactivateEvent)
{
errorMessage += "SheetDeactivateEvent failed ";
}
return(new TestResult(true, DateTime.Now.Subtract(startTime), errorMessage, null, ""));
}
}
catch (Exception exception)
{
return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, ""));
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
Bitmap iconBitmap = new Bitmap(System.Reflection.Assembly.GetAssembly(this.GetType()).GetManifestResourceStream("ExcelTestsCSharp.Test08.bmp"));
application = COMObject.Create <Excel.Application>(COMObjectCreateOptions.CreateNewCore);
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet;
// add a commandbar popup
Office.CommandBarPopup commandBarPopup = (Office.CommandBarPopup)application.CommandBars["Worksheet Menu Bar"].Controls.Add(MsoControlType.msoControlPopup, System.Type.Missing, System.Type.Missing, System.Type.Missing, true);
commandBarPopup.Caption = "commandBarPopup";
#region CommandBarButton
// add a button to the popup
Office.CommandBarButton commandBarBtn = (Office.CommandBarButton)commandBarPopup.Controls.Add(MsoControlType.msoControlButton, System.Type.Missing, System.Type.Missing, System.Type.Missing, true);
commandBarBtn.Style = MsoButtonStyle.msoButtonIconAndCaption;
commandBarBtn.Caption = "commandBarButton";
Clipboard.SetDataObject(iconBitmap);
commandBarBtn.PasteFace();
commandBarBtn.ClickEvent += new Office.CommandBarButton_ClickEventHandler(commandBarBtn_Click);
#endregion
#region Create a new toolbar
// add a new toolbar
Office.CommandBar commandBar = application.CommandBars.Add("MyCommandBar", MsoBarPosition.msoBarTop, false, true);
commandBar.Visible = true;
// add a button to the toolbar
commandBarBtn = (Office.CommandBarButton)commandBar.Controls.Add(MsoControlType.msoControlButton, System.Type.Missing, System.Type.Missing, System.Type.Missing, true);
commandBarBtn.Style = MsoButtonStyle.msoButtonIconAndCaption;
commandBarBtn.Caption = "commandBarButton";
commandBarBtn.FaceId = 3;
commandBarBtn.ClickEvent += new Office.CommandBarButton_ClickEventHandler(commandBarBtn_Click);
// add a dropdown box to the toolbar
commandBarPopup = (Office.CommandBarPopup)commandBar.Controls.Add(MsoControlType.msoControlPopup, System.Type.Missing, System.Type.Missing, System.Type.Missing, true);
commandBarPopup.Caption = "commandBarPopup";
// add a button to the popup, we use an own icon for the button
commandBarBtn = (Office.CommandBarButton)commandBarPopup.Controls.Add(MsoControlType.msoControlButton, System.Type.Missing, System.Type.Missing, System.Type.Missing, true);
commandBarBtn.Style = MsoButtonStyle.msoButtonIconAndCaption;
commandBarBtn.Caption = "commandBarButton";
Clipboard.SetDataObject(iconBitmap);
commandBarBtn.PasteFace();
commandBarBtn.ClickEvent += new Office.CommandBarButton_ClickEventHandler(commandBarBtn_Click);
#endregion
#region Create a new ContextMenu
// add a commandbar popup
commandBarPopup = (Office.CommandBarPopup)application.CommandBars["Cell"].Controls.Add(MsoControlType.msoControlPopup, System.Type.Missing, System.Type.Missing, System.Type.Missing, true);
commandBarPopup.Caption = "commandBarPopup";
// add a button to the popup
commandBarBtn = (Office.CommandBarButton)commandBarPopup.Controls.Add(MsoControlType.msoControlButton);
commandBarBtn.Style = MsoButtonStyle.msoButtonIconAndCaption;
commandBarBtn.Caption = "commandBarButton";
commandBarBtn.FaceId = 9;
commandBarBtn.ClickEvent += new Office.CommandBarButton_ClickEventHandler(commandBarBtn_Click);
#endregion
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 void buttonStartExample_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
Excel.Application excelApplication = COMObject.Create <Excel.Application>();
excelApplication.DisplayAlerts = false;
excelApplication.Visible = true;
excelApplication.Settings.ExceptionMessageBehavior = NetOffice.ExceptionMessageHandling.DiagnosticsAndInnerMessage;
excelApplication.Settings.ExceptionDiagnosticsMessage = "Failed to proceed {CallInstance}={CallType}=>{Name}.{NlApplicationVersions}{NewLine}";
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// show selected window and display user clicks ok or cancel
bool returnValue = false;
RadioButton radioSelectButton = GetSelectedRadioButton();
switch (radioSelectButton.Text)
{
case "xlDialogAddinManager":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogAddinManager].Show();
break;
case "xlDialogFont":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogFont].Show();
break;
case "xlDialogEditColor":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogEditColor].Show();
break;
case "xlDialogGallery3dBar":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogGallery3dBar].Show();
break;
case "xlDialogSearch":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogSearch].Show();
break;
case "xlDialogPrinterSetup":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogPrinterSetup].Show();
break;
case "xlDialogFormatNumber":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogFormatNumber].Show();
break;
case "xlDialogApplyStyle":
returnValue = excelApplication.Dialogs[XlBuiltInDialog.xlDialogApplyStyle].Show();
break;
default:
throw (new ArgumentOutOfRangeException("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();
}
