locked
How to open Excel FIles RRS feed

  • Question

  • I am a novice

    When I open excel file  I found some problems.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Reflection;
    using System.Windows.Forms;
    using XLS=Microsoft.Office.Interop.Excel;
    using WD=Microsoft.Office.Interop.Word;
    using Microsoft.Office.Core;

    namespace ExcelDataFillWord
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            private void button1_Click(object sender, EventArgs e)
            {

                //  Scheduler sched = new Scheduler();
                //foreach (Task t in sched.Tasks)
                //{
                //    Console.WriteLine(t.ToString());
                //    foreach (Trigger tr in t.Triggers)
                //        Console.WriteLine(tr.ToString());
                //}
            }

            private void label1_Click(object sender, EventArgs e)
            {
                FileDialog.Filter = "excel文件|*.xls|Excel文件|*.xlsx";
                FileDialog.Multiselect = false;
                FileDialog.Title = "打开excel文件";

                if (FileDialog.ShowDialog() == DialogResult.OK)
                {
                    textBox1.Text = FileDialog.FileName;
                    object missing = System.Reflection.Missing.Value;
                    //object missing = Type.Missing;
                    CloseProcess();

                   //'Excel::Application excel =new Excel::ApplicationClass();
                    Microsoft.Office.Interop.Excel.Application excel= new Microsoft.Office.Interop.Excel.Application();
                    //Excel::Workbook  Wk =(Excel::Workbook )Xls.Workbooks.Open(textBox1.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                    Microsoft.Office.Interop.Excel.Workbook workbook =(Microsoft.Office.Interop.Excel.Workbook) excel.Application.Workbooks.Open(textBox1.Text , missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);

                    //comboBox1.Items.Clear();
                    //foreach (Excel::Worksheet Sh in Wk.Worksheets)
                    //{
                    //    comboBox1.Items.Add((Sh.Name).ToString());
                    //}
                    workbook.Close(missing, missing, missing);
                    excel.Quit();
                }
            }
            private void CloseProcess()
            {
                System.Diagnostics.Process[] excelProcess = System.Diagnostics.Process.GetProcessesByName("EXCEL");//实例化进程对象
                foreach (System.Diagnostics.Process p in excelProcess)
                    p.Kill();//关闭进程
                System.Threading.Thread.Sleep(10);//使线程休眠10毫秒
            }
        }

    problem is : can't change  excel.applicaitonclass's Com object   to   interface

    so I want to ask a question:

    excel.application is a interface ,then interface variant = a class( it inherients this interface)  object

    but interface is a abstract object ,It has no construct function . why We use  Excel.Applcation xsl=new Excel.Application();

    Friday, February 6, 2015 2:04 AM

Answers

  • try this code :>

    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel; 
    
    namespace WindowsApplication1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                Excel.Application xlApp ;
                Excel.Workbook xlWorkBook ;
                Excel.Worksheet xlWorkSheet ;
                object misValue = System.Reflection.Missing.Value;
    
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    
                MessageBox.Show(xlWorkSheet.get_Range("A1","A1").Value2.ToString());
    
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
    
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
            }
    
            private void releaseObject(object obj)
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception ex)
                {
                    obj = null;
                    MessageBox.Show("Unable to release the Object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            } 
        }
    }


    Mark as answer or vote as helpful if you find it useful | Ammar Zaied [MCP]

    • Marked as answer by Caillen Sunday, March 1, 2015 12:11 PM
    Tuesday, February 10, 2015 10:11 AM

All replies

  • Hi Tinytree,

    If you select the Application class name, right click and select "Go to Definition" or press F12, you'll see the definition of the Application interface:

    #region Assembly Microsoft.Office.Interop.Excel.dll, v2.0.50727
    // C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Office.Interop.Excel.dll
    #endregion
    
    using System.Runtime.InteropServices;
    
    namespace Microsoft.Office.Interop.Excel
    {
        // Summary:
        //     Represents the entire Microsoft Excel application.
        [Guid("000208D5-0000-0000-C000-000000000046")]
        [CoClass(typeof(ApplicationClass))]
        public interface Application : _Application, AppEvents_Event
        {
        }
    }
    

    Notice that interface has an Attribute named CoClass, this is exactly the answer for your question. In ordinary circomstances, we don't recommend that you use this attribute in your own interfaces. It's usually used in COM interop. This attribute will give the developer an ability to initialize an interface with a specified "concrete class" by default. In this case, the "concrete class" is ApplicationClass. So the code is the same with this line:

    Application app = new ApplicationClass();


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Sunday, February 8, 2015 2:04 AM
  • Hello,

    Perhaps this will help. Open an Excel file, select a sheet, range then set a value, save then close. The idea here is not to write or save but demo a few basic operations.

    Also note 'as is' all memory is cleaned up without resorting to what many do, force the garbage collector. This is important if objects are not cleaned up it is very possible in extreme cases to bring a machine down so one should ensure all objects are cleaned up which only comes from well written code be it C# or VB.NET

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Runtime.InteropServices;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    
    namespace Example_C1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            public void OpenExcelExample(string FileName, string SheetName)
            {
                if (System.IO.File.Exists(FileName))
                {
                    bool Proceed = false;
                    Excel.Application xlApp = null;
                    Excel.Workbooks xlWorkBooks = null;
                    Excel.Workbook xlWorkBook = null;
                    Excel.Worksheet xlWorkSheet = null;
                    Excel.Sheets xlWorkSheets = null;
                    Excel.Range xlCells = null;
                    xlApp = new Excel.Application();
                    xlApp.DisplayAlerts = false;
                    xlWorkBooks = xlApp.Workbooks;
                    xlWorkBook = xlWorkBooks.Open(FileName);
                    xlApp.Visible = false;
                    xlWorkSheets = xlWorkBook.Sheets;
    
                    for (int x = 1; x <= xlWorkSheets.Count; x++)
                    {
                        xlWorkSheet = (Excel.Worksheet)xlWorkSheets.get_Item(x);
    
                        if (xlWorkSheet.Name == SheetName)
                        {
                            Proceed = true;
                            break;
                        }
                        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
                        xlWorkSheet = null;
                    }
    
                    if (Proceed)
                    {
                        Excel.Range xlRange1 = null;
                        xlRange1 = xlWorkSheet.get_Range("A1");
                        xlRange1.Value = "Hello";
                        Marshal.FinalReleaseComObject(xlRange1);
                        xlRange1 = null;
                        xlWorkSheet.SaveAs(FileName);
                    }
                    else
                    {
                        MessageBox.Show(SheetName + " not found.");
                    }
                    xlWorkBook.Close();
                    xlApp.UserControl = true;
                    xlApp.Quit();
                    ReleaseComObject(xlCells);
                    ReleaseComObject(xlWorkSheets);
                    ReleaseComObject(xlWorkSheet);
                    ReleaseComObject(xlWorkBook);
                    ReleaseComObject(xlWorkBooks);
                    ReleaseComObject(xlApp);
                    MessageBox.Show("Done");
                }
                else
                {
                    MessageBox.Show("'" + FileName + "' not located. Try one of the write examples first.");
                }
            }
    
            private void ReleaseComObject(object obj)
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception)
                {
                    obj = null;
                }
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                OpenExcelExample(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "WS1.xlsx"), "Sheet3");
            }
    
            private void releaseObject(object obj)
            {
                try
                {
                    if (obj == null)
                    {
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                        obj = null;                    
                    }
    
                }
                catch (Exception ex)
                {
                    obj = null;
                    MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            }
    
        }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Sunday, February 8, 2015 11:27 AM
    • Proposed as answer by chanmmMVP Sunday, February 8, 2015 2:25 PM
    Sunday, February 8, 2015 2:25 PM
  • First ,thank you for you reply, sometime  I use vba try to solute  this .but I meet another problem.

    In my system. I install office 2007,later ,I install office 2010. in my vba

    for example I use word vba 

    first I  refer excel 14.0

    dim xls as excel.application 

    set xls=new excel.application

    sometimes  It will  not work  in set xls =new excel.application

    but I modify it to this ,it will work

    set xls=createobject("excel.application")

    Probelm : What's difference between Using New  and createobject

    Tuesday, February 10, 2015 3:01 AM
  • try this code :>

    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel; 
    
    namespace WindowsApplication1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                Excel.Application xlApp ;
                Excel.Workbook xlWorkBook ;
                Excel.Worksheet xlWorkSheet ;
                object misValue = System.Reflection.Missing.Value;
    
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    
                MessageBox.Show(xlWorkSheet.get_Range("A1","A1").Value2.ToString());
    
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
    
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
            }
    
            private void releaseObject(object obj)
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception ex)
                {
                    obj = null;
                    MessageBox.Show("Unable to release the Object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            } 
        }
    }


    Mark as answer or vote as helpful if you find it useful | Ammar Zaied [MCP]

    • Marked as answer by Caillen Sunday, March 1, 2015 12:11 PM
    Tuesday, February 10, 2015 10:11 AM