/// <summary>
/// DataTable导出到Excel
/// </summary>
/// <param name="dt">DataTable类型的数据源</param>
/// <param name="FileType">文件类型</param>
/// <param name="FileName">文件名</param>
public void CreateExcel(DataTable dt,
string FileType,
string FileName)
{
Response.Clear();
Response.Charset =
"UTF-8";
Response.Buffer =
true;
Response.ContentEncoding = System.Text.Encoding.GetEncoding(
"GB2312");
Response.AppendHeader("Content-Disposition",
"attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) +
".xls\"");
Response.ContentType =
FileType;
string colHeaders =
string.Empty;
string ls_item =
string.Empty;
DataRow[] myRow =
dt.Select();
int i =
0;
int cl =
dt.Columns.Count;
for (
int j =
0; j < dt.Columns.Count; j++
)
{
ls_item += dt.Columns[j].ColumnName +
"\t";
//栏位：自动跳到下一单元格
}
ls_item = ls_item.Substring(
0, ls_item.Length -
1) +
"\n";
foreach (DataRow row
in myRow)
{
for (i =
0; i < cl; i++
)
{
if (i == (cl -
1))
{
ls_item += row[i].ToString() +
"\n";
}
else
{
ls_item += row[i].ToString() +
"\t";
}
}
Response.Output.Write(ls_item);
ls_item =
string.Empty;
}
Response.Output.Flush();
Response.End();
}
调用方法:
string sql =
"SELECT hy_no '促销员编号',UserName '促销员姓名',in_date '日期',(SELECT jpname FROM BasJp WHERE fid=t1.Jp_id) '奖品名称',jf '积分',amount '数量',linktell '联系方式',linkaddr '联系地址',CASE WHEN status = 1 THEN '待确认' WHEN status = 4 THEN '已确认' ELSE '其他' END '状态' FROM hy_djjl t1 order BY in_date DESC";
DataSet ds =
DbHelperSQL.Query(sql);
if (ds !=
null && ds.Tables[
0].Rows.Count >
0)
{
DataTable dt = ds.Tables[
0];
CreateExcel(dt, "application/ms-excel",
"兑奖记录-" + DateTime.Now.ToString(
"yyyy-MM-dd HHmmss") +
".xls");
//调用函数
}
转载于:https://www.cnblogs.com/siyunianhua/p/9619976.html
相关资源：C#导出数据到Excel（百万级3秒）
