1.引入org.apache.poi相关jar包
Apache POI(Poor Obfuscation Implementation)是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。API文档:http://poi.apache.org/apidocs/index.html ,我这里以SSM项目为例,在pom.xml配置文件中配置。
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.2</version>
</dependency>
2.在页面编写Excel上传的方法(这里使用了Layui组件),代码如下:
<!---------------------- 设置隐藏表单 ----------------------->
<div style="display: none;">
<form method="post" id="formFile" action=""
enctype="multipart/form-data">
<button type="button" id="uploadFile"></button>
</form>
</div>
<script>
var upload = layui.upload;
//上传Excel文件(添加数据)
function uploadExcelFile(){
$("#uploadFile").click();
}
//监听文件上传
upload.render({
elem : '#formFile',
url : '${ctx}/dataManage/importDidInfo.do',
accept: 'file', //普通文件
acceptMime:'.xls,.xlsx,.XLS',//设置文件的筛选
exts: 'xls|xlsx|XLS', //允许上传的文件后缀
field:"uploadFile", //默认文件域是file,也可以自己定义,与后台struts中获取文件名有关
before:function(obj){
onload();//加载层
},
done : function(res) {
layer.msg(res.text);//提示
searchAddDidInfo();//刷新数据表格
onclose();//关闭加载层
}
});
//显示加载层
function onload(){
layer.msg("正在上传", {
icon:16,
shade:[0.2, '#000'],
time:false//取消自动关闭
});
}
//关闭加载层
function onclose(){
layer.closeAll('loading');
}
</script>
3.controller层代码,如下所示:
@Controller
@RequestMapping("/dataManage")
@Api(value = "数据管理接口", description = "数据管理相关api")
public class DataManageController {
private Gson gson = new GsonBuilder().setDateFormat("yyyy-MM-dd HH:mm:ss").create();
@SuppressWarnings({ "resource", "unused", "unchecked" })
@ResponseBody
@RequestMapping(value = "/importDidInfo", method = RequestMethod.POST)
public Object importDidInfo(HttpServletRequest request, HttpSession session,
@RequestParam(value = "uploadFile", required = false) MultipartFile uploadFile)
throws IOException {
JsonReturn jsonReturn = new JsonReturn();
// poi--exl解析
InputStream is = uploadFile.getInputStream();
// 获取文件名称
String name = uploadFile.getOriginalFilename();
// 获取文件后缀名
String suffixName = name.substring(name.lastIndexOf("."));
Workbook work = null;//创建工作簿
if (".xls".equals(suffixName)) {
work = new HSSFWorkbook(is); // 2003-
} else {
work = new XSSFWorkbook(is); // 2007+
}
int successRows = 0;
if (work != null) {
Sheet sheet = null;
Row row = null;
// 获取session中的数据
List<DidInfo> listDid = (List<DidInfo>) session.getAttribute("listDid");
if (listDid == null) {
listDid = new ArrayList<DidInfo>();
}
// 判断工作簿中的工作表(Sheet)的个数
if (work.getNumberOfSheets() > 0) {
// 获取第一个工作表
sheet = work.getSheetAt(0);
if (sheet == null) {
jsonReturn.setText("这是一个空的工作簿");
return gson.toJson(jsonReturn);
}
// 遍历当前sheet中的所有行
if (sheet.getLastRowNum() > 0) {
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
// 排除第一行和空的行
if (row == null || row.getFirstCellNum() == j) {
continue;
}
// 遍历所有的列
Cell cell0 = row.getCell(0);// 数据类型
Cell cell1 = row.getCell(1);// 数据标识符
Cell cell2 = row.getCell(2);// 描述
Cell cell3 = row.getCell(3);// 单位
Cell cell4 = row.getCell(4);// 备注
DidInfo didInfo = new DidInfo();
// 赋值
didInfo.setDataType(cell0.toString());
didInfo.setDataIdentifier(cell1.toString());
didInfo.setDidDescribe(cell2.toString());
didInfo.setDidUnit(cell3.toString());
didInfo.setDidRemark(cell4.toString());
listDid.add(didInfo);
successRows++;// 成功条数
}
if (successRows == sheet.getLastRowNum()) {
jsonReturn.setText("导入成功");
} else if (successRows >= 0 && successRows < sheet.getLastRowNum()) {
int failRows = sheet.getLastRowNum() - successRows;
jsonReturn.setText(successRows + "条数据导入成功," + failRows + "条数数据导入失败");
}
// 将信息保存到session中
session.setAttribute("listDid", listDid);
} else {
jsonReturn.setText("物理行数为0");
}
}
} else {
jsonReturn.setText("创建Excel工作薄为空!");
}
is.close();
return gson.toJson(jsonReturn);
}
}
返回参数JsonReturn,示例代码:
package com.gx.vo;
import java.io.Serializable;
public class JsonReturn implements Serializable{
/**
*
*/
private static final long serialVersionUID = 1L;
private int code;
private String text;
private Object data;
public int getCode() {
return code;
}
public void setCode(int code) {
this.code = code;
}
public String getText() {
return text;
}
public void setText(String text) {
this.text = text;
}
public Object getData() {
return data;
}
public void setData(Object data) {
this.data = data;
}
}
4.上传的Excel文档以及上传成功后的截图,如下图所示: