利用jxl导入Excel,导出Excel
导入maven依赖
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
导出
前端
$("#exportExcelBtn").click(function() {
//带数据到你的controller
window.location.href = "/student/msg/stuMsg_export"
})
后端
设置表名
WritableWorkbook workbook;
String fileName="学生信息表";
fileName = new String(fileName.getBytes(),"iso-8859-1");
response.setCharacterEncoding("gb2312");response.reset();
response.setContentType("application/OCTET-STREAM;charset=gb2312");
response.setHeader("pragma", "no-cache");
response.addHeader("Content-Disposition", "attachment;filename=\""+ fileName + ".xls\"");// 点击导出excle按钮时候页面显示的默认名称
workbook = Workbook.createWorkbook(response.getOutputStream());
设置sheet
WritableSheet sheet = workbook.createSheet("学生信息表", 0);
设置字体
// 设置字体的attribute
WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD);
WritableCellFormat format = new WritableCellFormat(font);
设置第一行的列名
// 3.设置column名
sheet.addCell(new Label(0, 0, "学号", format));
sheet.addCell(new Label(1, 0, "姓名", format));
sheet.addCell(new Label(2, 0, "性别", format));
sheet.addCell(new Label(3, 0, "民族", format));
sheet.addCell(new Label(4, 0, "手机号", format));
sheet.addCell(new Label(5, 0, "班级", format));
sheet.addCell(new Label(6, 0, "专业", format));
sheet.addCell(new Label(7, 0, "院系", format));
sheet.addCell(new Label(8, 0, "年级", format));
循环导入数据
List<Student>=mapper.getAllStudentMsg();
// 导入行
for (int i = 0, j = 1; i < stuList.size(); i++, j++) {
Student stuMsg = stuList.get(i);
// 设置列宽
sheet.setColumnView(i, 16);
// 重新设置部分列宽
sheet.setColumnView(3, 14);
sheet.setColumnView(6, 10);
sheet.setColumnView(7, 10);
// 设置行高
sheet.setRowView(i, 350);
// 设置字体的attribute
WritableFont font1 = new WritableFont(WritableFont.createFont("楷体 _GB2312"), 12, WritableFont.NO_BOLD);
WritableCellFormat format1 = new WritableCellFormat(font1);
sheet.addCell(new Label(0, j, stuMsg.getStuId(), format1));
sheet.addCell(new Label(1, j, stuMsg.getStuName(), format1));
sheet.addCell(new Label(2, j, stuMsg.getSex(), format1));
sheet.addCell(new Label(3, j, stuMsg.getNation(), format1));
sheet.addCell(new Label(4, j, stuMsg.getPhone(), format1));
sheet.addCell(new Label(5, j, stuMsg.getClassName(), format1));
sheet.addCell(new Label(6, j, stuMsg.getMajorName(), format1));
sheet.addCell(new Label(7, j, stuMsg.getDepName(), format1));
sheet.addCell(new Label(8, j, stuMsg.getGradeClassName(), format1));
}
// 5.写入数据
workbook.write();
// 6.关闭资源
workbook.close();
导入
前端
文件上传的表单
<form id="QueryForm" action="${ctx}/student/msg/stuMsg_To_Lead"
method="post" enctype="multipart/form-data">
<div class="row">
<div class="col-sm-3" style="width: 78%;">
<div class="box box-primary">
<div class="box-header with-border">
<div class="box-body">
<input id="excel_file" class="form-control" type="file"
name="filename" accept="xlsx" size="80" />
</div>
</div>
</div>
</div>
</div>
</form>
随便定义一个要提交的btn
<button class="btn btn-info" id="Excel_To_Lead" >导入</button>
jQuery提交表单
//文件上传
$("#Excel_To_Lead").click(function (){
$("#QueryForm").submit();
})
后端
@Controller
/**接收上传的文件
* @throws IOException
* @throws BiffException */
@RequestMapping("/stuMsg_To_Lead")
public String upLoad(@RequestParam(value="filename")MultipartFile file) {
List<Student> list=new ArrayList();
// 1.获取用户上传的文件
Workbook workbook;
try {
workbook = Workbook.getWorkbook(file.getInputStream());
// 2.获取工作簿sheet
Sheet sheet = workbook.getSheet(0);
// 3.获取总行数
int rows = sheet.getRows();
for (int i = 1; i < rows; i++) {
Student s=new Student();
s.setStuId(sheet.getCell(0, i).getContents());
s.setStuName(sheet.getCell(1, i).getContents());
s.setSex(sheet.getCell(2, i).getContents());
s.setNation(sheet.getCell(3, i).getContents());
s.setPhone(sheet.getCell(4, i).getContents());
s.setClassName(sheet.getCell(5, i).getContents());
// 4.添加到数据库中
list.add(s);
}
//调用服务层
service.buildInsertStudentMsgList(list);
// 5.关闭资源
workbook.close();
} catch (Exception e) {
throw new ErrorReturnPageException("重复导入数据");
}
//重定向跳到主页
return "redirect:/student/msg/list";
}
“provider动态sql”
/**
* Excel导入数据 插入多条数据
* @param studentList
* @return
*/
public static String buildInsertStudentMsgList(Map map) {
List<Student> urlBlack = (List<Student>) map.get("list");
StringBuilder sb = new StringBuilder();
sb.append("INSERT INTO student ");
sb.append("(stu_id, stu_name, class_id, sex,nation,phone) ");
sb.append("VALUES ");
MessageFormat mf = new MessageFormat("(#'{'list[{0}].stuId},#'{'list[{0}].stuName},#'{'list[{0}].classId},#'{'list[{0}].sex},#'{'list[{0}].nation},#'{'list[{0}].phone})");
for (int i = 0; i < urlBlack.size(); i++) {
sb.append(mf.format(new Object[]{i}));
if (i < urlBlack.size() - 1) {
sb.append(",");
}
}
return sb.toString();
}
mapper
//导入数据
@InsertProvider(type = StudentProvider.class, method = "buildInsertStudentMsgList")
int buildInsertStudentMsgList(List<Student> list);
