Java Annotation 应用 -- 导出Excel表格
相关知识链接：
Introspector(内省)
POI
1.声明注解
package com.ciic.component.excel; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * */ @Retention(RetentionPolicy.RUNTIME) @Target(ElementType.FIELD) public @interface ExcelAnnotation { // excel导出时标题显示的名字，如果没有设置Annotation属性，将不会被导出和导入 public String exportName(); }
2.应用注解
package com.ciic.history.entity; import com.ciic.component.excel.ExcelAnnotation; import com.ciic.history.common.ExportBase; //客户一揽子表 public class EsinnerLimeCustomerPreviewIndex extends ExportBase { @ExcelAnnotation(exportName = "客户名称") private String imscustomername; @ExcelAnnotation(exportName = "客户编号") private String imscustomercode; @ExcelAnnotation(exportName = "合同方式") private long imscontracttypea; @ExcelAnnotation(exportName = "月服务费") private String serviceimstotalfee; @ExcelAnnotation(exportName = "雇员人数") private long employeecount; @ExcelAnnotation(exportName = "应收金额") private String imstotalfee; @ExcelAnnotation(exportName = "实收金额") private String doneimstotalfee; @ExcelAnnotation(exportName = "应付金额") private String imssocialinsurancetfee; @ExcelAnnotation(exportName = "实付金额") private String dtlimssocialinsurancetfee; @ExcelAnnotation(exportName = "最后修改日期") private String modifieddate; @ExcelAnnotation(exportName = "客户简称") private String imscustomershort; @ExcelAnnotation(exportName = "合作方式") private long imscontracttypeb; @ExcelAnnotation(exportName = "客户经理") private String imscustomerclerk; @ExcelAnnotation(exportName = "未付款日期") private String unimspaynoticemonth; @ExcelAnnotation(exportName = "已交付日期") private String doneimspaynoticemonth; getter() setter() }
3.解析注解
3.1 获取数据
public void exportCustomerPreview(EsinnerLimeCustomerPreviewIndex customerPreview, HttpServletResponse response)throws Exception{ JsonEntity entity =XAServiceL.customerPreviewSearch(customerPreview,customerPreview.getPage(),customerPreview.getRows()); ExcelExport excelExport= new ExcelExport(); response.reset(); String fileName=""; if(StringUtils.isBlank(customerPreview.getExcelName())){ fileName="客户一揽子表/第"+customerPreview.getPage()+"页.xls"; }else{ fileName=customerPreview.getExcelName()+"/第"+customerPreview.getPage()+"页.xls"; } response.setContentType("application/form-data;charset=UTF-8"); response.addHeader("Content-Disposition", "attachment;filename=\"" + new String(fileName.getBytes("UTF-8"), "UTF-8") + "\""); System.out.println(Arrays.toString(entity.getRows().toArray())); List<EsinnerLimeCustomerPreviewIndex> outExcel=new ArrayList<EsinnerLimeCustomerPreviewIndex>(); for(int i=0;i<entity.getRows().size();i++){ outExcel.add(MapBeanConvert.toBean(EsinnerLimeCustomerPreviewIndex.class,(Map) entity.getRows().get(i))); } excelExport.exportExcel(customerPreview.getExcelName(),outExcel,response.getOutputStream()); }
3.2 解析注解
/** * 将一个 Map 对象转化为一个 JavaBean * * @param clazz 要转化的类型 * @param map 包含属性值的 map * @return 转化出来的 JavaBean 对象 * @throws IntrospectionException 如果分析类属性失败 * @throws IllegalAccessException 如果实例化 JavaBean 失败 * @throws InstantiationException 如果实例化 JavaBean 失败 * @throws InvocationTargetException 如果调用属性的 setter 方法失败 */ @SuppressWarnings("rawtypes") public static <T> T toBean(Class<T> clazz, Map map) { T obj = null; String name = ""; try { BeanInfo beanInfo = Introspector.getBeanInfo(clazz); obj = clazz.newInstance(); // 创建 JavaBean 对象 // 给 JavaBean 对象的属性赋值 PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors(); for (int i = 0; i < propertyDescriptors.length; i++) { PropertyDescriptor descriptor = propertyDescriptors[i]; String propertyName = descriptor.getName(); name = propertyName; if (map.containsKey(propertyName)) { // 下面一句可以 try 起来，这样当一个属性赋值失败的时候就不会影响其他属性赋值。 Object value = map.get(propertyName); if ("".equals(value)) { value = null; } Object[] args = new Object[1]; args[0] = value; try { descriptor.getWriteMethod().invoke(obj, args); } catch (InvocationTargetException e) { System.out.println("字段映射失败"); } } } } catch (IllegalAccessException e) { System.out.println("实例化 JavaBean 失败"); } catch (IntrospectionException e) { System.out.println("分析类属性失败"); } catch (IllegalArgumentException e) { // e.printStackTrace(); System.err.println(name); System.out.println("映射错误"); } catch (InstantiationException e) { System.out.println("实例化 JavaBean 失败"); } return (T) obj; }
3.3 导出Excel
package com.ciic.component.excel; import org.apache.poi.hssf.usermodel.*; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.*; /** * */ public class ExcelExport<T> { /** * @param title 标题 * @param dataset 集合 * @param out 输出流 */ public void exportExcel(String title, Collection<T> dataset, OutputStream out) { // 声明一个工作薄 try { //首先检查数据看是否是正确的 Iterator<T> its = dataset.iterator(); if (dataset == null || !its.hasNext() || title == null || out == null) { throw new Exception("传入的数据不对！"); } T ts = (T) its.next(); HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(15); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置标题样式 // style = ExcelStyle.setHeadStyle(workbook, style); // // 生成并设置主体样式 // HSSFCellStyle style2 = workbook.createCellStyle(); // style2 = ExcelStyle.setbodyStyle(workbook, style2); // 得到所有字段 Field filed[] = ts.getClass().getDeclaredFields(); // 标题 List<String> exportfieldtile = new ArrayList<String>(); // 导出的字段 List<String> fiedName = new ArrayList<String>(); // 遍历整个filed for (int i = 0; i < filed.length; i++) { Field f = filed[i]; ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class); // 如果设置了annottion if (exa != null) { String exprot = exa.exportName(); // 添加到标题 exportfieldtile.add(exprot); // 添加到需要导出的字段 fiedName.add(f.getName()); } } // 产生表格标题行 HSSFRow row = sheet.createRow(0); for (int i = 0; i < exportfieldtile.size(); i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString( exportfieldtile.get(i)); cell.setCellValue(text); } Iterator<T> it = dataset.iterator(); int index = 0; // 循环整个集合 while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); for (int k = 0; k < fiedName.size(); k++) { HSSFCell cell = row.createCell(k); String fieldname = fiedName.get(k); String getMethodName = "get" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1); Class tCls = t.getClass(); Method getMethod = tCls.getMethod(getMethodName, new Class[]{}); Object value = getMethod.invoke(t, new Object[]{}); String textValue = getValue(value); HSSFRichTextString richString = new HSSFRichTextString( textValue); cell.setCellValue(richString); } } workbook.write(out); } catch (Exception e) { e.printStackTrace(); } } /** * @param title 标题 * @param dataset 集合 */ public File exportExcel(String title, Collection<T> dataset) { OutputStream out = null; File file = null; // 声明一个工作薄 try { //首先检查数据看是否是正确的 Iterator<T> its = dataset.iterator(); if (dataset == null || !its.hasNext() || title == null) { throw new Exception("传入的数据不对！"); } T ts = (T) its.next(); HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(15); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置标题样式 // style = ExcelStyle.setHeadStyle(workbook, style); // // 生成并设置主体样式 // HSSFCellStyle style2 = workbook.createCellStyle(); // style2 = ExcelStyle.setbodyStyle(workbook, style2); // 得到所有字段 Field filed[] = ts.getClass().getDeclaredFields(); // 标题 List<String> exportfieldtile = new ArrayList<String>(); // 导出的字段 List<String> fiedName = new ArrayList<String>(); // 遍历整个filed for (int i = 0; i < filed.length; i++) { Field f = filed[i]; ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class); // 如果设置了annottion if (exa != null) { String exprot = exa.exportName(); // 添加到标题 exportfieldtile.add(exprot); // 添加到需要导出的字段 fiedName.add(f.getName()); } } // 产生表格标题行 HSSFRow row = sheet.createRow(0); for (int i = 0; i < exportfieldtile.size(); i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString( exportfieldtile.get(i)); cell.setCellValue(text); } Iterator<T> it = dataset.iterator(); int index = 0; // 循环整个集合 while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); for (int k = 0; k < fiedName.size(); k++) { HSSFCell cell = row.createCell(k); String fieldname = fiedName.get(k); String getMethodName = "get" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1); Class tCls = t.getClass(); Method getMethod = tCls.getMethod(getMethodName, new Class[]{}); Object value = getMethod.invoke(t, new Object[]{}); String textValue = getValue(value); HSSFRichTextString richString = new HSSFRichTextString( textValue); cell.setCellValue(richString); } } file = new File("/tmp/testOne.xls"); out = new FileOutputStream(file); workbook.write(out); } catch (Exception e) { e.printStackTrace(); } finally { try { if (out != null) { out.close(); } } catch (IOException e) { e.printStackTrace(); } } return file; } private String getValue(Object value) { String textValue = ""; if (value == null) return textValue; if (value instanceof Boolean) { boolean bValue = (Boolean) value; textValue = "是"; if (!bValue) { textValue = "否"; } } else if (value instanceof Date) { Date date = (Date) value; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); textValue = sdf.format(date); } else textValue = value.toString(); return textValue; } }
啦啦啦
啦啦啦
