Excel导出工具类.--POI
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.anhry.app.util.bean.SystemBean;
import com.anhry.app.util.excel.annoation.Excel;
/**
* Excel导出工具类.
*/
public class ExportExcel<T> {
public static final Logger LOG = LoggerFactory.getLogger(ExportExcel.class);
/**
*
* @param title Sheet名字
* @param pojoClass Excel对象Class
* @param dataSet Excel对象数据List
* @param out 输出流
*/
public void exportExcel(String title, Class<T> pojoClass,
Collection<T> dataSet, OutputStream out) {
// 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出
exportExcelInUserModel(title, pojoClass, dataSet, out);
// 使用eventModel实现，可以一边读一边处理，效率较高，但是实现复杂，暂时未实现
}
private void exportExcelInUserModel(String title, Class<T> pojoClass,
Collection<T> dataSet, OutputStream out) {
try {
// 首先检查数据看是否是正确的
if (dataSet == null || dataSet.size() == 0) {
throw new Exception("导出数据为空！");
}
if (title == null || out == null || pojoClass == null) {
throw new Exception("传入参数不能为空！");
}
// 声明一个工作薄
Workbook workbook = new HSSFWorkbook();
// 生成一个表格
Sheet sheet = workbook.createSheet(title);
// 标题
List<String> exportFieldTitle = new ArrayList<String>();
List<Integer> exportFieldWidth = new ArrayList<Integer>();
// 拿到所有列名，以及导出的字段的get方法
List<Method> methodObj = new ArrayList<Method>();
Map<String, Method> convertMethod = new HashMap<String, Method>();
// 得到所有字段
Field fileds[] = pojoClass.getDeclaredFields();
// 遍历整个filed
for (int i = 0; i < fileds.length; i++) {
Field field = fileds[i];
Excel excel = field.getAnnotation(Excel.class);
// 如果设置了annottion
if (excel != null) {
// 添加到标题
exportFieldTitle.add(excel.exportName());
// 添加标题的列宽
exportFieldWidth.add(excel.exportFieldWidth());
// 添加到需要导出的字段的方法
String fieldname = field.getName();
// System.out.println(i+"列宽"+excel.exportName()+" "+excel.exportFieldWidth());
StringBuffer getMethodName = new StringBuffer("get");
getMethodName.append(fieldname.substring(0, 1)
.toUpperCase());
getMethodName.append(fieldname.substring(1));
Method getMethod = pojoClass.getMethod(getMethodName
.toString(), new Class[] {});
methodObj.add(getMethod);
if (excel.exportConvert() == true) {
StringBuffer getConvertMethodName = new StringBuffer(
"get");
getConvertMethodName.append(fieldname.substring(0, 1)
.toUpperCase());
getConvertMethodName.append(fieldname.substring(1));
getConvertMethodName.append("Convert");
Method getConvertMethod = pojoClass
.getMethod(getConvertMethodName.toString(),
new Class[] {});
convertMethod.put(getMethodName.toString(),
getConvertMethod);
}
}
}
int index = 0;
// 产生表格标题行
Row row = sheet.createRow(index);
for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) {
Cell cell = row.createCell(i);
// cell.setCellStyle(style);
RichTextString text = new HSSFRichTextString(exportFieldTitle
.get(i));
cell.setCellValue(text);
}
// 设置每行的列宽
for (int i = 0; i < exportFieldWidth.size(); i++) {
// 256=65280/255
sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i));
}
Iterator its = dataSet.iterator();
// 循环插入剩下的集合
while (its.hasNext()) {
// 从第二行开始写，第一行是标题
index++;
row = sheet.createRow(index);
Object t = its.next();
for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) {
Cell cell = row.createCell(k);
Method getMethod = methodObj.get(k);
Object value = null;
if (convertMethod.containsKey(getMethod.getName())) {
Method cm = convertMethod.get(getMethod.getName());
value = cm.invoke(t, new Object[] {});
} else {
value = getMethod.invoke(t, new Object[] {});
}
cell.setCellValue(value == null ? "" : value.toString());
}
}
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
*
* @param title Sheet名字
* @param pojoClass Excel对象Class
* @param dataSet Excel对象数据List
* @param out 输出流
*/
public HSSFWorkbook exportExcel(String title, Class<T> pojoClass,
Collection<T> dataSet) {
// 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出
return exportExcelInUserModel2File(title, pojoClass, dataSet, null);
}
/**
*
* @param title Sheet名字
* @param pojoClass Excel对象Class
* @param dataSet Excel对象数据List
* @param exportFields Excel对象选择要导出的字段
* @param out 输出流
*/
public HSSFWorkbook exportExcel(String title, Class<T> pojoClass,
Collection<T> dataSet,List<String> exportFields) {
// 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出
return exportExcelInUserModel2File(title, pojoClass, dataSet, exportFields);
}
HSSFWorkbook exportExcelInUserModel2File(String title, Class<T> pojoClass,
Collection<T> dataSet) {
return exportExcelInUserModel2File(title, pojoClass, dataSet, null);
}
private HSSFWorkbook exportExcelInUserModel2File(String title, Class<T> pojoClass,
Collection<T> dataSet, List<String> exportFields) {
// 声明一个工作薄
HSSFWorkbook workbook = null;
try {
// 声明一个工作薄
workbook = new HSSFWorkbook();
// 生成一个表格
Sheet sheet = workbook.createSheet(title);
// 标题
List<String> exportFieldTitle = new ArrayList<String>();
List<Integer> exportFieldWidth = new ArrayList<Integer>();
// 拿到所有列名，以及导出的字段的get方法
List<Method> methodObj = new ArrayList<Method>();
Map<String, Method> convertMethod = new HashMap<String, Method>();
Class superClazz = null;
Field fileds[] = new Field[0];
boolean flag = true;
while (flag) {
if(superClazz != null){
superClazz = superClazz.getSuperclass();
}else{
superClazz = pojoClass.getSuperclass();
}
if(superClazz.isInstance(Object.class)){
flag = false;
}else{
Field[] sf = superClazz.getDeclaredFields();
if(sf != null && sf.length >0){
for(int m = 0;m<sf.length;m++){
fileds = ArrayUtils.addAll(fileds, sf[m]);
}
}
}
}
// 得到所有字段
Field cfileds[] = pojoClass.getDeclaredFields();
if(cfileds != null && cfileds.length >0){
for(int n = 0;n<cfileds.length;n++){
fileds = ArrayUtils.addAll(fileds, cfileds[n]);
}
}
// 遍历整个filed
int exportFieldCount = 0; // 要导出字段的数量
if(exportFields != null && exportFields.size() > 0) {
exportFieldCount = exportFields.size() ;
}
for (int i = 0; i < fileds.length; i++) {
Field field = fileds[i];
Excel excel = field.getAnnotation(Excel.class);
// 如果设置了annottion
if (excel != null) {
if(exportFieldCount > 0) {
for(String eField: exportFields) {
if(eField.equals(excel.exportName())) {
addExportField(exportFieldTitle, exportFieldWidth, excel, field, methodObj, pojoClass, convertMethod);
exportFieldCount -=1;
LOG.debug("exportFieldCount --- > " + exportFieldCount);
break;
}
}
if(exportFieldCount <= 0) {
LOG.debug("Break Field Loop! ------------ !@!");
break;
}
} else {
addExportField(exportFieldTitle, exportFieldWidth, excel, field, methodObj, pojoClass, convertMethod);
}
}
}
int index = 0;
// 产生表格标题行
Row row = sheet.createRow(index);
row.setHeight((short)450);
CellStyle titleStyle = getTitleStyle(workbook);
for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) {
Cell cell = row.createCell(i);
// cell.setCellStyle(style);
RichTextString text = new HSSFRichTextString(exportFieldTitle
.get(i));
cell.setCellValue(text);
cell.setCellStyle(titleStyle);
}
// 设置每行的列宽
for (int i = 0; i < exportFieldWidth.size(); i++) {
// 256=65280/255
sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i));
}
Iterator its = dataSet.iterator();
// 循环插入剩下的集合
HSSFCellStyle oneStyle = getOneStyle(workbook);
HSSFCellStyle twoStyle = getTwoStyle(workbook);
while (its.hasNext()) {
// 从第二行开始写，第一行是标题
index++;
row = sheet.createRow(index);
row.setHeight((short)350);
Object t = its.next();
for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) {
Cell cell = row.createCell(k);
Method getMethod = methodObj.get(k);
Object value = null;
if (convertMethod.containsKey(getMethod.getName())) {
Method cm = convertMethod.get(getMethod.getName());
value = cm.invoke(t, new Object[] {});
} else {
value = getMethod.invoke(t, new Object[] {});
}
cell.setCellValue(value==null?"":value.toString());
if(index%2==0)
cell.setCellStyle(twoStyle);
else
cell.setCellStyle(oneStyle);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
private void addExportField(List<String> exportFieldTitle, List<Integer> exportFieldWidth, Excel excel,
Field field, List<Method> methodObj, Class<T> pojoClass, Map<String, Method> convertMethod) throws Exception{
// 添加到标题
exportFieldTitle.add(excel.exportName());
// 添加标题的列宽
exportFieldWidth.add(excel.exportFieldWidth());
// 添加到需要导出的字段的方法
String fieldname = field.getName();
// System.out.println(i+"列宽"+excel.exportName()+" "+excel.exportFieldWidth());
StringBuffer getMethodName = new StringBuffer("get");
getMethodName.append(fieldname.substring(0, 1)
.toUpperCase());
getMethodName.append(fieldname.substring(1));
Method getMethod = pojoClass.getMethod(getMethodName
.toString(), new Class[] {});
methodObj.add(getMethod);
if (excel.exportConvert() == true) {
//----------------------------------------------------------------
//update-begin--Author:Quainty Date:20130524 for：[8]excel导出时间问题
// 用get/setXxxxConvert方法名的话， 由于直接使用了数据库绑定的Entity对象，注入会有冲突
StringBuffer getConvertMethodName = new StringBuffer("convertGet");
getConvertMethodName.append(fieldname.substring(0, 1).toUpperCase());
getConvertMethodName.append(fieldname.substring(1));
//getConvertMethodName.append("Convert");
//update-end--Author:Quainty Date:20130524 for：[8]excel导出时间问题
//----------------------------------------------------------------
// System.out.println("convert: "+getConvertMethodName.toString());
Method getConvertMethod = pojoClass
.getMethod(getConvertMethodName.toString(),
new Class[] {});
convertMethod.put(getMethodName.toString(),
getConvertMethod);
}
}
/**
* 取得一个类添加了@Excel 注解的所有属性中 该注解中的exportName
* @param pojoClass
* @return
*/
public List<SystemBean> getExportFields(Class<T> pojoClass) {
// 标题
List<SystemBean> exportNames = new ArrayList<SystemBean>();
Class superClazz = null;
Field fileds[] = new Field[0];
boolean flag = true;
while (flag) {
if(superClazz != null){
superClazz = superClazz.getSuperclass();
}else{
superClazz = pojoClass.getSuperclass();
}
if(superClazz.isInstance(Object.class)){
flag = false;
}else{
Field[] sf = superClazz.getDeclaredFields();
if(sf != null && sf.length >0){
for(int m = 0;m<sf.length;m++){
fileds = ArrayUtils.addAll(fileds, sf[m]);
System.out.println(sf[m].getName());
}
}
}
}
// 得到所有字段
Field cfileds[] = pojoClass.getDeclaredFields();
if(cfileds != null && cfileds.length >0){
for(int n = 0;n<cfileds.length;n++){
fileds = ArrayUtils.addAll(fileds, cfileds[n]);
}
}
// 遍历整个filed
for (int i = 0; i < fileds.length; i++) {
Field field = fileds[i];
Excel excel = field.getAnnotation(Excel.class);
// 如果设置了annottion
if (excel != null) {
SystemBean sb = new SystemBean();
sb.setId(excel.exportName());
sb.setName(excel.exportName());
// 添加到标题
exportNames.add(sb);
}
}
return exportNames;
}
/**
* 导出excel的样式
* @param workbook
* @return
*/
public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook){
// 产生Excel表头
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); //设置边框样式
titleStyle.setBorderLeft((short)2); //左边框
titleStyle.setBorderRight((short)2); //右边框
titleStyle.setBorderTop((short)2); //左边框
titleStyle.setBorderBottom((short)2); //右边框
titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE); //顶边框
titleStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); //填充的背景颜色
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //填充图案
return titleStyle;
}
public static HSSFCellStyle getTwoStyle(HSSFWorkbook workbook){
// 产生Excel表头
// 产生Excel表头
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderLeft((short)1); //左边框
style.setBorderRight((short)1); //右边框
style.setBorderBottom((short)1);
style.setBorderTop((short)1);
style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); //填充的背景颜色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //填充图案
return style;
}
public static HSSFCellStyle getOneStyle(HSSFWorkbook workbook){
// 产生Excel表头
// 产生Excel表头
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderLeft((short)1); //左边框
style.setBorderRight((short)1); //右边框
style.setBorderBottom((short)1);
style.setBorderTop((short)1);
return style;
}
}
