asp.net MVC4.0 将数据 导出 excel 表格

 

1.1 什么是NPOI

NPOI，顾名思义，就是POI的.NET版本。那POI又是什么呢？POI是一套用Java写成的库，能够帮助开发者在没有安装微软Office的情况下读写Office 97-2003的文件，支持的文件格式包括xls, doc, ppt等。

在开发之前，要引用 NPOI.dll， 官方下载下载地址：http://npoi.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=24305。

 

下载之后要在项目中引用 NPOI.dll 。引用的时候，要注意，一定要 将 NPOI.dll 文件拷贝到 项目 目录之下 再引用，提交的时候，将NPOI.dll 文件一并提交到 SVN 服务器，这样其他同事 更新项目的时候就会下载NPOI.dll。

 

 

效果如下图所示：

 

关键代码：

创建 导出 Excel 的工具类

ExcelUtil.cs类文件内容如下：

using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using System.Web;

namespace Framework.ExcelUtil
{
    public class ExcelUtil
    {    

        /// <summary>
        /// 将泛类型集合List类转换成DataTable
        /// </summary>
        /// <param name="list">泛类型集合</param>
        /// <returns></returns>
        public static DataTable ListToDataTable<T>(List<T> entitys)
        {
            //检查实体集合不能为空
            if (entitys == null || entitys.Count < 1)
            {
                throw new Exception("需转换的集合为空");
            }
            //取出第一个实体的所有Propertie
            Type entityType = entitys[0].GetType();
            PropertyInfo[] entityProperties = entityType.GetProperties();

            //生成DataTable的structure
            //生产代码中，应将生成的DataTable结构Cache起来，此处略
            DataTable dt = new DataTable();
            for (int i = 0; i < entityProperties.Length; i++)
            {
                //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
                dt.Columns.Add(entityProperties[i].Name);
            }
            //将所有entity添加到DataTable中
            foreach (object entity in entitys)
            {
                //检查所有的的实体都为同一类型
                if (entity.GetType() != entityType)
                {
                    throw new Exception("要转换的集合元素类型不一致");
                }
                object[] entityValues = new object[entityProperties.Length];
                for (int i = 0; i < entityProperties.Length; i++)
                {
                    entityValues[i] = entityProperties[i].GetValue(entity, null);
                }
                dt.Rows.Add(entityValues);
            }
            return dt;
        }

        //将IList 转化为 DATATable
        public static DataTable ConvertToDataTable<T>(IList<T> i_objlist)
        {
            if (i_objlist == null || i_objlist.Count <= 0)
            {
                return null;
            }
            DataTable dt = new DataTable(typeof(T).Name);
            DataColumn column;
            DataRow row;
            System.Reflection.PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
            foreach (T t in i_objlist)
            {
                if (t == null)
                {
                    continue;
                }
                row = dt.NewRow();
                for (int i = 0, j = myPropertyInfo.Length; i < j; i++)
                {
                    System.Reflection.PropertyInfo pi = myPropertyInfo[i];

                    string name = pi.Name;
            
                        if (dt.Columns[name] == null)
                        {
                            //, pi.PropertyType
                            column = new DataColumn(name);
                            dt.Columns.Add(column);
                        }

                        row[name] = pi.GetValue(t, null);
                  
                }
                dt.Rows.Add(row);
            }
            return dt;

        }


       


        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="headers">需要导出的列的列头</param>
        /// <param name="cellKes">需要导出的对应的列字段</param>
        /// <param name="strFileName">保存位置</param>
        public static void Export(DataTable dtSource, string strHeaderText, string[] headers, string[] cellKes, string strFileName)
        {
            // 将需要导出的数据导到excel中并生成文件流
            using (MemoryStream ms = Export(dtSource, strHeaderText, headers, cellKes))
            {
                // 将文件流写入文件
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }

        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="headers">需要导出的列的列头</param>
        /// <param name="cellKes">需要导出的对应的列字段</param>
        /// <returns></returns>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText, string[] headers, string[] cellKes)
        {
            // excel
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 创建一个sheet页，已strHeaderText命名
            ISheet sheet = workbook.CreateSheet(strHeaderText);

            #region 右击文件 属性信息
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "NPOI";
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                //si.Author = "文件作者信息"; //填加xls文件作者信息
                //si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
                //si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
                //si.Comments = "作者信息"; //填加xls文件作者信息
                //si.Title = "标题信息"; //填加xls文件标题信息
                //si.Subject = "主题信息";//填加文件主题信息
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            // 日期的样式
            ICellStyle dateStyle = workbook.CreateCellStyle();
            // 日期的格式化
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            // 字体样式
            IFont datafont = workbook.CreateFont();
            // 字体大小
            datafont.FontHeightInPoints = 11;
            dateStyle.SetFont(datafont);
            // 边框
            dateStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            dateStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            dateStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            dateStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            // 其他数据的样式
            ICellStyle cellStyle = workbook.CreateCellStyle();
            // 字体样式
            IFont cellfont = workbook.CreateFont();
            // 字体大小
            cellfont.FontHeightInPoints = 11;
            cellStyle.SetFont(cellfont);
            // 边框
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            // 总的列数
            int colNum = headers.Length;
            // 每个列的宽度
            int[] arrColWidth = new int[colNum];
            // 初始化列的宽度为列头的长度，已需要显示的列头的名字长度计算
            for (int i = 0; i < headers.Length; i++ )
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(headers[i]).Length * 3;
            }

            // 循环数据，取每列数据最宽的作为该列的宽度
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < cellKes.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][cellKes[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }

            // 记录生成的行数
            int rowIndex = 0;

            // DataTable中的列信息
            DataColumnCollection columns = dtSource.Columns;
            // 循环所有的行，向sheet页中添加数据
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    // 如果不是第一行，则创建一个新的sheet页
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }

                    #region 表头及样式
                    {
                        // 在当前sheet页上创建第一行
                        IRow headerRow = sheet.CreateRow(0);
                        // 该行的高度
                        headerRow.HeightInPoints = 50;
                        // 设置第一列的值
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);
                        
                        // 设置列的样式
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        // 内容居中显示
                        headStyle.Alignment = HorizontalAlignment.Center;
                        // 字体样式
                        IFont font = workbook.CreateFont();
                        // 字体大小
                        font.FontHeightInPoints = 20;
                        // 粗体显示
                        font.Boldweight = 700;
                        // 字体颜色
                        font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
                        
                        headStyle.SetFont(font);
                        // 边框
                        headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                        headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                        headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                        headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                        // 设置单元格的样式
                        headerRow.GetCell(0).CellStyle = headStyle;

                        // 设置该行每个单元格的样式
                        for (int i = 1; i < colNum; i++)
                        {
                            headerRow.CreateCell(i);
                            headerRow.GetCell(i).CellStyle = headStyle;
                        }

                        // 合并单元格
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, colNum - 1));
                    }
                    #endregion


                    #region 列头及样式
                    {
                        // 创建第二行
                        IRow headerRow = sheet.CreateRow(1);
                        // 该行的高度
                        headerRow.HeightInPoints = 28;
                        // 列的样式
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        // 单元格内容居中显示
                        headStyle.Alignment = HorizontalAlignment.Center;
                        // 字体样式
                        IFont font = workbook.CreateFont();
                        // 字体大小
                        font.FontHeightInPoints = 12;
                        // 粗体
                        font.Boldweight = 700;
                        // 字体颜色
                        font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;
                        headStyle.SetFont(font);
                        // 边框
                        headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                        headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                        headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                        headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                        // 设置每列的样式和值
                        for (int i = 0; i < headers.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(headers[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;

                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
                        }
                    }
                    #endregion

                    rowIndex = 2;
                }
                #endregion


                #region 填充内容
                // 创建新的一行
                IRow dataRow = sheet.CreateRow(rowIndex);
                // 该行的高度
                dataRow.HeightInPoints = 23;
                // 循环需要写入的每列数据
                for (int i = 0; i < cellKes.Length; i++)
                {
                    // 创建列
                    ICell newCell = dataRow.CreateCell(i);
                    // 获取DataTable中该列对象
                    DataColumn column = columns[cellKes[i]];
                    // 该列的值
                    string drValue = row[column].ToString();

                    // 根据值得类型分别处理之后赋值
                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            newCell.SetCellValue(drValue);

                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);

                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);

                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);

                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");

                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");

                            newCell.CellStyle = cellStyle;
                            break;
                    }
                }
                #endregion

                rowIndex++;
            }

            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return ms;
        }

        /// <summary>
        /// 用于Web导出
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="headers">需要导出的列的列头</param>
        /// <param name="cellKes">需要导出的对应的列字段</param>
        /// <param name="strFileName">文件名</param>
        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string[] headers, string[] cellKes, string strFileName)
        {
            HttpContext curContext = HttpContext.Current;

            // 设置编码和附件格式
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));

            curContext.Response.BinaryWrite(Export(dtSource, strHeaderText, headers, cellKes).GetBuffer());
            curContext.Response.End();
        }

        /// <summary>读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable Import(string strFileName)
        {
            DataTable dt = new DataTable();

            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            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);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }

                dt.Rows.Add(dataRow);
            }
            return dt;
        }
    }
}

 

页面源代码如下：

@{
    ViewBag.Title = "用户日志管理";
    Layout = "~/Views/Shared/_ListLayout.cshtml";
}
@section nav{
    <nav class="breadcrumb">
        <i class="Hui-iconfont">&#xe67f;</i> 首页 <span class="c-gray en">&gt;</span>用户日志管理 
        <span class="c-gray en">&gt;</span> 日志 <a class="btn btn-success radius r mr-20" style="line-height:1.6em;margin-top:3px" href="javascript:location.replace(location.href);" title="刷新" ><i class="Hui-iconfont">&#xe68f;</i></a>
          <a href="javascript:;" onclick="UserExcel()" style="line-height:1.6em;margin-top:3px" class="btn btn-primary radius r  mr-20"><i class="Hui-iconfont"></i> 导出数据</a>
    </nav>
}
@section query{
    <div class="text-c"> 

      时间范围：<input type="text" onfocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss'})" id="logmin" class="input-text Wdate" style="width:180px;">
      ~ <input type="text" onfocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss'})" id="logmax" class="input-text Wdate" style="width:180px;">
          卡号： <input type="text" id="quryCard" placeholder="卡号" style="width:150px" class="input-text">
          学号： <input type="text" id="quryID" placeholder="学号" style="width:150px" class="input-text">
        
        <button class="btn btn-success" type="button" onclick="OnReload()"><i class="Hui-iconfont"></i> 搜索</button>
    </div>
}

<table class="table table-border table-bordered table-hover table-bg table-sort">
    <thead>
        <tr class="text-c">           
            <th>卡号</th>
            <th>学号</th>
            <th>部门</th>
            <th>类型</th>
            <th>涉及空间</th>
            <th>开始时间</th>
            <th>结束时间</th>
            <th>是否违约</th>
        </tr>
    </thead>
    <tbody  class="text-c">

    </tbody>
</table>

@section script{
<script type="text/javascript">
    $(function () {
        $('.table-sort').dataTable({
            "bProcessing": true,
            "bScrollCollapse": true,
            "searching": false,
            "bAutoWidth": false,
            "serverSide": true,
            "ordering": false,
            "ajax": {
                "url": "@Url.Action("UserQueryForPage")",
                "type": "POST",
                "dataSrc": "Logs",
                "data": function (d) {
                    //添加额外的参数传给服务器
                    d.beginTime = $("#logmin").val();
                    d.endTime = $("#logmax").val();
                    d.memberCard = $("#quryCard").val();
                    d.userId = $("#quryID").val();
                }
            },
            "columns": [              
                { "data": "memberCard", "sClass": "text-c" },
                { "data": "userId", "sClass": "text-c" },
                { "data": "memberDeptName", "sClass": "text-c" },
                { "data": "spaceCategory", "sClass": "text-c" },
                { "data": "area", "sClass": "text-c" },
                { "data": "BeginTimeStr", "sClass": "text-c" },
                { "data": "EndTimeStr", "sClass": "text-c" },
                { "data": "renegeCategory", "sClass": "text-c" }
            ]

        });


        $('.table-sort tbody').on('click', 'tr', function () {
            if ($(this).hasClass('selected')) {
                $(this).removeClass('selected');
            }
            else {
                table.$('tr.selected').removeClass('selected');
                $(this).addClass('selected');
            }
        });

    });
         
    function UserExcel() {
        window.open("@Url.Action("UserExcel")?BeginTime=" + $("#logmin").val() + "&EndTime=" + $("#logmax").val() + "&MemberCard=" + $("#quryCard").val() + "&UserId=" + $("#quryID").val());
    }

        function OnReload() {
            $(".table-sort").dataTable().api().ajax.reload();
        }
        function edit(title, url, w, h) {
            layer_show(title, url, w, h);
        }

        /*添加*/
        function add(title, url, w, h) {
            layer_show(title, url, w, h);
        }

        /*删除*/
        function del(obj, id) {
            layer.confirm('确认要删除吗？', function (index) {
                $.ajax({
                    type: 'POST',
                    url: '@Url.Action("Delete")',
                data: { id: id },
                success: function (data) {
                    if (data == "success") {
                        $(obj).parents("tr").remove();
                        layer.msg('删除成功!', { icon: 1, time: 1000 });
                    } else {
                        layer.msg('删除失败!', { icon: 1, time: 1000 });
                    }
                }
            });
        });
    }




    /*批量删除*/
    function datadel() {
        var ids = "";
        $(".table input[type='checkbox'][name='check']:checked").each(function () {
            ids += ids.length > 0 ? ("," + $(this).val()) : ($(this).val())
        });
        if (ids.length > 0) {
            layer.confirm('确认要删除吗？', function (index) {
                $.ajax({
                    type: 'POST',
                    url: '@Url.Action("DeleteByIds")',
                    data: { id: ids },
                    success: function (data) {
                        if (data == "success") {
                            $(".table input[type='checkbox'][name='check']:checked").each(function () {
                                $(this).parents("tr").remove();
                            });
                            layer.msg('删除成功!', { icon: 1, time: 1000 });
                        } else {
                            layer.msg('删除失败!', { icon: 1, time: 1000 });
                        }
                    }
                });
            });
        }
    }
</script>
}

 

控制器代码如下：

        public string UserExcel()
        {
            LogSimple query = new LogSimple();

            TryUpdateModel<LogSimple>(query);

            int start = 0;
            int length = 10;

            logDao.Token = ApiTokenManager.Instance.GetToken(UserContext.CurrentUser.Id);
            LogPage page = logDao.UserQueryForPage(query, (start / length) + 1, length);

            if (page.Logs == null) page.Logs = new List<LogSimple>();


            IList<LogSimple> reneges = page.Logs;
            if (reneges == null || reneges.Count == 0)
            {
                return "false";
            }
            DataTable dt = ExcelUtil.ConvertToDataTable(reneges);

                

            string[] headers = { "卡号", "学号", "部门", "类型", "涉及空间", "开始时间", "结束时间", "是否违约" };
            string[] cellKes = { "MemberCard", "userId", "MemberDeptName", "spaceCategory", "area", "beginTime", "endTime", "renegeCategory" };

            ExcelUtil.ExportByWeb(dt, "用户日志信息", headers, cellKes, "用户日志信息.xls");
            return "Success";
        }

 

参考博客：http://blog.csdn.net/pan_junbiao/article/details/39717443