Poi工具类快速生成Ecxel(升级版)
1.导入依赖
<dependencies> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>4.1.2</version> </dependency> </dependencies>
2.处理代码（写的有点拉跨）
package com.sjyf.gym.utils; import com.sjyf.gym.annotation.Excel; import com.sjyf.gym.ex.BusinessException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import javax.servlet.http.HttpServletResponse; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.time.LocalDateTime; import java.time.ZoneId; import java.time.ZonedDateTime; import java.util.*; /** * @author 何杰 * @date 2020年5月20日 */ public class ExcelUtil { /** * @Title: createEcxel * @Description: 通过属性组创建Excel，序号自动生成 * @param title * 标题 * @param property * 对象的属性名 * @param coll * 需要生成表格的对象 * @param suffix * 后缀名 xls || xlsx * @throws SecurityException * @throws NoSuchFieldException * @throws IllegalAccessException * @throws IllegalArgumentException * @throws IOException */ public static SXSSFWorkbook createEcxelByGroups(String[] title, String[] property, List<?> coll, PoiType suffix) { if (suffix == null) { throw new RuntimeException("请选择文件后缀名"); } SXSSFWorkbook wb = null; if (suffix == PoiType.XLS) { wb = new SXSSFWorkbook(); } else { wb = new SXSSFWorkbook(); } Sheet st = wb.createSheet(); // 创建第一行 Row rowone = st.createRow(0); // 设置标题 for (int i = 0; i <= title.length; i++) { Cell cell = rowone.createCell(i); if (i == 0) { cell.setCellValue("序号"); continue; } cell.setCellValue(title[i - 1]); } // 填充值 try { for (int i = 0; i < coll.size(); i++) { Class<? extends Object> clazz = coll.get(i).getClass(); Row row = st.createRow(i + 1); for (int j = 0; j <= property.length; j++) { // 创建j++列 Cell cell = row.createCell(j); if (j == 0) { cell.setCellValue(i + 1); continue; } // 判断要获取的属性名是否包含. if(property[j - 1].indexOf(".") == -1){ // 不包含 Field field = null; try { field = coll.get(i).getClass() .getDeclaredField(property[j - 1]); } catch (Exception e) { field = clazz.getSuperclass().getDeclaredField(property[j - 1]); } Method method = clazz.getMethod("get" + toUpperCase(field.getName())); Object invoke = method.invoke(coll.get(i)); // 设置对象的访问权限，保证对private的属性的访问 String classType = field.getType().toString(); if (classType.equals("class java.util.Date")){ String format = dateFormat(invoke); cell.setCellValue(format == null ? "" : format); continue; } if(classType.equals("class java.time.LocalDateTime")){ Date date = localDateTimeToDate((LocalDateTime) invoke); String format = dateFormat(date); cell.setCellValue(format == null ? "" : format); continue; } Excel excel = field.getAnnotation(Excel.class); if(excel != null){ String value = getValue(excel.value(), invoke + ""); cell.setCellValue(value); }else{ cell.setCellValue(invoke == null ? "" : invoke + ""); } }else{ // 包含 Field field = coll.get(i).getClass() .getDeclaredField(property[j - 1].substring(0, property[j - 1].lastIndexOf("."))); // 设置对象的访问权限，保证对private的属性的访问 field.setAccessible(true); Object o = field.get(coll.get(i)); // 判断是否属于基本数据类型，或是否包含. if(!typeDict(o) && property[j - 1].indexOf(".") != -1){ String value = getFieldValue(o, property[j - 1]); cell.setCellValue(value == null ? "" : value + "" ); continue; } cell.setCellValue(o == null ? "" : o + ""); } } } } catch (Exception e) { e.printStackTrace(); throw new BusinessException("导出错误，请联系管理员"); } return wb; } /** * * @Description: 获取字段值 * @param @param obj 对象 * @param @param field 获取的字段名 * @param @return * @return String * @throws */ public static String getFieldValue(Object obj, String field){ // 截取字段名，只支持一级，obj -> obj， try { String fname = field.substring(field.lastIndexOf(".") + 1,field.length()); // 创建j++列 Field d = obj.getClass().getDeclaredField(fname); // 设置对象的访问权限，保证对private的属性的访问 d.setAccessible(true); Object o = d.get(obj); return o + ""; } catch (Exception e) { e.printStackTrace(); } return null; } /** * 判断是否是常用基本数据类型 */ public static boolean typeDict(Object obj){ Class<?>[] clazzs = {int.class,short.class,double.class, float.class,byte.class,long.class,char.class,boolean.class, String.class,Double.class,Float.class,Integer.class,Long.class, Short.class,Boolean.class,Character.class,Byte.class,BigDecimal.class}; for (Class<?> c : clazzs) { if(obj.getClass().isAssignableFrom(c)){ return true; } } return false; } public static String toUpperCase(String name){ return name.substring(0,1).toUpperCase() + name.substring(1,name.length()); } public static void main(String[] args) { toUpperCase(null); } public enum PoiType { XLS, XLSX; } public static Date localDateTimeToDate(LocalDateTime localDateTime){ ZoneId zoneId = ZoneId.systemDefault(); ZonedDateTime zdt = localDateTime.atZone(zoneId); return Date.from(zdt.toInstant()); } /** * 导出 */ public static void exportExcel(SXSSFWorkbook wb, String fileName){ OutputStream out = null; try { HttpServletResponse response = HttpContextUtils.getHttpServletResponse(); response.reset(); response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO-8859-1") + ".xlsx"); out = response.getOutputStream(); wb.write(out); } catch (Exception e) { throw new BusinessException("导出Excel失败，请联系管理员！"); } finally { if (wb != null) { try { wb.close(); } catch (IOException e1) { e1.printStackTrace(); } } if (out != null) { try { out.close(); } catch (IOException e1) { e1.printStackTrace(); } } } } public static String dateFormat(Object obj){ if(Objects.isNull(obj)){ return ""; } return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(obj); } /** * 获取字段的字典值 */ public static Map<String, String> getDict(String dictValues) { // String dictValues = "1=待学员确认,2=待教练确认,3=待销课,4=已销课待评价,5=已销课已评价,6=已退还,7=已取消"; String[] keyAndValues = dictValues.split(","); Map<String, String> values = new HashMap<>(keyAndValues.length); for (String value : keyAndValues) { String[] key = value.split("="); values.put(key[0], key[1]); } return values; } public static String getValue(String dictValues, String key){ Map<String, String> dict = getDict(dictValues); if (dict == null || dict.size() == 0){ return ""; } return dict.get(key); } public static XSSFSheet getSheet() { FileInputStream fileInputStream = null; try { fileInputStream = new FileInputStream("C:\\Users\\qian\\Desktop\\私教模板(1)(2).xlsx"); XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream); XSSFSheet sheet = sheets.getSheetAt(0); return sheet; } catch (Exception e) { e.printStackTrace(); } return null; } }
3.所使用的注解类
package com.sjyf.gym.annotation; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; @Target({ElementType.FIELD}) @Retention(RetentionPolicy.RUNTIME) public @interface Excel { String value() default ""; }
4.注解类的使用方法
首先说明为什么要使用注解，因为有些类是int值，当然你可以在数据库用case when去处理，使用格式（1=正常,2=过期,3=停用）
程序会按照相应的int值匹配，去设置其相应的注释值。
@Excel("1=正常,2=过期,3=停用")
private Integer status;
5.使用（就是这么简单，当然还能更简单，可以使用注解，然后反射得到注解的值和key，自行发挥吧！）
List<ContractPersonalExportVo> personals = contractPersonalMapper.exportExcel(); String[] titles = {"门店", "虚拟会员卡号", "会员姓名", "手机号码", "性别", "身份证号", "课程名称", "总课时", "剩余课时", "总金额", "销售员", "状态", "单价", "合同编号", "创建时间"}; String[] propertys = {"storeName", "virtualCardNum", "userName", "phone", "sex", "idCard", "courseName", "totalNum", "surplusNum", "totalAmt", "nickName", "status", "avgPrice", "code", "createTime"}; SXSSFWorkbook workbook = ExcelUtil.createEcxelByGroups(titles, propertys, personals, ExcelUtil.PoiType.XLSX); ExcelUtil.exportExcel(workbook, "业务合同");
