Excel导出工具类.--POI
1import java.io.OutputStream; 2import java.lang.reflect.Field; 3import java.lang.reflect.Method; 4import java.util.ArrayList; 5import java.util.Collection; 6import java.util.HashMap; 7import java.util.Iterator; 8import java.util.List; 9import java.util.Map; 10 11import org.apache.commons.lang3.ArrayUtils; 12import org.apache.poi.hssf.usermodel.HSSFCellStyle; 13import org.apache.poi.hssf.usermodel.HSSFRichTextString; 14import org.apache.poi.hssf.usermodel.HSSFWorkbook; 15import org.apache.poi.hssf.util.HSSFColor; 16import org.apache.poi.ss.usermodel.Cell; 17import org.apache.poi.ss.usermodel.CellStyle; 18import org.apache.poi.ss.usermodel.RichTextString; 19import org.apache.poi.ss.usermodel.Row; 20import org.apache.poi.ss.usermodel.Sheet; 21import org.apache.poi.ss.usermodel.Workbook; 22import org.slf4j.Logger; 23import org.slf4j.LoggerFactory; 24 25import com.anhry.app.util.bean.SystemBean; 26import com.anhry.app.util.excel.annoation.Excel; 27 28 29/** 30 * Excel导出工具类. 31 */ 32public class ExportExcel<T> { 33 34 public static final Logger LOG = LoggerFactory.getLogger(ExportExcel.class); 35 /** 36 * 37 * @param title Sheet名字 38 * @param pojoClass Excel对象Class 39 * @param dataSet Excel对象数据List 40 * @param out 输出流 41 */ 42 public void exportExcel(String title, Class<T> pojoClass, 43 Collection<T> dataSet, OutputStream out) { 44 // 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出 45 exportExcelInUserModel(title, pojoClass, dataSet, out); 46 // 使用eventModel实现，可以一边读一边处理，效率较高，但是实现复杂，暂时未实现 47 } 48 49 private void exportExcelInUserModel(String title, Class<T> pojoClass, 50 Collection<T> dataSet, OutputStream out) { 51 try { 52 // 首先检查数据看是否是正确的 53 if (dataSet == null || dataSet.size() == 0) { 54 throw new Exception("导出数据为空！"); 55 } 56 if (title == null || out == null || pojoClass == null) { 57 throw new Exception("传入参数不能为空！"); 58 } 59 // 声明一个工作薄 60 Workbook workbook = new HSSFWorkbook(); 61 // 生成一个表格 62 Sheet sheet = workbook.createSheet(title); 63 64 // 标题 65 List<String> exportFieldTitle = new ArrayList<String>(); 66 List<Integer> exportFieldWidth = new ArrayList<Integer>(); 67 // 拿到所有列名，以及导出的字段的get方法 68 List<Method> methodObj = new ArrayList<Method>(); 69 Map<String, Method> convertMethod = new HashMap<String, Method>(); 70 // 得到所有字段 71 Field fileds[] = pojoClass.getDeclaredFields(); 72 // 遍历整个filed 73 for (int i = 0; i < fileds.length; i++) { 74 Field field = fileds[i]; 75 Excel excel = field.getAnnotation(Excel.class); 76 // 如果设置了annottion 77 if (excel != null) { 78 // 添加到标题 79 exportFieldTitle.add(excel.exportName()); 80 // 添加标题的列宽 81 exportFieldWidth.add(excel.exportFieldWidth()); 82 // 添加到需要导出的字段的方法 83 String fieldname = field.getName(); 84 // System.out.println(i+"列宽"+excel.exportName()+" "+excel.exportFieldWidth()); 85 StringBuffer getMethodName = new StringBuffer("get"); 86 getMethodName.append(fieldname.substring(0, 1) 87 .toUpperCase()); 88 getMethodName.append(fieldname.substring(1)); 89 90 Method getMethod = pojoClass.getMethod(getMethodName 91 .toString(), new Class[] {}); 92 93 methodObj.add(getMethod); 94 if (excel.exportConvert() == true) { 95 StringBuffer getConvertMethodName = new StringBuffer( 96 "get"); 97 getConvertMethodName.append(fieldname.substring(0, 1) 98 .toUpperCase()); 99 getConvertMethodName.append(fieldname.substring(1)); 100 getConvertMethodName.append("Convert"); 101 Method getConvertMethod = pojoClass 102 .getMethod(getConvertMethodName.toString(), 103 new Class[] {}); 104 convertMethod.put(getMethodName.toString(), 105 getConvertMethod); 106 } 107 } 108 } 109 int index = 0; 110 // 产生表格标题行 111 Row row = sheet.createRow(index); 112 for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) { 113 Cell cell = row.createCell(i); 114 // cell.setCellStyle(style); 115 RichTextString text = new HSSFRichTextString(exportFieldTitle 116 .get(i)); 117 cell.setCellValue(text); 118 } 119 120 // 设置每行的列宽 121 for (int i = 0; i < exportFieldWidth.size(); i++) { 122 // 256=65280/255 123 sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i)); 124 } 125 Iterator its = dataSet.iterator(); 126 // 循环插入剩下的集合 127 while (its.hasNext()) { 128 // 从第二行开始写，第一行是标题 129 index++; 130 row = sheet.createRow(index); 131 Object t = its.next(); 132 for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) { 133 Cell cell = row.createCell(k); 134 Method getMethod = methodObj.get(k); 135 Object value = null; 136 if (convertMethod.containsKey(getMethod.getName())) { 137 Method cm = convertMethod.get(getMethod.getName()); 138 value = cm.invoke(t, new Object[] {}); 139 } else { 140 value = getMethod.invoke(t, new Object[] {}); 141 } 142 cell.setCellValue(value == null ? "" : value.toString()); 143 } 144 } 145 146 workbook.write(out); 147 } catch (Exception e) { 148 e.printStackTrace(); 149 } 150 } 151 152 153 /** 154 * 155 * @param title Sheet名字 156 * @param pojoClass Excel对象Class 157 * @param dataSet Excel对象数据List 158 * @param out 输出流 159 */ 160 public HSSFWorkbook exportExcel(String title, Class<T> pojoClass, 161 Collection<T> dataSet) { 162 // 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出 163 return exportExcelInUserModel2File(title, pojoClass, dataSet, null); 164 } 165 /** 166 * 167 * @param title Sheet名字 168 * @param pojoClass Excel对象Class 169 * @param dataSet Excel对象数据List 170 * @param exportFields Excel对象选择要导出的字段 171 * @param out 输出流 172 */ 173 public HSSFWorkbook exportExcel(String title, Class<T> pojoClass, 174 Collection<T> dataSet,List<String> exportFields) { 175 // 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出 176 return exportExcelInUserModel2File(title, pojoClass, dataSet, exportFields); 177 } 178 HSSFWorkbook exportExcelInUserModel2File(String title, Class<T> pojoClass, 179 Collection<T> dataSet) { 180 return exportExcelInUserModel2File(title, pojoClass, dataSet, null); 181 } 182 private HSSFWorkbook exportExcelInUserModel2File(String title, Class<T> pojoClass, 183 Collection<T> dataSet, List<String> exportFields) { 184 // 声明一个工作薄 185 HSSFWorkbook workbook = null; 186 try { 187 // 声明一个工作薄 188 workbook = new HSSFWorkbook(); 189 // 生成一个表格 190 Sheet sheet = workbook.createSheet(title); 191 192 // 标题 193 List<String> exportFieldTitle = new ArrayList<String>(); 194 List<Integer> exportFieldWidth = new ArrayList<Integer>(); 195 // 拿到所有列名，以及导出的字段的get方法 196 List<Method> methodObj = new ArrayList<Method>(); 197 Map<String, Method> convertMethod = new HashMap<String, Method>(); 198 Class superClazz = null; 199 Field fileds[] = new Field[0]; 200 boolean flag = true; 201 while (flag) { 202 if(superClazz != null){ 203 superClazz = superClazz.getSuperclass(); 204 }else{ 205 superClazz = pojoClass.getSuperclass(); 206 } 207 if(superClazz.isInstance(Object.class)){ 208 flag = false; 209 }else{ 210 Field[] sf = superClazz.getDeclaredFields(); 211 if(sf != null && sf.length >0){ 212 for(int m = 0;m<sf.length;m++){ 213 fileds = ArrayUtils.addAll(fileds, sf[m]); 214 } 215 } 216 } 217 218 } 219 // 得到所有字段 220 Field cfileds[] = pojoClass.getDeclaredFields(); 221 if(cfileds != null && cfileds.length >0){ 222 for(int n = 0;n<cfileds.length;n++){ 223 fileds = ArrayUtils.addAll(fileds, cfileds[n]); 224 } 225 } 226 // 遍历整个filed 227 int exportFieldCount = 0; // 要导出字段的数量 228 if(exportFields != null && exportFields.size() > 0) { 229 exportFieldCount = exportFields.size() ; 230 } 231 for (int i = 0; i < fileds.length; i++) { 232 Field field = fileds[i]; 233 Excel excel = field.getAnnotation(Excel.class); 234 // 如果设置了annottion 235 if (excel != null) { 236 if(exportFieldCount > 0) { 237 for(String eField: exportFields) { 238 if(eField.equals(excel.exportName())) { 239 addExportField(exportFieldTitle, exportFieldWidth, excel, field, methodObj, pojoClass, convertMethod); 240 exportFieldCount -=1; 241 LOG.debug("exportFieldCount --- > " + exportFieldCount); 242 break; 243 } 244 } 245 if(exportFieldCount <= 0) { 246 LOG.debug("Break Field Loop! ------------ !@!"); 247 break; 248 } 249 } else { 250 addExportField(exportFieldTitle, exportFieldWidth, excel, field, methodObj, pojoClass, convertMethod); 251 } 252 253 } 254 } 255 int index = 0; 256 // 产生表格标题行 257 Row row = sheet.createRow(index); 258 row.setHeight((short)450); 259 CellStyle titleStyle = getTitleStyle(workbook); 260 for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) { 261 Cell cell = row.createCell(i); 262 // cell.setCellStyle(style); 263 RichTextString text = new HSSFRichTextString(exportFieldTitle 264 .get(i)); 265 cell.setCellValue(text); 266 cell.setCellStyle(titleStyle); 267 } 268 269 // 设置每行的列宽 270 for (int i = 0; i < exportFieldWidth.size(); i++) { 271 // 256=65280/255 272 sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i)); 273 } 274 Iterator its = dataSet.iterator(); 275 // 循环插入剩下的集合 276 277 HSSFCellStyle oneStyle = getOneStyle(workbook); 278 HSSFCellStyle twoStyle = getTwoStyle(workbook); 279 while (its.hasNext()) { 280 // 从第二行开始写，第一行是标题 281 index++; 282 row = sheet.createRow(index); 283 row.setHeight((short)350); 284 Object t = its.next(); 285 for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) { 286 Cell cell = row.createCell(k); 287 Method getMethod = methodObj.get(k); 288 Object value = null; 289 if (convertMethod.containsKey(getMethod.getName())) { 290 Method cm = convertMethod.get(getMethod.getName()); 291 value = cm.invoke(t, new Object[] {}); 292 } else { 293 value = getMethod.invoke(t, new Object[] {}); 294 } 295 cell.setCellValue(value==null?"":value.toString()); 296 297 if(index%2==0) 298 cell.setCellStyle(twoStyle); 299 else 300 cell.setCellStyle(oneStyle); 301 } 302 } 303 } catch (Exception e) { 304 e.printStackTrace(); 305 } 306 return workbook; 307 } 308 309 private void addExportField(List<String> exportFieldTitle, List<Integer> exportFieldWidth, Excel excel, 310 Field field, List<Method> methodObj, Class<T> pojoClass, Map<String, Method> convertMethod) throws Exception{ 311 // 添加到标题 312 exportFieldTitle.add(excel.exportName()); 313 // 添加标题的列宽 314 exportFieldWidth.add(excel.exportFieldWidth()); 315 // 添加到需要导出的字段的方法 316 String fieldname = field.getName(); 317 // System.out.println(i+"列宽"+excel.exportName()+" "+excel.exportFieldWidth()); 318 StringBuffer getMethodName = new StringBuffer("get"); 319 getMethodName.append(fieldname.substring(0, 1) 320 .toUpperCase()); 321 getMethodName.append(fieldname.substring(1)); 322 323 Method getMethod = pojoClass.getMethod(getMethodName 324 .toString(), new Class[] {}); 325 326 methodObj.add(getMethod); 327 if (excel.exportConvert() == true) { 328 //---------------------------------------------------------------- 329 //update-begin--Author:Quainty Date:20130524 for：[8]excel导出时间问题 330 // 用get/setXxxxConvert方法名的话， 由于直接使用了数据库绑定的Entity对象，注入会有冲突 331 StringBuffer getConvertMethodName = new StringBuffer("convertGet"); 332 getConvertMethodName.append(fieldname.substring(0, 1).toUpperCase()); 333 getConvertMethodName.append(fieldname.substring(1)); 334 //getConvertMethodName.append("Convert"); 335 //update-end--Author:Quainty Date:20130524 for：[8]excel导出时间问题 336 //---------------------------------------------------------------- 337 // System.out.println("convert: "+getConvertMethodName.toString()); 338 Method getConvertMethod = pojoClass 339 .getMethod(getConvertMethodName.toString(), 340 new Class[] {}); 341 convertMethod.put(getMethodName.toString(), 342 getConvertMethod); 343 } 344 } 345 346 /** 347 * 取得一个类添加了@Excel 注解的所有属性中 该注解中的exportName 348 * @param pojoClass 349 * @return 350 */ 351 public List<SystemBean> getExportFields(Class<T> pojoClass) { 352 // 标题 353 List<SystemBean> exportNames = new ArrayList<SystemBean>(); 354 Class superClazz = null; 355 Field fileds[] = new Field[0]; 356 boolean flag = true; 357 while (flag) { 358 if(superClazz != null){ 359 superClazz = superClazz.getSuperclass(); 360 }else{ 361 superClazz = pojoClass.getSuperclass(); 362 } 363 if(superClazz.isInstance(Object.class)){ 364 flag = false; 365 }else{ 366 Field[] sf = superClazz.getDeclaredFields(); 367 if(sf != null && sf.length >0){ 368 for(int m = 0;m<sf.length;m++){ 369 fileds = ArrayUtils.addAll(fileds, sf[m]); 370 System.out.println(sf[m].getName()); 371 } 372 } 373 } 374 375 } 376 // 得到所有字段 377 Field cfileds[] = pojoClass.getDeclaredFields(); 378 if(cfileds != null && cfileds.length >0){ 379 for(int n = 0;n<cfileds.length;n++){ 380 fileds = ArrayUtils.addAll(fileds, cfileds[n]); 381 } 382 } 383 // 遍历整个filed 384 for (int i = 0; i < fileds.length; i++) { 385 Field field = fileds[i]; 386 Excel excel = field.getAnnotation(Excel.class); 387 // 如果设置了annottion 388 if (excel != null) { 389 SystemBean sb = new SystemBean(); 390 sb.setId(excel.exportName()); 391 sb.setName(excel.exportName()); 392 // 添加到标题 393 exportNames.add(sb); 394 } 395 } 396 return exportNames; 397 } 398 399 400 /** 401 * 导出excel的样式 402 * @param workbook 403 * @return 404 */ 405 public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook){ 406 // 产生Excel表头 407 HSSFCellStyle titleStyle = workbook.createCellStyle(); 408 titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); //设置边框样式 409 titleStyle.setBorderLeft((short)2); //左边框 410 titleStyle.setBorderRight((short)2); //右边框 411 titleStyle.setBorderTop((short)2); //左边框 412 titleStyle.setBorderBottom((short)2); //右边框 413 titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE); //顶边框 414 titleStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); //填充的背景颜色 415 titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 416 417 titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //填充图案 418 419 return titleStyle; 420 } 421 422 public static HSSFCellStyle getTwoStyle(HSSFWorkbook workbook){ 423 // 产生Excel表头 424 // 产生Excel表头 425 HSSFCellStyle style = workbook.createCellStyle(); 426 style.setBorderLeft((short)1); //左边框 427 style.setBorderRight((short)1); //右边框 428 style.setBorderBottom((short)1); 429 style.setBorderTop((short)1); 430 style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); //填充的背景颜色 431 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //填充图案 432 return style; 433 } 434 435 public static HSSFCellStyle getOneStyle(HSSFWorkbook workbook){ 436 // 产生Excel表头 437 // 产生Excel表头 438 HSSFCellStyle style = workbook.createCellStyle(); 439 style.setBorderLeft((short)1); //左边框 440 style.setBorderRight((short)1); //右边框 441 style.setBorderBottom((short)1); 442 style.setBorderTop((short)1); 443 return style; 444 } 445 446}