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