首先导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
导出参考地址: