首先,jsp页面:
<input type="button" value="点击导出Excel" onClick="javascript:window.location.href='项目名/manage/downloadExcel/bug.do?fileName=bug'" title="点击下载">
然后,struts.xml 配置文件:
<package name="downloadExcel" namespace="/manage/downloadExcel" extends="struts-default"> <action name="bug" class="XX.XX.DownloadBugExcelAction" method="download"> <result name="excel" type="stream"> <param name="contentType">application/vnd.ms-excel</param> <param name="contentDisposition">filename="${fileName}.xls"</param> <param name="inputName">excelStream</param> </result> </action> </package>
然后,action类:
DownloadBugExcelAction.java
package XX.XX.XX;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.ArrayList;
import java.util.Date;
import java.util.Random;
import org.apache.struts2.ServletActionContext;
/**
* 下载excel action
* @author HH
*/
public class DownloadBugExcelAction extends ActionSupport{
private String fileName; //文件名
private String tempPath; //临时文件目录
public String download(){
initExcel();
return "excel";
}
/**
* 将最新的数据插入excel
*/
public void initExcel(){
List list = new ArrayList();
//这里是获取需要填充的数
list = XXXService().getXXX();
//excel模板绝对路径
String path = "/Edu/exceltemplet/";
try{
String filepath = ServletActionContext.getServletContext().getRealPath(path+ fileName +".xls");
FileInputStream fis = new FileInputStream(filepath);
ExcelUtils eu = new ExcelUtils();
tempPath = eu.exportExcel(fis,"temp/"+createFileName(), fileName, list);
}catch(Exception e){
e.printStackTrace();
}
}
public InputStream getExcelStream() {
return ServletActionContext.getServletContext().getResourceAsStream("/Edu/exceltemplet/"+tempPath);
}
/**
* 以年月日时分秒毫秒+4位随机数的格式来创建一个文件名,不带扩展名
* @return 文件名
*/
public static String createFileName() {
StringBuffer sb = new StringBuffer();
Date date = new Date();
//获取年月日时分秒
sb.append(new SimpleDateFormat("yyyyMMddHHmmss").format(date));
//毫秒
String milli = String.valueOf(date.getTime() % 1000);
while (milli.length() < 3) {
milli = "0" + milli;
}
sb.append(milli);
//四位随机数
String rondom = String.valueOf(new Random().nextInt(10000));
while (rondom.length() < 4) {
rondom = "0" + rondom;
}
sb.append(rondom);
return sb.toString();
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
}
ExcelUtils.java
package path.country.a.util;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Iterator;
import java.util.List;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.apache.struts2.ServletActionContext;
import path.country.a.basic.CountryStatics;
import path.country.a.context.ExecuteContext;
import path.country.statedu.entity.BugStat;
/**
* 操作Ecxel工具类
* @author HH
*/
public class ExcelUtils {
/**
* @param sheet 要添加数据的工作表
* @param list 填充的数据
*/
public void addCellOfBug(WritableSheet sheet, List list) {
if (list.size() > 0) {
int i = 0;
for (Iterator it = list.iterator(); it.hasNext();) {
int j = 0;
BugStat bs = (BugStat) it.next();
if (null != bs) {
try {
String bugs = bs.getBugs();
String[] strs = bugs.split(";");
//第一列,填充 XXX, Label(列,行,值)
sheet.addCell(new Label(j++, i + 3, bs.getSiteName()));
//第二列,填充 XXXXX
sheet.addCell(new Label(j++, i + 3, strs[0].equals("1") ? "是" : "否"));
} catch (WriteException ex) {
ex.printStackTrace();
}
}
i++;
}
}
}
/**
* 得到实际保存文件根目录
*/
public static String getRootPath() {
return ServletActionContext.getServletContext().getRealPath("").replace("\\", "/") + "/Edu/exceltemplet/";
}
/**
* 输出excel
* @param is 原始excel模版输入流
* @param path 临时文件目录
* @param fileName 文件名
* @param list 要填充的数据
* @return 返回相对临时文件的目录
*/
public String exportExcel(InputStream is, String path, String fileName, List list) {
//临时目录,用于生成临时文件
String tempPath = null ;
WritableWorkbook wb = null;
File f = new File(getRootPath() + path);
//不存在则创建它
if (!f.exists())
f.mkdirs();
tempPath = getRootPath() + path + "/" + fileName + ".xls";
final File file = new File(tempPath);
OutputStream oss = null;
try {
//创建临时文件
if(file.createNewFile()){
oss = new FileOutputStream(file);
wb = Workbook.createWorkbook(oss, Workbook.getWorkbook(is));
WritableSheet sheet = wb.getSheet(0);
if ("bug".equals(fileName)) {
addCellOfBug(sheet, list);
}
wb.write();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
wb.close();
oss.flush();
oss.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//过一段时间之后,删除临时文件
new Thread(new Runnable() {
public void run() {
try {
// 线程睡20秒
Thread.sleep(20000);
} catch (InterruptedException e) {
e.printStackTrace();
}
// 删除临时文件
file.delete();
}
}).start();
return path + "/" + fileName + ".xls";
}
}
ps:
1、这里的创建随机文件是为了避免当同一时刻,2个或以上用户同时点击的时候,出现下载数据混乱情况。
2、这里的BugStat 类只是一个javaBean,所以就不贴出来了。
3、Thread.sleep(20000) 只是测试用的,当然实际情况可以设置为1小时,差不多了。(根据实际情况而定)
4、还有在tomcat启动时,就删除Edu/exceltemplet/temp下所有文件,避免临时文件夹及文件某些特殊原因没有删除而导致太多
web.xml配置:
<servlet> <servlet-name>FileDeleteServlet</servlet-name> <servlet-class>XX.XX.FileDeleteServlet</servlet-class> <!--这个设置的意思是表示在服务器启动的时候跟着启动.数字5表示的是启动的顺序--> <load-on-startup>5</load-on-startup> </servlet>
FileDeleteServlet.java
package XX.XX;
import java.io.File;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
/**
* 清除excel临时存放文件
* @author HH
*/
public class FileDeleteServlet extends HttpServlet {
//这里只需要这个init方法.因为这个不需要用户调用
@Override
public void init() throws ServletException {
//excel临时文件的文件夹
String path = getServletConfig().getServletContext().getRealPath("").replace("\\", "/")+"/Edu/exceltemplet/temp";
delAllFile(path);
}
/**
* 删除指定文件夹下所有文件
* @param path 文件夹完整绝对路径
* @return
*/
public static boolean delAllFile(String path) {
boolean flag = false;
File file = new File(path);
if (!file.exists()) {
return flag;
}
if (!file.isDirectory()) {
return flag;
}
String[] tempList = file.list();
File temp = null;
for (int i = 0; i < tempList.length; i++) {
if (path.endsWith(File.separator)) {
temp = new File(path + tempList[i]);
} else {
temp = new File(path + File.separator + tempList[i]);
}
if (temp.isFile()) {
temp.delete();
}
if (temp.isDirectory()) {
delAllFile(path + "/" + tempList[i]);//先删除文件夹里面的文件
delFolder(path + "/" + tempList[i]);//再删除空文件夹
flag = true;
}
}
return flag;
}
/**
* 删除文件夹
* @param folderPath 文件夹完整绝对路径
*/
public static void delFolder(String folderPath) {
try {
delAllFile(folderPath); //删除完里面所有内容
String filePath = folderPath;
filePath = filePath.toString();
File myFilePath = new File(filePath);
myFilePath.delete(); //删除空文件夹
} catch (Exception e) {
e.printStackTrace();
}
}
}
如果大家有不当之处,欢迎拍砖~