Excel处理类

原来那个Excel处理类找不到了，从新整理了一个。

 

/**//**
 * Excel控制类
 * 作者：王文斌
 * 版本：bate1  2008-1-8
 * 备注：部分方法未经测试，使用时请注意。
 * 使用方法：确保已安装office03，并安装Excel的.net开发支持
 * 引用com口dll
 * */
using System;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
using System.Drawing;
namespace MyExcelClass
...{
    /**//// <summary>
    /// ExcelClass 的摘要说明。
    /// </summary>
    public class ExcelCtrl
    ...{
        /**//// <summary>
        /// 构建ExcelClass类
        /// </summary>
        public ExcelCtrl()
        ...{
            this.m_objExcel = new Microsoft.Office.Interop.Excel.Application();
        }
        /**//// <summary>
        /// 构建ExcelClass类
        /// </summary>
        /// <param name="objExcel">Excel.Application</param>
        public ExcelCtrl(Microsoft.Office.Interop.Excel.Application objExcel)
        ...{
            this.m_objExcel = objExcel;
        }

        /**//// <summary>
        /// 列标号
        /// </summary>
        private string AList = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

        /**//// <summary>
        /// 获取描述区域的字符
        /// </summary>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <returns></returns>
        public string GetAix(int x, int y)
        ...{
            char[] AChars = AList.ToCharArray();
            if (x >= 26) ...{ return ""; }
            string s = "";
            s = s + AChars[x - 1].ToString();
            s = s + y.ToString();
            return s;
        }

        /**//// <summary>
        /// 获取当前活动Sheet的Range的值
        /// </summary>
        /// <param name="y"></param>
        /// <param name="x"></param>
        /// <returns></returns>
        public string GetRangeText(int y, int x)
        ...{
            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
            return range.Text.ToString();
        }

        /**//// <summary>
        /// 给单元格赋值1
        /// </summary>
        /// <param name="x">行号</param>
        /// <param name="y">列号</param>
        /// <param name="align">对齐（CENTER、LEFT、RIGHT）</param>
        /// <param name="text">值</param>
        public void setValue(int y, int x, string align, string text)
        ...{
            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
            range.set_Value(miss, text);
            if (align.ToUpper() == "CENTER")
            ...{
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            }
            if (align.ToUpper() == "LEFT")
            ...{
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
            }
            if (align.ToUpper() == "RIGHT")
            ...{
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
            }
        }

        /**//// <summary>
        /// 给单元格赋值2
        /// </summary>
        /// <param name="x">行号</param>
        /// <param name="y">列号</param>
        /// <param name="text">值</param>
        public void setValue(int y, int x, string text)
        ...{
            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
            range.set_Value(miss, text);
        }

        /**//// <summary>
        /// 给单元格赋值3
        /// </summary>
        /// <param name="x">行号</param>
        /// <param name="y">列号</param>
        /// <param name="text">值</param>
        /// <param name="font">字符格式</param>
        /// <param name="color">颜色</param>
        public void setValue(int y, int x, string text, System.Drawing.Font font, System.Drawing.Color color)
        ...{
            this.setValue(x, y, text);
            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
            range.Font.Size = font.Size;
            range.Font.Bold = font.Bold;
            range.Font.Color = color;
            range.Font.Name = font.Name;
            range.Font.Italic = font.Italic;
            range.Font.Underline = font.Underline;
        }

        /**//// <summary>
        /// 插入新行 将设定行拷贝，然后插入空行，再将设定行粘贴到新行上
        /// </summary>
        /// <param name="y">模板行号</param>
        public void insertRow(int y)
        ...{
            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(GetAix(1, y), GetAix(25, y));
            range.Copy(miss);
            range.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, miss);
            range.get_Range(GetAix(1, y), GetAix(25, y));
            range.Select();
            sheet.Paste(miss, miss);

        }

        /**////// <summary>
        ///// 把剪切内容粘贴到当前区域
        ///// </summary>
        //public void past(int x1, int y1, int x2, int y2, int x3, int y3, int x4, int y4) 
        //{
        //    Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(GetAix(x1, y1), GetAix(x2, y2));
        //    sheet.Paste(sheet.get_Range(this.GetAix(x3, y3), this.GetAix(x4, y4)), range); 
        //} 

        /**//// <summary>
        /// 设置边框
        /// </summary>
        /// <param name="x1"></param>
        /// <param name="y1"></param>
        /// <param name="x2"></param>
        /// <param name="y2"></param>
        /// <param name="Width"></param>
        public void setBorder(int x1, int y1, int x2, int y2, int Width)
        ...{
            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));
            range.Borders.Weight = Width;
        }

        /**//// <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)
        ...{
            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));
            range.Merge(true);
        }

        /**//// <summary>
        /// 合并单元格，并设置居中方式
        /// </summary>
        /// <param name="x1"></param>
        /// <param name="y1"></param>
        /// <param name="x2"></param>
        /// <param name="y2"></param>
        /// <param name="align">居中方式：center,left,right</param>
        public void mergeCell(int x1, int y1, int x2, int y2, string align)
        ...{
            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));
            range.Merge(true);
            if (align.ToUpper() == "CENTER")
            ...{
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            }
            if (align.ToUpper() == "LEFT")
            ...{
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
            }
            if (align.ToUpper() == "RIGHT")
            ...{
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
            }
        }
        /**//// <summary>
        /// 获取Range区域
        /// </summary>
        /// <param name="x1"></param>
        /// <param name="y1"></param>
        /// <param name="x2"></param>
        /// <param name="y2"></param>
        /// <returns></returns>
        public Microsoft.Office.Interop.Excel.Range getRange(int x1, int y1, int x2, int y2)
        ...{
            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));
            return range;
        }

        private object miss = Missing.Value; //忽略的参数OLENULL 
        private Microsoft.Office.Interop.Excel.Application m_objExcel;//Excel应用程序实例 
        private Microsoft.Office.Interop.Excel.Workbooks m_objBooks;//工作表集合 
        private Microsoft.Office.Interop.Excel.Workbook m_objBook;//当前操作的工作表 
        private Microsoft.Office.Interop.Excel.Worksheet sheet;//当前操作的表格 

        /**//// <summary>
        /// 活动工作Sheet
        /// </summary>
        public Microsoft.Office.Interop.Excel.Worksheet CurrentSheet
        ...{
            get
            ...{
                return sheet;
            }
            set
            ...{
                this.sheet = value;
            }
        }

        /**//// <summary>
        /// 获取活动WorkBooks
        /// </summary>
        public Microsoft.Office.Interop.Excel.Workbooks CurrentWorkBooks
        ...{
            get
            ...{
                return this.m_objBooks;
            }
            set
            ...{
                this.m_objBooks = value;
            }
        }

        /**//// <summary>
        /// 获取活动WorkBook
        /// </summary>
        public Microsoft.Office.Interop.Excel.Workbook CurrentWorkBook
        ...{
            get
            ...{
                return this.m_objBook;
            }
            set
            ...{
                this.m_objBook = value;
            }
        }

        /**//// <summary>
        /// 打开Excel文件
        /// </summary>
        /// <param name="filename">路径</param>
        public void OpenExcelFile(string filename)
        ...{
            UserControl(false);

            m_objExcel.Workbooks.Open(filename, miss, miss, miss, miss, miss, miss, miss,
                                    miss, miss, miss, miss, miss, miss, miss);

            m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;

            m_objBook = m_objExcel.ActiveWorkbook;
            sheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.ActiveSheet;
        }

        public void UserControl(bool usercontrol)
        ...{
            if (m_objExcel == null) ...{ return; }
            m_objExcel.UserControl = usercontrol;
            m_objExcel.DisplayAlerts = usercontrol;
            m_objExcel.Visible = usercontrol;
        }

        /**//// <summary>
        /// 创建Excel文件
        /// </summary>
        public void CreateExceFile()
        ...{
            UserControl(false);
            m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
            m_objBook = (Microsoft.Office.Interop.Excel.Workbook)(m_objBooks.Add(miss));
            sheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.ActiveSheet;
        }

        /**//// <summary>
        /// Excel文件另存为
        /// </summary>
        /// <param name="FileName"></param>
        public void SaveAs(string FileName)
        ...{

            m_objBook.SaveAs(FileName, miss, miss, miss, miss,
             miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
             Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges,
             miss, miss, miss, miss);
            //m_objBook.Close(false, miss, miss); 
        }

        /**//// <summary>
        /// 释放Excel文件
        /// </summary>
        public void ReleaseExcel()
        ...{
            m_objExcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objExcel);
            System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBooks);
            System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject((object)sheet);
            m_objExcel = null;
            m_objBooks = null;
            m_objBook = null;
            sheet = null;
            GC.Collect();
        }

        /**//////////////////////////////////
        public bool KillAllExcelApp()
        ...{
            try
            ...{
                if (m_objExcel != null) // isRunning是判断xlApp是怎么启动的flag.
                ...{
                    m_objExcel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
                    //释放COM组件，其实就是将其引用计数减1
                    //System.Diagnostics.Process theProc;
                    foreach (System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
                    ...{
                        //先关闭图形窗口。如果关闭失败...有的时候在状态里看不到图形窗口的excel了，
                        //但是在进程里仍然有EXCEL.EXE的进程存在，那么就需要杀掉它:p
                        if (theProc.CloseMainWindow() == false)
                        ...{
                            theProc.Kill();
                        }
                    }
                    m_objExcel = null;
                    return true;
                }
            }
            catch
            ...{
                return false;
            }
            return true;
        }
        /**//////////////////////////////////////////////

        /// <summary>
        /// 获取一个工作表
        /// </summary>
        /// <param name="SheetName">Sheet名</param>
        /// <returns>Sheet对象</returns>
        public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string SheetName)
        ...{
            Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets[SheetName];
            return s;
        }

        /**//// <summary>
        /// 添加一个工作表
        /// </summary>
        /// <param name="SheetName"></param>
        /// <returns></returns>
        public Microsoft.Office.Interop.Excel.Worksheet AddSheetWithReturn(string SheetName)
        ...{
            Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            s.Name = SheetName;
            return s;
        }

        /**//// <summary>
        /// 添加一个工作表
        /// </summary>
        public void AddSheet(string SheetName)
        ...{
            Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            s.Name = SheetName;

        }

        /**//// <summary>
        /// 删除一个工作表
        /// </summary>
        /// <param name="SheetName"></param>
        public void DelSheet(string SheetName)
        ...{
            ((Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets[SheetName]).Delete();
        }

        /**//// <summary>
        /// 重命名工作表
        /// </summary>
        /// <param name="OldSheetName">原名</param>
        /// <param name="NewSheetName">新名</param>
        /// <returns></returns>
        public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一
        ...{
            Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets[OldSheetName];
            s.Name = NewSheetName;
            return s;
        }

        public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(Microsoft.Office.Interop.Excel.Worksheet Sheet, string NewSheetName)//重命名一个工作表二
        ...{

            Sheet.Name = NewSheetName;

            return Sheet;
        }


        /**//// <summary>
        /// 设置单元格值
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <param name="value"></param>
        public void SetCellValue(Microsoft.Office.Interop.Excel.Worksheet ws, int x, int y, object value)
        ...{
            ws.Cells[y, x] = value;
        }

        /**//// <summary>
        /// 设置单元格值
        /// </summary>
        /// <param name="ws">sheet名</param>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <param name="value"></param>
        public void SetCellValue(string ws, int x, int y, object value)
        ...{

            GetSheet(ws).Cells[y, x] = value;
        }


        public void SetCellProperty(Microsoft.Office.Interop.Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Microsoft.Office.Interop.Excel.Constants color, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)
        //设置一个单元格的属性   字体，   大小，颜色   ，对齐方式
        ...{
            //name = "宋体";
            //size = 12;
            color = Microsoft.Office.Interop.Excel.Constants.xlAutomatic;
            HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight;
            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Name = name;
            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Size = size;
            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Color = color;
            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = HorizontalAlignment;
        }

        /**//// <summary>
        /// 设置单元格属性
        /// </summary>
        /// <param name="wsn">Sheet页名称</param>
        /// <param name="Startx">x1</param>
        /// <param name="Starty">y1</param>
        /// <param name="Endx">x2</param>
        /// <param name="Endy">y2</param>
        /// <param name="size">字体大小</param>
        /// <param name="name">字体名称</param>
        /// <param name="color">字体颜色</param>
        /// <param name="HorizontalAlignment">对齐方式</param>
        public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Microsoft.Office.Interop.Excel.Constants color, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)
        ...{
            //name = "宋体";
            //size = 12;
            //color = Excel.Constants.xlAutomatic;
            //HorizontalAlignment = Excel.Constants.xlRight;


            Microsoft.Office.Interop.Excel.Worksheet ws = GetSheet(wsn);
            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Name = name;
            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Size = size;
            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Color = color;

            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = HorizontalAlignment;
        }

        /**//// <summary>
        /// 设置单元格属性
        /// </summary>
        /// <param name="wsn">Sheet页名称</param>
        /// <param name="Startx">x1</param>
        /// <param name="Starty">y1</param>
        /// <param name="Endx">x2</param>
        /// <param name="Endy">y2</param>
        /// <param name="size">字体大小</param>
        /// <param name="name">字体名称</param>
        /// <param name="color">字体颜色名称（系统预定义颜色）</param>
        /// <param name="HorizontalAlignment">对齐方式</param>
        public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, string colorName, string HorizontalAlignment)
        ...{
            //name = "宋体";
            //size = 12;
            //color = Excel.Constants.xlAutomatic;
            //HorizontalAlignment = Excel.Constants.xlRight;


            Microsoft.Office.Interop.Excel.Worksheet ws = GetSheet(wsn);
            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Name = name;
            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Size = size;

            Color color = Color.FromName(colorName);
            color = Color.FromArgb(color.B, color.G, color.R);

            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Color = color.ToArgb();

            if (HorizontalAlignment.ToUpper() == "CENTER")
            ...{

                ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ;
            }
            if (HorizontalAlignment.ToUpper() == "LEFT")
            ...{
                ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
            }
            if (HorizontalAlignment.ToUpper() == "RIGHT")
            ...{
                ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
            }

        }

        /**//// <summary>
        /// 设置单元格底色
        /// </summary>
        /// <param name="wsn">sheet页名称</param>
        /// <param name="Startx">x1</param>
        /// <param name="Starty">y1</param>
        /// <param name="Endx">x2</param>
        /// <param name="Endy">y2</param>
        /// <param name="colorName">系统颜色名称</param>
        public void SetBorderColor(string wsn, int Startx, int Starty, int Endx, int Endy, string colorName)
        ...{
            Color color = Color.FromName(colorName);
            color = Color.FromArgb(color.B, color.G, color.R);
            Microsoft.Office.Interop.Excel.Worksheet ws = GetSheet(wsn);
            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Cells.Interior.Color = color.ToArgb();
            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Cells.Interior.Pattern = Microsoft.Office.Interop.Excel.XlBackground.xlBackgroundAutomatic;

        }

        //合并单元格
        public void UniteCells(Microsoft.Office.Interop.Excel.Worksheet ws, int x1, int y1, int x2, int y2)
        ...{
            ws.get_Range(ws.Cells[y1, x1], ws.Cells[y2, x2]).Merge(Type.Missing);
        }

        //合并单元格
        public void UniteCells(string ws, int x1, int y1, int x2, int y2)
        ...{
            GetSheet(ws).get_Range(GetSheet(ws).Cells[y1, x1], GetSheet(ws).Cells[y2, x2]).Merge(Type.Missing);

        }

        //将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一
        public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY)
        ...{

            for (int i = 0; i < dt.Rows.Count; i++)
            ...{
                for (int j = 0; j < dt.Columns.Count; j++)
                ...{
                    GetSheet(ws).Cells[startY + i, j + startX] = dt.Rows[i][j].ToString();

                }

            }

        }

        //将内存中数据表格插入到Excel指定工作表的指定位置二
        public void InsertTable(System.Data.DataTable dt, Microsoft.Office.Interop.Excel.Worksheet ws, int startX, int startY)
        ...{
            for (int i = 0; i < dt.Rows.Count; i++)
            ...{
                for (int j = 0; j < dt.Columns.Count; j++)
                ...{

                    ws.Cells[startY + i, j + startX] = dt.Rows[i][j];

                }

            }

        }

        //插入图片操作一
        public void InsertPictures(string Filename, string ws, int x, int y, int wight, int lenth)
        ...{
            //后面的数字表示位置
            GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, x, y, wight, lenth);

        }

        //public void InsertPictures(string Filename, string ws, int Height, int Width)
        //插入图片操作二
        //{
        //    GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);
        //    GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height;
        //    GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width;
        //}
        //public void InsertPictures(string Filename, string ws, int left, int top, int Height, int Width)
        //插入图片操作三
        //{

        //    GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);
        //    GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementLeft(left);
        //    GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementTop(top);
        //    GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height;
        //    GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width;
        //}

    }
}