吐槽
最近新项目需要用到导出数据到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 = 10, /// <summary> /// execl单元格的货币格式 /// </summary> Number = 20 } }
>>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 <= 0) { return; } PropertyDescriptorCollection properties = FindProperties(); if (properties.Count <= 0) { return; } string content = string.Empty; for (int i = 0; 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 = 0; 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 != 0 && arrDesc[0].isExport) { ExeclFiledType eft = arrDesc[0].execlType; string execlTypeStr = (int)eft == 0 ? "s1" : eft.ToString(); string execlDataTypeStr = (int)eft == 0 ? "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 > 0) { 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 = 0; 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 != 0 && arrDesc[0].isExport) { sb.Append("<Column ss:Width='100'/>"); sheader.Append("<Cell ss:StyleID='header'>\n<Data ss:Type='String'>"); obj = arrDesc[0].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 > 0) { 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[0].GetCustomAttributes(typeof(ExportingField), false); return arrDesc == null || arrDesc.Length == 0 ? new ExportingField(false, string.Empty) : arrDesc[0]; } } }
导出效果如下:
如何要更改样式的话,可以通过调整文件样式,然后导出为xml格式,然后把程序里面的xml替换掉就OK了。具体自己可以试试~