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