C# EPPlus和NPOI组件导出Excel学习总结

最近由于工作需要，要求在无法使用com组件（微软提供，需要安装office才能使用，而且，微软的尿性 大家懂得）的情况下来导出。于是自己在网上找到了EPPlus和NPOI两个插件来导出。
(两个组件的网址，http://epplus.codeplex.com/， http://npoi.codeplex.com/)
具体做法是自己封装了一个公共基类，提供一些接口来实现项目所需要的功能，然后分别使用两种组件来实现这些接口。
实现如下，先从官网下载两个组件所需要的dll文件(EPPlus为EPPlus.dll，NPOI为NPOI.dll、NPOI.OOXML.dll、NPOI.OpenXml4Net.dll、NPOI.OpenXmlFormate.dll)，导入到项目中，然后使用相应的命名空间即可使用该组件。
其中EPPlus中主要使用到的命名空间有OfficeOpenXml和OfficeOpenXml.Style，后者主要是控制单元格样式所用到的命名空间。
其中NPOI主要用到得命名空间有NPOI.HSSF.UserModel、NPOI.SS.UserModel、NPOI.HPSF、NPOI.SS.Util

源码如下：其中主动抛出异常部分是我现在修改的，项目中会做特殊处理，这里无法实现了
接口（抽象类）定义如下：

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace ExcelTool{    enum EnuExcelType { EPPlus, NPOI};    abstract class ExcelTool    {        public abstract void CreatExcel();        public abstract void CreatSheet(string sheetname);        public abstract void SetCurrentSheet(int sheetIndex);        public abstract void SetCellValue(int row, int col, string value);        public abstract int MergerCells(int firstRow, int firstCol, int lastRow, int lastCol);        public abstract void SetCellsStyle(int firstRow, int firstCol, int lastRow, int lastCol, bool isBold, int size);        public abstract string SaveAs(string fileName);        public abstract void CloseExcel();    }}

EPPlus 实现

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.IO;using OfficeOpenXml;using OfficeOpenXml.Style;namespace ExcelTool{    class EPPlusExcelTool : ExcelTool    {        private ExcelPackage excelPackage = null;        private ExcelWorkbook workbook = null;        private ExcelWorksheet worksheet = null;        public override void CloseExcel()        {            excelPackage.Dispose();            worksheet = null;            workbook = null;            excelPackage = null;            GC.Collect();        }        public override void CreatExcel()        {            excelPackage = new ExcelPackage();            workbook = excelPackage.Workbook;        }        public override void CreatSheet(string sheetName)        {            workbook.Worksheets.Add(sheetName);        }        public override int MergerCells(int firstRow, int firstCol, int lastRow, int lastCol)        {            worksheet.Cells[firstRow, firstCol, lastRow, lastCol].Merge = true;            return (lastCol - firstCol) * (lastRow - firstRow);        }        public override string SaveAs(string fileName)        {        if(excelPackage == null)        {        throw new Exception("请先创建excel实例");        }            string filePath = string.Empty;            filePath = System.Environment.CurrentDirectory;            string datastr = DateTime.Now.ToString();            datastr = datastr.Replace(" ", "");            datastr = datastr.Replace("/", "");            datastr = datastr.Replace(":", "");            fileName = fileName + datastr + ".xlsx";            filePath = filePath + fileName;            using (FileStream file = new FileStream(filePath, FileMode.Create))            {                excelPackage.SaveAs(file);            }            return filePath;        }        public override void SetCellsStyle(int firstRow, int firstCol, int lastRow, int lastCol, bool isBold, int size)        {            using (var range = worksheet.Cells[firstRow, firstCol, lastRow, lastCol])            {                range.Style.Font.Bold = isBold;                range.Style.Font.Size = size;            }        }        public override void SetCellValue(int row, int col, string value)        {            if (workbook == null)            {                throw new Exception("请先创建excel实例");            }            if (worksheet == null)            {                throw new Exception("请先设置当前使用工作簿");            }            worksheet.Cells[row, col].Value = value;        }        public override void SetCurrentSheet(int sheetIndex)        {            if (workbook.Worksheets.Count < sheetIndex)            {                throw new Exception("没有这么多工作簿");            }            worksheet = workbook.Worksheets[sheetIndex];        }    }}

NPOI 实现

using System;using System.Collection.Generic;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using NPOI.HPSF;using System.IO;using NPOI.SS.Utilnamespace ExcelTool{    class NPOIExcelTool : ExcelTool    {    private HSSFWorkbook h_workbook;    private ISheet h_sheet;    public NPOIExcelTool()    {        h_workbook = null;        h_sheet = null;    }    public override void CloseExcel()        {            h_workbook.Close();            h_sheet = null;            h_workbook = null;            GC.Collect();        }        public override void CreatExcel()        {            h_workbook = new HSSFWorkbook();        }        public override void CreatSheet(string sheetName)        {            if(h_workbook == null)            {        throw new Exception("");        }        var sheet = h_worksheet.CreateSheet(sheetName);        }        public override int MergerCells(int firstRow, int firstCol, int lastRow, int lastCol)        {            if(h_sheet == null)            {        throw new Exception("请选设置当前使用工作簿");        }        CellRangeAddress range = new CellRangeAddress(firstRow - 1, lastRow - 1, firstCol - 1, lastCol - 1);        //        return (lastRow - firstRow) * (lastCol - firstCol);        }        public override string SaveAs(string fileName)        {        if(excelPackage == null)        {        throw new Exception("请先创建excel实例");        }            string filePath = string.Empty;            filePath = System.Environment.CurrentDirectory;            string datastr = DateTime.Now.ToString();            datastr = datastr.Replace(" ", "");            datastr = datastr.Replace("/", "");            datastr = datastr.Replace(":", "");            fileName = fileName + datastr + ".xlsx";            filePath = filePath + fileName;            using (FileStream file = new FileStream(filePath, FileMode.Create))            {                h_workbook.Write(file);            }            return filePath;        }        public override void SetCellsStyle(int firstRow, int firstCol, int lastRow, int lastCol, bool isBold, int size)        {            if(h_workbook = null)            {                throw new Exception("请先创建Excel");            }            if(h_sheet == null)            {            throw new Exception("请先创建Excel或设置当前使用工作簿");            }            var cellsStyle = h_workbook.CreateCellStyle();            var iFont = h_workbook.CreateFont();            iFont.IsBoid = isBold;            iFont.FontHeightInPoint = size;            cellsStyle.VerticalAlignment = VerticalAlignment.Center;            for(int i = firstRow - 1; i < lastRow; i++)            {            for(int j = firstCol - 1; j < lastCol; j++)            {            var row = h_sheet.GetRow(i);            if(row == null)            {            row = h_sheet.CreateRow(i);            var cell = row.GetCell(j);            if(cell == null)            {                cell = row.CreateCell(j);                cell.CellStyle = cellsStyle;            }            else            {                cell.CellStyle = cellsStyle;            }            }            else            {            var cell = row.GetCell(j);            if(cell == null)            {                cell = row.CreateCell(j);                cell.CellStyle = cellsStyle;            }            else            {                cell.CellStyle = cellsStyle;            }            }            }            }        }        public override void SetCellValue(int row, int col, string value)        {            if(h_sheet == null)            {            throw new Exception("请先创建Excel或设置当前使用工作簿");            }            if(h_sheet.GetRow(row - 1) == null)            {            var row = h_sheet.CreatRow(row - 1);            if(row.GetCell(col - 1) == null)            {            var cell = row.CreateCell(col - 1);            cell.SetCellValue(value);            }            else            {            var cell = row.GetCell(col - 1);            cell.SetCellValue(value);            }            }            else            {            var row = h_sheet.GetRow(row - 1);            if(row.GetCell(col - 1) == null)            {            var cell = row.CreateCell(col - 1);            cell.SetCellValue(value);            }            else            {            var cell = row.GetCell(col - 1);            cell.SetCellValue(value);            }            }        }        public override void SetCurrentSheet(int sheetIndex)        {            if(h_workbook.GetSheetAt(sheetIndex - 1) == null)            {        throw new Exception("未找到编号为" + sheetIndex.ToString() + "的工作簿");        }        h_workbook.SetActiveSheet(sheetIndex - 1);        h_sheet = h_workbook.GetSheetAt(h_workbook.ActiveSheetIndex);        }    }}

调用

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace ExcelTool{    class Program    {        static void Main(string[] args)        {            ExcelTool excelTool = null;            EnuExcelType type = EnuExcelType.EPPlus;            if (type == EnuExcelType.EPPlus)            {                excelTool = new EPPlusExcelTool();            }            excelTool.CreatExcel();            excelTool.CreatSheet("TestSheet");            excelTool.SetCurrentSheet(1);            excelTool.SetCellValue(1, 1, "test");            excelTool.SetCellValue(1, 3, "aaa");            excelTool.MergerCells(1, 1, 1, 2);            excelTool.SetCellsStyle(1, 1, 1, 3, true, 13);            excelTool.SaveAs("test");            excelTool.CloseExcel();        }    }}

NPOI部分明天下班再加，

新人第二篇 还望各位大神指点