asp.net导出数据到Excel的两种方法

原文链接：https://blog.youkuaiyun.com/weixin_42353499/article/details/80522734

public void CreateExcel(DataTable dt,string FileName)//HttpResponse Page.Response
    {
        string FileType = "application/ms-excel";
        Response.Clear();
        Response.Charset = "UTF-8";
        Response.Buffer = true;
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + FileName + ".xls\"");
        Response.ContentType = FileType;
        string colHeaders = string.Empty;
        colHeaders = '标题'
        Response.Output.Write(colHeaders);
        colHeaders = string.Empty;
        string ls_item = string.Empty;
        DataRow[] myRow = dt.Select();
        int cl = dt.Columns.Count;
        foreach (DataRow row in myRow)
        {
            int count = 0;
            for (int i = 0; i < cl; i++)
            {
                if (i == (cl - 1))
                {
                    ls_item += row[i].ToString() + "\n";
                }
                else
                {
                    if(count < 2)
                    {
                        ls_item = ls_item + "" + row[i].ToString() + "\t";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }
                }
                count++;
            }
            Response.Output.Write(ls_item);
            ls_item = string.Empty;
        }
        Response.Output.Flush();
        Response.End();
    }

第二种用HttpContext.Current.Response。这种能够解决导出Excel科学记数法的问题。这个方法的原文链接https://www.cnblogs.com/JsonShare/p/4872173.html

public void CreateExcel_t(DataTable dt, string FileName) {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Charset = "UTF-8";
        HttpContext.Current.Response.ContentType = "application/vnd.ms-xls";
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + FileName + ".xls");
        StringBuilder table = new StringBuilder();
        table.Append("<table><tr>");
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            table.Append("<td>");
            table.Append(dt.Columns[i].Caption.ToString());//表格的标题
            table.Append("</td>");
        }
        table.Append("</tr>");
        for (int i = 0 ; i < dt.Rows.Count; i++)
        {
            table.Append("<tr>");
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                table.Append("<td style='vnd.ms-excel.numberformat:@'>");
                table.Append(dt.Rows[i][j].ToString());
                table.Append("</td>");
            }
            table.Append("</tr>");
        }
        table.Append("</table>");
        HttpContext.Current.Response.Write(table);
        HttpContext.Current.Response.End();
    }

Layui.excel导出数据到excel

除了以上两种转载的方法外，Layui表格自带的导出文件只能导出当前页中的数据到excel中，但是我自己试了试导出当前页的数据到excel也提示“无法保存excel”，不知道怎么回事然后发现layui有一个第三方插件可以就是Layui.excel，大家可以去看看教程，挺简单的，同时还可以设置导出的excel中数据的格式花样什么的（花里胡哨），普通i5,8g运存最多可以导出50w数据，耗时45秒。对于一般使用来说完全够了，如果是特别大的数据可能就要另外向办法了。
代码示例

layui.use(['jquery', 'excel', 'layer'], function () {
                        //var loading;
                        //loading = layer.load(1, { shade: [0.3, '#fff'] });
                        var excel = layui.excel;
                        //日期格式化方法
                        Date.prototype.Format = function (fmt) {
                            var o = {
                                "M+": this.getMonth() + 1, //月份 
                                "d+": this.getDate(), //日 
                                "H+": this.getHours(), //小时 
                                "m+": this.getMinutes(), //分 
                                "s+": this.getSeconds(), //秒 
                                "q+": Math.floor((this.getMonth() + 3) / 3), //季度 
                                "S": this.getMilliseconds() //毫秒 
                            };
                            if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
                            for (var k in o)
                                if (new RegExp("(" + k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
                            return fmt;
                        }
                        var time = new Date().Format("yyyy-MM-dd");
                        $.ajax({
                            url: 'Handler5.ashx'
                            , type: 'POST'
                            , dataType: 'json'                                   
                            , data: {id: 1,}
                            , success(res) {
                                var data = res.data;
                                console.log(res)
                                // 重点！！！如果后端给的数据顺序和映射关系不对，请执行梳理函数后导出
                                data = excel.filterExportData(data, [
                                    'ID'
                                    , 'Query'
                                    , 'Sub_query'
                                    , 'Created_Alias'
                                    , 'Team'
                                    , 'Case_Priority'
                                    , 'Created_Date'
                                    , 'Issue_Summay'
                                    , 'Warning_Time'
                                    , 'Open_Date'
                                    , 'Status'
                                    , 'Dependency_Department'
                                    , 'Escalation'
                                    , 'Closed_Date'
                                    , 'Resolution_Summary'
                                    , 'SIT_PIC'
                                ]);
                                // 重点2！！！一般都需要加一个表头，表头的键名顺序需要与最终导出的数据一致
                                data.unshift({ ID: "ID", Query: "Query", Sub_query: 'Sub query', Created_Alias: 'Created Alias', Team: 'Team', Case_Priority: 'Case Priority', Created_Date: 'Created Date', Issue_Summay: 'Issue Summay', Warning_Time: 'Warning Time', Open_Date: 'Open Date', Status: 'Status', Dependency_Department: 'Dependency Department', Escalation: 'Escalation', Closed_Date: 'Closed Date', Resolution_Summary: 'Resolution Summary', SIT_PIC: 'SIT PIC'});
                                excel.exportExcel(data, time+'提交数据（所有）.xlsx', 'xlsx');
                            }
                            , error() {
                                layer.alert('获取数据失败，请检查是否部署在本地服务器环境下');
                            }
                        });
                    });