1.pom.xml中引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.0-beta2</version>
</dependency>
2.工具类:
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import org.springframework.web.multipart.MultipartFile;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class EasyExcels {
/**
*
* @param file 导入的Excel文件
* @param tClass 对应excel实体bean
* @return 对应excel实体bean的list
*/
public static<T> List<Map<String ,String>> getExcelContent(MultipartFile file, Class<T> tClass){
List<Map<String ,String>> excelPropertyIndexModelList = new ArrayList<>();
try {
//监听器
AnalysisEventListener<Map<String ,String>> listener = new AnalysisEventListener<Map<String ,String>>() {
//读取每一行的数据
@Override
public void invoke(Map<String ,String> excelPropertyIndexModel, AnalysisContext analysisContext) {
excelPropertyIndexModelList.add(excelPropertyIndexModel);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//读取之后的操作
}
};
/* file.getInputStream() 输入流
* listener 自定义的监听器
*/
ExcelReader excelReader = EasyExcel.read(file.getInputStream(), listener).build();
//readSheet(0) 指定读取哪一页的数据
ReadSheet sheet = EasyExcel.readSheet(0).build();
excelReader.read(sheet);
//读取所有的sheet页的数据
//excelReader.readAll();
//关闭,读的时候会创建临时文件,到时磁盘会崩的
excelReader.finish();
} catch (Exception e) {
e.printStackTrace();
}
return excelPropertyIndexModelList;
}
}
3.业务层代码
@ApiOperation(value = "Excel导入", notes="月份统计数据Excel导入接口")
@PostMapping(value = "/importExcel")
@ResponseBody
public String updateExcel(@Param("Filedata") MultipartFile file){
try {
List<Map<String ,String>> excelContentList = EasyExcels.getExcelContent(file, Month.class);
List<Month> monthList = new ArrayList<>();
for (int i =1;i<excelContentList.size();i++){
Month month = new Month();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = format.parse(format.format(new Date()));
Map<String ,String> list = excelContentList.get(i);
if (list.size()>0) {
month.setYear(Integer.valueOf(list.get(0)));
month.setMonth(Integer.valueOf(list.get(1)));
month.setDatatype(Integer.valueOf(list.get(2)));
month.setAmount(Integer.valueOf(list.get(3)));
month.setPercent(Float.valueOf(list.get(4)));
month.setUpdatetime(date);
Long id = snowFlake.nextId();
month.setPk(id.intValue());
System.out.println(JSON.toJSON(month));
monthList.add(month);
}
}
}
4.Excel表格
5.数据库表字段
6.运行结果