1
    
    
 、引入Java包依赖

<dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.2.0</version> </dependency>

2     


     


     、创建导出的Excel样式类:

import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.usermodel.Workbook; import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler; import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler; /** * @ClassName: ExcelExportMyStylerImpl * @Description: 自定义报表导出样式    
    
    
，可以修改表头颜色 


     


     


 ，高度等 * @Author: sunt * @Date: 2019/8/29 21:39 * @Version 1.0 **/ public class ExcelExportMyStylerImpl extends AbstractExcelExportStyler implements IExcelExportStyler { public ExcelExportMyStylerImpl(Workbook workbook) { super.createStyles(workbook); } @Override public CellStyle getTitleStyle(short color) { CellStyle titleStyle = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBold(true);// 加粗 titleStyle.setFont(font); titleStyle.setAlignment(HorizontalAlignment.CENTER);// 居中 titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 titleStyle.setFillForegroundColor(IndexedColors.AQUA.index);// 设置颜色 titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); titleStyle.setBorderRight(BorderStyle.THIN); titleStyle.setWrapText(true); return titleStyle; } @SuppressWarnings("deprecation") @Override public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) { CellStyle style = workbook.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setDataFormat(STRING_FORMAT); if (isWarp) { style.setWrapText(true); } return style; } @Override public CellStyle getHeaderStyle(short color) { CellStyle titleStyle = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBold(true);// 加粗 font.setColor(IndexedColors.RED.index); font.setFontHeightInPoints((short) 11); titleStyle.setFont(font); titleStyle.setAlignment(HorizontalAlignment.CENTER);// 居中 titleStyle.setFillForegroundColor(IndexedColors.WHITE.index);// 设置颜色 titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); titleStyle.setBorderRight(BorderStyle.THIN); titleStyle.setWrapText(true); return titleStyle; } @SuppressWarnings("deprecation") @Override public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) { CellStyle style = workbook.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setDataFormat(STRING_FORMAT); if (isWarp) { style.setWrapText(true); } return style; } }

3 




 




 




、创建核心导出工具类

import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.entity.ExportParams; import com.sunny.spring.boot.poi.common.ExcelExportMyStylerImpl; import com.sunny.spring.boot.poi.pojo.StudentInfoBean; import org.apache.poi.ss.formula.functions.T; import org.apache.poi.ss.usermodel.Workbook; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.Collection; import java.util.Date; import java.util.List; /** * @ClassName: ExcelExportUtil * @Description: Exceld导出工具类 * @Author: sunt * @Date: 2019/8/30 14:49 * @Version 1.0 **/ public class MyExcelExportUtil { /** * Excel文件导出,导出的文件名默认为:headTitle+当前系统时间 * @param listData 要导出的list数据 * @param pojoClass 定义excel属性信息 * @param headTitle Excel文件头信息 * @param sheetName Excel文件sheet名称 * @param response */ public static void exportExcel(Collection<?> listData,Class<?> pojoClass, String headTitle, String sheetName, HttpServletResponse response) { ExportParams params = new ExportParams(headTitle, sheetName); params.setHeight((short) 8); params.setStyle(ExcelExportMyStylerImpl.class); try { Workbook workbook = ExcelExportUtil.exportExcel(params, pojoClass, listData); String fileName = headTitle + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()); fileName = URLEncoder.encode(fileName, "UTF8"); response.setContentType("application/vnd.ms-excel;chartset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename="+fileName + ".xls"); ServletOutputStream out=response.getOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); } } }

4

   

   

、创建导出对象实体Bean

注意日期类型 注解内要加上: exportFormat = "yyyy-MM-dd hh:mm:ss"

import cn.afterturn.easypoi.excel.annotation.Excel; import com.baomidou.mybatisplus.annotation.TableField; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import lombok.Data; import lombok.EqualsAndHashCode; import lombok.experimental.Accessors; import java.io.Serializable; import java.math.BigDecimal; /** * <p> * 学生基本信息表 * </p> * * @author sunt * @since 2019-08-29 */ @Data @EqualsAndHashCode(callSuper = false) @Accessors(chain = true) @TableName("T_STUDENT") public class StudentInfoBean implements Serializable { private static final long serialVersionUID = 1L; /** * 学号 */ @TableId("ID") @Excel(name = "学号", width = 20, orderNum = "1") private String id; /** * 姓名 */ @TableField("NAME") @Excel(name = "姓名", width = 20, orderNum = "2") private String name; /** * 性别(1:男 2:女) * replace:导出是{a_id,b_id} 导入反过来,注意大括号里面单独引号引起来的 */ @TableField("SEX") @Excel(name = "性别", width = 20, replace = { "男_1", "女_2" },orderNum = "3") private String sex; /** * 年龄 */ @TableField("AGE") @Excel(name = "年龄", width = 20, orderNum = "4") private Integer age; /** * 出生日期 */ @TableField("BIRTHDAY") @Excel(name = "商品创建时间", width = 20, orderNum = "12",exportFormat = "yyyy-MM-dd hh:mm:ss") private String birthday; /** * 入学时间 */ @TableField("REGIST_DATE") @Excel(name = "入学时间",width = 20,orderNum = "6") private String registDate; /** * 学费 */ @TableField("FEE") @Excel(name = "学费", width = 20, orderNum = "7") private BigDecimal fee; } 属性字段 属性值 @TableField 这个字段代表数据库表的字段 @Excel name代表导出Excel列名称 @Excel orderNum代表Excel列排在第几列 @Excel replace一般数据库存的性别例如0和1

 




 




 ，导出的值0展示为男性   

   

   

，女展示为女性

5     

     

     、后台方法:

直接调用查询方法  

     

     

，返回给前台就OK

@RequestMapping("/exportStudent") public void exportStudent(HttpServletResponse response) { try { List<StudentInfoBean> sutdentList = studentService.queryAllStudent(); MyExcelExportUtil.exportExcel(sutdentList,StudentInfoBean.class,"学生基本信息","新生入学信息",response); } catch (Exception e) { e.printStackTrace(); } }

6  




  




  



、前台的方法

不能使用ajax方法


  




  




  ，返回的是字符串  


  


  

，后台返回的是流   
     
     
，如果用ajax返回的是乱码



   




   




  ，并且浏览器不下载

//导出excel excel(){ window.open("http://localhost:88/api/shop/shop/exportShop") },