[收藏] C#操作Excel的示例

using System;
using System.Data;
using System.Configuration;
using System.Web;

namespace Storm2.CommonLib
{

    /// <summary>
    /// ExcelEdit
    /// </summary>
    public class ExcelEdit : IDisposable
    {
        public string mFileName;
        private Microsoft.Office.Interop.Excel.Application oAP;
        private Microsoft.Office.Interop.Excel.Workbooks oWBS;
        private Microsoft.Office.Interop.Excel.Workbook oWB;

        public ExcelEdit()
        {
        }

        //********************************************************
        //** Description 丗 Creat Excel
        //** Parameter   丗
        //** Return      丗 
        //** Created By  丗 ShenJiXian
        //** Created Date丗 Sept 25, 2008
        //********************************************************
        public void Create()
        {
            oAP = new Microsoft.Office.Interop.Excel.Application();
            oWBS = oAP.Workbooks;
            oWB = oWBS.Add(true);
        }

        //********************************************************
        //** Description 丗 Open Excel
        //** Parameter   丗 FileName - open file name
        //** Return      丗 
        //** Created By  丗 ShenJiXian
        //** Created Date丗 Sept 25, 2008
        //********************************************************
        public void Open(string fileName)
        {
            oAP = new Microsoft.Office.Interop.Excel.Application();
            oWBS = oAP.Workbooks;
            oWB = oWBS.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);
            mFileName = fileName;
        }

        //********************************************************
        //** Description 丗 Get Excel Sheet
        //** Parameter   丗 SheetName - get sheet name
        //** Return      丗 workSheet - Object
        //** Created By  丗 ShenJiXian
        //** Created Date丗 Sept 25, 2008
        //********************************************************
        public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string sheetName)
        {
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[sheetName];
            return workSheet;
        }

        //********************************************************
        //** Description 丗 Addition Excel Sheet
        //** Parameter   丗 SheetName - append sheet name
        //** Return      丗 workSheet - Object
        //** Created By  丗 ShenJiXian
        //** Created Date丗 Sept 25, 2008
        //********************************************************
        public object AddSheet(string sheetName)
        {
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            workSheet.Name = sheetName;
            return workSheet;
        }

        //********************************************************
        //** Description 丗 Deletion Excel Sheet
        //** Parameter   丗 SheetName - deletion sheet name
        //** Return      丗 
        //** Created By  丗 ShenJiXian
        //** Created Date丗 Sept 25, 2008
        //******************************************************** 
        public void DelSheet(string sheetName)
        {
            ((Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[sheetName]).Delete();
        }

        //********************************************************
        //** Description 丗 Copy Excel Sheet
        //** Parameter   丗 OldSheetName - old sheet name
        //                  NewSheetName - copy new sheet
        //** Return      丗 workSheet    - Object
        //** Created By  丗 ShenJiXian
        //** Created Date丗 Sept 25, 2008
        //******************************************************** 
        public object RenameSheet(string oldSheetName, string newSheetName)
        {
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[oldSheetName];
            workSheet.Name = newSheetName;
            return workSheet;
        }
        public object RenameSheet(Microsoft.Office.Interop.Excel.Worksheet sheet, string newSheetName)
        {
            sheet.Name = newSheetName;
            return sheet;
        }

        //********************************************************
        //** Description 丗 Get Cell Value
        //** Parameter   丗 workSheet - get sheet name
        //                  x         - row
        //                  y         - loc
        //** Return      丗 Cell's Value
        //** Created By  丗 ShenJiXian
        //** Created Date丗 Sept 25, 2008
        //******************************************************** 
        public string GetCellValue(Microsoft.Office.Interop.Excel.Worksheet workSheet, int x, int y)
        {
            //return ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[x, y]).Text.ToString();
            return SetNullToEmpty(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[x, y]).Text);
        }
        public string GetCellValue(string workSheetName, int x, int y)
        {
            //return ((Microsoft.Office.Interop.Excel.Range)GetSheet(workSheetName).Cells[x, y]).Text.ToString();
            return SetNullToEmpty(((Microsoft.Office.Interop.Excel.Range)GetSheet(workSheetName).Cells[x, y]).Text);
        }

        //********************************************************
        //** Description 丗 Set Cell Value
        //** Parameter   丗 workSheet - get sheet name
        //                  x         - row
        //                  y         - loc
        //                  value     - object value
        //** Return      丗 
        //** Created By  丗 ShenJiXian
        //** Created Date丗 Sept 25, 2008
        //******************************************************** 
        public void SetCellValue(Microsoft.Office.Interop.Excel.Worksheet workSheet, int x, int y, object value)
        {
            workSheet.Cells[x, y] = value;
        }
        public void SetCellValue(string workSheet, int x, int y, object value)
        {
            GetSheet(workSheet).Cells[x, y] = value;
        }

        //*******************************************************
        //** Description 丗 Set Cell Property
        //** Parameter   丗 workSheet  ---sheet object
        //**             丗 Startx     ---row start position
        //**             丗 Starty     ---col start position
        //**             丗 Endx       ---row start position
        //**             丗 Endy       ---col start position
        //**             丗 name       ---sheet name
        //**             丗 size       ---font size
        //**             丗 color      ---font color
        //**             丗 bold       ---font bold
        //**             丗 hAlignment ---Horizontal position
        //** Return      丗 
        //** Created By  丗 ShenJiXian
        //** Created Date丗 Sept 25, 2008
        //******************************************************** 
        public void SetCellProperty(Microsoft.Office.Interop.Excel.Worksheet workSheet, int Startx, int Starty, int Endx, int Endy, string name, int size, int color, bool bold, object hAlignment
        )
        {
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Name = name;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Bold = bold;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Size = size;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Color = color;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).HorizontalAlignment = hAlignment;
        }
        public void SetCellProperty(string workSheetName, int Startx, int Starty, int Endx, int Endy, string name, int size, int color, bool bold, object hAlignment
        )
        {
            Microsoft.Office.Interop.Excel.Worksheet workSheet = GetSheet(workSheetName);
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Name = name;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Bold = bold;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Size = size;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Color = color;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).HorizontalAlignment = hAlignment;
        }

        //********************************************************
        //** Description 丗 Sheet Unite Cells
        //** Parameter   丗 workSheet - sheet object
        //                  x1        - row start position
        //                  y1        - col start position
        //                  x2        - row end position
        //                  y2        - col end position
        //** Return      丗 
        //** Created By  丗 ShenJiXian
        //** Created Date丗 Sept 25, 2008
        //********************************************************
        public void MergeCells(Microsoft.Office.Interop.Excel.Worksheet workSheet, int x1, int y1, int x2, int y2)
        {

            workSheet.get_Range(workSheet.Cells[x1, y1], workSheet.Cells[x2, y2]).Merge(Type.Missing);
        }
        public void MergeCells(string workSheetName, int x1, int y1, int x2, int y2)
        {
            GetSheet(workSheetName).get_Range(GetSheet(workSheetName).Cells[x1, y1], GetSheet(workSheetName).Cells[x2, y2]).Merge(Type.Missing);
        }

        //********************************************************
        //** Description 丗 more information Insert Sheet
        //** Parameter   丗 dt            - DataTable
        //                  workSheetName - sheet name
        //                  startX        - row start position
        //                  startY        - col start position
        //** Return      丗 
        //** Created By  丗 ShenJiXian
        //** Created Date丗 Sept 25, 2008
        //********************************************************
        public void InsertTable(DataTable dt, string workSheetName, int startX, int startY)
        {
            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    GetSheet(workSheetName).Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();
                }
            }
        }
        public void InsertTable(DataTable dt, Microsoft.Office.Interop.Excel.Worksheet workSheet, int startX, int startY)
        {
            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    workSheet.Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();
                }
            }
        }

        //********************************************************
        //** Description 丗 Save File
        //** Parameter   丗
        //** Return      丗 True  - success
        //                  False - failure
        //** Created By  丗 ShenJiXian
        //** Created Date丗 Sept 25, 2008
        //********************************************************
        public bool Save()
        {
            if (string.IsNullOrEmpty(mFileName))
            {
                return false;
            }
            else
            {
                oWB.Save();
                return true;
            }
        }

        //********************************************************
        //** Description 丗 Save File
        //** Parameter   丗 FileName - save file name
        //** Return      丗 True  - success
        //                  False - failure
        //** Created By  丗 ShenJiXian
        //** Created Date丗 Sept 25, 2008
        //********************************************************
        public bool SaveAs(string fileName)
        {
            if (string.IsNullOrEmpty(fileName))
            {
                return false;
            }
            else
            {
                mFileName = fileName;
                oWB.SaveAs(mFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);
                return true;
            }
        }

        public void Dispose()
        {
            if (oWB != null)
                oWB = null;
            if (oWBS != null)
                oWBS = null;
            if (oAP != null)
            {
                oAP.Quit();
                oAP = null;
            }
            GC.Collect();
        }
        //********************************************************
        //** Description 丗 Close File
        //** Parameter   丗
        //** Return      丗 
        //** Created By  丗 ShenJiXian
        //** Created Date丗 Sept 25, 2008
        //********************************************************
        public void Close()
        {
            oWB.Close(true, mFileName, Type.Missing);
            oWBS.Close();
            oAP.Quit();
            oWB = null;
            oWBS = null;
            oAP = null;
            GC.Collect();
        }

        private string SetNullToEmpty(Object mValue)
        {
            if (null == mValue || string.Empty.Equals(mValue))
            {
                return string.Empty;
            }
            else
            {
                return mValue.ToString();
            }
        }
    }
}