public static void prjTb(System.Data.DataTable fnlCnsInput, System.Data.DataTable newPrmInput, InputOptionsPage projectionOptions, Excel.Workbook inputOwb)
{
oWB = inputOwb;
prjNme = projectionOptions.projName;
fnlCns = fnlCnsInput;
nwPrm = newPrmInput;
Tuple<List<string>, DataTable, List<double>, List<string>, DataTable, List<double>> projectionResultsData
= PrepareSummaryAndDetailResultsForOutput();
CheckIfResultsExist();
ProjectionSummaryTab projectionSummaryResults = new ProjectionSummaryTab(projectionResultsData, oWB, projectionNameOfSummaryTabs);
ProjectionRatesTab projectionRatesGrid = new ProjectionRatesTab(oWB, nwPrm, projectionNameOfSummaryTabs);
ProjectionDetailTab projectedDetailResults = new ProjectionDetailTab(fnlCns, oWB, projectionNameOfSummaryTabs);
ProjectionAssumptionResults projectedAssumptionResults = new ProjectionAssumptionResults(projectionOptions, oWB, projectionNameOfSummaryTabs);
FinalizeResults();
}
private static void runCalcAction(System.Action act)
{
SplashScreen splash = new SplashScreen();
try
{
oXL.ScreenUpdating = false;
oXL.EnableEvents = false;
oXL.DisplayAlerts = false;
oXL.Calculation = XlCalculation.xlCalculationManual;
splash.Show();
Task.Factory.StartNew(act).Wait();
}
catch (AggregateException exception)
{
AggregateException exception2 = exception.Flatten();
foreach (Exception exception3 in exception2.InnerExceptions)
{
showError(exception3.Message);
}
}
finally
{
oXL.ScreenUpdating = true;
oXL.EnableEvents = true;
oXL.DisplayAlerts = true;
oXL.Calculation = XlCalculation.xlCalculationAutomatic;
splash.Hide();
oWB.Dispose();
oWB = oXL.ActiveWorkbook;
}
}
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 static int Open()
{
int return_code = 0;
string complete_filename = FilePath + "\\" + FileName + fileExt;
try
{
System.IO.File.Exists(complete_filename);
workBook = excelApplication.Workbooks.Open(complete_filename);
workSheet = (_Excel.Worksheet)workBook.Worksheets[1];
return_code = 0;
}
catch
{
return_code = 9;
}
return return_code;
}
//method for get content of excel file
public ObservableCollection <bomm> getContent_EXCEL()
{
//list for return
ObservableCollection <bomm> db = new ObservableCollection <bomm>();
//window for select excel file
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel Files (*.xls)|*.xls";
openFileDialog.ShowDialog();
string local = openFileDialog.FileName;
if (local != String.Empty)
{
//using library for acess excel file
var application = new Excel.Application();
Excel.Workbook book = application.Workbooks.Open(local);
Excel.Worksheet mainSheet = (Excel.Worksheet)book.Sheets[4];
DataTable tb = new DataTable();
//getting excel dates and saving in collection
foreach (var item in mainSheet.UsedRange.Rows)
{
Excel.Range b = item;
List <string> str = new List <string>();
foreach (Excel.Range cell in item.Columns)
{
str.Add(cell.Value + "");
}
bomm bomobj = new bomm();
bomobj.bom_level = str[0];
bomobj.Part_Number = str[2];
bomobj.Part_Name = str[3];
bomobj.Revision = str[4];
bomobj.Quantit = str[5];
bomobj.Unit_of_measure = str[6];
bomobj.Procurement_Type = str[7];
bomobj.Reference_Designatos = str[8];
bomobj.BOM_Notes = str[9];
str.Clear();
db.Add(bomobj);
}
db.RemoveAt(0);
db.RemoveAt(0);
db.RemoveAt(61);
db.RemoveAt(61);
//CleanUp
application.ActiveWorkbook.Close();
application.Quit();
application.Dispose();
}
foreach (var item in db)
{
switch (item.bom_level)
{
case "0":
pivo0 = item.Part_Number;
break;
case "1":
pivo1 = item.Part_Number;
break;
case "2":
pivo2 = item.Part_Number;
break;
case "3":
pivo3 = item.Part_Number;
break;
case "4":
pivo4 = item.Part_Number;
break;
default:
{
break;
}
}
switch (item.bom_level)
{
case "1":
item.Parent_Part_Number = pivo0;
break;
case "2":
item.Parent_Part_Number = pivo1;
break;
case "3":
item.Parent_Part_Number = pivo2;
break;
case "4":
item.Parent_Part_Number = pivo3;
break;
default:
{
break;
}
}
}
return(db);
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
// start excel and turn off Application 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];
/* some kind of numerics */
// the given thread culture in all latebinding calls are stored in NetOffice.Settings.
// you can change the culture. 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.get_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 = cultureInfo.DateTimeFormat.FullDateTimePattern;
workSheet.Range("C10").Value = cultureInfo.DateTimeFormat.LongDatePattern;
workSheet.Range("D10").Value = cultureInfo.DateTimeFormat.ShortDatePattern;
workSheet.Range("E10").Value = cultureInfo.DateTimeFormat.LongTimePattern;
workSheet.Range("F10").Value = cultureInfo.DateTimeFormat.ShortTimePattern;
// DateTime
DateTime dateTimeValue = DateTime.Now;
workSheet.Range("B11").Value = dateTimeValue;
workSheet.Range("B11").NumberFormat = cultureInfo.DateTimeFormat.FullDateTimePattern;
workSheet.Range("C11").Value = dateTimeValue;
workSheet.Range("C11").NumberFormat = cultureInfo.DateTimeFormat.LongDatePattern;
workSheet.Range("D11").Value = dateTimeValue;
workSheet.Range("D11").NumberFormat = cultureInfo.DateTimeFormat.ShortDatePattern;
workSheet.Range("E11").Value = dateTimeValue;
workSheet.Range("E11").NumberFormat = cultureInfo.DateTimeFormat.LongTimePattern;
workSheet.Range("F11").Value = dateTimeValue;
workSheet.Range("F11").NumberFormat = cultureInfo.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();
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 CreateExcelFile()
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
excelApp = new NetOffice.ExcelApi.Application();
excelApp.DefaultSheetDirection = 0;
if (excelApp == null)
{
Console.WriteLine("EXCEL could not be started. aCheck that your office installation and project references are correct.");
return;
}
workBook = excelApp.Workbooks.Add();
}
public static NetOffice.ExcelApi.Worksheet AddSheet(this NetOffice.ExcelApi.Workbook book)
{
return(book.Worksheets.AddSheet());
}
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();
}
void excelApplication_NewWorkbook(Excel.Workbook Wb)
{
textBoxEvents.BeginInvoke(_updateDelegate, new object[] { "Event NewWorkbook called." });
Wb.Dispose();
}
private void excelApplication_WorkbookDeactivate(Excel.Workbook Wb)
{
textBoxEvents.BeginInvoke(_updateDelegate, new object[] { "Event WorkbookDeactivate called." });
Wb.Dispose();
}
public ImportBuildingDataFileResponse ImportBuildingDataFile(ImportBuildingDataFileRequest request) {
ImportBuildingDataFileResponse response = null;
DataSet ds = null;
try {
response = new ImportBuildingDataFileResponse();
//read file from excel, using NetOffice
Excel.Application excelApplication = null;
using (excelApplication = new Excel.Application()) {
Excel.Workbook workBook = excelApplication.Workbooks.Open(request.SourceFilePath,
0, true, 5, "", "", true, null, "\t", false, false, 0, true, 1, 0);
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets[1];
Excel.Range excelRange = workSheet.UsedRange;
int rowCount = excelRange.Rows.Count;
int colCount = excelRange.Columns.Count;
//System.Data.SQLite.SQLiteConnection cn = new SQLiteConnection(@"Data Source=C:\sqlite\test.db;Version=3;New=True;Compress=True;");
//SQLiteCommand sqlite_cmd;
//SQLiteDataReader sqlite_datareader;
//// open the connection:
//cn.Open();
//// create a new SQL command:
//sqlite_cmd = cn.CreateCommand();
//// Let the SQLiteCommand object know our SQL-Query:
//sqlite_cmd.CommandText = "CREATE TABLE hello (id integer primary key, text varchar(100));";
//// Now lets execute the SQL ;D
//sqlite_cmd.ExecuteNonQuery();
ds = new DataSet();
ds.Tables.Add("Source");
ds.AcceptChanges();
BuildingDataAssessor record = null;
for (int i = 1; i <= rowCount; i++) {
record = new BuildingDataAssessor();
for (int j = 1; j <= colCount; j++) {
//MessageBox.Show(workSheet.Cells[i, j].Value.ToString());
switch (j) {
case 1:
record.Owner = workSheet.Cells[i, j].Value.ToString();
break;
case 2:
record.BuildingLocation = workSheet.Cells[i, j].Value.ToString();
break;
case 3:
record.LandArea = Convert.ToDouble(workSheet.Cells[i, j].Value.ToString());
break;
case 4:
record.BuildingType = workSheet.Cells[i, j].Value.ToString();
break;
case 5:
record.DateContructed = Convert.ToDateTime(workSheet.Cells[i, j].Value.ToString());
break;
case 6:
record.BuildingCost = Convert.ToDouble(workSheet.Cells[i, j].Value.ToString());
break;
}
}
//DataRow newRow = ds.Tables[0].NewRow();
ds.Tables[0].Rows.Add(new object[] {
record.Owner,
record.BuildingLocation,
record.LandArea,
record.BuildingType,
record.DateContructed,
record.BuildingCost
});
//newRow[0] = record.Owner;
ds.AcceptChanges();
// // Lets insert something into our new table:
// sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (1, 'Test Text 1');";
// // And execute this again ;D
// sqlite_cmd.ExecuteNonQuery();
}
//// We are ready, now lets cleanup and close our connection:
//cn.Close();
//ds.
ds.WriteXml(request.SourceDataPath);
workBook.DisposeChildInstances();
workBook = null;
excelApplication.Quit();
//excelApplication.Dispose();
//excelApplication = null;
}
//save to database
return response;
}
finally {
response = null;
ds = null;
}
}
public static void Convert(String xlsxFilePath, IDelimitedTextWriter textWriter, WorksheetConverter worksheetConverter = null)
{
if (xlsxFilePath == null)
{
throw new ArgumentNullException(nameof(xlsxFilePath));
}
if (textWriter == null)
{
throw new ArgumentNullException(nameof(textWriter));
}
if (System.IO.File.Exists(xlsxFilePath) == false)
{
throw new FileNotFoundException(String.Format("\"{0}\" file has not been found.", xlsxFilePath));
}
if (worksheetConverter == null)
{
worksheetConverter = new WorksheetConverter();
}
using (Excel.Application excelApplication = new Excel.Application())
{
try
{
excelApplication.Visible = false;
excelApplication.ScreenUpdating = false;
excelApplication.EnableEvents = false;
using (Excel.Workbooks workbooks = excelApplication.Workbooks)
{
using (Excel.Workbook emptyExcelWorkbook =
workbooks.Count == 0 ? workbooks.Add() : null)
{
try
{
excelApplication.Calculation = XlCalculation.xlCalculationManual;
using (var excelWorkbook = workbooks.Open(filename: Path.GetFullPath(xlsxFilePath)))
{
Object activeSheet = excelWorkbook.ActiveSheet;
try
{
if (activeSheet is Excel.Worksheet == false)
{
throw new ActiveWorksheetException();
}
Excel.Worksheet worksheet = (Excel.Worksheet)activeSheet;
worksheetConverter.Convert(worksheet, textWriter);
}
finally
{
(activeSheet as IDisposable)?.Dispose();
}
}
}
finally
{
emptyExcelWorkbook?.Close(saveChanges: false);
}
}
}
}
finally
{
excelApplication.Quit();
}
}
}
private void buttonStartExample_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
_excelApplication = new Excel.Application();
_excelApplication.DisplayAlerts = false;
Office.CommandBar commandBar = null;
Office.CommandBarButton commandBarBtn = null;
// add a new workbook
Excel.Workbook workBook = _excelApplication.Workbooks.Add();
// add a commandbar popup
Office.CommandBarPopup commandBarPopup = (Office.CommandBarPopup)_excelApplication.CommandBars["Worksheet Menu Bar"].Controls.Add(MsoControlType.msoControlPopup, System.Type.Missing, System.Type.Missing, System.Type.Missing, true);
commandBarPopup.Caption = "commandBarPopup";
#region few words, how to access the picture
/*
* you can see we use an own icon via .PasteFace()
* is not possible from outside process boundaries to use the PictureProperty directly
* the reason for is IPictureDisp: http://support.microsoft.com/kb/286460/de
* its not important is early or late binding or managed or unmanaged, the behaviour is always the same
* For example, a COMAddin running as InProcServer and can access the Picture Property
*/
#endregion
#region CommandBarButton
// add a button to the popup
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(_hostApplication.DisplayIcon.ToBitmap());
commandBarBtn.PasteFace();
commandBarBtn.ClickEvent += new Office.CommandBarButton_ClickEventHandler(commandBarBtn_Click);
#endregion
#region Create a new toolbar
// add a new toolbar
commandBar = _excelApplication.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(_hostApplication.DisplayIcon.ToBitmap());
commandBarBtn.PasteFace();
commandBarBtn.ClickEvent += new Office.CommandBarButton_ClickEventHandler(commandBarBtn_Click);
#endregion
#region Create a new ContextMenu
// add a commandbar popup
commandBarPopup = (Office.CommandBarPopup)_excelApplication.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
#region Display info
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets[1];
sheet.Cells[2, 2].Value = "this excel instance contains 3 custom menus";
sheet.Cells[3, 2].Value = "the main menu, the toolbar menu and the cell context menu";
sheet.Cells[4, 2].Value = "in this case the menus are temporaily created";
sheet.Cells[5, 2].Value = "they are not persistant and needs no unload event or something like this";
sheet.Cells[6, 2].Value = "you can also create persistant menus if you want";
#endregion
// make visible & set buttons
_excelApplication.Visible = true;
buttonStartExample.Enabled = false;
buttonQuitExample.Enabled = true;
}
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;
}
}
void _Application_WorkbookActivateEvent(Excel.Workbook Wb)
{
}
void _Application_WorkbookOpenEvent(Excel.Workbook Wb)
{
//_Application.Calculation = XlCalculation.xlCalculationAutomatic;
}
void WorkbookActivateEvent(Excel.Workbook Wb)
{
Globals.book = Wb;
Globals.sheet = (Excel.Worksheet)Wb.ActiveSheet;
}
void ExcelApplication_WorkbookDeactivate(Excel.Workbook Wb)
{
_workbookDeactivateEvent = true;
Wb.Dispose();
}
private void ExecuteEvents(Timeline timeline, TimelineHandler handler)
{
try
{
foreach (TimelineEvent 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 pids = ProcessManager.GetPids(ProcessManager.ProcessNames.Excel).ToList();
if (pids.Count > timeline.TimeLineHandlers.Count(o => o.HandlerType == HandlerType.Excel))
{
return;
}
}
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application
{
DisplayAlerts = false,
Visible = true
};
try
{
excelApplication.WindowState = XlWindowState.xlMinimized;
foreach (Excel.Workbook 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
CommonUtils utils = new CommonUtils(excelApplication);
_log.Trace("Excel adding workbook");
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
_log.Trace("Excel adding worksheet");
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[(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.";
string rand = RandomFilename.Generate();
string dir = timelineEvent.CommandArgs[0].ToString();
if (dir.Contains("%"))
{
dir = Environment.ExpandEnvironmentVariables(dir);
}
if (Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
string path = $"{dir}\\{rand}.xlsx";
//if directory does not exist, create!
_log.Trace($"Checking directory at {path}");
DirectoryInfo 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 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;
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();
}
}
}
private void Application2_NewWorkbookEvent(Excel.Workbook wb)
{
MessageBox.Show("Application2_NewWorkbookEvent");
}
private void excelApplication_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
{
textBoxEvents.BeginInvoke(_updateDelegate, new object[] { "Event WorkbookBeforeClose called." });
Wb.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
Excel.Tools.CommonUtils utils = new Excel.Tools.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 = COMObject.Create <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", DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show end dialog
HostApplication.ShowFinishDialog(null, workbookFile);
}
public virtual Int32 WorkbookBeforePrint(NetOffice.ExcelApi.Workbook wb, bool cancel)
{
return(InvokerService.InvokeInternal.ExecuteInt32MethodGet(this, "WorkbookBeforePrint", wb, cancel));
}
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 virtual Int32 WorkbookAddinUninstall(NetOffice.ExcelApi.Workbook wb)
{
return(InvokerService.InvokeInternal.ExecuteInt32MethodGet(this, "WorkbookAddinUninstall", wb));
}
private static void OpenWorkSheet()
{
// add a new workbook
workBook = excelApplication.Workbooks.Add();
workSheet = (_Excel.Worksheet)workBook.Worksheets[1];
}
private void Application_WorkbookActivateEvent(Excel.Workbook wb)
{
Console.WriteLine("WorkbookActivateEvent has been called.");
}
public virtual Int32 WorkbookActivate(NetOffice.ExcelApi.Workbook wb)
{
return(InvokerService.InvokeInternal.ExecuteInt32MethodGet(this, "WorkbookActivate", wb));
}
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();
}
public virtual Int32 WorkbookNewSheet(NetOffice.ExcelApi.Workbook wb, object sh)
{
return(InvokerService.InvokeInternal.ExecuteInt32MethodGet(this, "WorkbookNewSheet", wb, sh));
}
void ExcelApplication_NewWorkbook(Excel.Workbook Wb)
{
_newWorkbookEvent = true;
Wb.Dispose();
}
public virtual Int32 WindowDeactivate(NetOffice.ExcelApi.Workbook wb, NetOffice.ExcelApi.Window wn)
{
return(InvokerService.InvokeInternal.ExecuteInt32MethodGet(this, "WindowDeactivate", wb, wn));
}
void ExcelApplication_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
{
_workbookBeforeCloseEvent = true;
Wb.Dispose();
}
private void xlApp_WorkbookActivateEvent(Workbook newWb)
{
DocumentProperties builtinDocumentProperties = (DocumentProperties) newWb.BuiltinDocumentProperties;
object obj2 = builtinDocumentProperties["Title"].Value;
if ((obj2 != null) && obj2.ToString().Equals("Aon Ax Model", StringComparison.OrdinalIgnoreCase))
{
oWB = newWb;
AxCalcEngineAPI.AxCalcEngineAPI.Initialize();
}
else if (oWB != null)
{
oWB.Dispose();
oWB = null;
}
}
public void OpenExcelFile()
{
if (excelApp == null || excelApp.IsDisposed)
{
excelApp = new NetOffice.ExcelApi.Application();
fileExtension = GetDefaultExtension(excelApp);
workbookFile = string.Format("{0}FreqResult{1}", filespath, fileExtension);
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
excelApp.DefaultSheetDirection = 0;
if (excelApp == null)
{
Console.WriteLine("EXCEL could not be started. aCheck that your office installation and project references are correct.");
return;
}
excelApp.AlertBeforeOverwriting = false;
excelApp.DisplayAlerts = false;
excelApp.Visible = false;
if (!System.IO.File.Exists(workbookFile))
{
workBook = excelApp.Workbooks.Add();
workBook.Worksheets.Add();
((Worksheet)workBook.Worksheets[1]).Name = "Loop 1";
((Worksheet)workBook.Worksheets[2]).Name = "Loop 2";
((Worksheet)workBook.Worksheets[3]).Name = "Loop 3";
((Worksheet)workBook.Worksheets[4]).Name = "Loop 4";
workBook.SaveAs(workbookFile);
}
else
{
workBook = excelApp.Workbooks.Open(workbookFile, updateLinks: 0, readOnly: false);
}
}
for (int i = 0; i < LoopWorksheet.Length; i++)
{
LoopWorksheet[i] = ((Worksheet)workBook.Worksheets[i + 1]);
LoopWorksheet[i].Range("A1:D1000").Clear();
if (((ChartObjects)LoopWorksheet[i].ChartObjects()).Count > 0)
((ChartObjects)LoopWorksheet[i].ChartObjects()).Delete();
}
}