导出效果：
为了节省时间使用的的工具类在线下载
注：此为SSM+layui项目
配置依赖
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><!-- /artifact/mons/commons-lang3 --><dependency><groupId>mons</groupId><artifactId>commons-lang3</artifactId><version>3.7</version></dependency>
layui前端代码：
from绑定一个id，条件查询每个属性给个name值，用于根据条件查询导出
1.头部工具栏添加一个按钮
<!-- 头部工具栏 --><script type="text/html" id="toolbarDemo"><div class="layui-btn-container"><button class="layui-btn layui-btn-sm layui-btn-normal" lay-event="exportExcel"><i class="layui-icon layui-icon-export"></i>导出用户</button></div></script>
2.在监听头工具栏事件定义方法和实现传参
//监听头工具栏事件table.on('toolbar(userList)', function(obj){var checkStatus = table.checkStatus(obj.config.id),data = checkStatus.data; //获取选中的数据switch(obj.event){//导出case 'exportExcel':exportExcel();break;};});//导出function exportExcel(){var searchForm = document.getElementById("searchForm");searchForm.action="/user/exportExcel";searchForm.submit();}
3.在UserController接参
//导出@RequestMapping("exportExcel")public void exportExcel(UserVo vo, HttpServletResponse response){//ExportingEntityPoi 导出定义的实体类List<ExportingEntityPoi> userList = userService.queryUserList(vo);// 导出ExcelExportUtil.ExportExcel(userList,ExportingEntityPoi.class,response);}
4.创建ExportUtil 工具类
package com.ff.util;import mon.excel.ExcelUtilsBean;import mon.excel.annotations.ExcelDescAnnotation;import mon.excel.annotations.ExcelFieldAnnotation;import mon.excel.annotations.model.ExportingEntityPoi;import mon.excel.constant.SuperEnum;import mons.lang3.StringUtils;import mons.lang3.time.DateFormatUtils;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.util.IOUtils;import org.apache.poi.xssf.usermodel.*;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.util.ArrayList;import java.util.Date;import java.util.List;public class ExportUtil {public static void ExportExcel(List<?> data, Class<?> clazz, HttpServletResponse response) {//获取对象中的自定义注解的信息ExcelUtilsBean excelUtilsBean = getExceInfo(clazz);//导出Exceltry {ExportExcels(data,excelUtilsBean,response);} catch (Exception e) {e.printStackTrace();}}//获取对象中的自定义注解的信息 描述excel信息private static ExcelUtilsBean getExceInfo(Class<?> clazz) {ExcelUtilsBean excelUtilsBean = new ExcelUtilsBean();//获取类上的自定义注解中的信息ExcelDescAnnotation annotation = clazz.getAnnotation(ExcelDescAnnotation.class);//给excel设置标题excelUtilsBean.setTitleName(annotation.title());//给excel设置Sheet页名称excelUtilsBean.setSheetName(annotation.sheetName());//获取字段上面的注解信息Field[] fields = clazz.getDeclaredFields();List<String> cloumnNames = new ArrayList<>();List<String> fieldNames = new ArrayList<>();for (Field field : fields) {//获取所有的字段fieldNames.add(field.getName());//获取字段上的自定义注解信息ExcelFieldAnnotation fieldAnnotation = field.getAnnotation(ExcelFieldAnnotation.class);//获取表头cloumnNames.add(fieldAnnotation.cloumnName());}excelUtilsBean.setFieldList(fieldNames);excelUtilsBean.setCloumnNameList(cloumnNames);return excelUtilsBean;}//导出Excelprivate static void ExportExcels(List<?> data, ExcelUtilsBean excelUtilsBean, HttpServletResponse response) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException, IOException, NoSuchFieldException {// 创建一个工作薄XSSFWorkbook workbook = new XSSFWorkbook();// 创建sheet页XSSFSheet sheet = workbook.createSheet(excelUtilsBean.getSheetName());// 表头数据List<String> cloumnNameList = excelUtilsBean.getCloumnNameList();// 给列设置默认宽度sheet.setDefaultColumnWidth(30);// 合并单元格CellRangeAddress range = new CellRangeAddress(0,0,0,cloumnNameList.size()-1);sheet.addMergedRegion(range);CellRangeAddress range2 = new CellRangeAddress(1,1,0,cloumnNameList.size()-1);sheet.addMergedRegion(range2);// 创建行XSSFRow row1 = sheet.createRow(0);// 创建单元格XSSFCell cell = row1.createCell(0);// 给单元格赋值cell.setCellValue(excelUtilsBean.getTitleName());// 设置第一行样式XSSFCellStyle titleStyle = createTitleStyle(workbook);cell.setCellStyle(titleStyle);// 创建第二行XSSFRow row2 = sheet.createRow(1);XSSFCell cell2 = row2.createCell(0);cell2.setCellValue("总数："+data.size()+"条，导出时间："+new Date().toLocaleString());// 设置第二行样式XSSFCellStyle subTitleStyle = createSubTitleStyle(workbook);cell2.setCellStyle(subTitleStyle);// 创建第三行(表头行)XSSFRow row3 = sheet.createRow(2);for (int i = 0; i <cloumnNameList.size() ; i++) {XSSFCell row3_cell = row3.createCell(i);row3_cell.setCellValue(cloumnNameList.get(i));XSSFCellStyle tableTitle = createTableTitle(workbook);row3_cell.setCellStyle(tableTitle);}// 创建数据行XSSFCellStyle baseStyle = createBaseStyle(workbook);List<String> fielNames = excelUtilsBean.getFieldList();for (int i = 0; i < data.size(); i++) {Object obj = data.get(i);Class<?> clazz = obj.getClass();XSSFRow dataRow = sheet.createRow(i + 3);for (int j = 0; j < fielNames.size(); j++) {String fielName = fielNames.get(j);String methodName =getMethod(fielName);Method method = clazz.getMethod(methodName);// 执行get方法获取该字段的值Object fieldValue = method.invoke(obj);// 创建数据行的单元格XSSFCell dataRowCell = dataRow.createCell(j);//获取单元格的值String cellValue=getCellValue(fieldValue,clazz,fielName,i,j);dataRowCell.setCellValue(cellValue);dataRowCell.setCellStyle(baseStyle);}}//设置自适应for (int i = 0; i < fielNames.size(); i++) {sheet.autoSizeColumn((short) i);// 解决自动设置列宽中文失效的问题sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);}//添加返回的数据的类型，和文件名response.setContentType("application/octet-stream");response.setHeader("Content-Disposition", "attachment;fileName=" +System.currentTimeMillis() + ".xlsx");response.flushBuffer();workbook.write(response.getOutputStream());}//获取单元格的值private static String getCellValue(Object data, Class<?> clazz, String fielName, int i, int j) throws NoSuchFieldException, InvocationTargetException, IllegalAccessException, NoSuchMethodException {if(data == null ){return "";}if(data instanceof Date){Field field = clazz.getDeclaredField(fielName);ExcelFieldAnnotation fieldAnno = field.getAnnotation(ExcelFieldAnnotation.class);String pattern=fieldAnno.pattern();if(StringUtils.isNotBlank(pattern)){String dateValue = DateFormatUtils.format((Date) data, pattern);return dateValue;}else{String dateValue = DateFormatUtils.format((Date) data, "yyyy-MM-dd HH:mm:ss");return dateValue;}}else if (j == 0){//编号顺序return String.valueOf(i+1);}else if(data instanceof String){return String.valueOf(data);}else if(data instanceof Integer){Field field = clazz.getDeclaredField(fielName);ExcelFieldAnnotation fieldAnno = field.getAnnotation(ExcelFieldAnnotation.class);Class enumClazz = fieldAnno.enumClazz();if(enumClazz.isEnum()){Method method = enumClazz.getMethod("values");SuperEnum[] superEnums= (SuperEnum[]) method.invoke(null);for(SuperEnum superEnum:superEnums){Integer code=superEnum.getCode();if(code.equals(data)){return superEnum.getName();}}}return String.valueOf(data);}else{return "";}}private static String getMethod(String fielName) {return "get" + fielName.substring(0,1).toUpperCase()+fielName.substring(1);}/*** 设置技术样式* 水平居中和垂直居中* @param workbook* @return*/public static XSSFCellStyle createBaseStyle(XSSFWorkbook workbook){XSSFCellStyle style = workbook.createCellStyle();// 水平居中style.setAlignment(HorizontalAlignment.CENTER);// 垂直居中style.setVerticalAlignment(VerticalAlignment.CENTER);return style;}/*** 设置标题样式*/public static XSSFCellStyle createTitleStyle(XSSFWorkbook workbook){XSSFCellStyle style = createBaseStyle(workbook);// 给字体设置样式XSSFFont font = workbook.createFont();// 加粗font.setBold(true);// 大小font.setFontHeightInPoints((short) 30);// 字体颜色font.setColor(HSSFColor.BLUE.index);// 字体样式font.setFontName("华文彩云");style.setFont(font);return style;}/*** 设置小标题样式*/public static XSSFCellStyle createSubTitleStyle(XSSFWorkbook workbook){XSSFCellStyle style = createBaseStyle(workbook);// 给字体设置样式XSSFFont font = workbook.createFont();// 加粗font.setBold(true);// 大小font.setFontHeightInPoints((short) 20);// 字体颜色font.setColor(HSSFColor.RED.index);// 字体样式font.setFontName("微软雅黑");style.setFont(font);return style;}/*** 设置表格标题样式*/public static XSSFCellStyle createTableTitle(XSSFWorkbook workbook){XSSFCellStyle style = createBaseStyle(workbook);// 给字体设置样式XSSFFont font = workbook.createFont();// 加粗font.setBold(true);// 大小font.setFontHeightInPoints((short) 15);// 字体颜色// font.setColor(HSSFColor.RED.index);// 字体样式font.setFontName("宋体");style.setFont(font);return style;}}/*** excel的导入* @param inputStream* @param clazz* @return* @throws IOException* @throws InstantiationException* @throws IllegalAccessException* @throws InvocationTargetException* @throws NoSuchMethodException*/public static List<?> importExcel(InputStream inputStream, Class<?> clazz)throws IOException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {Workbook workbook = WorkbookFactory.create(inputStream);Sheet sheet = workbook.getSheetAt(0);Row titleCell = sheet.getRow(0);List<Object> dataList = new ArrayList<>(sheet.getLastRowNum());Object datum;Map<String, Field> fieldMap = getFieldMap(clazz);for (int i = 1; i <= sheet.getLastRowNum(); i++) {Row row = sheet.getRow(i);datum = clazz.newInstance();int minCell = row.getFirstCellNum();int maxCell = row.getLastCellNum();for (int cellNum = minCell; cellNum <= maxCell; cellNum++) {Cell title = titleCell.getCell(cellNum);if (title == null) {continue;}String tag = title.getStringCellValue();Field field = fieldMap.get(tag);if (field == null) {continue;}Class<?> type = field.getType();Object value = null;Cell cell = row.getCell(cellNum);if (cell == null) {continue;}if (type.equals(Date.class)){value = cell.getDateCellValue();} else {value = cell.getStringCellValue();}PropertyUtils.setProperty(datum, field.getName(), value);}dataList.add(datum);}return dataList;}/*** key :headName val:该名称对应的字段* @param clazz* @param <T>* @return*/private static <T> Map<String, Field> getFieldMap(Class<T> clazz) {Field[] fields = clazz.getDeclaredFields();Map<String, Field> fieldMap = new HashMap<>();for (Field field : fields) {if (field.isAnnotationPresent(ExcelAnnotation.class)) {ExcelAnnotation annotation = field.getAnnotation(ExcelAnnotation.class);fieldMap.put(annotation.headName(), field);}}return fieldMap;}
华文彩云字体下载
5.创建两个自定义注解
package mon.excel.annotations;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;//ElementType.TYPE //类上使用@Target(ElementType.TYPE)//RetentionPolicy.RUNTIM 注解不仅被保存到class文件中，jvm加载class文件之后，仍然存在@Retention(RetentionPolicy.RUNTIME)public @interface ExcelDescAnnotation {//用来描述excel中sheet的名字String value() default "";// 标题String title() default "";// sheet 名称String sheetName() default "";}
package mon.excel.annotations;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;// ElementType.FIELD //字段、枚举的上的常量@Target(ElementType.FIELD)RetentionPolicy.RUNTIM 注解不仅被保存到class文件中，jvm加载class文件之后，仍然存在@Retention(RetentionPolicy.RUNTIME)public @interface ExcelFieldAnnotation {//用来描述字段的值，以及需要转换的格式String value() default "";//字段名称String cloumnName() default "";// 时间String pattern() default "";//性别 格式转换Class enumClazz() default Object.class;}
6.创建接口做枚举类返回值
package mon.excel.constant;public interface SuperEnum {// 创建接口做枚举类返回值public String getName();public Integer getCode();}
7.创建枚举类用来做字段的转换 比如性别
package mon.excel.constant;public enum SexEnum implements SuperEnum {//转换性别MAN(0,"男"),GIRL(1,"女");private Integer code ;private String name ;private SexEnum(Integer code,String name){this.code=code;this.name=name;}@Overridepublic String getName() {return name;}@Overridepublic Integer getCode() {return code;}}
8.创建导出使用的实体类 用于放需要导出字段
package mon.excel.annotations.model;import mon.excel.annotations.ExcelDescAnnotation;import mon.excel.annotations.ExcelFieldAnnotation;import mon.excel.constant.SexEnum;import lombok.Data;import lombok.EqualsAndHashCode;import java.util.Date;//导出使用的实体类@Data//title Excel标题 sheetName sheet 名称@ExcelDescAnnotation(title = "用户信息",sheetName = "用户信息")public class ExportingEntityPoi {//导出需要的字段@ExcelFieldAnnotation(cloumnName = "用户编号")private String id;@ExcelFieldAnnotation(cloumnName = "用户名称")private String name;@ExcelFieldAnnotation(cloumnName = "登录名称")private String loginname;@ExcelFieldAnnotation(cloumnName = "用户地址")private String address;@ExcelFieldAnnotation(cloumnName = "直属领导")private String remark;@ExcelFieldAnnotation(cloumnName = "用户性别",enumClazz = SexEnum.class)//enumClazz = SexEnum.class 导出时转换性别格式private Integer sex;@ExcelFieldAnnotation(cloumnName = "所属部门")private String deptname;@ExcelFieldAnnotation(cloumnName = "入职时间",pattern = "yyyy-MM-dd HH:mm:ss")private Date hiredate;}
9.创建用来存储对象中注解配置的信息
package mon.excel;import lombok.Data;import java.util.List;//此类用来存储对象中注解配置的信息@Datapublic class ExcelUtilsBean {private List<String> fieldList;private List<String> cloumnNameList;private String sheetName;private String titleName;}
10.在UserMapper.xml中执行Sql文件
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.ff.mapper.UserMapper"><sql id="where"><where><if test="vo.name != null and vo.name != ''">u1.name like concat('%',#{vo.name},'%')</if><if test="vo.deptid != null and vo.deptid!=-1">and u1.deptid=#{vo.deptid}</if><if test="vo.startTime != null">and u1.hiredate >= #{vo.startTime}</if><if test="vo.endTime != null">and u1.hiredate <= #{vo.endTime}</if><if test="vo.available != null">and u1.available = #{vo.available}</if></where></sql><!--查询用户列表--><select id="queryuser" resultType="com.ff.domain.User">SELECTu1.*,d.title deptnameFROMsys_user u1LEFT JOIN sys_dept d ON u1.deptid = d.id<include refid="where"></include> order by u1.id desclimit #{vo.startIndex},#{vo.limit}</select><!--导出--><select id="queryUserList" resultType="mon.excel.annotations.model.ExportingEntityPoi">SELECTu1.*,d.title as deptnameFROMsys_user u1left join sys_dept d on u1.deptid=d.id<include refid="where"></include>order by id desc</select><!--总条数--><select id="queryCount" resultType="long">select count(*) from sys_user u1 <include refid="where"></include></select></mapper>
