需要导入的pom:
com.alibaba
easyexcel
2.0.5
com.alibaba
fastjson
1.2.71
controller:
@RequestMapping("/export")
public void export(HttpServletResponse response) throws IOException {
response.setHeader("Content-Disposition",
"attachment;fileName=" + URLEncoder.encode("地址.xlsx", "UTF-8"));
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
WriteSheet sheet = EasyExcel.writerSheet(0).head(tAddressCity.class).build();
List list = addressService.filefindAll();
excelWriter.write(list, sheet);
excelWriter.finish();
}
@RequestMapping("/downloadModel")
public void downloadModel(HttpServletResponse response) throws IOException {
response.reset();
response.setContentType("application/octet-stream");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition",
"attachment;fileName=" + URLEncoder.encode("模板.xlsx", "UTF-8"));
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
WriteSheet sheet = EasyExcel.writerSheet(0).head(tAddressCity.class).build();
excelWriter.write(Collections.emptyList(), sheet);
excelWriter.finish();
}
@ApiOperation(value = "导入单个sheet数据")
@PostMapping("/importSheet")
public void importSheet(@RequestParam("multipartFile") MultipartFile multipartFile) {
try {
InputStream inputStream = multipartFile.getInputStream();
//异步读取所有sheet数据,可在sheet方法参数中指定sheet索引,sheet名称
EasyExcel.read(inputStream, tAddressCity.class, new UserDataListener(addressService)).sheet().doRead();
} catch (IOException e) {
e.printStackTrace();
}
}
监听器:
注意: 如何注入一个非被spring管理的类,需要手动注入才能使用
import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.fastjson.JSON; import com.yjxxt.crm.bean.tAddressCity; import com.yjxxt.crm.service.AddressService; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.util.ArrayList; import java.util.List; public class UserDataListener extends AnalysisEventListener到处多张表sheet{ private static final Logger LOGGER = LoggerFactory.getLogger(UserDataListener.class); private static final int BATCH_COUNT = 3000; List list = new ArrayList<>(); private AddressService addressService;; public UserDataListener(AddressService addressService) { this.addressService = addressService; } @Override public void invoke(tAddressCity data, AnalysisContext context) { LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data)); list.add(data); if (list.size() >= BATCH_COUNT) { saveData(); list.clear(); } } @Override public void doAfterAllAnalysed(AnalysisContext context) { saveData(); LOGGER.info("所有数据解析完成!"); } private void saveData() { LOGGER.info("{}条数据,开始存储数据库!", list.size()); for (tAddressCity tAddressCity : list) { addressService.insert(tAddressCity); System.out.println(tAddressCity.toString()); } LOGGER.info("存储数据库成功!"); } }
@ApiOperation(value = "导入单个sheet数据")
@PostMapping("/importSheet")
public void importSheet(@RequestParam("multipartFile") MultipartFile multipartFile) {
try {
InputStream inputStream = multipartFile.getInputStream();
//异步读取所有sheet数据,可在sheet方法参数中指定sheet索引,sheet名称
EasyExcel.read(inputStream, User.class, new UserDataListener(userService)).sheet().doRead();
} catch (IOException e) {
e.printStackTrace();
}
}
@ApiOperation(value = "导入多个sheet数据")
@PostMapping("/importSheets")
public void importSheets(@RequestParam("multipartFile") MultipartFile multipartFile) {
ExcelReader excelReader = null;
try {
InputStream inputStream = multipartFile.getInputStream();
excelReader = EasyExcel.read(inputStream).build();
ReadSheet readSheet1 =
//构建ExcelReader对象
EasyExcel.readSheet(0).head(User.class).registerReadListener(new UserDataListener(userService)).build();
ReadSheet readSheet2 =
//构建ExcelReader对象
EasyExcel.readSheet(1).head(Achievement.class).registerReadListener(new AchievementDataListener(achievementService)).build();
//这里注意 一定要把sheet1 sheet2 一起传进去
excelReader.read(readSheet1, readSheet2);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (excelReader != null) {
// 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
excelReader.finish();
}
}
}
@ApiOperation(value = "导出数据", produces = "application/octet-stream")
@PostMapping("/simpleWrite")
public void simpleWrite(HttpServletResponse response) {
userService.simpleWrite(response);
}
@ApiOperation(value = "分页导出数据", produces = "application/octet-stream")
@PostMapping("/pageWrite")
public void pageWrite(HttpServletResponse response) {
userService.pageWrite(response);
}
@ApiOperation(value = "分页导出多sheet数据", produces = "application/octet-stream")
@PostMapping("/pageWriteSheets")
public void pageWriteSheets(HttpServletResponse response) {
userService.pageWriteSheets(response);
}
@ApiOperation(value = "分页导出多sheet数据发送邮件")
@PostMapping("/writeSendMail")
public void writeSendMail(HttpServletResponse response) throws IOException {
userService.writeSendMail(response);
}
}



