程序员必备小知识系列--记录一次工作中Springboot的Excel导入导出功能
前不久有个朋友让我写个导入导出功能给他，由于当时忙于工作，就让他在网上找找看，网上有很多。 没多久，自己就做了个导入导出的功能。Excel导入导出功能在开发中还是蛮常见的。而碰巧的是，这个需求碰巧是我写的上一篇博客的拓展功能。这里记录一下自己做的excel导入导出功能。 需求，原型图很简单：
很常见的一个excel导入导出需求 excel模板长这样：
下载下来的客户清单如图：
这个没什么难度，就是从数据库抓取数据，把数据带下来放在excel文件里 废话不多说，直接看代码
Controller层代码
@Autowired private FirstOrderHistoryDataService firstOrderHistoryDataService; @RequestMapping(value = "/download-customer-list", method = RequestMethod.GET, produces = "application/xls") public ResponseEntity<byte[]> downCustomerList() { return firstOrderHistoryDataService.downCustomerList(); } 复制代码Service层代码
ResponseEntity<byte[]> downCustomerList(); 复制代码
实现类
@Autowired private ResourceLoader resourceLoader;//spring自带的获取资源的接口 @Autowired private CustomerRepository customerRepository; @Override public ResponseEntity<byte[]> downCustomerList() { HttpHeaders headers = new HttpHeaders(); headers.setContentType(MediaType.parseMediaType("application/xls")); String excelName = "客户清单.xls"; headers.setContentDispositionFormData(excelName, excelName); byte[] contents = buildExcelDocument(); ResponseEntity<byte[]> response = new ResponseEntity<byte[]>(contents, headers, HttpStatus.OK); return response; } private byte[] buildExcelDocument() { ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); Workbook workbook; try { //在reource资源包下放个excel模板，并加载这个excel文件 Resource resource = resourceLoader.getResource("classpath:excel-templates/customerlist.xls"); InputStream stream = resource.getInputStream(); workbook = WorkbookFactory.create(stream); Sheet sheet = workbook.getSheetAt(0); setSheetBody(sheet, workbook); workbook.write(outputStream); workbook.close(); } catch (IOException e) { e.printStackTrace(); } catch (EncryptedDocumentException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } return outputStream.toByteArray(); } private void setSheetBody(Sheet sheet, Workbook workbook) { int rowSeq = 0; int dataRowNum = 3; //获取所有的客户数据（Repository层的findAll()方法获取所有数据） List<Customer> customers = Lists.newArrayList(customerRepository.findAll()).stream() .sorted(Comparator.comparing(Customer::getCustomerNo)).collect(Collectors.toList());//按照客户编号排序 for (Customer customer : customers) { rowSeq++; sheet.shiftRows(dataRowNum, sheet.getLastRowNum(), 1); Row dataRow = sheet.createRow(dataRowNum); dataRow.createCell(1).setCellValue(rowSeq); dataRow.createCell(2).setCellValue(customer.getCustomerNo()); dataRow.createCell(3).setCellValue(customer.getShortName()); for (int i = 1; i <= 3; i++) { //调整excel格式 ExcelFormat.setCellStyle(workbook, dataRow, i, false, "", HorizontalAlignment.CENTER, null, true, true, true, true); } dataRowNum++; } } 复制代码OK，下载功能就做好了，接下来就是上传功能，上传功能也不是很难，不过，校验比较多。 现在把这个代码放下来。 Controller层代码：
import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.multipart.MultipartFile; @RestController @RequestMapping("/api/historydata/first-order-history-data") public class FirstOrderHistoryDataController extends ExceptionResponse { @Autowired private FirstOrderHistoryDataService firstOrderHistoryDataService; @RequestMapping(value = "/download-customer-list", method = RequestMethod.GET, produces = "application/xls") public ResponseEntity<byte[]> downCustomerList() { return firstOrderHistoryDataService.downCustomerList(); } //上传excel功能 @RequestMapping(value = "/upload-history-data-excel", method = RequestMethod.POST) public String uploadHistoryDataExcel(@RequestParam("file") MultipartFile file) { return firstOrderHistoryDataService.uploadHistoryDataExcel(file); } } 复制代码Service层代码
import org.springframework.http.ResponseEntity; import org.springframework.web.multipart.MultipartFile; public interface FirstOrderHistoryDataService { //下载 ResponseEntity<byte[]> downCustomerList(); //上传 String uploadHistoryDataExcel(MultipartFile file); } 复制代码实现类
import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Comparator; import java.util.Date; import java.util.List; import java.util.Set; import java.util.regex.Pattern; import java.util.stream.Collectors; import javax.transaction.Transactional; import org.apache.poi.EncryptedDocumentException; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.core.io.Resource; import org.springframework.core.io.ResourceLoader; import org.springframework.http.HttpHeaders; import org.springframework.http.HttpStatus; import org.springframework.http.MediaType; import org.springframework.http.ResponseEntity; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import com.google.common.collect.Lists; @Service @Transactional public class FirstOrderHistoryDataServiceImpl implements FirstOrderHistoryDataService { @Autowired private ResourceLoader resourceLoader; @Autowired private CustomerRepository customerRepository; @Autowired private FirstOrderHistoryDataRepository firstOrderHistoryDataRepository; //下载功能 @Override public ResponseEntity<byte[]> downCustomerList() { HttpHeaders headers = new HttpHeaders(); headers.setContentType(MediaType.parseMediaType("application/xls")); String excelName = "客户清单.xls"; headers.setContentDispositionFormData(excelName, excelName); byte[] contents = buildExcelDocument(); ResponseEntity<byte[]> response = new ResponseEntity<byte[]>(contents, headers, HttpStatus.OK); return response; } private byte[] buildExcelDocument() { ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); Workbook workbook; try { Resource resource = resourceLoader.getResource("classpath:excel-templates/customerlist.xls"); InputStream stream = resource.getInputStream(); workbook = WorkbookFactory.create(stream); Sheet sheet = workbook.getSheetAt(0); setSheetBody(sheet, workbook); workbook.write(outputStream); workbook.close(); } catch (IOException e) { e.printStackTrace(); } catch (EncryptedDocumentException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } return outputStream.toByteArray(); } private void setSheetBody(Sheet sheet, Workbook workbook) { int rowSeq = 0; int dataRowNum = 3; List<Customer> customers = Lists.newArrayList(customerRepository.findAll()).stream() .sorted(Comparator.comparing(Customer::getCustomerNo)).collect(Collectors.toList()); for (Customer customer : customers) { rowSeq++; sheet.shiftRows(dataRowNum, sheet.getLastRowNum(), 1); Row dataRow = sheet.createRow(dataRowNum); dataRow.createCell(1).setCellValue(rowSeq); dataRow.createCell(2).setCellValue(customer.getCustomerNo()); dataRow.createCell(3).setCellValue(customer.getShortName()); for (int i = 1; i <= 3; i++) { ExcelFormat.setCellStyle(workbook, dataRow, i, false, "", HorizontalAlignment.CENTER, null, true, true, true, true); } dataRowNum++; } } //上传功能 @Override public String uploadHistoryDataExcel(MultipartFile file) { StringBuilder sBuilder = new StringBuilder(); Workbook workbook; try { InputStream stream = file.getInputStream(); workbook = WorkbookFactory.create(stream); Sheet sheet = workbook.getSheetAt(0); //构建FirstOrderHistoryData对象数据，之后返回List集合 List<FirstOrderHistoryData> firstOrderHistoryDatas = genFirstOrderHistoryDataList(sheet); workbook.close(); isExcelRepeat(sheet, firstOrderHistoryDatas);//校验excel里面的数据是否重复 isDataBaseRepeat(sheet, firstOrderHistoryDatas);//跟数据库的数据做校验 validCustomerNoAndCustomerName(firstOrderHistoryDatas);//校验客户编号和客户名称是否对应 //在数据库中找到所有的数据 List<FirstOrderHistoryData> allHistoryDatas = firstOrderHistoryDataRepository.findAll(); //用excel里面的数据和数据库中的数据进行对比，方便之后进行操作（存在的数据进行覆盖，没有的数据直接插入） firstOrderHistoryDatas.stream().forEach(row -> { FirstOrderHistoryData firstOrderHistoryData = allHistoryDatas.stream() .filter(hdata -> hdata.getCustomerName().equals(row.getCustomerName())).findFirst() .orElse(null); if (firstOrderHistoryData != null) { row.setId(firstOrderHistoryData.getId()); } }); firstOrderHistoryDataRepository.save(firstOrderHistoryDatas); sBuilder.append("导入数据成功.更新历史数据" + firstOrderHistoryDatas.size() + "条."); } catch (IOException | EncryptedDocumentException | InvalidFormatException e) { e.printStackTrace(); } return sBuilder.toString(); } //客户编号和客户名称是一一对应的，为了以防被修改，所以进行这个校验 private void validCustomerNoAndCustomerName(List<FirstOrderHistoryData> firstOrderHistoryDatas) { List<Customer> customers = Lists.newArrayList(customerRepository.findAll()); firstOrderHistoryDatas.stream().forEach(row -> { Customer cus = customers.stream().filter(customer -> customer.getCustomerNo().equals(row.getCustomerNo())) .findFirst().orElse(null); if (!(cus != null && cus.getShortName().trim().toLowerCase().equals(row.getCustomerName().trim().toLowerCase()))) { throw new ExcelContentException( "上传历史数据文件中客户编号与客户名称不对应! " + "请查看客户主数据中与客户名称对应的客户编号! 客户名称为：" + row.getCustomerName()); } }); } //构建FirstOrderHistoryData集合 private List<FirstOrderHistoryData> genFirstOrderHistoryDataList(Sheet sheet) { List<FirstOrderHistoryData> firstOrderHistoryDatas = new ArrayList<FirstOrderHistoryData>(); int lastRowNum = sheet.getLastRowNum(); if (lastRowNum <= 2) { throw new ExcelContentException("上传历史数据文件中没有有效数据."); } for (int i = 3; i <= lastRowNum; i++) { Row row = sheet.getRow(i); if (row == null) { throw new ExcelContentException("上传历史数据文件中的第" + (i + 1) + "行为空,请先确认删除本行"); } String customerNo = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 2); String customerName = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 3); Date orderDate = null; Date productionDate = null; Date stockDate = null; orderDate = validOrderDate(i, row, orderDate);//日期校验 String orderNo = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 5);//后面有这个excel工具类的代码 String orderAmount = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 6); productionDate = validProductionDate(i, row, productionDate);//日期校验 String productionNo = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 8); String productionAmount = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 9); stockDate = validStockDate(i, row, stockDate);//日期校验 String stockNo = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 11); String stockAmount = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 12); //构建FirstOrderHistoryData对象 firstOrderHistoryDatas.add(genFirstOrder(customerNo, customerName, orderDate, orderNo, orderAmount, productionDate, productionNo, productionAmount, stockDate, stockNo, stockAmount, i)); } return firstOrderHistoryDatas; } private Date validStockDate(int i, Row row, Date stockDate) { if (row.getCell(10) != null) { if (row.getCell(10).getCellTypeEnum() == CellType.NUMERIC) { if (HSSFDateUtil.isCellDateFormatted(row.getCell(10))) { stockDate = row.getCell(10).getDateCellValue(); } else { throw new ExcelContentException("第" + (i + 1) + "行的出货日期格式或时间错误，格式为yyyy-MM-dd."); } } else { throw new ExcelContentException("第" + (i + 1) + "行的出货日期格式或时间错误，格式为yyyy-MM-dd."); } } return stockDate; } private Date validProductionDate(int i, Row row, Date productionDate) { if (row.getCell(7) != null) { if (row.getCell(7).getCellTypeEnum() == CellType.NUMERIC) { if (HSSFDateUtil.isCellDateFormatted(row.getCell(7))) { productionDate = row.getCell(7).getDateCellValue(); } else { throw new ExcelContentException("第" + (i + 1) + "行的下生产单日期格式或时间错误，格式为yyyy-MM-dd."); } } else { throw new ExcelContentException("第" + (i + 1) + "行的下生产单日期格式或时间错误，格式为yyyy-MM-dd."); } } return productionDate; } private Date validOrderDate(int i, Row row, Date orderDate) { if (row.getCell(4) != null) { if (row.getCell(4).getCellTypeEnum() == CellType.NUMERIC) { if (HSSFDateUtil.isCellDateFormatted(row.getCell(4))) { orderDate = row.getCell(4).getDateCellValue(); } else { throw new ExcelContentException("第" + (i + 1) + "行的下订单日期格式或时间错误，格式为yyyy-MM-dd."); } } else { throw new ExcelContentException("第" + (i + 1) + "行的下订单日期格式或时间错误，格式为yyyy-MM-dd."); } } return orderDate; } private FirstOrderHistoryData genFirstOrder(String customerNo, String customerName, Date orderDate, String orderNo, String orderAmount, Date productionDate, String productionNo, String productionAmount, Date stockDate, String stockNo, String stockAmount, int i) { FirstOrderHistoryData firstOrderHistoryData = new FirstOrderHistoryData(); firstOrderHistoryData.setCustomerName(customerName); firstOrderHistoryData.setCustomerNo(customerNo); genOrderData(orderDate, orderNo, orderAmount, firstOrderHistoryData, i); genProduction(productionDate, productionNo, productionAmount, firstOrderHistoryData, i); genStock(stockDate, stockNo, stockAmount, firstOrderHistoryData, i); return firstOrderHistoryData; } private void genStock(Date stockDate, String stockNo, String stockAmount, FirstOrderHistoryData firstOrderHistoryData, int i) { firstOrderHistoryData .setStockDate(stockDate == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", stockDate)); if (stockNo != null && !isInteger(stockNo)) { throw new ExcelContentException("第" + (i + 1) + "行的出货订单号格式错误，必须为数字."); } else { firstOrderHistoryData.setStockNo(stockNo == null ? "" : stockNo); } if (stockAmount != null && !isInteger(stockAmount)) { throw new ExcelContentException("第" + (i + 1) + "行的出货金额格式错误，必须为数字."); } else { firstOrderHistoryData.setStockAmount(stockAmount == null ? 0 : Float.parseFloat(stockAmount)); } } private void genProduction(Date productionDate, String productionNo, String productionAmount, FirstOrderHistoryData firstOrderHistoryData, int i) { firstOrderHistoryData.setProductionDate( productionDate == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", productionDate)); if (productionNo != null && !isInteger(productionNo)) { throw new ExcelContentException("第" + (i + 1) + "行的下生产单订单号格式错误，必须为数字."); } else { firstOrderHistoryData.setProductionNo(productionNo == null ? "" : productionNo); } if (productionAmount != null && !isInteger(productionAmount)) { throw new ExcelContentException("第" + (i + 1) + "行的下生产单金额格式错误，必须为数字."); } else { firstOrderHistoryData .setProductionAmount(productionAmount == null ? 0 : Float.parseFloat(productionAmount)); } } private void genOrderData(Date orderDate, String orderNo, String orderAmount, FirstOrderHistoryData firstOrderHistoryData, int i) { firstOrderHistoryData .setOrderDate(orderDate == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", orderDate)); if (orderNo != null && !isInteger(orderNo)) { throw new ExcelContentException("第" + (i + 1) + "行的下订单的订单号格式错误，必须为数字."); } else { firstOrderHistoryData.setOrderNo(orderNo == null ? "" : orderNo); } if (orderAmount != null && !isInteger(orderAmount)) { throw new ExcelContentException("第" + (i + 1) + "行的下订单金额格式错误，必须为数字."); } else { firstOrderHistoryData.setOrderAmount(orderAmount == null ? 0 : Float.parseFloat(orderAmount)); } } //校验是否为数字 private boolean isInteger(String str) { Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$"); return pattern.matcher(str).matches(); } //校验excel中客户名字是否重复（需求：一个客户只能有一条数据） private void isExcelRepeat(Sheet sheet, List<FirstOrderHistoryData> firstOrderHistoryDatas) { List<FirstOrderHistoryData> repeatRecords = firstOrderHistoryDatas.stream() .collect(Collectors.groupingBy(FirstOrderHistoryData::getCustomerName)).entrySet().stream() .filter(entry -> entry.getValue().size() > 1).map(entry -> entry.getValue().get(0)) .collect(Collectors.toList()); if (repeatRecords != null && repeatRecords.size() > 0) { throwExceptionOfRepeatRecordRowNums(sheet, repeatRecords, false); } } private void throwExceptionOfRepeatRecordRowNums(Sheet sheet, List<FirstOrderHistoryData> repeatRecords, boolean isDataBaseRepeatException) { List<String> customerNames = new ArrayList<String>(); List<String> rowNumsOfEachCustomer = new ArrayList<String>(); repeatRecords.stream().forEach(record -> getRepeatCustomerNamesAndExcelRowNumsOfEachCustomer(sheet, record, customerNames, rowNumsOfEachCustomer)); String allCustomerNamesStr = customerNames.stream().collect(Collectors.joining(", ", "[", "]")); String allrowNumsStr = rowNumsOfEachCustomer.stream().collect(Collectors.joining(", ", "[", "]")); if (customerNames != null && customerNames.size() > 0 && rowNumsOfEachCustomer != null && rowNumsOfEachCustomer.size() > 0) { if (!isDataBaseRepeatException) { throw new ExcelContentException("上传的历史数据文件中存在客户名称为 " + allCustomerNamesStr + " 的重复记录,分别位于 " + allrowNumsStr + " 行.一个客户只能有一条数据!"); } } } private void getRepeatCustomerNamesAndExcelRowNumsOfEachCustomer(Sheet sheet, FirstOrderHistoryData record, List<String> customerNames, List<String> rowNumsOfEachCustomer) { customerNames.add(record.getCustomerName()); Set<Integer> rowNums = ExcelUtil.findRows(sheet, record.getCustomerName()); String rowNumStr = rowNums.stream().map(rowNum -> String.valueOf((rowNum + 1))).collect(Collectors.toSet()) .stream().collect(Collectors.joining(", ", "[", "]")); rowNumsOfEachCustomer.add(rowNumStr); } //校验上传的excel文件中在数据库中存在不存在 private void isDataBaseRepeat(Sheet sheet, List<FirstOrderHistoryData> firstOrderHistoryDatas) { Set<String> customerNames = firstOrderHistoryDatas.stream().map(customerName -> customerName.getCustomerName()) .collect(Collectors.toSet()); List<Customer> repeatRecords = customerRepository.getCustomersByCustomerNameIn(customerNames); if (firstOrderHistoryDatas.size() != repeatRecords.size()) { throw new ExcelContentException("上传历史数据文件中的客户名称在数据库中不存在"); } } } 复制代码excel工具类ExcelUtil类的代码
public static String getStringCellValueWithNumOrStr(Sheet sheet, int rowNom, int colNum) { Cell cell = sheet.getRow(rowNom).getCell(colNum); if (cell != null) { CellType cellType = cell.getCellTypeEnum(); if (cellType == CellType.STRING) { return cell.getStringCellValue().trim().toUpperCase(); } else if (cellType == CellType.NUMERIC) { return String.valueOf((long) cell.getNumericCellValue()); } else { return null; } } return null; } 复制代码看效果：
作者：exodus3
链接：https://juejin.cn/post/7021525134087290893