导出样式

 

 

一、设置头部

@HeadRowHeight(35)//表头行高
@ContentRowHeight(25)//内容行高
@ColumnWidth(20)//列宽
@Data
@TableName("student")
public class ExcelInfo {
    @ExcelIgnore
    private Integer id;
    @ExcelProperty(value = {"学生姓名"}, index = 0)
    private String name;
    @ExcelProperty(value = {"学生信息","学生年龄"}, index = 1)
    private Integer age;
    @ExcelProperty(value = {"学生信息","学生性别"}, index = 2)
    private Integer sex;
    @ExcelProperty(value = {"学生信息","学生住址"}, index = 3)
    private String address;
    @ExcelProperty(value = {"学生电话"}, index = 4)
    private String phone;
    @ExcelIgnore
    @ExcelProperty(value = {"学生信息","学生状态"}, index = 5)
    private Integer status;

}

二、为了方便逻辑是直接写在controller层

@RestController
@RequestMapping("/excel")
@Slf4j
public class ExcelController {
    @Resource
    private ExcelService excelService;

    @GetMapping("/export")
    public void export(HttpServletResponse response) {
        String filename = new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + ".xlsx";
        ExcelWriter excelWriter = null;
        try {
            // 头的策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 背景色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short) 12);
            headWriteCellStyle.setWriteFont(headWriteFont);

            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 字体策略
            WriteFont contentWriteFont = new WriteFont();
            // 字体大小
            contentWriteFont.setFontHeightInPoints((short) 12);
            contentWriteCellStyle.setWriteFont(contentWriteFont);

            //设置 自动换行
            contentWriteCellStyle.setWrapped(true);
            //设置 垂直居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //设置边框样式
            contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
            contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
            contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
            contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
            //传入样式
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

            List<ExcelInfo> list = excelService.list();
            response.setHeader("Content-Disposition", "attachment;filename=" + filename);
            //导入一个sheet
//            EasyExcel.write(response.getOutputStream(), ExcelInfo.class).sheet(1,"模板1").registerWriteHandler(horizontalCellStyleStrategy).doWrite(list);
           //导入多个sheet
            excelWriter  = EasyExcel.write(response.getOutputStream(), ExcelInfo.class).build();
            //创建一个sheet
            WriteSheet writeSheet = EasyExcel.writerSheet(0, "模板1").registerWriteHandler(horizontalCellStyleStrategy).build();
            excelWriter.write(list, writeSheet);
            //创建一个新的sheet
            writeSheet = EasyExcel.writerSheet(1, "模板2").registerWriteHandler(horizontalCellStyleStrategy).build();
            excelWriter.write(list, writeSheet);
        } catch (IOException e) {
            e.printStackTrace();
            log.error("导出{}文件失败！", filename);
        }finally {
            //关闭流
            if(excelWriter != null){
                excelWriter.finish();
            }
        }
    }
}

三、数据库（ORM框架用的mybatis-plus）

create database  db_excel;
use db_excel;
create table if not exists student(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    sex int(2) ,
    address varchar(30),
    phone varchar(11),
    status int(2)
);