【配置准备】
因为项目采用的是springmvc和ejb结合,采用maven仓库管理项目,前台使用easy-ui框架。①做导入导出就要引入相应的excel的jar包,在三层的pom.xml文件中添加依赖:
<!-- 下边是导入导入的Jar -->
<dependency>
<groupId>com.tgb</groupId>
<artifactId>itoo-excelV2.0-api</artifactId>
<version>${project.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.tgb</groupId>
<artifactId>itoo-excelV2.0-tool</artifactId>
<version>${project.version}</version>
</dependency>
②在springmvc.xml文件中的配置:
<!-- SpringMVC上传文件时,需要配置MultipartResolver处理器 -->
<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="defaultEncoding" value="UTF-8" />
<!-- 指定所上传文件的总大小不能超过10485760000B。注意maxUploadSize属性的限制不是针对单个文件,而是所有文件的容量之和 -->
<property name="maxUploadSize" value="10485760000"></property>
<property name="maxInMemorySize" value="40960"></property>
</bean>
【导入具体实现】
前台jsp:
<div id="studentImport" class="easyui-window" title="批量导入学生"
data-options="modal:true,closed:true,"
style="width: 650px; height: 200px; padding: 10px;">
<form id="Manage" method="post" enctype="multipart/form-data"
action="" novalidate>
<br> <a
href="${pageContext.request.contextPath}/student/leadToExcelTemplet"
class="easyui-linkbutton" style="width: 120px">点击下载模板</a> <br>
<br> <br> <input id="uploadExcel" name="uploadExcel"
class="easyui-filebox" style="width: 70%"
data-options="prompt:'选择文件...'"> <a href="#"
class="easyui-linkbutton" style="width: 10%"
οnclick="uploadExcel()">导入学生</a>
</form>
</div>
前台JS:
//导入excel
function uploadExcel() {
//得到上传文件的全路径
var fileName = $('#uploadExcel').filebox('getValue')
//进行基本校验
if (fileName == "") {
$.messager.alert("提示",
"请选择上传文件!", "info");
} else {
//对文件格式进行校验
var d1 = /\.[^\.]+$/.exec(fileName);
if (d1 == ".xls" || d1==".xlsx") {
$('#Manage')
.form(
'submit',
{
url : "${pageContext.request.contextPath}/student/importStudent",
onSubmit : function() {
return $(this).form('validate');
},
success : function(result) {
var result = eval('(' + result+ ')');
if (result == "error") {
$.messager.alert("警告", "导入失败!","error");
$('#studentImport').dialog('close');
$('#dg').datagrid('reload');
} else {
$.messager.alert("提示", "导入成功!","info");
$('#studentImport').dialog('close');
$('#dg').datagrid('reload');
}
}
});
} else {
$.messager.alert("提示",
"请选择xls格式文件!", "info");
$('#uploadExcel').filebox('setValue', '');
}
}
}
controller--导出模板:
@RequestMapping("/student/leadToExcelTemplet")
public void leadToExcel(HttpServletRequest request,
HttpServletResponse response) {
excelUtil = new ExcelUtil();
try {
// excel表格的表头,map
LinkedHashMap<String, String> fieldMap = new LinkedHashMap<String, String>();
fieldMap.put("code", "学号");
fieldMap.put("name", "姓名");
fieldMap.put("sex", "性别");
fieldMap.put("classes.className", "所属班级");
fieldMap.put("entranceDate", "入学日期");
fieldMap.put("identityCardID", "身份证号");
fieldMap.put("nativePlaceNativePlace", "籍贯");
fieldMap.put("nation", "民族");
fieldMap.put("politicalStatus", "政治面貌");
fieldMap.put("accountAddress", "户口所在地");
fieldMap.put("originalPlace", "生源地");
fieldMap.put("graduatedSchool", "毕业学校");
fieldMap.put("email", "电子邮箱");
fieldMap.put("telNum", "手机");
String sheetName = "学生";
// 导出模板
excelUtil.leadToExcel(fieldMap, sheetName, response);
} catch (Exception e) {
e.printStackTrace();
}
}
controller--导入:
@RequestMapping(value = "/student/importStudent", method = RequestMethod.POST)
public void importStudent(HttpServletResponse response,
HttpServletRequest request) throws Exception {
// 创建一个通用的多部分解析器
CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
request.getSession().getServletContext());
InputStream inExcelFile = null;
// 判断 request 是否有文件上传,即多部分请求importDailyResult
if (multipartResolver.isMultipart(request)) {
// 转换成多部分request
MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
// 取得request中的所有文件名
Iterator<String> iter = multiRequest.getFileNames();
while (iter.hasNext()) {
// 记录上传过程起始时的时间,用来计算上传时间
int pre = (int) System.currentTimeMillis();
// 取得上传文件
MultipartFile file = multiRequest.getFile(iter.next());
try {
inExcelFile = file.getInputStream();
} catch (IOException e) {
e.printStackTrace();
}
}
}
// 创建一个list 用来存储读取的内容
List list = new ArrayList();
Workbook rwb = null;
Cell cell = null;
String result = "error";
// 获取Excel文件对象
try {
rwb = Workbook.getWorkbook(inExcelFile);
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
// 获取文件的指定工作表 默认的第一个
Sheet sheet = rwb.getSheet(0);
// 行数(表头的目录不需要,从1开始)
for (int i = 0; i < sheet.getRows(); i++) {
// 创建一个数组 用来存储每一列的值
String[] str = new String[sheet.getColumns()];
// 列数
for (int j = 0; j < sheet.getColumns(); j++) {
// 获取第i行,第j列的值
cell = sheet.getCell(j, i);
str[j] = cell.getContents();
}
// 把刚获取的列存入list
list.add(str);
}
List<Student> studentList = new ArrayList<Student>();
if (list.size() > 0) {
for (int i = 1; i < list.size(); i++) {
Student student = new Student();
Object[] object = (Object[]) list.get(i);
String code = object[0].toString();// 学号
String name = object[1].toString();// 姓名
String sex = object[2].toString();// 性别
String className = object[3].toString();// 班级
String classesId = null;
// 1.判断学生表中是否存在,根据学生code查询
String studentCodeResult = "error";
studentCodeResult = studentBean.queryByCode(code, dataBaseName);
if (studentCodeResult == "error") {
// 2.根据班级名称查 询班级id,如果不存在,需要打印日志,日志表明第几条数据,哪个字段有问题。
List<Classes> classesList = studentBean
.queryClassesByClassName(className, dataBaseName);
if (classesList != null && classesList.size() > 0) {
classesId = classesList.get(0).getId();
} else {
logger.info("第" + (i + 1) + "行的班级名称不规范");
}
// 将学生信息添加进studentList中--start
if (studentCodeResult == "error" && classesId != null) {
String id = CreateUUID22.getUUID22();
student.setId(id);
student.setCode(code);
student.setName(name);
student.setSex(sex);
student.setClassesId(classesId);
student.setDataBaseName(dataBaseName);
studentList.add(student);
}
// 将学生信息添加进studentList中--end
}
}
// 将学生信息导入到数据库-start
if (studentList.size() > 0) {
boolean flag = studentBean.saveEntitys(studentList);
if (flag == true) {
result = "success";
}
}
// 将学生信息导入到数据库-end
}
jacksonJsonUntil.beanToJson(response, result);
}
【导出具体实现】
前台JS:
//导出Excel
function exportExcel() {
document.getElementById("exportExcel").href = "${pageContext.request.contextPath}/student/exportStudent";
}
controller:
@RequestMapping("/student/exportStudent")
public void exportStudent(HttpServletRequest request,
HttpServletResponse response) {
String jobTitleName = null;
// 1.获取要导出的教师集合(可以在页面选择也可以使所有教师,暂定++为所有教师)
List<Student> studentList = studentBean
.queryStudentAll(dataBaseName);
// 2.创建Excel表头
excelUtil = new ExcelUtil();
LinkedHashMap<String, String> fieldMap = new LinkedHashMap<String, String>();
fieldMap.put("code", "学号");
fieldMap.put("name", "姓名");
fieldMap.put("sex", "性别");
String sheetName = "学生";
try {
// 导出模板
ExcelUtil.listToExcel(studentList, fieldMap, sheetName, response);
System.out.println("导出成功");
} catch (Exception e) {
e.printStackTrace();
}
}