后端Springboot前端VUE实现Excel导⼊功能
功能描述：
做的是物联⽹的项⽬，Excel导⼊实现的功能是将Excel中的数据批量的导⼊AEP系统，再导⼊我们系统中。⽬前已经完成该功能，前端还会添加进度条优化。
对于导⼊导出功能，推荐这个Git:
Excel模板：
前端向后端传递的参数：
前端代码：
<Upload
name="wlwDeviceFile"
ref="upload"
:action="pathUrl"
:on-success="handleSuccess"
:on-error="handleError"
:format="['xls','xlsx']"
:max-size="5120"
:on-format-error="handleFormatError"
:on-exceeded-size="handleMaxSize"
:
before-upload="handleBeforeUpload"
:data="extraData"
:disabled="!isLock"
>
<Button type="primary">选择⽂件</Button>
</Upload>
data() {
return {
isLock: true,
mesg: false,
extraData: {
aepProductId: localStorage.productId,
productId: localStorage.productItemId,
projectId: localStorage.currentProjectId
},
mes: "",
cod: 0,
// currentShow: false,
pathUrl: Util.baseUrl + "/api/excel/import",
wlwDevice: {
fileName: "", //⽤于显⽰上传⽂件名
id: localStorage.productId,
autoSubscribe: 0
},
ruleValidate: {
fileName: [
{
required: true,
message: "请选择⽂件",
trigger: "change"
}
]
}
};
},
handleSuccess(res, file) {
// console.log("res++++++++++++++++++++++++++", res);
if (de == 0) {
console.log(this.$refs.upload.fileList);
// this.$refs.upload.fileList.splice(0, 1);
} else {
}
this.isLock = true;
// this.$Spin.hide();
this.wlwDevice.fileName = file.name;
if (this.$refs.upload.fileList.length > 1) {
this.$refs.upload.fileList.splice(0, 1);
}
// this.$emit("cancel", this.isOpen);
},
handleSubmit(name) {
console.log("----------------------");
this.$refs[name].validate(valid => {
if (valid) {
this.$refs[name].resetFields();
this.$emit("cancel", this.isOpen);
this.$emit("refreshList");
console.log("-------------@@@--------");
} else {
this.$(this.$t("errorText"));
}
后端解析 Excel，将数据读取出来导⼊AEP系统
导⼊我们系统：
后端代码：
导⼊POM依赖：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>
解析Excel的⽅法：
@Component
@NoArgsConstructor
public class ParseExcelUtil {
private static final Logger log = Logger(ParseExcelUtil.class);
public static Workbook getWorkbook(InputStream is, String fileName) throws ExcelIOException {
Workbook workbook = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
// 推荐使⽤poi-ooxml中的ate(is)来创建Workbook,
// 因为HSSFWorkbook和XSSFWorkbook都实现了Workbook接⼝（可以解决以下报错问题）
try {
if (Suffix().equals(fileType)) {
workbook = new HSSFWorkbook(is);
} else if (Suffix().equals(fileType)) {
workbook = new XSSFWorkbook(is);
}
}catch (IOException e){
// ⽆效后缀名称，这⾥之能保证excel的后缀名称，不能保证⽂件类型正确，不过没关系，在创建Workbook的时候会校验⽂件格式            throw new ExcelIOException("请上传⽂件！");
}
return workbook;
}
}
Excel版本问题的枚举：
@NoArgsConstructor
public enum ExcelVersion {
/**
* 虽然V2007版本⽀持最⼤⽀持1048575 * 16383 ，
* V2003版⽀持65535*255
* 但是在实际应⽤中如果使⽤如此庞⼤的对象集合会导致内存溢出，
* 因此这⾥限制最⼤为10000*100，如果还要加⼤建议先通过单元测试进⾏性能测试。
* 1000*100 全部导出预计时间为27s左右
*/
V2003(".xls", 10000, 100), V2007(".xlsx", 100, 100);
private String suffix;
private int maxRow;
private int maxColumn;
ExcelVersion(String suffix, int maxRow, int maxColumn) {
this.suffix = suffix;
this.maxRow = maxRow;
this.maxColumn = maxColumn;
}
public String getSuffix() {
return this.suffix;
}
public int getMaxRow() {
return maxRow;
}
void setMaxRow(int maxRow) {
this.maxRow = maxRow;
}
public int getMaxColumn() {
return maxColumn;
}
void setMaxColumn(int maxColumn) {
this.maxColumn = maxColumn;
}
void setSuffix(String suffix) {
this.suffix = suffix;
}
Long型转ZonedDateTime型⽅法：
/**
* 将Long类型转化成0
* @author yk
* @param time
* @return
*/
public static ZonedDateTime toZonedDateTime(Long time){
SimpleDateFormat sdf = new SimpleDateFormat(LONG_DATE);
Date createDate = new Date(time);
String format = sdf.format(createDate);
DateTimeFormatter beijingFormatter = DateTimeFormatter.ofPattern(LONG_DATE).withZone(ZoneId.of("Asia/Shanghai"));
if(StringUtils.isBlank(format)){
return null;
}
ZonedDateTime beijingDateTime = ZonedDateTime.parse(format, beijingFormatter);
ZonedDateTime utc = beijingDateTime.withZoneSameInstant(ZoneId.of("UTC"));
return utc;
}
控制层
@RestController
@RequestMapping("/api/excel")
public class ImportExcelController {
private final Logger log = Logger(ImportExcelController.class);
private final WlwDeviceService wlwDeviceService;
@Autowired
private ImportExcelService importExcelService;
public ImportExcelController(WlwDeviceService wlwDeviceService) {
this.wlwDeviceService = wlwDeviceService;
}
@PostMapping("/import")
public ImportExcelResult excelToWlwDevice(@RequestParam("wlwDeviceFile") MultipartFile wlwDeviceFile, @RequestParam("productId") String productId, @RequestParam("aepProductId") String aepProductId, @RequestParam("projectId")        lToWlwDeviceService(wlwDeviceFile, productId, aepProductId, projectId);
}
Service接⼝：
public interface ImportExcelService {
/**
* 导⼊
* @param wlwDeviceFile
* @param productId
* @param aepProductId
* @param projectId
* @return
*/
ImportExcelResult excelToWlwDeviceService(MultipartFile wlwDeviceFile, String productId, String aepProductId, String projectId);
}
Service实现类：
@Service
public class ImportExcelServiceImpl implements ImportExcelService {
private static final Logger log = Logger(ImportExcelServiceImpl.class);
@Autowired
private AepApiUtil util;
@Autowired
private WlwDeviceRepository wlwDeviceRepository;
@Override
public ImportExcelResult excelToWlwDeviceService(MultipartFile wlwDeviceFile, String productId, String aepProductId, String projectId) {
ImportExcelResult importExcelResult = new ImportExcelResult(0, "导⼊成功");
//List<String> sList = new ArrayList<>();
if (wlwDeviceFile.isEmpty()) {
importExcelResult.setCode(100);
importExcelResult.setMessage("填写模版信息不能为空");
return importExcelResult;
}
//1.将excel中的数据放⼊list中
List<AepDeviceDTO> aepDeviceDTOList = new ArrayList<>();
//解析Excel⽂件
try {
InputStream is = InputStream();
int size = 0;
String imei = null;
//⼚家名称
String manufacturer = null;
//型号
String productType = null;
/
/设备名称
String deviceName = null;
//⾃动订阅
String autoObserver = null;
//设备类型
String deviceType = null;
StringBuilder megBuilder = new StringBuilder();
String filename = OriginalFilename();
//1.根据Excel⽂件创建⼯作簿
Workbook wb = Workbook(is, filename);
//2.获取Sheet
Sheet sheet = wb.getSheetAt(0);
if (sheet != null) {
WlwDevice wlwDevice = new WlwDevice();
//3.获取Sheet中的每⼀⾏，和每⼀个单元格，数据从第⼆⾏开始
for (int rowNum = 1; rowNum <= LastRowNum(); rowNum++) {
log.info("处理到{}条记录",rowNum);
//根据索引获取每⼀个⾏
Row row = Row(rowNum);
size = LastCellNum();
//Aep的设备实体类
AepDeviceDTO deviceDTO = new AepDeviceDTO();
AepDeviceOther other = new AepDeviceOther();
Set<String> imeiMap = new HashSet<>();
for (int cellNum = 0; cellNum < LastCellNum(); cellNum++) {
Cell cell = Cell(cellNum);
if (null == cell) {
break;
}
manufacturer = StringCellValue();
break;
case 1:
productType = StringCellValue();
break;
case 2:
imei = StringCellValue();
if (StringUtils.isBlank(imei)) {
importExcelResult.setCode(101);
megBuilder.append("⾏号=[").append (rowNum + 1).append("];IMEI=[").append(imei).append("]，设备名称=[").append(deviceName).append("]，失败原因:IMEI号不能为空");
continue;
}
if (im().length() != 15) {
importExcelResult.setCode(102);
megBuilder.append("⾏号=[").append (rowNum + 1).append("];IMEI=[").append(imei).append("]，设备名称=[").append(deviceName).append("]，失败原因:IMEI号必须是15位");
continue;
}
deviceDTO.im());
wlwDevice.im());
break;
case 3:
deviceName = StringCellValue();
if (StringUtils.isBlank(deviceName)) {
importExcelResult.setCode(104);
megBuilder.append("⾏号=[").append (rowNum + 1).append("];IMEI=[").append(imei).append("]，设备名称=[").append(deviceName).append("]，失败原因：设备名称不能为空");
continue;
}
deviceDTO.im());
wlwDevice.im());
break;
case 4:
autoObserver = StringCellValue();
if (StringUtils.isBlank(autoObserver)) {
importExcelResult.setCode(105);
megBuilder.append("⾏号=[").append (rowNum + 1).append("];IMEI=[").append(imei).append("]，设备名称=[").append(deviceName).append("]，失败原因：设备必须选择是否⾃动订阅");
continue;
}
//Aep的设备实体类的autoObserver    pass平台暂时不做⾃动订阅和PSK，若做在这⾥set
if (im().hashCode() == 26159) {
other.setAutoObserver(0);
} else if (im().hashCode() == 21542) {
other.setAutoObserver(1);
} else {
importExcelResult.setCode(106);
megBuilder.append("⾏号=[").append (rowNum + 1).append("];IMEI=[").append(imei).append("]，设备名称=[").append(deviceName).append("]，失败原因：设备必须选择是否⾃动订阅");
continue;
}
break;
case 5:
deviceName = StringCellValue();
break;
default:
break;
}
}
deviceDTO.setProductId(Long.valueOf(aepProductId));
deviceDTO.setOther(other);
deviceDTO.setOperator(String.CurrentUserId()));
aepDeviceDTOList.add(deviceDTO);
//2.遍历list校验是否有重复的imei号
boolean add = imeiMap.add(imei);
if (!add) {
importExcelResult.setCode(108);
megBuilder.append("⾏号=[").append (rowNum + 1).append("];IMEI=[").append(imei).append("]，设备名称=[")
.append(deviceName).append("]，失败原因：设备IMEI存在重复数据，请检查模板");
continue;
}
//导⼊aep系统的设备
AepCreateDeviceResponse aepDevice = ateAepDevice(deviceDTO);
Thread.sleep(200);
System.out.println("调⽤aep接⼝：" + Result());
if (Code() != 0) {
importExcelResult.Code());
megBuilder.append("⾏号=[").append (rowNum + 1).append("];IMEI=[").append(imei).append("]，设备名称=[")
.append(deviceName).append("]，失败原因:").Msg());
continue;
} else {
importExcelResult.setCode(0);
importExcelResult.Msg());
//aep返回的结果参数
AepDevice result = Result();
wlwDevice.DeviceId());
wlwDevice.setProductId(Long.valueOf(productId));
wlwDevice.setProjectId(Integer.valueOf(projectId));
wlwDevice.setAepProductId(Long.ProductId()));
wlwDevice.DeviceSn());
wlwDevice.TenantId());
wlwDevice.UpdateTime() == null ? null : UpdateTime()));
wlwDevice.NetStatus());
wlwDevice.Operator());
int insert = wlwDeviceRepository.insert(wlwDevice);
if (insert == 0) {
importExcelResult.setCode(109);
megBuilder.append("⾏号=[").append (rowNum + 1).append("];IMEI=[").append(imei).append("]，设备名称=[").append(deviceName).append("]，失败原因:PasS系统导⼊错误").Result().getDeviceName());                            continue;
}
}
}
}
importExcelResult.String());
is.close();
} catch (Exception e) {
<("插⼊设备失败", e);
importExcelResult.setCode(-1);
importExcelResult.setMessage("插⼊设备失败");
}
return importExcelResult;
}
}  AepDeviceDTO实体类：
public class AepDeviceDTO {
/
**
* 终端名称
*/
private String deviceName;
/**
/**
* IMEI号，全局唯⼀，根据产品的Endpoint必填，创建时可相同，则删除原产品新建产品    */
private String imei;
/**
* 操作者
*/
private String operator;
/**
* LWM2M协议必填参数，其他协议不填
*  {
*    autoObserver:0.⾃动订阅 1.取消⾃动订阅，必填;
error parse new
*    imsi：总长度不超过15位，使⽤0~9的数字，String类型，选填;
*    pskValue：由⼤⼩写字母加0-9数字组成的16位字符串，选填
*  }
*/
private AepDeviceOther other;
/**
* 产品id
*/
private Long productId;
public String getDeviceName() {
return deviceName;
}
public void setDeviceName(String deviceName) {
this.deviceName = deviceName;
}
public String getDeviceSn() {
return deviceSn;
}
public void setDeviceSn(String deviceSn) {
this.deviceSn = deviceSn;
}
public String getImei() {
return imei;
}
public void setImei(String imei) {
this.imei = imei;
}
public String getOperator() {
return operator;
}
public void setOperator(String operator) {
this.operator = operator;
}
public AepDeviceOther getOther() {
return other;
}
public void setOther(AepDeviceOther other) {
}
public Long getProductId() {
return productId;
}
public void setProductId(Long productId) {
this.productId = productId;
}
@Override
public String toString() {
return "AepDeviceDTO{" +
"deviceName='" + deviceName + '\'' +
", deviceSn='" + deviceSn + '\'' +
", imei='" + imei + '\'' +
", operator='" + operator + '\'' +
", other=" + other +
", productId=" + productId +
'}';
}
} 
AepDeviceOther实体类：
public class AepDeviceOther {
/**
* 0.⾃动订阅 1.取消⾃动订阅，必填;
*/
private Integer autoObserver;
/**
* 总长度不超过15位，使⽤0~9的数字，String类型，选填;
*/
private String imsi;
/**
* 由⼤⼩写字母加0-9数字组成的16位字符串，选填
*/
private String pskValue;
public Integer getAutoObserver() {
return autoObserver;
}
public void setAutoObserver(Integer autoObserver) {
this.autoObserver = autoObserver;
}
public String getImsi() {
return imsi;
}
public void setImsi(String imsi) {
this.imsi = imsi;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。