easyExcel
详细学习请看官网 https://www.yuque.com/easyexcel/doc/easyexcel
1、文件批量导出excel
导入maven
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beat1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.28</version>
</dependency>
实体类
package com.wzw.pojo.manage;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.sql.rowset.BaseRowSet;
import java.io.Serializable;
@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("仓库表实体类") //要使用easyExcel必须继承BaseRowModel
public class WareHouse extends BaseRowModel implements Serializable {
@ExcelProperty(value = "id",index = 0)
@ApiModelProperty(value = "仓库存货id")
private int id;
@ExcelProperty(value = "物资编号",index = 1)
@ApiModelProperty(value = "物资编号")
private String materialNumber;
@ExcelProperty(value = "物资类别",index = 2)
@ApiModelProperty(value = "物资类别")
private String materialCategory;
@ExcelProperty(value = "物资名称",index = 3)
@ApiModelProperty(value = "物资名称")
private String materialName;
@ExcelProperty(value = "物资数量",index = 4)
@ApiModelProperty(value = "物资数量")
private int materialNum;
@ExcelProperty(value = "计量单位",index = 5)
@ApiModelProperty(value = "计量单位")
private String unit;
@ExcelProperty(value = "参数备注",index = 6)
@ApiModelProperty(value = "参数备注")
private String parameter;
}
controller层
@RequestMapping("/warehouseExcelOut")
public void warehouseExcelOut(HttpServletResponse response){
ExcelWriter writer = null;
OutputStream out = null;
try{
//通过数据库查出你要批量导出的数据,这里要用List
List wareHouseList = wareHouseService.findAll();
out = response.getOutputStream();
writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
//设置文件名
String fileName = "仓库信息表格";
//WareHouse.class 实体类的class对象
Sheet sheet = new Sheet(1, 0, WareHouse.class);
//表名
sheet.setSheetName("仓库信息");
//把wareHouseList数据写入sheet中
writer.write(wareHouseList, sheet);
//设置响应头
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "ISO8859-1"));
out.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (writer != null) {
writer.finish();
}
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
1、文件批量导入excel
controller
@Autowired
private WareHouseServiceImpl wareHouseService;
@RequestMapping("/warehouseExcelIn")
public ResultVo warehouseExcelOut(@RequestParam MultipartFile file) throws IOException {
// if(!file.getOriginalFilename().equals("仓库信息表格.xls") && !file.getOriginalFilename().equals("仓库 信息表格.xlsx") ){
// return;
// }
InputStream inputStream = new BufferedInputStream(file.getInputStream());
//实例化实现了AnalysisEventListener接口的类
DemoDataListener excelListener = new DemoDataListener(wareHouseService);
ExcelReader reader = new ExcelReader(inputStream,null,excelListener);
//读取信息
reader.read(new Sheet(1,1,WareHouse.class));
return ResultVo.success("上传成功");
}
DemoDataListener
package com.wzw.untils;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.wzw.pojo.manage.WareHouse;
import com.wzw.service.manage.WareHouseServiceImpl;
import org.mybatis.logging.Logger;
import org.mybatis.logging.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import springfox.documentation.spring.web.json.Json;
import java.util.ArrayList;
import java.util.List;
// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<WareHouse> {
//WareHouse实体类
private List<WareHouse> datas = new ArrayList<>();
private static final int BATCH_COUNT = 3000;
//注入service层
@Autowired
private WareHouseServiceImpl wareHouseService;
//有参构造
public DemoDataListener(WareHouseServiceImpl wareHouseService) {
this.wareHouseService=wareHouseService;
}
@Override
public void invoke(WareHouse wareHouse, AnalysisContext analysisContext) {
System.out.println("warehouse: "+wareHouse);
//数据存储到datas,供批量处理,或后续自己业务逻辑处理。
datas.add(wareHouse);
//达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if(datas.size() >= BATCH_COUNT){
saveData();
// 存储完成清理datas
datas.clear();
}
}
//使用add对导入数据进行入库
private void saveData() {
for(WareHouse wareHouse : datas){
System.out.println("saveData: "+wareHouse);
wareHouseService.addWareHourse(wareHouse);
}
}
public List<WareHouse> getDatas() {
return datas;
}
public void setDatas(List<WareHouse> datas) {
this.datas = datas;
}
/**
* 所有数据解析完成了 都会来调用
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();//确保所有数据都能入库
}
}
前端
//批量上传
$(function () {
upload.render({
elem: '#inputBtn'
, url: 'warehouseExcelIn'
, accept: 'file'
, auto: true
, exts: 'xlsx|xls'
, before: function (obj) {
}
, done: function (res) {
if (res.success){
layer.msg(res.data, {icon: 6, time: 1000});
}else {
layer.msg(data.data, {icon: 5, time: 1000});
}
}
});
});
<button type="button" class="layui-btn layui-btn-primary layui-btn-sm" lay-data="{url:'warehouseExcelIn'}"
id="inputBtn"><i class="layui-icon"></i>批量导入
<!-- <button type="button" class="layui-btn test333" lay-data="{size:10,url:'warehouseExcelIn'}" id="test3"><i class="layui-icon"></i>上传文件-->
</button>