Exemplo n.º 1
0
        /// <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);
            }
        }
Exemplo n.º 2
0
 public ExcelClass()
 {
     if (m_ExcelApplication == null)
     {
         m_ExcelApplication = new Excel.Application();
     }
 }
Exemplo n.º 3
0
        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();
            }
        }
Exemplo n.º 4
0
        //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();
            }
        }
Exemplo n.º 5
0
            /// <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);
            }
Exemplo n.º 6
0
        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);
        }
Exemplo n.º 7
0
        //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();
            }
        }
Exemplo n.º 8
0
        /// <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);
        }
Exemplo n.º 9
0
 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);
 }
Exemplo n.º 10
0
        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();
            }
        }
Exemplo n.º 11
0
 public static string MessageInStatusBar(
     [Parameter("The String ")] string Message
     )
 {
     XL._Application theApp = ExcelInstance.Instance();
     theApp.StatusBar = Message;
     return("--");
 }
Exemplo n.º 12
0
 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));
 }
Exemplo n.º 13
0
 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;
 }
Exemplo n.º 14
0
 /// <summary>
 /// 释放
 /// </summary>
 public void Close()
 {
     if (ExcelApp != null)
     {
         ExcelApp.Quit();
         ExcelApp = null;
     }
 }
Exemplo n.º 15
0
 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;
 }
Exemplo n.º 16
0
        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);
            }
        }
Exemplo n.º 17
0
        /// <summary>
        /// Quit the current application
        /// </summary>
        protected void QuitApplication()
        {
            if (_application != null)
            {
                _application.Quit();
            }

            _application = null;
        }
Exemplo n.º 18
0
 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();
        }
Exemplo n.º 20
0
 /// <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();
     }
 }
Exemplo n.º 21
0
            public RptSummary()
            {
                _xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (_xlApp == null)
                {
                    MessageBox.Show("Error (Excel Interop) : cannot start !");
                }

                _xlApp.Visible = true;
            }
Exemplo n.º 22
0
        /// <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;
        }
Exemplo n.º 23
0
        internal void CollectData(IEnumerable <string> years)
        {
            _excelApp = new ExcelApp.Application();

            foreach (var year in years)
            {
                CollectDataForYear(year);
            }

            _excelApp.Quit();
        }
Exemplo n.º 24
0
        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");
        }
Exemplo n.º 25
0
        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();
        }
Exemplo n.º 26
0
        /// <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();
            }
        }
Exemplo n.º 27
0
        /// <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;
        }
Exemplo n.º 28
0
        /// <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();
        }
Exemplo n.º 29
0
 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();
 }
Exemplo n.º 30
0
        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");
        }
Exemplo n.º 31
0
        //关闭Excel
        public void close()
        {
            wbclass.Close();
            wbclass = null;
            excelApp.Quit();
            excelApp = null;

            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
Exemplo n.º 32
0
        /// <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;
        }
Exemplo n.º 33
0
        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;
        }
Exemplo n.º 34
0
        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);
            }
        }
Exemplo n.º 35
0
        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");
        }
Exemplo n.º 36
0
 //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();
         }
     }
 }
Exemplo n.º 37
0
        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;
            }
        }
Exemplo n.º 38
0
    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;
    }
Exemplo n.º 39
0
 public ExcelHelper(string filename)
 {
     _fileName = filename;
     outstring = "";
     excelApp = new Excel.Application();
 }
Exemplo n.º 40
0
 public void Close()
 {
     excelApp.Quit();
     excelApp = null;
 }
Exemplo n.º 41
0
        //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;
        }
Exemplo n.º 42
0
        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();
        }
Exemplo n.º 43
0
        /// <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
                {
                    //进程已经结束了
                }
            }
        }