首页 > 代码库 > java:POI导出excel
java: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 }
b) 伪造点数据
1 private List<AwbDto> getData1() { 2 List<AwbDto> data = http://www.mamicode.com/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 = http://www.mamicode.com/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 }
上面都是准备工作，下面才是重点：
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 =http://www.mamicode.com/ 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 }
导出后，大致是这个样子：
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 =http://www.mamicode.com/ ReflectionUtil.invokeGetterMethod(obj,107 fieldNames[num]);108 cells[cellNum].setCellValue(value =http://www.mamicode.com/= null ? "" : value109 .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, setInfo163 .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, setInfo178 .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("导出时间：" + new185 // 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: 创建所有的Sheet213 */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 // 设置序号列值，因为出去标题行和日期行，所有-2234 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 jimmy343 *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 }
里面提供了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 " + parameterTypes125 + " 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 = superClass147 .getSuperclass()) {148 try {149 return superClass.getDeclaredField(fieldName);150 } catch (NoSuchFieldException e) {// NOSONAR151 // 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 = superClass177 .getSuperclass()) {178 try {179 return superClass.getDeclaredMethod(methodName, parameterTypes);180 } catch (NoSuchMethodException e) {// NOSONAR181 // Method不在当前类定义,继续向上转型182 }183 }184 return null;185 }186 187 /**188 * 通过反射, 获得Class定义中声明的父类的泛型参数的类型. 如无法找到, 返回Object.class. eg. public UserDao189 * extends HibernateDao<User>190 * 191 * @param clazz192 * The class to introspect193 * @return the first generic declaration, or Object.class if cannot be194 * determined195 */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 clazz207 * clazz The class to introspect208 * @param index209 * the Index of the generic ddeclaration,start from 0.210 * @return the index generic declaration, or Object.class if cannot be211 * determined212 */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 collection245 * 来源集合.246 * @param propertyName247 * 要提取的属性名.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 collection269 * 来源集合.270 * @param propertyName271 * 要提取的属性名.272 * @param separator273 * 分隔符.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 value287 * 待转换的字符串288 * @param toType289 * 转换目标类型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 IllegalAccessException312 || e instanceof IllegalArgumentException313 || 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 void339 * @throws InvocationTargetException340 * @throws IllegalAccessException341 */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 void366 * @throws IllegalAccessException367 * @throws InvocationTargetException368 */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 = new396 * DocumentVo(); ReflectionUtils.copyPorperties(dcoVo, document,new397 * String[]{"id","businessName","createDate","applyName","docTitle",398 * "transactStatus"}); System.out.println(dcoVo.getId());399 */400 }401 }
此外，导出到文件时，还用到了一个读写文件的工具类：
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 =http://www.mamicode.com/ 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 dirPath109 * 目录路径110 * @param suffix111 * 文件后缀112 * @return113 */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 fileName142 * 待读取的完整文件名143 * @return 文件内容144 * @throws IOException145 */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 fileName161 * 目标文件名162 * @param fileContent163 * 写入的内容164 * @return165 * @throws IOException166 */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 fileName176 * 完整文件名(类似：/usr/a/b/c/d.txt)177 * @param fileContent178 * 文件内容179 * @param autoCreateDir180 * 目录不存在时，是否自动创建(多级)目录181 * @param autoOverWrite182 * 目标文件存在时，是否自动覆盖183 * @return184 * @throws IOException185 */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 fileName196 * 完整文件名(类似：/usr/a/b/c/d.txt)197 * @param contentBytes198 * 文件内容的字节数组199 * @param autoCreateDir200 * 目录不存在时，是否自动创建(多级)目录201 * @param autoOverWrite202 * 目标文件存在时，是否自动覆盖203 * @return204 * @throws IOException205 */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 fileName228 * @param fileContent229 * @return230 * @throws IOException231 */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 fileName253 * 待拆分的完整文件名254 * @param byteSize255 * 按多少字节大小拆分256 * @return 拆分后的文件名列表257 * @throws IOException258 */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 dirPath283 * 拆分文件所在目录名284 * @param partFileSuffix285 * 拆分文件后缀名286 * @param partFileSize287 * 拆分文件的字节数大小288 * @param mergeFileName289 * 合并后的文件名290 * @throws IOException291 */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.com319 *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 filePath331 * 完整的文件名(类似：/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 * 分割处理Runnable344 * 345 * @author yjmyzz@126.com346 *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 * 合并处理Runnable382 * 383 * @author yjmyzz@126.com384 *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 }
最后是调用示例：
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 }
导出后的样子如下：
java:POI导出excel
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。