最近开发项目中遇到了很多问题,终于可以在空闲的时候总结一下,今天要汇总的是Excel下载导出等相关问题,主要涉及到问题,如下:
1. 生成Excel
2. 大数据Excel导出(支持多个sheet,亲测可以导出30W数据,再大程序报内存溢出)
3. 下载文件,及中文文件名下载乱码,为空等问题
工具/原料
1、poi-3.8.jar2、poi-ooxml-3.8.jar
方法/步骤
1、添加Excel标题对象
/**
* 用来存储Excel标题的对象,通过该对象可以获取标题和方法的对应关系
*
* @author monkey
*
*/
public class ExcelHeader implements Comparable<ExcelHeader> {
/**
* excel的标题名称
*/
private String title;
/**
* 每一个标题的顺序
*/
private int order;
/**
* 说对应方法名称
*/
private String methodName;
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public int getOrder() {
return order;
}
public void setOrder(int order) {
this.order = order;
}
public String getMethodName() {
return methodName;
}
public void setMethodName(String methodName) {
this.methodName = methodName;
}
public int compareTo(ExcelHeader o) {
return order > o.order ? 1 : (order < o.order ? -1 : 0);
}
public ExcelHeader(String title, int order, String methodName) {
super();
this.title = title;
this.order = order;
this.methodName = methodName;
}
@Override
public String toString() {
return "ExcelHeader [title=" + title + ", order=" + order
+ ", methodName=" + methodName + "]";
}
}
定义annotation类,可以通过该类设置导出相应的属性,标题及排序
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
/**
* 用来在对象的get方法上加入的annotation,通过该annotation说明某个属性所对应的标题
* @author Administrator
*
*/
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelResources {
/**
* 属性的标题名称
* @return
*/
String title();
/**
* 在excel的顺序
* @return
*/
int order() default 9999;
}
操作Excel,设置Excel标题样式,sheet名称,sheet每页显示条数等信息
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Workbook;
/**
* Excel 操作类
*
* @author houzhiqing
*
*/
@SuppressWarnings("unchecked")
public class ExcelUtil {
private static final String POSITION_TITLE = "title";
private static final String POSITION_BODY = "body";
private static ExcelUtil eu = new ExcelUtil();
private ExcelUtil() {
}
public static ExcelUtil getInstance() {
return eu;
}
/**
* 导出对象到Excel,不是基于模板的,直接新建一个Excel完成导出,基于路径的导出
*
* @param outPath
* 输出路径
* @param objs
* 数据源
* @param clz
* 类
* @param sheetName
* 分sheet导出是sheet的名字 , 如 “sheet” -> sheet1,sheet2...
* @param pageSize
* 每个sheet要显示多少条数据
*/
public void exportObj2Excel(String outPath, List objs, Class clz,
String sheetName, int pageSize) {
Workbook wb = handleObj2Excel(objs, clz, sheetName, pageSize);
FileOutputStream fos = null;
try {
fos = new FileOutputStream(outPath);
wb.write(fos);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (fos != null)
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 导出对象到Excel,不是基于模板的,直接新建一个Excel完成导出,基于路径的导出
*
* @param outPath
* 输出路径
* @param objs
* 数据源
* @param clz
* 类
* @param sheetName
* 分sheet导出是sheet的名字 , 如 “sheet” -> sheet1,sheet2...
* @param pageSize
* 每个sheet要显示多少条数据
*/
public HSSFWorkbook handleObj2Excel(List objs, Class clz, String sheetName,
int pageSize) {
HSSFWorkbook wb = null;
try {
wb = new HSSFWorkbook();
// TODO 获取表头
List<ExcelHeader> headers = getHeaderList(clz);
Collections.sort(headers);
if (null != objs && objs.size() > 0) {
int sheetCount = objs.size() % pageSize == 0 ? objs.size()
/ pageSize : objs.size() / pageSize + 1;
for (int i = 1; i <= sheetCount; i++) {
HSSFSheet sheet = null;
if(!StringUtils.isEmpty(sheetName)) {
sheet = wb.createSheet(sheetName + i);
} else {
sheet = wb.createSheet();
}
HSSFRow row = sheet.createRow(0);
// 写标题
CellStyle titleStyle = setCellStyle(wb, POSITION_TITLE);
for (int m = 0; m < headers.size(); m++) {
HSSFCell cell = row.createCell(m);
cell.setCellStyle(titleStyle);
cell.setCellValue(headers.get(m).getTitle());
sheet.setColumnWidth(m, 5000); // 设置每列的宽度
}
// 写数据
Object obj = null;
CellStyle bodyStyle = setCellStyle(wb, POSITION_BODY);
int begin = (i - 1) * pageSize;
int end = (begin + pageSize) > objs.size() ? objs.size()
: (begin + pageSize);
System.out.println("begin:" + begin + ",end=" + end);
int rowCount = 1;
for (int n = begin; n < end; n++) {
row = sheet.createRow(rowCount);
rowCount++;
obj = objs.get(n);
for (int x = 0; x < headers.size(); x++) {
Cell cell = row.createCell(x);
cell.setCellStyle(bodyStyle);
cell.setCellValue(BeanUtils.getProperty(obj,
getMethodName(headers.get(x))));
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("生成excel失败");
}
return wb;
}
/**
* 根据标题获取相应的方法名称
*
* @param eh
* @return
*/
private String getMethodName(ExcelHeader eh) {
String mn = eh.getMethodName().substring(3);
mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
return mn;
}
/**
* 获取excel标题列表
*
* @param clz
* @return
*/
private List<ExcelHeader> getHeaderList(Class clz) {
List<ExcelHeader> headers = new ArrayList<ExcelHeader>();
Method[] ms = clz.getDeclaredMethods();
for (Method m : ms) {
String mn = m.getName();
if (mn.startsWith("get")) {
if (m.isAnnotationPresent(ExcelResources.class)) {
ExcelResources er = m.getAnnotation(ExcelResources.class);
headers.add(new ExcelHeader(er.title(), er.order(), mn));
}
}
}
return headers;
}
/**
* 设置单元格样式
*
* @param position
* ["body","title"]
*/
private static CellStyle setCellStyle(Workbook workBook, String position) {
CellStyle cellStyle = workBook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 设置单元格字体
Font headerFont = workBook.createFont(); // 字体
if (POSITION_TITLE.equals(position)) {
headerFont.setFontHeightInPoints((short) 12);
} else {
headerFont.setFontHeightInPoints((short) 10);
}
headerFont.setFontName("宋体");
if (POSITION_TITLE.equals(position))
headerFont.setBoldweight((short) 10);
cellStyle.setFont(headerFont);
cellStyle.setWrapText(true);
cellStyle.setFillBackgroundColor(HSSFCellStyle.THICK_FORWARD_DIAG);
// 设置单元格边框及颜色
cellStyle.setBorderBottom((short) 1);
cellStyle.setBorderLeft((short) 1);
cellStyle.setBorderRight((short) 1);
cellStyle.setBorderTop((short) 1);
cellStyle.setWrapText(true);
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); // 设置边框颜色
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
return cellStyle;
}
}
导出对象实体类,实际项目中这个类可以有更多的属性
import java.io.Serializable;
import java.util.Date;
import com.monkey.poi.util.ExcelResources;
public class ProductCard implements Serializable {
private static final long serialVersionUID = -70571478472359104L;
private Integer id;
private String code;
private String codePwd;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
@ExcelResources(title="卡号", order=1)
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
@ExcelResources(title="密码", order=2)
public String getCodePwd() {
return codePwd;
}
public void setCodePwd(String codePwd) {
this.codePwd = codePwd;
}
}
junit测试
@Test
public void test002() {
List<ProductCard> pcList = new ArrayList<ProductCard>();
for (int i = 0; i < 300000; i++) {
ProductCard p = new ProductCard();
p.setCode(DateUtils.formatDate(DateUtils.DATE_PATTERN_PLAIN,new Date()));
p.setCodePwd("123456" + i);
pcList.add(p);
}
ExcelUtil.getInstance().exportObj2Excel("d:/product-1.xls", pcList,
ProductCard.class, "我的sheet", 10000);
}
-
Excel样式部分我是按照我们项目中的所用的格式导出,各位在自己项目中可以自己定义
-
Excel可以分开单独显示不同的