记一个小东东.相信大家都做过不少这样的,把数据导入到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();
}
}
{
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();
// 把数据转化成相应的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 ;
}
{
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();
}
{
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里改,不用重新发布.