Asp.Net Excel 操作 类

因为工作原因，做了很多次关于Winform操作Excel的项目

今天把操作Excel的类整理了一下，跟大家分享


还有很多操作，大家可以补充或者提意见给我.非常感谢！

using System;
using Microsoft.Office.Interop;
using Microsoft.Office.Core;

public class ExcelHelp
{


    private Microsoft.Office.Interop.Excel.Application FApp;

    private Microsoft.Office.Interop.Excel.Workbooks workbooks;
    private Microsoft.Office.Interop.Excel.Workbook workbook;

    private Microsoft.Office.Interop.Excel.Worksheet worksheet;

    private Microsoft.Office.Interop.Excel.Range aRg;

    public Microsoft.Office.Interop.Excel.XlLineStyle xlLineStyle { get; set; }

    public ExcelHelp()
    {
        Visible = true;         // 默认可视化
        DisplayAlerts = false;  // 默认不弹出保存提示窗体
    }


    /// <summary>
    /// 可视化编辑
    /// </summary>
    public bool Visible { get; set; }

    /// <summary>
    /// 是否弹出保存确认对话框
    /// </summary>
    public bool DisplayAlerts { get; set; }

    /// <summary>
    /// 保存文件名称
    /// </summary>
    public string SaveName { get; set; }

    /// <summary>
    /// 创建一个Excel对象
    /// </summary>
    private void Create()
    {
        FApp = new Microsoft.Office.Interop.Excel.Application();
        FApp.Visible = Visible;
        workbooks = FApp.Workbooks;
        workbook = workbooks.Add(true);
        worksheet = (Microsoft.Office.Interop.Excel.Worksheet)FApp.ActiveSheet;
    }


    /// <summary>
    /// 打开 新Excel
    /// </summary>
    public void OpenExcel()
    {
        Create();
    }

    /// <summary>
    /// 打开已有Excel
    /// </summary>
    /// <param name="mfileName">Excel路径</param>
    public void OpenExcel(string mfileName)
    {
        SaveName = mfileName;

        FApp = new Microsoft.Office.Interop.Excel.Application();
        workbooks = FApp.Workbooks;
        workbook = workbooks.Add(SaveName);
        worksheet = (Microsoft.Office.Interop.Excel.Worksheet)FApp.ActiveSheet;

        //设置禁止弹出保存和覆盖的询问提示框
        FApp.DisplayAlerts = DisplayAlerts;
        FApp.AlertBeforeOverwriting = false;
        FApp.UserControl = true;//如果只想用程序控制该excel而不想让用户操作时候，可以设置为false
        FApp.Visible = Visible;

    }


    
    /// <summary>
    /// 获取单元格内容
    /// </summary>
    /// <param name="aRow">行</param>
    /// <param name="aCol">列</param>
    /// <returns>内容</returns>
    public string GetCellValue(int aRow, int aCol)
    {
        aRg = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[aRow, aCol];

        if (aRg.Text == null)
        {
            return "";
        }
        else
        {
            return (string)aRg.Text;
        }
    }

    #region 写入文本内容
    /// <summary>
    /// 单元格写入内容
    /// </summary>
    /// <param name="aRow">行</param>
    /// <param name="aCol">列</param>
    /// <param name="sValue">内容</param>
    public void SetCellValue(int aRow, int aCol, string sValue)
    {
        worksheet.Cells[aRow, aCol] = sValue;
    }
    #endregion

    #region 颜色写入
    /// <summary>
    /// 设置单元格颜色
    /// </summary>
    /// <param name="aRow">行</param>
    /// <param name="aCol">列</param>
    /// <param name="aColorIndex">颜色索引</param>
    public void SetCellColor(int aRow, int aCol, float aColorIndex)
    {
        aRg = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[aRow, aCol];
        aRg.Interior.ColorIndex = aColorIndex;
        aRg = null;
    }

    /// <summary>
    /// 批量写入内容（内容相同）
    /// </summary>
    /// <param name="startRows">起始行</param>
    /// <param name="startColumns">起始列</param>
    /// <param name="endRows">结束行</param>
    /// <param name="endcolumns">结束列</param>
    /// <param name="sValue">文本内容</param>
    public void SetCellValue(int startRows, int startColumns, int endRows, int endcolumns, string sValue)
    {
        aRg = worksheet.get_Range(worksheet.Cells[startRows, startColumns], worksheet.Cells[endRows, endcolumns]);//(Microsoft.Office.Interop.Excel.Range)aWS.Cells[aRow, aCol];
        aRg.Value = sValue;
        aRg = null;
    }

    /// <summary>
    /// 单元格边框
    /// </summary>
    /// <param name="startRows">起始行</param>
    /// <param name="startColumns">起始列</param>
    /// <param name="endRows">结束行</param>
    /// <param name="endcolumns">结束列</param>
    public void SetBordersStyle(int startRows, int startColumns, int endRows, int endcolumns)
    {
        //Borders.LineStyle 单元格边框线
        aRg = worksheet.get_Range(worksheet.Cells[startRows, startColumns], worksheet.Cells[endRows, endcolumns]);
        //单元格边框线类型(线型,虚线型)
        aRg.Borders.LineStyle = xlLineStyle;
        aRg.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = xlLineStyle;
        aRg = null;

    }
    /// <summary>
    /// 批量设置单元格背景色
    /// </summary>
    /// <param name="startRows">起始行</param>
    /// <param name="startColumns">起始列</param>
    /// <param name="endRows">结束行</param>
    /// <param name="endcolumns">结束列</param>
    /// <param name="aColorIndex">颜色索引</param>
    public void SetCellColor(int startRows, int startColumns, int endRows, int endcolumns, float aColorIndex)
    {
        aRg = worksheet.get_Range(worksheet.Cells[startRows, startColumns], worksheet.Cells[endRows, endcolumns]);//(Microsoft.Office.Interop.Excel.Range)aWS.Cells[aRow, aCol];
        aRg.Interior.ColorIndex = aColorIndex;
        aRg = null;
    }
    /// <summary>
    /// 设置单元格背景色
    /// </summary>
    /// <param name="iRow">行</param>
    /// <param name="iCol">列</param>
    /// <param name="sColor">颜色</param>
    public void SetCellColor(int iRow, int iCol, Microsoft.Office.Interop.Excel.Constants sColor)
    {
        aRg = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
        aRg.Interior.Color = sColor;
        aRg = null;
    }
    #endregion

    #region 工作簿操作
    /// <summary>
    ///  设置工作簿名称
    /// </summary>
    /// <param name="SheetName">工作簿名称</param>
    public void SetActiveSheet(string SheetName)
    {
        worksheet.Activate();
    }
    #endregion


    #region 行操作
    /// <summary>
    /// 在工作表中删除行
    /// </summary>
    /// <param name="sheet">当前工作表</param>
    /// <param name="rowIndex">欲删除的行索引</param>
    public void DeleteRows(int rowIndex)
    {
        aRg = (Microsoft.Office.Interop.Excel.Range)worksheet.Rows[rowIndex, Type.Missing];
        aRg.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
        aRg = null;
    }
    /// <summary>
    /// 在工作表中插入行，并调整其他行以留出空间
    /// </summary>
    /// <param name="sheet">当前工作表</param>
    /// <param name="rowIndex">欲插入的行索引</param>
    public void InsertRows(int rowIndex)
    {
        aRg = (Microsoft.Office.Interop.Excel.Range)worksheet.Rows[rowIndex, Type.Missing];
        aRg.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing);
        aRg = null;
    }

    #endregion

    #region 列操作
    /// <summary>
    /// 在工作表中删除列
    /// </summary>
    /// <param name="sheet">当前工作表</param>
    /// <param name="rowIndex">欲删除的行索引</param>
    public void DeleteColumn(int columnIndex)
    {
        aRg = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, columnIndex];
        aRg.EntireColumn.Delete(0);
        aRg = null;
    }

    /// <summary>
    /// 在工作表中插入列
    /// </summary>
    /// <param name="columnIndex">列索引</param>
    public void InsertColumn(int columnIndex)
    {
        aRg = (Microsoft.Office.Interop.Excel.Range)worksheet.Columns[columnIndex, Type.Missing];
        aRg.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing);
        aRg = null;
    }

    /// <summary>
    /// 列宽度
    /// </summary>
    /// <param name="columnIndex">列索引</param>
    /// <param name="width">宽度</param>
    public void SetColumnWidth(int columnIndex, float width)
    {
        aRg = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, columnIndex];
        aRg.ColumnWidth = width;
        aRg = null;
    }
    #endregion



    #region 合并单元格
    /// <summary>
    /// 合并单元格
    /// </summary>
    /// <param name="ws">Worksheet对象</param>
    /// <param name="Start_iRow">起始行</param>
    /// <param name="Start_iCol">起始列</param>
    /// <param name="End_iRow">结束行</param>
    /// <param name="End_iCol">结束列</param>
    public void UniteCells(int startRows, int startColumns, int endRows, int endcolumns)
    {
        worksheet.get_Range(worksheet.Cells[startRows, startColumns], worksheet.Cells[endRows, endcolumns]).Merge(Type.Missing);
    }

    #endregion

    #region 文字操作
    /// <summary>
    /// 文字剧中
    /// </summary>
    /// <param name="startRows">起始行</param>
    /// <param name="startColumns">起始列</param>
    /// <param name="endRows">结束行</param>
    /// <param name="endcolumns">结束列</param>
    public void SetValueAlign(int startRows, int startColumns, int endRows, int endcolumns)
    {
        aRg = worksheet.get_Range(worksheet.Cells[startRows, startColumns], worksheet.Cells[endRows, endcolumns]);

        // 可以提出Microsoft.Office.Interop.Excel.XlHAlign 让对齐方式更灵活
        aRg.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        aRg = null;
    }

    /// <summary>
    /// 文字加粗
    /// </summary>
    /// </summary>
    /// <param name="startRows">起始行</param>
    /// <param name="startColumns">起始列</param>
    /// <param name="endRows">结束行</param>
    /// <param name="endcolumns">结束列</param>
    public void SetValueBold(int startRows, int startColumns, int endRows, int endcolumns)
    {
        aRg = worksheet.get_Range(worksheet.Cells[startRows, startColumns], worksheet.Cells[endRows, endcolumns]);

        //
        aRg.Font.Bold = 2;
        aRg = null;
    }

    /// <summary>
    /// 加入超链接
    /// </summary>
    /// <param name="startRows">起始行</param>
    /// <param name="startColumns">起始列</param>
    /// <param name="endRows">结束行</param>
    /// <param name="endcolumns">结束列</param>
    /// <param name="link">链接地址</param>
    public void SetLink(int startRows, int startColumns, int endRows, int endcolumns, string link)
    {
        aRg = worksheet.get_Range(worksheet.Cells[startRows, startColumns], worksheet.Cells[endRows, endcolumns]);

        //参数说明：单元格，链接地址，missing，鼠标在超链接上显示内容，单元格内的文本
        aRg.Hyperlinks.Add(aRg.Cells, link, Type.Missing, link, link);
        aRg = null;
    }

    /// <summary>
    /// 设置单个单元格文字内容颜色
    /// </summary>
    /// <param name="iRow">行</param>
    /// <param name="iCol">列</param>
    /// <param name="sColor">颜色</param>
    public void SetCellFontColor(int iRow, int iCol, Microsoft.Office.Interop.Excel.Constants sColor)
    {
        aRg = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
        aRg.Font.Color = sColor;
        aRg = null;
    }
    /// <summary>
    /// 设置单个单元格文字内容颜色
    /// </summary>
    /// <param name="iRow">行</param>
    /// <param name="iCol">列</param>
    /// <param name="iColorIndex">索引</param>
    public void SetCellFontColor(int iRow, int iCol, int iColorIndex)
    {
        aRg = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
        aRg.Font.ColorIndex = iColorIndex;
        aRg = null;
    }

    /// <summary>
    /// 设置单个单元格内容文字大小
    /// </summary>
    /// <param name="iRow">行</param>
    /// <param name="iCol">列</param>
    /// <param name="iSize">大小</param>
    public void SetCellFontSize(int iRow, int iCol, int iSize)
    {
        aRg = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
        aRg.Font.Size = iSize;
        aRg = null;
    }

    /// <summary>
    /// 设置单个单元格内容对齐方式
    /// </summary>
    /// <param name="iRow">行</param>
    /// <param name="iCol">列</param>
    /// <param name="HorizontalAlignment">对齐方式</param>
    public void SetCellFontAlign(int iRow, int iCol, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)
    {
        aRg = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
        aRg.HorizontalAlignment = HorizontalAlignment;
        aRg = null;
    }


    /// <summary>
    /// 设置多个单元格的属性   字体，   大小，颜色   ，对齐方式
    /// </summary>
    /// <param name="Start_iRow">起始行</param>
    /// <param name="Start_iCol">起始列</param>
    /// <param name="End_iRow">结束行</param>
    /// <param name="End_iCol">结束列</param>
    /// <param name="iSize">大小</param>
    /// <param name="fontName">字体</param>
    /// <param name="sColor">颜色</param>
    /// <param name="HorizontalAlignment">对齐方式</param>
    public void SetCellProperty(int Start_iRow, int Start_iCol, int End_iRow, int End_iCol, int iSize, string fontName, Microsoft.Office.Interop.Excel.Constants sColor, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)
    {
        worksheet.get_Range(worksheet.Cells[Start_iRow, Start_iCol], worksheet.Cells[End_iRow, End_iCol]).Font.Name = fontName;
        worksheet.get_Range(worksheet.Cells[Start_iRow, Start_iCol], worksheet.Cells[End_iRow, End_iCol]).Font.Size = iSize;
        worksheet.get_Range(worksheet.Cells[Start_iRow, Start_iCol], worksheet.Cells[End_iRow, End_iCol]).Font.Color = sColor;
        worksheet.get_Range(worksheet.Cells[Start_iRow, Start_iCol], worksheet.Cells[End_iRow, End_iCol]).HorizontalAlignment = HorizontalAlignment;
    }
    #endregion

    #region 内存数据写入Excel
    /// <summary>
    /// 将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一
    /// </summary>
    /// <param name="dt">DataTable集合</param>
    /// <param name="ws">工作簿名称</param>
    /// <param name="iRow">起始行</param>
    /// <param name="iCol">起始列</param>
    public void InsertTable(System.Data.DataTable dt, int iRow, int iCol)
    {
        for (int i = 0; i <= dt.Rows.Count - 1; i++)
        {
            for (int j = 0; j <= dt.Columns.Count - 1; j++)
            {
                worksheet.Cells[iRow + i, j + iCol] = dt.Rows[i][j].ToString();
            }
        }
    }
    #endregion

    #region 插入图片
    /// <summary>
    /// 插入图片
    /// </summary>
    /// <param name="Filename">图片名称地址</param>
    /// <param name="left">距离左边的距离</param>
    /// <param name="top">距离顶部的距离</param>
    /// <param name="Height">图片的高</param>
    /// <param name="Width">图片的宽</param>
    public void InsertPictures(string Filename,int left, int top, int Height, int Width)
    {
        worksheet.Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, left, top, Width, Height);
    }
    #endregion


    #region 关闭操作
    /// <summary>
    /// 保存Excel文档
    /// </summary>
    /// <param name="FileName">文件名称</param>
    /// <returns>true:保存成功, false:失败</returns>
    public bool Save(object FileName)
    {
        try
        {
            FApp.DisplayAlerts = DisplayAlerts;

            workbook.SaveAs(FileName, 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;
        }
        catch (Exception ex)
        {
            return false;
        }
    }
    /// <summary>
    /// 关闭excel
    /// </summary>
    public void Close()
    {
        workbook.Close(false, Type.Missing, Type.Missing);
        workbooks.Close();
        FApp.Quit();
        workbook = null;
        workbooks = null;
        FApp = null;
        GC.Collect();
    }
    #endregion
}

关于Execl颜色索引的补充：

1	2	3	4	5	6	7	8	9	10
11	12	13	14	15	16	17	18	19	20
21	22	23	24	25	26	27	28	29	30
31	32	33	34	35	36	37	38	39	40
41	42	43	44	45	46	47	48	49	50
51	52	53	54	55	56