/**
* 写XLS文件
* @param fileName 文件名(全路径)
* @param colTitleList 标题
* @param colPorpertyList 如果属性列 为NULL,则resultData 中的每个元素为一个LIST
* @param resultData 数据
* @throws Exception
*/
public static void writeXLS(HttpServletResponse response,String fileName,List colTitleList,List colPorpertyList,List resultData) throws Exception {
if (resultData == null)
resultData = new ArrayList();
System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"+fileName+" data count="+resultData.size());
int maxCount = 65536;//每页数据最多65536行
boolean bNeedTitle = true;//每页是否要加上标题一行
if (colTitleList == null || colTitleList.size() ==0)
bNeedTitle = false;
HSSFWorkbook workBook = new HSSFWorkbook();
int nDataPerPage = maxCount;//每页数据条数.
if (bNeedTitle)
nDataPerPage = maxCount-1;
//SHEET数
int sheetCount = resultData.size()/nDataPerPage;
if (resultData.size()%nDataPerPage!=0)
sheetCount++;
//如果无数据,则只添加标题行
if (sheetCount == 0){
HSSFSheet sheet = workBook.createSheet("sheet0");
if (bNeedTitle){
HSSFRow row = sheet.createRow(0);
addSheetRow(colTitleList,row);
}
}
OutputStream ouputStream = null;
int rowIndex = 0;//当前数据写到第几行的索引值
for(int i=0;i<sheetCount;i++) {
System.out.println("页码======"+i);
HSSFSheet sheet = workBook.createSheet("sheet"+i);
int dIndex = 0;
//添加标题
if (bNeedTitle){
HSSFRow row = sheet.createRow(dIndex++);
addSheetRow(colTitleList,row);
}
//添加数据
while(dIndex < maxCount) {
//System.out.println("条数"+dIndex);
HSSFRow tempRow = sheet.createRow(dIndex++);//建立新行
List rowDataObj = null;
if (colPorpertyList == null)//如果没有传属性列,则resultData 中的每个元素为一个LIST
rowDataObj = (List)resultData.get(rowIndex++);
else rowDataObj= getRowData(colPorpertyList,resultData.get(rowIndex++));
addSheetRow(rowDataObj,tempRow);
//全部写完了
if (rowIndex == resultData.size())break;
}
}
ouputStream = response.getOutputStream();
//数据流输出
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename ="+fileName+".xls");
workBook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
//OutputStream fos = new BufferedOutputStream(new FileOutputStream(new File("D:/down/aa.xls")),1024);
//workBook.write(fos);
//fos.flush();
//fos.close();
* 写XLS文件
* @param fileName 文件名(全路径)
* @param colTitleList 标题
* @param colPorpertyList 如果属性列 为NULL,则resultData 中的每个元素为一个LIST
* @param resultData 数据
* @throws Exception
*/
public static void writeXLS(HttpServletResponse response,String fileName,List colTitleList,List colPorpertyList,List resultData) throws Exception {
if (resultData == null)
resultData = new ArrayList();
System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"+fileName+" data count="+resultData.size());
int maxCount = 65536;//每页数据最多65536行
boolean bNeedTitle = true;//每页是否要加上标题一行
if (colTitleList == null || colTitleList.size() ==0)
bNeedTitle = false;
HSSFWorkbook workBook = new HSSFWorkbook();
int nDataPerPage = maxCount;//每页数据条数.
if (bNeedTitle)
nDataPerPage = maxCount-1;
//SHEET数
int sheetCount = resultData.size()/nDataPerPage;
if (resultData.size()%nDataPerPage!=0)
sheetCount++;
//如果无数据,则只添加标题行
if (sheetCount == 0){
HSSFSheet sheet = workBook.createSheet("sheet0");
if (bNeedTitle){
HSSFRow row = sheet.createRow(0);
addSheetRow(colTitleList,row);
}
}
OutputStream ouputStream = null;
int rowIndex = 0;//当前数据写到第几行的索引值
for(int i=0;i<sheetCount;i++) {
System.out.println("页码======"+i);
HSSFSheet sheet = workBook.createSheet("sheet"+i);
int dIndex = 0;
//添加标题
if (bNeedTitle){
HSSFRow row = sheet.createRow(dIndex++);
addSheetRow(colTitleList,row);
}
//添加数据
while(dIndex < maxCount) {
//System.out.println("条数"+dIndex);
HSSFRow tempRow = sheet.createRow(dIndex++);//建立新行
List rowDataObj = null;
if (colPorpertyList == null)//如果没有传属性列,则resultData 中的每个元素为一个LIST
rowDataObj = (List)resultData.get(rowIndex++);
else rowDataObj= getRowData(colPorpertyList,resultData.get(rowIndex++));
addSheetRow(rowDataObj,tempRow);
//全部写完了
if (rowIndex == resultData.size())break;
}
}
ouputStream = response.getOutputStream();
//数据流输出
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename ="+fileName+".xls");
workBook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
//OutputStream fos = new BufferedOutputStream(new FileOutputStream(new File("D:/down/aa.xls")),1024);
//workBook.write(fos);
//fos.flush();
//fos.close();
}
/**
* 添加标题
* @param colTitleList
* @param row
*/
private static void addSheetRow(List rowDataList,HSSFRow row){
if (rowDataList == null || rowDataList.size() == 0)return;
Iterator tIter = rowDataList.iterator();
int index = 0;
while (tIter.hasNext()) {
HSSFCell cell = row.createCell((short)index++);
//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(String.valueOf(tIter.next()));
}
}
经测试 下载一百多万的数据 查询要一分半 导出要三分钟左右,不会报内存溢出 但是数据量太大 xls 文件无法打开 推荐把文件分开放入zip中。