愚见未来

人的思想时时刻刻都在进步,如果你早上起床,想起昨天所做的事情是那么幼稚和迂腐,那么恭喜你,你又变得成熟一点了!
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

      最近项目上有导入excel的需求,其实导入一个固定格式的excel数据非常容易,但是,发现一个问题就是,导入excel后,用户在打开excel时,必须要打开2次才能打开excel,这让用户很不爽;开始查找原因:excel的进程关闭不了,资源不能释放,于是就想尽办法释放com对象excel实例,在博客园就找到一篇文章,所以就做个学习笔记了。

public class ExcelHelper
    {
        #region 成员变量
        private string templetFile = null;
        private string outputFile = null;
        private object missing = Missing.Value;
        private DateTime beforeTime;            //Excel启动之前时间
        private DateTime afterTime;                //Excel启动之后时间
        Microsoft.Office.Interop.Excel.Application app;
        Microsoft.Office.Interop.Excel.Workbook workBook;
        Microsoft.Office.Interop.Excel.Worksheet workSheet;
        Microsoft.Office.Interop.Excel.Range range;
        Microsoft.Office.Interop.Excel.Range range1;
        Microsoft.Office.Interop.Excel.Range range2;
        Microsoft.Office.Interop.Excel.TextBox textBox;
        private int sheetCount = 1;            //WorkSheet数量
        private string sheetPrefixName = "页";
        #endregion

        #region 公共属性
        /// <summary>
        /// WorkSheet前缀名,比如:前缀名为“页”,那么WorkSheet名称依次为“页-1,页-2...”
        /// </summary>
        public string SheetPrefixName
        {
            set { this.sheetPrefixName = value; }
        }

        /// <summary>
        /// WorkSheet数量
        /// </summary>
        public int WorkSheetCount
        {
            get { return workBook.Sheets.Count; }
        }

        /// <summary>
        /// Microsoft.Office.Interop.Excel模板文件路径
        /// </summary>
        public string TempletFilePath
        {
            set { this.templetFile = value; }
        }

        /// <summary>
        /// 输出Excel文件路径
        /// </summary>
        public string OutputFilePath
        {
            set { this.outputFile = value; }
        }
        #endregion

        public static void MakeExcelDocs(string sourcepath, string destinpath, string newname)
        {
            destinpath = destinpath + "\\" + newname + ".xls";
            // Ensure that the target does not exist.
            File.Delete(destinpath);
            // Copy the file.
            File.Copy(sourcepath, destinpath);
            // Try to copy the same file again, which should succeed.
            File.Copy(sourcepath, destinpath, true);

        }

        public static void OpenExcelDocs(string filename,string[] content)
        {

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //引用Excel对象
            Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Open(filename,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing
                ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);   //引用Excel工作簿
            Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets.get_Item(1); ;  //引用Excel工作页面
            excel.Visible = false;     

            string[] newcontent = StringHelper.Modify(content);

            sheet.Cells[10, 3] = newcontent[0];
            sheet.Cells[11, 3] = newcontent[1];
            sheet.Cells[12, 3] = newcontent[2];
            sheet.Cells[13, 3] = newcontent[3];
            sheet.Cells[14, 3] = newcontent[4];
            sheet.Cells[15, 3] = newcontent[5];
            sheet.Cells[16, 3] = newcontent[6];
            sheet.Cells[17, 3] = newcontent[7];
           

            book.Save();
            book.Close(Type.Missing, Type.Missing, Type.Missing);
            excel.Quit();

            IntPtr t = new IntPtr(excel.Hwnd);
            int k = 0;
            GetWindowThreadProcessId(t, out k);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();

            //sheet = null;
            //book = null;
            //excel = null

            //System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        }

        [DllImport("User32.dll",   CharSet   =   CharSet.Auto)]    
        public static extern  int  GetWindowThreadProcessId(IntPtr   hwnd,   out   int   ID);     
        public static void OpenExcelDocs2(string filename, double[] content)
        {

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //引用Excel对象
            Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);   //引用Excel工作簿
            Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets.get_Item(1); ;  //引用Excel工作页面
            excel.Visible = false;

            sheet.Cells[24, 3] = content[1];
            sheet.Cells[25, 3] = content[0];

            book.Save();
            book.Close(Type.Missing, Type.Missing, Type.Missing);
            excel.Quit();  //应用程序推出,但是进程还在运行

            IntPtr t = new IntPtr(excel.Hwnd);          //杀死进程的好方法,很有效
            int k = 0;
            GetWindowThreadProcessId(t, out k);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();

            //sheet = null;
            //book = null;
            //excel = null;   //不能杀死进程
 
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);  //可以释放对象,但是不能杀死进程
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

             
        }

    }

 

原文引自:http://www.cnblogs.com/zhangjun1130/archive/2010/12/06/1897717.html

 

下面是我自己用到的代码,记下来以备后用。

 

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //引用Excel对象
            Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Open(textBox1.Text.Trim(), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);   //引用Excel工作簿
            Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets.get_Item(1); ;  //引用Excel工作页面
            excel.Visible = false;

            //读取excel内容代码部分

 

            book.Save();
            book.Close(Type.Missing, Type.Missing, Type.Missing);
            excel.Quit();

            IntPtr t = new IntPtr(excel.Hwnd);
            int k = 0;
            GetWindowThreadProcessId(t, out k);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();

 

而且 在上述代码的方法上面,需要导入win32组件,代码如下:

[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out   int ID);