.Net生成导出Excel

概述

在做.Net web开发的过程中经常需要将查出的数据导成Excel表返给用户，方便用户对数据的处理和汇总。这里我将导出Excel表格的代码做一个总结,这也是我项目中经常用到的，代码简单易懂，使用方便，分享给大家。

1、查询数据，数据需要是datatable

后端代码实例，先给Excel表头转化为中文描述

public OperationResult CreateExportExcel(Model queryModel)
        {
            string errorMsg;
            DataSet ds = GetService.RunPRC_ConfirmCompSKD(queryModel, out errorMsg);
            if (!string.IsNullOrEmpty(errorMsg))
            {
                return new OperationResult("99", errorMsg);
            }
            if (ds.Tables.Count != 2)
            {
                return new OperationResult("99", "查询为空");
            }
            DataTable dt = ds.Tables[0];
            dt.Columns.Remove(dt.Columns["DeptID"]);
            dt.Columns.Remove(dt.Columns["SearchKey"]);//删除不需要的列
            dt.Columns["FYear"].ColumnName = "年份";
            dt.Columns["FMonth"].ColumnName = "月份";
            dt.Columns["FranchiseeNo"].ColumnName = "编号";
            dt.Columns["FranchiseeName"].ColumnName = "名称";
            dt.Columns["DeptName"].ColumnName = "维护部门";
            dt.Columns["FContractNo"].ColumnName = "合同编号";
            dt.Columns["FDate1"].ColumnName = "起租日期";
            dt.Columns["FDate2"].ColumnName = "止租日期";
            dt.Columns["FNumber"].ColumnName = "收款单编号";
            dt.Columns["FAmount"].ColumnName = "收款金额";
            dt.Columns["FDate"].ColumnName = "收款日期";
            dt.Columns["SkdNo"].ColumnName = "已核销收款单编号";
            dt.Columns["SkdAmount"].ColumnName = "已核销收款金额";
            dt.Columns["SkdDate"].ColumnName = "已核销收款日期";
            return Export(dt, "收款单-");
        }
　　　　　/// <summary>
        /// 生成导出临时文件
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="preName"></param>
        /// <returns></returns>
        private OperationResult Export(DataTable dt, string preName)
        {

            dt.TableName = preName + DateTime.Now.ToString("yyyyMMddHHmmss");

            string baseDictory = AppDomain.CurrentDomain.BaseDirectory;

            string dirctory = string.Format("{0}Doc", baseDictory);

            if (!Directory.Exists(dirctory))
            {
                Directory.CreateDirectory(dirctory);
            }

            string path = string.Format("{1}Doc/{0}.xls", dt.TableName, baseDictory);

            string serverPath = path;

            if (File.Exists(serverPath))
            {
                File.Delete(serverPath);
            }

            ExcelUtil.SaveAsExcel(serverPath, preName, dt);

            bool flag = File.Exists(serverPath);

            if (!flag)
            {
                return new OperationResult("01", "生成失败");
            }

            var result = new OperationResult("00", "success")
            {
                returnURL = string.Format("/Doc/{0}", Path.GetFileName(serverPath))
            };

            return result;
        }

public class OperationResult
    {
        public OperationResult()
        {
        }

        public OperationResult(string errCode, string errMsg)
        {
            ErrorCode = errCode;
            ErrorMsg = errMsg;
        }

        /// <summary>
        ///     返回状态
        /// </summary>
        public bool Flag { get; set; }

        /// <summary>
        ///     返回码
        /// </summary>
        public string ErrorCode { get; set; }

        /// <summary>
        ///     返回的URL
        /// </summary>
        public string returnURL { get; set; }

        /// <summary>
        ///     返回消息
        /// </summary>
        public string ErrorMsg { get; set; }
        public object ObjectData { get; set; }
        public object rows { get; set; }

        public int total { get; set; }
        /// <summary>
        /// 页脚
        /// </summary>
        public object footer { get; set; }

    }

前端代码实例：

 function  outExcel() {
            var queryUrl = "/Statistics/D_IComeData/CreateD_MonthIncomeExportExcel";
            var queryModel = form2Json("searchform");
            $.ajax({
                type: "POST",
                url: queryUrl,
                data: queryModel,
                dataType: "JSON",
                success: function (result) {
                    var row = result.rows;
                    if (Util.isValidResult(row)) {
                        if (row.hasOwnProperty("returnURL") && row.returnURL != null) {
                            OpenFrameUrl(row.returnURL);
                        }
                    }
                }
            });
        }

function OpenFrameUrl(url) {
        var form = document.createElement("form");
        form.id = "outExcelResult";
        form.action = url;
        form.method = "get";
        document.body.appendChild(form);
        form.submit();
        document.body.removeChild(form);

    };