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(); }