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; } }