html表格导出Excel的一点经验心得 - TOMMYHU - 专注互联网开发及运营技术,提供相关资料及软件下载,奇趣网络时事评论!
Jul 31

html表格导出Excel的一点经验心得 不指定

tommyhu , 23:29 , ASP.NET , Comments(0) , Trackbacks(0) , Reads(4998) , Via Original Large | Medium | Small
最近在做统计功能,要求统计结果(表格)既能查看(BS系统,在浏览器查看),又能输出为excel文件。对于输出excel文件,在网上找到n种方案,因为还需查看,最终选择了统计结果输出为table,查看时直接显示table,输出excel时把table写进输出流,ContentType设置为application/vnd.ms-excel再输出,具体方法如下:
软件环境:VS2008,C#,IE,office2010
1.输出流内容的格式

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<table>
......
</table>
</body>
</html>

2.Response的属性设置
Response关键属性有几个

这样已经能发布最基本的excel,下面说些细节
1.样式最好用css,即设style属性或class属性,有些样式属性excel不认的。th、td的宽度最好在colgroup中设
2.在excel的格子中换行,可以用这个

3.关于边框宽度的问题,如果你的table是全边框,可以设置table的border属性,其中0=不显示边框。
如果表的格式比较复杂,特别是表头,有些边要隐藏的,这个要先把table的border=0,之后对每个th td用css的边框样式进行设置,但这里有个地方要注意,就是宽度,设为0.5pt(例:border-left: 0.5pt solid #000;),如果设为1px输出的边框会很粗。
4.excel空余部分边框的问题。用此方法输出的excel,空余部分边框都不显示,如果要做成想普通excel的样子,需在输出流的head部分加上excel的设置,例子如下:

    <!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
   <x:ExcelWorksheet>  
    <x:Name></x:Name>
    <x:WorksheetOptions>    
     <x:Selected/>    
    </x:WorksheetOptions>
   </x:ExcelWorksheet>  
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml><![endif]-->

最后附上一个成果作为例子,扩展名为xls可用excel打开

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name></x:Name><x:WorksheetOptions><x:Selected/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
    <style type="text/css">
        .td
        {
            width: 84px;
        }
        .gdtjContainer .tb tr
        {
            text-align: center;
            vertical-align: middle;
        }
        .gdtjContainer .tb th
        {
            border-left: 0.5pt solid #000;
            border-bottom: 0.5pt solid #000;
            text-align: center;
            font-weight: normal;
            font-size: 10pt;
            middle: ;;height:30px;}
        .gdtjContainer .header th
        {
            font-size: 12pt;
        }
        .gdtjContainer .tb tr th.noleftborder
        {
            border-left: none;
        }
        .gdtjContainer .tb tr th.rightborder
        {
            border-right: 0.5pt solid #000;
        }
    </style>
</head>
<body>
    <div class="gdtjContainer">
        <table class="tb" cellspacing="0" cellpadding="0" border="0" width="2184px">
            <colgroup>
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
                <col class="td" />
            </colgroup>
            <tr style="height: 40px">
                <th style="font-size: 20pt; font-family: 宋体; border: none;" colspan="26">
年增城市单位土地使用权出让情况登记表(统计时间从2011-06-29至2011-06-30)
                </th>
            </tr>
            <tr>
                <th colspan="23" style="border-left: none;">
                    &nbsp;
                </th>
                <th style="text-align: left; font-size: 12pt; border-left: none;" colspan="3">
                    单位:万元、平方米
                </th>
            </tr>
            <tr class="header">
                <th rowspan="2">
                    合同编号
                </th>
                <th colspan="2" rowspan="2">
                    用地单位
                </th>
                <th colspan="2" rowspan="2">
                    土地座落
                </th>
                <th rowspan="2">
                    供地面积
                </th>
                <th style="border-left: none">
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th rowspan="2">
                    用途
                </th>
                <th colspan="3" rowspan="1">
                    出让金
                </th>
                <th rowspan="2">
                    容积率
                </th>
                <th rowspan="2">
                    建筑密度
                </th>
                <th rowspan="2">
                    绿地率
                </th>
                <th rowspan="2">
                    规划建筑面积
                </th>
                <th rowspan="2">
                    出让方式
                </th>
                <th rowspan="2">
                    审批日期
                </th>
                <th rowspan="2">
                    合同签订日期
                </th>
                <th rowspan="2">
                    动工期限
                </th>
                <th rowspan="2">
                    竣工日期
                </th>
                <th rowspan="2">
                    批次情况
                </th>
                <th rowspan="2">
                    合同约定缴费期限
                </th>
                <th rowspan="2">
                    缴费情况
                </th>
                <th rowspan="2">
                    滞纳金
                </th>
                <th rowspan="2" class="rightborder">
                    备注
                </th>
            </tr>
            <tr style="height: 40px" class="header">
                <th>
                    新增面积
                </th>
                <th style="font-size: 10pt;">
                    保障性住房用地占用面积
                </th>
                <th>
                    应缴
                </th>
                <th>
                    已缴
                </th>
                <th>
                    未缴
                </th>
            </tr>
            <tr>
                <th>
-2011-
                </th>
                <th colspan="2">

                </th>
                <th colspan="2">
                    &nbsp;
                </th>
                <th>

                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>

                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    拍卖出让
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
-06-29
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th class="rightborder">
                    &nbsp;
                </th>
            </tr>
            <tr>
                <th>
                    &nbsp;
                </th>
                <th colspan="2">
                    合计
                </th>
                <th colspan="2">
                    &nbsp;
                </th>
                <th>

                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>

                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th>
                    &nbsp;
                </th>
                <th class="rightborder">
                    &nbsp;
                </th>
            </tr>
        </table>
    </div>
</body>
</html>

▲返回顶部

Add a comment

Nickname

emotemotemotemotemotemotemotemotemotemotemotemotemotemotemotemot