easyexcel导出和导入

首先导jar包

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.4</version>
        </dependency

导出功能实体类

public class Plan {

    /**
     * 序号
     */
    @TableId(value = "id", type = IdType.AUTO)
    @ExcelProperty(value = {"序号","序号"},index = 0)
    private Integer id;

    /**
     * 月份
     */
    @TableField("month")
    @ExcelProperty(value = {"月份","月份"},index = 1)
    private String month;

    /**
     * 发货单位
     */
    @TableField("unit")
    @ExcelProperty(value = {"发货单位","发货单位"},index = 2)
    private String unit;

    /**
     * 发运品名
     */
    @TableField("name")
    @ExcelProperty(value = {"发运品名","发运品名"},index = 3)
    private String name;

    /**
     * 发运量（万吨）
     */
    @TableField("shipment")
    @ExcelProperty(value = {"发运量(万吨)","发运量(万吨)"},index = 4)
    private Float shipment;
    /**
     * 火车皮
     */
    @TableField("train")
    @ExcelProperty(value = {"火车皮","火车皮"},index = 5)
    private String train;

    /**
     * 收货单位
     */
    @TableField("consignee")
    @ExcelProperty(value = {"收货单位","收货单位"},index = 6)
    private String consignee;

    /**
     * 车次
     */
    @TableField("train_number")
    @ExcelIgnore//忽略这个字段
    private String trainNumber;

    /**
     * 车站
     */
    @TableField("station")
    @ExcelProperty(value = {"到站","车站"},index = 7)
    private String station;

    /**
     * 专用线
     */
    @TableField("private_wire")
    @ExcelProperty(value = {"到站","专用线"},index = 8)
    private String privateWire;

    /**
     * 联系人
     */
    @TableField("linkman")
    @ExcelProperty(value = {"联系人","联系人"},index = 9)
    private String linkman;

    /**
     * 电话
     */
    @TableField("phone")
    @ExcelProperty(value = {"电话","电话"},index = 10)
    private String phone;

    /**
     * 受票方
     */
    @TableField("drawee_party")
    @ExcelProperty(value = {"受票方","受票方"},index = 11)
    private String draweeParty;

    /**
     * 备注
     */
    @TableField("remark")
    @ExcelProperty(value = {"备注","备注"},index = 12)
    private String remark;

    /**
     * 状态 0-审核中 1-审核通过 2-未通过
     */
    @TableField("state")
    @ExcelIgnore//忽略这个字段
    private Integer state;

    /**
     * 发运计划
     */
    @TableField("despatch_id")
    @ExcelIgnore
    private Integer despatchId;

    /**
     * 未通过原因
     */
    @TableField("reject")
    @ExcelIgnore//忽略这个字段
    private String reject;
    /**
     * 请车计划时间
     */
    @TableField("time")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    @ExcelIgnore//忽略这个字段
    private Date time;
    
    @TableField(exist = false)
    @ExcelIgnore//忽略这个字段
    private Date timeStart;
    
    @TableField(exist = false)
    @ExcelIgnore//忽略这个字段
    private Date timeEnd;
    /**
     * 创建时间
     */
    @TableField("create_date")
    @ExcelIgnore//忽略这个字段
    private Date createDate;

    //0代表不发短信 1代表发短信
    @TableField(exist = false)
    private Integer sms;

controller层

   @RequestMapping("plan/excelOne")
    @ResponseBody
    @RequiresPermissions("plan:export")
    public void excelOne(HttpServletResponse response,@RequestParam("id") Integer id) throws IOException {
        List<Plan> list = planService.export(id);
        List<String> totalList = new ArrayList<String>();
        totalList.add("1111");
        System.out.println(list);
        response.setContentType("application/json;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        String fileName= URLEncoder.encode("任务表","utf-8");
        response.setHeader("Content-disposition",  "attachment;filename=" + fileName + ".xls");
        //内容样式策略
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        //垂直居中,水平居中
        writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//水平对齐
        writeCellStyle.setBorderLeft(BorderStyle.THIN);//边框属性
        writeCellStyle.setBorderTop(BorderStyle.THIN);
        writeCellStyle.setBorderRight(BorderStyle.THIN);
        writeCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置自动换行
        writeCellStyle.setWrapped(true);//自动换行
        //字体策略
        WriteFont writeFont = new WriteFont();
        writeFont.setFontHeightInPoints((short) 12);
        writeCellStyle.setWriteFont(writeFont);
        //头策略使用默认
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        EasyExcel.write(response.getOutputStream(),Plan.class)//Plan为实体类模板
                //设置输出execl版本,不设置默认为xlxs模式
                .excelType(ExcelTypeEnum.XLS).head(Plan.class)
                .withTemplate(new File(path))//这个是我的模板可以删除
                //设置拦截器样式
                .registerWriteHandler(new MonthSheetWriteHandler())
                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle,writeCellStyle))
                .sheet("任务表")
                //设置默认样式及写入头信息开始的行数
                .useDefaultStyle(true).relativeHeadRowIndex(2)
                .doFill(list);
    }

自定义拦截器

public class MonthSheetWriteHandler implements SheetWriteHandler {
        //设置xls样式表拦截器
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String nowDate = df.format(new Date());
        String[] split = nowDate.split("-");
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = writeSheetHolder.getSheet();
        Row row1 = sheet.createRow(0);
        row1.setHeight((short) 500);
        //设置标题
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 12));
        Row row2 = sheet.createRow(0);
        row2.setHeight((short) 800);
        Cell cell1 = row2.createCell(0);
        cell1.setCellValue("郭家湾专用线" + split[0] +"年"+split[1]+ "月份发运计划提报单");
        sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 11, 12));
        Row row = sheet.createRow(1);
        String yMd= split[0]+"年"+ split[1]+ "月"+ split[2]+"日";
        row.createCell(11).setCellValue(yMd);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeight((short) 400);
        cellStyle.setFont(font);
        cell1.setCellStyle(cellStyle);
      sheet.addMergedRegionUnsafe(new CellRangeAddress(9, 9, 8, 12));
        Row row5 = sheet.createRow(9);
        row5.createCell(0).setCellValue("填表人：    日期："+yMd);
        row5.createCell(8).setCellValue("接收人：      日期：       年   月  日");
    }

效果：

 相比导出 导入能够简单些

实体类

package cc.mrbird.febs.reportDetails.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
import lombok.EqualsAndHashCode;

@Data
@EqualsAndHashCode(callSuper = true)
public class ReportDetailsUp extends BaseRowModel {
    /**
     * 序号
     */
    @TableId(value = "id", type = IdType.AUTO)
    @ExcelProperty(value = "序号", index = 0)
    private Integer id;

    /**
     * 车型
     */
    @TableField("type")
    @ExcelProperty(value = "车型", index = 1)
    private String type;

    /**
     * 车号
     */
    @TableField("wagon_number")
    @ExcelProperty( value = "车号",index = 2)
    private String wagonNumber;

    /**
     * 发货单位
     */
    @TableField("unit")
    @ExcelProperty( value = "发货单位",index = 3)
    private String unit;

    /**
     * 货名
     */
    @TableField("name")
    @ExcelProperty(value = "货名",index = 4)
    private String name;

    /**
     * 收货单位
     */
    @TableField("consignee")
    @ExcelProperty(value = "收货单位",index = 5)
    private String consignee;

    /**
     * 速度
     */
    @TableField("speed")
    @ExcelProperty(value = "速度",index = 6)
    private String speed;

    /**
     * 毛重
     */
    @TableField("gross_weight")
    @ExcelProperty(value = "毛重",index = 7)
    private Double grossWeight;

    /**
     * 皮重
     */
    @TableField("tare")
    @ExcelProperty(value = "皮重",index = 8)
    private Double tare;

    /**
     * 净重
     */
    @TableField("net_weight")
    @ExcelProperty(value = "净重",index = 9)
    private Double netWeight;

    /**
     * 票重
     */
    @TableField("ticket_again")
    @ExcelProperty(value = "票重",index =10)
    private Double ticketAgain;

    /**
     * 盈亏
     */
    @TableField("profit")
    @ExcelProperty(value = "盈亏",index = 11)
    private String profit;

    @TableField("railroad_trains")
    @ExcelProperty
    private String railroadTrains;
}

controller层

 @RequestMapping(value = "reportDetails/uploadEasyExcl")
    @RequiresPermissions("reportDetails:add")
    @ResponseBody
    public FebsResponse uploadEasyExcl(@RequestParam("file")MultipartFile file, @RequestParam("railroadTrains") String railroadTrains) throws IOException {
        //删除上一次的数据
        reportDetailsService.delete(railroadTrains);//可要可不要
        //保存数据
        reportDetailsService.excelImport(file,railroadTrains);
        return new FebsResponse().success();
    }

server

 void excelImport(MultipartFile file,String railroadTrains) throws IOException;

serverimpl

 @Override
    public void excelImport(MultipartFile file,String railroadTrains) throws IOException {
        String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
       //判断文件的后缀类型
         if (!xls.equals(suffix)&&!xlsx.equals(suffix)){
                return;
            }
        InputStream inputStream = new BufferedInputStream(file.getInputStream());
        //实例化实现了AnalysisEventListener接口的类
        ExcelListener excelListener = new ExcelListener(reportDetailsMapper,railroadTrains);
        ExcelReader reader = new ExcelReader(inputStream,null,excelListener);

        //读取信息
        reader.read(new Sheet(1,1, ReportDetailsUp.class));

    }

工具类 可以去easyExcel官网看

package cc.mrbird.febs.util;

import cc.mrbird.febs.report.mapper.ReportMapper;
import cc.mrbird.febs.reportDetails.entity.ReportDetailsUp;
import cc.mrbird.febs.reportDetails.mapper.ReportDetailsMapper;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;

public class ExcelListener extends AnalysisEventListener<ReportDetailsUp> {
    private List<ReportDetailsUp> datas = new ArrayList<>();
    private static final int BATCH_COUNT = 3000;
    private ReportDetailsMapper reportDetailsMapper;
    private String railroadTrains ;//接收铁路车次


    public ExcelListener(ReportDetailsMapper reportDetailsMapper,String railroadTrains) {
        this.reportDetailsMapper=reportDetailsMapper;
        this.railroadTrains=railroadTrains;
    }

    @Override
    public void invoke(ReportDetailsUp reportDetailsUp, AnalysisContext analysisContext) {
        reportDetailsUp.setRailroadTrains(railroadTrains);//给一个默认值不可删除
        //数据存储到datas，供批量处理，或后续自己业务逻辑处理。
        datas.add(reportDetailsUp);

        if(datas.size() >= BATCH_COUNT){
            saveData();
            // 存储完成清理datas
            datas.clear();
        }
    }

    private void saveData() {
        for (ReportDetailsUp data : datas) {
            reportDetailsMapper.insert(data);
        }
    }

    public List<ReportDetailsUp> getDatas() {
        return datas;
    }

    public void setDatas(List<ReportDetailsUp> datas) {
        this.datas = datas;
    }

    /**
     * 所有数据解析完成了 都会来调用
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();//确保所有数据都能入库
    }
}

导入参考地址： SpringBoot整合EasyExcel实现Excel导入导出/上传下载至数据库_南城.南城的博客-CSDN博客_easyexcel导入数据库d

导出参考地址： 

使用easyexcel完成复杂表头及标题的导出功能(自定义样式及多sheet导出)_深蓝格调_的博客-CSDN博客