java:POI导出excel
POI导出excel示例
POI是一个开源项目，专用于java平台上操作MS OFFICE，企业应用开发中可用它方便导出Excel.
下面是使用示例：
1、maven中先添加依赖项
1 <dependency> 2 <groupId>org.apache.poi</groupId> 3 <artifactId>poi</artifactId> 4 <version>3.11</version> 5 </dependency>
2、最基本的导出示例
a) 先定义一个基本的类AwbData
1 package com.cnblogs.yjmyzz.test.domain; 2 3 public class AwbDto { 4 5 public AwbDto() { 6 super(); 7 8 } 9 10 public AwbDto(String awbNumber, String agent) { 11 super(); 12 this.awbNumber = awbNumber; 13 this.agent = agent; 14 } 15 16 /** 17 * 运单号 18 */ 19 private String awbNumber; 20 21 /** 22 * 代理人 23 */ 24 private String agent; 25 26 public String getAwbNumber() { 27 return awbNumber; 28 } 29 30 public void setAwbNumber(String awbNumber) { 31 this.awbNumber = awbNumber; 32 } 33 34 public String getAgent() { 35 return agent; 36 } 37 38 public void setAgent(String agent) { 39 this.agent = agent; 40 } 41 }
View Code
b) 伪造点数据
1 private List<AwbDto> getData1() { 2 List<AwbDto> data = new ArrayList<AwbDto>(); 3 for (int i = 0; i < 1000; i++) { 4 data.add(new AwbDto("112-" + FileUtil.leftPad(i + "", 8, \'0\'), "张三")); 5 } 6 return data; 7 } 8 9 private List<AwbDto> getData2() { 10 List<AwbDto> data = new ArrayList<AwbDto>(); 11 for (int i = 0; i < 1000; i++) { 12 data.add(new AwbDto("999-" + FileUtil.leftPad(i + "", 8, \'0\'), "李四")); 13 } 14 return data; 15 }
View Code
上面都是准备工作，下面才是重点：
1 @Test 2 public void testExcelExport() throws Exception { 3 4 // 创建excel 5 HSSFWorkbook wb = new HSSFWorkbook(); 6 7 // 创建sheet 8 HSSFSheet sheet = wb.createSheet("运单数据"); 9 10 // 创建一行 11 HSSFRow rowTitle = sheet.createRow(0); 12 13 // 创建标题栏样式 14 HSSFCellStyle styleTitle = wb.createCellStyle(); 15 styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中 16 HSSFFont fontTitle = wb.createFont(); 17 // 宋体加粗 18 fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 19 fontTitle.setFontName("宋体"); 20 fontTitle.setFontHeight((short) 200); 21 styleTitle.setFont(fontTitle); 22 23 // 在行上创建1列 24 HSSFCell cellTitle = rowTitle.createCell(0); 25 26 // 列标题及样式 27 cellTitle.setCellValue("运单号"); 28 cellTitle.setCellStyle(styleTitle); 29 30 // 在行上创建2列 31 cellTitle = rowTitle.createCell(1); 32 cellTitle.setCellValue("代理人"); 33 cellTitle.setCellStyle(styleTitle); 34 35 HSSFCellStyle styleCenter = wb.createCellStyle(); 36 styleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中 37 38 // 取数据 39 List<AwbDto> data = getData1(); 40 41 for (int i = 0; i < data.size(); i++) { 42 43 AwbDto item = data.get(i); 44 HSSFRow row = sheet.createRow(i + 1); 45 46 HSSFCell cell = row.createCell(0); 47 cell.setCellValue(item.getAwbNumber()); 48 cell.setCellStyle(styleCenter); 49 50 cell = row.createCell(1); 51 cell.setCellValue(item.getAgent()); 52 cell.setCellStyle(styleCenter); 53 } 54 55 FileOutputStream fout = new FileOutputStream("r:/awb.xls"); 56 wb.write(fout); 57 fout.close(); 58 wb.close(); 59 60 System.out.println("导出完成！"); 61 }
View Code
导出后，大致是这个样子：
3、通用的Excel导出类
对于格式不太复杂的常规excel，如果每次都要写上面这一堆代码，当然有点2，已经有无私的高逼格程序猿在开源中国上奉献了自己的劳动成果，借来用一下（再次向作者表示感谢），不过这份代码年头略久，有些方法已经被现在的版本标识为过时，略微改进了一下下，贴在这里：
1 package com.cnblogs.yjmyzz.utils; 2 3 import java.io.ByteArrayOutputStream; 4 import java.io.IOException; 5 import java.text.SimpleDateFormat; 6 import java.util.Date; 7 import java.util.LinkedHashMap; 8 import java.util.List; 9 import java.util.Set; 10 import java.util.Map.Entry; 11 import org.apache.poi.hssf.usermodel.HSSFCell; 12 import org.apache.poi.hssf.usermodel.HSSFRow; 13 import org.apache.poi.hssf.usermodel.HSSFSheet; 14 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 15 import org.apache.poi.ss.usermodel.CellStyle; 16 import org.apache.poi.ss.usermodel.Font; 17 import org.apache.poi.ss.usermodel.IndexedColors; 18 import org.apache.poi.ss.util.CellRangeAddress; 19 20 public class ExcelUtil { 21 private static HSSFWorkbook wb; 22 23 private static CellStyle titleStyle; // 标题行样式 24 private static Font titleFont; // 标题行字体 25 private static CellStyle dateStyle; // 日期行样式 26 private static Font dateFont; // 日期行字体 27 private static CellStyle headStyle; // 表头行样式 28 private static Font headFont; // 表头行字体 29 private static CellStyle contentStyle; // 内容行样式 30 private static Font contentFont; // 内容行字体 31 32 /** 33 * 导出文件 34 * 35 * @param setInfo 36 * @param outputExcelFileName 37 * @return 38 * @throws IOException 39 */ 40 public static boolean export2File(ExcelExportData setInfo, 41 String outputExcelFileName) throws Exception { 42 return FileUtil.write(outputExcelFileName, export2ByteArray(setInfo), 43 true, true); 44 } 45 46 /** 47 * 导出到byte数组 48 * 49 * @param setInfo 50 * @return 51 * @throws Exception 52 */ 53 public static byte[] export2ByteArray(ExcelExportData setInfo) 54 throws Exception { 55 return export2Stream(setInfo).toByteArray(); 56 } 57 58 /** 59 * 导出到流 60 * 61 * @param setInfo 62 * @return 63 * @throws Exception 64 */ 65 public static ByteArrayOutputStream export2Stream(ExcelExportData setInfo) 66 throws Exception { 67 init(); 68 69 ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); 70 71 Set<Entry<String, List<?>>> set = setInfo.getDataMap().entrySet(); 72 String[] sheetNames = new String[setInfo.getDataMap().size()]; 73 int sheetNameNum = 0; 74 for (Entry<String, List<?>> entry : set) { 75 sheetNames[sheetNameNum] = entry.getKey(); 76 sheetNameNum++; 77 } 78 HSSFSheet[] sheets = getSheets(setInfo.getDataMap().size(), sheetNames); 79 int sheetNum = 0; 80 for (Entry<String, List<?>> entry : set) { 81 // Sheet 82 List<?> objs = entry.getValue(); 83 84 // 标题行 85 createTableTitleRow(setInfo, sheets, sheetNum); 86 87 // 日期行 88 createTableDateRow(setInfo, sheets, sheetNum); 89 90 // 表头 91 creatTableHeadRow(setInfo, sheets, sheetNum); 92 93 // 表体 94 String[] fieldNames = setInfo.getFieldNames().get(sheetNum); 95 96 int rowNum = 3; 97 for (Object obj : objs) { 98 HSSFRow contentRow = sheets[sheetNum].createRow(rowNum); 99 contentRow.setHeight((short) 300); 100 HSSFCell[] cells = getCells(contentRow, setInfo.getFieldNames() 101 .get(sheetNum).length); 102 int cellNum = 1; // 去掉一列序号，因此从1开始 103 if (fieldNames != null) { 104 for (int num = 0; num < fieldNames.length; num++) { 105 106 Object value = ReflectionUtil.invokeGetterMethod(obj, 107 fieldNames[num]); 108 cells[cellNum].setCellValue(value == null ? "" : value 109 .toString()); 110 cellNum++; 111 } 112 } 113 rowNum++; 114 } 115 adjustColumnSize(sheets, sheetNum, fieldNames); // 自动调整列宽 116 sheetNum++; 117 } 118 wb.write(outputStream); 119 return outputStream; 120 } 121 122 /** 123 * @Description: 初始化 124 */ 125 private static void init() { 126 wb = new HSSFWorkbook(); 127 128 titleFont = wb.createFont(); 129 titleStyle = wb.createCellStyle(); 130 dateStyle = wb.createCellStyle(); 131 dateFont = wb.createFont(); 132 headStyle = wb.createCellStyle(); 133 headFont = wb.createFont(); 134 contentStyle = wb.createCellStyle(); 135 contentFont = wb.createFont(); 136 137 initTitleCellStyle(); 138 initTitleFont(); 139 initDateCellStyle(); 140 initDateFont(); 141 initHeadCellStyle(); 142 initHeadFont(); 143 initContentCellStyle(); 144 initContentFont(); 145 } 146 147 /** 148 * @Description: 自动调整列宽 149 */ 150 private static void adjustColumnSize(HSSFSheet[] sheets, int sheetNum, 151 String[] fieldNames) { 152 for (int i = 0; i < fieldNames.length + 1; i++) { 153 sheets[sheetNum].autoSizeColumn(i, true); 154 } 155 } 156 157 /** 158 * @Description: 创建标题行(需合并单元格) 159 */ 160 private static void createTableTitleRow(ExcelExportData setInfo, 161 HSSFSheet[] sheets, int sheetNum) { 162 CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, setInfo 163 .getFieldNames().get(sheetNum).length); 164 sheets[sheetNum].addMergedRegion(titleRange); 165 HSSFRow titleRow = sheets[sheetNum].createRow(0); 166 titleRow.setHeight((short) 800); 167 HSSFCell titleCell = titleRow.createCell(0); 168 titleCell.setCellStyle(titleStyle); 169 titleCell.setCellValue(setInfo.getTitles()[sheetNum]); 170 } 171 172 /** 173 * @Description: 创建日期行(需合并单元格) 174 */ 175 private static void createTableDateRow(ExcelExportData setInfo, 176 HSSFSheet[] sheets, int sheetNum) { 177 CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, setInfo 178 .getFieldNames().get(sheetNum).length); 179 sheets[sheetNum].addMergedRegion(dateRange); 180 HSSFRow dateRow = sheets[sheetNum].createRow(1); 181 dateRow.setHeight((short) 350); 182 HSSFCell dateCell = dateRow.createCell(0); 183 dateCell.setCellStyle(dateStyle); 184 // dateCell.setCellValue("导出时间：" + new 185 // SimpleDateFormat("yyyy-MM-dd HH:mm:ss") 186 // .format(new Date())); 187 dateCell.setCellValue(new SimpleDateFormat("yyyy-MM-dd") 188 .format(new Date())); 189 } 190 191 /** 192 * @Description: 创建表头行(需合并单元格) 193 */ 194 private static void creatTableHeadRow(ExcelExportData setInfo, 195 HSSFSheet[] sheets, int sheetNum) { 196 // 表头 197 HSSFRow headRow = sheets[sheetNum].createRow(2); 198 headRow.setHeight((short) 350); 199 // 序号列 200 HSSFCell snCell = headRow.createCell(0); 201 snCell.setCellStyle(headStyle); 202 snCell.setCellValue("序号"); 203 // 列头名称 204 for (int num = 1, len = setInfo.getColumnNames().get(sheetNum).length; num <= len; num++) { 205 HSSFCell headCell = headRow.createCell(num); 206 headCell.setCellStyle(headStyle); 207 headCell.setCellValue(setInfo.getColumnNames().get(sheetNum)[num - 1]); 208 } 209 } 210 211 /** 212 * @Description: 创建所有的Sheet 213 */ 214 private static HSSFSheet[] getSheets(int num, String[] names) { 215 HSSFSheet[] sheets = new HSSFSheet[num]; 216 for (int i = 0; i < num; i++) { 217 sheets[i] = wb.createSheet(names[i]); 218 } 219 return sheets; 220 } 221 222 /** 223 * @Description: 创建内容行的每一列(附加一列序号) 224 */ 225 private static HSSFCell[] getCells(HSSFRow contentRow, int num) { 226 HSSFCell[] cells = new HSSFCell[num + 1]; 227 228 for (int i = 0, len = cells.length; i < len; i++) { 229 cells[i] = contentRow.createCell(i); 230 cells[i].setCellStyle(contentStyle); 231 } 232 233 // 设置序号列值，因为出去标题行和日期行，所有-2 234 cells[0].setCellValue(contentRow.getRowNum() - 2); 235 236 return cells; 237 } 238 239 /** 240 * @Description: 初始化标题行样式 241 */ 242 private static void initTitleCellStyle() { 243 titleStyle.setAlignment(CellStyle.ALIGN_CENTER); 244 titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); 245 titleStyle.setFont(titleFont); 246 titleStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index); 247 } 248 249 /** 250 * @Description: 初始化日期行样式 251 */ 252 private static void initDateCellStyle() { 253 dateStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION); 254 dateStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); 255 dateStyle.setFont(dateFont); 256 dateStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index); 257 } 258 259 /** 260 * @Description: 初始化表头行样式 261 */ 262 private static void initHeadCellStyle() { 263 headStyle.setAlignment(CellStyle.ALIGN_CENTER); 264 headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); 265 headStyle.setFont(headFont); 266 headStyle.setFillBackgroundColor(IndexedColors.YELLOW.index); 267 headStyle.setBorderTop(CellStyle.BORDER_MEDIUM); 268 headStyle.setBorderBottom(CellStyle.BORDER_THIN); 269 headStyle.setBorderLeft(CellStyle.BORDER_THIN); 270 headStyle.setBorderRight(CellStyle.BORDER_THIN); 271 headStyle.setTopBorderColor(IndexedColors.BLUE.index); 272 headStyle.setBottomBorderColor(IndexedColors.BLUE.index); 273 headStyle.setLeftBorderColor(IndexedColors.BLUE.index); 274 headStyle.setRightBorderColor(IndexedColors.BLUE.index); 275 } 276 277 /** 278 * @Description: 初始化内容行样式 279 */ 280 private static void initContentCellStyle() { 281 contentStyle.setAlignment(CellStyle.ALIGN_CENTER); 282 contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); 283 contentStyle.setFont(contentFont); 284 contentStyle.setBorderTop(CellStyle.BORDER_THIN); 285 contentStyle.setBorderBottom(CellStyle.BORDER_THIN); 286 contentStyle.setBorderLeft(CellStyle.BORDER_THIN); 287 contentStyle.setBorderRight(CellStyle.BORDER_THIN); 288 contentStyle.setTopBorderColor(IndexedColors.BLUE.index); 289 contentStyle.setBottomBorderColor(IndexedColors.BLUE.index); 290 contentStyle.setLeftBorderColor(IndexedColors.BLUE.index); 291 contentStyle.setRightBorderColor(IndexedColors.BLUE.index); 292 contentStyle.setWrapText(true); // 字段换行 293 } 294 295 /** 296 * @Description: 初始化标题行字体 297 */ 298 private static void initTitleFont() { 299 titleFont.setFontName("华文楷体"); 300 titleFont.setFontHeightInPoints((short) 20); 301 titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); 302 titleFont.setCharSet(Font.DEFAULT_CHARSET); 303 titleFont.setColor(IndexedColors.BLUE_GREY.index); 304 } 305 306 /** 307 * @Description: 初始化日期行字体 308 */ 309 private static void initDateFont() { 310 dateFont.setFontName("隶书"); 311 dateFont.setFontHeightInPoints((short) 10); 312 dateFont.setBoldweight(Font.BOLDWEIGHT_BOLD); 313 dateFont.setCharSet(Font.DEFAULT_CHARSET); 314 dateFont.setColor(IndexedColors.BLUE_GREY.index); 315 } 316 317 /** 318 * @Description: 初始化表头行字体 319 */ 320 private static void initHeadFont() { 321 headFont.setFontName("宋体"); 322 headFont.setFontHeightInPoints((short) 10); 323 headFont.setBoldweight(Font.BOLDWEIGHT_BOLD); 324 headFont.setCharSet(Font.DEFAULT_CHARSET); 325 headFont.setColor(IndexedColors.BLUE_GREY.index); 326 } 327 328 /** 329 * @Description: 初始化内容行字体 330 */ 331 private static void initContentFont() { 332 contentFont.setFontName("宋体"); 333 contentFont.setFontHeightInPoints((short) 10); 334 contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL); 335 contentFont.setCharSet(Font.DEFAULT_CHARSET); 336 contentFont.setColor(IndexedColors.BLUE_GREY.index); 337 } 338 339 /** 340 * Excel导出数据类 341 * 342 * @author jimmy 343 * 344 */ 345 public static class ExcelExportData { 346 347 /** 348 * 导出数据 key:String 表示每个Sheet的名称 value:List<?> 表示每个Sheet里的所有数据行 349 */ 350 private LinkedHashMap<String, List<?>> dataMap; 351 352 /** 353 * 每个Sheet里的顶部大标题 354 */ 355 private String[] titles; 356 357 /** 358 * 单个sheet里的数据列标题 359 */ 360 private List<String[]> columnNames; 361 362 /** 363 * 单个sheet里每行数据的列对应的对象属性名称 364 */ 365 private List<String[]> fieldNames; 366 367 public List<String[]> getFieldNames() { 368 return fieldNames; 369 } 370 371 public void setFieldNames(List<String[]> fieldNames) { 372 this.fieldNames = fieldNames; 373 } 374 375 public String[] getTitles() { 376 return titles; 377 } 378 379 public void setTitles(String[] titles) { 380 this.titles = titles; 381 } 382 383 public List<String[]> getColumnNames() { 384 return columnNames; 385 } 386 387 public void setColumnNames(List<String[]> columnNames) { 388 this.columnNames = columnNames; 389 } 390 391 public LinkedHashMap<String, List<?>> getDataMap() { 392 return dataMap; 393 } 394 395 public void setDataMap(LinkedHashMap<String, List<?>> dataMap) { 396 this.dataMap = dataMap; 397 } 398 399 } 400 }
View Code
里面提供了3个方法，可用于导出到文件、byte数组、以及流，其中有一个反射工具类：
1 package com.cnblogs.yjmyzz.utils; 2 3 import java.lang.reflect.Field; 4 import java.lang.reflect.InvocationTargetException; 5 import java.lang.reflect.Method; 6 import java.lang.reflect.Modifier; 7 import java.lang.reflect.ParameterizedType; 8 import java.lang.reflect.Type; 9 import java.util.ArrayList; 10 import java.util.Collection; 11 import java.util.Date; 12 import java.util.List; 13 14 import org.apache.commons.beanutils.BeanUtils; 15 import org.apache.commons.beanutils.ConvertUtils; 16 import org.apache.commons.beanutils.PropertyUtils; 17 import org.apache.commons.beanutils.locale.converters.DateLocaleConverter; 18 import org.apache.commons.lang.StringUtils; 19 import org.apache.commons.logging.Log; 20 import org.apache.commons.logging.LogFactory; 21 import org.springframework.util.Assert; 22 23 /** 24 * 反射工具类. 25 * 26 * 提供访问私有变量,获取泛型类型Class, 提取集合中元素的属性, 转换字符串到对象等Util函数. 27 * 28 */ 29 30 public class ReflectionUtil { 31 32 private static Log logger = LogFactory.getLog(ReflectionUtil.class); 33 34 static { 35 DateLocaleConverter dc = new DateLocaleConverter(); 36 // dc.setPatterns(new String[] { "yyyy-MM-dd", "yyyy-MM-dd HH:mm:ss" }); 37 ConvertUtils.register(dc, Date.class); 38 } 39 40 /** 41 * 调用Getter方法. 42 */ 43 public static Object invokeGetterMethod(Object target, String propertyName) { 44 String getterMethodName = "get" + StringUtils.capitalize(propertyName); 45 return invokeMethod(target, getterMethodName, new Class[] {}, 46 new Object[] {}); 47 } 48 49 /** 50 * 调用Setter方法.使用value的Class来查找Setter方法. 51 */ 52 public static void invokeSetterMethod(Object target, String propertyName, 53 Object value) { 54 invokeSetterMethod(target, propertyName, value, null); 55 } 56 57 /** 58 * 调用Setter方法. 59 * 60 * @param propertyType 61 * 用于查找Setter方法,为空时使用value的Class替代. 62 */ 63 public static void invokeSetterMethod(Object target, String propertyName, 64 Object value, Class<?> propertyType) { 65 Class<?> type = propertyType != null ? propertyType : value.getClass(); 66 String setterMethodName = "set" + StringUtils.capitalize(propertyName); 67 invokeMethod(target, setterMethodName, new Class[] { type }, 68 new Object[] { value }); 69 } 70 71 /** 72 * 直接读取对象属性值, 无视private/protected修饰符, 不经过getter函数. 73 */ 74 public static Object getFieldValue(final Object object, 75 final String fieldName) { 76 Field field = getDeclaredField(object, fieldName); 77 78 if (field == null) { 79 throw new IllegalArgumentException("Could not find field [" 80 + fieldName + "] on target [" + object + "]"); 81 } 82 83 makeAccessible(field); 84 85 Object result = null; 86 try { 87 result = field.get(object); 88 } catch (IllegalAccessException e) { 89 logger.error("不可能抛出的异常{}" + e.getMessage()); 90 } 91 return result; 92 } 93 94 /** 95 * 直接设置对象属性值, 无视private/protected修饰符, 不经过setter函数. 96 */ 97 public static void setFieldValue(final Object object, 98 final String fieldName, final Object value) { 99 Field field = getDeclaredField(object, fieldName); 100 101 if (field == null) { 102 throw new IllegalArgumentException("Could not find field [" 103 + fieldName + "] on target [" + object + "]"); 104 } 105 106 makeAccessible(field); 107 108 try { 109 field.set(object, value); 110 } catch (IllegalAccessException e) { 111 logger.error("不可能抛出的异常:{}" + e.getMessage()); 112 } 113 } 114 115 /** 116 * 直接调用对象方法, 无视private/protected修饰符. 117 */ 118 public static Object invokeMethod(final Object object, 119 final String methodName, final Class<?>[] parameterTypes, 120 final Object[] parameters) { 121 Method method = getDeclaredMethod(object, methodName, parameterTypes); 122 if (method == null) { 123 throw new IllegalArgumentException("Could not find method [" 124 + methodName + "] parameterType " + parameterTypes 125 + " on target [" + object + "]"); 126 } 127 128 method.setAccessible(true); 129 130 try { 131 return method.invoke(object, parameters); 132 } catch (Exception e) { 133 throw convertReflectionExceptionToUnchecked(e); 134 } 135 } 136 137 /** 138 * 循环向上转型, 获取对象的DeclaredField. 139 * 140 * 如向上转型到Object仍无法找到, 返回null. 141 */ 142 protected static Field getDeclaredField(final Object object, 143 final String fieldName) { 144 Assert.notNull(object, "object不能为空"); 145 Assert.hasText(fieldName, "fieldName"); 146 for (Class<?> superClass = object.getClass(); superClass != Object.class; superClass = superClass 147 .getSuperclass()) { 148 try { 149 return superClass.getDeclaredField(fieldName); 150 } catch (NoSuchFieldException e) {// NOSONAR 151 // Field不在当前类定义,继续向上转型 152 } 153 } 154 return null; 155 } 156 157 /** 158 * 强行设置Field可访问. 159 */ 160 protected static void makeAccessible(final Field field) { 161 if (!Modifier.isPublic(field.getModifiers()) 162 || !Modifier.isPublic(field.getDeclaringClass().getModifiers())) { 163 field.setAccessible(true); 164 } 165 } 166 167 /** 168 * 循环向上转型, 获取对象的DeclaredMethod. 169 * 170 * 如向上转型到Object仍无法找到, 返回null. 171 */ 172 protected static Method getDeclaredMethod(Object object, String methodName, 173 Class<?>[] parameterTypes) { 174 Assert.notNull(object, "object不能为空"); 175 176 for (Class<?> superClass = object.getClass(); superClass != Object.class; superClass = superClass 177 .getSuperclass()) { 178 try { 179 return superClass.getDeclaredMethod(methodName, parameterTypes); 180 } catch (NoSuchMethodException e) {// NOSONAR 181 // Method不在当前类定义,继续向上转型 182 } 183 } 184 return null; 185 } 186 187 /** 188 * 通过反射, 获得Class定义中声明的父类的泛型参数的类型. 如无法找到, 返回Object.class. eg. public UserDao 189 * extends HibernateDao<User> 190 * 191 * @param clazz 192 * The class to introspect 193 * @return the first generic declaration, or Object.class if cannot be 194 * determined 195 */ 196 @SuppressWarnings("unchecked") 197 public static <T> Class<T> getSuperClassGenricType(final Class<?> clazz) { 198 return getSuperClassGenricType(clazz, 0); 199 } 200 201 /** 202 * 通过反射, 获得定义Class时声明的父类的泛型参数的类型. 如无法找到, 返回Object.class. 203 * 204 * 如public UserDao extends HibernateDao<User,Long> 205 * 206 * @param clazz 207 * clazz The class to introspect 208 * @param index 209 * the Index of the generic ddeclaration,start from 0. 210 * @return the index generic declaration, or Object.class if cannot be 211 * determined 212 */ 213 @SuppressWarnings("unchecked") 214 public static Class getSuperClassGenricType(final Class<?> clazz, 215 final int index) { 216 Type genType = clazz.getGenericSuperclass(); 217 218 if (!(genType instanceof ParameterizedType)) { 219 logger.warn(clazz.getSimpleName() 220 + "\'s superclass not ParameterizedType"); 221 return Object.class; 222 } 223 224 Type[] params = ((ParameterizedType) genType).getActualTypeArguments(); 225 226 if (index >= params.length || index < 0) { 227 logger.warn("Index: " + index + ", Size of " 228 + clazz.getSimpleName() + "\'s Parameterized Type: " 229 + params.length); 230 return Object.class; 231 } 232 if (!(params[index] instanceof Class)) { 233 logger.warn(clazz.getSimpleName() 234 + " not set the actual class on superclass generic parameter"); 235 return Object.class; 236 } 237 238 return (Class) params[index]; 239 } 240 241 /** 242 * 提取集合中的对象的属性(通过getter函数), 组合成List. 243 * 244 * @param collection 245 * 来源集合. 246 * @param propertyName 247 * 要提取的属性名. 248 */ 249 250 public static List convertElementPropertyToList( 251 final Collection collection, final String propertyName) { 252 List list = new ArrayList(); 253 254 try { 255 for (Object obj : collection) { 256 list.add(PropertyUtils.getProperty(obj, propertyName)); 257 } 258 } catch (Exception e) { 259 throw convertReflectionExceptionToUnchecked(e); 260 } 261 262 return list; 263 } 264 265 /** 266 * 提取集合中的对象的属性(通过getter函数), 组合成由分割符分隔的字符串. 267 * 268 * @param collection 269 * 来源集合. 270 * @param propertyName 271 * 要提取的属性名. 272 * @param separator 273 * 分隔符. 274 */ 275 @SuppressWarnings("unchecked") 276 public static String convertElementPropertyToString( 277 final Collection collection, final String propertyName, 278 final String separator) { 279 List list = convertElementPropertyToList(collection, propertyName); 280 return StringUtils.join(list, separator); 281 } 282 283 /** 284 * 转换字符串到相应类型. 285 * 286 * @param value 287 * 待转换的字符串 288 * @param toType 289 * 转换目标类型 290 */ 291 @SuppressWarnings("unchecked") 292 public static <T> T convertStringToObject(String value, Class<T> toType) { 293 try { 294 return (T) ConvertUtils.convert(value, toType); 295 } catch (Exception e) { 296 throw convertReflectionExceptionToUnchecked(e); 297 } 298 } 299 300 /** 301 * 将反射时的checked exception转换为unchecked exception. 302 */ 303 public static RuntimeException convertReflectionExceptionToUnchecked( 304 Exception e) { 305 return convertReflectionExceptionToUnchecked(null, e); 306 } 307 308 public static RuntimeException convertReflectionExceptionToUnchecked( 309 String desc, Exception e) { 310 desc = (desc == null) ? "Unexpected Checked Exception." : desc; 311 if (e instanceof IllegalAccessException 312 || e instanceof IllegalArgumentException 313 || e instanceof NoSuchMethodException) { 314 return new IllegalArgumentException(desc, e); 315 } else if (e instanceof InvocationTargetException) { 316 return new RuntimeException(desc, 317 ((InvocationTargetException) e).getTargetException()); 318 } else if (e instanceof RuntimeException) { 319 return (RuntimeException) e; 320 } 321 return new RuntimeException(desc, e); 322 } 323 324 public static final <T> T getNewInstance(Class<T> cls) { 325 try { 326 return cls.newInstance(); 327 } catch (InstantiationException e) { 328 e.printStackTrace(); 329 } catch (IllegalAccessException e) { 330 e.printStackTrace(); 331 } 332 return null; 333 } 334 335 /** 336 * 拷贝 source 指定的porperties 属性 到 dest中 337 * 338 * @return void 339 * @throws InvocationTargetException 340 * @throws IllegalAccessException 341 */ 342 public static void copyPorperties(Object dest, Object source, 343 String[] porperties) throws InvocationTargetException, 344 IllegalAccessException { 345 for (String por : porperties) { 346 Object srcObj = invokeGetterMethod(source, por); 347 logger.debug("属性名：" + por + "------------- 属性值：" + srcObj); 348 if (srcObj != null) { 349 try { 350 BeanUtils.setProperty(dest, por, srcObj); 351 } catch (IllegalArgumentException e) { 352 e.printStackTrace(); 353 } catch (IllegalAccessException e) { 354 throw e; 355 } catch (InvocationTargetException e) { 356 throw e; 357 } 358 } 359 } 360 } 361 362 /** 363 * 两者属性名一致时，拷贝source里的属性到dest里 364 * 365 * @return void 366 * @throws IllegalAccessException 367 * @throws InvocationTargetException 368 */ 369 370 public static void copyPorperties(Object dest, Object source) 371 throws IllegalAccessException, InvocationTargetException { 372 Class<? extends Object> srcCla = source.getClass(); 373 Field[] fsF = srcCla.getDeclaredFields(); 374 375 for (Field s : fsF) { 376 String name = s.getName(); 377 Object srcObj = invokeGetterMethod(source, name); 378 try { 379 BeanUtils.setProperty(dest, name, srcObj); 380 } catch (IllegalArgumentException e) { 381 e.printStackTrace(); 382 } catch (IllegalAccessException e) { 383 throw e; 384 } catch (InvocationTargetException e) { 385 throw e; 386 } 387 } 388 // BeanUtils.copyProperties(dest, orig); 389 } 390 391 public static void main(String[] args) throws InvocationTargetException, 392 IllegalAccessException { 393 /* 394 * Document document = new Document(); document.setId(2); 395 * document.setCreateDate(new Date()); DocumentVo dcoVo = new 396 * DocumentVo(); ReflectionUtils.copyPorperties(dcoVo, document,new 397 * String[]{"id","businessName","createDate","applyName","docTitle", 398 * "transactStatus"}); System.out.println(dcoVo.getId()); 399 */ 400 } 401 }
View Code
此外，导出到文件时，还用到了一个读写文件的工具类：
1 package com.cnblogs.yjmyzz.utils; 2 3 import java.io.*; 4 import java.util.*; 5 import java.util.concurrent.*; 6 7 /** 8 * 文件处理辅助类 9 * 10 * @author yjmyzz@126.com 11 * @version 0.2 12 * @since 2014-11-17 13 * 14 */ 15 public class FileUtil { 16 17 /** 18 * 当前目录路径 19 */ 20 public static String currentWorkDir = System.getProperty("user.dir") + "\\"; 21 22 /** 23 * 左填充 24 * 25 * @param str 26 * @param length 27 * @param ch 28 * @return 29 */ 30 public static String leftPad(String str, int length, char ch) { 31 if (str.length() >= length) { 32 return str; 33 } 34 char[] chs = new char[length]; 35 Arrays.fill(chs, ch); 36 char[] src = str.toCharArray(); 37 System.arraycopy(src, 0, chs, length - src.length, src.length); 38 return new String(chs); 39 40 } 41 42 /** 43 * 删除文件 44 * 45 * @param fileName 46 * 待删除的完整文件名 47 * @return 48 */ 49 public static boolean delete(String fileName) { 50 boolean result = false; 51 File f = new File(fileName); 52 if (f.exists()) { 53 result = f.delete(); 54 55 } else { 56 result = true; 57 } 58 return result; 59 } 60 61 /*** 62 * 递归获取指定目录下的所有的文件（不包括文件夹） 63 * 64 * @param obj 65 * @return 66 */ 67 public static ArrayList<File> getAllFiles(String dirPath) { 68 File dir = new File(dirPath); 69 70 ArrayList<File> files = new ArrayList<File>(); 71 72 if (dir.isDirectory()) { 73 File[] fileArr = dir.listFiles(); 74 for (int i = 0; i < fileArr.length; i++) { 75 File f = fileArr[i]; 76 if (f.isFile()) { 77 files.add(f); 78 } else { 79 files.addAll(getAllFiles(f.getPath())); 80 } 81 } 82 } 83 return files; 84 } 85 86 /** 87 * 获取指定目录下的所有文件(不包括子文件夹) 88 * 89 * @param dirPath 90 * @return 91 */ 92 public static ArrayList<File> getDirFiles(String dirPath) { 93 File path = new File(dirPath); 94 File[] fileArr = path.listFiles(); 95 ArrayList<File> files = new ArrayList<File>(); 96 97 for (File f : fileArr) { 98 if (f.isFile()) { 99 files.add(f); 100 } 101 } 102 return files; 103 } 104 105 /** 106 * 获取指定目录下特定文件后缀名的文件列表(不包括子文件夹) 107 * 108 * @param dirPath 109 * 目录路径 110 * @param suffix 111 * 文件后缀 112 * @return 113 */ 114 public static ArrayList<File> getDirFiles(String dirPath, 115 final String suffix) { 116 File path = new File(dirPath); 117 File[] fileArr = path.listFiles(new FilenameFilter() { 118 public boolean accept(File dir, String name) { 119 String lowerName = name.toLowerCase(); 120 String lowerSuffix = suffix.toLowerCase(); 121 if (lowerName.endsWith(lowerSuffix)) { 122 return true; 123 } 124 return false; 125 } 126 127 }); 128 ArrayList<File> files = new ArrayList<File>(); 129 130 for (File f : fileArr) { 131 if (f.isFile()) { 132 files.add(f); 133 } 134 } 135 return files; 136 } 137 138 /** 139 * 读取文件内容 140 * 141 * @param fileName 142 * 待读取的完整文件名 143 * @return 文件内容 144 * @throws IOException 145 */ 146 public static String read(String fileName) throws IOException { 147 File f = new File(fileName); 148 FileInputStream fs = new FileInputStream(f); 149 String result = null; 150 byte[] b = new byte[fs.available()]; 151 fs.read(b); 152 fs.close(); 153 result = new String(b); 154 return result; 155 } 156 157 /** 158 * 写文件 159 * 160 * @param fileName 161 * 目标文件名 162 * @param fileContent 163 * 写入的内容 164 * @return 165 * @throws IOException 166 */ 167 public static boolean write(String fileName, String fileContent) 168 throws IOException { 169 return write(fileName, fileContent, true, true); 170 } 171 172 /** 173 * 写文件 174 * 175 * @param fileName 176 * 完整文件名(类似：/usr/a/b/c/d.txt) 177 * @param fileContent 178 * 文件内容 179 * @param autoCreateDir 180 * 目录不存在时，是否自动创建(多级)目录 181 * @param autoOverWrite 182 * 目标文件存在时，是否自动覆盖 183 * @return 184 * @throws IOException 185 */ 186 public static boolean write(String fileName, String fileContent, 187 boolean autoCreateDir, boolean autoOverwrite) throws IOException { 188 return write(fileName, fileContent.getBytes(), autoCreateDir, 189 autoOverwrite); 190 } 191 192 /** 193 * 写文件 194 * 195 * @param fileName 196 * 完整文件名(类似：/usr/a/b/c/d.txt) 197 * @param contentBytes 198 * 文件内容的字节数组 199 * @param autoCreateDir 200 * 目录不存在时，是否自动创建(多级)目录 201 * @param autoOverWrite 202 * 目标文件存在时，是否自动覆盖 203 * @return 204 * @throws IOException 205 */ 206 public static boolean write(String fileName, byte[] contentBytes, 207 boolean autoCreateDir, boolean autoOverwrite) throws IOException { 208 boolean result = false; 209 if (autoCreateDir) { 210 createDirs(fileName); 211 } 212 if (autoOverwrite) { 213 delete(fileName); 214 } 215 File f = new File(fileName); 216 FileOutputStream fs = new FileOutputStream(f); 217 fs.write(contentBytes); 218 fs.flush(); 219 fs.close(); 220 result = true; 221 return result; 222 } 223 224 /** 225 * 追加内容到指定文件 226 * 227 * @param fileName 228 * @param fileContent 229 * @return 230 * @throws IOException 231 */ 232 public static boolean append(String fileName, String fileContent) 233 throws IOException { 234 boolean result = false; 235 File f = new File(fileName); 236 if (f.exists()) { 237 RandomAccessFile rFile = new RandomAccessFile(f, "rw"); 238 byte[] b = fileContent.getBytes(); 239 long originLen = f.length(); 240 rFile.setLength(originLen + b.length); 241 rFile.seek(originLen); 242 rFile.write(b); 243 rFile.close(); 244 } 245 result = true; 246 return result; 247 } 248 249 /** 250 * 拆分文件 251 * 252 * @param fileName 253 * 待拆分的完整文件名 254 * @param byteSize 255 * 按多少字节大小拆分 256 * @return 拆分后的文件名列表 257 * @throws IOException 258 */ 259 public List<String> splitBySize(String fileName, int byteSize) 260 throws IOException { 261 List<String> parts = new ArrayList<String>(); 262 File file = new File(fileName); 263 int count = (int) Math.ceil(file.length() / (double) byteSize); 264 int countLen = (count + "").length(); 265 ThreadPoolExecutor threadPool = new ThreadPoolExecutor(count, 266 count * 3, 1, TimeUnit.SECONDS, 267 new ArrayBlockingQueue<Runnable>(count * 2)); 268 269 for (int i = 0; i < count; i++) { 270 String partFileName = file.getPath() + "." 271 + leftPad((i + 1) + "", countLen, \'0\') + ".part"; 272 threadPool.execute(new SplitRunnable(byteSize, i * byteSize, 273 partFileName, file)); 274 parts.add(partFileName); 275 } 276 return parts; 277 } 278 279 /** 280 * 合并文件 281 * 282 * @param dirPath 283 * 拆分文件所在目录名 284 * @param partFileSuffix 285 * 拆分文件后缀名 286 * @param partFileSize 287 * 拆分文件的字节数大小 288 * @param mergeFileName 289 * 合并后的文件名 290 * @throws IOException 291 */ 292 public void mergePartFiles(String dirPath, String partFileSuffix, 293 int partFileSize, String mergeFileName) throws IOException { 294 ArrayList<File> partFiles = FileUtil.getDirFiles(dirPath, 295 partFileSuffix); 296 Collections.sort(partFiles, new FileComparator()); 297 298 RandomAccessFile randomAccessFile = new RandomAccessFile(mergeFileName, 299 "rw"); 300 randomAccessFile.setLength(partFileSize * (partFiles.size() - 1) 301 + partFiles.get(partFiles.size() - 1).length()); 302 randomAccessFile.close(); 303 304 ThreadPoolExecutor threadPool = new ThreadPoolExecutor( 305 partFiles.size(), partFiles.size() * 3, 1, TimeUnit.SECONDS, 306 new ArrayBlockingQueue<Runnable>(partFiles.size() * 2)); 307 308 for (int i = 0; i < partFiles.size(); i++) { 309 threadPool.execute(new MergeRunnable(i * partFileSize, 310 mergeFileName, partFiles.get(i))); 311 } 312 313 } 314 315 /** 316 * 根据文件名，比较文件 317 * 318 * @author yjmyzz@126.com 319 * 320 */ 321 private class FileComparator implements Comparator<File> { 322 public int compare(File o1, File o2) { 323 return o1.getName().compareToIgnoreCase(o2.getName()); 324 } 325 } 326 327 /** 328 * 创建(多级)目录 329 * 330 * @param filePath 331 * 完整的文件名(类似：/usr/a/b/c/d.xml) 332 */ 333 public static void createDirs(String filePath) { 334 File file = new File(filePath); 335 File parent = file.getParentFile(); 336 if (parent != null && !parent.exists()) { 337 parent.mkdirs(); 338 } 339 340 } 341 342 /** 343 * 分割处理Runnable 344 * 345 * @author yjmyzz@126.com 346 * 347 */ 348 private class SplitRunnable implements Runnable { 349 int byteSize; 350 String partFileName; 351 File originFile; 352 int startPos; 353 354 public SplitRunnable(int byteSize, int startPos, String partFileName, 355 File originFile) { 356 this.startPos = startPos; 357 this.byteSize = byteSize; 358 this.partFileName = partFileName; 359 this.originFile = originFile; 360 } 361 362 public void run() { 363 RandomAccessFile rFile; 364 OutputStream os; 365 try { 366 rFile = new RandomAccessFile(originFile, "r"); 367 byte[] b = new byte[byteSize]; 368 rFile.seek(startPos);// 移动指针到每“段”开头 369 int s = rFile.read(b); 370 os = new FileOutputStream(partFileName); 371 os.write(b, 0, s); 372 os.flush(); 373 os.close(); 374 } catch (IOException e) { 375 e.printStackTrace(); 376 } 377 } 378 } 379 380 /** 381 * 合并处理Runnable 382 * 383 * @author yjmyzz@126.com 384 * 385 */ 386 private class MergeRunnable implements Runnable { 387 long startPos; 388 String mergeFileName; 389 File partFile; 390 391 public MergeRunnable(long startPos, String mergeFileName, File partFile) { 392 this.startPos = startPos; 393 this.mergeFileName = mergeFileName; 394 this.partFile = partFile; 395 } 396 397 public void run() { 398 RandomAccessFile rFile; 399 try { 400 rFile = new RandomAccessFile(mergeFileName, "rw"); 401 rFile.seek(startPos); 402 FileInputStream fs = new FileInputStream(partFile); 403 byte[] b = new byte[fs.available()]; 404 fs.read(b); 405 fs.close(); 406 rFile.write(b); 407 rFile.close(); 408 } catch (IOException e) { 409 e.printStackTrace(); 410 } 411 } 412 } 413 414 }
View Code
最后是调用示例：
1 @Test 2 public void testExcel() throws Exception { 3 4 List<String[]> columNames = new ArrayList<String[]>(); 5 columNames.add(new String[] { "运单号", "代理人" }); 6 columNames.add(new String[] { "运单号", "代理人" }); 7 8 List<String[]> fieldNames = new ArrayList<String[]>(); 9 fieldNames.add(new String[] { "awbNumber", "agent" }); 10 fieldNames.add(new String[] { "awbNumber", "agent" }); 11 12 LinkedHashMap<String, List<?>> map = new LinkedHashMap<String, List<?>>(); 13 map.put("运单月报(1月)", getData1()); 14 map.put("运单月报(2月)", getData2()); 15 16 17 ExcelExportData setInfo = new ExcelExportData(); 18 setInfo.setDataMap(map); 19 setInfo.setFieldNames(fieldNames); 20 setInfo.setTitles(new String[] { "航空运单报表1","航空运单报表2"}); 21 setInfo.setColumnNames(columNames); 22 23 // 将需要导出的数据输出到文件 24 System.out.println(ExcelUtil.export2File(setInfo, "r:/test.xls")); 25 26 }
View Code
导出后的样子如下：
版权声明：本文为yjmyzz原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。