3.8版本的POI对excel的导出操作,一般只使用HSSFWorkbook以及SXSSFWorkbook,HSSFWorkbook用来处理较少的数据量,SXSSFWorkbook用来处理大数据量以及超大数据量的导出。
3.8版本的POI新出来了SXSSFWorkbook,可以支持大数据量的操作,只是SXSSFWorkbook只支持.xlsx格式,不支持.xls格式。
DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
Random rand = new Random();
// 导出Excel文件名
String xlsName = "xxx_"+df.format(new Date())+rand.nextInt(100)+".xlsx";
//创建excel文件,内存只有100条记录
Workbook wb = new SXSSFWorkbook(100);
//一个sheet存储的记录条数
int record = 5000;
//总记录数
int recordTotal = listtemp.size();
//sheet个数(循环几次就有几个sheet,1个sheet存放5000条数据)
int shTotal = recordTotal % EXCEL_SHEETRECORD == 0?recordTotal/EXCEL_SHEETRECORD:recordTotal/EXCEL_SHEETRECORD + 1;
//最后一个sheet中记录的条数
int lastRecord = recordTotal % EXCEL_SHEETRECORD == 0?record:recordTotal % EXCEL_SHEETRECORD;
for(int shIndex = 0; shIndex < shTotal; shIndex++){
// 创建工作表
Sheet sh = wb.createSheet("sheet"+shIndex);
//最后一个sheet
if(shIndex == shTotal - 1){
// 写表头 创建第一行(表头,即列名)
Row row = sh.createRow((short)0);
// 给列添加名字
row.createCell((short)0).setCellValue("xxx");
row.createCell((short)1).setCellValue("xxx");
row.createCell((short)2).setCellValue("xxx");
row.createCell((short)3).setCellValue("xxx"); // 数据写入EXCEL
for(short i = 0 ; i < lastRecord ; i++){
// 创建数据行
Row rowTmp = sh.createRow((short)(i+1));
//最后一个sheet的记录的索引
int ii = EXCEL_SHEETRECORD * shIndex + i;
// 得到一个对象(即一条记录)
Object[] oj = listtemp.get(ii);
// Create a cell and put a value in it.
rowTmp.createCell((short)0).setCellValue((oj[1]==null || oj[1].toString().equals(""))?"":oj[1].toString());
rowTmp.createCell((short)1).setCellValue((oj[2]==null || oj[2].toString().equals(""))?"":oj[2].toString());
rowTmp.createCell((short)2).setCellValue((oj[3]==null || oj[3].toString().equals(""))?"":oj[3].toString());
rowTmp.createCell((short)3).setCellValue((oj[4]==null || oj[4].toString().equals(""))?"":oj[4].toString());
}
}else{
// 写表头 创建第一行(表头,即列名)
Row row = sh.createRow((short)0);
// 给列添加名字
row.createCell((short)0).setCellValue("xxx");
row.createCell((short)1).setCellValue("xxx");
row.createCell((short)2).setCellValue("xxx");
row.createCell((short)3).setCellValue("xxx"); // 数据写入EXCEL
for(short i = 0 ; i < listtemp.size() ; i++){
// 创建数据行
Row rowTmp = sh.createRow((short)(i+1));
//第shIndex个sheet的记录的索引
int ii = EXCEL_SHEETRECORD * shIndex + i;
// 得到一个对象(即一条记录)
Object[] oj = listtemp.get(ii);
// Create a cell and put a value in it.
rowTmp.createCell((short)0).setCellValue((oj[1]==null || oj[1].toString().equals(""))?"":oj[1].toString());
rowTmp.createCell((short)1).setCellValue((oj[2]==null || oj[2].toString().equals(""))?"":oj[2].toString());
rowTmp.createCell((short)2).setCellValue((oj[3]==null || oj[3].toString().equals(""))?"":oj[3].toString());
rowTmp.createCell((short)3).setCellValue((oj[4]==null || oj[4].toString().equals(""))?"":oj[4].toString());
}
}
}
HttpServletResponse response = ServletActionContext.getResponse();
//指定输出文件名
response.setHeader("Content-Disposition","attachment;filename=" + xlsName);
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
OutputStream os = null;
try {
os = response.getOutputStream();
wb.write(response.getOutputStream());
os.flush();
os.close();
} catch (IOException e) {
}