C#利用NPOI导出Excel-ASP.NET MVC示例

Excel结构：工作薄.xls->工作表sheet->单元格Cell.

使用NPOI导出Excel速度挺快。

微软内置的COM组件，需要依赖office，而且发布到IIS权限配置很麻烦。而且效率较低。

NPOI导出Excel主要C#代码：

写入Excel文件类：

public static class XlsWriter    {        public static IWorkbook Writer(XlsWorkSet workSet)        {            if (workSet == null || workSet.Sheets == null || workSet.Sheets.Length == 0)                throw new ArgumentNullException();            // 工作簿            IWorkbook work = RenderWorkBook(workSet.Version);            for (var i = 0; i < workSet.Sheets.Length; i += 1)            {                var layer = workSet.Sheets[i];                ISheet workSheet = RenderSheet(work, layer);                DataTable dataRow = layer.Rows;                for (int row = 0; row < dataRow.Rows.Count; row += 1)                {                    IRow sheetRow = workSheet.CreateRow(row + layer.titleRowIndex);                    for (int cols = 0; cols < layer.Fields.Length; cols += 1)                    {                        ICell cell = sheetRow.CreateCell(cols);                        DataSetter(dataRow.Rows[row][layer.Fields[cols].FieldName], cell, layer.Fields[cols]);                    }                }                AutoFitBodyWidth(workSheet, layer);            }            return work;        }        public static byte[] ToByte(this IWorkbook work)        {            using (MemoryStream ms = new MemoryStream())            {                work.Write(ms);                return ms.ToArray();            }        }        private static ISheet RenderSheet(IWorkbook work, XlsWorkSheet sheet)        {            ISheet workSheet = work.CreateSheet(sheet.SheetName);            if (sheet.Fields == null || sheet.Fields.Length == 0)                return workSheet;            IRow sheetRow = workSheet.CreateRow(0);            var format = work.CreateDataFormat();            XlsField field = null;            for (int i = 0; i < sheet.Fields.Length; i += 1)            {                field = sheet.Fields[i];                ICell cell = sheetRow.CreateCell(i);                cell.SetCellValue(field.Title);                ICellStyle style1 = work.CreateCellStyle();                IFont font1 = work.CreateFont();                field.HeaderS.Initilize(style1, font1);                cell.CellStyle = style1;                ICellStyle style2 = work.CreateCellStyle();                IFont font2 = work.CreateFont();                field.CellS.Initilize(style2, font2);                if (field.DataFormat != null                    && field.DataFormatType != XlsDataFormatType.String)                    style2.DataFormat = format.GetFormat(field.DataFormat);            }            RenderCellProperties(workSheet, sheet);            return workSheet;        }        private static void DataSetter(object value, ICell cell, XlsField field)        {            cell.CellStyle = field.CellS._style;            switch (field.DataFormatType)            {                case XlsDataFormatType.Boolean:                    cell.SetCellValue(Convert.ToBoolean(value));                    break;                case XlsDataFormatType.DateTime:                    string strDate = value.ToString();                    if (strDate.Length == 0)                        cell.SetCellValue(strDate);                    else                        cell.SetCellValue(DateTime.Parse(strDate));                    break;                case XlsDataFormatType.Double:                    cell.SetCellValue(Convert.ToDouble(value));                    break;                default:                    cell.SetCellValue(Convert.ToString(value));                    break;            }        }        private static IWorkbook RenderWorkBook(ExcelVersion version)        {            if (version == ExcelVersion.Excel07)                return new XSSFWorkbook();            else                return new HSSFWorkbook();        }        private static void RenderCellProperties(ISheet workSheet, XlsWorkSheet sheet)        {            for (int cols = 0; cols < sheet.Fields.Length; cols += 1)            {                if (sheet.Fields[cols].AutoFitBodyWidth == false)                    // 标题自适应宽度                    if (sheet.Fields[cols].AutoFitHeaderWidth)                        workSheet.AutoSizeColumn(cols);                    else                        workSheet.SetColumnWidth(cols, sheet.Fields[cols].Width << 8);            }            // 单元格冻结            if (sheet.FrozenColumns > 0 || sheet.FrozenRows > 0)                workSheet.CreateFreezePane(sheet.FrozenColumns, sheet.FrozenRows);        }        private static void AutoFitBodyWidth(ISheet workSheet, XlsWorkSheet sheet)        {            for (int cols = 0; cols < sheet.Fields.Length; cols += 1)            {                // 内容自适应宽度                if (sheet.Fields[cols].AutoFitBodyWidth)                    workSheet.AutoSizeColumn(cols);            }        }    }

操作Excel工作表类

    /// <summary>    /// 工作表    /// </summary>    public sealed class XlsWorkSheet    {        /// <summary>        /// Sheet名称        /// </summary>        public string SheetName;        internal ushort titleRowIndex;        private XlsField[] _fields;        public XlsField[] Fields        {            get { return this._fields; }            set            {                if (value == null || value.Length == 0)                    return;                this._fields = value;                this.titleRowIndex = 1;            }        }        /// <summary>        /// 数据行        /// </summary>        public DataTable Rows;        /// <summary>        /// 冻结行        /// </summary>        public ushort FrozenRows;        /// <summary>        /// 冻结列        /// </summary>        public ushort FrozenColumns;    }

字段设置类

/// <summary>    /// 字段设置    /// </summary>    public sealed class XlsField    {        #region private fields        private bool _autoFitHeaderWidth;        private bool _autoFitBodyWidth;        private ushort _width = 0xA;        #endregion        /// <summary>        /// 列标题        /// </summary>        public string Title;        /// <summary>        /// 字段名        /// </summary>        public string FieldName;        /// <summary>        /// 单元格样式        /// </summary>        public XlsStyle CellS;        /// <summary>        /// 表头样式        /// </summary>        public XlsStyle HeaderS;        /// <summary>        /// 格式        /// </summary>        public string DataFormat;        /// <summary>        /// 格式        /// </summary>        public XlsDataFormatType DataFormatType;        /// <summary>        /// 标题自适应宽度        /// </summary>        public bool AutoFitHeaderWidth        {            get { return this._autoFitHeaderWidth; }            set { this._autoFitHeaderWidth = value; }        }        /// <summary>        /// 内容自适应宽度        /// </summary>        public bool AutoFitBodyWidth        {            get { return this._autoFitBodyWidth; }            set { this._autoFitBodyWidth = value; }        }        /// <summary>        /// 单元格宽度(默认10)        /// </summary>        public ushort Width        {            get { return this._width; }            set { this._width = value; }        }        public XlsField()        {            this.HeaderS.Alignment = HorizontalAlignment.Center;            this.HeaderS.Boldweight = FontBoldWeight.Bold;            this.HeaderS.VerticalAlignment = VerticalAlignment.Center;            this.HeaderS.FontName = "宋体";            this.HeaderS.Size = 14;            this.HeaderS.BackgroundColor = NPOI.HSSF.Util.HSSFColor.COLOR_NORMAL;            this.HeaderS.FillPattern = NPOI.SS.UserModel.FillPattern.NoFill;            //this.CellS.Alignment = HorizontalAlignment.Right;            //this.CellS.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;            this.CellS.FontName = "宋体";            this.CellS.Size = 12;            this.CellS.BackgroundColor = NPOI.HSSF.Util.HSSFColor.COLOR_NORMAL;            this.CellS.FillPattern = NPOI.SS.UserModel.FillPattern.NoFill;        }    }

样式类

 public struct XlsStyle    {        public HorizontalAlignment Alignment;        public short BackgroundColor;        public FillPattern FillPattern;        public VerticalAlignment VerticalAlignment;        public bool WrapText;        #region 字体        public short Color;        public string FontName;        public bool IsItalic;        public FontBoldWeight Boldweight;        public short Size;        #endregion        #region internal fields        internal ICellStyle _style;        #endregion        internal void Initilize(ICellStyle style, IFont font)        {            style.Alignment = this.Alignment;            style.FillBackgroundColor = this.BackgroundColor;            style.FillPattern = this.FillPattern;            style.VerticalAlignment = this.VerticalAlignment;            style.WrapText = this.WrapText;            font.Color = this.Color;            font.FontName = this.FontName;            font.IsItalic = this.IsItalic;            font.Boldweight = (short)this.Boldweight;            font.FontHeightInPoints = this.Size;            style.SetFont(font);            this._style = style;        }    }

字段格式化类型

/// <summary>    /// 字段格式化类型    /// </summary>    public enum XlsDataFormatType    {        /// <summary>        /// 字符串(默认)        /// </summary>        String,        /// <summary>        /// 日期类型        /// </summary>        DateTime,        /// <summary>        /// 数值类型        /// </summary>        Double,        /// <summary>        /// bool类型        /// </summary>        Boolean    }

工作簿设置

    public sealed class XlsWorkSet    {        public ExcelVersion Version { get; set; }        public XlsWorkSheet[] Sheets { get; set; }    }

业务调用

 DataTable dt = GetTable("select Name,Age,Sex,Marriage,Job,Tel,Address from xxx where IsDelete=0", System.Data.CommandType.Text);            var workSet = new XlsWorkSet            {                Version = ExcelVersion.Excel07,                Sheets = new[] { new XlsWorkSheet { FrozenRows = 1 } }            };            workSet.Sheets[0].SheetName = "xxx";            workSet.Sheets[0].Rows = dt;            workSet.Sheets[0].Fields = new[] {                new XlsField {  FieldName="Name", Title="姓名",AutoFitBodyWidth=true },                new XlsField {  FieldName="Sex", Title="性别",AutoFitBodyWidth=true },                new XlsField {  FieldName="Age", Title="年龄",AutoFitBodyWidth=true},            };            return XlsWriter.Writer(workSet).ToByte();

转正byte[]了，再导出就很容易了。

比如ASP.NET MVC中使用FileResult.

public class ExcelFileResult : System.Web.Mvc.FileResult    {        private byte[] byteArray;        public ExcelFileResult(byte[] byteArray,string fileName)            :base("application/vnd.ms-excel")        {            this.FileDownloadName = fileName;            this.byteArray = byteArray;        }        protected override void WriteFile(HttpResponseBase response)        {            response.BinaryWrite(this.byteArray);        }    }

这样在控制器直接return文件结果就好了。