给Execl样式添加配置文件.

来源：互联网发布：淘宝那家店篮球鞋好编辑：程序博客网时间：2024/06/17 01:22

记一个小东东.相信大家都做过不少这样的,把数据导入到execl上去.这个很容易.但是不久后就是用户老是要修改execl上的样式.比如给某个栏位加上颜色.

虽然说很容易.但是每修改次都要重新发布.不是很爽.下面说一个比较另类的方法.效果相当于给Execl的样式加上配置文件.

第一步.把数据集给导出成HTML格式.如下.主要是方法EnumToHtml,别的大家不用看.

public static class ExportHelper<T>
    {
        public static readonly List<PropertyInfo> ps = null;
        static ExportHelper()
        {
            if (ps == null)
            {
                ps = typeof(T).GetProperties().ToList();
            }
        }
        public static string EnumToHtml(IEnumerable<T> data, string subject)
        {
            return EnumToHtml(data, subject, null);
        }
        public static string EnumToHtml(IEnumerable<T> data, string subject,string linkcss)
        {
            if (data == null)
                return string.Empty;
            StringBuilder sb = new StringBuilder();
            if (!string.IsNullOrEmpty(linkcss))
            {
                sb.Append("<head>");
                sb.Append("<link rel='stylesheet' type='text/css' href=");
                sb.Append(linkcss).Append(" />");
                sb.Append("</head>");
                sb.Append("<body>");
                sb.Append("<table>");
                sb.Append("<caption>").Append(subject).Append("</caption>");
                sb.Append("<tr class='column'>");
            }
            else
            {
                sb.Append("<body>");
                sb.Append("<table style='border-color: #000000; width:80%; table-layout: auto; border-collapse: collapse;' ");
                sb.Append("border='1' cellpadding='2' cellspacing='2'>");
                sb.Append("<caption style='color: #000000; font-weight: bold; font-size: large' >");               
                sb.Append(subject);
                sb.Append("</caption>");
                sb.Append("<tr>");
            }
            foreach (PropertyInfo info in ps)
            {
                sb.Append("<th id=").Append(info.Name).Append(">");
                sb.Append(info.Name);
                sb.Append("</th>");
            }
            sb.Append("</tr>");
            foreach (T t in data)
            {
                sb.Append("<tr>");
                foreach (PropertyInfo info in ps)
                {
                    sb.Append("<td>");
                    sb.Append(info.GetValue(t, null));
                    sb.Append("</td>");
                }
                sb.Append("</tr>");
            }
            sb.Append("</table>");
            sb.Append("</body>");
            return sb.ToString();
        }
        public static void EnumToExel(Worksheet wsheet, IEnumerable<T> data, string toptext)
        {
            EnumToExel(wsheet, data, toptext, 1, 1);
        }
        public static void EnumToExel(Worksheet wsheet, IEnumerable<T> data, string toptext, int top, int left)
        {
            if (data == null)
                return;
            int nTop = top;
            int nLeft = left;
            foreach (PropertyInfo info in ps)
            {
                wsheet.Cells[nTop, nLeft] = info.Name;
                nLeft++;
            }
            foreach (T t in data)
            {
                nTop++;
                nLeft = left;
                foreach (PropertyInfo info in ps)
                {
                    wsheet.Cells[nTop, nLeft] = info.GetValue(t, null);
                    nLeft++;
                }
            }
            wsheet.get_Range(wsheet.Cells[top, left], wsheet.Cells[nTop, nLeft - 1]).Borders.LineStyle = 1;
        }
        public static void Write(T s)
        {
            foreach (PropertyInfo minfo in ps)
            {
                if (minfo.GetValue(s, null) != null)
                {
                    string value = minfo.GetValue(s, null).ToString();
                    Console.ForegroundColor = ConsoleColor.Yellow;
                    Console.Write(minfo.Name);
                    Console.ForegroundColor = ConsoleColor.White;
                    Console.Write(":");
                    Console.ForegroundColor = ConsoleColor.Green;
                    Console.Write(value);
                    Console.Write(" ");
                }
            }
            Console.WriteLine();
        }
    }

然后.用如下方法导出数据到execl.

 var bomresult = db.A51_BomSendMail(bom.ModelNo).ToList<A51_BomSendMailResult>();
                      //把数据转化成相应的HTML格式的数据表
 body = ExportHelper<A51_BomSendMailResult>.EnumToHtml(bomresult, bom.ModelNo, @"D:\style.css");
    //得到上面数据的字节转换
 byte[] buffer = Encoding.ASCII.GetBytes(body);
     //生成对应的字节流
 Stream stream = new MemoryStream(buffer);
 FileStream filestream = new FileStream(FilePath, FileMode.Create, FileAccess.ReadWrite);
 filestream.Write(buffer, 0, buffer.Length);
 filestream.Close();  

上面的我一部分实现.大家可按自己需求来改.

到这里大家也知道了.其实就是利用CSS来做配置文件.希望大家不要鄙视.^_^

比如开始生成的文件如下.

后面用户要把栏位名加Replaced_by变成红色.整个页面要带点墨色.要好看.

用户就是上帝.没办法.用上面的代码.在如下位置D:\style.css写上CSS;

table
{
    margin: 1em auto;
    font: 95%/130% Tahoma, Arial, Helvetica, sans-serif;
    border-spacing: 0;
    border-color: #000000;
    width:80%; 
    table-layout: auto; 
    border-collapse: collapse;
}
table caption {
    color: #2442b1;
    font-size:200%;
    letter-spacing: .1em;
    padding: 3.5em 0.2em 2em 0;
    text-align:left;    
    font-weight:900;
}
table td
{
    color: #203276;
    border-top: 1px solid #fff;
    border-right: 1px solid #c6cdd8;
    border-bottom: 1px solid #c6cdd8;
    border-left: 1px solid #fff;
    padding: 1.5em 0.5em 1.5em 0.8em;
    text-align:left;
}
tr.column th
{
    background-color:#BBAABB;    
}
#Replaced_by
{
    background-color:#FF3399;
}

然后效果图如下:

总的来就这么多.但是有的同志喜欢把直接用DataTable.下面也做了一个差不多的.代码简单.给个思路.自己去改.

        public static string EnumToHtml(System.Data.DataTable data, string subject, string linkcss)
        {
            if (data == null)
                return string.Empty;
            StringBuilder sb = new StringBuilder();
            int columnCount = 0;
            if (!string.IsNullOrEmpty(linkcss))
            {
                sb.Append("<head>");
                sb.Append("<link rel='stylesheet' type='text/css' href=");
                sb.Append(linkcss).Append(" />");
                sb.Append("</head>");
                sb.Append("<body>");
                sb.Append("<table>");
                sb.Append("<caption>").Append(subject).Append("</caption>");
                sb.Append("<tr class='column'>");
            }
            else
            {
                sb.Append("<body>");
                sb.Append("<table style='border-color: #000000; width:80%; table-layout: auto; border-collapse: collapse;' ");
                sb.Append("border='1' cellpadding='2' cellspacing='2'>");
                sb.Append("<caption style='color: #000000; font-weight: bold; font-size: large' >");
                sb.Append(subject);
                sb.Append("</caption>");
                sb.Append("<tr>");
            }
            foreach (DataColumn info in data.Columns)
            {
                sb.Append("<th id=").Append(info.ColumnName).Append(">");
                sb.Append(info.ColumnName);
                sb.Append("</th>");
                columnCount++;
            }
            sb.Append("</tr>");
            foreach (DataRow row in data.Rows)
            {
                sb.Append("<tr>");
                for (int i = 0; i < columnCount; i++)
                {
                    sb.Append("<td>");
                    sb.Append(row[i]);
                    sb.Append("</td>");
                }
                sb.Append("</tr>");
            }
            sb.Append("</table>");
            return sb.ToString();
        }

差不多就这么样了.先放着看看.以后他们要改什么样式,直接去CSS里改,不用重新发布.

0 0