EasyPoi实现excel文件导入导出

1.环境搭建

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

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.4.0</version>
        </dependency>

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.4.0</version>
        </dependency>
    </dependencies>
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;
 
 
}
 

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")

}