JXL生成Excel并下载

[size=x-large]package cn.hld.practise.excel;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.WritableFont;

public class TestServletOperateExcel extends HttpServlet
{

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
/*
StringBuffer sb = new StringBuffer() ;
sb.append("测试导出Excel用,现在这个地方只是用一个字符串来表示") ;
sb.append("可以用DisplayTag 或者 vba 或者 jlx 或者 POI来进行导出Excel") ;
exportExcel("thename", sb,request,response) ;
*/
// response.setHeader("Content-disposition","attachment; filename=" + new String(("员工信息表.xls").getBytes("gb2312"),"iso8859-1"));
// response.setDateHeader("Expires", 0);
// response.setContentType("application/vnd.ms-excel;charset=GBK");

//下面是生成Excel但是不提示下载的信息的
try
{
//构建Workbook对象, 只读Workbook对象
//Method 1：创建可写入的Excel工作薄
// jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File("c:/testt.xls"));
//Method 2：将WritableWorkbook直接写入到输出流

OutputStream os = new FileOutputStream("c:/testt.xls");
//OutputStream os = response.getOutputStream() ;
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);

//创建Excel工作表
jxl.write.WritableSheet ws = wwb.createSheet("Test Sheet 1", 0);
//jxl.write.Label labelC = new jxl.write.Label(0, 0, "This is a Label cell");
// 1.添加Label对象
jxl.write.Label labelC = new jxl.write.Label(0, 0, "这是一个电子表格测试");

ws.addCell(labelC);
// 添加带有字型Formatting的对象
jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);
jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
jxl.write.Label labelCF1 = new jxl.write.Label(1, 0, "This is a Label Cell", wcfF);
ws.addCell(labelCF1);
// 添加带有字体颜色Formatting的对象
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
jxl.write.Label labelCFC2 = new jxl.write.Label(1, 0, "This is a Label Cell", wcfFC);
ws.addCell(labelCFC2);
// 2.添加Number对象
jxl.write.Number labelN = new jxl.write.Number(0, 1, 3.1415926);
ws.addCell(labelN);
// 添加带有formatting的Number对象
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.Number labelNF3 = new jxl.write.Number(1, 1, 3.1415926, wcfN);
ws.addCell(labelNF3);
// 3.添加Boolean对象
jxl.write.Boolean labelB = new jxl.write.Boolean(0, 2, false);
ws.addCell(labelB);
// 4.添加DateTime对象
jxl.write.DateTime labelDT4 = new jxl.write.DateTime(0, 3, new java.util.Date());
ws.addCell(labelDT4);
// 添加带有formatting的DateFormat对象
jxl.write.DateFormat df = new jxl.write.DateFormat("dd MM yyyy hh:mm:ss");
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
jxl.write.DateTime labelDTF5 = new jxl.write.DateTime(1, 3, new java.util.Date(), wcfDF);
ws.addCell(labelDTF5);


//写入Exel工作表
wwb.write();
//关闭Excel工作薄对象
wwb.close();

os.flush() ;
os.close() ;


//下面是提示下载Excel的具体操作

String displayFileName = "产品报表.xls" ;
String downLoadFilePath = "c:/testt.xls" ;
displayFileName = URLEncoder.encode(displayFileName,"UTF-8");

response.reset();
response.setContentType("application/x-msdownload");
response.addHeader("Content-Disposition","attachment;filename=" + displayFileName);


OutputStream output = response.getOutputStream();

FileInputStream fis = new FileInputStream(downLoadFilePath);
byte[] b = new byte[1024];
int i = 0;
while((i = fis.read(b)) > 0)
{
output.write(b, 0, i);
}
output.flush();
fis.close();
output.close();
}
catch (Exception e)
{
e.printStackTrace();
}



}

/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
doGet(request,response) ;
}
public void exportExcel(String fileName,StringBuffer sb, HttpServletRequest request,
HttpServletResponse response) {
ServletOutputStream o = null;
if (null != sb) {
try {
if(fileName==""){
fileName="DefaultName";
}
String length = String.valueOf(sb.length());//长度
byte[] b = sb.toString().getBytes("GB2312");

String filename = new String((fileName+".xls").getBytes("GB2312"),
"iso-8859-1");
o = response.getOutputStream();

response.reset();
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content_Length", length);

if (request.getHeader("User-Agent").indexOf("MSIE 5.5") != -1) {
/** MS IE5.5 */
response.setHeader("Content-disposition", "filename=\""
+ filename + "\"");
} else {
/** 非MS IE5.5 */
response.setHeader("Content-disposition",
"attachment;filename=\"" + filename + "\"");
}
try {
o.write(b);
o.flush();
} catch (Exception e) {
response.setStatus(HttpServletResponse.SC_OK);
response.flushBuffer();
} finally {
o.close();
}

} catch (Exception e) {
System.err.println(e.getMessage());
}
}

}

}[/size]