SpringCloud之实现上传Excel文件，初始化导入数据至数据库
黄宏大
1、页面代码如下，弹出窗是用的layer：
<input type="button" class="btn_default" style="width: 100px;" onclick="investigation();" value="导入心理咨询"/>function investigation() {
down("请按照模板内容导入心理咨询文档");
}function down(message) {
var index = layer.confirm(message, {
btn: ['确认'] //按钮
}, function(){
$.ajax({
url: path+"/web/api/index/getTblTeacherClassVoListData",
type: "post",
data: {
accessToken:'${accessToken}'
},
success: function(msg){
if(msg.type=="success"){
$("#classId").html("");
var html = "<option value=''>--选择班级--</option>";
for(var i=0;i<msg.data.length;i++){
html+="<option value="+msg.data[i].classId+">"+msg.data[i].className+"</option>"
}
$("#classId").append(html);
}else{
layer.msg(msg.message);
}
}
});
layer.close(index);
$("#colorbox").show();
var excel = layer.open({
type: 1,
title:message,
fixed:true,
area: ['330px','290px'],
content: $('#colorbox'),
btn: ['确认', '关闭'],
yes: function(){
var formData = new FormData($( "#excelForm" )[0]);
$.ajax({
url: path+"/web/api/excel/open/uploadCounselingData",
type: 'post',
data: formData,
dataType:'json',
async: false,
cache: false,
contentType: false,
processData: false,
success: function(msg){
if(msg.type=="success"){
layer.close(excel);
}
layer.msg(msg.message);
}
})
}
})
});
}2、主要是后台代码，如下所示：
controller层：
/**
* 上传文件
* @param request
* @param file
* @return
*/
@ResponseBody
@RequestMapping(value = "/open/uploadCounselingData", method = RequestMethod.POST)
public Result uploadData(HttpServletRequest request, @RequestParam MultipartFile file,
@RequestParam(required = false) String classId,
@RequestParam(required = false) String isTrack ) {
//手工导入
Result result = new Result();
try {
if (file != null && file.getSize() > 0) {
if(CheckUtil.checkNull(classId)){
result.setType(TypeEnum.FAIL.getCode());
result.setMessage("请选择班级");
return result;
}
if(CheckUtil.checkNull(isTrack)){
result.setType(TypeEnum.FAIL.getCode());
result.setMessage("请选择是否跟踪");
return result;
}
String fileName = file.getOriginalFilename();
result = excelService.batchImport(fileName,file,classId,isTrack);
} else {
result.setType(TypeEnum.FAIL.getCode());
result.setMessage("请选择上传文件");
}
} catch (Exception e) {
logger.error("上传文件出错");
result.setType(TypeEnum.FAIL.getCode());
result.setMessage("发生异常");
}
return result;
}service层：
public interface ExcelService {
Result batchImport(String fileName, MultipartFile excelFile, String classId, String isTrack) throws Exception;
}@Service
public class ExcelServiceImpl implements ExcelService {
@Autowired
private TblStudentInfomationService tblStudentInfomationService;
@Autowired
private TblClassService tblClassService;
@Autowired
private TblCompositeScoreService tblCompositeScoreService;
@Transactional(readOnly = false,rollbackFor = Exception.class)
@Override
public Result batchImport(String fileName, MultipartFile file,String classId,String isTrack) throws Exception {
Result result = new Result();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
result.setType(TypeEnum.FAIL.getCode());
result.setMessage("文件格式不正确");
return result;
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
InputStream is = file.getInputStream();
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);
if(sheet!=null){
result.setType(TypeEnum.SUCCESS.getCode());
result.setMessage("文件上传成功");
}else{
result.setType(TypeEnum.FAIL.getCode());
result.setMessage("上传失败sheet为空");
return result;
}
String schoolCode = "";
int collegeId = 0;
int professionId = 0;
Result<SCPCVo> scpcVoResult = tblClassService.getSCPCVoByClassId(Integer.parseInt(classId));
if(CheckUtil.checkEqresultSuccessAndData(scpcVoResult)){
schoolCode = scpcVoResult.getData().getSchoolCode();
collegeId = scpcVoResult.getData().getCollegeId();
professionId = scpcVoResult.getData().getProfessionId();
}else{
result.setType(TypeEnum.FAIL.getCode());
result.setMessage("未知的班级id");
return result;
}
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
String sno = row.getCell(0).getStringCellValue();
String name = row.getCell(1).getStringCellValue();
String sex = row.getCell(2).getStringCellValue();
String score = row.getCell(3).getStringCellValue();
String enrollment = row.getCell(4).getStringCellValue();
TblStudentInfomation tblStudentInfomation = new TblStudentInfomation();
tblStudentInfomation.setSno(sno);
tblStudentInfomation.setName(name);
int sexInt = 1;
if(!CheckUtil.checkNull(sex)){
if(sex.equals("女")||sex=="女") sexInt = 0;
}
tblStudentInfomation.setSex(sexInt);
tblStudentInfomation.setClassId(Integer.parseInt(classId));
tblStudentInfomation.setIsTrack(Integer.parseInt(isTrack));
tblStudentInfomation.setProfessionId(professionId);
tblStudentInfomation.setCollegeId(collegeId);
tblStudentInfomation.setSchoolCode(schoolCode);
tblStudentInfomation.setEnrollment(enrollment);
tblStudentInfomationService.addStudentInfomation(tblStudentInfomation);
TblCompositeScore tblCompositeScore = new TblCompositeScore();
tblCompositeScore.setScore(score==null?0:Double.parseDouble(score));
tblCompositeScore.setSno(sno);
tblCompositeScore.setVestingDate(new Date());
tblCompositeScoreService.addCompositeScore(tblCompositeScore);
}
return result;
}
}pom文件：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
代码那么多，能全部看完才怪，而且复制可能也不能直接可以用，但是核心代码就是如下两部分，判断是否有数据，和一行一行、一列一列的读取数据
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
result.setType(TypeEnum.FAIL.getCode());
result.setMessage("文件格式不正确");
return result;
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
InputStream is = file.getInputStream();
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
String sno = row.getCell(0).getStringCellValue();
String name = row.getCell(1).getStringCellValue();
String sex = row.getCell(2).getStringCellValue();
String score = row.getCell(3).getStringCellValue();
String enrollment = row.getCell(4).getStringCellValue();
TblStudentInfomation tblStudentInfomation = new TblStudentInfomation();
tblStudentInfomation.setSno(sno);
tblStudentInfomation.setName(name);
int sexInt = 1;
if(!CheckUtil.checkNull(sex)){
if(sex.equals("女")||sex=="女") sexInt = 0;
}
tblStudentInfomation.setSex(sexInt);
tblStudentInfomation.setClassId(Integer.parseInt(classId));
tblStudentInfomation.setIsTrack(Integer.parseInt(isTrack));
tblStudentInfomation.setProfessionId(professionId);
tblStudentInfomation.setCollegeId(collegeId);
tblStudentInfomation.setSchoolCode(schoolCode);
tblStudentInfomation.setEnrollment(enrollment);
tblStudentInfomationService.addStudentInfomation(tblStudentInfomation);
TblCompositeScore tblCompositeScore = new TblCompositeScore();
tblCompositeScore.setScore(score==null?0:Double.parseDouble(score));
tblCompositeScore.setSno(sno);
tblCompositeScore.setVestingDate(new Date());
tblCompositeScoreService.addCompositeScore(tblCompositeScore);
}
类似资料：