基于C#语言MVC框架NPOI控件导出Excel表数据

控件bin文件下载地址：https://download.csdn.net/download/u012949335/10610726
@{
    ViewBag.Title = "dcxx";
}
<script type="text/javascript" language="javascript">
 
    function excelport()
    {
        var checktag=$("#ischecked:checked");
        var form=$("<form action=\"dcxx\" method=\"post\"></form>");
        $(document.body).append(form);
        form.submit();
    }
</script>
<button type="button" class="but-primary" onclick="excelport()"> 导出数据</button>



//调用方法
public ActionResult dcxx()
        {
           
            DataTable dt = dal.getlist(" 1=1 ");
            NpoiWorkbook book = new NpoiWorkbook();
            book.CreateSheet(dt, "xxx信息");
            Stream excelstream = book.GetMemoryStream();
            return File(excelstream, "application/octet-stream", "xx信息.xls");
        }



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.IO;
using System.Data;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
namespace YidiTutor.Common.NOPI
{
    /// <summary>
    /// 工作薄
    /// </summary>
    public class NpoiWorkbook
    {
        public HSSFWorkbook workbook;
        public XSSFWorkbook workbook2007;
        public bool isExcel2007 = false;//生成excel2007
        /// <summary>
        /// 表头格式
        /// </summary>
        private ICellStyle HeadStyle
        {
            get
            {
                if (!isExcel2007)
                {
                    HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                    HSSFFont font = (HSSFFont)workbook.CreateFont();
                    font.FontHeightInPoints = 11;
                    font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                    font.FontName = "宋体";
                    headStyle.SetFont(font);
                    return headStyle;
                }
                else
                {
                    XSSFCellStyle headStyle = (XSSFCellStyle)workbook2007.CreateCellStyle();
                    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                    XSSFFont font = (XSSFFont)workbook2007.CreateFont();
                    font.FontHeightInPoints = 11;
                    font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                    font.FontName = "宋体";
                    headStyle.SetFont(font);
                    return headStyle;
                }
            }
        }
        private ICellStyle HeadStyle_red
        {
            get
            {
                ICellStyle Iheadstyle = null;
                if (!isExcel2007)
                {
                    HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                    HSSFFont font = (HSSFFont)workbook.CreateFont();
                    font.FontHeightInPoints = 11;
                    font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                    font.FontName = "宋体";
                    font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
                    headStyle.SetFont(font);
                    Iheadstyle = headStyle;
                }
                else
                {
                    XSSFCellStyle headStyle = (XSSFCellStyle)workbook2007.CreateCellStyle();
                    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                    XSSFFont font = (XSSFFont)workbook2007.CreateFont();
                    font.FontHeightInPoints = 11;
                    font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                    font.FontName = "宋体";
                    font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
                    headStyle.SetFont(font);
                    Iheadstyle = headStyle;
                }
                return Iheadstyle;
            }
        }
        /// <summary>
        /// 时间格式
        /// </summary>
        private ICellStyle DateStyle
        {
            get
            {
                ICellStyle datestyle = null;
                if (!isExcel2007)
                {
                    HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                    HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
                    dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
                    datestyle = dateStyle;
                }
                else
                {
                    XSSFCellStyle dateStyle = (XSSFCellStyle)workbook2007.CreateCellStyle();
                    XSSFDataFormat format = (XSSFDataFormat)workbook2007.CreateDataFormat();
                    dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
                    datestyle = dateStyle;
                }
                return datestyle;
            }
        }
 
        private ICellStyle ContentStyle
        {
            get
            {
                ICellStyle contentstyle = null;
                if (!isExcel2007)
                {
                    HSSFCellStyle contentStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                    contentStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    contentStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                    HSSFFont font = (HSSFFont)workbook.CreateFont();
                    font.FontHeightInPoints = 11;
                    font.FontName = "宋体";
                    contentStyle.SetFont(font);
                    contentstyle = contentStyle;
                }
                else
                {
                    XSSFCellStyle contentStyle = (XSSFCellStyle)workbook2007.CreateCellStyle();
                    contentStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    contentStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                    XSSFFont font = (XSSFFont)workbook2007.CreateFont();
                    font.FontHeightInPoints = 11;
                    font.FontName = "宋体";
                    contentStyle.SetFont(font);
                    contentstyle = contentStyle;
                }
                return contentstyle;
            }
        }
 
        private ICellStyle ContentStyle_red
        {
            get
            {
                ICellStyle contentStyle_red = null;
                if (!isExcel2007)
                {
                    HSSFCellStyle contentStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                    contentStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    contentStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                    HSSFFont font = (HSSFFont)workbook.CreateFont();
                    font.FontHeightInPoints = 11;
                    font.FontName = "宋体";
                    font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
                    contentStyle.SetFont(font);
                    contentStyle_red = contentStyle;
                }
                else
                {
                    XSSFCellStyle contentStyle = (XSSFCellStyle)workbook2007.CreateCellStyle();
                    contentStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    contentStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                    XSSFFont font = (XSSFFont)workbook2007.CreateFont();
                    font.FontHeightInPoints = 11;
                    font.FontName = "宋体";
                    font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
                    contentStyle.SetFont(font);
                    contentStyle_red = contentStyle;
                }
                return contentStyle_red;
            }
        }
        /// <summary>
        /// 实例一个工作薄
        /// </summary>
        public NpoiWorkbook()
        {
            workbook2007 = new XSSFWorkbook();
            workbook = new HSSFWorkbook();
            #region 右击文件 属性信息
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "SiBu";
            workbook.DocumentSummaryInformation = dsi;
 
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.CreateDateTime = System.DateTime.Now;
            workbook.SummaryInformation = si;
            #endregion
        }
 
        /// <summary>
        /// 导出excel初始化
        /// </summary>
        /// <param name="isexcel2007"></param>
        public NpoiWorkbook(bool isexcel2007)
            : this()
        {
            isExcel2007 = isexcel2007;
        }
        /// <summary>
        /// 读取excel初始化,加载Excel文件
        /// </summary>
        /// <param name="filePath">文件路径</param>
        public NpoiWorkbook(string filePath)
        {
            string filetype = Path.GetExtension(filePath).ToLower();
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    if (".xls".Equals(filetype))
                    {
                        isExcel2007 = false;
                    }
                    else
                    {
                        isExcel2007 = true;
                    }
                    if (filetype.Equals(".xls"))
                    {
                        workbook = new HSSFWorkbook(file);
                        isExcel2007 = false;
                    }
                    else
                    {
                        workbook2007 = new XSSFWorkbook(file);
                        isExcel2007 = true;
                    }
                }
            }
            catch (Exception ex)
            {
 
            }
        }
        /// <summary>
        /// 读取excel初始化,加载Excel文件流
        /// </summary>
        /// <param name="excelstream">excel文件流，使用场景：excel上传导入时候直接获取的文件流</param>
        /// <param name="filename">带后缀名的文件名(格式如xx.xls或D:\excel\xx.xls)</param>
        public NpoiWorkbook(Stream excelstream, string filename)
        {
            string filetype = Path.GetExtension(filename).ToLower();
            if (".xls".Equals(filetype))
            {
                workbook = new HSSFWorkbook(excelstream);
                isExcel2007 = false;
            }
            else
            {
                workbook2007 = new XSSFWorkbook(excelstream);
                isExcel2007 = true;
            }
 
        }
        /// <summary>
        /// 获取Sheet页的数据
        /// </summary>
        /// <param name="sheetIndex">Sheet页Index，从0开始</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTable(int sheetIndex = 0)
        {
            DataTable dt = new DataTable();
            ISheet sheet = null;
            IWorkbook iworkbook = null;
            if (!isExcel2007)
            {
                sheet = (HSSFSheet)workbook.GetSheetAt(sheetIndex);
                iworkbook = workbook;
            }
            else
            {
                sheet = (XSSFSheet)workbook2007.GetSheetAt(sheetIndex);
                iworkbook = workbook;
            }
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
 
            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;
 
            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }
 
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                    continue;
                DataRow dataRow = dt.NewRow();
 
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    ICell cell = row.GetCell(j);
                    if (cell != null)
                    {
                        if (cell.CellType == CellType.Numeric)
                        {
                            //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                            if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                            {
                                dataRow[j] = cell.DateCellValue;
                            }
                            else//其他数字类型
                            {
                                dataRow[j] = cell.NumericCellValue;
                            }
                        }
                        else if (cell.CellType == CellType.Blank)//空数据类型
                        {
                            dataRow[j] = "";
                        }
                        else if (cell.CellType == CellType.Formula)//公式类型
                        {
                            HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(iworkbook);
                            dataRow[j] = eva.Evaluate(cell).StringValue;
                        }
                        else //其他类型都按字符串类型来处理
                        {
                            dataRow[j] = cell.StringCellValue;
                        }
                    }
                }
 
                dt.Rows.Add(dataRow);
            }
            return dt;
        }
 
        /// <summary>
        /// 创建一个Sheet页
        /// </summary>
        /// <param name="Sheet">Sheet</param>
        public void CreateSheet(Sheet sheetInfo)
        {
            if (string.IsNullOrWhiteSpace(sheetInfo.Name)) sheetInfo.Name = "Sheet" + workbook.NumberOfSheets + 1;
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetInfo.Name);
            int rowIndex = 0;
 
            #region 新建表，填充表头，填充列头，样式
            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
            headerRow.HeightInPoints = 20;
            var columIndex = 0;
            foreach (var column in sheetInfo.Columns)
            {
                headerRow.CreateCell(columIndex).SetCellValue(column.Name);
                if ("Red".Equals(column.ColorType))
                    headerRow.GetCell(columIndex).CellStyle = HeadStyle_red;
                else
                    headerRow.GetCell(columIndex).CellStyle = HeadStyle;
                //设置列宽
                sheet.SetColumnWidth(columIndex, column.Width * 256);
                sheet.SetColumnHidden(columIndex, column.Hidden);
                columIndex++;
            }
 
            #endregion
            #region 填充内容
            rowIndex = 1;
 
            HSSFCellStyle contentStyle = (HSSFCellStyle)ContentStyle;
            foreach (DataRow row in sheetInfo.DataSource.Rows)
            {
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                var columnIndex = 0;
                //dataRow.RowStyle = ContentStyle;
                foreach (var column in sheetInfo.Columns)
                {
                    HSSFCell newCell = (HSSFCell)dataRow.CreateCell(columnIndex);
                    if (!sheetInfo.DataSource.Columns.Contains(column.Code))
                    {
                        newCell.SetCellValue("");
                    }
                    else
                    {
                        string drValue = row[column.Code].ToString();
 
                        switch (column.DataType.ToUpper())
                        {
                            case "S"://字符串类型
                                newCell.SetCellValue(drValue);
                                if ("Red".Equals(column.ColorType))
                                    newCell.CellStyle = ContentStyle_red;//格式化显示
                                else
                                    newCell.CellStyle = contentStyle;//格式化显示
                                break;
                            case "D"://日期类型
                                System.DateTime dateV;
                                System.DateTime.TryParse(drValue, out dateV);
                                newCell.SetCellValue(dateV);
                                newCell.CellStyle = contentStyle;//格式化显示
                                break;
                            case "B"://布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;
                            case "I"://整型
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                newCell.SetCellValue(intV);
                                break;
                            case "F"://浮点型
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            default:
                                newCell.SetCellValue(drValue);
                                break;
                        }
                    }
                    columnIndex++;
                }
                rowIndex++;
            }
            #endregion
        }
 
        public void CreateSheet(DataTable dt, string sheetname)
        {
            Sheet sheet = new Sheet();
            sheet.Name = sheetname;
            List<Column> columns = new List<Column>();
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                DataColumn col = dt.Columns[i];
                Column column = new Column();
                column.Code = col.ColumnName;
                column.Name = col.ColumnName;
                column.DataType = "S";
                column.Width = 30;
                column.Hidden = false;
                columns.Add(column);
            }
            sheet.Columns = columns;
            sheet.DataSource = dt;
            if (!isExcel2007)
            {
                this.CreateSheet(sheet);
            }
            else
            {
                this.CreateSheet2007(sheet);
            }
        }
 
        public void CreateSheet2007(Sheet sheetInfo)
        {
            if (string.IsNullOrWhiteSpace(sheetInfo.Name)) sheetInfo.Name = "Sheet" + workbook2007.NumberOfSheets + 1;
            XSSFSheet sheet = (XSSFSheet)workbook2007.CreateSheet(sheetInfo.Name);
 
            int rowIndex = 0;
 
            #region 新建表，填充表头，填充列头，样式
            XSSFRow headerRow = (XSSFRow)sheet.CreateRow(rowIndex);
            headerRow.HeightInPoints = 20;
            var columIndex = 0;
            foreach (var column in sheetInfo.Columns)
            {
                headerRow.CreateCell(columIndex).SetCellValue(column.Name);
                if ("Red".Equals(column.ColorType))
                    headerRow.GetCell(columIndex).CellStyle = HeadStyle_red;
                else
                    headerRow.GetCell(columIndex).CellStyle = HeadStyle;
                //设置列宽
                sheet.SetColumnWidth(columIndex, column.Width * 256);
                sheet.SetColumnHidden(columIndex, column.Hidden);
                columIndex++;
            }
 
            #endregion
            #region 填充内容
            rowIndex = 1;
            XSSFCellStyle contentStyle = (XSSFCellStyle)ContentStyle;
            //XSSF contentStyle = (HSSFCellStyle)ContentStyle;
            foreach (DataRow row in sheetInfo.DataSource.Rows)
            {
                XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
                var columnIndex = 0;
                //dataRow.RowStyle = ContentStyle;
                foreach (var column in sheetInfo.Columns)
                {
                    XSSFCell newCell = (XSSFCell)dataRow.CreateCell(columnIndex);
                    if (!sheetInfo.DataSource.Columns.Contains(column.Code))
                    {
                        newCell.SetCellValue("");
                    }
                    else
                    {
                        string drValue = row[column.Code].ToString();
 
                        switch (column.DataType.ToUpper())
                        {
                            case "S"://字符串类型
                                newCell.SetCellValue(drValue);
                                if ("Red".Equals(column.ColorType))
                                    newCell.CellStyle = ContentStyle_red;//格式化显示
                                else
                                    newCell.CellStyle = contentStyle;//格式化显示
                                break;
                            case "D"://日期类型
                                System.DateTime dateV;
                                System.DateTime.TryParse(drValue, out dateV);
                                newCell.SetCellValue(dateV);
                                newCell.CellStyle = contentStyle;//格式化显示
                                break;
                            case "B"://布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;
                            case "I"://整型
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                newCell.SetCellValue(intV);
                                break;
                            case "F"://浮点型
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            default:
                                newCell.SetCellValue(drValue);
                                break;
                        }
                    }
                    columnIndex++;
                }
                rowIndex++;
            }
            #endregion
        }
        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="filePath">文件路径</param>
        public void SaveAs(string filePath)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
 
                using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
 
        /// <summary>
        /// 获取Workbook的MemoryStream
        /// </summary>
        /// <returns></returns>
        public MemoryStream GetMemoryStream()
        {
            MemoryStream ms = new MemoryStream();
            if (!isExcel2007)
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
            }
            else
            {
                workbook2007.Write(ms);
            }
            return ms;
        }
    }
 
    public class Column
    {
        public string Code { get; set; }
        public string Name { get; set; }
        public string DataType { get; set; }
        public int Width { get; set; }
        public bool Hidden { get; set; }
        public string ColorType { get; set; }
        public Column() { }
        public Column(string code, string name, string dataType, int width, bool hidden = false)
        {
            Code = code;
            Name = name;
            DataType = dataType;
            Width = width;
            Hidden = hidden;
        }
    }
 
    public class Sheet
    {
        public string Name { get; set; }
        public List<Column> Columns { get; set; }
        public DataTable DataSource { get; set; }
        public Sheet() { }
        public Sheet(string name, List<Column> columns, DataTable dataSource)
        {
            Name = name;
            Columns = columns;
            DataSource = dataSource;
        }
    }
 
 
 
 
}