以前做过一次这样的工作,采用了2种方式:
第一种是调用一个jsp,在jsp里定义其显示格式及文件类型--起到如下作用:
response.setContentType("application/*" + ";name=/"" + sFileName+ "/"");
response.addHeader("Content-Disposition","filename=/"" + sFileName + "/"");
//*************jsp中修改类似上面的两行即可***********//
// (如果后台实现,再在下面接着写入内容)
OutputStreamWriter ow = new OutputStreamWriter( stream , "UTF-8");
ow.write(doc);
stream.close();
ow.close();
//这时上面的doc变量(如果是String且从xml转化 )的得到方式:
InputStream xsltFile = null;
try{
xsltFile = new FileInputStream("//形如:myXlst.xlst---根据显示先定义好");
}catch(Exception e){
System.out.println("UI LOG:The " +sXsltName+ "Xsl load faild ");
}
if(sXml!=null && !sXml.trim().equals("")){
Reader r = new StringReader(sXml);
StreamSource source = new StreamSource(r);
Source xsltSource = new StreamSource(xsltFile);
TransformerFactory transFact = TransformerFactory.newInstance();
Transformer trans = null;
Writer w = new StringWriter();
StreamResult result = new StreamResult(w);
trans = transFact.newTransformer(xsltSource);
trans.transform(source, result);
return w.toString();
}else
return null;
//myXlst.xlst的例子:
<?xml version="1.0" encoding="UTF-8"?>
<!-- edited with XMLSpy v2005 sp2 U (http://www.altova.com) by any (Ru-Board) -->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="http://lucky.myrice.com" xmlns:sldate="urn:sealink-extension-lib:date">
<xsl:output method="html" encoding="UTF-8"/>
<xsl:template match="/">
<html 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 name="ProgId content=Excel.Sheet"/>
<meta name="Generator" content="Microsoft Excel 9"/>
<meta HTTP-EQUIV="Pragma" CONTENT="no-cache"/>
<meta HTTP-EQUIV="Expires" CONTENT="-1"/>
<style>
table
{
width: 100%;
}
.TabTitleStyle
{
font-size: 20px;
vertical-align:middle;
}
.TabSummaryStyle
{
color: navy;
vertical-align:middle;
font-size: 14px;
}
.HeaderStyle
{
color: white;
vertical-align:middle;
font-size: 14px;
background-color: navy;
}
.ItemStyle
{
text-align:right;
vertical-align:middle;
padding-right: 3px;
padding-left: 3px;
border-style: solid;
font-size: 12px;
}
.AlternatingItemStyle
{
text-align:right;
vertical-align:middle;
padding-right: 3px;
padding-left: 3px;
border-style: solid;
font-size: 12px;
background-color: #ebf7ff;
}
.FonterStyle
{
font-size: 12px;
vertical-align:middle;
}
.SubTotalStyle
{
vertical-align:middle;
padding-right: 3px;
padding-left: 3px;
border-style: solid;
font-size: 13px;
background-color: #FFFF99;
}
.GridHead
{
text-align: center;
background-color:#afc7e2;
font-weight: bold;
color:#000000;
}
.GridData
{
background-color:#FFFFFF;
}
</style>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Bill Status Query</x:Name>
<x:WorksheetOptions>
<x:Selected/>
<x:FreezePanes/>
<x:FrozenNoSplit/>
<x:SplitHorizontal>1</x:SplitHorizontal>
<x:TopRowBottomPane>1</x:TopRowBottomPane>
<x:Panes>
<x:Pane>
<x:Number>0</x:Number>
</x:Pane>
</x:Panes>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml>
</head>
<body>
<table cellspacing="0" rules="all" border="1" style="border-collapse:collapse">
<tr>
<td colspan="6" align="center">
<b>Bill Status Query</b>
</td>
</tr>
<tr class="GridHead">
<td align="center">BL Num</td>
<td align="center">BL Status</td>
</tr>
<xsl:apply-templates select="//bills"/>
</table>
</body>
</html>
</xsl:template>
<xsl:template match="bills" xml:space=">
<tr>
<td align="center"><xsl:value-of select="billNoOne"/></td>
<td><xsl:value-of select="xlsStatus1"/></td>
</tr>
<tr>
<td align="center"><xsl:value-of select="billNoTwo"/></td>
<td><xsl:value-of select="xlsStatus2"/></td>
</tr>
<tr>
<td align="center"><xsl:value-of select="billNoThree"/></td>
<td><xsl:value-of select="xlsStatus3"/></td>
</tr>
<tr>
<td align="center"><xsl:value-of select="billNoFour"/></td>
<td><xsl:value-of select="xlsStatus4"/></td>
</tr>
</xsl:template>
</xsl:stylesheet>
第一种也是采用jsp,利用了一个开源包poi(例子就不再举,有兴趣可以自己查)。
第一种生成的xls一般只有一个sheet,此情况下生成多个sheet不知道可不可以,没研究。
弊端: 而且此时如果copy一个xls文件中的sheet到另一个生成的xls的新建sheet中时,会在当前目录下额外生成一个文件夹,正因为如果,当用到多个sheet时,我采用了开源的情况。当然,第一种情况最简单(直接jsp方式--变题头类型,上面所列的jsp后台实现方式考虑到xml转化才写的复杂)
在jsp中实现,生成的xls其实也就是该jsp了,换到servlet中亦无不可。
ps:定义xsl中的字体颜色时,未用template,简单用了
.c9Y6TC { color:#0000ff; } .cLUY2 { color:#ea8f0f; } .c49DIK { color:#ac306d; } .c18YC0 { color:#d00020; } .c5M9S0 { color:#000090; } <font>
<xsl:attribute name="style">color:<xsl:value-of select='colorXls4'/></xsl:attribute>
<xsl:value-of select="billNoFour"/>
</font>