二次封装POI实现一行代码导出Excel

引入依赖包


org.apache.poipoi3.17
org.apache.poipoi-ooxml3.17



自定义Excel注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;/*** &＃64;author 易水●墨龙吟* &＃64;Description* &＃64;create 2019-02-25 13:18*/
&＃64;Target({ElementType.CONSTRUCTOR, ElementType.METHOD, ElementType.PARAMETER, ElementType.FIELD, ElementType.ANNOTATION_TYPE})
&＃64;Retention(RetentionPolicy.RUNTIME)
public &＃64;interface ExcelAttribute {// 字段字符码String name() default "";// 字段宽度int width() default 10;boolean sex() default false;}


配合反射和注解实现导出主要逻辑

import com.lehui.molong.config.excel.annotation.ExcelAttribute;
import com.lehui.molong.entity.excel.ApplyInfo;
import org.apache.poi.hssf.usermodel.*;import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** &＃64;author 易水●墨龙吟* &＃64;Description Excel导出* &＃64;create 2019-02-24 15:54*/
public class ExcelUtiles {/*** Excel后缀*/private final static String SUFFIX &＃61; "报名表.xls";/*** Excel导出* &＃64;param title* &＃64;param response* &＃64;param entityExcel* &＃64;param data*/public static void PrintToExcel(String title, HttpServletResponse response, Class entityExcel, List data) throws UnsupportedEncodingException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {// 实例化HSSFWorkbookHSSFWorkbook workbook &＃61; new HSSFWorkbook();// 创建一个Excel表单&＃xff0c;参数为sheet的名字HSSFSheet sheet &＃61; workbook.createSheet("sheet");Map map &＃61; setTitle(workbook, sheet, entityExcel);setData(sheet, data, map);downloadExcel(workbook, response, setFileName(title));}/*** 创建文件名* &＃64;param title* &＃64;return*/private static String setFileName (String title) throws UnsupportedEncodingException {String newFile &＃61; title &＃43; SUFFIX;// 防止出现文件名出现乱码return new String(newFile.getBytes("UTF-8"), "ISO-8859-1");}/*** 设置表头* &＃64;param workbook* &＃64;param sheet* &＃64;param entityExcel*/private static Map setTitle(HSSFWorkbook workbook, HSSFSheet sheet, Class entityExcel) {System.out.println("获取到&＃xff1a;" &＃43; entityExcel.getName() &＃43; "类");// 定义一个map存放表头的下标和数据信息Map titleMap &＃61; new HashMap<>();Field[] fs &＃61; entityExcel.getDeclaredFields();for (int i &＃61; 0; i map) throws InvocationTargetException, IllegalAccessException, NoSuchMethodException {// 表头为第0行int rowNum &＃61; 1;for (ApplyInfo entity : data) {// 反射获取实体的信息Class clazz &＃61; entity.getClass();Field[] fields &＃61; clazz.getDeclaredFields();HSSFRow row &＃61; sheet.createRow(rowNum);int index &＃61; 0;for(Field field : fields) {// 修改权限&＃xff0c;可以访问私有属性field.setAccessible(true);String attr &＃61; field.getName();// 调用方法的getXXXX 方法获取属性值Method method &＃61; clazz.getMethod("get" &＃43; attr.substring(0, 1).toUpperCase() &＃43; attr.substring(1));// 获取返回值类型String type &＃61; method.getAnnotatedReturnType().getType().getTypeName();Proprety proprety &＃61; map.get(index);// 判断类型&＃xff0c;返回相应的值if(type.equals("java.lang.String")) {row.createCell(index&＃43;&＃43;).setCellValue((String)method.invoke(entity));} else if (type.equals("java.lang.Integer")) {if(proprety.isSex()) {Integer sex &＃61; (Integer)method.invoke(entity);row.createCell(index&＃43;&＃43;).setCellValue( sex &＃61;&＃61; 1 ? "男" : "女");}row.createCell(index&＃43;&＃43;).setCellValue((Integer)method.invoke(entity));}}rowNum&＃43;&＃43;;}}/*** 浏览器下载* &＃64;param workbook* &＃64;param response* &＃64;param fileName*/private static void downloadExcel (HSSFWorkbook workbook, HttpServletResponse response, String fileName) {try {//清空responseresponse.reset();//设置response的Headerresponse.setHeader("Content-disposition",String.format("attachment; filename&＃61;\"%s\"", fileName));response.setContentType("multipart/form-data");response.setCharacterEncoding("UTF-8");OutputStream os &＃61; new BufferedOutputStream(response.getOutputStream());//将excel写入到输出流中workbook.write(os);os.flush();os.close();} catch (Exception e) {e.printStackTrace();}}}


字段属性类

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;/*** &＃64;author 易水●墨龙吟* &＃64;Description* &＃64;create 2019-02-28 21:09*/
&＃64;Getter
&＃64;Setter
&＃64;NoArgsConstructor
&＃64;AllArgsConstructor
public class Proprety {private String name;private int width;private boolean sex;}


在实体类上添加注解

import java.io.Serializable;/*** &＃64;author 易水●墨龙吟* &＃64;Description* &＃64;create 2019-02-24 15:40*/&＃64;ToString
&＃64;AllArgsConstructor
&＃64;NoArgsConstructor
&＃64;Setter
&＃64;Getter
public class ApplyInfo implements Serializable {&＃64;ExcelAttribute(name &＃61; "申请人")private String applyName;&＃64;ExcelAttribute(name &＃61; "性别")private Integer applySex;&＃64;ExcelAttribute(name &＃61; "年龄")private Integer applyYear;&＃64;ExcelAttribute(name &＃61; "联系方式")private String applyPhone;&＃64;ExcelAttribute(name &＃61; "学校")private String applySchool;&＃64;ExcelAttribute(name &＃61; "学历")private String applyEducation;&＃64;ExcelAttribute(name &＃61; "地址")private String applyAddress;&＃64;ExcelAttribute(name &＃61; "邮箱")private String applyEmail;&＃64;ExcelAttribute(name &＃61; "工作经验")private String applyExperience;&＃64;ExcelAttribute(name &＃61; "教育经历")private String applyEdu;&＃64;ExcelAttribute(name &＃61; "职位")private String applySchoolDuty;&＃64;ExcelAttribute(name &＃61; "在荣誉")private String applySchoolHonor;&＃64;ExcelAttribute(name &＃61; "特长")private String applySpeciality;}


使用

/*** &＃64;author 易水●墨龙吟* &＃64;Description* &＃64;create 2019-02-14 17:44*/
&＃64;Controller
&＃64;RequestMapping("/admin")
public class JobController {/*** 导出Excel数据表格* &＃64;param jobId*/&＃64;GetMapping("/job/applyToExcel")public void printToExcel (HttpServletResponse response, &＃64;RequestParam("jobId") Integer jobId) throws UnsupportedEncodingException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {// 获取文件名PartTimeJob job &＃61; partTimeJobService.selectById(jobId);// 获取需要导出的所有数据List applyInfoData &＃61; jobApplyService.getApplyInfoAll(jobId);// 使用ExcelUtiles.PrintToExcel(job.getJobName(), response, ApplyInfo.class, applyInfoData);}
}


可以关注我的公众号哦&＃xff01;感谢大家&＃xff01;