最近在網(wǎng)上看到一個用java來操縱excel的open source,在weblogic上試用了一下,覺得很不錯,特此向大家推薦一下。
首先去http://www.andykhan.com/jexcelapi/index.html下載最新的jexcelapi,把jxl.jar置于你的classpath中。
寫一個javabean,利用jexcelapi來動態(tài)生成excel文檔，我這里寫一個最簡單的，示意性的。復(fù)雜的你可能還要查詢數(shù)據(jù)庫什么的。
///////////////////////////test.java///////////////////////////////////////////
package com.jagie.test;
import java.io.*;
import jxl.*;
import jxl.write.*;
import jxl.format.*;
import java.util.*;
import java.awt.color;
public class test{
public static void writeexcel(outputstream os) throws exception {
jxl.write.writableworkbook wwb = workbook.createworkbook(os);
jxl.write.writablesheet ws = wwb.createsheet("testsheet1", 0);
jxl.write.label labelc = new jxl.write.label(0, 0, "我愛中國");
ws.addcell(labelc);
jxl.write.writablefont wfc = new jxl.write.writablefont(writablefont.arial,
20, writablefont.bold, false,
underlinestyle.no_underline, jxl.format.colour.green);
jxl.write.writablecellformat wcffc = new jxl.write.writablecellformat(wfc);
wcffc.setbackground(jxl.format.colour.red);
labelc = new jxl.write.label(6, 0, "中國愛我",wcffc);
ws.addcell(labelc);
//寫入exel工作表
wwb.write();
//關(guān)閉excel工作薄對象
wwb.close();
}
//最好寫一個這樣的main方法來測試一下你的這個class是否寫好了。
public static void main(string[] args)throws exception{
file f=new file("kk.xls");
f.createnewfile();
writeexcel(new fileoutputstream(f));
}
}
寫一個jsp,來利用test這個javabean輸出excel文檔。
///////////////////////////test_excel.jsp//////////////////////////
<%@page import="com.jagie.test.test" %>
<%
response.reset();
response.setcontenttype("application/vnd.ms-excel");
test.writeexcel(response.getoutputstream());
%>
這樣就大功告成了，你用ie訪問test_excel.jsp就能在ie里面打開動態(tài)生成的excel文檔了。一點亂碼也沒有。
也許有人會問:response.reset();可不可以不要這一句,我的建議是一定要寫，除非你能保證response的buffer里面沒有別的東西。
還有人也許會問:我在jsp開頭加上<%@page contenttype="application/vnd.ms-excel;charset=gbk" %>這一句，去掉response.setcontenttype("application/vnd.ms-excel");行不行?回答這個問題很簡單，就是查看jsp服務(wù)器編譯jsp后生成的java代碼，如果改成這樣，我的welogic7編譯test_excel.jsp后生成的java文件的示意性代碼是這樣的:
public void _jspservice(javax.servlet.http.httpservletrequest request,
javax.servlet.http.httpservletresponse response) throws java.io.ioexception,
javax.servlet.servletexception {
// declare and set well-known variables:
javax.servlet.servletconfig config = getservletconfig();
javax.servlet.servletcontext application = config.getservletcontext();
javax.servlet.jsp.tagext.tag _activetag = null;
// variables for tag extension protocol
object page = this;
javax.servlet.jsp.jspwriter out;
javax.servlet.jsp.pagecontext pagecontext =
javax.servlet.jsp.jspfactory.getdefaultfactory().getpagecontext(this,
request, response, null, true, 8192, true);
response.setheader("content-type",
"application/vnd.ms-excel; charset=gbk");
out = pagecontext.getout();
jspwriter _originalout = out;
javax.servlet.http.httpsession session = request.getsession(true);
try { // error page try block
response.setcontenttype("application/vnd.ms-excel;charset=gbk");
out.print("/r/n/r/n/r/n/r/n");
out.print("/r/n");
//[ /test_excel.jsp; line: 6]
response.reset(); //[ /test_excel.jsp; line: 7]
//response.setcontenttype("application/vnd.ms-excel");
//[ /test_excel.jsp; line: 8]
test.writeexcel(response.getoutputstream()); //[ /test_excel.jsp; line: 9]
} catch (throwable __ee) {
while (out != null && out != _originalout) out = pagecontext.popbody();
((weblogic.servlet.jsp.pagecontextimpl)pagecontext).handlepageexception((throwable)__ee);
}
//before final close brace...
}
很明顯，屏蔽response.setcontenttype("application/vnd.ms-excel");后，在test.writeexcel(response.getoutputstream());之前，response.reset(); 之后沒有設(shè)置response contenttype的正確類型，當(dāng)然輸出為亂碼了。而正確輸出excel的jsp的編譯后源碼是這樣的:
public void _jspservice(javax.servlet.http.httpservletrequest request,
javax.servlet.http.httpservletresponse response) throws java.io.ioexception,
javax.servlet.servletexception
{
// declare and set well-known variables:
javax.servlet.servletconfig config = getservletconfig();
javax.servlet.servletcontext application = config.getservletcontext();
javax.servlet.jsp.tagext.tag _activetag = null;
// variables for tag extension protocol
object page = this;
javax.servlet.jsp.jspwriter out;
javax.servlet.jsp.pagecontext pagecontext =
javax.servlet.jsp.jspfactory.getdefaultfactory().getpagecontext(
this, request, response, null, true, 8192, true);
out = pagecontext.getout();
jspwriter _originalout = out;
javax.servlet.http.httpsession session = request.getsession(true);
try { // error page try block
out.print("/r/n");
//[ /test_excel.jsp; line: 2]
response.reset(); //[ /test_excel.jsp; line: 3]
response.setcontenttype("application/vnd.ms-excel"); //[ /test_excel.jsp; line: 4]
test.writeexcel(response.getoutputstream()); //[ /test_excel.jsp; line: 5]
} catch (throwable __ee) {
while (out != null && out != _originalout) out = pagecontext.popbody();
((weblogic.servlet.jsp.pagecontextimpl)pagecontext).handlepageexception((throwable)__ee);
}
//before final close brace...
}
大家可以看到在response.reset();之后,test.writeexcel(response.getoutputstream());之前正確的設(shè)置了response的輸出內(nèi)容。所以輸出就正常了。
