java中使用poi导出excel表格数据并且可以手动修改导出路径
在我们开发项目中，很多时候会提出这样的需求：将前端的某某数据以excel表格导出，今天就给大家写一个简单的模板。
这里我们选择使用poi导出excel:
第一步：导入需要的jar包到 lib 文件夹下
jar包下载路径：http://download.csdn.net/download/pumpkin09/7077011
第二步：添加poi导出工具类
1 package com.yjd.admin.util; 2 3 import java.io.IOException; 4 import java.io.OutputStream; 5 import java.lang.reflect.Field; 6 import java.lang.reflect.InvocationTargetException; 7 import java.lang.reflect.Method; 8 import java.text.SimpleDateFormat; 9 import java.util.Collection; 10 import java.util.Date; 11 import java.util.Iterator; 12 import java.util.regex.Matcher; 13 import java.util.regex.Pattern; 14 15 16 import org.apache.poi.hssf.usermodel.HSSFCell; 17 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 18 import org.apache.poi.hssf.usermodel.HSSFClientAnchor; 19 import org.apache.poi.hssf.usermodel.HSSFComment; 20 import org.apache.poi.hssf.usermodel.HSSFFont; 21 import org.apache.poi.hssf.usermodel.HSSFPatriarch; 22 import org.apache.poi.hssf.usermodel.HSSFRichTextString; 23 import org.apache.poi.hssf.usermodel.HSSFRow; 24 import org.apache.poi.hssf.usermodel.HSSFSheet; 25 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 26 import org.apache.poi.hssf.util.HSSFColor; 27 28 /** 29 * 利用开源组件POI3.0.2动态导出EXCEL文档 转载时请保留以下信息，注明出处！ 30 * 31 * @author leno 32 * @version v1.0 33 * @param <T> 34 * 应用泛型，代表任意一个符合javabean风格的类 35 * 注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx() 36 * byte[]表jpg格式的图片数据 37 */ 38 public class ExportExcel<T> { 39 public void exportExcel(Collection<T> dataset, OutputStream out) { 40 exportExcel("测试POI导出EXCEL文档", null, dataset, out, "yyyy-MM-dd"); 41 } 42 43 public void exportExcel(String[] headers, Collection<T> dataset, 44 OutputStream out) { 45 exportExcel("测试POI导出EXCEL文档", headers, dataset, out, "yyyy-MM-dd"); 46 } 47 48 public void exportExcel(String[] headers, Collection<T> dataset, 49 OutputStream out, String pattern) { 50 exportExcel("测试POI导出EXCEL文档", headers, dataset, out, pattern); 51 } 52 53 /** 54 * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上 55 * 56 * @param title 57 * 表格标题名 58 * @param headers 59 * 表格属性列名数组 60 * @param dataset 61 * 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的 62 * javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据) 63 * @param out 64 * 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中 65 * @param pattern 66 * 如果有时间数据，设定输出格式。默认为"yyy-MM-dd" 67 */ 68 @SuppressWarnings("unchecked") 69 public void exportExcel(String title, String[] headers, 70 Collection<T> dataset, OutputStream out, String pattern) { 71 // 声明一个工作薄 72 HSSFWorkbook workbook = new HSSFWorkbook(); 73 // 生成一个表格 74 HSSFSheet sheet = workbook.createSheet(title); 75 // 设置表格默认列宽度为15个字节 76 sheet.setDefaultColumnWidth((short) 15); 77 // 生成一个样式 78 HSSFCellStyle style = workbook.createCellStyle(); 79 // 设置这些样式 80 style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); 81 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 82 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 83 style.setBorderLeft(HSSFCellStyle.BORDER_THIN); 84 style.setBorderRight(HSSFCellStyle.BORDER_THIN); 85 style.setBorderTop(HSSFCellStyle.BORDER_THIN); 86 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 87 // 生成一个字体 88 HSSFFont font = workbook.createFont(); 89 font.setColor(HSSFColor.VIOLET.index); 90 font.setFontHeightInPoints((short) 12); 91 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 92 // 把字体应用到当前的样式 93 style.setFont(font); 94 // 生成并设置另一个样式 95 HSSFCellStyle style2 = workbook.createCellStyle(); 96 style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); 97 style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 98 style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); 99 style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); 100 style2.setBorderRight(HSSFCellStyle.BORDER_THIN); 101 style2.setBorderTop(HSSFCellStyle.BORDER_THIN); 102 style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); 103 style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 104 // 生成另一个字体 105 HSSFFont font2 = workbook.createFont(); 106 font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); 107 // 把字体应用到当前的样式 108 style2.setFont(font2); 109 // 声明一个画图的顶级管理器 110 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); 111 // 定义注释的大小和位置,详见文档 112 HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 113 0, 0, 0, (short) 4, 2, (short) 6, 5)); 114 // 设置注释内容 115 comment.setString(new HSSFRichTextString("可以在POI中添加注释！")); 116 // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容. 117 comment.setAuthor("leno"); 118 // 产生表格标题行 119 HSSFRow row = sheet.createRow(0); 120 for (short i = 0; i < headers.length; i++) { 121 HSSFCell cell = row.createCell(i); 122 cell.setCellStyle(style); 123 HSSFRichTextString text = new HSSFRichTextString(headers[i]); 124 cell.setCellValue(text); 125 } 126 // 遍历集合数据，产生数据行 127 Iterator<T> it = dataset.iterator(); 128 int index = 0; 129 while (it.hasNext()) { 130 index++; 131 row = sheet.createRow(index); 132 T t = (T) it.next(); 133 // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值 134 Field[] fields = t.getClass().getDeclaredFields(); 135 for (short i = 0; i < fields.length; i++) { 136 HSSFCell cell = row.createCell(i); 137 cell.setCellStyle(style2); 138 Field field = fields[i]; 139 String fieldName = field.getName(); 140 String getMethodName = "get" 141 + fieldName.substring(0, 1).toUpperCase() 142 + fieldName.substring(1); 143 try { 144 Class tCls = t.getClass(); 145 Method getMethod = tCls.getMethod(getMethodName, 146 new Class[] {}); 147 Object value = getMethod.invoke(t, new Object[] {}); 148 // 判断值的类型后进行强制类型转换 149 String textValue = null; 150 // if (value instanceof Integer) { 151 // int intValue = (Integer) value; 152 // cell.setCellValue(intValue); 153 // } else if (value instanceof Float) { 154 // float fValue = (Float) value; 155 // textValue = new HSSFRichTextString( 156 // String.valueOf(fValue)); 157 // cell.setCellValue(textValue); 158 // } else if (value instanceof Double) { 159 // double dValue = (Double) value; 160 // textValue = new HSSFRichTextString( 161 // String.valueOf(dValue)); 162 // cell.setCellValue(textValue); 163 // } else if (value instanceof Long) { 164 // long longValue = (Long) value; 165 // cell.setCellValue(longValue); 166 // } 167 if (value instanceof Boolean) { 168 boolean bValue = (Boolean) value; 169 textValue = "男"; 170 if (!bValue) { 171 textValue = "女"; 172 } 173 } else if (value instanceof Date) { 174 Date date = (Date) value; 175 SimpleDateFormat sdf = new SimpleDateFormat(pattern); 176 textValue = sdf.format(date); 177 } else if (value instanceof byte[]) { 178 // 有图片时，设置行高为60px; 179 row.setHeightInPoints(60); 180 // 设置图片所在列宽度为80px,注意这里单位的一个换算 181 sheet.setColumnWidth(i, (short) (35.7 * 80)); 182 // sheet.autoSizeColumn(i); 183 byte[] bsValue = (byte[]) value; 184 HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 185 1023, 255, (short) 6, index, (short) 6, index); 186 anchor.setAnchorType(2); 187 patriarch.createPicture(anchor, workbook.addPicture( 188 bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG)); 189 } else { 190 // 其它数据类型都当作字符串简单处理 191 textValue = value.toString(); 192 } 193 // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成 194 if (textValue != null) { 195 Pattern p = Pattern.compile("^//d+(//.//d+)?$"); 196 Matcher matcher = p.matcher(textValue); 197 if (matcher.matches()) { 198 // 是数字当作double处理 199 cell.setCellValue(Double.parseDouble(textValue)); 200 } else { 201 HSSFRichTextString richString = new HSSFRichTextString( 202 textValue); 203 HSSFFont font3 = workbook.createFont(); 204 font3.setColor(HSSFColor.BLUE.index); 205 richString.applyFont(font3); 206 cell.setCellValue(richString); 207 } 208 } 209 } catch (SecurityException e) { 210 e.printStackTrace(); 211 } catch (NoSuchMethodException e) { 212 e.printStackTrace(); 213 } catch (IllegalArgumentException e) { 214 e.printStackTrace(); 215 } catch (IllegalAccessException e) { 216 e.printStackTrace(); 217 } catch (InvocationTargetException e) { 218 e.printStackTrace(); 219 } finally { 220 // 清理资源 221 } 222 } 223 } 224 try { 225 workbook.write(out); 226 } catch (IOException e) { 227 e.printStackTrace(); 228 } 229 } 230 231 }
第三步：由于我使用的的spring-mvc框架，所以在Controller调用上面的工具类ExportExcel
1 package com.yjd.admin.vo; 2 3 import java.util.Date; 4 5 public class P2pLoanPlanVo { 6 private Date repayDate; 7 private double repayAmount; 8 private Integer repayDays; 9 private Integer repayYqDays; 10 private Double lateAmount; 11 private String isPosPaid; 12 private String statusName; 13 private String isSysPay; 14 public Date getRepayDate() { 15 return repayDate; 16 } 17 public void setRepayDate(Date repayDate) { 18 this.repayDate = repayDate; 19 } 20 public double getRepayAmount() { 21 return repayAmount; 22 } 23 public void setRepayAmount(double repayAmount) { 24 this.repayAmount = repayAmount; 25 } 26 public Integer getRepayDays() { 27 return repayDays; 28 } 29 public void setRepayDays(Integer repayDays) { 30 this.repayDays = repayDays; 31 } 32 public Integer getRepayYqDays() { 33 return repayYqDays; 34 } 35 public void setRepayYqDays(Integer repayYqDays) { 36 this.repayYqDays = repayYqDays; 37 } 38 public Double getLateAmount() { 39 return lateAmount; 40 } 41 public void setLateAmount(Double lateAmount) { 42 this.lateAmount = lateAmount; 43 } 44 public String getIsPosPaid() { 45 return isPosPaid; 46 } 47 public void setIsPosPaid(String isPosPaid) { 48 this.isPosPaid = isPosPaid; 49 } 50 public String getStatusName() { 51 return statusName; 52 } 53 public void setStatusName(String statusName) { 54 this.statusName = statusName; 55 } 56 public String getIsSysPay() { 57 return isSysPay; 58 } 59 public void setIsSysPay(String isSysPay) { 60 this.isSysPay = isSysPay; 61 } 62 63 64 65 66 67 }
1 /** 2 * 导出excel数据 3 * @param id 4 * @param m 5 * @return 6 */ 7 @RequestMapping("/exportExcel") 8 public void exportExcel(@RequestParam("id") Integer id, Model m,HttpServletRequest req, HttpServletResponse resp) { 9 try { 10 11 ExportExcel<P2pLoanPlanVo> ex = new ExportExcel<P2pLoanPlanVo>(); 12 String[] headers = {"最迟还款日", "还款金额","剩余几天","逾期几天", "罚息","是否垫付","状态","是否发放收益"}; 13 List<P2pLoanPlanVo> dataset = new ArrayList<P2pLoanPlanVo>(); 14 List<P2pLoanPlan> plans = this.planService.getListByLoan(id); 15 for (int i = 0; i < plans.size(); i++) { 16 P2pLoanPlanVo p2pLoanPlanVo = new P2pLoanPlanVo(); 17 18 p2pLoanPlanVo.setRepayDate(plans.get(i).getRepayDate()); 19 p2pLoanPlanVo.setRepayAmount(plans.get(i).getRepayAmount()); 20 21 if(plans.get(i).getRepayDays() >= 0 && plans.get(i).getStatus() == 0){ 22 p2pLoanPlanVo.setRepayDays(plans.get(i).getRepayDays()); 23 }else{ 24 p2pLoanPlanVo.setRepayDays(0); 25 } 26 if(plans.get(i).getRepayDays() < 0 && plans.get(i).getStatus() == 0){ 27 p2pLoanPlanVo.setRepayYqDays(-plans.get(i).getRepayDays()); 28 }else{ 29 p2pLoanPlanVo.setRepayYqDays(0); 30 } 31 32 p2pLoanPlanVo.setLateAmount(plans.get(i).getLateAmount()); 33 String IsPosPaid = ""; 34 if(plans.get(i).getIsPosPaid()==true){ 35 IsPosPaid = "已垫付"; 36 }else{ 37 IsPosPaid = "未垫付"; 38 } 39 p2pLoanPlanVo.setIsPosPaid(IsPosPaid); 40 p2pLoanPlanVo.setStatusName(plans.get(i).getStatusName()); 41 String IsSysPay =""; 42 if(plans.get(i).getIsSysPay() == true){ 43 IsSysPay = "已发放收益"; 44 }else{ 45 IsSysPay = "未发放收益"; 46 } 47 p2pLoanPlanVo.setIsSysPay(IsSysPay); 48 49 dataset.add(p2pLoanPlanVo); 50 51 } 52 try { 53 req.setCharacterEncoding("UTF-8"); 54 resp.setCharacterEncoding("UTF-8"); 55 resp.setContentType("application/x-download"); 56 57 String filedisplay = "还款计划.xls"; 58 //防止文件名含有中文乱码 59 filedisplay = new String( filedisplay.getBytes("gb2312"), "ISO8859-1" ); 60 resp.setHeader("Content-Disposition", "attachment;filename="+ filedisplay); 61 62 OutputStream out = resp.getOutputStream(); 63 ex.exportExcel(headers, dataset, out); 64 out.close(); 65 } catch (FileNotFoundException e) { 66 e.printStackTrace(); 67 } catch (IOException e) { 68 e.printStackTrace(); 69 } 70 } catch (Exception e) { 71 Exceptions.getExceptionMsg(e, logger); 72 } 73 }
注意：此处ExportExcel<T>工具类中的泛型对应P2pLoanPlanVo这个类，P2pLoanPlanVo类中的属性要对应exportExcel（）接口中headers中每个值并且都要有值，不允许为空，若有不足的地方还望各位大神多多指点！
未经博主允许，请勿转载
