C# Excel 导入导出操作类

  1 public class ExcelUtil : IDisposable
  2    {
  3        //是否已经释放资源的标记
  4
  5        private bool disposed = false;
  6
  7        private Excel.Application m_objExcel = null;
  8        private Excel.Workbooks m_objBooks = null;
  9        private Excel._Workbook m_objBook = null;
 10        private Excel.Sheets m_objSheets = null;
 11        private Excel._Worksheet m_objSheet = null;
 12        private Excel.Range m_objRange = null;
 13        private DateTime ExcelTime = new DateTime();
 14        private int ExcelID = 0;
 15        private object m_objOpt = System.Reflection.Missing.Value;
 16
 17
 18       
 19        /**//// <summary>
 20        /// 打开没有模板的操作。
 21
 22
 23        /// </summary>
 24        public void Open()
 25        {
 26            this.Open(String.Empty);
 27        }
 28
 29        /**//// <summary>
 30        /// 功能：实现Excel应用程序的打开
 31        /// </summary>
 32        /// <param name="TemplateFilePath">模板文件物理路径</param>
 33        public void Open(string TemplateFilePath)
 34        {
 35            //打开对象
 36            m_objExcel = new Excel.Application();
 37
 38           
 39            m_objExcel.Visible = false;
 40            m_objExcel.DisplayAlerts = false;
 41
 42            
 43
 44            m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
 45            if (TemplateFilePath.Equals(String.Empty))
 46            {
 47                m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
 48            }
 49            else
 50            {
 51
 52                if (File.Exists(TemplateFilePath))
 53                {
 54                    m_objBook = m_objBooks.Open(TemplateFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
 55                m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
 56                }
 57                else
 58                {
 59                    //Add a new workbook to the file
 60                    m_objBook = m_objExcel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
 61                }
 62
 63
 64            }
 65            m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
 66            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
 67                     }
 70
 71        private void m_objExcel_WorkbookBeforeClose(Excel.Workbook m_objBooks, ref bool _Cancel)
 72        {
 73            //MessageBox.Show("保存完毕！");
 74        }
 75
 76
 77
 78       
 79
 80
 81   
 82
 83
 84       
 85        /**//// <summary>
 86        /// 给单元格赋值
 87
 88
 89        /// </summary>
 90        /// <param name="BeginRangeName"></param>
 91        /// <param name="EndRangeName"></param>
 92        /// <param name="value"></param>
 93        public void SetCellsValue(string BeginRangeName, string EndRangeName, string value)
 94        {
 95            try
 96            {
 97                Excel.Range excelCell = (Excel.Range)m_objSheet.get_Range(BeginRangeName, EndRangeName);
 98
 99                excelCell.Value2 = value;

            }
            catch (Exception e)
            {

                throw e;
            }
        }

       
       
        /**//// <summary>
        /// 将Excel文件保存到指定的目录，目录必须事先存在，文件名称不一定要存在。


        /// </summary>
        /// <param name="OutputFilePath">要保存成的文件的全路径。</param>
        public void SaveFile(string OutputFilePath)
        {
                        m_objExcel.UserControl = false;
            
            m_objBook.SaveAs(OutputFilePath, m_objOpt, m_objOpt,
              m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
              m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);


                   }

        private void killExcel()
        {
            try
            {
                Process[] ps = Process.GetProcesses();


                foreach (Process p in ps)
                {
                    if (p.ProcessName.ToLower().Equals("excel"))
                    {

                        if (p.Id == ExcelID)
                        {
                            p.Kill();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //MessageBox.Show("ERROR " + ex.Message);
            }
        }

        /**//// <summary>
        /// 关闭应用程序
        /// </summary>
        public void Close()
        {
            //m_objBook.Close(false, m_objOpt, m_objOpt);
            //m_objExcel.Quit();

            try
            {
                Dispose();
            }
            catch (Exception e)
            {

                throw e;
            }
        }

        /**//// <summary>
        /// 释放所引用的COM对象。注意：这个过程一定要执行。


        /// </summary>
        public void Dispose()
        {
            try
            {
                Dispose(true);

                //告诉垃圾回收器,资源已经被回收


                GC.SuppressFinalize(this);

                //ReleaseObj(m_objSheets);
                //ReleaseObj(m_objBook);
                //ReleaseObj(m_objBooks);
                //ReleaseObj(m_objExcel);
                //System.GC.Collect();
                //System.GC.WaitForPendingFinalizers();

                //Dispose(false);
            }
            catch (Exception e)
            {

                throw e;
            }
        }

        /**//// <summary>
        /// 释放资源
        /// </summary>
        /// <param name="disposing"></param>
        protected virtual void Dispose(bool disposing)
        {
            try
            {
                if (!disposed)
                {
                    if (disposing)
                    {


                        //托管资源的释放 
                                          //m_objBooks.Close();
                        if (m_objOpt != null && m_objOpt != null)
                        {
                            m_objBook.Close(false, m_objOpt, m_objOpt);
                        }

                        //m_objExcel.ActiveWorkbook.Close();

                        if (m_objRange != null && m_objSheet != null && m_objSheets != null && m_objBook != null && m_objBooks != null && m_objExcel != null)
                        {
                            ReleaseObj(m_objRange);
                            ReleaseObj(m_objSheet);
                            ReleaseObj(m_objSheets);
                            ReleaseObj(m_objBook);
                            ReleaseObj(m_objBooks);
                            m_objExcel.Quit();
                            ReleaseObj(m_objExcel);
                        }


                        m_objRange = null;
                        m_objSheet = null;
                        m_objSheets = null;
                        m_objBook = null;
                        m_objBooks = null;

                        int generation = GC.GetGeneration(m_objExcel);
                        System.GC.Collect(generation);

                    }
                    //非托管资源的释放
                      //killExcel();
                }

                disposed = true;



            }
            catch (Exception e)
            {

                throw e;
            }
        }

        /**//// <summary>
        /// 析构函数
        /// </summary>
        ~ExcelUtil()
        {
            try
            {
                Dispose(false);
            }
            catch (Exception e)
            {

                throw e;
            }

        }

        /**//// <summary>
        /// 释放对象，内部调用


        /// </summary>
        /// <param name="o"></param>
        private void ReleaseObj(object o)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
            catch { }
            finally { o = m_objOpt; }
        }

        /**//// <summary>
        /// 删除指定sheet下的指定行


        /// </summary>
        /// <param name="SheetIndex">sheet索引</param>
        /// <param name="DeleteRowIndex">行索引</param>
        public void DeleteRow(int SheetIndex, int DeleteRowIndex)
        {
            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(SheetIndex));
            m_objRange = (Excel.Range)m_objSheet.Rows[DeleteRowIndex, System.Reflection.Missing.Value];
            m_objRange.EntireRow.Delete(Excel.XlDirection.xlToRight);
        }

        /**//// <summary>
        /// 删除指定sheet下的指定列


        /// </summary>
        /// <param name="SheetIndex">sheet索引</param>
        /// <param name="DeleteColumnIndex">列索引</param>
        public void DeleteColumn(int SheetIndex, int DeleteColumnIndex)
        {
            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(SheetIndex));
            m_objRange = (Excel.Range)m_objSheet.Columns[DeleteColumnIndex, System.Reflection.Missing.Value];
            m_objRange.EntireColumn.Delete(Excel.XlDirection.xlDown);
        }

      
        /**//// <summary>
        /// DataTable导入excel
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="Vx">横坐标</param>
        /// <param name="Vy">纵坐标</param>
        ///  <param name="isuprightness">是否竖排</param>
        public void ConvertDateTableToExcel(System.Data.DataTable dt, int Vx, int Vy, bool isuprightness)
        {

            try
            {
                //竖排
                if (isuprightness)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            m_objRange = (Excel.Range)m_objSheet.Cells[j + Vy, i + Vx];

                            if (i == 0)
                            {
                                //ApplyStyle();
                            }

                            m_objRange.Value2 = dt.Rows[i][j].ToString();

                        }

                    }
                }
                else
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            m_objRange = (Excel.Range)m_objSheet.Cells[i + Vx, j + Vy];

                            if (i == 0)
                            {
                                //ApplyStyle();
                            }

                            m_objRange.Value2 = dt.Rows[i][j].ToString();

                        }

                    }
                }
            }
            catch (Exception e)
            {

                throw e;
            }



        }

            
        /**//// <summary>
        /// 设置列宽
        /// </summary>
        /// <param name="columnIndex">列序号</param>
        public void SetColumnsWidth(int columnIndex, int width)
        {
            try
            {
                m_objRange = (Excel.Range)m_objSheet.Cells[columnIndex, Type.Missing];
                m_objRange.ColumnWidth = width;

            }
            catch (Exception e)
            {

                throw e;
            }

        }


        /**//// <summary>
        /// 加密
        /// </summary>
        /// <param name="passWord">密码</param>
        public void SetPassWord(string passWord)
        {
            try
            {
                m_objBook.Password = passWord;
            }
            catch (Exception e)
            {

                throw e;
            }

        }



        /**//// <summary>
        /// 拷贝指定行到指定行

        /// </summary>
        /// <param name="SheetIndex"></param>
        /// <param name="CopyRowIndex">拷贝行号</param>
        /// <param name="InsertRowIndex">要插入行号</param>
        public void InsertRow(int SheetIndex, int CopyRowIndex, int InsertRowIndex)
        {
            try
            {
                m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(SheetIndex));
                m_objRange = (Excel.Range)m_objSheet.Rows[CopyRowIndex, System.Reflection.Missing.Value];
                Excel.Range inserRange = (Excel.Range)m_objSheet.Rows[InsertRowIndex, System.Reflection.Missing.Value];
                inserRange.Insert(Type.Missing, m_objRange.Copy(Type.Missing));
            }
            catch (Exception e)
            {

                throw e;
            }


        }


        /**//// <summary>
        /// 设置光标位置
        /// </summary>
        /// <param name="passWord">密码</param>
        public void Excel_Select(int x, int y)
        {
            try
            {
                m_objRange = (Excel.Range)m_objSheet.Cells[x, y];
                m_objRange.Select();
            }
            catch (Exception e)
            {

                throw e;
            }

        }




       
       
    }

在网上找的源码的基础上修改，在此感谢原创者（忘了原创者是谁）