poi java.awt android_Java-POI 4.1.2 表格导入导出2020.2.17官网发布的库

1 一、读取表格2

3

4

5

6

7

8

9

10

11

读取表格数据

12

13

14 table td{border:1px solid #F00}15

16

17

18

19 //"想办法找到要提供下载的文件的物理路径＋文件名";

20 String localFilePath = request.getServletContext().getRealPath("") + "\\本地导出的文件.xls";21 %>

22

读取表格实例

23

24

25

26 //读取Excel文件内容

27 List readResult =POIExcelReader.readExcel(localFilePath);28 out.println("

编号	姓名	学号
"+item.getId() + "	" + item.getName() + "	" + item.getStudentNo() + "

");34 %>

35

36

37

38

39

40

41 二、导出本地表格42

43

44

45

46

47

48

49

50

51 Created by IntelliJ IDEA.52 User: Administrator53 Date: 2020/7/16

54 Time: 9:27

55 To change this template use File | Settings |File Templates.56 --%>

57

58

59

60

下载表格

61

62

63

64 if (request.getParameter("type") == "2003") {65

66 } else{67

68 }69

70 String[] columnNames = {"ID", "姓名", "学号"};71

72 List list = new ArrayList<>();73 for (int i = 1; i <= 100; i++) {74 Student model = newStudent();75 model.Id =Integer.toString(i);76 model.Name = "学生" +i;77 model.StudentNo = "序号000" +i;78 list.add(model);79 }80

81 //方式1：直接导出表格测试【保存到本地】

82 PoiExportExcelUtil util = new PoiExportExcelUtil();83

84 String localFilePath = request.getServletContext().getRealPath("") + "\\本地导出的文件.xls";85 util.exportExcel("用户导出", columnNames, list, newFileOutputStream(localFilePath), PoiExportExcelUtil.EXCEL_FILE_2003);86

87 out.print("写入完成"+localFilePath);88 %>

89

90

91

92

93

94

95

96

97

98 三、下载表格99

100

101

102

103

104

105

106

107

108

109

下载web表格

110

111

112

113

114 //方式2：通过流下载文件(适合web下载)115 //关于文件下载时采用文件流输出的方式处理：116 //加上response.reset()，并且所有的％>后面不要换行，包括最后一个；

117 response.reset();//可以加也可以不加

118 response.setContentType("application/x-download");119 //application.getRealPath("/main/mvplayer/CapSetup.msi");获取的物理路径120

121 //"想办法找到要提供下载的文件的物理路径＋文件名";

122 String localFilePath=request.getServletContext().getRealPath("")+"\\本地导出的文件.xls";123

124 String filedisplay ="web下载的文件"+ Utils.getDateTimeNow()+".xls" ;//"给用户提供的下载文件名";

125 filedisplay = URLEncoder.encode(filedisplay,"UTF-8");126 response.addHeader("Content-Disposition","attachment;filename=" +filedisplay);127

128 java.io.OutputStream outp = null;129 java.io.FileInputStream in = null;130 try{131 outp =response.getOutputStream();132 in = newFileInputStream(localFilePath);133 byte[] b = new byte[1024];134 int i = 0;135 while((i = in.read(b)) > 0) {136 outp.write(b, 0, i);137 }138 outp.flush();139 //要加以下两句话，否则会报错140 //java.lang.IllegalStateException: getOutputStream() has already been called for//this response

141

142 out.clear();143 out =pageContext.pushBody();144

145

146 } catch(Exception e){147 System.out.println("Error!");148 e.printStackTrace();149 }finally{150 if(in != null){151 in.close();152 in = null;153 }154 //这里不能关闭155 //if(outp != null) {156 //outp.close();157 //outp = null;158 //}

159 }160

161 %>

162

163

164

165

166 核心方法如下，建议下载完整代码查看167

168

169

170 ==》》》读取表格171

172 POIExcelReader.java173

174

175

176 packagecodedna616.demo.commom;177

178 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;179 importorg.apache.poi.ss.usermodel.Cell;180 importorg.apache.poi.ss.usermodel.Row;181 importorg.apache.poi.ss.usermodel.Sheet;182 importorg.apache.poi.ss.usermodel.Workbook;183 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;184 //import org.springframework.web.multipart.MultipartFile;

185

186 importjava.io.File;187 importjava.io.FileInputStream;188 importjava.io.IOException;189 importjava.io.InputStream;190 importjava.text.DecimalFormat;191 import java.util.*;192 importjava.util.logging.Logger;193

194 /**

195 * Author: Dreamer-1196 * Date: 2019-03-01197 * Time: 10:21198 * Description: 读取Excel内容199 */

200 public classPOIExcelReader {201

202 private static Logger logger = Logger.getLogger(POIExcelReader.class.getName()); //日志打印类

203

204 private static final String XLS = "xls";205 private static final String XLSX = "xlsx";206

207 /**

208 * 根据文件后缀名类型获取对应的工作簿对象209 *@paraminputStream 读取文件的输入流210 *@paramfileType 文件后缀名类型(xls或xlsx)211 *@return包含文件数据的工作簿对象212 *@throwsIOException213 */

214 public static Workbook getWorkbook(InputStream inputStream, String fileType) throwsIOException {215 Workbook workbook = null;216 if(fileType.equalsIgnoreCase(XLS)) {217 workbook = newHSSFWorkbook(inputStream);218 } else if(fileType.equalsIgnoreCase(XLSX)) {219 workbook = newXSSFWorkbook(inputStream);220 }221 returnworkbook;222 }223

224 /**

225 * 读取Excel文件内容226 *@paramfileName 要读取的Excel文件所在路径227 *@return读取结果列表，读取失败时返回null228 */

229 public static ListreadExcel(String fileName) {230

231 Workbook workbook = null;232 FileInputStream inputStream = null;233

234 try{235 //获取Excel后缀名

236 String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());237 //获取Excel文件

238 File excelFile = newFile(fileName);239 if (!excelFile.exists()) {240 logger.warning("指定的Excel文件不存在！");241 return null;242 }243

244 //获取Excel工作簿

245 inputStream = newFileInputStream(excelFile);246 workbook =getWorkbook(inputStream, fileType);247

248 //读取excel中的数据

249 List resultDataList =parseExcel(workbook);250

251 returnresultDataList;252 } catch(Exception e) {253 logger.warning("解析Excel失败，文件名：" + fileName + " 错误信息：" +e.getMessage());254 return null;255 } finally{256 try{257 if (null !=workbook) {258 workbook.close();259 }260 if (null !=inputStream) {261 inputStream.close();262 }263 } catch(Exception e) {264 logger.warning("关闭数据流出错！错误信息：" +e.getMessage());265 return null;266 }267 }268 }269

270 /**

271 * 读取Excel文件内容272 *@paramfile 上传的Excel文件273 *@return读取结果列表，读取失败时返回null274 */

275 //public static List readExcel(MultipartFile file) {276 //

277 //Workbook workbook = null;278 //

279 //try {280 // //获取Excel后缀名281 //String fileName = file.getOriginalFilename();282 //if (fileName == null || fileName.isEmpty() || fileName.lastIndexOf(".") < 0) {283 //logger.warning("解析Excel失败，因为获取到的Excel文件名非法！");284 //return null;285 //}286 //String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());287 //

288 // //获取Excel工作簿289 //workbook = getWorkbook(file.getInputStream(), fileType);290 //

291 // //读取excel中的数据292 //List resultDataList = parseExcel(workbook);293 //

294 //return resultDataList;295 //} catch (Exception e) {296 //logger.warning("解析Excel失败，文件名：" + file.getOriginalFilename() + " 错误信息：" + e.getMessage());297 //return null;298 //} finally {299 //try {300 //if (null != workbook) {301 //workbook.close();302 //}303 //} catch (Exception e) {304 //logger.warning("关闭数据流出错！错误信息：" + e.getMessage());305 //return null;306 //}307 //}308 //}

309

310

311 /**

312 * 解析Excel数据313 *@paramworkbook Excel工作簿对象314 *@return解析结果315 */

316 private static ListparseExcel(Workbook workbook) {317 List resultDataList = new ArrayList<>();318 //解析sheet

319 for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {320 Sheet sheet =workbook.getSheetAt(sheetNum);321

322 //校验sheet是否合法

323 if (sheet == null) {324 continue;325 }326

327 //获取第一行数据

328 int firstRowNum =sheet.getFirstRowNum();329 Row firstRow =sheet.getRow(firstRowNum);330 if (null ==firstRow) {331 logger.warning("解析Excel失败，在第一行没有读取到任何数据！");332 }333

334 //解析每一行的数据，构造数据对象

335 int rowStart = firstRowNum + 1;336 int rowEnd =sheet.getPhysicalNumberOfRows();337 for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {338 Row row =sheet.getRow(rowNum);339

340 if (null ==row) {341 continue;342 }343

344 Student resultData =convertRowToData(row);345 if (null ==resultData) {346 logger.warning("第 " + row.getRowNum() + "行数据不合法，已忽略！");347 continue;348 }349 resultDataList.add(resultData);350 }351 }352

353 returnresultDataList;354 }355

356 /**

357 * 将单元格内容转换为字符串358 *@paramcell359 *@return

360 */

361 private staticString convertCellValueToString(Cell cell) {362 if(cell==null){363 return null;364 }365 String returnValue = null;366 switch(cell.getCellType()) {367 case NUMERIC: //数字

368 Double doubleValue =cell.getNumericCellValue();369

370 //格式化科学计数法，取一位整数

371 DecimalFormat df = new DecimalFormat("0");372 returnValue =df.format(doubleValue);373 break;374 case STRING: //字符串

375 returnValue =cell.getStringCellValue();376 break;377 case BOOLEAN: //布尔

378 Boolean booleanValue =cell.getBooleanCellValue();379 returnValue =booleanValue.toString();380 break;381 case BLANK: //空值

382 break;383 case FORMULA: //公式

384 returnValue =cell.getCellFormula();385 break;386 case ERROR: //故障

387 break;388 default:389 break;390 }391 returnreturnValue;392 }393

394 /**

395 * 提取每一行中需要的数据，构造成为一个结果数据对象396 *397 * 当该行中有单元格的数据为空或不合法时，忽略该行的数据398 *399 *@paramrow 行数据400 *@return解析后的行数据对象，行数据错误时返回null401 */

402 private staticStudent convertRowToData(Row row) {403 Student resultData = newStudent();404

405 Cell cell;406 int cellNum = 0;407 //获取第一列

408 cell = row.getCell(0);409 String id =convertCellValueToString(cell);410 resultData.setId(id);411 //第二列

412 cell = row.getCell(1);413 String name =convertCellValueToString(cell);414 resultData.setName(name);415 //第三列

416 cell = row.getCell(2);417 String sutdnetNo =convertCellValueToString(cell);418 resultData.setStudentNo(sutdnetNo);419

420 returnresultData;421 }422

423 }424

425

426

427

428 ==》》》》导出表格429

430 PoiExportExcelUtil.java文件431 packagecodedna616.demo.commom;432

433 importjava.io.IOException;434 importjava.io.OutputStream;435 importjava.lang.reflect.Field;436 importjava.lang.reflect.InvocationTargetException;437 importjava.lang.reflect.Method;438 importjava.text.SimpleDateFormat;439 importjava.util.Collection;440 importjava.util.Date;441 importjava.util.Iterator;442 importjava.util.regex.Matcher;443 importjava.util.regex.Pattern;444

445 //import org.apache.commons.lang3.StringUtils;

446 importorg.apache.poi.hssf.usermodel.HSSFCell;447 importorg.apache.poi.hssf.usermodel.HSSFCellStyle;448 importorg.apache.poi.hssf.usermodel.HSSFFont;449 importorg.apache.poi.hssf.usermodel.HSSFRichTextString;450 importorg.apache.poi.hssf.usermodel.HSSFRow;451 importorg.apache.poi.hssf.usermodel.HSSFSheet;452 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;453 importorg.apache.poi.hssf.util.HSSFColor;454 importorg.apache.poi.xssf.usermodel.XSSFCell;455 importorg.apache.poi.xssf.usermodel.XSSFCellStyle;456 importorg.apache.poi.xssf.usermodel.XSSFColor;457 importorg.apache.poi.xssf.usermodel.XSSFFont;458 importorg.apache.poi.xssf.usermodel.XSSFRichTextString;459 importorg.apache.poi.xssf.usermodel.XSSFRow;460 importorg.apache.poi.xssf.usermodel.XSSFSheet;461 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;462

463

464 /**

465 * 导出Excel466 *467 *@param468 *@author

469 */

470 public class PoiExportExcelUtil{471

472 //2007 版本以上 最大支持1048576行

473 public final static String EXCEl_FILE_2007 = "2007";474 //2003 版本 最大支持65536 行

475 public final static String EXCEL_FILE_2003 = "2003";476

477 /**

478 *

479 * 导出无头部标题行Excel
480 * 时间格式默认：yyyy-MM-dd hh:mm:ss
481 *

482 *483 *@paramtitle 表格标题484 *@paramdataset 数据集合485 *@paramout 输出流486 *@paramversion 2003 或者 2007，不传时默认生成2003版本487 */

488 public void exportExcel(String title, Collectiondataset, OutputStream out, String version) {489 if (Utils.isNullOrEmpty(version) ||EXCEL_FILE_2003.equals(version.trim())) {490 exportExcel2003(title, null, dataset, out, "yyyy-MM-dd HH:mm:ss");491 } else{492 exportExcel2007(title, null, dataset, out, "yyyy-MM-dd HH:mm:ss");493 }494 }495

496 /**

497 *

498 * 导出带有头部标题行的Excel
499 * 时间格式默认：yyyy-MM-dd hh:mm:ss
500 *

501 *502 *@paramtitle 表格标题503 *@paramheaders 头部标题集合504 *@paramdataset 数据集合505 *@paramout 输出流506 *@paramversion 2003 或者 2007，不传时默认生成2003版本507 */

508 public void exportExcel(String title, String[] headers, Collectiondataset, OutputStream out, String version) {509 if (Utils.isNullOrEmpty(version) ||EXCEL_FILE_2003.equals(version.trim())) {510 exportExcel2003(title, headers, dataset, out, "yyyy-MM-dd HH:mm:ss");511 } else{512 exportExcel2007(title, headers, dataset, out, "yyyy-MM-dd HH:mm:ss");513 }514 }515

516 /**

517 *

518 * 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中
519 * 此版本生成2007以上版本的文件 (文件后缀：xlsx)520 *

521 *522 *@paramtitle 表格标题名523 *@paramheaders 表格头部标题集合524 *@paramdataset 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的525 * JavaBean属性的数据类型有基本数据类型及String,Date526 *@paramout 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中527 *@parampattern 如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"528 */

529 @SuppressWarnings({"unchecked", "rawtypes"})530 public void exportExcel2007(String title, String[] headers, Collectiondataset, OutputStream out, String pattern) {531 //声明一个工作薄

532 XSSFWorkbook workbook = newXSSFWorkbook();533 //生成一个表格

534 XSSFSheet sheet =workbook.createSheet(title);535 //设置表格默认列宽度为15个字节

536 sheet.setDefaultColumnWidth(20);537 //生成一个样式

538 XSSFCellStyle style =workbook.createCellStyle();539 //设置这些样式

540 style.setFillForegroundColor(newXSSFColor(java.awt.Color.gray));541 //style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);542 //

543 //style.setBorderBottom(XSSFCellStyle.BORDER_THIN);544 //style.setBorderLeft(XSSFCellStyle.BORDER_THIN);545 //style.setBorderRight(XSSFCellStyle.BORDER_THIN);546 //style.setBorderTop(XSSFCellStyle.BORDER_THIN);547 //style.setAlignment(XSSFCellStyle.ALIGN_CENTER);548 //生成一个字体

549 XSSFFont font =workbook.createFont();550 //font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);

551 font.setFontName("宋体");552 font.setColor(newXSSFColor(java.awt.Color.BLACK));553 font.setFontHeightInPoints((short) 11);554 //把字体应用到当前的样式

555 style.setFont(font);556 //生成并设置另一个样式

557 XSSFCellStyle style2 =workbook.createCellStyle();558 style2.setFillForegroundColor(newXSSFColor(java.awt.Color.WHITE));559 //style2.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);560 //style2.setBorderBottom(XSSFCellStyle.BORDER_THIN);561 //style2.setBorderLeft(XSSFCellStyle.BORDER_THIN);562 //style2.setBorderRight(XSSFCellStyle.BORDER_THIN);563 //style2.setBorderTop(XSSFCellStyle.BORDER_THIN);564 //style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);565 //style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);566 //生成另一个字体

567 XSSFFont font2 =workbook.createFont();568 //font2.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);569 //把字体应用到当前的样式

570 style2.setFont(font2);571

572 //产生表格标题行

573 XSSFRow row = sheet.createRow(0);574 XSSFCell cellHeader;575 for (int i = 0; i < headers.length; i++) {576 cellHeader =row.createCell(i);577 cellHeader.setCellStyle(style);578 cellHeader.setCellValue(newXSSFRichTextString(headers[i]));579 }580

581 //遍历集合数据，产生数据行

582 Iterator it =dataset.iterator();583 int index = 0;584 T t;585 Field[] fields;586 Field field;587 XSSFRichTextString richString;588 Pattern p = Pattern.compile("^//d+(//.//d+)?$");589 Matcher matcher;590 String fieldName;591 String getMethodName;592 XSSFCell cell;593 Class tCls;594 Method getMethod;595 Object value;596 String textValue;597 SimpleDateFormat sdf = newSimpleDateFormat(pattern);598 while(it.hasNext()) {599 index++;600 row =sheet.createRow(index);601 t =(T) it.next();602 //利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值

603 fields =t.getClass().getDeclaredFields();604 for (int i = 0; i < fields.length; i++) {605 cell =row.createCell(i);606 cell.setCellStyle(style2);607 field =fields[i];608 fieldName =field.getName();609 getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()610 + fieldName.substring(1);611 try{612 tCls =t.getClass();613 getMethod = tCls.getMethod(getMethodName, newClass[]{});614 value = getMethod.invoke(t, newObject[]{});615 //判断值的类型后进行强制类型转换

616 textValue = null;617 if (value instanceofInteger) {618 cell.setCellValue((Integer) value);619 } else if (value instanceofFloat) {620 textValue =String.valueOf((Float) value);621 cell.setCellValue(textValue);622 } else if (value instanceofDouble) {623 textValue =String.valueOf((Double) value);624 cell.setCellValue(textValue);625 } else if (value instanceofLong) {626 cell.setCellValue((Long) value);627 }628 if (value instanceofBoolean) {629 textValue = "是";630 if (!(Boolean) value) {631 textValue = "否";632 }633 } else if (value instanceofDate) {634 textValue =sdf.format((Date) value);635 } else{636 //其它数据类型都当作字符串简单处理

637 if (value != null) {638 textValue =value.toString();639 }640 }641 if (textValue != null) {642 matcher =p.matcher(textValue);643 if(matcher.matches()) {644 //是数字当作double处理

645 cell.setCellValue(Double.parseDouble(textValue));646 } else{647 richString = newXSSFRichTextString(textValue);648 cell.setCellValue(richString);649 }650 }651 } catch(SecurityException e) {652 e.printStackTrace();653 } catch(NoSuchMethodException e) {654 e.printStackTrace();655 } catch(IllegalArgumentException e) {656 e.printStackTrace();657 } catch(IllegalAccessException e) {658 e.printStackTrace();659 } catch(InvocationTargetException e) {660 e.printStackTrace();661 } finally{662 //清理资源

663 }664 }665 }666 try{667 workbook.write(out);668 } catch(IOException e) {669 e.printStackTrace();670 }671 }672

673

674 /**

675 *

676 * 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中
677 * 此方法生成2003版本的excel,文件名后缀：xls
678 *

679 *680 *@paramtitle 表格标题名681 *@paramheaders 表格头部标题集合682 *@paramdataset 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的683 * JavaBean属性的数据类型有基本数据类型及String,Date684 *@paramout 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中685 *@parampattern 如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"686 */

687 @SuppressWarnings({"unchecked", "rawtypes"})688 public void exportExcel2003(String title, String[] headers, Collectiondataset, OutputStream out, String pattern) {689 //声明一个工作薄

690 HSSFWorkbook workbook = newHSSFWorkbook();691 //生成一个表格

692 HSSFSheet sheet =workbook.createSheet(title);693 //设置表格默认列宽度为15个字节

694 sheet.setDefaultColumnWidth(20);695 //生成一个样式

696 HSSFCellStyle style =workbook.createCellStyle();697 //设置这些样式698 //style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);699 //style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);700 //style.setBorderBottom(HSSFCellStyle.BORDER_THIN);701 //style.setBorderLeft(HSSFCellStyle.BORDER_THIN);702 //style.setBorderRight(HSSFCellStyle.BORDER_THIN);703 //style.setBorderTop(HSSFCellStyle.BORDER_THIN);704 //style.setAlignment(HSSFCellStyle.ALIGN_CENTER);705 //生成一个字体

706 HSSFFont font =workbook.createFont();707 //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

708 font.setFontName("宋体");709 //font.setColor(HSSFColor.WHITE.index);

710 font.setFontHeightInPoints((short) 11);711 //把字体应用到当前的样式

712 style.setFont(font);713 //生成并设置另一个样式

714 HSSFCellStyle style2 =workbook.createCellStyle();715 //style2.setFillForegroundColor(HSSFColor.WHITE.index);716 //style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);717 //style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);718 //style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);719 //style2.setBorderRight(HSSFCellStyle.BORDER_THIN);720 //style2.setBorderTop(HSSFCellStyle.BORDER_THIN);721 //style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);722 //style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);723 //生成另一个字体

724 HSSFFont font2 =workbook.createFont();725 //font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);726 //把字体应用到当前的样式

727 style2.setFont(font2);728

729 //产生表格标题行

730 HSSFRow row = sheet.createRow(0);731 HSSFCell cellHeader;732 for (int i = 0; i < headers.length; i++) {733 cellHeader =row.createCell(i);734 cellHeader.setCellStyle(style);735 cellHeader.setCellValue(newHSSFRichTextString(headers[i]));736 }737

738 //遍历集合数据，产生数据行

739 Iterator it =dataset.iterator();740 int index = 0;741 T t;742 Field[] fields;743 Field field;744 HSSFRichTextString richString;745 Pattern p = Pattern.compile("^//d+(//.//d+)?$");746 Matcher matcher;747 String fieldName;748 String getMethodName;749 HSSFCell cell;750 Class tCls;751 Method getMethod;752 Object value;753 String textValue;754 SimpleDateFormat sdf = newSimpleDateFormat(pattern);755 while(it.hasNext()) {756 index++;757 row =sheet.createRow(index);758 t =(T) it.next();759 //利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值

760 fields =t.getClass().getDeclaredFields();761 for (int i = 0; i < fields.length; i++) {762 cell =row.createCell(i);763 cell.setCellStyle(style2);764 field =fields[i];765 fieldName =field.getName();766 getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()767 + fieldName.substring(1);768 try{769 tCls =t.getClass();770 getMethod = tCls.getMethod(getMethodName, newClass[]{});771 value = getMethod.invoke(t, newObject[]{});772 //判断值的类型后进行强制类型转换

773 textValue = null;774 if (value instanceofInteger) {775 cell.setCellValue((Integer) value);776 } else if (value instanceofFloat) {777 textValue =String.valueOf((Float) value);778 cell.setCellValue(textValue);779 } else if (value instanceofDouble) {780 textValue =String.valueOf((Double) value);781 cell.setCellValue(textValue);782 } else if (value instanceofLong) {783 cell.setCellValue((Long) value);784 }785 if (value instanceofBoolean) {786 textValue = "是";787 if (!(Boolean) value) {788 textValue = "否";789 }790 } else if (value instanceofDate) {791 textValue =sdf.format((Date) value);792 } else{793 //其它数据类型都当作字符串简单处理

794 if (value != null) {795 textValue =value.toString();796 }797 }798 if (textValue != null) {799 matcher =p.matcher(textValue);800 if(matcher.matches()) {801 //是数字当作double处理

802 cell.setCellValue(Double.parseDouble(textValue));803 } else{804 richString = newHSSFRichTextString(textValue);805 cell.setCellValue(richString);806 }807 }808 } catch(SecurityException e) {809 e.printStackTrace();810 } catch(NoSuchMethodException e) {811 e.printStackTrace();812 } catch(IllegalArgumentException e) {813 e.printStackTrace();814 } catch(IllegalAccessException e) {815 e.printStackTrace();816 } catch(InvocationTargetException e) {817 e.printStackTrace();818 } finally{819 //清理资源

820 }821 }822 }823 try{824 workbook.write(out);825 } catch(IOException e) {826 e.printStackTrace();827 }catch(Exception e) {828 e.printStackTrace();829 }830 }831 }