Recently, I saw an open source on the Internet that uses java to control excel. I tried it on weblogic and found it very good. I would like to recommend it to everyone.
First go to http://www.andykhan.com/jexcelapi/index.html to download the latest JExcelApi and put jxl.jar in your classpath.
Write a javaBean and use JExcelApi to dynamically generate excel documents. I will write the simplest and schematic one here. If it's complicated, you may need to query the database or something.
//////////////////////////////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, "I love China");
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, "China loves me",wcfFC);
ws.addCell(labelC);
//Write to Exel worksheet wwb.write();
//Close the Excel workbook object wwb.close();
}
//It is best to write a main method like this to test whether your class is written well.
public static void main(String[] args)throws Exception{
File f=new File("kk.xls");
f.createNewFile();
writeExcel(new FileOutputStream(f));
}
}
Write a jsp to use the Javabean Test to output excel documents.
/////////////////////////////test_excel.jsp////////////////////// //////
＜ %@page import="com.jagie.test.Test" %＞
<%
response.reset();
response.setContentType("application/vnd.ms-excel");
Test.writeExcel(response.getOutputStream());
%>
This is done. You can use IE to access test_excel.jsp to open the dynamically generated excel document in IE. No gibberish at all.
Some people may ask: response.reset(); Can this sentence be omitted? My suggestion is that it must be written unless you can guarantee that there is nothing else in the response buffer.
Others may ask: I add < %@page contentType="application/vnd.ms-excel;charset=GBK" %> to the beginning of jsp and remove response.setContentType("application/vnd.ms-excel" ); Is it okay? The answer to this question is very simple, just check the java code generated by the jsp server after compiling jsp. If it is changed to this, the schematic code of the java file generated by my welogic7 after compiling test_excel.jsp is as follows:
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("rnrnrnrn");
out.print("rn");
//[ /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...
}
Obviously, after blocking response.setContentType("application/vnd.ms-excel");, before Test.writeExcel(response.getOutputStream());, and after response.reset();, the correct type of response contenttype is not set , of course the output is garbled. The compiled source code of jsp that correctly outputs excel is as follows:
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("rn");
//[ /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...
}
You can see that after response.reset(); and before Test.writeExcel(response.getOutputStream()); the response output content is correctly set. So the output is normal.
Finally, I hope this article can inspire you. If there are any mistakes, please criticize and correct them!