原文链接:https://blog.csdn.net/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('获取数据失败,请检查是否部署在本地服务器环境下');
}
});
});