【小丸类库系列】Excel操作类

using Microsoft.Office.Interop.Excel;
using System;
using System.IO;
using System.Reflection;

namespace ECIT.ProjectManagementSystem.Common
{
    public class ExcelHelper : IDisposable
    {
        #region 成员变量

        private object missing = Missing.Value;
        private Application app;
        private Workbook workBook;
        public Worksheet workSheet;

        public void setWorkSheet(int i)
        {
            workSheet = (Worksheet)workBook.Worksheets[i];
        }

        #endregion 成员变量

        #region 公共属性

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

        #endregion 公共属性

        #region 构造函数

        /// <summary>
        /// 构造函数，新建一个工作簿
        /// </summary>
        public ExcelHelper()
        {
            app = new Application();
            workBook = app.Workbooks.Add(Type.Missing);
            workSheet = (Worksheet)app.Worksheets[1];
        }

        /// <summary>
        /// 构造函数，打开一个已有的工作簿
        /// </summary>
        /// <param name="fileName">Excel文件名</param>
        public ExcelHelper(string fileName)
        {
            if (!File.Exists(fileName))
                throw new Exception("指定路径的Excel文件不存在！");

            app = new Application();

            workBook = app.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);

            workSheet = (Worksheet)app.Worksheets[1];
        }

        #endregion 构造函数

        #region 工作表操作

        /// <summary>
        /// 删除工作表
        /// </summary>
        /// <param name="i">工作表的序号</param>
        public void RemoveSheet(int i)
        {
            Worksheet worksheet = (Worksheet)app.Worksheets[i];
            app.DisplayAlerts = false;
            worksheet.Delete();
            app.DisplayAlerts = true;
        }

        /// <summary>
        /// 增加工作表
        /// </summary>
        /// <param name="i">工作表的序号</param>
        public void AddSheet(int i)
        {
            app.Worksheets.Add(Type.Missing, Type.Missing, i, XlSheetType.xlWorksheet);
        }

        #endregion 工作表操作

        #region 单元格操作

        /// <summary>
        /// 获得最大行号
        /// </summary>
        /// <returns></returns>
        public int GetUsedRangeRow()
        {
            return workSheet.UsedRange.Rows.Count;
        }

        /// <summary>
        /// 获得最大列号
        /// </summary>
        /// <returns></returns>
        public int GetUsedRangeColumn()
        {
            return workSheet.UsedRange.Columns.Count;
        }

        /// <summary>
        /// 获取单元格的文本内容
        /// </summary>
        /// <param name="row">行号</param>
        /// <param name="column">列号</param>
        /// <returns></returns>
        public string GetCellText(int row, int column)
        {
            if (workSheet == null)
                return null;
            Range range = (Range)workSheet.Cells[row, column];
            string cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
            return cellContent;
        }

        /// <summary>
        /// 赋值单元格
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="value"></param>
        public void SetValue(ExcelRange excelRange, object value)
        {
            workSheet.Range[excelRange.ToString()].Value2 = value;
        }

        /// <summary>
        /// 设置字体大小
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="size"></param>
        public void SetFontSize(ExcelRange excelRange, int size)
        {
            workSheet.Range[excelRange.ToString()].Font.Size = size;
        }

        /// <summary>
        /// 设置字体颜色
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="color"></param>
        public void SetFontColor(ExcelRange excelRange, System.Drawing.Color color)
        {
            workSheet.Range[excelRange.ToString()].Font.Color = color;
        }

        /// <summary>
        /// 设置单元格背景色
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="color"></param>
        public void SetBackgroundColor(ExcelRange excelRange, System.Drawing.Color color)
        {
            workSheet.Range[excelRange.ToString()].Interior.Color = color;
        }

        /// <summary>
        /// 设置字体粗体
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="isBold"></param>
        public void SetFontBold(ExcelRange excelRange, bool isBold)
        {
            workSheet.Range[excelRange.ToString()].Font.Bold = isBold;    //设置字体粗体。
        }

        /// <summary>
        /// 设置是否有下划线
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="isUnderline"></param>
        public void SetUnderline(ExcelRange excelRange, bool isUnderline)
        {
            workSheet.Range[excelRange.ToString()].Font.Underline = isUnderline;
        }

        /// <summary>
        /// 设置字体
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="fontname"></param>
        public void SetFontName(ExcelRange excelRange, string fontname)
        {
            workSheet.Range[excelRange.ToString()].Font.Name = fontname;
        }

        /// <summary>
        /// 设置单元格宽度
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="columnwidth"></param>
        public void SetColumnWidth(ExcelRange excelRange, int columnwidth)
        {
            workSheet.Range[excelRange.ToString()].ColumnWidth = columnwidth;
        }

        /// <summary>
        /// 文本自动换行
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="isWrapText"></param>
        public void SetWrapText(ExcelRange excelRange, bool isWrapText)
        {
            workSheet.Range[excelRange.ToString()].WrapText = isWrapText;
        }

        /// <summary>
        /// 文本横向对齐方式
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="xlHAlign"></param>
        public void SetHorizontalAlignment(ExcelRange excelRange, XlHAlign xlHAlign)
        {
            workSheet.Range[excelRange.ToString()].HorizontalAlignment = xlHAlign;
        }

        /// <summary>
        /// 文本垂直对齐方式
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="xlHAlign"></param>
        public void SetVerticalAlignment(ExcelRange excelRange, XlHAlign xlHAlign)
        {
            workSheet.Range[excelRange.ToString()].VerticalAlignment = xlHAlign;
        }

        /// <summary>
        /// 自动调整列宽
        /// </summary>
        /// <param name="excelRange"></param>
        public void FitColumn(ExcelRange excelRange)
        {
            workSheet.Range[excelRange.ToString()].EntireColumn.AutoFit();
        }

        /// <summary>
        /// 设置单元格边框
        /// </summary>
        /// <param name="excelRange"></param>
        public void SetBorderAround(ExcelRange excelRange, XlLineStyle LineStyle, XlBorderWeight BorderWeight)
        {
            workSheet.Range[excelRange.ToString()].BorderAround(LineStyle, BorderWeight, XlColorIndex.xlColorIndexAutomatic, 15);    //设置区域边框
        }

        /// <summary>
        /// 单元格合并动作
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="IsMergeCells"></param>
        public void MergeCell(ExcelRange excelRange, bool IsMergeCells)
        {
            workSheet.Range[excelRange.ToString()].MergeCells = IsMergeCells;
        }

        #endregion 单元格操作

        #region 图表绘制

        /// <summary>
        /// 3D柱状图
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="left"></param>
        /// <param name="top"></param>
        /// <param name="width"></param>
        /// <param name="height"></param>
        /// <param name="title"></param>
        /// <param name="xTitle"></param>
        /// <param name="yTitle"></param>
        /// <param name="plotBy">XlRowCol.xlRows=数据系列在一列中 XlRowCol.xlColumns=数据系列在一行中</param>
        public void Draw3DColumn(ExcelRange excelRange, double left, double top, double width, double height, string title, string xTitle, string yTitle, XlRowCol plotBy)
        {
            ChartObjects charts = (ChartObjects)workSheet.ChartObjects(Type.Missing);
            ChartObject chartObj = charts.Add(left, top, width, height);
            Chart chart = chartObj.Chart;
            chart.ChartWizard(workSheet.Range[excelRange.ToString()], XlChartType.xl3DColumn, missing, plotBy, 1, 1, true, title, xTitle, yTitle, 1);
        }

        /// <summary>
        /// 3D柱状图
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="startRange"></param>
        /// <param name="width"></param>
        /// <param name="height"></param>
        /// <param name="title"></param>
        /// <param name="xTitle"></param>
        /// <param name="yTitle"></param>
        /// <param name="plotBy">XlRowCol.xlRows=数据系列在一列中 XlRowCol.xlColumns=数据系列在一行中</param>
        public void Draw3DColumn(ExcelRange excelRange, ExcelRange startRange, double width, double height, string title, string xTitle, string yTitle, XlRowCol plotBy)
        {
            Draw3DColumn(excelRange, Convert.ToDouble(workSheet.Range[startRange.ToString()].Left), Convert.ToDouble(workSheet.Range[startRange.ToString()].Top), width, height, title, xTitle, yTitle, plotBy);
        }

        /// <summary>
        /// 3D饼图
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="left"></param>
        /// <param name="top"></param>
        /// <param name="width"></param>
        /// <param name="height"></param>
        /// <param name="title"></param>
        public void Draw3DPie(ExcelRange excelRange, double left, double top, double width, double height, string title)
        {
            ChartObjects charts = (ChartObjects)workSheet.ChartObjects(Type.Missing);
            ChartObject chartObj = charts.Add(left, top, width, height);
            Chart chart = chartObj.Chart;
            chart.ChartWizard(workSheet.Range[excelRange.ToString()], XlChartType.xl3DPie, missing, XlRowCol.xlColumns, 1, 1, true, title, missing, missing, 1);
        }

        /// <summary>
        /// 3D饼图
        /// </summary>
        /// <param name="excelRange"></param>
        /// <param name="startRange"></param>
        /// <param name="width"></param>
        /// <param name="height"></param>
        /// <param name="title"></param>
        public void Draw3DPie(ExcelRange excelRange, ExcelRange startRange, double width, double height, string title)
        {
            Draw3DPie(excelRange, Convert.ToDouble(workSheet.Range[startRange.ToString()].Left), Convert.ToDouble(workSheet.Range[startRange.ToString()].Top), width, height, title);
        }

        #endregion 图表绘制

        #region 文件操作

        /// <summary>
        /// 保存文件
        /// </summary>
        /// <param name="fileName"></param>
        public void SaveFile(string fileName)
        {
            try
            {
                workBook.RefreshAll();
                workBook.SaveAs(fileName, missing, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing);
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw e;
            }
        }

        #endregion 文件操作

        #region 资源回收

        private bool disposed = false;

        ~ExcelHelper()
        {
            //必须为false
            Dispose(false);
        }

        public void Dispose()
        {
            //必须为true
            Dispose(true);
            //通知垃圾回收机制不再调用终结器（析构器）
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (disposed)
            {
                return;
            }
            if (disposing)
            {
                // 清理托管资源
            }

            // 清理非托管资源
            if (app != null)
            {
                app.Quit();
                app = null;
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();

            //让类型知道自己已经被释放
            disposed = true;
        }

        #endregion 资源回收
    }

    /// <summary>
    /// EXCEL单元格操作范围
    /// </summary>
    public class ExcelRange
    {
        public string RangeString { get; set; }

        public ExcelRange(string RangeString)
        {
            this.RangeString = RangeString;
        }

        /// <summary>
        /// 输入Excel的行号和列号获得范围
        /// </summary>
        /// <param name="x">列号</param>
        /// <param name="y">行号</param>
        public ExcelRange(int x, int y)
        {
            this.RangeString = IntToLetter(x) + y.ToString();
        }

        public ExcelRange(int x1, int y1, int x2, int y2)
        {
            this.RangeString = IntToLetter(x1) + y1.ToString() + ":" + IntToLetter(x2) + y2.ToString();
        }

        public ExcelRange(ExcelRange from, ExcelRange to)
        {
            this.RangeString = from.RangeString + ":" + to.RangeString;
        }

        static public implicit operator ExcelRange(string value)
        {
            return new ExcelRange(value);
        }

        static public implicit operator string (ExcelRange ExcelRange)
        {
            return ExcelRange.RangeString;
        }

        public override string ToString()
        {
            return RangeString;
        }

        /// <summary>
        /// 将Excel列的整数索引值转换为字符索引值
        /// </summary>
        /// <param name="n"></param>
        /// <returns></returns>
        public static string IntToLetter(int n)
        {
            if (n > 700)
                throw new Exception("索引超出范围，Excel的列索引不能超过700！");

            int i = Convert.ToInt32(n / 26);
            int j = n % 26;

            char c1 = Convert.ToChar(i + 64);
            char c2 = Convert.ToChar(j + 64);

            if (n > 26)
                return c1.ToString() + c2.ToString();
            else if (n == 26)
                return "Z";
            else
                return c2.ToString();
        }
    }
}