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
package com.cnblogs.yjmyzz.test.domain; public class AwbDto { public AwbDto() { super(); } public AwbDto(String awbNumber, String agent) { super(); this.awbNumber = awbNumber; this.agent = agent; } /** * 运单号 */ private String awbNumber; /** * 代理人 */ private String agent; public String getAwbNumber() { return awbNumber; } public void setAwbNumber(String awbNumber) { this.awbNumber = awbNumber; } public String getAgent() { return agent; } public void setAgent(String agent) { this.agent = agent; } }
b) 伪造点数据
private List<AwbDto> getData1() { List<AwbDto> data = new ArrayList<AwbDto>(); for (int i = 0; i < 1000; i++) { data.add(new AwbDto("112-" + FileUtil.leftPad(i + "", 8, '0'), "张三")); } return data; } private List<AwbDto> getData2() { List<AwbDto> data = new ArrayList<AwbDto>(); for (int i = 0; i < 1000; i++) { data.add(new AwbDto("999-" + FileUtil.leftPad(i + "", 8, '0'), "李四")); } return data; }
上面都是准备工作,下面才是重点:
@Test public void testExcelExport() throws Exception { // 创建excel HSSFWorkbook wb = new HSSFWorkbook(); // 创建sheet HSSFSheet sheet = wb.createSheet("运单数据"); // 创建一行 HSSFRow rowTitle = sheet.createRow(0); // 创建标题栏样式 HSSFCellStyle styleTitle = wb.createCellStyle(); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中 HSSFFont fontTitle = wb.createFont(); // 宋体加粗 fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontTitle.setFontName("宋体"); fontTitle.setFontHeight((short) 200); styleTitle.setFont(fontTitle); // 在行上创建1列 HSSFCell cellTitle = rowTitle.createCell(0); // 列标题及样式 cellTitle.setCellValue("运单号"); cellTitle.setCellStyle(styleTitle); // 在行上创建2列 cellTitle = rowTitle.createCell(1); cellTitle.setCellValue("代理人"); cellTitle.setCellStyle(styleTitle); HSSFCellStyle styleCenter = wb.createCellStyle(); styleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中 // 取数据 List<AwbDto> data = getData1(); for (int i = 0; i < data.size(); i++) { AwbDto item = data.get(i); HSSFRow row = sheet.createRow(i + 1); HSSFCell cell = row.createCell(0); cell.setCellValue(item.getAwbNumber()); cell.setCellStyle(styleCenter); cell = row.createCell(1); cell.setCellValue(item.getAgent()); cell.setCellStyle(styleCenter); } FileOutputStream fout = new FileOutputStream("r:/awb.xls"); wb.write(fout); fout.close(); wb.close(); System.out.println("导出完成!"); }
导出后,大致是这个样子:
3、通用的Excel导出类
对于格式不太复杂的常规excel,如果每次都要写上面这一堆代码,当然有点2,已经有无私的高逼格程序猿在开源中国上奉献了自己的劳动成果,借来用一下(再次向作者表示感谢),不过这份代码年头略久,有 些方法已经被现在的版本标识为过时,略微改进了一下下,贴在这里:
package com.cnblogs.yjmyzz.utils; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.LinkedHashMap; import java.util.List; import java.util.Set; import java.util.Map.Entry; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.util.CellRangeAddress; public class ExcelUtil { private static HSSFWorkbook wb; private static CellStyle titleStyle; // 标题行样式 private static Font titleFont; // 标题行字体 private static CellStyle dateStyle; // 日期行样式 private static Font dateFont; // 日期行字体 private static CellStyle headStyle; // 表头行样式 private static Font headFont; // 表头行字体 private static CellStyle contentStyle; // 内容行样式 private static Font contentFont; // 内容行字体 /** * 导出文件 * * @param setInfo * @param outputExcelFileName * @return * @throws IOException */ public static boolean export2File(ExcelExportData setInfo, String outputExcelFileName) throws Exception { return FileUtil.write(outputExcelFileName, export2ByteArray(setInfo), true, true); } /** * 导出到byte数组 * * @param setInfo * @return * @throws Exception */ public static byte[] export2ByteArray(ExcelExportData setInfo) throws Exception { return export2Stream(setInfo).toByteArray(); } /** * 导出到流 * * @param setInfo * @return * @throws Exception */ public static ByteArrayOutputStream export2Stream(ExcelExportData setInfo) throws Exception { init(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); Set<Entry<String, List<?>>> set = setInfo.getDataMap().entrySet(); String[] sheetNames = new String[setInfo.getDataMap().size()]; int sheetNameNum = 0; for (Entry<String, List<?>> entry : set) { sheetNames[sheetNameNum] = entry.getKey(); sheetNameNum++; } HSSFSheet[] sheets = getSheets(setInfo.getDataMap().size(), sheetNames); int sheetNum = 0; for (Entry<String, List<?>> entry : set) { // Sheet List<?> objs = entry.getValue(); // 标题行 createTableTitleRow(setInfo, sheets, sheetNum); // 日期行 createTableDateRow(setInfo, sheets, sheetNum); // 表头 creatTableHeadRow(setInfo, sheets, sheetNum); // 表体 String[] fieldNames = setInfo.getFieldNames().get(sheetNum); int rowNum = 3; for (Object obj : objs) { HSSFRow contentRow = sheets[sheetNum].createRow(rowNum); contentRow.setHeight((short) 300); HSSFCell[] cells = getCells(contentRow, setInfo.getFieldNames() .get(sheetNum).length); int cellNum = 1; // 去掉一列序号,因此从1开始 if (fieldNames != null) { for (int num = 0; num < fieldNames.length; num++) { Object value = ReflectionUtil.invokeGetterMethod(obj, fieldNames[num]); cells[cellNum].setCellValue(value == null ? "" : value .toString()); cellNum++; } } rowNum++; } adjustColumnSize(sheets, sheetNum, fieldNames); // 自动调整列宽 sheetNum++; } wb.write(outputStream); return outputStream; } /** * @Description: 初始化 */ private static void init() { wb = new HSSFWorkbook(); titleFont = wb.createFont(); titleStyle = wb.createCellStyle(); dateStyle = wb.createCellStyle(); dateFont = wb.createFont(); headStyle = wb.createCellStyle(); headFont = wb.createFont(); contentStyle = wb.createCellStyle(); contentFont = wb.createFont(); initTitleCellStyle(); initTitleFont(); initDateCellStyle(); initDateFont(); initHeadCellStyle(); initHeadFont(); initContentCellStyle(); initContentFont(); } /** * @Description: 自动调整列宽 */ private static void adjustColumnSize(HSSFSheet[] sheets, int sheetNum, String[] fieldNames) { for (int i = 0; i < fieldNames.length + 1; i++) { sheets[sheetNum].autoSizeColumn(i, true); } } /** * @Description: 创建标题行(需合并单元格) */ private static void createTableTitleRow(ExcelExportData setInfo, HSSFSheet[] sheets, int sheetNum) { CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, setInfo .getFieldNames().get(sheetNum).length); sheets[sheetNum].addMergedRegion(titleRange); HSSFRow titleRow = sheets[sheetNum].createRow(0); titleRow.setHeight((short) 800); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(titleStyle); titleCell.setCellValue(setInfo.getTitles()[sheetNum]); } /** * @Description: 创建日期行(需合并单元格) */ private static void createTableDateRow(ExcelExportData setInfo, HSSFSheet[] sheets, int sheetNum) { CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, setInfo .getFieldNames().get(sheetNum).length); sheets[sheetNum].addMergedRegion(dateRange); HSSFRow dateRow = sheets[sheetNum].createRow(1); dateRow.setHeight((short) 350); HSSFCell dateCell = dateRow.createCell(0); dateCell.setCellStyle(dateStyle); // dateCell.setCellValue("导出时间:" + new // SimpleDateFormat("yyyy-MM-dd HH:mm:ss") // .format(new Date())); dateCell.setCellValue(new SimpleDateFormat("yyyy-MM-dd") .format(new Date())); } /** * @Description: 创建表头行(需合并单元格) */ private static void creatTableHeadRow(ExcelExportData setInfo, HSSFSheet[] sheets, int sheetNum) { // 表头 HSSFRow headRow = sheets[sheetNum].createRow(2); headRow.setHeight((short) 350); // 序号列 HSSFCell snCell = headRow.createCell(0); snCell.setCellStyle(headStyle); snCell.setCellValue("序号"); // 列头名称 for (int num = 1, len = setInfo.getColumnNames().get(sheetNum).length; num <= len; num++) { HSSFCell headCell = headRow.createCell(num); headCell.setCellStyle(headStyle); headCell.setCellValue(setInfo.getColumnNames().get(sheetNum)[num - 1]); } } /** * @Description: 创建所有的Sheet */ private static HSSFSheet[] getSheets(int num, String[] names) { HSSFSheet[] sheets = new HSSFSheet[num]; for (int i = 0; i < num; i++) { sheets[i] = wb.createSheet(names[i]); } return sheets; } /** * @Description: 创建内容行的每一列(附加一列序号) */ private static HSSFCell[] getCells(HSSFRow contentRow, int num) { HSSFCell[] cells = new HSSFCell[num + 1]; for (int i = 0, len = cells.length; i < len; i++) { cells[i] = contentRow.createCell(i); cells[i].setCellStyle(contentStyle); } // 设置序号列值,因为出去标题行和日期行,所有-2 cells[0].setCellValue(contentRow.getRowNum() - 2); return cells; } /** * @Description: 初始化标题行样式 */ private static void initTitleCellStyle() { titleStyle.setAlignment(CellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); titleStyle.setFont(titleFont); titleStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index); } /** * @Description: 初始化日期行样式 */ private static void initDateCellStyle() { dateStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION); dateStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); dateStyle.setFont(dateFont); dateStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index); } /** * @Description: 初始化表头行样式 */ private static void initHeadCellStyle() { headStyle.setAlignment(CellStyle.ALIGN_CENTER); headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); headStyle.setFont(headFont); headStyle.setFillBackgroundColor(IndexedColors.YELLOW.index); headStyle.setBorderTop(CellStyle.BORDER_MEDIUM); headStyle.setBorderBottom(CellStyle.BORDER_THIN); headStyle.setBorderLeft(CellStyle.BORDER_THIN); headStyle.setBorderRight(CellStyle.BORDER_THIN); headStyle.setTopBorderColor(IndexedColors.BLUE.index); headStyle.setBottomBorderColor(IndexedColors.BLUE.index); headStyle.setLeftBorderColor(IndexedColors.BLUE.index); headStyle.setRightBorderColor(IndexedColors.BLUE.index); } /** * @Description: 初始化内容行样式 */ private static void initContentCellStyle() { contentStyle.setAlignment(CellStyle.ALIGN_CENTER); contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); contentStyle.setFont(contentFont); contentStyle.setBorderTop(CellStyle.BORDER_THIN); contentStyle.setBorderBottom(CellStyle.BORDER_THIN); contentStyle.setBorderLeft(CellStyle.BORDER_THIN); contentStyle.setBorderRight(CellStyle.BORDER_THIN); contentStyle.setTopBorderColor(IndexedColors.BLUE.index); contentStyle.setBottomBorderColor(IndexedColors.BLUE.index); contentStyle.setLeftBorderColor(IndexedColors.BLUE.index); contentStyle.setRightBorderColor(IndexedColors.BLUE.index); contentStyle.setWrapText(true); // 字段换行 } /** * @Description: 初始化标题行字体 */ private static void initTitleFont() { titleFont.setFontName("华文楷体"); titleFont.setFontHeightInPoints((short) 20); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); titleFont.setCharSet(Font.DEFAULT_CHARSET); titleFont.setColor(IndexedColors.BLUE_GREY.index); } /** * @Description: 初始化日期行字体 */ private static void initDateFont() { dateFont.setFontName("隶书"); dateFont.setFontHeightInPoints((short) 10); dateFont.setBoldweight(Font.BOLDWEIGHT_BOLD); dateFont.setCharSet(Font.DEFAULT_CHARSET); dateFont.setColor(IndexedColors.BLUE_GREY.index); } /** * @Description: 初始化表头行字体 */ private static void initHeadFont() { headFont.setFontName("宋体"); headFont.setFontHeightInPoints((short) 10); headFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headFont.setCharSet(Font.DEFAULT_CHARSET); headFont.setColor(IndexedColors.BLUE_GREY.index); } /** * @Description: 初始化内容行字体 */ private static void initContentFont() { contentFont.setFontName("宋体"); contentFont.setFontHeightInPoints((short) 10); contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL); contentFont.setCharSet(Font.DEFAULT_CHARSET); contentFont.setColor(IndexedColors.BLUE_GREY.index); } /** * Excel导出数据类 * * @author jimmy * */ public static class ExcelExportData { /** * 导出数据 key:String 表示每个Sheet的名称 value:List<?> 表示每个Sheet里的所有数据行 */ private LinkedHashMap<String, List<?>> dataMap; /** * 每个Sheet里的顶部大标题 */ private String[] titles; /** * 单个sheet里的数据列标题 */ private List<String[]> columnNames; /** * 单个sheet里每行数据的列对应的对象属性名称 */ private List<String[]> fieldNames; public List<String[]> getFieldNames() { return fieldNames; } public void setFieldNames(List<String[]> fieldNames) { this.fieldNames = fieldNames; } public String[] getTitles() { return titles; } public void setTitles(String[] titles) { this.titles = titles; } public List<String[]> getColumnNames() { return columnNames; } public void setColumnNames(List<String[]> columnNames) { this.columnNames = columnNames; } public LinkedHashMap<String, List<?>> getDataMap() { return dataMap; } public void setDataMap(LinkedHashMap<String, List<?>> dataMap) { this.dataMap = dataMap; } } }
里面提供了3个方法,可用于导出到文件、byte数组、以及流,其中有一个反射工具类:
package com.cnblogs.yjmyzz.utils; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.lang.reflect.Modifier; import java.lang.reflect.ParameterizedType; import java.lang.reflect.Type; import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.List; import org.apache.commons.beanutils.BeanUtils; import org.apache.commons.beanutils.ConvertUtils; import org.apache.commons.beanutils.PropertyUtils; import org.apache.commons.beanutils.locale.converters.DateLocaleConverter; import org.apache.commons.lang.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.util.Assert; /** * 反射工具类. * * 提供访问私有变量,获取泛型类型Class, 提取集合中元素的属性, 转换字符串到对象等Util函数. * */ public class ReflectionUtil { private static Log logger = LogFactory.getLog(ReflectionUtil.class); static { DateLocaleConverter dc = new DateLocaleConverter(); // dc.setPatterns(new String[] { "yyyy-MM-dd", "yyyy-MM-dd HH:mm:ss" }); ConvertUtils.register(dc, Date.class); } /** * 调用Getter方法. */ public static Object invokeGetterMethod(Object target, String propertyName) { String getterMethodName = "get" + StringUtils.capitalize(propertyName); return invokeMethod(target, getterMethodName, new Class[] {}, new Object[] {}); } /** * 调用Setter方法.使用value的Class来查找Setter方法. */ public static void invokeSetterMethod(Object target, String propertyName, Object value) { invokeSetterMethod(target, propertyName, value, null); } /** * 调用Setter方法. * * @param propertyType * 用于查找Setter方法,为空时使用value的Class替代. */ public static void invokeSetterMethod(Object target, String propertyName, Object value, Class<?> propertyType) { Class<?> type = propertyType != null ? propertyType : value.getClass(); String setterMethodName = "set" + StringUtils.capitalize(propertyName); invokeMethod(target, setterMethodName, new Class[] { type }, new Object[] { value }); } /** * 直接读取对象属性值, 无视private/protected修饰符, 不经过getter函数. */ public static Object getFieldValue(final Object object, final String fieldName) { Field field = getDeclaredField(object, fieldName); if (field == null) { throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + object + "]"); } makeAccessible(field); Object result = null; try { result = field.get(object); } catch (IllegalAccessException e) { logger.error("不可能抛出的异常{}" + e.getMessage()); } return result; } /** * 直接设置对象属性值, 无视private/protected修饰符, 不经过setter函数. */ public static void setFieldValue(final Object object, final String fieldName, final Object value) { Field field = getDeclaredField(object, fieldName); if (field == null) { throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + object + "]"); } makeAccessible(field); try { field.set(object, value); } catch (IllegalAccessException e) { logger.error("不可能抛出的异常:{}" + e.getMessage()); } } /** * 直接调用对象方法, 无视private/protected修饰符. */ public static Object invokeMethod(final Object object, final String methodName, final Class<?>[] parameterTypes, final Object[] parameters) { Method method = getDeclaredMethod(object, methodName, parameterTypes); if (method == null) { throw new IllegalArgumentException("Could not find method [" + methodName + "] parameterType " + parameterTypes + " on target [" + object + "]"); } method.setAccessible(true); try { return method.invoke(object, parameters); } catch (Exception e) { throw convertReflectionExceptionToUnchecked(e); } } /** * 循环向上转型, 获取对象的DeclaredField. * * 如向上转型到Object仍无法找到, 返回null. */ protected static Field getDeclaredField(final Object object, final String fieldName) { Assert.notNull(object, "object不能为空"); Assert.hasText(fieldName, "fieldName"); for (Class<?> superClass = object.getClass(); superClass != Object.class; superClass = superClass .getSuperclass()) { try { return superClass.getDeclaredField(fieldName); } catch (NoSuchFieldException e) {// NOSONAR // Field不在当前类定义,继续向上转型 } } return null; } /** * 强行设置Field可访问. */ protected static void makeAccessible(final Field field) { if (!Modifier.isPublic(field.getModifiers()) || !Modifier.isPublic(field.getDeclaringClass().getModifiers())) { field.setAccessible(true); } } /** * 循环向上转型, 获取对象的DeclaredMethod. * * 如向上转型到Object仍无法找到, 返回null. */ protected static Method getDeclaredMethod(Object object, String methodName, Class<?>[] parameterTypes) { Assert.notNull(object, "object不能为空"); for (Class<?> superClass = object.getClass(); superClass != Object.class; superClass = superClass .getSuperclass()) { try { return superClass.getDeclaredMethod(methodName, parameterTypes); } catch (NoSuchMethodException e) {// NOSONAR // Method不在当前类定义,继续向上转型 } } return null; } /** * 通过反射, 获得Class定义中声明的父类的泛型参数的类型. 如无法找到, 返回Object.class. eg. public UserDao * extends HibernateDao<User> * * @param clazz * The class to introspect * @return the first generic declaration, or Object.class if cannot be * determined */ @SuppressWarnings("unchecked") public static <T> Class<T> getSuperClassGenricType(final Class<?> clazz) { return getSuperClassGenricType(clazz, 0); } /** * 通过反射, 获得定义Class时声明的父类的泛型参数的类型. 如无法找到, 返回Object.class. * * 如public UserDao extends HibernateDao<User,Long> * * @param clazz * clazz The class to introspect * @param index * the Index of the generic ddeclaration,start from 0. * @return the index generic declaration, or Object.class if cannot be * determined */ @SuppressWarnings("unchecked") public static Class getSuperClassGenricType(final Class<?> clazz, final int index) { Type genType = clazz.getGenericSuperclass(); if (!(genType instanceof ParameterizedType)) { logger.warn(clazz.getSimpleName() + "'s superclass not ParameterizedType"); return Object.class; } Type[] params = ((ParameterizedType) genType).getActualTypeArguments(); if (index >= params.length || index < 0) { logger.warn("Index: " + index + ", Size of " + clazz.getSimpleName() + "'s Parameterized Type: " + params.length); return Object.class; } if (!(params[index] instanceof Class)) { logger.warn(clazz.getSimpleName() + " not set the actual class on superclass generic parameter"); return Object.class; } return (Class) params[index]; } /** * 提取集合中的对象的属性(通过getter函数), 组合成List. * * @param collection * 来源集合. * @param propertyName * 要提取的属性名. */ public static List convertElementPropertyToList( final Collection collection, final String propertyName) { List list = new ArrayList(); try { for (Object obj : collection) { list.add(PropertyUtils.getProperty(obj, propertyName)); } } catch (Exception e) { throw convertReflectionExceptionToUnchecked(e); } return list; } /** * 提取集合中的对象的属性(通过getter函数), 组合成由分割符分隔的字符串. * * @param collection * 来源集合. * @param propertyName * 要提取的属性名. * @param separator * 分隔符. */ @SuppressWarnings("unchecked") public static String convertElementPropertyToString( final Collection collection, final String propertyName, final String separator) { List list = convertElementPropertyToList(collection, propertyName); return StringUtils.join(list, separator); } /** * 转换字符串到相应类型. * * @param value * 待转换的字符串 * @param toType * 转换目标类型 */ @SuppressWarnings("unchecked") public static <T> T convertStringToObject(String value, Class<T> toType) { try { return (T) ConvertUtils.convert(value, toType); } catch (Exception e) { throw convertReflectionExceptionToUnchecked(e); } } /** * 将反射时的checked exception转换为unchecked exception. */ public static RuntimeException convertReflectionExceptionToUnchecked( Exception e) { return convertReflectionExceptionToUnchecked(null, e); } public static RuntimeException convertReflectionExceptionToUnchecked( String desc, Exception e) { desc = (desc == null) ? "Unexpected Checked Exception." : desc; if (e instanceof IllegalAccessException || e instanceof IllegalArgumentException || e instanceof NoSuchMethodException) { return new IllegalArgumentException(desc, e); } else if (e instanceof InvocationTargetException) { return new RuntimeException(desc, ((InvocationTargetException) e).getTargetException()); } else if (e instanceof RuntimeException) { return (RuntimeException) e; } return new RuntimeException(desc, e); } public static final <T> T getNewInstance(Class<T> cls) { try { return cls.newInstance(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } return null; } /** * 拷贝 source 指定的porperties 属性 到 dest中 * * @return void * @throws InvocationTargetException * @throws IllegalAccessException */ public static void copyPorperties(Object dest, Object source, String[] porperties) throws InvocationTargetException, IllegalAccessException { for (String por : porperties) { Object srcObj = invokeGetterMethod(source, por); logger.debug("属性名:" + por + "------------- 属性值:" + srcObj); if (srcObj != null) { try { BeanUtils.setProperty(dest, por, srcObj); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { throw e; } catch (InvocationTargetException e) { throw e; } } } } /** * 两者属性名一致时,拷贝source里的属性到dest里 * * @return void * @throws IllegalAccessException * @throws InvocationTargetException */ public static void copyPorperties(Object dest, Object source) throws IllegalAccessException, InvocationTargetException { Class<? extends Object> srcCla = source.getClass(); Field[] fsF = srcCla.getDeclaredFields(); for (Field s : fsF) { String name = s.getName(); Object srcObj = invokeGetterMethod(source, name); try { BeanUtils.setProperty(dest, name, srcObj); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { throw e; } catch (InvocationTargetException e) { throw e; } } // BeanUtils.copyProperties(dest, orig); } public static void main(String[] args) throws InvocationTargetException, IllegalAccessException { /* * Document document = new Document(); document.setId(2); * document.setCreateDate(new Date()); DocumentVo dcoVo = new * DocumentVo(); ReflectionUtils.copyPorperties(dcoVo, document,new * String[]{"id","businessName","createDate","applyName","docTitle", * "transactStatus"}); System.out.println(dcoVo.getId()); */ } }
此外,导出到文件时,还用到了一个读写文件的工具类:
package com.cnblogs.yjmyzz.utils; import java.io.*; import java.util.*; import java.util.concurrent.*; /** * 文件处理辅助类 * * @author yjmyzz@126.com * @version 0.2 * @since 2014-11-17 * */ public class FileUtil { /** * 当前目录路径 */ public static String currentWorkDir = System.getProperty("user.dir") + "\"; /** * 左填充 * * @param str * @param length * @param ch * @return */ public static String leftPad(String str, int length, char ch) { if (str.length() >= length) { return str; } char[] chs = new char[length]; Arrays.fill(chs, ch); char[] src = str.toCharArray(); System.arraycopy(src, 0, chs, length - src.length, src.length); return new String(chs); } /** * 删除文件 * * @param fileName * 待删除的完整文件名 * @return */ public static boolean delete(String fileName) { boolean result = false; File f = new File(fileName); if (f.exists()) { result = f.delete(); } else { result = true; } return result; } /*** * 递归获取指定目录下的所有的文件(不包括文件夹) * * @param obj * @return */ public static ArrayList<File> getAllFiles(String dirPath) { File dir = new File(dirPath); ArrayList<File> files = new ArrayList<File>(); if (dir.isDirectory()) { File[] fileArr = dir.listFiles(); for (int i = 0; i < fileArr.length; i++) { File f = fileArr[i]; if (f.isFile()) { files.add(f); } else { files.addAll(getAllFiles(f.getPath())); } } } return files; } /** * 获取指定目录下的所有文件(不包括子文件夹) * * @param dirPath * @return */ public static ArrayList<File> getDirFiles(String dirPath) { File path = new File(dirPath); File[] fileArr = path.listFiles(); ArrayList<File> files = new ArrayList<File>(); for (File f : fileArr) { if (f.isFile()) { files.add(f); } } return files; } /** * 获取指定目录下特定文件后缀名的文件列表(不包括子文件夹) * * @param dirPath * 目录路径 * @param suffix * 文件后缀 * @return */ public static ArrayList<File> getDirFiles(String dirPath, final String suffix) { File path = new File(dirPath); File[] fileArr = path.listFiles(new FilenameFilter() { public boolean accept(File dir, String name) { String lowerName = name.toLowerCase(); String lowerSuffix = suffix.toLowerCase(); if (lowerName.endsWith(lowerSuffix)) { return true; } return false; } }); ArrayList<File> files = new ArrayList<File>(); for (File f : fileArr) { if (f.isFile()) { files.add(f); } } return files; } /** * 读取文件内容 * * @param fileName * 待读取的完整文件名 * @return 文件内容 * @throws IOException */ public static String read(String fileName) throws IOException { File f = new File(fileName); FileInputStream fs = new FileInputStream(f); String result = null; byte[] b = new byte[fs.available()]; fs.read(b); fs.close(); result = new String(b); return result; } /** * 写文件 * * @param fileName * 目标文件名 * @param fileContent * 写入的内容 * @return * @throws IOException */ public static boolean write(String fileName, String fileContent) throws IOException { return write(fileName, fileContent, true, true); } /** * 写文件 * * @param fileName * 完整文件名(类似:/usr/a/b/c/d.txt) * @param fileContent * 文件内容 * @param autoCreateDir * 目录不存在时,是否自动创建(多级)目录 * @param autoOverWrite * 目标文件存在时,是否自动覆盖 * @return * @throws IOException */ public static boolean write(String fileName, String fileContent, boolean autoCreateDir, boolean autoOverwrite) throws IOException { return write(fileName, fileContent.getBytes(), autoCreateDir, autoOverwrite); } /** * 写文件 * * @param fileName * 完整文件名(类似:/usr/a/b/c/d.txt) * @param contentBytes * 文件内容的字节数组 * @param autoCreateDir * 目录不存在时,是否自动创建(多级)目录 * @param autoOverWrite * 目标文件存在时,是否自动覆盖 * @return * @throws IOException */ public static boolean write(String fileName, byte[] contentBytes, boolean autoCreateDir, boolean autoOverwrite) throws IOException { boolean result = false; if (autoCreateDir) { createDirs(fileName); } if (autoOverwrite) { delete(fileName); } File f = new File(fileName); FileOutputStream fs = new FileOutputStream(f); fs.write(contentBytes); fs.flush(); fs.close(); result = true; return result; } /** * 追加内容到指定文件 * * @param fileName * @param fileContent * @return * @throws IOException */ public static boolean append(String fileName, String fileContent) throws IOException { boolean result = false; File f = new File(fileName); if (f.exists()) { RandomAccessFile rFile = new RandomAccessFile(f, "rw"); byte[] b = fileContent.getBytes(); long originLen = f.length(); rFile.setLength(originLen + b.length); rFile.seek(originLen); rFile.write(b); rFile.close(); } result = true; return result; } /** * 拆分文件 * * @param fileName * 待拆分的完整文件名 * @param byteSize * 按多少字节大小拆分 * @return 拆分后的文件名列表 * @throws IOException */ public List<String> splitBySize(String fileName, int byteSize) throws IOException { List<String> parts = new ArrayList<String>(); File file = new File(fileName); int count = (int) Math.ceil(file.length() / (double) byteSize); int countLen = (count + "").length(); ThreadPoolExecutor threadPool = new ThreadPoolExecutor(count, count * 3, 1, TimeUnit.SECONDS, new ArrayBlockingQueue<Runnable>(count * 2)); for (int i = 0; i < count; i++) { String partFileName = file.getPath() + "." + leftPad((i + 1) + "", countLen, '0') + ".part"; threadPool.execute(new SplitRunnable(byteSize, i * byteSize, partFileName, file)); parts.add(partFileName); } return parts; } /** * 合并文件 * * @param dirPath * 拆分文件所在目录名 * @param partFileSuffix * 拆分文件后缀名 * @param partFileSize * 拆分文件的字节数大小 * @param mergeFileName * 合并后的文件名 * @throws IOException */ public void mergePartFiles(String dirPath, String partFileSuffix, int partFileSize, String mergeFileName) throws IOException { ArrayList<File> partFiles = FileUtil.getDirFiles(dirPath, partFileSuffix); Collections.sort(partFiles, new FileComparator()); RandomAccessFile randomAccessFile = new RandomAccessFile(mergeFileName, "rw"); randomAccessFile.setLength(partFileSize * (partFiles.size() - 1) + partFiles.get(partFiles.size() - 1).length()); randomAccessFile.close(); ThreadPoolExecutor threadPool = new ThreadPoolExecutor( partFiles.size(), partFiles.size() * 3, 1, TimeUnit.SECONDS, new ArrayBlockingQueue<Runnable>(partFiles.size() * 2)); for (int i = 0; i < partFiles.size(); i++) { threadPool.execute(new MergeRunnable(i * partFileSize, mergeFileName, partFiles.get(i))); } } /** * 根据文件名,比较文件 * * @author yjmyzz@126.com * */ private class FileComparator implements Comparator<File> { public int compare(File o1, File o2) { return o1.getName().compareToIgnoreCase(o2.getName()); } } /** * 创建(多级)目录 * * @param filePath * 完整的文件名(类似:/usr/a/b/c/d.xml) */ public static void createDirs(String filePath) { File file = new File(filePath); File parent = file.getParentFile(); if (parent != null && !parent.exists()) { parent.mkdirs(); } } /** * 分割处理Runnable * * @author yjmyzz@126.com * */ private class SplitRunnable implements Runnable { int byteSize; String partFileName; File originFile; int startPos; public SplitRunnable(int byteSize, int startPos, String partFileName, File originFile) { this.startPos = startPos; this.byteSize = byteSize; this.partFileName = partFileName; this.originFile = originFile; } public void run() { RandomAccessFile rFile; OutputStream os; try { rFile = new RandomAccessFile(originFile, "r"); byte[] b = new byte[byteSize]; rFile.seek(startPos);// 移动指针到每“段”开头 int s = rFile.read(b); os = new FileOutputStream(partFileName); os.write(b, 0, s); os.flush(); os.close(); } catch (IOException e) { e.printStackTrace(); } } } /** * 合并处理Runnable * * @author yjmyzz@126.com * */ private class MergeRunnable implements Runnable { long startPos; String mergeFileName; File partFile; public MergeRunnable(long startPos, String mergeFileName, File partFile) { this.startPos = startPos; this.mergeFileName = mergeFileName; this.partFile = partFile; } public void run() { RandomAccessFile rFile; try { rFile = new RandomAccessFile(mergeFileName, "rw"); rFile.seek(startPos); FileInputStream fs = new FileInputStream(partFile); byte[] b = new byte[fs.available()]; fs.read(b); fs.close(); rFile.write(b); rFile.close(); } catch (IOException e) { e.printStackTrace(); } } } }
最后是调用示例:
@Test public void testExcel() throws Exception { List<String[]> columNames = new ArrayList<String[]>(); columNames.add(new String[] { "运单号", "代理人" }); columNames.add(new String[] { "运单号", "代理人" }); List<String[]> fieldNames = new ArrayList<String[]>(); fieldNames.add(new String[] { "awbNumber", "agent" }); fieldNames.add(new String[] { "awbNumber", "agent" }); LinkedHashMap<String, List<?>> map = new LinkedHashMap<String, List<?>>(); map.put("运单月报(1月)", getData1()); map.put("运单月报(2月)", getData2()); ExcelExportData setInfo = new ExcelExportData(); setInfo.setDataMap(map); setInfo.setFieldNames(fieldNames); setInfo.setTitles(new String[] { "航空运单报表1","航空运单报表2"}); setInfo.setColumnNames(columNames); // 将需要导出的数据输出到文件 System.out.println(ExcelUtil.export2File(setInfo, "r:/test.xls")); }
导出后的样子如下: