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 = 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 }
上面都是准备工作,下面才是重点:
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 }
导出后,大致是这个样子:
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 }
里面提供了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 }
此外,导出到文件时,还用到了一个读写文件的工具类:
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 }
最后是调用示例:
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 }
导出后的样子如下: