环境准备
首先要导入poi的jar包,进入到apache官网下载。点击右边链接进入 apache官网poi下载链接
1.选择 poi-bin-4.1.0-20190412.zip
2.点击下载压缩包解压,找到poi-4.1.0.jar导入项目
如果是maven项目,在xml中添加以下依赖即可
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
一、Excel文件导出
页面展示:主要讲解实现此页面的Excel导出和Excel导入功能
前端代码:
html:
<button type="button" data-ope="out" class="btn bg-maroon btn-flat" id="excelOut">Excel导出</button>
js:
//点击导入按钮下载数据进Excel
$("#excelOut").click(function(){
/* window.location.href="${pageContext.request.contextPath}/users/downloadExcel"; */
window.open('${pageContext.request.contextPath}/users/downloadExcel');
})
后端代码:
/**
* Excel导出
*/
@RequestMapping("/downloadExcel")
@ResponseBody
public void downloadExcel(UserQuery userQuery,HttpServletResponse response) {
//1.从数据库当中取列表数据
List<User> user = userService.excelFindAll(userQuery);
//2.创建Excel 写到excel中,导入poi的jar
HSSFWorkbook wb = new HSSFWorkbook();//创建一个工作簿
HSSFSheet sheet = wb.createSheet("员工姓名");//给sheet页赋值
//创建一行
HSSFRow row = sheet.createRow(0);
//设置行的每一列的数据
row.createCell(0).setCellValue("编号");
row.createCell(1).setCellValue("登录名");
row.createCell(2).setCellValue("用户名");
/*取出每一个员工来去设置数据*/
HSSFRow users = null;
for(int i=0;i<user.size();i++) {
users=sheet.createRow(i+1);
users.createCell(0).setCellValue(user.get(i).getId());
users.createCell(1).setCellValue(user.get(i).getLoginName());
users.createCell(2).setCellValue(user.get(i).getName());
/*
* 对于时间的处理方法,需要把Date转为String
if(user.get(i).getTime()!=null){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String format = sdf.format(user.get(i).getTime());
users.createCell(3).setCellValue(user.get(i).getTime());
}else {
users.createCell(3).setCellValue("");
}
*/
}
//3.响应给浏览器
try {
//处理中文名乱码
String fileName = new String("员工信息.xls".getBytes("utf-8"),"iso8859-1");
//告诉浏览器以附件的形式下载
response.setHeader("content-Disposition","attachment;filename="+fileName);
wb.write(response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
}
}
下载出的Excel中的内容
二、Excel文件数据写入数据库
1.此项目使用了bootstrap框架,使用了bootstrap模态框,点击“Excel导入”按钮弹出模态框
前端代码:
html:
<!-- 模态对话框 -->
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
<h4 class="modal-title" id="myModalLabel">Modal title</h4>
</div>
<div class="modal-body">
<form method="post" enctype="multipart/form-data" id="uploadForm" action="${pageContext.request.contextPath}/users/uploadExcelFile">
<table>
<tr>
<td><input type="file" name="excel" style="width:180px;margin-top:20px;margin-left:5px;"></td>
<td><a href="javascript:void(0)" id="downloadTml">下载模板</td>
</tr>
</table>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
<button type="buttom" data-ope="upload" class="btn btn-primary">提交</button>
</div>
</form>
</div>
</div>
</div>
</div>
<!-- 触发模态框按钮 -->
<button type="button" data-ope="in" class="btn bg-maroon btn-flat btn-primary" id="excelIn" data-toggle="modal" data-target="#myModal">Excel导入</button>
js:
//点击导出按钮弹出模态框
$("#excelIn").on('shown.bs.modal', function () {
$('#myInput').focus();
})
2.点击下载模板
我放了一份模板在WebContent下自己创的static文件夹中
//下载模板
前端代码:
$("#downloadTml").click(function(){
window.open('${pageContext.request.contextPath}/users/downloadExcelTpl');
});
//下载模板
后端代码:
@RequestMapping(value="downloadExcelTpl",method=RequestMethod.GET)
@ResponseBody
public void downloadExcelTpl(HttpServletRequest request,HttpServletResponse responses) {
FileInputStream is = null;
try {
String fileName = new String("UserTpl.xls".getBytes("utf-8"),"iso8859-1");
responses.setHeader("content-Disposition","attachment;filename="+fileName);
//获取文件路径
String realPath = request.getSession().getServletContext().getRealPath("static/ExcelTml.xls");
is = new FileInputStream(realPath);
IOUtils.copy(is, responses.getOutputStream());
}catch (Exception e) {
e.printStackTrace();
}finally {
if(is!=null) {
try {
is.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
3.下载好后打开Excel写入信息
4.选择写好信息的Excel
5.点击提交按钮
//点击提交
前端代码:
$(".modal-footer>button").click(function(){
var ope = $(this).data("ope");
if(ope=="upload"){
$("#uploadForm").submit();
//会来到${pageContext.request.contextPath}/users/uploadExcelFile下
}
});
后端代码
//配置文件上传解析器 mvc中配置
@RequestMapping(value="uploadExcelFile")
@ResponseBody
public void uploadExcelFile(MultipartFile excel) {
System.out.println("in here");
try {
HSSFWorkbook wb = new HSSFWorkbook(excel.getInputStream());
HSSFSheet sheet = wb.getSheetAt(0);
//获取最大行号
int lastRow = sheet.getLastRowNum();
Row userRow = null;
for(int i=1;i<=lastRow;i++){
userRow = sheet.getRow(i);
User user = new User();
user.setLoginName((String)getCellValue(userRow.getCell(1)));//拿到第一列数据
user.setName((String)getCellValue(userRow.getCell(2)));//拿到第二列数据
int num = userService.add(user);
if(num!=0) {
System.out.println("add is success");
}
}
}catch(Exception e) {
e.printStackTrace();
}
}
//根据传入不同类型返回不同类型
private Object getCellValue(Cell cell) {
switch (cell.getCellType()) {
case STRING:
return cell.getRichStringCellValue().getString();
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
} else {
return cell.getNumericCellValue();
}
case BOOLEAN:
return cell.getBooleanCellValue();
case FORMULA:
return cell.getCellFormula();
}
return cell;
}
//在SpringMVC.xml中配置文件上传解析器
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="defaultEncoding">
<value>UTF-8</value>
</property>
<!-- 总文件大小最大不能超多。。。。 -->
<property name="maxUploadSize">
<value>#{10*1024*1024}</value>
</property>
</bean>
6.添加成功,数据库内信息