我们使用的导出并下载功能是利用一个插件叫POI的插件提供的导出功能,很实用;首先先导入Jar包:
Action代码:
public void exportToExcel(List data) throws Exception
{
this.setEnableAccessRequest(true);
this.setEnableAccessResponse(true);
HttpServletRequest request = this.getRequest();
HttpServletResponse response = this.getResponse();
String randomNumber = request.getParameter("randomNumber");// session名称
try {
session = request.getSession();
session.setAttribute(randomNumber, new Double(1));
// 导出的EXCEL文件名
String exportFileName = "addressBook.xlsx";
response.reset();
response.setContentType("octets/stream");
// response.setHeader("Content-Disposition","attachment;filename="+exportFileName);
response.setHeader("Content-Disposition", "attachment;filename=\"" + new String(exportFileName.getBytes("UTF-8"), "iso8859-1") + "\"");
// 导出的EXCEL列属性
List columnListName = new ArrayList();
columnListName.add("userName&姓名");
columnListName.add("mobile&手机");
columnListName.add("shopTel&分店电话");
columnListName.add("postName&职位");
columnListName.add("email&邮箱");
columnListName.add("shopAddress&分店地址");
Bean2ExcelConversionUtils.beans2excelFile07(columnListName, data, response.getOutputStream());
session.setAttribute(randomNumber, new Double(100));
} catch (Exception e) {
e.printStackTrace();
session.setAttribute(randomNumber, new Double(100));
} catch (Throwable e) {
e.printStackTrace();
session.setAttribute(randomNumber, new Double(100));
}
}
JSP代码:
function exportToExcel() {
var randomNumber=new Date().getTime();
top.$.jBox.tip("正在导出...", 'loading');
var exportDate = "${ctx}/xxxAction.do?method=export&randomNumber="+randomNumber;
$("#exportForm").attr("action", exportDate);
$("#exportForm").attr("method","post");
$("#exportForm").submit();
}
因为是使用的插件,所以需要引入一个工具类(下面的工具类直接复制到新建的类文件里面即可)
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
/**
* @author Tan Jiangyong
* @date 2013-9-3 下午3:36:43
* @version V1.0
*/
@SuppressWarnings("all")
public class Bean2ExcelConversionUtils {
private static final String PATTERN="yyyy-MM-dd HH:mm:ss"; //excel日期格式,默认配置
private static final String DATE_PATTERN="yyyy-MM-dd"; //excel日期格式
private static final String DATE_HH_PATTERN="HH:mm:ss"; //excel时间格式
private static final int TOTAL_SIZE=40000; //每个excel写入多少数据(默认配置)
private static final int MAX_SHEET_SIZE=10000; //每一个sheet的大小(默认配置)
private static final int COLUMN_WIDTH_WORD=25; //列宽,默认汉字个数为25个
private static final int FLUSH_ROWS=100; //每生成excel行数,内存中缓存记录数清空(目的,避免零时文件过大)
/**
* 07、10办公版EXCEL导出(数据直接写到服务器的EXCEL里,以下载的形式,下载导出的数据)
* @param listName 列表头名称
* @param beans 实体集合
* @param result 数字字典Map集
* @param filePath 服务器存放文件路径
* @param fileName 文件名称
* @param totalSize EXCEL条数量
* @param maxSheetSize sheet页条数量
* @return 文件集合
* @throws Exception
*/
public static List beans2excelFile07(List listName,List beans,HashMap> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize) throws Exception{
if(totalSize==null || totalSize<=0)
totalSize=TOTAL_SIZE;
if(maxSheetSize==null || maxSheetSize<=0)
maxSheetSize=MAX_SHEET_SIZE;
if(fileName==null)
fileName="";
return beans2excelFile2007(listName, beans, result, filePath, fileName,totalSize,maxSheetSize);
}
/**
* 07、10办公版EXCEL导出(数据直接写到服务器的EXCEL里,以下载的形式,下载导出的数据)
* @param listName 列表头名称
* @param beans 实体集合
* @param result 数字字典Map集
* @param filePath 服务器存放文件路径
* @param fileName 文件名称
* @param totalSize EXCEL条数量
* @param maxSheetSize sheet页条数量
* @param request 客户端请求对象
* @param response 客户端响应对象
* @throws Exception
*/
public static void beans2excelFile07(List listName,List beans,HashMap> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize,HttpServletRequest request,HttpServletResponse response) throws Exception{
if(totalSize==null || totalSize<=0)
totalSize=TOTAL_SIZE;
if(maxSheetSize==null || maxSheetSize<=0)
maxSheetSize=MAX_SHEET_SIZE;
if(fileName==null)
fileName="";
List files = beans2excelFile2007(listName, beans, result, filePath, fileName,totalSize,maxSheetSize);
DownLoadUtils.downLoadFiles(files, filePath, request, response);
}
/**
* 07、10办公版EXCEL导出,每个EXCEL组织数据
* @param listName 列表头名称
* @param beans 实体集合
* @param result 数字字典Map集
* @param filePath 服务器存放文件路径
* @param fileName 文件名称
* @param totalSize EXCEL条数量
* @param maxSheetSize sheet页条数量
* @return 文件集合
* @throws Exception
*/
private static List beans2excelFile2007(List listName,List beans,HashMap> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize) throws Exception{
if ((listName == null) || (listName.size() == 0)) {
throw new Exception("listName is null when create excel document");
}
List listFile=new ArrayList();//返回的文件集合
int size=beans==null?0:beans.size();
String fileSuffixName=".xlsx";//后缀
String path="";//文件路径
Integer startIdx=0;//数据读取的起始行
Integer endIdx=0;//数据读取的结束行
(new File(filePath)).mkdirs(); //没有该目录创建目录
if(size==0){
startIdx=0;
endIdx=(totalSize)>size?size:(totalSize);
String name=fileName+"_第0-0条数据";
path=filePath+File.separatorChar+name+fileSuffixName;
Workbook wb =new SXSSFWorkbook();
buildExcelDocument2007(wb, listName, beans,result,startIdx,endIdx,maxSheetSize);
//没有文件,创建文件
File file = new File(path);
if (!file.exists()){
file.createNewFile();
}
FileOutputStream out=new FileOutputStream(file);
wb.write(out);
out.close();
return listFile;
}
for (int i = 0; i < size;i++) {
int remainder=i%totalSize;
if(size==0 || remainder==0){
startIdx=i;
endIdx=(i+totalSize)>size?size:(i+totalSize);
String name=fileName+"_第"+(startIdx+1)+"-"+(endIdx)+"条数据";
path=filePath+"/"+name+fileSuffixName;
Workbook wb =new SXSSFWorkbook();
buildExcelDocument2007(wb, listName, beans,result,startIdx,endIdx,maxSheetSize);
//没有文件,创建文件
File file = new File(path);
if (!file.exists()){
file.createNewFile();
}
FileOutputStream out=new FileOutputStream(file);
wb.write(out);
out.close();
listFile.add(file);
}else if((size-i)endIdx){//最后,不满一万条
startIdx=i;
endIdx=i+totalSize;
String name=fileName+"_第"+(startIdx+1)+"-"+(endIdx)+"条数据";
path=filePath+name+"."+fileSuffixName;//没有文件,创建文件
Workbook wb =new SXSSFWorkbook();
buildExcelDocument2007(wb, listName, beans, result,startIdx,endIdx,maxSheetSize);
//没有文件,创建文件
File file = new File(path);
if (!file.exists()){
file.createNewFile();
}
FileOutputStream out=new FileOutputStream(file);
wb.write(out);
out.close();
listFile.add(file);