正文
将查询到的数据导出到Excel终结版
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
吐槽
最近新项目需要用到导出数据到Excel，试了试之前写的一篇博文，但是感觉那个不太好，主要原因是没能实现样式控制，今天我们就来介绍一种新的导出Excel方法，而且这种方法很轻量级，它利用xml生成，然后加不同后缀进行导出不同格式的文件。
正文
1.前台实现（这里直接使用submit将参数post到后台即可。）
function download() {
$('form').submit();
}
2.控制器实现
return new XmlExcelResult<MemberMessageListDto>(list, "会籍公海");
3.帮助类实现
>>ExportingField.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace Cloud.Arch.Utility.Excel
{
[AttributeUsage(AttributeTargets.Property
, AllowMultiple = false, Inherited = true)]
public class ExportingField : System.Attribute
{
public bool isExport;
public string exportTitle;
/// <summary>
/// execl格式串
/// </summary>
public ExeclFiledType execlType;
public ExportingField() { }
public ExportingField(bool isexport, string exporttitle)
{
isExport = isexport;
exportTitle = exporttitle;
}
public ExportingField(bool isexport, string exporttitle, ExeclFiledType execltype)
{
isExport = isexport;
exportTitle = exporttitle;
execlType = execltype;
}
}
public enum ExeclFiledType
{
/// <summary>
/// execl单元格的文本
/// </summary>
String = ,
/// <summary>
/// execl单元格的货币格式
/// </summary>
Number =
}
}
>>XMLExcelResult.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Web.Mvc; namespace Cloud.Arch.Utility.Excel
{
/// <summary>
/// 提供将泛型集合数据导出Excel文档。 要转换的类型的属性必须用ExportingField声明 才能识别
/// </summary>
/// <typeparam name="T"></typeparam>
public class XmlExcelResult<T> : ActionResult where T : new()
{
public XmlExcelResult(IList<T> entity, string fileName)
{
this.Entity = entity;
DateTime time = DateTime.Now;
this.FileName = string.Format("{0}{1}", fileName,
time.ToString("yyMMddhhmmss"));
} public XmlExcelResult(IList<T> entity)
{
this.Entity = entity;
DateTime time = DateTime.Now;
this.FileName = string.Format("{0}_{1}_{2}_{3}",
time.Month, time.Day, time.Hour, time.Minute);
} public IList<T> Entity
{
get;
set;
}
public string FileName
{
get;
set;
} public override void ExecuteResult(ControllerContext context)
{
if (Entity == null)
{
new EmptyResult().ExecuteResult(context);
return;
}
SetResponse(context);
} /// <summary>
/// 设置并向客户端发送请求响应。
/// </summary>
/// <param name="context"></param>
private void SetResponse(ControllerContext context)
{
StringBuilder sBuilder = ConvertEntity();
byte[] bytestr = Encoding.UTF8.GetBytes(sBuilder.ToString());
context.HttpContext.Response.Clear();
context.HttpContext.Response.ClearContent();
context.HttpContext.Response.Buffer = true;
context.HttpContext.Response.Charset = "GB2312";
context.HttpContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
context.HttpContext.Response.ContentType = "text/xml";
context.HttpContext.Response.AddHeader("Content-Disposition", "attachment; filename=" + FileName + ".xls");
context.HttpContext.Response.AddHeader("Content-Length", bytestr.Length.ToString());
context.HttpContext.Response.Write(sBuilder);
context.HttpContext.Response.Flush();
context.HttpContext.Response.End();
} /// <summary>
/// 把泛型集合转换成组合Excel表格的字符串。
/// </summary>
/// <returns></returns>
private StringBuilder ConvertEntity()
{
StringBuilder sb = new StringBuilder();
sb.Append(@"<?xml version='1.0'?>
<?mso-application progid='Excel.Sheet'?>
<Workbook
xmlns='urn:schemas-microsoft-com:office:spreadsheet'
xmlns:o='urn:schemas-microsoft-com:office:office'
xmlns:x='urn:schemas-microsoft-com:office:excel'
xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'
xmlns:html='http://www.w3.org/TR/REC-html40'>
<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>
<Author>Darl McBride</Author>
<LastAuthor>Bill Gates</LastAuthor>
<Created>2007-03-15T23:04:04Z</Created>
<Company>SCO Group, Inc.</Company>
<Version>11.8036</Version>
</DocumentProperties>
<ExcelWorkbook xmlns='urn:schemas-microsoft-com:office:excel'>
<WindowHeight>6795</WindowHeight>
<WindowWidth>8460</WindowWidth>
<WindowTopX>120</WindowTopX>
<WindowTopY>15</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID='Default' ss:Name='Normal'>
<Alignment ss:Vertical='Bottom' />
<Borders />
<Font />
<Interior />
<NumberFormat />
<Protection />
</Style>
<Style ss:ID='s1' ss:Name='s1'>
<Borders>
<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>
</Borders>
</Style>
<Style ss:ID='header' ss:Name='Header'>
<Font ss:FontName='宋体' x:CharSet='134' ss:Size='11' ss:Color='#FFFFFF'/>
<Alignment ss:Horizontal='Center' ss:Vertical='Center'/>
<Interior ss:Color='#0070C0' ss:Pattern='Solid'/>
<Borders>
<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>
</Borders>
</Style>
<Style ss:ID='Number'>
<Borders>
<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>
</Borders>
<NumberFormat ss:Format='"¥"#,##0;"¥"\-#,##0'/>
</Style> </Styles>
<Worksheet ss:Name='Sheet1'>
<Table x:FullColumns='1' x:FullRows='1'>
");
AddTableHead(sb);
AddTableBody(sb);
sb.Append(@"</Table> <WorksheetOptions xmlns='urn:schemas-microsoft-com:office:excel'>
<Print>
<ValidPrinterInfo />
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected />
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>5</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions></Worksheet></Workbook>");
return sb;
} /// <summary>
/// 根据IList泛型集合中 用ExportingField特性标示的属性值来组合Excel表格。
/// </summary>
/// <param name="sb"></param>
private void AddTableBody(StringBuilder sb)
{ if (Entity == null || Entity.Count <= )
{
return;
}
PropertyDescriptorCollection properties = FindProperties(); if (properties.Count <= )
{
return;
}
string content = string.Empty;
for (int i = ; i < Entity.Count; i++)
{
Type t = Entity[i].GetType();
PropertyInfo[] fields = t.GetProperties();
sb.Append("<Row ss:AutoFitHeight='0' ss:Height='20'>\n");
for (int j = ; j < fields.Length; j++)
{
string sign = string.Empty;
ExportingField[] arrDesc = (ExportingField[])fields[j].GetCustomAttributes(typeof(ExportingField), false);
object obj = null;
if (arrDesc != null && arrDesc.Length != && arrDesc[].isExport)
{
ExeclFiledType eft = arrDesc[].execlType;
string execlTypeStr = (int)eft == ? "s1" : eft.ToString();
string execlDataTypeStr = (int)eft == ? "String" : eft.ToString();
sb.Append("<Cell ss:StyleID='" + execlTypeStr + "'>\n<Data ss:Type='" + execlDataTypeStr + "'>"); obj = fields[j].GetValue(Entity[i], null);
content = obj == null ? string.Empty : obj.ToString();
//var arr = content.Split("<br/>".ToCharArray());
var arr = Regex.Split(content, "<br/>", RegexOptions.IgnoreCase);
if (arr != null && arr.Length > )
{
foreach (var s in arr)
{
if (!string.IsNullOrWhiteSpace(s))
{
sb.Append("<![CDATA[");
sb.Append(s);
sb.Append("]]>");
sb.Append("<br/>");
}
}
}
sb.Append("</Data>\n</Cell>");
} }
sb.Append("</Row>\n");
}
} /// <summary>
/// 根据指定类型T的所有根用ExportingField特性标示的属性值来组合Excel表头。
/// </summary>
/// <param name="sb"></param>
private void AddTableHead(StringBuilder sb)
{
Type t = typeof(T);
PropertyInfo[] fields = t.GetProperties();
StringBuilder sheader = new StringBuilder();//存储标题行信息
sheader.Append("<Row ss:AutoFitHeight='0' ss:Height='20'>\n");
string content = string.Empty;
for (int j = ; j < fields.Length; j++)
{
string sign = string.Empty;
ExportingField[] arrDesc = (ExportingField[])fields[j].GetCustomAttributes(typeof(ExportingField), false);
object obj = null;
if (arrDesc != null && arrDesc.Length != && arrDesc[].isExport)
{
sb.Append("<Column ss:Width='100'/>");
sheader.Append("<Cell ss:StyleID='header'>\n<Data ss:Type='String'>");
obj = arrDesc[].exportTitle;
content = obj == null ? string.Empty : obj.ToString();
// var arr = content.Split("<br/>".ToCharArray());
var arr = Regex.Split(content, "<br/>", RegexOptions.IgnoreCase);
if (arr != null && arr.Length > )
{
foreach (var s in arr)
{
if (!string.IsNullOrWhiteSpace(s))
{
sheader.Append("<![CDATA[");
sheader.Append(s);
sheader.Append("]]>");
sheader.Append("<br/>");
}
}
}
sheader.Append("</Data>\n</Cell>\n");
} }
sheader.Append("</Row>");
sb.Append(sheader.ToString());
} /// <summary>
/// 返回指定类型T的属性集合。
/// </summary>
/// <returns></returns>
private static PropertyDescriptorCollection FindProperties()
{
return TypeDescriptor.GetProperties(typeof(T));
}
/// <summary>
/// 获取枚举类型的描述信息
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static ExportingField GetDescription(object obj)
{
string objName = obj.ToString();
Type t = obj.GetType();
FieldInfo[] fields = t.GetFields(); ExportingField[] arrDesc = (ExportingField[])fields[].GetCustomAttributes(typeof(ExportingField), false); return arrDesc == null || arrDesc.Length == ? new ExportingField(false, string.Empty) : arrDesc[];
}
}
}
导出效果如下:
如何要更改样式的话，可以通过调整文件样式，然后导出为xml格式，然后把程序里面的xml替换掉就OK了。具体自己可以试试~
