本次的poi导出是用的SXSSFWorkbook,不同于之前写的HSSFWorkbook,HSSFWorkbook导出时,超过65536条数据就会出错,当然前面说的分sheet可以解决该问题,但是SXSSFWorkbook就能导出大量的数据。如下:
1、引用jar包:
我用的版本是3.9的,SXSSFWorkbook的版本要3.8以上的。
poi-3.9.jar、poi-examples-3.0.jar、poi-ooxml-3.9.jar、poi-scratchpad-3.9.jar。我是写过导入,所以要这四个包,其实不需要这么多包,只是我不记得导出只需要哪些包了,所有的包都要同一个版本,不然会冲突。
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.struts2.ServletActionContext;
public class XSSFPoiExcelExport {
Logger logger = Logger.getLogger(XSSFPoiExcelExport.class);
HttpServletResponse response;
// 文件名
private String fileName;
// 文件保存路径
private String fileDir;
// sheet名
private String sheetName;
// 表头字体
private String titleFontType = "Arial Unicode MS";
// 表头背景色
private String titleBackColor = "C1FBEE";
// 表头字号
private short titleFontSize = 12;
// 添加自动筛选的列 如 A:M
private String address = "";
// 正文字体
private String contentFontType = "Arial Unicode MS";
// 正文字号
private short contentFontSize = 12;
// 设置列的公式
private String colFormula[] = null;
public XSSFPoiExcelExport(HttpServletResponse response, String fileName, String sheetName) {
this.response = response;
this.fileName = fileName;
this.sheetName = sheetName;
}
@SuppressWarnings("unchecked")
public void wirteExcel(String titleColumn[], String titleName[], int titleSize[],
List<?> dataList) throws Exception {
// //excel文件名
// String fileName = System.currentTimeMillis() + ".xlsx";
// FileOutputStream fos = null;
/// -> excel导出逻辑
long startTime = System.currentTimeMillis();
SXSSFWorkbook sxssfWorkbook = null;
try {
HttpServletRequest request = ServletActionContext.getRequest();
// 新建文件
OutputStream out = null;
String finalFileName = null;
if (fileDir != null) {
deleteExcel(fileDir);
// 有文件路径
out = new FileOutputStream(fileDir);
} else {
// 否则,直接写到输出流中
out = response.getOutputStream();
fileName = fileName + ".xlsx";
logger.info("导出excel,文件为:" + fileName);
response.setContentType("application/x-msdownload");
final String userAgent = request.getHeader("USER-AGENT"); // 获取浏览器的代理
// 下面主要是让文件名适应不同浏览器的编码格式
if (StringUtils.contains(userAgent, "MSIE")) {
finalFileName = URLEncoder.encode(fileName, "UTF8");
} else if (StringUtils.contains(userAgent, "Mozilla")) {// google,火狐浏览器
finalFileName = new String(fileName.getBytes(), "ISO8859-1");
} else {
finalFileName = URLEncoder.encode(fileName, "UTF8");// 其他浏览器
}
response.setHeader("Content-Disposition", "attachment; filename=\"" + finalFileName + "\"");
}
// 创建sheet并命名
sxssfWorkbook = new SXSSFWorkbook(100);
sxssfWorkbook.setCompressTempFiles(true);
// 设置并获取到需要的样式
XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(sxssfWorkbook);
XSSFCellStyle xssfCellStyleOne = getAndSetXSSFCellStyleOne(sxssfWorkbook);
XSSFCellStyle xssfCellStyleTwo = getAndSetXSSFCellStyleTwo(sxssfWorkbook);
XSSFCellStyle xssfCellStyleThree = getAndSetXSSFCellStyleThree(sxssfWorkbook);
if(dataList!=null) {
// 求总sheet数量,大于pageCount设置的数据就新建一个sheet
int pageCount = 10000;
final int sheetNum = (int) Math.ceil((float) dataList.size() / pageCount);
logger.info("excel中sheet数量:"+sheetNum);
for (int n = 1; n <= sheetNum; n++) {
// 添加Worksheet(不添加sheet时生成的xls文件打开时会报错)
String sheetNames = this.sheetName + "_" + n;
Sheet sheet = sxssfWorkbook.createSheet(sheetNames);
// 冻结最左边的两列、冻结最上面的一行
// 即:滚动横向滚动条时,左边的第一、二列固定不动;滚动纵向滚动条时,上面的第一行固定不动。
// sheet.createFreezePane(2, 1);
sheet.createFreezePane(0, 1);
// 创建第一行,作为header表头
Row header = sheet.createRow(0);
for (int i = 0; i < titleName.length; i++) {
Cell cell = header.createCell(i);
cell.setCellStyle(xssfCellStyleHeader);
cell.setCellValue(titleName[i].toString());
}
if(dataList.size() > 0) {
if (titleColumn.length > 0) {
int list_num_start = pageCount*(n-1); // 开始下标
int list_num_end = (sheetNum == n?(n-1):n)*pageCount+(sheetNum == n?dataList.size()%pageCount:0); // 结束下标
// 将本sheet数据加入到集合中
List<Object> useList = new ArrayList<>();
for (int j = list_num_start; j < list_num_end; j++) {
Object object = dataList.get(j);
useList.add(object);
}
// 遍历创建行,导出数据
int rows = 0;
for (Iterator iterator = useList.iterator(); iterator.hasNext();) {
Object obj = (Object) iterator.next();
Class clsss = obj.getClass(); // 获得该对对象的class实例
rows++;
Row row = sheet.createRow(rows); // 将数据写入行中
// 将数据写入对应列中
int a = 0;
for (int columnIndex = 0; columnIndex < titleColumn.length; columnIndex++) {
String title = titleColumn[columnIndex].toString().trim();
if (!"".equals(title)) { // 字段不为空
// 使首字母大写
String UTitle = Character.toUpperCase(title.charAt(0)) + title.substring(1, title.length()); // 使其首字母大写;
String methodName = "get" + UTitle;
// 设置要执行的方法
Method method = clsss.getDeclaredMethod(methodName);
// 获取返回类型
Class<?> returnType2 = method.getReturnType();
String returnType = returnType2.getName();
String data = method.invoke(obj) == null ? "" : method.invoke(obj).toString();
int x = a++; // 定义列
Cell cell = row.createCell(x); // 将数据写入行中
if (data != null && !"".equals(data)) {
if ("int".equals(returnType)) {
cell.setCellStyle(xssfCellStyleTwo);
cell.setCellValue(Integer.parseInt(data));
} else if ("long".equals(returnType)) {
cell.setCellStyle(xssfCellStyleTwo);
cell.setCellValue(Long.parseLong(data));
} else if ("float".equals(returnType)) {
cell.setCellStyle(xssfCellStyleOne);
cell.setCellValue(Float.parseFloat(data));
} else if ("double".equals(returnType)) {
cell.setCellStyle(xssfCellStyleOne);
cell.setCellValue(Double.parseDouble(data));
} else {
cell.setCellStyle(xssfCellStyleThree);
String str = data.substring(0, 1);
if (data.matches("\\d+") == true && GlobalFunctions.isEquals(str, "0")) {// 判断能否转成数字
cell.setCellValue("'" + data);// 转换成文本形式 避免 0001导出变成1
} else {
cell.setCellValue(data);
}
}
} else {
cell.setCellStyle(xssfCellStyleThree);
}
}
}
}
}
}
setSheet(sheet, titleSize);
}
}
// fos = new FileOutputStream(fileName);
sxssfWorkbook.write(out);
out.close();
long endTime = System.currentTimeMillis();
logger.info("数据全部导出至excel总耗时:" + (endTime - startTime) + "ms;数据长度:" + dataList.size());
// 当前JVM占用的内存总数(M)
double total = (Runtime.getRuntime().totalMemory()) / (1024.0 * 1024);
logger.info("当前JVM占用的内存总数(M):"+ total);
// JVM最大可用内存总数(M)
double max = (Runtime.getRuntime().maxMemory()) / (1024.0 * 1024);
logger.info("JVM最大可用内存总数(M):"+ max);
// JVM空闲内存(M)
double free = (Runtime.getRuntime().freeMemory()) / (1024.0 * 1024);
logger.info("JVM空闲内存(M):"+ free);
// 可用内存内存(M)
double mayuse=(max - total + free);
logger.info("可用内存内存(M):"+ mayuse);
// 已经使用内存(M)
double used=(total-free);
logger.info("已经使用内存(M):"+ used);
} catch (Exception e) {
// TODO: handle exception
logger.error("发生异常!", e);
} finally {
if (sxssfWorkbook != null) {
// dispose of temporary files backing this workbook on disk -> 处
// 理SXSSFWorkbook导出excel时,产生的临时文件
sxssfWorkbook.dispose();
}
// if(fos != null) {
// fos.close();
// }
}
// sheet名
// if (Objects.isNull(num)) {
// num = 65536;
// }
// String[][] content = buildContent(num);
// long start = System.currentTimeMillis();
SXSSFWorkbook wb = ExcelUtil.getSXSSFWorkbookByPageThread(TITLE, content);
// long millis = System.currentTimeMillis() - start;
// long second = millis / 1000;
// System.out.println("SXSSF Page Thread 导出" + num + "条数据,花费:" + second + "s/ " + millis + "ms");
// writeAndClose(response, fileName, wb);
// wb.dispose();
}
/**
* 删除文件,当文件名出现重复的时候调用
*
* @param fileDir
* @return
*/
public boolean deleteExcel(String path) {
boolean flag = false;
File file = new File(path);
// 判断目录或文件是否存在
if (!file.exists()) { // 不存在返回 false
return flag;
} else {
// 判断是否为文件
if (file.isFile()) { // 为文件时调用删除文件方法
file.delete();
flag = true;
}
}
return flag;
}
/**
* 设置sheet
*/
private void setSheet(Sheet sheet, int titleSize[]) {
// 设置各列宽度(单位为:字符宽度的1/256)
for (int i = 0; i < titleSize.length; i++) {
sheet.setColumnWidth(i, titleSize[i] * 256);
}
}
/**
* 获取并设置header样式
*/
private XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
Font font = sxssfWorkbook.createFont();
// 字体大小
font.setFontHeightInPoints((short) 10);
// 字体粗细
font.setBoldweight((short) 20);
// 将字体应用到样式上面
xssfCellStyle.setFont(font);
// 前景颜色
xssfCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
xssfCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
// 是否自动换行
xssfCellStyle.setWrapText(false);
// 水平居中
xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return xssfCellStyle;
}
/**
* 获取并设置样式一
*/
private XSSFCellStyle getAndSetXSSFCellStyleOne(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
XSSFDataFormat format = (XSSFDataFormat) sxssfWorkbook.createDataFormat();
// 是否自动换行
xssfCellStyle.setWrapText(false);
// 水平居中
xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// // 前景颜色
// xssfCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
// xssfCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
// 边框
xssfCellStyle.setBorderBottom(BorderStyle.THIN);
xssfCellStyle.setBorderRight(BorderStyle.THIN);
xssfCellStyle.setBorderTop(BorderStyle.THIN);
xssfCellStyle.setBorderLeft(BorderStyle.THIN);
xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
// xssfCellStyle.setDataFormat(format.getFormat("0"));
xssfCellStyle.setDataFormat(format.getFormat("0.00")); // 保留两位小数点
return xssfCellStyle;
}
/**
* 获取并设置样式二
*/
private XSSFCellStyle getAndSetXSSFCellStyleTwo(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
XSSFDataFormat format = (XSSFDataFormat) sxssfWorkbook.createDataFormat();
// 是否自动换行
xssfCellStyle.setWrapText(false);
// 水平居中
xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
// 边框
xssfCellStyle.setBorderBottom(BorderStyle.THIN);
xssfCellStyle.setBorderRight(BorderStyle.THIN);
xssfCellStyle.setBorderTop(BorderStyle.THIN);
xssfCellStyle.setBorderLeft(BorderStyle.THIN);
xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 垂直居中
xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
xssfCellStyle.setDataFormat(format.getFormat("0"));
return xssfCellStyle;
}
/**
* 获取并设置样式三
*/
private XSSFCellStyle getAndSetXSSFCellStyleThree(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
// 是否自动换行
xssfCellStyle.setWrapText(false);
// 边框
xssfCellStyle.setBorderBottom(BorderStyle.THIN);
xssfCellStyle.setBorderRight(BorderStyle.THIN);
xssfCellStyle.setBorderTop(BorderStyle.THIN);
xssfCellStyle.setBorderLeft(BorderStyle.THIN);
xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
return xssfCellStyle;
}
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(), "UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
调用方法:
// 获取文件名
String statisticsTypeName = "导出";
// 拼接地址文件名并放入
XSSFPoiExcelExport pee = new XSSFPoiExcelExport(response,statisticsTypeName+System.currentTimeMillis(),statisticsTypeName);
// 获取要导出的数据(我这里的获取数据是打个比方,你获取要导出的数据就行)
List<Admin> adminList= new ArrayList<Admin>();
// 要导出的数据字段,按表格的顺序列出
String titleColumn[] = {"Name","Sex","Age","Love"};
// 要导出的表头
String titleName[] = {"姓名","性别","年龄","爱好"};
// 导出到excel中表头的宽度
String titleSize[] = {13,13,13,20};
// 调用导出方法
pee.wirteExcel(titleColumn, titleName, titleSize, adminList);
我这里的SXSSFWorkbook导出excel也分了sheet,不需要的可以自己去除,或者把pageCount改大就行。