EPPlusHelper

        private string m_FilePath;
        private string m_DefaultDir;
        private string m_CreatingDate = DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss");

        private Stream m_Stream;                        //创建一个Stream对象
        private ExcelPackage m_ExcelPackage;            //创建一个ExcelPackage对象
        private ExcelWorksheet m_CurrentWorksheet;      //创建一个ExcelWorksheet对象
        
        /// <summary>
        /// 构造函数，默认路径
        /// </summary>
        public ExcelHelper_EPPlus()
        {
            m_DefaultDir = System.AppDomain.CurrentDomain.BaseDirectory + "Data\\";

            if (!Directory.Exists(m_DefaultDir))
                Directory.CreateDirectory(m_DefaultDir);

            m_FilePath = m_DefaultDir + m_CreatingDate + ".xlsx";
        }

        /// <summary>
        /// 构造函数，自定义路径
        /// </summary>
        /// <param name="strFilePath">Excel文件的绝对路径</param>
        public ExcelHelper_EPPlus(string strFilePath)
        {
            if (Directory.Exists(strFilePath))
            {
                m_FilePath = strFilePath;
            }
            else
            {
                string strErrorMsg = string.Format("指定路径:{0}，文件未找到！", strFilePath);
                throw new Exception(strErrorMsg);
            }
        }
        
        /// <summary>
        /// 打开一个已有的工作簿或者创建一个Excel对象
        /// </summary>
        /// public ExcelPackage();
        /// public ExcelPackage(FileInfo newFile);
        /// public ExcelPackage(Stream newStream);
        public void OpenOrCreateExcel()
        {
            if (File.Exists(m_FilePath))
            {
                var file = new FileInfo(m_FilePath);
                m_ExcelPackage = new ExcelPackage(file);
            }
            else
            {
                m_Stream = File.Create(m_FilePath);
                m_ExcelPackage = new ExcelPackage(m_Stream);
            }
            m_CurrentWorksheet = GetOrAddWorkSheet();
        }

Worksheet相关操作

        /// <summary>
        /// 获取worksheet，没有时创建
        /// </summary>
        /// <returns></returns>
        public ExcelWorksheet GetOrAddWorkSheet()
        {
            ExcelWorksheet workSheet = m_ExcelPackage.Workbook.Worksheets.FirstOrDefault();
            if (workSheet == null)
                workSheet = m_ExcelPackage.Workbook.Worksheets.Add("Sheet1");
            return workSheet;
        }

        /// <summary>
        /// 根据工作表名获取Excel工作表对象
        /// </summary>
        /// <param name="sheetName">工作表名</param>
        /// <returns></returns>
        public ExcelWorksheet GetWorksheet(string sheetName)
        {
            return m_ExcelPackage.Workbook.Worksheets.FirstOrDefault(i => i.Name == sheetName);
        }

        /// <summary>
        /// 根据工作表索引获取Excel工作表对象
        /// </summary>
        /// <param name="index">工作表序号,索引号从1开始</param>
        public ExcelWorksheet GetWorksheet(int index)
        {
            return m_ExcelPackage.Workbook.Worksheets[index];
        }

        /// <summary>
        /// 给当前工作簿添加工作表
        /// </summary>
        /// <param name="sheetName">工作表名</param>
        /// <param name="isDeleteSameNameSheet">如果存在同名的sheet是否删除</param>
        public void AddWorkSheet(string sheetName, bool isDeleteSameNameSheet)
        {
            if (isDeleteSameNameSheet)
            {
                DeleteWorkSheet(sheetName);
            }
            else
            {
                while (m_ExcelPackage.Workbook.Worksheets.Any(i => i.Name == sheetName))
                {
                    sheetName = sheetName + "(1)";
                }
            }
            m_ExcelPackage.Workbook.Worksheets.Add(sheetName);
        }

        /// <summary>
        /// 重命名工作表
        /// </summary>
        /// <param name="worksheet">工作表对象</param>
        /// <param name="newSheetName">新的工作表名</param>
        public void ReNameWorkSheet(ExcelWorksheet worksheet, string newSheetName)
        {
            worksheet.Name = newSheetName;
        }

        /// <summary>
        /// 重命名工作表
        /// </summary>
        /// <param name="oldSheetName">原有工作表名</param>
        /// <param name="newSheetName">新的工作表名</param>
        public void ReNameWorkSheet(string oldSheetName, string newSheetName)
        {
            var worksheet = GetWorksheet(oldSheetName);
            if (worksheet != null)
                worksheet.Name = newSheetName;
        }

        /// <summary>
        /// 删除指定的工作表
        /// </summary>
        /// <param name="SheetName">删除的工作表名</param>
        public void DeleteWorkSheet(string sheetName)
        {
            var worksheet = m_ExcelPackage.Workbook.Worksheets.FirstOrDefault(i => i.Name == sheetName);
            if (worksheet != null)
                m_ExcelPackage.Workbook.Worksheets.Delete(worksheet);
        }

        /// <summary>
        /// 删除除指定worksheet外的其余工作表
        /// </summary>
        /// <param name="worksheet"></param>
        public void DeleteWorkSheetExcept(ExcelWorksheet worksheet)
        {
            foreach (ExcelWorksheet sheet in m_ExcelPackage.Workbook.Worksheets)
            {
                if (sheet != worksheet)
                {
                    m_ExcelPackage.Workbook.Worksheets.Delete(sheet);
                }
            }
        }

对行列的增删操作

		/// public void InsertRow(int rowFrom, int rows);
        /// public void InsertColumn(int columnFrom, int columns);
        /// public void DeleteRow(int rowFrom, int rows);
        /// public void DeleteColumn(int columnFrom, int columns);

        /// <summary>
        /// 获取当前工作表最后一行
        /// </summary>
        /// <returns></returns>
        public int GetRowIndex()
        {
            return m_CurrentWorksheet.Dimension.End.Row;
        }

        /// <summary>
        /// 获取当前工作表最后一列
        /// </summary>
        /// <returns></returns>
        public int GetColIndex()
        {
            return m_CurrentWorksheet.Dimension.End.Column;
        }

        /// <summary>
        /// 添加表头
        /// </summary>
        /// <param name="values"></param>
        public void AddHeader(object[] values)
        {
            SetRowValues(1, values);
        }

        /// <summary>
        /// 插入数据行
        /// </summary>
        /// <param name="rowIndex">插入位置，起始位置为1</param>
        /// <param name="values">行类容，一个单元格一个对象</param>
        public void SetRowValues(int rowIndex, object[] values)
        {
            for (int i = 1; i <= values.Length; i++)
            {
                m_CurrentWorksheet.SetValue(rowIndex, i, values[i]);
            }
        }

对单元格操作

   		/// <summary>
        /// 设置单元格的值
        /// </summary>
        /// <param name="X">写入的行</param>
        /// <param name="Y">写入的列</param>
        /// <param name="value">要写入的字符串</param>
        public void SetCellValue(int X, int Y, object value)
        {
            m_CurrentWorksheet.SetValue(X, Y, value);
        }

        /// <summary>
        /// 获取单元格的值
        /// </summary>
        /// <param name="X">写入的行</param>
        /// <param name="Y">写入的列</param>
        public object GetCellValue(int X, int Y)
        {
            return m_CurrentWorksheet.GetValue(X, Y);
        }

对Range操作

 		/// <summary>
        /// 从二维数据集合中装载数据
        /// </summary>
        /// public ExcelRangeBase LoadFromCollection<T>(IEnumerable<T> Collection);
        /// public ExcelRangeBase LoadFromDataReader(IDataReader Reader, bool PrintHeaders);
        /// public ExcelRangeBase LoadFromText(FileInfo TextFile);  //这里的文件是指CSV文件
        /// <param name="table"></param>
        /// <param name="printHeaders"></param>
        public void SetRangeToWorkSheet(DataTable table, bool printHeaders)
        {
            m_CurrentWorksheet.Cells["A1"].LoadFromDataTable(table, printHeaders);
        }

        /// <summary>
        /// 获取ExcelRange的值
        /// 3个获取单元格范围的方法：
        /// public ExcelRange this[string Address] { get; }     Address是指"A1:C5"这种格式
        /// public ExcelRange this[int Row, int Col] { get; }
        /// public ExcelRange this[int FromRow, int FromCol, int ToRow, int ToCol] { get; }
        /// 重要属性
        /// public object Value { get; set; }  //获取或设置单元格的值 
        /// </summary>
        /// <param name="startX">起始单元格行号</param>
        /// <param name="startY">起始单元格列号</param>
        /// <param name="endX">结束单元格行号</param>
        /// <param name="endY">结束单元格列号</param>
        /// <returns>Range对象</returns>
        public ExcelRange GetRangeFromWorkSheet(int startX, int startY, int endX, int endY)
        {
            return m_CurrentWorksheet.Cells[startX, startY, endX, endY];
        }

        /// <summary>
        /// 保存修改
        /// </summary>
        /// <returns></returns>
        public void SaveExcel()
        {
            m_ExcelPackage.Save();
        }

        /// <summary>
        /// 文档另存为
        /// </summary>
        /// <param name="FileName">文件名</param>
        /// <returns></returns>
        public void SaveAsExcel(string FileName)
        {
            var file = new FileInfo(FileName);
            m_ExcelPackage.SaveAs(file);
        }

        public void Dispose()
        {
            if (m_ExcelPackage != null)
            {
                m_ExcelPackage.Dispose();
            }

            if (m_Stream != null)
            {
                m_Stream.Dispose();
                m_Stream.Close();
            }
        }