asp.net(jqueryeasy-uidatagrid)通用Excel文件导出（NPOI）

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

NPOI导出Excel文件的文章很多了，但是没看到easy-ui datagrid表格直接导出Excel文件的，要显示中文Title和Excel文件每列列宽和页面上看到的基本相符。这里可能是个实现，不是最佳，写出来给有用的人参考。可能在通用二字上做得还不是特别好，有好的方法请留言指正。

效果：

编码过程：

1、js

//-----------------------------------------------------
// @description 返回grid的(可见的)所有行给后端导出Excel用
// @param {string} table    表格ID
// @returns rows
// @author 肖峰
//------------------------------------------------------
function getGridDataToExcelExport(table) {
    var allRows = $("#" + table).datagrid("getRows");
    return allRows;
}

//-----------------------------------------------------
// @description 返回grid的所有列
// @param {string} table    表格ID
// @returns rows
// @author 肖峰
function getGridColumnFields(table) {
    var allCols = $("#" + table).datagrid("getColumnFields");
    return allCols;
}

//-----------------------------------------------------
// @description 返回grid的所有列的选项title、列宽等
// @param {string} table    表格ID
// @returns rows
// @author 肖峰
function getGridColumnFieldsOptions(table) {
    var allColsTitle = $("#" + table).datagrid("options").columns;
    return allColsTitle;
}



        //导出结果集到Excel
        function exportGrid() {
            if (getGridDataToExcelExport("ResultGrid").length == 0) {
                alertMsg("系统提示", "记录数为0，不需要导出！", "error");
                return;
            }
            var entity = {};
            entity.GridRows = getGridDataToExcelExport("ResultGrid");
            entity.GridColumnOptions = getGridColumnFieldsOptions("ResultGrid");
            entity.ExportFileName = '产品箱盒对应查询结果.xls';
            POVOS.BarCode.Website.BoxCorrespondingQuery.ExportGridToExcelFileAndDownload(entity, function (p) {
                location.href = "ExportExcelFile.ashx?ExcelFileID=" + p.value;
            });
        }

//导出结果集到Excel
        function exportGrid() {
            if (getGridDataToExcelExport("ResultGrid").length == 0) {
                alertMsg("系统提示", "记录数为0，不需要导出！", "error");
                return;
            }
            var entity = {};
            entity.GridRows = getGridDataToExcelExport("ResultGrid");          
            entity.GridColumnOptions = getGridColumnFieldsOptions("ResultGrid");
            entity.ExportFileName = '产品物流流向查询结果.xls';           
            POVOS.BarCode.Website.ProductTransQuery.ExportGridToExcelFileAndDownload(entity, function (p) {               
                location.href = "ExportExcelFile.ashx?ExcelFileID="+p.value;
            });
        }

2、后台代码，前台使用ajaxpro2调用后台方法，传递前端页面grid行列记录的Json格式。

/// <summary>
        /// 形成Excel对应的文件流存在数据库里，返回给前端File GUID
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        [AjaxMethod]
        public string ExportGridToExcelFileAndDownload(JavaScriptObject entity)
        {
            var gridRows = (JavaScriptArray)entity["GridRows"];
            var gridOptions = (JavaScriptArray)entity["GridColumnOptions"];
            var gridOptionList = (JavaScriptArray)gridOptions[0];
            //可见的列
            List<JavaScriptObject> gridCols = new List<JavaScriptObject>();
            foreach (JavaScriptObject j in gridOptionList)
            {
                if (j.Value.IndexOf("hidden") == -1)
                {
                    gridCols.Add(j);
                }
            }
            var fileName = entity["ExportFileName"].ToString();           
            string tempFileName = AppDomain.CurrentDomain.BaseDirectory + "CommonExcelFile.xls";
            FileStream fs = new FileStream(tempFileName, FileMode.Open, FileAccess.Read);
            var workBook = new HSSFWorkbook(fs);
            workBook.SetSheetName(0,"查询结果");
            var sheet = workBook.GetSheetAt(0); 
            //表头(列)，第一行
            int newColIndex = 0;
            var titleRow = sheet.CreateRow(newColIndex);
            int cIndex = 0;
            foreach (JavaScriptObject j in gridCols)
            {
                titleRow.CreateCell(cIndex).SetCellValue(j["title"].ToString());
                int width = int.Parse(j["width"].ToString()) / 6;
                if (width > 255)
                    width = 250;
                sheet.SetColumnWidth(cIndex, width*256);
                cIndex++;
            }
            //行记录
            for (int rowIndex = 0; rowIndex < gridRows.Count; rowIndex++)
            {
                newColIndex++;
                var row = sheet.CreateRow(newColIndex);
                var jsonEntity = gridRows[rowIndex] as JavaScriptObject;
                for (int colIndex = 0; colIndex < gridCols.Count; colIndex++)
                {
                    string cellValue = string.Empty;
                    JavaScriptObject colOption = (JavaScriptObject)gridCols[colIndex];
                    string field = colOption["field"].ToString();
                    if (jsonEntity[field] != null)
                        cellValue = jsonEntity[field].ToString();
                    row.CreateCell(colIndex).SetCellValue(cellValue);
                }
            }
            MemoryStream newFile = new MemoryStream();
            sheet.Workbook.Write(newFile);
            using (POVOSEntities db = new POVOSEntities())
            {
                var resultFile = new ExportFileEntity();
                resultFile.FileGuid = Guid.NewGuid();
                resultFile.FileName = fileName;
                resultFile.FileCreateDateTime = DateTime.Now;
                resultFile.FileStreamByte = newFile.GetBuffer();
                db.AddToExportFileEntity(resultFile);
                db.SaveChanges();
                return resultFile.FileGuid.ToString();
            }            
        }

3、文件下载使用一般处理程序，接收QueryString里传的文件ID，返回给前端Excel文件。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.SessionState;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using BarCode.DataAccess;

namespace POVOS.BarCode.Website
{
    /// <summary>
    /// 读取数据库Excel文件并下载
    /// </summary>
    public class ExportExcelFile : IHttpHandler, IRequiresSessionState
    {
        public void ProcessRequest(HttpContext context)
        {
            using (POVOSEntities db = new POVOSEntities())
            {
                Guid fileID = new Guid(context.Request["ExcelFileID"] as string);
                var fileEntity = db.ExportFileEntity.Where(e => e.FileGuid == fileID).FirstOrDefault();
                string filename = string.Format("[{0}]{1}", DateTime.Now.ToString("yyMMddHHmmss"), fileEntity.FileName);
                context.Response.ContentType = "application/vnd.ms-excel";
                context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", context.Server.UrlEncode(filename)));
                context.Response.Clear();
                context.Response.BinaryWrite(fileEntity.FileStreamByte);
                context.Response.End();
            }
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}