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

导出后的样子如下:

posted @ 2015-01-06 16:59  菩提树下的杨过  阅读(16089)  评论(4编辑  收藏  举报