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 List<string> GetValuesFromListOfCells(List<ExcelAddress> addresses, string WorkbookPath, string WorksheetName)
{
// 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);
Excel.Worksheet xlsheet = GetWorkSheetWithName(xlwkbook, WorksheetName);
string CellValue = null;
List<string> Values = new List<string>();
foreach (var addr in addresses)
{
Excel.Range CurrentRange = (Excel.Range)xlsheet.Cells[addr.Row, addr.Column];
CellValue = Convert.ToString(CurrentRange.Cells.Value);
Values.Add(CellValue);
}
return Values;
}
public void RunExample()
{
// create excel instance
Excel.Application application = new NetOffice.ExcelApi.Application();
// ask the application object for Quit method support
bool supportQuitMethod = application.EntityIsAvailable("Quit");
// ask the application object for Visible property support
bool supportVisbibleProperty = application.EntityIsAvailable("Visible");
// ask the application object for SmartArtColors property support (only available in Excel 2010)
bool supportSmartArtColorsProperty = application.EntityIsAvailable("SmartArtColors");
// ask the application object for XYZ property or method support (not exists of course)
bool supportTestXYZProperty = application.EntityIsAvailable("TestXYZ");
// print result
string messageBoxContent = "";
messageBoxContent += string.Format("Your installed Excel Version supports the Quit Method: {0}{1}", supportQuitMethod, Environment.NewLine);
messageBoxContent += string.Format("Your installed Excel Version supports the Visible Property: {0}{1}", supportVisbibleProperty, Environment.NewLine);
messageBoxContent += string.Format("Your installed Excel Version supports the SmartArtColors Property: {0}{1}", supportSmartArtColorsProperty, Environment.NewLine);
messageBoxContent += string.Format("Your installed Excel Version supports the TestXYZ Property: {0}{1}", supportTestXYZProperty, Environment.NewLine);
MessageBox.Show(messageBoxContent, "EntityIsAvailable Result", MessageBoxButtons.OK, MessageBoxIcon.Information);
// quit and dispose
application.Quit();
application.Dispose();
}
private void Form1_Shown(object sender, EventArgs e)
{
Excel.Application app = null;
try
{
Settings.Default.PerformanceTrace.Alert += new PerformanceTrace.PerformanceAlertEventHandler(PerformanceTrace_Alert);
Settings.Default.PerformanceTrace["ExcelApi"].Enabled = true;
Settings.Default.PerformanceTrace["ExcelApi"].IntervalMS = 0;
//Settings.Default.PerformanceTrace["ExcelApi", "Application", "DisplayAlerts"].Enabled = true;
//Settings.Default.PerformanceTrace["ExcelApi", "Application", "DisplayAlerts"].IntervalMS = 0;
app = new Excel.Application();
NOTools.Utils.CommonUtils utils = new NOTools.Utils.CommonUtils(app, typeof(Form1).Assembly);
app.DisplayAlerts = false;
utils.Dialog.SuppressOnAutomation = false;
utils.Dialog.SuppressOnHide = false;
utils.Dialog.ShowDiagnostics(true);
}
catch (Exception exception)
{
Console.WriteLine(exception.ToString());
}
finally
{
app.Quit();
app.Dispose();
Close();
}
}
public void Run()
{
// Replace Excel.Workbook with MyWorkbook
NetOffice.Core.Default.CreateInstance += delegate(Core sender, Core.OnCreateInstanceEventArgs args)
{
if (args.Instance.InstanceType == typeof(Excel.Workbook))
{
args.Replace = typeof(MyWorkbook);
}
};
Excel.Application application = new Excel.Application();
application.DisplayAlerts = false;
// add and cast book to MyWorkbook
MyWorkbook book = application.Workbooks.Add() as MyWorkbook;
if (book.Has3Sheets)
{
Console.WriteLine("Book has 3 sheets.");
}
application.Quit();
application.Dispose();
HostApplication.ShowFinishDialog();
}
/// <summary>
/// Recupera el texto plano de un documento de PowerPoint
/// </summary>
/// <returns></returns>
//private StringBuilder GetTextFromPowerPoint(string source)
//{
// StringBuilder text = new StringBuilder();
// PowerPoint.Application pa = new PowerPoint.Application();
// PowerPoint.Presentation pp = pa.Presentations.Open(source,
// Microsoft.Office.Core.MsoTriState.msoTrue,
// Microsoft.Office.Core.MsoTriState.msoFalse,
// Microsoft.Office.Core.MsoTriState.msoFalse);
// foreach (PowerPoint.Slide slide in pp.Slides)
// {
// foreach (PowerPoint.Shape shape in slide.Shapes)
// {
// try
// {
// text.Append(shape.TextFrame.TextRange.Text).Append(" ");
// }
// catch (Exception)
// {
// }
// }
// }
// return text;
//}
/// <summary>
/// Recupera el texto plano de un documento de excel
/// </summary>
/// <returns></returns>
private StringBuilder GetTextFromExcel(string path)
{
StringBuilder text = new StringBuilder();
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(path);
//TODO: pendiente ajustar con NetOffice
//Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
//for (int sh = 1; sh <= xlWorkbook.Sheets.Count; sh++)
//{
// xlWorksheet = xlWorkbook.Sheets[sh];
// Excel.Range xlRange = xlWorksheet.UsedRange;
// int rowCount = xlRange.Rows.Count;
// int colCount = xlRange.Columns.Count;
// for (int i = 1; i <= rowCount; i++)
// {
// for (int j = 1; j <= colCount; j++)
// {
// try
// {
// text.Append(xlRange.Cells[i, j].Value2.ToString()).Append(" ");
// }
// catch (Exception) { }
// }
// }
//}
return(text);
}
//public ExcelExporter(string txtBox)
//{
// Console.SetOut(new TextBoxWriter(txtBox));
//}
public void GenerateReport()
{
var excelApplication = new Excel.Application();
excelApplication.Workbooks.Add();
excelApplication.DisplayAlerts = false;
var style = excelApplication.ActiveWorkbook.Styles.Add("NewStyle");
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Bold = true;
//Get active worksheet
var sheet = (Excel.Worksheet)excelApplication.ActiveSheet;
AddColumnNames(sheet);
AddExcelHeadingText(sheet);
AddDataRows(sheet);
var filePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), "FinancialReport");
excelApplication.ActiveWorkbook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, false,
false, XlSaveAsAccessMode.xlExclusive);
//CleanUp
excelApplication.ActiveWorkbook.Close();
excelApplication.Quit();
excelApplication.Dispose();
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// create a utils instance, not need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// we need some data to display
Excel.Range dataRange = PutSampleData(workSheet);
// create a nice diagram
Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
// save the book
string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example05", Excel.Tools.DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
HostApplication.ShowFinishDialog(null, workbookFile);
}
protected virtual void ResizeOfficeWindow()
{
Rectangle workingArea = Screen.PrimaryScreen.WorkingArea;
int height = Convert.ToInt32(workingArea.Height * 0.5);
int width = Convert.ToInt32(workingArea.Width * 0.75);
switch (this.Test.OfficeApp)
{
case "Word":
Word.Application wordApp = this.Application as Word.Application;
wordApp.WindowState = Word.Enums.WdWindowState.wdWindowStateNormal;
wordApp.Top = 0;
wordApp.Left = 0;
wordApp.Height = height;
wordApp.Width = width;
break;
case "Excel":
Excel.Application excelApp = this.Application as Excel.Application;
excelApp.WindowState = Excel.Enums.XlWindowState.xlNormal;
excelApp.Top = 0;
excelApp.Left = 0;
excelApp.Height = height;
excelApp.Width = width;
break;
case "PowerPoint":
break;
}
}
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)
{ }
}
private void Form1_Shown(object sender, EventArgs e)
{
Excel.Application app = null;
try
{
Settings.Default.PerformanceTrace.Alert += new PerformanceTrace.PerformanceAlertEventHandler(PerformanceTrace_Alert);
Settings.Default.PerformanceTrace["ExcelApi"].Enabled = true;
Settings.Default.PerformanceTrace["ExcelApi"].IntervalMS = 0;
//Settings.Default.PerformanceTrace["ExcelApi", "Application", "DisplayAlerts"].Enabled = true;
//Settings.Default.PerformanceTrace["ExcelApi", "Application", "DisplayAlerts"].IntervalMS = 0;
app = new Excel.Application();
NOTools.Utils.CommonUtils utils = new NOTools.Utils.CommonUtils(app, typeof(Form1).Assembly);
app.DisplayAlerts = false;
utils.Dialog.SuppressOnAutomation = false;
utils.Dialog.SuppressOnHide = false;
utils.Dialog.ShowDiagnostics(true);
}
catch (Exception exception)
{
Console.WriteLine(exception.ToString());
}
finally
{
app.Quit();
app.Dispose();
Close();
}
}
public void RunExample()
{
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()
{
// start application
Excel.Application application = new Excel.Application();
application.DisplayAlerts = false;
// create new Workbook & attach close event trigger
Excel.Workbook book = application.Workbooks.Add();
book.BeforeCloseEvent += new Excel.Workbook_BeforeCloseEventHandler(book_BeforeCloseEvent);
// we dispose the instance. the parameter false signals to api dont release the event listener
// set parameter to true and the event listener will stopped and you dont get events for the instance
// the DisposeChildInstances() method has the same method overload
book.Close();
book.Dispose(false);
application.Quit();
application.Dispose();
// the application object is ouer root object
// dispose them release himself and any childs of application, in this case workbooks and workbook
// the excel instance are now removed from process list
_hostApplication.ShowFinishDialog();
}
public static void Main(string[] args)
{
Excel.Application app = null;
try
{
System.Console.WriteLine("NetOffice CreateInstance Event Concept Test\r\n");
// Use this.Factory.CreateInstance instead in NetOffice Tools COMAddin
NetOffice.Core.Default.CreateInstance += new Core.OnCreateInstanceEventHandler(Default_CreateInstance);
//Disable NO console for clean view/output
NetOffice.Core.Default.Console.Mode = DebugConsoleMode.None;
app = new Excel.Application();
Excel.Workbook book = app.Workbooks.Add();
MyCustomWorksheet sheet = book.Sheets[1] as MyCustomWorksheet;
sheet.PrintName();
Console.WriteLine("\r\nTest passed");
Console.ReadKey();
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
Console.ReadKey();
}
finally
{
if (null != app)
{
app.Quit();
app.Dispose();
app = null;
}
}
}
public 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 static void Main(string[] args)
{
Excel.Application app = null;
try
{
System.Console.WriteLine("NetOffice CreateInstance Event Concept Test\r\n");
// Use this.Factory.CreateInstance instead in NetOffice Tools COMAddin
NetOffice.Core.Default.ObjectActivator.CreateInstance += ObjectActivator_CreateInstance;
app = new Excel.ApplicationClass();
Excel.Workbook book = app.Workbooks.Add();
MyCustomWorksheet sheet = book.Sheets[1] as MyCustomWorksheet;
sheet.PrintName();
Console.WriteLine("\r\nTest passed");
Console.ReadKey();
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
Console.ReadKey();
}
finally
{
if (null != app)
{
app.Quit();
app.Dispose();
app = null;
}
}
}
public 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 static void GenerateSequence()
{
Excel.Application application = new Excel.Application(null, ExcelDnaUtil.Application);
string path = application.ActiveWorkbook.FullName;
string filenameNoExtension = Path.GetFileNameWithoutExtension(path);
string filename = Path.GetFileName(path);
string root = Path.GetDirectoryName(path) + Path.DirectorySeparatorChar;
try
{
CTestContainer container = WorkbookParser.parseTestsOfWorkbook(application.ActiveWindow.SelectedSheets, filename);
string URIFilename = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase) + Path.DirectorySeparatorChar + "templates" + Path.DirectorySeparatorChar + "ST-TestStand4" + Path.DirectorySeparatorChar;
Uri uri = new Uri(URIFilename);
logger.Debug("Defining Template directory for TestStand templates to " + uri.LocalPath);
TestStandGen.TestStandGen.genSequence(container, root + filenameNoExtension + ".seq", uri.LocalPath);
}
catch (Exception ex)
{
logger.Debug("Exception raised : ", ex);
XlCall.Excel(XlCall.xlcAlert, ex.Message);
}
XlCall.Excel(XlCall.xlcAlert, "Generation is finished");
}
static void ExportChart(string strFile)
{
Application app = GetApp();
if (app == null)
{
throw new Exception("先打开excel");
}
Workbook workbook = app.Workbooks.Open(strFile);
foreach (Worksheet sheet in workbook.Sheets)
{
foreach (Shape shape in sheet.Shapes)
{
MsoShapeType typ = MsoShapeType.msoShapeTypeMixed;
try
{
typ = shape.Type;
}
catch (Exception e)
{
continue;
}
if (typ == MsoShapeType.msoChart)
{
ExportChart(shape, strFile);
}
}
}
workbook.Close(MsoTriState.msoFalse);
}
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();
}
}
}
private static Excel.Range ToRange(ExcelReference excelReference)
{
Excel.Application app = new Excel.Application(null, ExcelDnaUtil.Application);
Excel.Range refRange = app.Range(XlCall.Excel(XlCall.xlfReftext, excelReference, true));
return(refRange);
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
application = new NetOffice.ExcelApi.Application();
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet;
foreach (Excel.Range item in sheet.Range("$A1:$B100"))
item.Value = DateTime.Now;
return new TestResult(true, DateTime.Now.Subtract(startTime), "", null, "");
}
catch (Exception exception)
{
return new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, "");
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
private static void TestExcel()
{
Console.WriteLine("Test Excel File Utils");
Excel.Application application = new Excel.Application();
application.DisplayAlerts = false;
Excel.Tools.Utils.CommonUtils utils = new Excel.Tools.Utils.CommonUtils(application);
string fileName = utils.File.Combine("C:\\MyFiles", "Test01", Excel.Tools.DocumentFormat.Normal);
application.Quit();
application.Dispose();
if (utils.ApplicationIs2007OrHigher)
{
if ("C:\\MyFiles\\Test01.xlsx" != fileName)
{
throw new Exception("Unexpected excel filename");
}
}
else
{
if ("C:\\MyFiles\\Test01.xls" != fileName)
{
throw new Exception("Unexpected excel filename");
}
}
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
application = COMObject.Create <Excel.Application>(COMObjectCreateOptions.CreateNewCore);
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet;
foreach (Excel.Range item in sheet.Range("$A1:$B100"))
{
item.Value = DateTime.Now;
}
return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, ""));
}
catch (Exception exception)
{
return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, ""));
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
public void 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 ActionResult Index()
{
// start excel and turn off msg boxes
Application excelApplication = new Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
Workbook workBook = excelApplication.Workbooks.Add();
Worksheet workSheet = (Worksheet) workBook.Worksheets[1];
// draw back color and perform the BorderAround method
workSheet.Range("$B2:$B5").Interior.Color = ToDouble(Color.DarkGreen);
workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);
// draw back color and border the range explicitly
workSheet.Range("$D2:$D5").Interior.Color = ToDouble(Color.DarkGreen);
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle
= XlLineStyle.xlDouble;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = ToDouble(Color.Black);
// save the book
string fileExtension = GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example01{1}", "D:", fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
var bytes = System.IO.File.ReadAllBytes(workbookFile);
System.IO.File.Delete(workbookFile);
return File(bytes, "application/vnd.ms-excel", "FileName2" + fileExtension);
}
public void Run()
{
// this examples shows 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 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 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);
}
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 static void Main(string[] args)
{
Excel.Application app = null;
try
{
System.Console.WriteLine("NetOffice CreateInstance Event Concept Test\r\n");
// Use this.Factory.CreateInstance instead in NetOffice Tools COMAddin
NetOffice.Core.Default.CreateInstance += new Core.OnCreateInstanceEventHandler(Default_CreateInstance);
//Disable NO console for clean view/output
NetOffice.Core.Default.Console.Mode = DebugConsoleMode.None;
app = new Excel.Application();
Excel.Workbook book = app.Workbooks.Add();
MyCustomWorksheet sheet = book.Sheets[1] as MyCustomWorksheet;
sheet.PrintName();
Console.WriteLine("\r\nTest passed");
Console.ReadKey();
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
Console.ReadKey();
}
finally
{
if (null != app)
{
app.Quit();
app.Dispose();
app = null;
}
}
}
public static void BeginUpdate(this Excel.Application app)
{
app.ScreenUpdating = false;
app.DisplayStatusBar = false;
app.Calculation = Excel.Enums.XlCalculation.xlCalculationManual;
app.EnableEvents = false;
}
public void Run()
{
// this example shows you how i still can recieve events from an disposed proxy.
// you have to use th Dispose oder DisposeChildInstances method with a parameter.
// start application
Excel.Application application = new Excel.Application();
application.DisplayAlerts = false;
// create new Workbook & attach close event trigger
Excel.Workbook book = application.Workbooks.Add();
book.BeforeCloseEvent += new Excel.Workbook_BeforeCloseEventHandler(book_BeforeCloseEvent);
// we dispose the instance. the parameter false signals to api dont release the event listener
// set parameter to true and the event listener will stopped and you dont get events for the instance
// the DisposeChildInstances() method has the same method overload
book.Close();
book.Dispose(false);
application.Quit();
application.Dispose();
// the application object is ouer root object
// dispose them release himself and any childs of application, in this case workbooks and workbook
// the excel instance are now removed from process list
HostApplication.ShowFinishDialog();
}
public void Write(string filename, int filterindex_UNUSED, IEnumerable <ITransPair> pairs, IColumnDesc[] columns, InlineString.Render render)
{
string tmpname = null;
try
{
tmpname = CreateTempFile(Path.GetTempPath(), ".xml");
new XmlssWriter().Write(tmpname, 0, pairs, columns, render);
var excel = new Excel()
{
Visible = false, Interactive = false, DisplayAlerts = false
};
try
{
var book = excel.Workbooks.Open(tmpname);
book.SaveAs(filename, XlFileFormat.xlOpenXMLWorkbook);
book.Close(false);
}
finally
{
excel.Quit();
excel.Dispose();
}
}
finally
{
if (tmpname != null)
{
File.Delete(tmpname);
}
}
}
public void Run()
{
// this is a simple demonstration how i can convert unkown types at runtime
// start application
Excel.Application application = new Excel.Application();
application.Visible = false;
application.DisplayAlerts = false;
foreach (Office.COMAddIn item in application.COMAddIns)
{
// the application property is an unkown COM object
// we need a cast at runtime
Excel.Application hostApp = item.Application as Excel.Application;
// do some sample stuff
string hostAppName = hostApp.Name;
bool hostAppVisible = hostApp.Visible;
}
// quit and dispose excel
application.Quit();
application.Dispose();
_hostApplication.ShowFinishDialog();
}
public void 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 Build(string outputFile, string className, DateTime reportDate)
{
using (var db = new SamContext())
using (var excel = new NetOffice.ExcelApi.Application())
{
try
{
excel.DisplayAlerts = false;
var wb = excel.Workbooks.Open(_templateFile, false, false);
var ws = (Worksheet)wb.Worksheets.First();
var students = db.Students.Where(s => s.ClassName == className).ToList();
AppendToList(ws, students.Where(s => s.Sex == "Female"), FemaleRow);
AppendToList(ws, students.Where(s => s.Sex == "Male"), MaleRow);
wb.SaveAs(Path.Combine(_startUpPath, "output.xlsx"));
wb.Close();
Process.Start(Path.Combine(_startUpPath, "output.xlsx"));
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
if (excel.Workbooks.Any() == false)
{
excel.Quit();
}
}
}
}
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);
}
internal void Run()
{
MyCore core = new MyCore();
core.ObjectActivator.RegisterType(typeof(Excel.Application), typeof(MyExcelApplication));
core.ObjectActivator.RegisterType(typeof(Excel.Range), typeof(MyExcelRange));
core.Settings.EnableAutomaticQuit = true;
using (Excel.Application application = COMObject.Create <Excel.Application>(core))
{
application.DisplayAlerts = false;
var workbooks = application.Workbooks;
var book = workbooks.Add();
var sheet = book.Sheets.Add().To <Excel.Worksheet>();
bool visible = application.Visible;
application.Visible = true;
try
{
application.Visible = false;
}
catch (ArgumentException)
{
Console.WriteLine("MyExcelApplication prevent us to make excel invisible.");
}
var range = sheet.Range("$A1");
range.Value = null;
var value = range.Value;
Console.WriteLine("We set null for range but MyExcelRange change it to {0}.", value);
}
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public Workbook Fetch(string name)
{
//try to get the active PPT Instance
this.application = Excel.Application.GetActiveInstance();
if (this.application == null)
{
//start PPT if ther is no active instance
throw new Exception("Missing PowerPoint Application");
}
this.application.Visible = true;
Workbook workbook = null;
if (name != null)
{
try
{
workbook = new Workbook(this.application.Workbooks[name]);
}
catch
{
throw new Exception("Cannot find the PowerPoint presentations");
}
}
else
{
workbook = new Workbook(this.application.ActiveWorkbook);
}
return(workbook);
}
//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();
}
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();
}
}
}
internal void Run()
{
Excel.Application app = null;
try
{
Settings.Default.PerformanceTrace.Alert += new PerformanceTrace.PerformanceAlertEventHandler(PerformanceTrace_Alert);
Settings.Default.PerformanceTrace["ExcelApi"].Enabled = true;
Settings.Default.PerformanceTrace["ExcelApi"].IntervalMS = 0;
app = new Excel.ApplicationClass();
//app = COMObject.Create<Excel.Application>();
app.Visible = true;
Contribution.CommonUtils utils = new Contribution.CommonUtils(app, typeof(Form1).Assembly);
app.DisplayAlerts = false;
Excel.Workbook book = app.Workbooks.Add();
Excel.Worksheet sheet = book.Sheets[1] as Excel.Worksheet;
sheet.Cells[1, 1].Value = "This is a sample value";
sheet.Protect();
utils.Dialog.SuppressOnAutomation = false;
utils.Dialog.SuppressOnHide = false;
utils.Dialog.ShowDiagnostics(true);
}
catch (Exception exception)
{
Console.WriteLine(exception.ToString());
}
finally
{
if (null != app)
{
app.Quit();
app.Dispose();
}
}
}
public void 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 static void EndUpdate(this Excel.Application app)
{
app.ScreenUpdating = true;
app.DisplayStatusBar = true;
app.Calculation = Excel.Enums.XlCalculation.xlCalculationAutomatic;
app.EnableEvents = true;
}
public void Run()
{
// this is a simple demonstration how i can convert unkown types at runtime
// start application
Excel.Application application = new Excel.Application();
application.Visible = false;
application.DisplayAlerts = false;
foreach (Office.COMAddIn item in application.COMAddIns)
{
// the application property is an unkown COM object
// we need a cast at runtime
Excel.Application hostApp = item.Application as Excel.Application;
// do some sample stuff
string hostAppName = hostApp.Name;
bool hostAppVisible = hostApp.Visible;
}
// quit and dispose excel
application.Quit();
application.Dispose();
HostApplication.ShowFinishDialog();
}
public void 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();
}
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();
}
private string GetDefaultExtension(Application application)
{
double Version = Convert.ToDouble(application.Version, CultureInfo.InvariantCulture);
if (Version >= 12.00)
return ".xlsx";
else
return ".xls";
}
/// <summary>
/// creates a new excel application
/// </summary>
/// <returns></returns>
static Excel.Application CreateExcelApplication()
{
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
excelApplication.Interactive = false;
excelApplication.ScreenUpdating = false;
return excelApplication;
}
public object SetExcelApplication(dynamic p){
this.excelApp = new Excel.Application();
this.excelApp.Visible = false;
this.excelApp.DisplayAlerts = false;
this.isAppSet = true;
p.success = true;
return p;
}
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();
}
}
}
/// <summary>
/// creates a new excel application
/// </summary>
/// <returns></returns>
static Excel.Application CreateExcelApplication()
{
NetOffice.Factory.Initialize();
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
excelApplication.Interactive = false;
excelApplication.ScreenUpdating = false;
return excelApplication;
}
public void AbrirArchivo(string sNombrePlantilla)
{
AplicacionExcel = new Excel.Application();
AplicacionExcel.DisplayAlerts = false;
AplicacionExcel.Visible = false;
Libro = AplicacionExcel.Workbooks.Open(Environment.CurrentDirectory + "\\Plantillas\\" + sNombrePlantilla);
Hoja = (Excel.Worksheet)Libro.Sheets[1];
}
public List<List<string>> GetListOfValues (int StartRow, int StartColumn, int NumberOfColumns, string WorkbookPath, string WorksheetName)
{
int Row = StartRow;
int Column = StartColumn;
// 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);
Excel.Worksheet xlsheet = GetWorkSheetWithName(xlwkbook, WorksheetName);
string CellValue = null;
List<List<string>> Values = new List<List<string>>();
while (CellValue != "")
{
Excel.Range CurrentRange = (Excel.Range)xlsheet.Cells[Row, StartColumn];
CellValue = Convert.ToString(CurrentRange.Cells.Value);
if (CellValue != "" && CellValue != null)
{
List<string> currentRowValues = new List<string>();
for (int i = 0; i < NumberOfColumns; i++)
{
Excel.Range thisValueRange = (Excel.Range)xlsheet.Cells[Row, StartColumn+i];
string thisCellValue = Convert.ToString(thisValueRange.Cells.Value);
if (thisCellValue != null && thisCellValue !="")
{
currentRowValues.Add(thisCellValue);
}
else
{
currentRowValues.Add("");
}
}
if (currentRowValues[0]!="" && currentRowValues[0]!=null)
{
Values.Add(currentRowValues);
}
}
else
{
CellValue = "";
}
Row++;
}
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
return Values;
}
void Go()
{
Application excelApp = new Application();
//ApplicationClass excel = new ApplicationClass();
//excel.Workbooks.op
//excelApp.
//excelApp.Workbooks.op
}
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();
}
public void AutoOpen()
{
dnaApp = ExcelDnaUtil.Application;
oXL = new NetOffice.ExcelApi.Application(null, dnaApp);
if (oXL.ActiveWorkbook != null)
{
this.xlApp_WorkbookActivateEvent(oXL.ActiveWorkbook);
}
oXL.WorkbookActivateEvent += xlApp_WorkbookActivateEvent;
}
