C#操作EXCEL类

C#操作EXCEL类  

使用前先添加引用啊，而且最后不用杀死excel进程就能关闭excel对象，释放资源。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;

namespace BLL
{
    public class ExcelEdit
    {
        private Excel.Application excelApp = null;
        private Excel.Workbooks excelWorkBooks = null;

        public Excel.Workbooks ExcelWorkBooks
        {
            get { return excelWorkBooks; }
            set { excelWorkBooks = value; }
        }
        private Excel.Workbook excelWorkBook = null;

        public Excel.Workbook ExcelWorkBook
        {
            get { return excelWorkBook; }
            set { excelWorkBook = value; }
        }
       // private Excel.Worksheets excelWorkSheets = null;
        private Excel.Worksheet excelWorkSheet = null;

        public Excel.Worksheet ExcelWorkSheet
        {
            get { return excelWorkSheet; }
            set { excelWorkSheet = value; }
        }
        object oMissing = Missing.Value;

        public ExcelEdit()
        {
            if (excelApp==null)
            {
                excelApp = new Microsoft.Office.Interop.Excel.Application();
            }
        }

        /// <summary>
        /// 创建一个excel对象
        /// </summary>
        public void CreateExcelApp()
        {
            //excelApp.Visible = true;
            excelWorkBooks = excelApp.Workbooks;
            excelWorkBook = excelWorkBooks.Add(true);
            excelWorkSheet = excelWorkBook.Worksheets[1] as Excel.Worksheet;

        }

        /// <summary>
        /// 显示excel工作薄
        /// </summary>
        public void ShowExcelApp()
        {
            excelApp.Visible = true;
        }
        /// <summary>
        /// 打开工作薄
        /// </summary>
        /// <param name="fileName">文件名</param>
        public void Open(string fileName)
        {
            excelWorkBooks = excelApp.Workbooks;
            excelWorkBook = excelWorkBooks.Add(fileName);
            excelWorkSheet = excelWorkBook.Worksheets[1] as Excel.Worksheet;

        }

        /// <summary>
        /// 根据工作薄返回工作表的个数
        /// </summary>
        /// <param name="wb">工作薄</param>
        /// <returns></returns>
        public int GetWorkSheetsCount()
        {
            try
            {
                if (excelWorkBook==null)
                {
                    return -1;
                }
                else
                {
                    return excelWorkBook.Worksheets.Count;
                }
            }
            catch (Exception )
            {
               return -1;
            }
        }

     
        /// <summary>
        /// 获取或设置当前工作表
        /// </summary>
        public int GetCurrentWorkSheet
        {
            set
            {
                if (value<=0||value>excelWorkBooks.Count)
                {
                    throw new Exception("索引超出范围");
                }
                else
                {
                    object index = value;
                    excelWorkSheet = excelWorkBook.Worksheets[index] as Excel.Worksheet;
                }
            }
        }

        /// <summary>
        /// 根据sheet表的名字返回sheet表
        /// </summary>
        /// <param name="sheetName">sheet表名</param>
        /// <returns></returns>
        public Excel.Worksheet GetWorkSheet(string sheetName)
        {
           return excelWorkSheet = excelWorkBook.Worksheets[sheetName] as Excel.Worksheet;
        }

        /// <summary>
        /// 添加一个新工作表
        /// </summary>
        /// <param name="sheetName">新工作表名称</param>
        /// <returns></returns>
        public Excel.Worksheet AddWorkSheet(string sheetName)
        {
            //excelApp.Visible = true;
            excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets.Add(oMissing, oMissing, oMissing, oMissing);
            excelWorkSheet.Name = sheetName;
            return excelWorkSheet;
        }

        /// <summary>
        /// 删除一个工作表
        /// </summary>
        /// <param name="sheetName"></param>
        public void DeleteSheet(string sheetName)
        {
            ((Excel.Worksheet)excelWorkBook.Worksheets[sheetName]).Delete();
        }

        /// <summary>
        /// 重命名工作表
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="newName"></param>
        /// <returns></returns>
        public Excel.Worksheet ReNameSheet(Excel.Worksheet ws,string newName)
        {
            ws.Name = newName;
            return ws;
        }

        /// <summ个ary>
        /// 读取某单元格的值
        /// </summary>
        /// <param name="row">行号</param>
        /// <param name="colunm">列号</param>
        /// <returns></returns>
        public string ReadCellValue(int row, int column)
        {
            Excel.Range range = excelWorkSheet.Cells[row, column] as Excel.Range;
            return range.Text.ToString();
        }

        /// <summary>
        /// 设置某一列单元格的值
        /// </summary>
        /// <param name="row">行号</param>
        /// <param name="column">列号</param>
        /// <param name="value">值</param>
        public void SetCellValue(int row, int column,string value)
        {
            Excel.Range range = excelWorkSheet.Cells[row, column] as Excel.Range;
            range.Value2 = value;
        }

        /// <summary>
        /// 设置单元格的值根据datatable
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="startX"></param>
        /// <param name="startY"></param>
        public void SetCellValue(DataTable dt, int startX, int startY)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    excelWorkSheet.Cells[startX + i, startY + j] = dt.Rows[i][j];
                }
            }
        }

        /// <summary>
        /// 写入datagridview内容
        /// </summary>
        /// <param name="dvg">要导出的datagridview</param>
        /// <param name="startX"></param>
        /// <param name="startY"></param>
        public void SetCellValue(System.Windows.Forms.DataGridView dvg, int startX, int startY)
        {
            //写入datagridview标题
            for (int k = 0; k < dvg.Columns.Count; k++)
            {
                ((Excel.Range)(excelWorkSheet.Cells[startX, startY+k])).Value2 = dvg.Columns[k].Name;
            }
            //写入datagridview内容
            for (int i = 0; i < dvg.Rows.Count; i++)
            {
                for (int j = 0; j < dvg.Columns.Count; j++)
                {
                    excelWorkSheet.Cells[startX + i+1, startY + j] = dvg.Rows[i].Cells[j].Value;
                }
            }
        }
        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="x1">开始的行号</param>
        /// <param name="y1">开始列</param>
        /// <param name="x2">结束行号</param>
        /// <param name="y2">结束列号</param>
        public void MergeCell(int x1,int y1,int x2,int y2)
        {

            excelApp.DisplayAlerts = false;// 关闭合并时提示
            Excel.Range range = (excelWorkSheet.get_Range(excelWorkSheet.Cells[x1, y1], excelWorkSheet.Cells[x2, y2]));
            range.Merge(oMissing);
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            excelApp.DisplayAlerts = true;
        }

        /// <summary>
        /// 打印表
        /// </summary>
        public void PrintWorkSheet()
        {
            Excel.Range range = excelWorkSheet.UsedRange;
            range.Select();
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            excelWorkSheet.PageSetup.PrintTitleRows = "$1:$1";//每页都打印标题
            excelWorkSheet.PageSetup.PrintGridlines = true;//打印表格
            //range.PrintPreview(oMissing);
            range.PrintOut(oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,oMissing);
        }

 

        /// <summary>
        /// 关闭excel对象,不保存
        /// </summary>
        /// <param name="isTrue"></param>
        private void CloseExcel(bool isTrue)
        {
            if (ExcelWorkBook!=null)
            {
                excelWorkBook.Close(isTrue,oMissing,oMissing);
            }
            if (excelApp != null)
            {
                excelApp.Quit();
            }
        }
        /// <summary>
        /// 销毁excel对象
        /// </summary>
        private void ReleaseExcel()
        {
            if (excelApp!=null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excelApp);
            }

            if (excelWorkBooks != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excelWorkBooks);
            }

            if (excelWorkBook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excelWorkBook);
            }

            if (excelWorkSheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excelWorkSheet);
            }
        }

        /// <summary>
        /// 销毁关闭的excel
        /// </summary>
        public void DisPoseExcel()
        {
            CloseExcel(false);
            ReleaseExcel();
            GC.Collect();
        }
    }
}