泛型数据源:
/// <summary>
/// 导出EXCEL
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="page">当前页</param>
/// <param name="list">数据源</param>
/// <param name="ExcelTitle">EXCEL标题</param>
/// <param name="strFilter">过滤字段</param>
public void DataBindTitleExcel<T>(System.Web.UI.Page page, List<T> list, string ExcelTitle, string[] strFilter)
{
System.Web.HttpResponse response = HttpContext.Current.Response;
if (list.Count == 0)
{
response.Write("<script>alert('对不起,没有查询到任何记录,导出失败!')</script>");
response.End();
}
response.ContentEncoding = Encoding.UTF8;
response.ContentType = "application/ms-excel";
response.AppendHeader("Content-Disposition", "attachment;filename=" + ExcelTitle + ".xls");
int count = list.Count;
StringBuilder builder = new StringBuilder();
builder.Append("<html><head>\n");
builder.Append("<meta http-equiv=\"Content-Language\" content=\"zh-cn\">\n");
builder.Append("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">\n");
builder.Append("</head>\n");
builder.Append("<table border=1 >");
Type objType = typeof(T);
BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;//反射标识
PropertyInfo[] propInfoArr = objType.GetProperties(bf);
int counts = 0;
foreach (var itemName in list)
{
if (counts == 1)
break;
foreach (var item in itemName.GetType().GetProperties(bf))
{
if (strFilter.Length > 0)
{
if (strFilter.Where(P => P.Contains(item.Name)).ToArray().Length > 0)
continue;
}
builder.Append("<td style='background-color: rgb(227, 238, 251);'><b>" + GetResource(item.Name.ToString()) + "</b></td>\n");
//builder.Append("<td style='background-color: rgb(227, 238, 251);'><b>" + item.Name.ToString() + "</b></td>\n");
}
counts++;
}
int count2 = 1;
foreach (var model in list)
{
if (count2 % 2 == 0)
builder.Append("<tr style='background-color: rgb(227, 238, 251);' >");
else
builder.Append("<tr>");
foreach (var item in model.GetType().GetProperties(bf))
{
if (strFilter.Length > 0)
{
if (strFilter.Where(P => P.Contains(item.Name)).ToArray().Length > 0)
continue;
}
var a = item.Name;
if (item.PropertyType.Name == "DateTime")
{
//----------Update by yuanyi-----Start
//builder.Append("<td style='vnd.ms-excel.numberformat:@;'>" + Convert.ToDateTime(item.GetValue(model, null)).ToString("yyyy-MM-dd") + "</td>");
if (Convert.ToDateTime(item.GetValue(model, null)) == DateTime.MinValue)
builder.Append("<td style='vnd.ms-excel.numberformat:@;'></td>");
else
builder.Append("<td style='vnd.ms-excel.numberformat:@;'>" + Convert.ToDateTime(item.GetValue(model, null)).ToString("yyyy-MM-dd") + "</td>");
//----------Update by yuanyi --------end
}
else
builder.Append("<td style='vnd.ms-excel.numberformat:@;'>" + item.GetValue(model, null) + "</td>");
}
builder.Append("</tr>\n");
count2++;
}
builder.Append("</table>\n");
response.Write(builder.ToString());
response.End();
}
DataTable 数据源
public void ExportExcelDataTable(System.Data.DataTable dt, string filename, System.Web.UI.Page page)
{
HttpResponse resp;
resp = page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8));
resp.Charset = "GB2312";
//resp.ContentType = "application/ms-excel";
string colHeaders = "", ls_item = "";
//定义表对象与行对象,同时用DataSet对其值进行初始化
System.Data.DataRow[] myRow = dt.Select();
int i = 0;
int cl = dt.Columns.Count;
StringWriter sfw = new StringWriter();
//取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符
for (i = 0; i < cl; i++)
{
//if(i==(cl-1)) //最后一列,加\n
// colHeaders+=dt.Columns[i].Caption.ToString();
//else
colHeaders += dt.Columns[i].Caption.ToString() + ",";
}
sfw.WriteLine(colHeaders);
//向HTTP输出流中写入取得的数据信息
//逐行处理数据
foreach (DataRow row in myRow)
{
//当前数据写入
for (i = 0; i < cl; i++)
{
//if(i==(cl-1))
// ls_item+=row[i].ToString()+"\n";
//else
if (row[i].GetType().Name == "Boolean")
{
ls_item += row[i].ToString().ToUpper() == "TRUE" ? "1" : "0";
ls_item +=" ,";
}
else if (row[i].GetType().Name == "DateTime")
{
ls_item += DateTime.Parse(row[i].ToString()).ToString("yyyy-MM-dd");
ls_item += ",";
}
else if (row[i].GetType().Name == "Decimal")
{
ls_item += GetCSVstring(decimal.Parse(row[i].ToString()).ToString("F2"));
ls_item += ",";
}
else
{
if (row[i] != null)
{
ls_item += GetCSVstring(row[i].ToString().Trim());
}
ls_item = ls_item + ",";
}
}
sfw.WriteLine(ls_item);
ls_item = null;
}
resp.Write(sfw);
resp.End();
}