public void RunExample() { // start excel and turn off msg boxes Excel.Application excelApplication = new Excel.Application(); excelApplication.DisplayAlerts = false; // create a utils instance, not need for but helpful to keep the lines of code low CommonUtils utils = new CommonUtils(excelApplication); // add a new workbook Excel.Workbook workBook = excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1]; // we need some data to display Excel.Range dataRange = PutSampleData(workSheet); // create a nice diagram Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225); chart.Chart.SetSourceData(dataRange); // save the book string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example05", Excel.Tools.DocumentFormat.Normal); workBook.SaveAs(workbookFile); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); // show dialog for the user(you!) HostApplication.ShowFinishDialog(null, workbookFile); }
public void RunExample() { bool isFailed = false; string workbookFile = null; Excel.Application excelApplication = null; try { // start excel and turn off msg boxes excelApplication = new Excel.Application(); excelApplication.DisplayAlerts = false; excelApplication.Visible = false; // create a utils instance, not need for but helpful to keep the lines of code low CommonUtils utils = new CommonUtils(excelApplication); // add a new workbook Excel.Workbook workBook = excelApplication.Workbooks.Add(); // add new global Code Module VB.VBComponent globalModule = workBook.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule); globalModule.Name = "MyNewCodeModule"; // add a new procedure to the modul globalModule.CodeModule.InsertLines(1, "Public Sub HelloWorld(Param as string)\r\n MsgBox \"Hello from NetOffice!\" & vbnewline & Param\r\nEnd Sub"); // create a click event trigger for the first worksheet int linePosition = workBook.VBProject.VBComponents[2].CodeModule.CreateEventProc("BeforeDoubleClick", "Worksheet"); workBook.VBProject.VBComponents[2].CodeModule.InsertLines(linePosition + 1, "HelloWorld \"BeforeDoubleClick\""); // display info in the worksheet Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets[1]; sheet.Cells[2, 2].Value = "This workbook contains dynamic created VBA Moduls and Event Code"; sheet.Cells[5, 2].Value = "Open the VBA Editor to see the code"; sheet.Cells[8, 2].Value = "Do a double click to catch the BeforeDoubleClick Event from this Worksheet."; // save the book XlFileFormat fileFormat = GetFileFormat(excelApplication); workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example07", Excel.Tools.DocumentFormat.Macros); workBook.SaveAs(workbookFile, fileFormat); } catch (System.Runtime.InteropServices.COMException throwedException) { isFailed = true; HostApplication.ShowErrorDialog("VBA Error", throwedException); } finally { // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); if ((null != workbookFile) && (!isFailed)) HostApplication.ShowFinishDialog(null, workbookFile); } }
public void 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); }
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() { // Any MS-Office application in NetOffice has a custom utils provider for common tasks // Moreover its available as instance property in NetOffice.Tools.COMAddin // If you have suggestions for the utils please feel free to contact the project // This tutorial shows only few features in MS-Excel // start excel and disable alerts Excel.Application application = new Excel.Application(); application.DisplayAlerts = false; // Create an instance of excel utils CommonUtils utils = new CommonUtils(application, typeof(Tutorial13).Assembly); // the file part of the utils makes it easier to deal with file extensions depedent on the current version // get default(xls or xlsx) , template with macros(xlt or xltm) - extension and build a valid file path string extensionNormal = utils.File.FileExtension(DocumentFormat.Normal); string extensionTemplateWithMacros = utils.File.FileExtension(DocumentFormat.TemplateMacros); string exampleFilePath = utils.File.Combine("C:\\MyFiles", "MyWorkbook", DocumentFormat.Normal); // the dialog part of the utils allows you to show default dialogs/messageboxes or you own dialogs // dialogs want be suppressed by default if the office application is currently in automation or not visible // you can also trigger the DialogShow and DialogShown event to observe dialog popups // we disable any suppress behavior here utils.Dialog.SuppressOnAutomation = false; utils.Dialog.SuppressOnHide = false; // show a simple message box. Have a look at the last argument. Its a default result and used if the messagebox is not shown. // In this tutorial, excel is in automation and hidden. Remove one or both of the 2 code lines above and the message box is not shown. // We got the default result in this case DialogResult userResult = utils.Dialog.ShowMessageBox("Hello World from NetOffice tutorial", "NO tutorial", MessageBoxButtons.YesNo, DialogResult.No); application.Quit(); application.Dispose(); HostApplication.ShowFinishDialog(); }
/// <summary> /// Creates an instance of the class /// </summary> /// <param name="owner">owner instance</param> protected internal FileUtils(CommonUtils owner) { if (null == owner) throw new ArgumentNullException("owner"); _owner = owner; }
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]; // 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 workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example02", 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 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); }