记录一次工作中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;
}
看效果：
本文来自博客园，作者：暗影月色程序猿，转载请注明原文链接：https://www.cnblogs.com/Geneling/p/15249192.html
