ASP程序快速生成Excel文件

在一个web项目中，要求将数据生成Excel文件保存到本地，最早使用的方法是直接使用Microsoft的Office Web组件，但是总体感觉是慢（微软的通病）。然后使用SQL SERVER语句直接生成excel文件，速度是快了，但是标题等信息还需要再打开一次生成的文件进行修改，还有一个更重要问题就是当查询比较复杂的时候，就很难处理。后来又想用csv格式，但是存在分隔符好可能与数据存在冲突，出现问题。最后想到Office xp以上版本的EXCEL不是可以保存为xml格式么？那么是否可以直接以写文件的形式xml格式呢？于是立刻将一个测试的Excel文件保存成为xml格式，然后对其格式进行了分析研究，最终发现是可行的，而且速度比其他方式快多了，而且效果也好很多。

下面是一个生成的Excel文件的例子：

<?xml version="1.0" encoding="gb2312"?>

<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www.w3.org/TR/REC-html40">

</DocumentProperties>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

</Style>

</Style>

</Style>

</Styles>

<Table ss:ExpandedColumnCount="7" ss:ExpandedRowCount="4" x:FullColumns="1"

x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">

<Row>

<Cell ss:MergeAcross="6" ss:StyleID="s23"><Data ss:Type="String">2006年全年文具用品库存统计</Data></Cell>

</Row>

<Row>

</Row>

<Row>

</Row>

<Row>

</Row>

</Table>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

</Workbook>

在生成该文件的时候一定要注意以下几个问题，不然生成的文件用EXCEL打开的时候会产生错误。

1. <?xml version="1.0" encoding="gb2312"?>

该句一定要加encoding="gb2312"，不然在处理style定义的时候如果包含“宋体”等文字会产生错误。直接用EXCEL保存出来的XML文件是不包含这个编码定义。这个问题很奇怪，我一直搞不明白为什么Excel直接生成的就行，而我自己处理加工的就不行。

2. <Table ss:ExpandedColumnCount="7" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">

这句里面的ExpandedColumnCount和ExpandedRowCount的数字一定要正确。ExpandedColumnCount是表格的列数，ExpandedRowCount是表格的行数。

3. <Cell ss:MergeAcross="6" ss:StyleID="s23"><Data ss:Type="String">2006年全年文具用品库存统计</Data></Cell>

这句里面的ss:MergeAcross="6"也要注意，不能超出第2个问题中定义的列数。

ss:StyleID="s23"是表示该单元格使用的显示格式是style里面的s23定义，就是：

</Style>

4. 设置sheet的名字

要设置sheet的名字可以在该行设置：<Worksheet ss:Name="库存统计">

下面是生成该文件的一个ASP程序：

dim rs,,recordcount,datas,sql,heads,filepath,fso,f,filename,crlf

crlf=chr(10)&chr(13)

recordcount=0 ‘设置记录总数为0

‘生成一个excel文件名

if month(now)>9 then

if day(now)>9 then

filename=year(now) & month(now) & day(now) & replace(time,":","") & ".xls"

else

filename=year(now) & month(now) & "0" & day(now) & replace(time,":","") & ".xls"

end if

else

if day(now)>9 then

filename=year(now) & "0" & month(now) & day(now) & replace(time,":","") & ".xls"

else

filename=year(now) & "0" & month(now) & "0" & day(now) & replace(time,":","") & ".xls"

end if

‘获取生成临时excel文件的目录

filepath=server.mappath("./excel/"& filename)

openconn() ‘打开数据库连接

sql="SELECT WPID, Name, Standard, Unit,instocks,outstocks FROM wupin order by wpid"

set rs=conn.execute(sql) ‘打开记录集

‘生成行列数据

do while not rs.eof

datas=datas & "<Row>" & crlf & _

ProcessColXMlData(rs(0),3) & crlf & _

ProcessColXMlData(rs(1),3) & crlf & _

ProcessColXMlData(rs(2),3) & crlf & _

ProcessColXMlData(rs(3),3) & crlf & _

ProcessColXMlData(rs(4),1) & crlf & _

ProcessColXMlData(rs(5),1) & crlf & _

ProcessColXMlData((rs(4)-rs(5)),1) & crlf & _

"</Row>" & crlf

recordcount=recordcount+1 ‘记录总数+1

rs.movenext

loop

closeconn(3) ‘关闭数据库连接

Set fso = CreateObject("Scripting.FileSystemObject")

set f=fso.CreateTextFile(filepath,2) ‘常见一个要输出的文本文件

‘写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"" ss:Size=""12""/>" & crlf & _

"<Interior/>" & crlf & _

"<NumberFormat/>" & crlf & _

"<Protection/>" & crlf & _

"</Style>" & crlf & _

"<Style ss:ID=""s21"">" & crlf & _

"<NumberFormat ss:Format=""Currency""/>" & crlf & _

"</Style>" & crlf & _

"<Style ss:ID=""s23"">" & crlf & _

"<Alignment ss:Horizontal=""Center"" ss:Vertical=""Bottom""/>" & crlf & _

"</Style>" & crlf & _

"</Styles>" & crlf & _

"<Worksheet ss:Name=""库存统计"">" & crlf & _

"<Table ss:ExpandedColumnCount=""7"" ss:ExpandedRowCount="""&(recordcount+2)&""" x:FullColumns=""1""" & crlf & _

"x:FullRows=""1"" ss:DefaultColumnWidth=""54"" ss:DefaultRowHeight=""14.25"">" & crlf & _

"<Column ss:Index=""2"" ss:AutoFitWidth=""0"" ss:Width=""230.25""/>" & crlf & _

"<Column ss:AutoFitWidth=""0"" ss:Width=""86.25""/>" & crlf & _

"<Row>" & crlf

‘写标题行

f.write "<Cell ss:MergeAcross=""6"" ss:StyleID=""s23""><Data ss:Type=""String"">"&syear&"年全年"&GetClassName(classid)&"库存统计</Data></Cell>" & crlf

‘写表格标题

f.write "</Row>" & crlf & _

"<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 &_

"</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("./excel/"& filename)

end if

‘该函数根据数据类型处理字段的输出格式

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

总结：使用该方法生成EXCEL文件不单速度快，而且灵活，可以适合多种样式的表格数据，唯一缺陷是不支持图表。

参考资料：

HOWTO: Use Visual Basic or ASP to Create an XML Spreadsheet for Excel 2002

如何使用 Visual Basic 或 ASP 创建 Excel 2002 和 Excel 2003 的 XML 表格

http://support.microsoft.com/kb/285891