记录一次工作中Springboot的Excel导入导出功能

最新推荐文章于 2024-08-15 00:07:53 发布

exodus3

最新推荐文章于 2024-08-15 00:07:53 发布

阅读量1.1w

点赞数

分类专栏： Java

本文链接：https://blog.csdn.net/exodus3/article/details/102172138

版权

Java 专栏收录该内容

37 篇文章 8 订阅

订阅专栏

前不久有个朋友让我写个导入导出功能给他，由于当时忙于工作，就让他在网上找找看，网上有很多。
没多久，自己就做了个导入导出的功能。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;
}