幸运星空

Lucker的程序人生

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

(本文转自:http://www.cnblogs.com/ruinet/archive/2009/10/17/1585320.html)

      在应用中经常会遇到要从系统或数据库中导出数据平面文件,一般是导出到txt,csv或excel。txt和csv一般用在系统间的数据交换,而excel一般有较好的显示效果,可以按照一定的模板导出,导出就不用再排版了,使用简单,如果是使用做报表一般都导出excel文件。

但是使用com组件导出到Excel数据很慢,有另一种生成excel文件的方式就是通过html和css快速导出数据同时并能设置样式,使用这种方式有两个优点:1是速度快,2是不需安装excel支持。

实现就是通过html可以直接转换成excel,有两个要点:一是显示出表格线,像ASP.net中直接通过Gridview导出excel都没有显示出表格,二是设置数据格式。

一、显示出表格线:
在html的head标记中加入以下代码:

<xml>
   
<x:ExcelWorkbook>
     
<x:ExcelWorksheets>
       
<x:ExcelWorksheet>                                                       
        
<x:Name>工作表标题</x:Name>

                 
<x:WorksheetOptions>
                   
<x:Print>
                     
<x:ValidPrinterInfo />
                   
</x:Print>
                 
</x:WorksheetOptions>
       
</x:ExcelWorksheet>
      
</x:ExcelWorksheets>
  
</x:ExcelWorkbook>
</xml>

二、设置数据格式:
在head中加入css定义

<style type="text/css">
.spercent
{

 background-color
:#ffff99;
 mso-number-format
:0.00%;
}
</style>

在css中加入:mso-number-format定义数据格式,格式可以在excel中查看自定义格式,具体可以参考一下:
mso-number-format:"0" NO Decimals
mso-number-format:"0\.000" 3 Decimals
mso-number-format:"\#\,\#\#0\.000" Comma with 3 dec
mso-number-format:"mm\/dd\/yy" Date7
mso-number-format:"mmmm\ d\,\ yyyy" Date9
mso-number-format:"m\/d\/yy\ h\:mm\ AM\/PM" D -T AMPM
mso-number-format:"Short Date" 01/03/1998
mso-number-format:"Medium Date" 01-mar-98
mso-number-format:"d\-mmm\-yyyy" 01-mar-1998
mso-number-format:"Short Time" 5:16
mso-number-format:"Medium Time" 5:16 am
mso-number-format:"Long Time" 5:16:21:00
mso-number-format:"Percent" Percent - two decimals
mso-number-format:"0%" Percent - no decimals
mso-number-format:"0\.E+00" Scientific Notation
mso-number-format:"\@" Text
mso-number-format:"\#\ ???\/???" Fractions - up to 3 digits (312/943)

导出的excel可以直接通过excel打开,效果如下:

完整代码:


class Program
    {
        
protected const string HEADER = "<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">" +

                                          
"<meta http-equiv=Content-Type content=\"text/html; charset=\"gb2312\">" +
                                          "<head>" +
                                          
"<!--[if gte mso 9]><xml>" +
                                           
"<x:ExcelWorkbook>" +
                                               
"<x:ExcelWorksheets>" +
                                                   
"<x:ExcelWorksheet>" +
                                                       
"<x:Name>工作表标题</x:Name>" +
                                                       
"<x:WorksheetOptions>" +
                                                           
"<x:Print>" +
                                                               
"<x:ValidPrinterInfo />" +
                                                           
"</x:Print>" +
                                                       
"</x:WorksheetOptions>" +
                                                   
"</x:ExcelWorksheet>" +
                                               
"</x:ExcelWorksheets>" +
                                           
"</x:ExcelWorkbook>" +
                                       
"</xml>" +
                                       
"<![endif]-->" ;

        
const string STYLE="<style type=\"text/css\">" +

                                       
".spercent" +
                                       
" {" +
                                       
"   background-color:#ffff99;" +
                                       
"   mso-number-format:0.00%;" +
                                       
" }" +
                                        
".sId" +
                                       
" {" +
                                       
"   background-color:#ff6633;" +
                                       
"   mso-number-format:0;" +
                                       
" }" +
                                        
".sName" +
                                       
" {" +
                                       
"     color:red;" +
                                       
" }" +
                                        
".sValue" +
                                       
" {" +
                                       
"   color:blue;" +
                                       
"   mso-number-format:0;" +
                                       
" }" +
                                       
"</style>";


        
static void Main(string
[] args)
        {
            
using (StreamWriter writer = new StreamWriter(@"C:\1.xls"true, System.Text.Encoding.GetEncoding("gb2312"), 512
))
            {
                writer.WriteLine(HEADER);
                writer.WriteLine(STYLE);
                writer.WriteLine(
"</head><body><table border=\"1\" style=\"font-size:9pt\"><tr>"
);
                writer.WriteLine (
"<th>ID</th>"
);
                writer.WriteLine (
"<th>Name</th>"
);
                writer.WriteLine (
"<th>Value</th>"
);
                writer.WriteLine (
"<th>Percent</th>"
);

                
for (int row = 1; row < 50; row++
)
                {
                    writer.WriteLine(
"<tr>"
);
                    writer.WriteLine(
"<td class=\"sId\">{0}</td"
, row);
                    writer.WriteLine(
"<td class=\"sName\">{0}</td"
, Guid.NewGuid ().ToString ());
                    writer.WriteLine(
"<td class=\"sValue\">{0}</td"new
 Random().Next());
                    writer.WriteLine(
"<td class=\"spercent\">{0}</td"new
 Random().NextDouble());
                    writer.WriteLine(
"</tr>"
);
                }

                writer.WriteLine(
"</table></body>"
);
            }
           
        }
    }
<完>
posted on 2009-10-18 13:19  Lucker  阅读(807)  评论(2编辑  收藏  举报