ASP程序快速生成Excel文件
2018-09-06 11:49
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=
<DocumentProperties xmlns=urn:schemas-microsoft-com:office:office>
</DocumentProperties>
<ExcelWorkbook xmlns=urn:schemas-microsoft-com:office:excel>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID=Default ss:Name=Normal>
<Alignment ss:Vertical=Bottom/>
<Borders/>
<Font ss:FontName=宋体 x:CharSet=134 ss:Size=12/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID=s21>
<NumberFormat ss:Format=Currency/>
</Style>
<Style ss:ID=s23>
<Alignment ss:Horizontal=Center ss:Vertical=Bottom/>
</Style>
</Styles>
<Worksheet ss:Name=库存统计>
<Table ss:ExpandedColumnCount=7 ss:ExpandedRowCount=4 x:FullColumns=1
x:FullRows=1 ss:DefaultColumnWidth=54 ss:DefaultRowHeight=14.25>
<Column ss:Index=2 ss:AutoFitWidth=0 ss:Width=230.25/>
<Column ss:AutoFitWidth=0 ss:Width=86.25/>
<Row>
<Cell ss:MergeAcross=6 ss:StyleID=s23><Data ss:Type=String>2006年全年文具用品库存统计</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type=String>物品编号</Data></Cell>
<Cell><Data ss:Type=String>物品名称</Data></Cell>
<Cell><Data ss:Type=String>型号规格</Data></Cell>
<Cell><Data ss:Type=String>计量单位</Data></Cell>
<Cell><Data ss:Type=String>进仓数量</Data></Cell>
<Cell><Data ss:Type=String>出仓数量</Data></Cell>
<Cell><Data ss:Type=String>库存</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type=String>02001001</Data></Cell>
<Cell><Data ss:Type=String>钢笔</Data></Cell>
<Cell><Data ss:Type=String>钢笔</Data></Cell>
<Cell><Data ss:Type=String>支</Data></Cell>
<Cell><Data ss:Type=Number>30</Data></Cell>
<Cell><Data ss:Type=Number>2</Data></Cell>
<Cell><Data ss:Type=Number>28</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type=String>02002001</Data></Cell>
<Cell><Data ss:Type=String>签字笔</Data></Cell>
<Cell><Data ss:Type=String>签字笔</Data></Cell>
<Cell><Data ss:Type=String>支</Data></Cell>
<Cell><Data ss:Type=Number>200</Data></Cell>
<Cell><Data ss:Type=Number>54</Data></Cell>
<Cell><Data ss:Type=Number>146</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns=urn:schemas-microsoft-com:office:excel>
<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 ss:ID=s23>
<Alignment ss:Horizontal=Center ss:Vertical=Bottom/>
</Style>
4. 设置sheet的名字
要设置sheet的名字可以在该行设置：<Worksheet ss:Name=库存统计>
下面是生成该文件的一个ASP程序：
<!--#include file=include/conn.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