将查询到的数据导出到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 = 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='&quot;¥&quot;#,##0;&quot;¥&quot;\-#,##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了。具体自己可以试试~