cn.hutool
hutool-all
5.1.0
com.alibaba
easyexcel
2.2.5
2.实现导入
@PostMapping("/import")
@ApiOperation("导入")
public Result fileUpload(
@RequestParam("file") MultipartFile file
) {
try {
//使用hutool工具类导入Excel文件
ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
//读取excel中的数据，与User实体类一一对应
List listData = reader.readAll(User.class);
//批量存入数据库中
//userTaskService.saveimportTask(listData);
} catch (IOException e) {
e.printStackTrace();
}
return new Result<>();
}
3.实现导出
@GetMapping(value = "/export")
@ApiOperation(value = "导出")
public void statisticsExport(
@ApiParam(name = "province", value = "省")
@RequestParam("province") String province,
@ApiParam(name = "city", value = "市")
@RequestParam("city") String city,
@ApiParam(name = "counter", value = "区")
@RequestParam("counter") String counter,
@ApiParam(name = "startTime", value = "开始时间")
@RequestParam("startTime") Long startTime,
@ApiParam(name = "endTime", value = "结束时间")
@RequestParam("endTime") Long endTime,
@ApiParam(name = "orderByType", value = "排序类型 ASC 升序 DESC 倒序 默认倒序")
@RequestParam("orderByType") String orderByType,
HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("统计", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
FastDateFormat fastDateFormat = FastDateFormat.getInstance(DatePattern.NORM_DATETIME_PATTERN, TimeZone.getTimeZone("Asia/Shanghai"));
String startTimeStr = fastDateFormat.format(DateUtil.beginOfDay(new DateTime(startTime, TimeZone.getTimeZone("Asia/Shanghai"))));
String endTimeStr = fastDateFormat.format(DateUtil.endOfDay(new DateTime(endTime, TimeZone.getTimeZone("Asia/Shanghai"))));
// DemoDTO 查询条件入参
DemoDto dto= new DemoDto();
dto.setProvince(province);
dto.setCity(city);
dto.setCounter(counter);
dto.setStartTime(startTimeStr);
dto.setEndTime(endTimeStr);
dto.setOrderByType(orderByType);
//根据查询条件查询数据库---把需要导出的数据放到list中
List list = task.findStatisByParams(DemoDto);
// 这里需要设置不关闭流
String dateTitle = "时间段：" + fastDateFormat.format(new DateTime(startTime,TimeZone.getTimeZone("Asia/Shanghai"))) + "至" + DateUtil.formatDate(new DateTime(endTime, TimeZone.getTimeZone("Asia/Shanghai")));
String rangeTitle = "范围:" +
(StrUtil.isBlank(province) ? "全部" : province) + "/" +
(StrUtil.isBlank(city) ? "全部" : city) + "/" +
(StrUtil.isBlank(counter) ? "全部" : counter);
EasyExcel.write(response.getOutputStream(), ClientDetailStatisVO.class)
.head(ClientDetailStatisVO.head(dateTitle, rangeTitle))
.autoCloseStream(Boolean.FALSE).sheet("统计")
//上面从数据库查出来的数据
.doWrite(statisByParams);
} catch (Exception e) {
// 重置response
response.reset();
throw new CustomException(Result.Status.INVALID_PARAM);
}
}
第二种方法：poi
1. Excel中有两条数据，我们需要通过java利用IO流操作，把这两条数据，导入进我们的mysql数据库。
2. 在pom.xml 中导入POI依赖
3.在model中建立实体类，与Excel中的表头一致----------实体类org.apache.poi poi-ooxml 3.9
@Data
@ApiModel("java批量导入")
@Table(name = "excel")
public class ExcelModel {
@Id
@Column(name = "id")
@GeneratedValue(generator="JDBC")
@ApiModelProperty("主键")
private Integer id;
@ApiModelProperty("公司名字")
private String companyName;
@ApiModelProperty("职位名称")
private String jobTitle;
@ApiModelProperty("年薪")
private String annualSalary;
@ApiModelProperty("候选人姓名")
private String name;
public void toPo(ExcelModelFormBean formBean) {
this.id = formBean.getId();
this.companyName = formBean.getCompanyName();
this.jobTitle = formBean.getJobTitle();
this.annualSalary = formBean.getAnnualSalary();
this.name = formBean.getName();
}
}
4.通过IO流批量获取Excel中的信息转化成list集合-------工具类
public static List getExcelFile(MultipartFile file) throws IOException {
List list = new linkedList<>();
String fileName = file.getOriginalFilename();
if (StringUtils.isEmpty(fileName)) {
return Collections.emptyList();
}
if (!fileName.matches("^.+\.(?i)(xls)$") && !fileName.matches("^.+\.(?i)(xlsx)$")) {
return Collections.emptyList();
} else {
boolean isExcel2003 = true;
if (fileName.matches("^.+\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
InputStream is = file.getInputStream();
Workbook workbook;
if (isExcel2003) {
workbook = new HSSFWorkbook(is);
} else {
workbook = new XSSFWorkbook(is);
}
Sheet sheet = workbook.getSheetAt(0);
if (null == sheet) {
return Collections.emptyList();
}
//r = 1 表示从第二行开始循环 如果你的第三行开始是数据
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
//通过sheet表单对象得到 行对象
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
ExcelDateInfo info = new ExcelDateInfo();
if (row.getCell(0) != null) {
//得到每一行第二个单元格的值
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
info.setColumn1(row.getCell(0).getStringCellValue());
}
if (row.getCell(1) != null) {
//得到每一行的 第三个单元格的值
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
info.setColumn2(row.getCell(1).getStringCellValue());
}
if (row.getCell(2) != null) {
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
info.setColumn3(row.getCell(2).getStringCellValue());
}
if (row.getCell(3) != null) {
row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
info.setColumn4(row.getCell(3).getStringCellValue());
}
// 对象放入集合
list.add(info);
}
}
return list;
}
5.调用sql批量添加到数据库中
@Override
@Transactional(rollbackFor = Exception.class)
public Result saveOrderList(MultipartFile file) {
String fileName = file.getOriginalFilename();
if (StringUtils.isEmpty(fileName)) {
return new Result<>(Result.Status.INVALID_PARAM);
}
if (StringUtils.equals(fileName.substring(fileName.lastIndexOf(".")), "xlsx")) {
return new Result<>(Result.Status.INVALID_PARAM);
}
try {
//读取Excel文件转化成list
List list = FileUtil.getExcelFile(file);
if (CollectionUtils.isEmpty(list) || list.size() < 2) {
return new Result<>(Result.Status.EMPTY_DATA);
}
ExcelDateInfo excel = list.get(0);
// 验证文件头是否正确
if (!"公司".equals(excel.getColumn1()) || !"职位".equals(excel.getColumn2()) ||
!"年薪".equals(excel.getColumn3()) || !"姓名".equals(excel.getColumn4())) {
return new Result<>(Result.Status.TEMPLATE_ERROR);
} else {
list.remove(0);
}
List orderList = new ArrayList<>();
for (ExcelDateInfo info : list) {
if (StringUtils.isEmpty(info.getColumn1()) && StringUtils.isEmpty(info.getColumn2()) &&
StringUtils.isEmpty(info.getColumn3()) && StringUtils.isEmpty(info.getColumn4())) {
continue;
}
ServiceHistoryOrder order = new ServiceHistoryOrder();
// 判断公司列长度
if (StringUtils.isEmpty(info.getColumn1())) {
return new Result<>(Result.Status.EMPTY_DATA);
} else if (info.getColumn1().length() > 500) {
return new Result<>(Result.Status.DATA_TOO_LONG);
} else {
order.setCompanyName(info.getColumn1());
}
// 判断职位列长度
if (StringUtils.isEmpty(info.getColumn2())) {
return new Result<>(Result.Status.EMPTY_DATA);
} else if (info.getColumn2().length() > 500) {
return new Result<>(Result.Status.DATA_TOO_LONG);
} else {
order.setJobTitle(info.getColumn2());
}
// 判断年薪列长度
if (StringUtils.isEmpty(info.getColumn3())) {
return new Result<>(Result.Status.EMPTY_DATA);
} else if (info.getColumn3().length() > 500) {
return new Result<>(Result.Status.DATA_TOO_LONG);
} else {
order.setAnnualSalary(info.getColumn3());
}
// 判断姓名列长度
if (StringUtils.isEmpty(info.getColumn4())) {
return new Result<>(Result.Status.EMPTY_DATA);
} else if (info.getColumn4().length() > 500) {
return new Result<>(Result.Status.DATA_TOO_LONG);
} else {
order.setName(info.getColumn4());
}
orderList.add(order);
}
// 调用批量插入的sql语句，把excel数据插入到数据库 返回成功条数
int result = orderMapper.saveHistoryOrderList(orderList);
if (result == 0) {
return new Result<>(Result.Status.ERROR);
}
return new Result<>(result);
} catch (Exception e) {
e.printStackTrace();
return new Result<>(Result.Status.ERROR);
}
}
Best Regards！
Make a little progress every day！
