模板下载与数据导入
1 模板下载
1.1 前端页面
/** 下载模板*/
function downloadExcel(group_id) {
location.href = '${webRoot }/evaluate/homework/downloadExcel?group_id=' + group_id;
}
1.2 Controller
/**
* 下载成绩导入模板
* @param response
* @throws Exception
*/
@RequestMapping("downloadExcel")
public void downloadExcel(HttpServletResponse response) throws Exception {
PageData pd = this.getPageData();
List<PageData> list = service.getListData(pd);
Map<String,Object> data = new HashMap<String,Object>();
//处理导入模板数据
data = this.formatData(list);
List<PageData> column = (List<PageData>) data.get("column");
List<PageData> result = (List<PageData>) data.get("data");
InputStream ins = FileDownloadUtils.getTemplateStream("exceltemple/evaluate/achievement/test.xls");
HSSFWorkbook work = new HSSFWorkbook(ins);
//设置单元格内容居中
HSSFCellStyle style = work.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); //左右居中
// 得到excel的第0张表
HSSFSheet sheet = work.getSheetAt(0);
//将错误信息输入excel
HSSFRow row0 = sheet.createRow(0);//创建excel行
HSSFRow row1 = sheet.createRow(1);//创建excel行
HSSFRow row2 = sheet.createRow(2);//创建excel行
HSSFRow row3 = sheet.createRow(3);//创建excel行
//设置行高为0,达到隐藏行的目的
row3.setZeroHeight(true);
HSSFCell cell00 = row0.createCell(1);
cell00.setCellValue("姓名");
HSSFCell cell01 = row0.createCell(0);
cell01.setCellValue("学号");
//第一列为学号，并且合并第1-3行，第二列为学生姓名，并且合并1-3行
CellRangeAddress a1 = new CellRangeAddress(0,2,0,0);
CellRangeAddress a2 = new CellRangeAddress(0,2,1,1);
sheet.addMergedRegion(a1);
sheet.addMergedRegion(a2);
//设置作业组名
int index0 = 2;
HSSFCell cell0Index = row0.createCell(index0);//创建excel行
//从第一行第三列设置作业组名
cell0Index.setCellValue(column.get(0).getString("group_name"));
cell0Index.setCellStyle(style);
HSSFCell cell30 = row3.createCell(0);
//设置隐藏行的第一列数据
cell30.setCellValue(column.get(0).getString("class_id"));
//循环每一个课程目标
for(int i = 0,index=2; i<column.size() ;i++){
PageData obj = column.get(i);
List<PageData> hws = (List<PageData>) obj.get("list_hw");
//添加作业信息
for(int j = 0;j<hws.size();j++){
//课程目标
HSSFCell cell1Index = row1.createCell(index+j);
cell1Index.setCellValue(obj.getString("title"));
cell1Index.setCellStyle(style);
PageData hw = hws.get(j);
HSSFCell cell2Index = row2.createCell(index+j);
cell2Index.setCellValue(hw.getString("title"));
HSSFCell cell3Index = row3.createCell(index+j);
//设置隐藏行值 homework_id
cell3Index.setCellValue(hw.getString("field"));
//宽度自适应
sheet.autoSizeColumn(index+j, true);
}
index = index +hws.size();
//得到最后的作业总数
index0 = index;
}
// 合并作业组名
// 作业数大于1就合并
if(index0 > 3){
CellRangeAddress cra0 = new CellRangeAddress(0,0,2,index0-1);
sheet.addMergedRegion(cra0);
}
//添加学生信息
for(int i=0;i<result.size();i++){
PageData param = result.get(i);
HSSFRow dataRow = sheet.createRow(4+i);//创建excel行
HSSFCell cellData0 = dataRow.createCell(0);
cellData0.setCellValue(param.getString("number_"));
HSSFCell cellData1 = dataRow.createCell(1);
cellData1.setCellValue(param.getString("name_"));
short a = row3.getLastCellNum();
for(int j = 2;j<a;j++){
HSSFCell cellDataIndex = dataRow.createCell(j);
//作业得分
cellDataIndex.setCellValue(param.getString(row3.getCell(j).getStringCellValue().toLowerCase()));
}
}
//宽度自适应
sheet.autoSizeColumn(0, true);
sheet.autoSizeColumn(1, true);
String filename = "成绩上传模板.xls";
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;fileName="+new String(filename.getBytes("UTF-8"),"ISO8859-1"));
response.setHeader("Connection", "close");
response.flushBuffer();
OutputStream out = response.getOutputStream();
try {
work.write(out);// 将数据写出去
} catch (Exception e) {
e.printStackTrace();
} finally {
out.close();
}
}
1.3 导入模板中的数据处理
/**
* 对导入模板的查询数据进行数据处理
* @param list
* @return
*/
private Map<String, Object> formatData(List<PageData> list) {
Map<String,Object> data = new HashMap<String,Object>();
List<PageData> result = new ArrayList<PageData>();
List<PageData> column = new ArrayList<PageData>();
for(PageData score:list){
boolean newStu = true;
boolean newObj = true;
boolean newHw = true;
PageData stu = new PageData();
PageData obj = new PageData();
PageData hw = new PageData();
for(PageData resultSub : result){
//判断是否是新学生
if(resultSub.getString("student_id").equals(score.getString("student_id"))){
newStu = false;
stu = resultSub;
break;
}
}
//添加学生作业得分 k: homework_id
stu.put(score.getString("homework_id"),score.get("score"));
if(newStu){
stu.put("student_id",score.get("student_id"));
stu.put("name_",score.get("sname_"));
stu.put("number_",score.get("snumber_"));
result.add(stu);
}
//判断是否新课程目标
for(PageData columnSub : column){
if(Objects.equals(columnSub.getString("field"),score.getString("objective_id"))){
newObj = false;
obj = columnSub;
break;
}
}
if(newObj){
//添加新的课程目标
obj.put("group_name",score.getString("gname_"));
obj.put("class_id",score.getString("class_id"));
obj.put("field",score.getString("objective_id"));
obj.put("title",score.getString("onumber_"));
obj.put("hw_count",1);
List<PageData> homeworks = new ArrayList<PageData>();
//添加课程目标中的作业
hw.put("field",score.getString("homework_id"));
hw.put("title",score.getString("name_")+"("+score.getString("hscore")+")");
homeworks.add(hw);
obj.put("list_hw",homeworks);
column.add(obj);
}else{
List<PageData> homeworks = (List<PageData>) obj.get("list_hw");
for(PageData homework : homeworks){
if(Objects.equals(score.getString("homework_id"),homework.getString("field"))){
newHw = false;
break;
}
}
//添加新的作业
if(newHw){
hw.put("field",score.getString("homework_id"));
hw.put("title",score.getString("name_")+"("+score.getString("hscore")+")");
homeworks.add(hw);
obj.put("hw_count",homeworks.size());
}
}
}
data.put("data",result);
data.put("column",column);
return data;
}
1.4 运行效果
2 导入数据与验证
2.1 import.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<title>课程资源管理文件上传</title>
<%@include file="/WEB-INF/view/admin/include.inc.jsp"%>
<%@include file="/WEB-INF/view/admin/easyuiheader.jsp"%>
<script type="text/javascript">
var failData;
$(function () {
//文件上传
$("#upload").click(function () {
$('#validateFlag').val("false");
$('#message').empty();
var formData = new FormData($('#myForm')[0]);
if (formData.get("file").name==="") {
$.messager.alert('警告','请选择文件!');
return;
}
$.ajax({
type: 'post',
url: "${webRoot }/evaluate/homework/validateData", //上传文件的请求路径必须是绝对路径
data: formData,
cache: false,
processData: false,
contentType: false,
}).success(function (data) {
var displayInfo = "";
if(data.faildata){
$('#validateFlag').val("false");
displayInfo ="<p>验证成功"+data.successdata.length+"条</p>"
+"<p>验证失败"+data.faildata.length+"条<a href='#' style='color:red' onclick='javascript:outFail();'>查看失败结果</a></p>";
failData = data;
}else{
displayInfo = "<p>验证通过！可以导入该文件数据！</p><p>验证成功"+data.successdata.length+"条</p>";
$("#validateFlag").val("true");
failData = data;
}
$('#message').append(displayInfo);
})
});
});
function changeFile(){
$('#validateFlag').val("false");
$('#message').empty();
}
//iframe方式加载执行保存动作
function dosave() {
if($("#validateFlag").val()=="true"){
var myform = $('#myForm');
var validate = myform.form('validate');
var actionUrl = myform.attr('action');
if (!validate) {
return false;
}
var resultFlag = false;
var options = {
url : actionUrl,
method:"post",
data : {"data":JSON.stringify(failData)},
async : false,
callback : function(data) {
if (data.msg == 'success') {
parent.$.messager.show({
title : '我的消息',
msg : '导入成功！'
});
resultFlag = true;
} else {
parent.$.messager.alert('我的消息', data.msg, 'error');
resultFlag = false;
}
}
};
$.doAjax(options);
return resultFlag;
}else{
parent.$.messager.alert('我的消息', "请先验证数据！必须所有数据通过验证才能导入！", 'error');
return false;
}
}
function outFail(){
var $form1=$("<form action='${webRoot }/evaluate/homework/outFail' id='f' name='f' target='newWindow1' method='post'></form>");
$form1.append($("<input type='hidden' name='faildata' id = 'faildata' value='"+JSON.stringify(failData)+"'/>"));
$("body").append($form1);
$form1.submit();
}
</script>
</head>
<body class="aui">
<form id='myForm' action="${webRoot }/evaluate/homework/saveData" class="easyui-form" enctype="multipart/form-data"
onload="">
<war:ReSubmit />
<div class="form-column1">
<div class="form-column-left">
<input type="hidden" id ="validateFlag" name="validateFlag"/>
<input type="text" name="file" id = "file" class="easyui-filebox" data-options="
label:'附件',
buttonText: '选择文件',
width:200,
accept: 'application/msword,application/vnd.openxmlformats-officedocument.wordprocessingml.document, application/pdf,application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.openxmlformats-officedocument.presentationml.presentation,application/vnd.ms-powerpoint',
prompt:'选择已有类型',
onChange:changeFile
"/>
<span ><a href='#' id = "upload" class='ace_button' style='background-color:#008000;' ><i class='glyphicon glyphicon-open'></i>验证数据</a></span>
<span id = "message" style="width: 20px;height: 20px"></span>
</div>
</div>
</form>
</body>
</html>
2.2 后端代码
/**
* 验证数据
* @param
* @param
* @throws Exception
*/
@RequestMapping(value = "validateData")
public PageData validateData(@RequestParam(required = false) MultipartFile file) throws Exception {
//上传附件
//从excel获取导入数据
PageData temp = this.getImpData(file);
//验证excel导入数据
PageData resultPd = this.validateImpData(temp);
return resultPd;
}
获取表格中的数据
/**
* 获取导入数据
* @param file
* @return
* @throws IOException
*/
private PageData getImpData(MultipartFile file) throws IOException {
PageData result = new PageData();
List<PageData> temp = new ArrayList<PageData>();
InputStream inputStream = file.getInputStream();
String sourceFileName = file.getOriginalFilename();
Workbook wk = null;
if(sourceFileName.endsWith(".xls")){
wk= new HSSFWorkbook(inputStream);
}else if (sourceFileName.endsWith(".xlsx")){
// wk= new XSSFWorkbook(inputStream);
}else{
}
if(wk!=null){
//获取第一张Sheet表
Sheet sheet=wk.getSheetAt(0);
//获取总行数
//从数据行开始迭代每一行
Row row3 = sheet.getRow(3);
String[][] strColumn =new String[4][row3.getLastCellNum()];
//得到班级id
strColumn[1][0] = row3.getCell(0).getStringCellValue();
strColumn[3][0] = "number_";
strColumn[3][1] = "name_";
for(Row r : sheet){
//前4行是表头，第四行是隐藏行
if(r.getRowNum()<4){
for(Cell c:r){
if(StringUtils.isEmpty(strColumn[c.getRowIndex()][c.getColumnIndex()]) ){
strColumn[c.getRowIndex()][c.getColumnIndex()] = c.getStringCellValue();
}
}
continue;
}
//创建实体类
PageData info=new PageData();
info.put("row_num", r.getRowNum());
for(int i=0;i<r.getLastCellNum() ;i++){
Cell cell = r.getCell(i);
if(cell!=null){
cell.setCellType(CellType.STRING);
info.put(strColumn[3][i], cell.getStringCellValue());
}
}
temp.add(info);
}
result.put("strColumn", strColumn);
result.put("textData", temp);
}
return result;
}
验证表格中的数据
/**
* 验证导入数据
* @param temp
* @return
* @throws Exception
*/
private PageData validateImpData(PageData temp) throws Exception {
List<PageData> textData = (List<PageData>) temp.get("textData");
String[][] strColumn = (String[][]) temp.get("strColumn");
PageData resultPd =new PageData();
List<PageData> successData = new ArrayList<PageData>();
List<PageData> failData = new ArrayList<PageData>();
// PageData failCol =new PageData();
//得到班级id
String class_id = strColumn[1][0];
//根据班级查出所有学生信息
List<PageData> stuList = service.listStudentByClass(class_id);
//循环所有学生数据
for(PageData var : textData){
boolean pass = true;
//判断学生是否存在，且是该班级学生
String stuIdCard = var.getString("number_");
String stuName = var.getString("name_");
boolean stuExist = false;
for (PageData item : stuList) {
boolean isNotEmpty = StringUtils.isNotBlank(stuIdCard) && StringUtils.isNotBlank(stuName) && StringUtils.isNotBlank(item.getString("number_")) && StringUtils.isNotBlank(item.getString("name_"));
if(isNotEmpty && item.getString("number_").equals(stuIdCard) && item.getString("name_").equals(stuName)){
stuExist = true;
var.put("student_id",item.getString("student_id"));
}
}
if(stuExist){
//依次判断每个作业分数
for(int i = 2 ; i<strColumn[3].length;i++){
//作业总分
String hname_ = strColumn[2][i];
String hscore = hname_.substring(hname_.lastIndexOf("(")+1,hname_.lastIndexOf(")"));
String score = var.getString(strColumn[3][i]);
if((!service.isNumeric(score)) || Double.parseDouble(score) < 0 || Double.parseDouble(score) > Double.parseDouble(hscore)){
var.put(strColumn[3][i]+"__error","成绩填写有误！成绩必须为数字并且范围是0到总分之间！");
pass = pass && false;
}
}
}else {
var.put(strColumn[3][0]+"__error","该班级未找到学生关联数据！请检查！");
pass = pass && false;
}
if(pass){
successData.add(var);
}else{
failData.add(var);
}
}
resultPd.put("successdata", successData);
resultPd.put("strcolumn", strColumn);
if(failData.size()>0){
resultPd.put("faildata", failData);
}
return resultPd;
}
导出错误的数据
/**
* 导出错误数据
* @param response
* @throws Exception
*/
@RequestMapping("outFail")
public void outFail(HttpServletResponse response) throws Exception {
PageData pd = this.getPageData();
//此处的faildata包含了所有数据
Object jsonObj = JSONUtils.parse(pd.getString("faildata"));
List<Map<Object,Object>> failData = null;
// Map<Object,Object> failCol = null;
Map<Object,Object> map = (Map<Object, Object>) jsonObj;
List<List<String>> strColumn = (List<List<String>>) map.get("strcolumn");
List<Map<Object,Object>> successData = (List<Map<Object,Object>>) map.get("successdata");
if(map.get("faildata")!=null){
failData = (List<Map<Object,Object>>) map.get("faildata");
}
/* if(map.get("failcol")!=null){
failCol = (Map<Object,Object>) map.get("failcol");
}*/
InputStream ins = FileDownloadUtils.getTemplateStream("exceltemple/evaluate/achievement/test.xls");
HSSFWorkbook work = new HSSFWorkbook(ins);
//设置单元格内容居中
HSSFCellStyle style = work.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); //左右居中
// 得到excel的第0张表
HSSFSheet sheet = work.getSheetAt(0);
//将错误信息输入excel
HSSFRow row0 = sheet.createRow(0);//创建excel行
HSSFRow row1 = sheet.createRow(1);//创建excel行
HSSFRow row2 = sheet.createRow(2);//创建excel行
HSSFRow row3 = sheet.createRow(3);//创建excel行
row3.setZeroHeight(true);
HSSFCell cell00 = row0.createCell(0);
cell00.setCellValue("学号");
HSSFCell cell01 = row0.createCell(1);
cell01.setCellValue("姓名");
HSSFCell cell30 = row3.createCell(0);
//班级id
cell30.setCellValue(strColumn.get(1).get(0));
CellRangeAddress a1 = new CellRangeAddress(0,2,0,0);
CellRangeAddress a2 = new CellRangeAddress(0,2,1,1);
sheet.addMergedRegion(a1);
sheet.addMergedRegion(a2);
for(int i = 0; i<strColumn.size() ;i++){
HSSFRow rowi = sheet.getRow(i);
for(int j = 2;j<strColumn.get(i).size();j++){
HSSFCell cellij = rowi.createCell(j);
if(StringUtils.isEmpty(strColumn.get(i).get(j))){
continue;
}else{
//设置作业组名左右居中
if(i == 0 && j == 2){
cellij.setCellStyle(style);
}
cellij.setCellValue(strColumn.get(i).get(j));
}
}
}
// 合并作业组名
if(strColumn.get(3).size() > 3){ //作业数大于1
CellRangeAddress cra0 = new CellRangeAddress(0,0,2,strColumn.get(3).size()-1);
sheet.addMergedRegion(cra0);
}
if(failData!=null){
for(int i=0;i<failData.size();i++){
Map<Object,Object> var = failData.get(i);
HSSFRow rowi = sheet.createRow(i+4);
for(int j = 0 ;j<strColumn.get(3).size();j++){
HSSFCell cellij = rowi.createCell(j);
//设置宽度自适应
sheet.autoSizeColumn(j, true);
cellij.setCellValue((String) var.get(strColumn.get(3).get(j)));
if(var.get(strColumn.get(3).get(j)+"__error")!=null){
HSSFPatriarch p=sheet.createDrawingPatriarch();
HSSFComment comment=p.createComment(new HSSFClientAnchor(0,0,0,0,(short)3,3,(short)5,6));
//输入批注信息
comment.setString(new HSSFRichTextString((String) var.get(strColumn.get(3).get(j)+"__error")));
cellij.setCellComment(comment);
}
}
}
}
HSSFRow rowFailEnd = sheet.createRow(4+failData.size());
HSSFCell cellFailEnd = rowFailEnd.createCell(0);
cellFailEnd.setCellValue("------------------------错误数据分割行，上传前请删除----------------------");
CellRangeAddress a3 = new CellRangeAddress(4+failData.size(),4+failData.size(),0,2);
sheet.addMergedRegion(a3);
if(successData!=null){
for(int i=0;i<successData.size();i++){
Map<Object,Object> var = successData.get(i);
HSSFRow rowi = sheet.createRow(5+failData.size()+i);
for(int j = 0 ;j<strColumn.get(3).size();j++){
//设置宽度自适应
sheet.autoSizeColumn(j, true);
HSSFCell cellij = rowi.createCell(j);
cellij.setCellValue((String) var.get(strColumn.get(3).get(j)));
}
}
}
String filename = "错误信息.xls";
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;fileName="+new String(filename.getBytes("UTF-8"),"ISO8859-1"));
response.setHeader("Connection", "close");
response.flushBuffer();
OutputStream out = response.getOutputStream();
try {
work.write(out);// 将数据写出去
} catch (Exception e) {
e.printStackTrace();
} finally {
out.close();
}
}
3 导入数据
/**
* 导入验证通过的数据
* @return
* @throws Exception
*/
@RequestMapping("saveData")
public PageData saveData() throws Exception {
PageData pd = this.getPageData();
Object jsonObj = JSONUtils.parse(pd.getString("data"));
Map<Object,Object> map = (Map<Object, Object>) jsonObj;
List<List<String>> strColumn = (List<List<String>>) map.get("strcolumn");
List<Map<Object,Object>> successData = (List<Map<Object,Object>>) map.get("successdata");
List<String> columnArray = strColumn.get(3);
//得到作业组所有作业id
List<String> homework_ids = new ArrayList<>();
for (int i = 2; i < columnArray.size(); i++) {
homework_ids.add(columnArray.get(i));
}
//查询每个作业对应作业组信息
List<PageData> groupInfos = service.listGroupInfoByHomework(homework_ids);
//判断之前是否已经导入作业分数
List<PageData> homeworkAchievement_ids = service.listHomeworkAchievementId(groupInfos.get(0).getString("group_id"));
//判断之前是否已经导入平时作业考核方式总分
List<PageData> achievement_ids = service.listAchievementDetailId(groupInfos.get(0).getString("evaluate_id"));
List<PageData> insertList = new ArrayList<>();
//学生作业组总分
List<PageData> insertSumList = new ArrayList<>();
DecimalFormat df = new DecimalFormat("#.00");
for(Map<Object,Object> var : successData){
for(int i = 2;i<columnArray.size();i++){
PageData param = new PageData();
String homework_id = columnArray.get(i);
//添加作业对应目标
groupInfos.forEach(item -> {
if(item.getString("homework_id").equals(homework_id)){
param.put("objective_id",item.getString("objective_id"));
}
});
param.put("group_id", groupInfos.get(0).getString("group_id"));
param.put("homework_id",homework_id);
param.put("student_id",var.get("student_id"));
param.put("score",var.get(homework_id));
//判断之前是否已经导入作业分数
homeworkAchievement_ids.forEach(item -> {
if(StringUtils.isNotBlank(item.getString("achievement_id"))
&& homework_id.equals(item.getString("homework_id"))
&& param.getString("student_id").equals(item.getString("student_id"))){
param.put("achievement_id", item.getString("achievement_id"));
}
});
if(StringUtils.isBlank(param.getString("achievement_id"))){
param.put("achievement_id", UuidUtil.get32UUID());
}
insertList.add(param);
//计算学生作业组中同一目标总分
boolean stuObjNew = true;
for (PageData item : insertSumList) {
//学生已导入了作业组中同一目标的一个作业
if(item.getString("student_id").equals(var.get("student_id").toString())
&& item.getString("objective_id").equals(param.getString("objective_id"))){
//求和保留2位小数
String res = df.format(Double.parseDouble(item.getString("score")) + Double.parseDouble(param.getString("score")));
item.put("score",res);
stuObjNew = false;
break;
}
}
//学生未导入作业组中同一目标任一作业
if(stuObjNew){
PageData temp = new PageData();
temp.put("student_id",var.get("student_id").toString());
temp.put("score",param.getString("score"));
temp.put("evaluate_id",groupInfos.get(0).getString("evaluate_id"));
temp.put("objective_id",param.getString("objective_id"));
//判断之前是否已经导入同一目标平时作业考核方式总分
achievement_ids.forEach(item -> {
if(StringUtils.isNotBlank(item.getString("achievement_id"))
&& temp.getString("evaluate_id").equals(item.getString("evaluate_id"))
&& temp.getString("objective_id").equals(item.getString("objective_id"))
&& temp.getString("student_id").equals(item.getString("student_id"))){
temp.put("achievement_id", item.getString("achievement_id"));
}
});
if(StringUtils.isBlank(temp.getString("achievement_id"))){
temp.put("achievement_id", UuidUtil.get32UUID());
}
insertSumList.add(temp);
}
}
}
//导入学生作业分数以及学生平时作业考核方式总分
service.insertScoreData(insertList,insertSumList,strColumn.get(1).get(0));
pd.put("msg","success");
return pd;
}
岁月如歌，，，
