WEB数据导出EXCEL表格

'写excel的文件头
f.write "<?xml version=""1.0""  encoding=""gb2312""?>" & crlf &  _
"<?mso-application progid=""Excel.Sheet""?>" & crlf &  _
"<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" & crlf &  _
"xmlns:o=""urn:schemas-microsoft-com:office:office""" & crlf &  _
"xmlns:x=""urn:schemas-microsoft-com:office:excel""" & crlf &  _
"xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""" & crlf &  _
"xmlns:html="" http://www.w3.org/TR/REC-html40"">" & crlf &  _
"<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">" & crlf &  _
"</DocumentProperties>" & crlf &  _
"<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">" & crlf &  _
"<ProtectStructure>False</ProtectStructure>" & crlf &  _
"<ProtectWindows>False</ProtectWindows>" & crlf &  _
"</ExcelWorkbook>" & crlf &  _
"<Styles>" & crlf &  _
"<Style ss:ID=""Default"" ss:Name=""Normal"">" & crlf &  _
"<Alignment ss:Vertical=""Bottom""/>" & crlf &  _
"<Borders/>" & crlf &  _
"<Font ss:FontName=""宋体"" x:CharSet=""134""/>" & crlf &  _
"<Interior/>" & crlf &  _
"<NumberFormat/>" & crlf &  _
"<Protection/>" & crlf &  _
"</Style>" & crlf &  _
"<Style ss:ID=""s23"">" & crlf &  _
"<NumberFormat/>" & crlf &  _
"</Style>" & crlf &  _
"</Styles>" & crlf &  _
"<Worksheet ss:Name=""客户资料"">" & crlf & _
"<Table ss:ExpandedColumnCount=""20"" ss:ExpandedRowCount="""&(recordcount+2)&""" x:FullColumns=""1""" & crlf & _
   "x:FullRows=""1"" ss:DefaultRowHeight=""12"">" & crlf & _
   "<Column ss:Index=""3"" ss:AutoFitWidth=""0"" ss:Width=""132""/>" & crlf & _
   "<Column ss:AutoFitWidth=""0"" ss:Width=""106.5""/>" & crlf & _
   "<Column ss:AutoFitWidth=""0"" ss:Width=""65.25""/>" & crlf & _
   "<Column ss:AutoFitWidth=""0"" ss:Width=""63""/>" & crlf & _
   "<Column ss:AutoFitWidth=""0"" ss:Width=""69""/>" & crlf & _
   "<Column ss:AutoFitWidth=""0"" ss:Width=""68.25""/>" & crlf & _
   "<Column ss:AutoFitWidth=""0"" ss:Width=""84.75""/>" & crlf & _
   "<Column ss:AutoFitWidth=""0"" ss:Width=""76.5""/>" & crlf & _
   "<Column ss:AutoFitWidth=""0"" ss:Width=""144"" ss:Span=""2""/>" & crlf & _
   "<Column ss:Index=""14"" ss:AutoFitWidth=""0"" ss:Width=""192.75""/>" & crlf & _
   "<Column ss:Index=""17"" ss:AutoFitWidth=""0"" ss:Width=""78""/>" & crlf & _
   "<Column ss:Index=""19"" ss:AutoFitWidth=""0"" ss:Width=""74.25""/>" & crlf & _
   "<Column ss:AutoFitWidth=""0"" ss:Width=""288""/>" & crlf
'写表格标题
f.write "<Row>" & crlf & _
"<Cell><Data ss:Type=""String"">客户编号</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">客户类型</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">客户名称</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">证件号码</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">车牌号码</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">车型</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">产品型号</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">产品ID</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">车载</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">序列号</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">产品功能</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">第一联系人</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">第二联系人</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">付款方式</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">服务费</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">业务员</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">安装日期</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">安装员</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">查车密码</Data></Cell>" & crlf &_
"<Cell><Data ss:Type=""String"">备注</Data></Cell>" & crlf &_
"</Row>" & crlf
'写数据
f.write datas


'写excel文件尾
f.write "</Table>" & crlf & _
"<WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">" & crlf & _
"<ProtectObjects>False</ProtectObjects>" & crlf & _
"<ProtectScenarios>False</ProtectScenarios>" & crlf & _
"</WorksheetOptions>" & crlf & _
"</Worksheet>" & crlf & _
"</Workbook>" & crlf
f.close
set fso=nothing
'转向excel文件，提供下载
response.redirect("temp/"& filename)


'该函数根据数据类型处理字段的输出格式
Function ProcessColXMlData(data,dataType)
if len(data)=0 or isnull(data) or data="" then data=""
select case dataType
case 1 '整型
ProcessColXMlData="<Cell><Data ss:Type=""Number"">"&data&"</Data></Cell>"
case 2 '货币
ProcessColXMlData="<Cell ss:StyleID=""s21""><Data ss:Type=""Number"">"&data&"</Data></Cell>"
case 3 '字符串
ProcessColXMlData="<Cell><Data ss:Type=""String"">"&data&"</Data></Cell>"
case 4 '日期
ProcessColXMlData="<Cell ss:StyleID=""s22""><Data ss:Type=""DateTime"">"&data&"</Data></Cell>"
end select
End Function
%>

文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags: EXCELEXCEL
相关日志: