myexcel导入导出数据
谢泽语
myexcel导出实体类对象。
@ExcelTable(sheetName = "Sheet1",rowAccessWindowSize = 100,useFieldNameAsTitle = true)
public class UserExaminationExportExcelItem {
@ExcelColumn(order = 1,index = 0,title = "考试名称")
private String examName;
@ExcelColumn(order = 2,index = 1,title = "学生姓名")
private String trueName;
@ExcelColumn(order = 3,index = 2,title = "手机号码")
private String mobile;
@ExcelColumn(order = 4,index = 3,title = "学校名称")
private String schoolName;
@ExcelColumn(order = 5,index = 4,title = "班级代号")
private String classCode;
@ExcelColumn(order = 6,index = 5,title = "考试科目")
private String examKemu;
@ExcelColumn(order = 7,index = 6,title = "考试成绩")
private Integer examUserChengji;
@ExcelColumn(order = 8,index = 7,title = "班级排名")
private Integer banji;
@ExcelColumn(order = 9,index = 8,title = "全排名")
private Integer hontu;
public String getExamName() {
return examName;
}
public void setExamName(String examName) {
this.examName = examName;
}
public String getTrueName() {
return trueName;
}
public void setTrueName(String trueName) {
this.trueName = trueName;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getSchoolName() {
return schoolName;
}
public void setSchoolName(String schoolName) {
this.schoolName = schoolName;
}
public String getClassCode() {
return classCode;
}
public void setClassCode(String classCode) {
this.classCode = classCode;
}
public String getExamKemu() {
return examKemu;
}
public void setExamKemu(String examKemu) {
this.examKemu = examKemu;
}
public Integer getExamUserChengji() {
return examUserChengji;
}
public void setExamUserChengji(Integer examUserChengji) {
this.examUserChengji = examUserChengji;
}
public Integer getBanji() {
return banji;
}
public void setBanji(Integer banji) {
this.banji = banji;
}
public Integer getHontu() {
return hontu;
}
public void setHontu(Integer hontu) {
this.hontu = hontu;
}
}
具体导出业务代码。
@RequestMapping(value = "/exportListUserExamination", method = RequestMethod.GET)
@ApiOperation(httpMethod = "GET", value = "导出学生考试信息", notes = "导出学生考试信息")
@SessionCheck
public Map<String, Object> exportListUserExamination(
HttpServletRequest request, @ApiParam(name = "examId", value = "考试id") @RequestParam(value = "examId", required = false) Integer examId,
@ApiParam(name = "classId", value = "班级id") @RequestParam(value = "classId", required = false) Integer classId) {
Map<String, Object> result = new HashMap<>();
AuthAdminDTO authAdminDTO = (AuthAdminDTO) request.getAttribute("adminDTO");
List<UserExaminationResult> userExaminationList = userExaminationFacade.listUserExamination(null, examId, classId, null, null, null);
List<UserExaminationExportExcelItem> userExaminationExportExcelItemList = new ArrayList<>();
for (UserExaminationResult userExaminationResult : userExaminationList) {
UserExaminationExportExcelItem userExaminationExportExcelItem = new UserExaminationExportExcelItem();
BeanUtils.copyProperties(userExaminationResult, userExaminationExportExcelItem, FieldUtils.getNullPropertyNames(userExaminationResult));
userExaminationExportExcelItemList.add(userExaminationExportExcelItem);
}
if (CollectionUtils.isNotEmpty(userExaminationExportExcelItemList)) {
Workbook workbook = DefaultExcelBuilder.of(UserExaminationExportExcelItem.class)
.build(userExaminationExportExcelItemList);
String key = UUID.randomUUID().toString();
ossClient.putObject(AliyunBucketEnum.BUCKET_ADMIN_RESOURCE.getName(), "ListUserExamination/" + key + ".xlsx", FileExportUtil.getInputStream(workbook));
result.put("url", AliyunBucketEnum.BUCKET_ADMIN_RESOURCE.getUrl() + "ListUserExamination/" + key + ".xlsx");
}
return result;
}
myexcel导入实体类对象。
@ExcelTable(sheetName = "Sheet1",rowAccessWindowSize = 100,useFieldNameAsTitle = true)
public class UserExaminationImportExcelltem {
@ExcelColumn(order = 1,index = 0,title = "考试名称")
private String examName;
@ExcelColumn(order = 2,index = 1,title = "考试科目")
private String examKemu;
@ExcelColumn(order = 3,index = 2,title = "学生名称")
private String trueName;
@ExcelColumn(order = 4,index = 3,title = "手机号码")
private String mobile;
@ExcelColumn(order = 5,index = 4,title = "班级代号")
private String classCode;
@ExcelColumn(order = 6,index = 5,title = "考试成绩")
private String examUserChengji;
@ExcelColumn(order = 7,index = 6,title = "错误原因")
private String error;
public String getExamKemu() {
return examKemu;
}
public void setExamKemu(String examKemu) {
this.examKemu = examKemu;
}
public String getTrueName() {
return trueName;
}
public void setTrueName(String trueName) {
this.trueName = trueName;
}
public String getExamName() {
return examName;
}
public void setExamName(String examName) {
this.examName = examName;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getClassCode() {
return classCode;
}
public void setClassCode(String classCode) {
this.classCode = classCode;
}
public String getExamUserChengji() {
return examUserChengji;
}
public void setExamUserChengji(String examUserChengji) {
this.examUserChengji = examUserChengji;
}
public String getError() {
return error;
}
public void setError(String error) {
this.error = error;
}
}
具体导入业务逻辑。
@RequestMapping(value = "/importUserExamination", method = RequestMethod.POST)
@NoAuthCheck
public Map<String, Object> importUserExamination(@RequestParam("file") MultipartFile file) throws Exception {
Map<String, Object> result = new HashMap<>();
List<UserExaminationImportExcelltem> userExaminationImportExcelltemList = DefaultExcelReader.of(UserExaminationImportExcelltem.class).sheet(0).rowFilter(row -> row.getRowNum() > 0).read(file.getInputStream());
List<UserExaminationImportExcelltem> UserExaminationImportExcelltem = new ArrayList<>();
for (UserExaminationImportExcelltem classImportExcelItem : userExaminationImportExcelltemList) {
UserExaminationImportExcelltem classImportExcelItem1 = new UserExaminationImportExcelltem();
BeanUtils.copyProperties(classImportExcelItem, classImportExcelItem1);
if (StringUtils.isBlank(classImportExcelItem.getExamName())) {
classImportExcelItem1.setError("考试名称未填写");
}
if (StringUtils.isBlank(classImportExcelItem1.getError()) && StringUtils.isBlank(classImportExcelItem.getExamKemu())) {
classImportExcelItem1.setError("考试科目未填写");
}
if (StringUtils.isBlank(classImportExcelItem1.getError()) && StringUtils.isBlank(classImportExcelItem.getTrueName())) {
classImportExcelItem1.setError("学生姓名未填写");
}
if (StringUtils.isBlank(classImportExcelItem1.getError()) && StringUtils.isBlank(classImportExcelItem.getMobile())) {
classImportExcelItem1.setError("手机号码未填写");
}
if (StringUtils.isBlank(classImportExcelItem1.getError()) && classImportExcelItem.getExamUserChengji()+"" == null) {
classImportExcelItem1.setError("考试成绩未填写");
}
ExaminationDTO byExamName = examinationFacade.getByExamName(classImportExcelItem.getExamName(), classImportExcelItem.getExamKemu());
if (StringUtils.isBlank(classImportExcelItem1.getError()) && byExamName == null) {
classImportExcelItem1.setError("考试名称或科目错误");
}
UserDTO userByMobile = userFacade.getUserByMobile(classImportExcelItem.getMobile());
if (StringUtils.isBlank(classImportExcelItem1.getError()) && userByMobile == null) {
classImportExcelItem1.setError("手机号码错误");
} else if (StringUtils.isBlank(classImportExcelItem1.getError()) && null != userByMobile) {
if(userByMobile.getTruename()!=null){
if (!userByMobile.getTruename().trim().equals(classImportExcelItem.getTrueName())) {
classImportExcelItem1.setError("学生姓名错误");
}
}
}
// Integer bySchoolName = schoolInfoFacade.getBySchoolName(userByMobile.getSchool());
// if (StringUtils.isBlank(classImportExcelItem1.getError()) && classDTO == null) {
// classImportExcelItem1.setError("班级代号错误");
// }
if (StringUtils.isBlank(classImportExcelItem1.getError())) {
if (Double.parseDouble(classImportExcelItem.getExamUserChengji() + "") > Double.parseDouble(byExamName.getExamManfen() + "")) {
classImportExcelItem1.setError("考试成绩不能大于当前科目满分");
}
}
if (StringUtils.isNotBlank(classImportExcelItem1.getError())) {
UserExaminationImportExcelltem.add(classImportExcelItem1);
} else {
UserExaminationDTO userExaminationDTO = new UserExaminationDTO();
userExaminationDTO.setExamId(byExamName.getId());
userExaminationDTO.setUserId(userByMobile.getUserId());
// userExaminationDTO.setSchoolId(bySchoolName);
if (classImportExcelItem.getClassCode() != null) {
ClassDTO classDTO = classFacade.getClassByClassCode(classImportExcelItem.getClassCode());
if (classDTO != null) {
userExaminationDTO.setClassId(classDTO.getClassId());
}
}
userExaminationDTO.setExamUserChengji(Double.parseDouble(classImportExcelItem.getExamUserChengji()));
try {
List<UserExaminationResult> userExaminationResults = userExaminationFacade.listUserExamination(null, byExamName.getId(), null, classImportExcelItem.getMobile(), null, null);
//如果重复导入则以最新的为准
if (0 != userExaminationResults.size()) {
userExaminationDTO.setId(userExaminationResults.get(0).getId());
userExaminationFacade.updateUserExamination(userExaminationDTO,0);
} else {
userExaminationFacade.insertUserExamination(userExaminationDTO);
}
} catch (Exception e) {
e.printStackTrace();
classImportExcelItem1.setError("未知错误");
UserExaminationImportExcelltem.add(classImportExcelItem1);
}
}
}
// if (CollectionUtils.isEmpty(UserExaminationImportExcelltem)) {
ExaminationDTO byExamName = examinationFacade.getByExamName(userExaminationImportExcelltemList.get(0).getExamName(), userExaminationImportExcelltemList.get(0).getExamKemu());
userExaminationFacade.updateRanking(byExamName.getId());
// List<UserExaminationResult> userExaminationResults = userExaminationFacade.listUserExamination(null, byExamName.getId(), null, null, null, null);
// for (UserExaminationResult userExaminationResult : userExaminationResults) {
// //学生的宏图排名
// Integer byPaiMing = userExaminationFacade.getByAchievementRanking(byExamName.getId(), null, userExaminationResult.getUserId());
// //学生的班级排名
// Integer byPaiMing1 = 0;
// if (userExaminationResult.getClassId() != null) {
// byPaiMing1 = userExaminationFacade.getByAchievementRanking(byExamName.getId(), userExaminationResult.getClassId(), userExaminationResult.getUserId());
// }
// userExaminationFacade.updateAchievementRanking(byPaiMing1, byPaiMing, userExaminationResult.getUserId(), byExamName.getId());
// }
// }
if (CollectionUtils.isNotEmpty(UserExaminationImportExcelltem)) {
result.put("errorCount", UserExaminationImportExcelltem.size());
Workbook workbook = DefaultExcelBuilder.of(UserExaminationImportExcelltem.class)
.build(UserExaminationImportExcelltem);
String key = UUID.randomUUID().toString();
ossClient.putObject(AliyunBucketEnum.BUCKET_ADMIN_RESOURCE.getName(), "importUserExamination/" + key + ".xlsx", FileExportUtil.getInputStream(workbook));
result.put("url", AliyunBucketEnum.BUCKET_ADMIN_RESOURCE.getUrl() + "importUserExamination/" + key + ".xlsx");
}
result.put("totalCount", userExaminationImportExcelltemList.size());
return result;
}
类似资料：