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