/// <summary> /// Release all references, must do lower to higher level... /// </summary> public static void DeleteObject() { if (LogCalls) { DebugLog.Message("ExcelOp DeleteObject"); } ReleaseAllObjects(); ReleaseObject(XlApp); XlApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); if (DebugFileSaveOperation) { return; // debug } try // hack to kill Excel process until proper close can be done { if (ExcelProcess != null && !ExcelProcess.HasExited) { ExcelProcess.Kill(); } } catch (Exception ex) { throw new Exception("Kill Process Failed: " + ex.Message); } }
public ExcelClass() { if (m_ExcelApplication == null) { m_ExcelApplication = new Excel.Application(); } }
private void fileOpen() { try { mApp = new Excel.Application(); mApp.Visible = true; //myBook = mApp.Workbooks.Add(filepath); myBook = mApp.Workbooks.Open(filepath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t", false, false, 0, true, Type.Missing, Type.Missing); sheets = myBook.Sheets; mySheet = sheets[1]; if (mySheet == null) { Console.WriteLine("没有工作簿"); return; } mySheet.Activate(); rowCount = mySheet.UsedRange.Rows.Count + 1; Console.WriteLine("加载时行数:" + rowCount); status_textBox.Text = "文件初始化成功"; insertTitle(); }catch (Exception ex) { status_textBox.Text = ex.Message; mApp.Quit(); } }
//Method to initialize opening Excel private void ExcelInit(String path, out List<Product> idinfo) { xlApp = new Excel.Application(); string dsSheet = "datasheet"; string idinfoSheet = "idinfo"; idinfo = null; if (System.IO.File.Exists(path)) { // then go and load this into excel xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //read info datasheet first to generate product specs list info_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(idinfoSheet); idinfo = ReadIdInfoIntoDataTable(info_xlWorkSheet); //read datasheet and load the data into product specs list //ds_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(dsSheet); //ReadDatasheetIntoDataTable(ds_xlWorkSheet); } else { MessageBox.Show("Unable to open excel file!"); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; System.Windows.Forms.Application.Exit(); } }
/// <summary> /// 构造 /// </summary> /// <param name="FileName"></param> public ExcelHelper(string FileName) { ExcelApp = new InteropExcel.Application(); object objOpt = System.Reflection.Missing.Value; wbclass = (InteropExcel.WorkbookClass)ExcelApp.Workbooks.Open(FileName, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt); }
public ExcelHelper(string _filename) { excelApp = new Excel.Application(); object objOpt = System.Reflection.Missing.Value; wbclass = (Excel.WorkbookClass)excelApp.Workbooks.Open(_filename, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt); }
//Method to initialize opening Excel private void ExcelInit(String path, out List <Product> idinfo) { xlApp = new Excel.Application(); string dsSheet = "datasheet"; string idinfoSheet = "idinfo"; idinfo = null; if (System.IO.File.Exists(path)) { // then go and load this into excel xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //read info datasheet first to generate product specs list info_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(idinfoSheet); idinfo = ReadIdInfoIntoDataTable(info_xlWorkSheet); //read datasheet and load the data into product specs list //ds_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(dsSheet); //ReadDatasheetIntoDataTable(ds_xlWorkSheet); } else { MessageBox.Show("Unable to open excel file!"); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; System.Windows.Forms.Application.Exit(); } }
/// <summary> /// 释放内存 /// </summary> public void Dispose(Excel._Worksheet CurSheet, Excel._Workbook CurBook, Excel._Application CurExcel) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet); CurSheet = null; CurBook.Close(false, mValue, mValue); System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook); CurBook = null; CurExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel); CurExcel = null; GC.Collect(); GC.WaitForPendingFinalizers(); } catch (System.Exception ex) { throw new Exception(ex.Message, ex); } finally { foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel")) { //if (pro.StartTime < DateTime.Now) pro.Kill(); } } System.GC.SuppressFinalize(this); }
public ExcelHelper(string _filePath) { filePath = _filePath; excelApp = new Excel.Application(); object objOpt = System.Reflection.Missing.Value; wbclass = (Excel.Workbook)excelApp.Workbooks.Open(_filePath, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt); }
static void MergeExcels(Excel._Application app, IEnumerable <string> sourcePaths, string destinationPath) { var dstWb = app.Workbooks.Add(""); var srcWbs = sourcePaths.Select(sourcePath => app.Workbooks.Add(sourcePath)); // Don't start with i = 1 because trying to delete the last sheet makes an error. for (var i = dstWb.Worksheets.Count; 2 <= i; i--) { dstWb.Worksheets[i].Delete(); } // Keep the last sheet to be deleted when it is no longer the last sheet. Excel.Worksheet ws1 = dstWb.Worksheets[1]; // app.Workbooks[1] is a destination so needs to be skipped. for (var i = app.Workbooks.Count; 2 <= i; i--) { var srcWb = app.Workbooks[i]; for (var j = srcWb.Worksheets.Count; 1 <= j; j--) { srcWb.Worksheets[j].Copy(dstWb.Worksheets[1]); } } ws1.Delete(); dstWb.SaveAs(destinationPath); foreach (var srcWb in srcWbs) { srcWb.Close(); } }
public static string MessageInStatusBar( [Parameter("The String ")] string Message ) { XL._Application theApp = ExcelInstance.Instance(); theApp.StatusBar = Message; return("--"); }
public static string MyAddress( [Parameter("x")] double x ) { XL._Application theApp = ExcelInstance.Instance(); XL.Range range = (XL.Range)theApp.get_Caller(System.Type.Missing); return(range.get_Address(System.Type.Missing, System.Type.Missing, XL.XlReferenceStyle.xlA1, System.Type.Missing, System.Type.Missing)); }
static public void Open(string path, bool visible) { exApp = new Ex.Application(); exApp.Visible = visible; exApp.DisplayAlerts = false; workBook = exApp.Workbooks.Open(path); workSheet = workBook.ActiveSheet; }
/// <summary> /// 释放 /// </summary> public void Close() { if (ExcelApp != null) { ExcelApp.Quit(); ExcelApp = null; } }
public DocExcel(string nameExcel, string nameSheet, XlPageOrientation orientation) //конструктор { excelapp = new ExcelApp.Application(); excelapp.Caption = nameExcel; excelBook = excelapp.Workbooks.Add(Type.Missing); _currentSheet = (ExcelApp.Worksheet)excelBook.Sheets.get_Item(1); _currentSheet.PageSetup.Orientation = orientation; }
public void ExportCallQualityData(string formPath, string savePath, string fileFormat, DataTable dt, string contragent, string ocinyvach, string ocinyvanyi, string callType, string opDate, string criticalError, string filePath, string result) { try { Cursor.Current = Cursors.WaitCursor; app = new Excel.Application(); Excel._Workbook workbook = app.Workbooks.Open(formPath); //Excel._Worksheet worksheet = null; Excel._Worksheet worksheet = workbook.Sheets[1]; worksheet = workbook.ActiveSheet; worksheet.Name = "Оцінка якості"; worksheet.Cells.Replace("{OpDateTime}", opDate); worksheet.Cells.Replace("{Ochinuvanyy}", ocinyvanyi); worksheet.Cells.Replace("{CallType}", callType); worksheet.Cells.Replace("{Contragent}", contragent); worksheet.Cells.Replace("{FilePath}", filePath); worksheet.Cells.Replace("{Ocinyvach}", ocinyvach); worksheet.Cells.Replace("{CriticalError}", criticalError); worksheet.Cells.Replace("{Result}", result); int startRow = 14; for (int i = 0; i < dt.Rows.Count; i++) { if (i < dt.Rows.Count - 1) { worksheet.Rows[(startRow + i) + ":" + (startRow + i)].Copy(); worksheet.Rows[(startRow + i + 1) + ":" + (startRow + i + 1)].Insert(); } worksheet.Range["B" + (startRow + i)].Value = dt.Rows[i][0].ToString(); worksheet.Range["D" + (startRow + i)].Value = dt.Rows[i][1].ToString(); worksheet.Range["E" + (startRow + i)].Value = dt.Rows[i][2].ToString(); } worksheet.Rows[7 + ":" + (dt.Rows.Count - 1)].EntireRow().AutoFit(); if (fileFormat.Contains("xlsx")) { workbook.SaveAs(savePath); app.Visible = true; } else if (fileFormat.Contains("pdf")) { workbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, savePath); workbook.Close(false); app.Quit(); System.Diagnostics.Process.Start(savePath); } Cursor.Current = Cursors.Default; //MessageBox.Show("Дані вивантажено!" + Environment.NewLine + savePath, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { if (app.Workbooks.Count == 1) { app.Quit(); } app = null; MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
/// <summary> /// Quit the current application /// </summary> protected void QuitApplication() { if (_application != null) { _application.Quit(); } _application = null; }
public void Close() { try { excelApp.Quit(); } catch { } excelApp = null; }
private static void ExcelClose(Excel._Workbook workbook, Excel._Application application) { var missingObject = System.Reflection.Missing.Value; workbook.Close(false, missingObject, missingObject); application.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(application); GC.Collect(); }
/// <summary> /// Cette méthode permet de supprimer la feuille excel nouvellement créée. /// Ceci nous permet de n'avoir qu'une seule feuille présente dans le SheetPanel. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Office_WorkbookNewSheet(object sender, EventArgs e) { if (this.Office.GetCurrentProgID() == EXCEL_ID) { Excel._Application xlApp = this.Office.GetApplication() as Excel._Application; xlApp.DisplayAlerts = false; Excel._Worksheet workSheet = (Excel._Worksheet)xlApp.ActiveWorkbook.ActiveSheet; //workSheet.Delete(); } }
public RptSummary() { _xlApp = new Microsoft.Office.Interop.Excel.Application(); if (_xlApp == null) { MessageBox.Show("Error (Excel Interop) : cannot start !"); } _xlApp.Visible = true; }
/// <summary> /// Create the Excel object /// </summary> public static void CreateObject() { KeepExcelVisibleForDebugging = SS.I.UserIniFile.ReadBool("ExcelOpKeepExcelVisibleForDebugging", KeepExcelVisibleForDebugging); if (LogCalls) { DebugLog.Message("ExcelOp CreateObject"); } XlApp = new Microsoft.Office.Interop.Excel.Application(); MessageFilter.Register(); // filter messages, e.g. RetryRejectedCall ("Call was rejected by callee") ExcelProcess = GetNewExcelProcess(); XlBooks = XlApp.Workbooks; try // avoid popups that lockup Excel { XlApp.AskToUpdateLinks = false; // XlApp.Workbooks.Open Filename:="C:\Book1withLinkToBook2.xlsx", UpdateLinks:=False also works XlApp.DisplayAlerts = false; // avoid alert popup dialogs //XlApp.AutomationSecurity = // AutomationSecurity normally starts at: msoAutomationSecurityLow // MsoAutomationSecurity.msoAutomationSecurityForceDisable; // setting to ForceDisable won't allow structures to be inserted which is something that we don't want } catch (Exception ex) { ex = ex; } Version version = ExcelOp.GetExcelVersion(); bool visible = XlApp.Visible; StructureInsertCount = 0; if (!KeepExcelVisibleForDebugging) { ShowExcelApp(false); // hide the app //if (version.Major >= 14) // >= Excel 2010 - window visible & no screen updating during sheet build //{ // ExcelOp.HideExcelApp(true); // ExcelOp.ScreenUpdating(false); //} //else // Excel 2007 or earlier - window invisible & no screen updating during sheet build //{ // ExcelOp.HideExcelApp(false); // hide Excel instance // ExcelOp.ScreenUpdating(false); //} } else // make visible for dev/debug { XlApp.Visible = true; XlApp.WindowState = XlWindowState.xlNormal; } return; }
internal void CollectData(IEnumerable <string> years) { _excelApp = new ExcelApp.Application(); foreach (var year in years) { CollectDataForYear(year); } _excelApp.Quit(); }
static ExcelInstance() { Hashtable result = new Hashtable(); IntPtr numFetched = IntPtr.Zero; IRunningObjectTable runningObjectTable; IEnumMoniker monikerEnumerator; IMoniker[] monikers = new IMoniker[1]; GetRunningObjectTable(0, out runningObjectTable); runningObjectTable.EnumRunning(out monikerEnumerator); monikerEnumerator.Reset(); while (monikerEnumerator.Next(1, monikers, numFetched) == 0) { System.Runtime.InteropServices.ComTypes.IBindCtx ctx; CreateBindCtx(0, out ctx); string runningObjectName; monikers[0].GetDisplayName(ctx, null, out runningObjectName); object runningObjectVal; runningObjectTable.GetObject(monikers[0], out runningObjectVal); if (runningObjectVal is XL._Workbook) { XL._Workbook theExcel = (XL._Workbook)runningObjectVal; int theHandle = theExcel.Application.Hwnd; int ProcessId; GetWindowThreadProcessId(theHandle, out ProcessId); if (ProcessId == Process.GetCurrentProcess().Id) { theInstance=theExcel.Application; return; } } if (runningObjectVal is XL._Application) { XL._Application theExcel = (XL._Application)runningObjectVal; int theHandle = theExcel.Hwnd; int ProcessId; GetWindowThreadProcessId(theHandle, out ProcessId); if (ProcessId == Process.GetCurrentProcess().Id) { theInstance = (XL._Application)runningObjectVal; return; } } } throw new Exception("Didn't get a Handle"); }
static void SaveAsCsv(Excel._Application app, string path) { var wb = app.Workbooks.Add(path); foreach (Excel.Worksheet ws in wb.Worksheets) { ws.Activate(); wb.SaveAs(Path.ChangeExtension(Io.File1.AppendSuffix(path, "-" + ws.Name), ".csv"), Excel.XlFileFormat.xlCSV); } app.Workbooks.Close(); }
/// <summary> /// Closes the workbook and the excel application. /// </summary> /// <param name="application">Excel application object</param> public static void Close(this InteropExcel._Application application) { if (application != null) { foreach (InteropExcel.Workbook workbook in application.Workbooks) { workbook.Close(false); } application.Quit(); } }
/// <summary> /// Создание страницы с параметрами техобъектов проекта /// </summary> private static void CreateObjectParamsPage(ref Excel.Worksheet xlWorkSheet, ref Excel._Application xlApp) { xlWorkSheet = xlApp.Sheets.Add(Type.Missing, xlWorkSheet) as Excel.Worksheet; xlWorkSheet.Name = "Параметры объектов"; TreeView tree = TechObject.TechObjectManager.GetInstance().SaveParamsAsTree(); int row = 1; WriteTreeNode(ref xlWorkSheet, tree.Nodes, ref row); xlWorkSheet.Cells.EntireColumn.AutoFit(); xlWorkSheet.Outline.SummaryRow = Excel.XlSummaryRow.xlSummaryAbove; }
/// <summary> /// Создание страницы с итоговыми данными по устройствам /// </summary> private static void CreateTotalDevicePage(ref Excel.Worksheet xlWorkSheet, ref Excel._Application xlApp) { xlWorkSheet = xlApp.Sheets.Add(Type.Missing, xlWorkSheet) as Excel.Worksheet; xlWorkSheet.Name = "Сводная таблица устройств"; object[,] res = Device.DeviceManager.GetInstance().SaveSummaryAsArray(); string endPos = "Q" + res.GetLength(0); xlWorkSheet.Range["A1", endPos].Value2 = res; xlWorkSheet.Cells.EntireColumn.AutoFit(); }
private void ClearClient() { ClientWorkSheetList.Clear(); ExcelListBox.Items.Clear(); ClientConfigSheetList.Clear(); ExcelApp.Quit(); foreach (ExcelItem item in ClientWorkSheetList) { item.Workbook.Close(false, false, Missing.Value); } ExcelApp = new Excel.Application(); }
static ExcelInstance() { Hashtable result = new Hashtable(); IntPtr numFetched = IntPtr.Zero; IRunningObjectTable runningObjectTable; IEnumMoniker monikerEnumerator; IMoniker[] monikers = new IMoniker[1]; GetRunningObjectTable(0, out runningObjectTable); runningObjectTable.EnumRunning(out monikerEnumerator); monikerEnumerator.Reset(); while (monikerEnumerator.Next(1, monikers, numFetched) == 0) { System.Runtime.InteropServices.ComTypes.IBindCtx ctx; CreateBindCtx(0, out ctx); string runningObjectName; monikers[0].GetDisplayName(ctx, null, out runningObjectName); object runningObjectVal; runningObjectTable.GetObject(monikers[0], out runningObjectVal); if (runningObjectVal is XL._Workbook) { XL._Workbook theExcel = (XL._Workbook)runningObjectVal; int theHandle = theExcel.Application.Hwnd; int ProcessId; GetWindowThreadProcessId(theHandle, out ProcessId); if (ProcessId == Process.GetCurrentProcess().Id) { theInstance = theExcel.Application; return; } } if (runningObjectVal is XL._Application) { XL._Application theExcel = (XL._Application)runningObjectVal; int theHandle = theExcel.Hwnd; int ProcessId; GetWindowThreadProcessId(theHandle, out ProcessId); if (ProcessId == Process.GetCurrentProcess().Id) { theInstance = (XL._Application)runningObjectVal; return; } } } throw new Exception("Didn't get a Handle"); }
//关闭Excel public void close() { wbclass.Close(); wbclass = null; excelApp.Quit(); excelApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); }
/// <summary> /// Создание страницы с итоговыми данными по устройствам /// </summary> private static void CreateDeviceConnectionPage(ref Excel.Worksheet xlWorkSheet, ref Excel._Application xlApp) { xlWorkSheet = xlApp.Sheets.Add(Type.Missing, xlWorkSheet) as Excel.Worksheet; xlWorkSheet.Name = "Подключение устройств"; TreeView tree = Device.DeviceManager.GetInstance().SaveConnectionAsTree(); int row = 1; WriteTreeNode(ref xlWorkSheet, tree.Nodes, ref row); xlWorkSheet.Cells.EntireColumn.AutoFit(); xlWorkSheet.Outline.SummaryRow = Excel.XlSummaryRow.xlSummaryAbove; }
public ExcelHelper(string _filename) { if (excelApp == null) { excelApp = new Excel.Application(); } object objOpt = System.Reflection.Missing.Value; wbclass = (Excel.Workbook)excelApp.Workbooks.Open(_filename, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt); excelApp.DisplayAlerts = false; //是否需要显示提示 excelApp.AlertBeforeOverwriting = false; //是否弹出提示覆盖 excelApp.Visible = false; }
public void ProtectSheet(String password = "******") { if (this.Office.GetCurrentProgID() == EXCEL_ID) { Excel._Application xlApp = this.Office.GetApplication() as Excel._Application; xlApp.ActiveSheet.Protect(password, true, false, true, true, true, true, true, false, false, false, false, false, false, false, true); } }
private void button1_Click(object sender, RibbonControlEventArgs e) { Excel._Application app = (Excel._Application)Globals.ThisAddIn.Application; Excel._Workbook wb2 = app.ActiveWorkbook; Type to = typeof(Excel.Application); //object f= to.InvokeMember("ThisWorkbook", BindingFlags.GetProperty, null, app, null); Excel.Workbook wb = (Excel.Workbook)app.ActiveWorkbook; string name = wb.Name; Excel.Workbook addedwb = Globals.ThisAddIn.Application.Workbooks.Add(); addedwb.SaveAs("newwb"); }
//Method to close excel connection private void ExcelClose() { if (xlApp != null) { try { xlWorkBook.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; xlWorkBook = null; } catch (Exception ex) { xlApp = null; MessageBox.Show("Unable to release the Object " + ex.ToString()); } finally { GC.Collect(); } } }
private void Init(ref MSExcel._Workbook wb, out bool success) { try { _excelApp = new MSExcel.Application(); //int[] oldproc = GetPIDs(@"EXCEL"); object oMissiong = System.Reflection.Missing.Value; wb = _excelApp.Workbooks.Open(path, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong); success = true; //获取Excel App的句柄 hwnd = new IntPtr(ExcelApp.Hwnd); //通过Windows API获取Excel进程ID GetWindowThreadProcessId(hwnd, out pid); _desiredName = path; } catch (System.Exception ex) { LogHelper.AddLog(@"异常24", ex.Message, true); success = false; } }
public bool LoadExcelFile() { object objOpt = System.Reflection.Missing.Value; try { wbclass = (Excel.WorkbookClass)excelApp.Workbooks.Open(_fileName, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt); if (wbclass == null) { Program.Logger.ErrorFormat("ExcelHelper , Excel file not exist {0}",_fileName); } return true; } catch (System.Exception ex) { Program.Logger.Error("ExcelHelper , Excel file not exist", ex); excelApp.Quit(); excelApp = null; } return false; }
public ExcelHelper(string filename) { _fileName = filename; outstring = ""; excelApp = new Excel.Application(); }
public void Close() { excelApp.Quit(); excelApp = null; }
//private bool createJobCostingSheet(ServiceSubmissionModel submission, string filename) private int createJobCostingSheet(ServiceSheetViewModel submission, string filename, bool firstSubmission, int lineNumber) { Excel.Range range; //First Open excel if (firstSubmission) { excelApplication = new Excel.Application(); excelApplication.Visible = true; excelWorkbook = excelApplication.Workbooks.Open(filename); excelWorksheet = excelWorkbook.ActiveSheet; //Add the job title string customer = submission.Customer; string machineMake = submission.MachineMakeModel; string serialNumber = submission.MachineSerial; string jobDescription = submission.JobDescription; range = excelWorksheet.Cells[13, 1]; range.Value2 = string.Concat(customer, " - ", machineMake, " - S/N: ", serialNumber, " - ", jobDescription); range = excelWorksheet.Cells[11, 9]; range.Value2 = submission.MttJobNumber; } //Load all the days and loop through them. Output to the sheet AllServiceDayViewModels serviceDays = submission.AllServiceDays; //Need the engineers initials for each row string engFirstName = submission.UserFirstName; string engSurname = submission.UserSurname; string initials = string.Concat(engFirstName[0], engSurname[0]); int sheetNo = submission.SubmissionNumber; int currentSpreadsheetRow; if (lineNumber == -1) { currentSpreadsheetRow = 17; } else { currentSpreadsheetRow = lineNumber++; } foreach (ServiceDayViewModel currentDay in serviceDays.AllServiceDayVMs) { range = excelWorksheet.Cells[currentSpreadsheetRow, 1]; range.Value2 = currentDay.DtReport; string day = currentDay.DtReport.DayOfWeek.ToString(); range = excelWorksheet.Cells[currentSpreadsheetRow, 2]; range.Value2 = day; double standardHours = calculateStandardHours(currentDay); range = excelWorksheet.Cells[currentSpreadsheetRow, 3]; range.Value2 = standardHours; double overtimeHours = calculateOvertimeHours(currentDay); range = excelWorksheet.Cells[currentSpreadsheetRow, 4]; range.Value2 = overtimeHours; range = excelWorksheet.Cells[currentSpreadsheetRow, 5]; range.Value2 = currentDay.TotalTravelTime; bool dailyAllowance = currentDay.DailyAllowance; int dailyAllowanceValue = convertBoolToIntForAllowances(dailyAllowance); range = excelWorksheet.Cells[currentSpreadsheetRow, 6]; range.Value2 = dailyAllowanceValue; bool overNight = currentDay.OvernightAllowance; int overnightAllowanceValue = convertBoolToIntForAllowances(overNight); range = excelWorksheet.Cells[currentSpreadsheetRow, 7]; range.Value2 = overnightAllowanceValue; range = excelWorksheet.Cells[currentSpreadsheetRow, 8]; range.Value2 = currentDay.Mileage; range = excelWorksheet.Cells[currentSpreadsheetRow, 9]; range.Value2 = initials; range = excelWorksheet.Cells[currentSpreadsheetRow, 10]; range.Value2 = sheetNo; currentSpreadsheetRow++; } MessageBox.Show("Need to handle bank holidays"); return currentSpreadsheetRow; }
public void OnStartupComplete(ref System.Array custom) { CommandBars oCommandBars; CommandBar oStandardBar; object omissing = System.Reflection.Missing.Value; Thread.Sleep(50); app = (Microsoft.Office.Interop.Excel._Application)applicationObject; try { oCommandBars = (CommandBars)applicationObject.GetType().InvokeMember("CommandBars", BindingFlags.GetProperty, null, applicationObject, null); } catch (Exception) { return; } // Set up a custom button on the "Standard" commandbar. try { oStandardBar = oCommandBars["Worksheet Menu Bar"]; } catch (Exception) { return; } CommandBarControls controls = oStandardBar.Controls; // remove old menus... foreach (CommandBarControl control in controls) { String caption = control.Caption; if (caption.EndsWith("Remote Documents") || caption.EndsWith("Remote documents")) { control.Delete(null); } } // In case the button was not deleted, use the exiting one. try { eXoMenu = (CommandBarPopup)oStandardBar.Controls["Remote documents"]; Open = (CommandBarButton)eXoMenu.Controls["Open"]; Save = (CommandBarButton)eXoMenu.Controls["Save"]; SaveAs = (CommandBarButton)eXoMenu.Controls["SaveAs"]; Search = (CommandBarButton)eXoMenu.Controls["Search"]; Settings = (CommandBarButton)eXoMenu.Controls["Settings"]; About = (CommandBarButton)eXoMenu.Controls["About"]; } catch (Exception) { eXoMenu = (CommandBarPopup)oStandardBar.Controls.Add(MsoControlType.msoControlPopup, omissing, omissing, omissing, true); eXoMenu.Caption = "Remote Documents"; eXoMenu.Tag = eXoMenu.Caption; Open = (CommandBarButton)eXoMenu.Controls.Add(1, omissing, omissing, omissing, omissing); Open.Caption = "Open..."; Open.Tag = Open.Caption; Save = (CommandBarButton)eXoMenu.Controls.Add(1, omissing, omissing, omissing, omissing); Save.Caption = "Save"; Save.Tag = Save.Caption; SaveAs = (CommandBarButton)eXoMenu.Controls.Add(1, omissing, omissing, omissing, omissing); SaveAs.Caption = "Save As..."; SaveAs.Tag = SaveAs.Caption; Search = (CommandBarButton)eXoMenu.Controls.Add(1, omissing, omissing, omissing, omissing); Search.Caption = "Search..."; Search.Tag = Search.Caption; Settings = (CommandBarButton)eXoMenu.Controls.Add(1, omissing, omissing, omissing, omissing); Settings.Caption = "Settings..."; Settings.Tag = Settings.Caption; About = (CommandBarButton)eXoMenu.Controls.Add(1, omissing, omissing, omissing, omissing); About.Caption = "About..."; About.Tag = About.Caption; } Open.Visible = true; Open.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.Open_Click); Save.Visible = true; Save.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.Save_Click); SaveAs.Visible = true; SaveAs.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.SaveAs_Click); Search.Visible = true; Search.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.Search_Click); Settings.Visible = true; Settings.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.Settings_Click); About.Visible = true; About.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.About_Click); object oName = applicationObject.GetType().InvokeMember("Name", BindingFlags.GetProperty, null, applicationObject, null); oStandardBar = null; oCommandBars = null; clearRepository(); }
/// <summary> /// 释放所引用的COM对象。注意:这个过程一定要执行。 /// </summary> public void DisposeExcel(ref MSExcel._Workbook _excelDoc) { ReleaseObj(_excelDoc); ReleaseObj(_excelApp); _excelDoc = null; _excelApp = null; System.GC.Collect(); System.GC.WaitForPendingFinalizers(); System.Threading.Thread.Sleep(1000); //强制结束Excel进程 if (pid > 0) //_excelApp != null && { int ExcelProcess = OpenProcess(PROCESS_VM_READ | PROCESS_VM_WRITE, false, pid); //判断进程是否仍然存在 if (ExcelProcess > 0) { try { //通过进程ID,找到进程 System.Diagnostics.Process process = System.Diagnostics.Process.GetProcessById(pid); //Kill 进程 process.Kill(); } catch { //强制结束Excel进程失败,可以记录一下日志. //AddLog(@"退出", " 结束进程失败,异常:" + ex.Message, false); //AddLog(@"退出", " " + ex.TargetSite, false); } } else { //进程已经结束了 } } }