项目背景：springmvc架构下，前端jsp（引入layui框架样式）需要实现导出excel文件（模板），用户填写内容后，再上传进系统，系统解析保存excel文件中的数据。
1、【模板制作】提前做好一个excel文件，可以用Excel自带的功能限制单元格的格式：例如某列只允许填写纯数字，某列不允许为空等等，以便用户填写错误。
2、【导出模板】把模板放到项目中，前端请求后，后端直接下载模板。
3、【导入】使用layui框架实现前端，java后端读取excel文件，POI框架操作文件，验证数据正确性等。
<script src="${xxxx}/layui-v2.4.5/layui/layui.all.js" charset="utf-8"></script>有一弹窗，点击【导入xx】按钮之前不显示。点击【导入xx】按钮后弹出：
<body>
<div style="display: none;padding: 5px" id="showUploadExcelDiv">
<form action="" method="post" class="layui-form layui-form-pane" id="exportFrm" lay-filter="exportFrm">
<div class="layui-upload" >
<button type="button" style="margin-top: 20px;margin-left: 20px" class="layui-btn layui-btn-warm"
onclick="downloadTemplate()">下载模板</button>
<button type="button" style="margin-top: 20px;margin-left: 20px" class="layui-btn layui-btn-normal"
id="chooseFile">选择文件</button>
<button type="button" style="margin-top: 20px" class="layui-btn"
id="uploadFle">开始上传</button>
</div>
</form>
</div>
<table class="layui-hide" id="test" lay-filter="test"></table>
<form>
<div class="panel-body">
<div class="row-fluid">
<div class="span3">
<span class="label-none"> <br/></span>
<button type="button" id="uploadFile" class="layui-btn layui-btn-sm" onclick="upload();" >导入</button>
</div>
</div>
</div>
</form>
<body> function upload(){
layer.open({
type: 1,
area: ['800px','150px'],
fixed: false,
title: '导入数据',
content: $("#showUploadExcelDiv"),
end: function () {
location.reload();
}
})
}
function downloadTemplate(){
//后端下载地址
location.href="${xxx}/xxx/yyy/downloadTemplate";
} <script>
/* 导入 */
layui.use(['form', 'layedit','upload'], function(){
var form = layui.form,
$ = layui.jquery,
layer = layui.layer,
layedit = layui.layedit,
upload=layui.upload;
upload.render({
elem: "#chooseFile", //导入id
//这里可以带一些页面上想传给后台的参数
data:{
id:function(){
var id = $("#id").val();
return id;
},
year:function(){
var year = $("#year").val();
return year;
}
},
url: '${xx}/xxx/yyy/importExcel?year='+year+"&id="+id,
// 与bindAction一起使用，不直接上传，当点击#uploadFle按钮时才上传
auto: false,
bindAction: '#uploadFle',
accept: "file",
size: 10240,
exts: 'xls|xlsx',
/* 上传完毕回调 */
done: function (res) {
/* 后端传code值：1代表成功，0代表失败 */
var code = res.code;
if(code == "1"){
top.$.jBox.tip("导入成功！");
//可以跳转到某页面
//location.href="${xxx}/xxx/yyy/form;
//可以刷新本页面
//window.location.reload()
}else{
//失败后弹出页面，上面附有失败原因（后端写好传来）
layer.confirm(res.msg, {title:'导入失败，系统提示'},function(index){
layer.close(index);
window.location.reload()
});
}
},
error: function(){
//请求异常回调
top.$.jBox.tip("服务器异常，请稍后再试");
window.location.reload()
}
});
})
</script>效果展示：
/**
* 文件模板下载
* @param fileModleId
* @param request
* @param response
* @throws Exception
*/
@RequestMapping(value = "downloadTemplate")
public void downloadTemplate(HttpServletResponse response) throws Exception {
//获取项目中模板为输入流
InputStream is=Thread.currentThread().getContextClassLoader().getResourceAsStream("templates/xxx/yyyy/DetailTemp.xlsx");
//利用poi框架导出文件
//org.apache.poi.xssf.usermodel.XSSFWorkbook;
XSSFWorkbook wb0 = new XSSFWorkbook(is);
try {
OutputStream out = response.getOutputStream();
response.setHeader("Content-disposition", "attachment;filename="+ new String( "导入成本明细清单模板.xlsx".getBytes("gb2312"), "ISO8859-1" ));
response.setContentType("application/msexcel;charset=UTF-8");
wb0.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
} /**
* 导入excel文件
* @param file
* @return
*/
@RequestMapping(value = "importExcel", method = RequestMethod.POST, consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
@ResponseBody
public HashMap<String, Object> importData(@RequestPart(value = "file") MultipartFile file,HttpServletRequest request) {
//获取导入时传来的参数：可做业务处理：略
String year = request.getParameter("year");
String id = request.getParameter("id");
//构建返回前端值，此处使用HashMap<String, Object>
//其中key为code value为1代表成功，value为0代表失败
// key为msg，value写入具体错误信息是什么
HashMap<String, Object> resultMap = null;
try {
InputStream inputStream = file.getInputStream();
//写个工具类，处理excel 的校验、保存：返回值直接是可以对接前端的
resultMap = util.readExcel(inputStream, file.getOriginalFilename(),new XXXEntity());
inputStream.close();
return resultMap;
} catch (Exception e) {
System.out.print("[导入异常]"+e.getMessage());
resultMap.put("code", "0");
resultMap.put("msg", "读取文件异常！");
return resultMap;
}
}@Service
public class ExcelUtilForXXXImport {
private static final Logger logger = LoggerFactory.getLogger(ExcelUtilForXXXImport.class);
@Autowired
private XXXEntityService xxxEntityService;
/**
* 处理上传的文件
*
* @param in
* @param fileName
* @return
* @throws Exception
*/
public HashMap<String, Object> readExcel(InputStream in, String fileName,XXXEntity xxxEntity) throws Exception {
//构建返回值
HashMap<String, Object> hashMap=new HashMap<String, Object>();
//创建Excel工作薄
try {
//判断文件格式是否为excel，是否为空
//详见：3）后端校验文件格式的方法
Workbook work = getWorkbook(in, fileName);
if (null == work) {
throw new Exception("创建Excel工作薄为空！");
}
//读第一页
Sheet sheet = work.getSheetAt(0);
//step1: 验证导入文件的标题头是否合法
String[] columnName = {"序号", "月份"，...};
//详见 ： 4）校验表格标题行方法
String resultString=verificationExcelHeadLine(sheet, columnName);
//有返回信息代表有错误，没有返回信息代表验证通过
if (!resultString.equals("")) {
hashMap.put("code", "0");
hashMap.put("msg", "请使用正确模板上传excel文件。"+resultString);
return hashMap;
}
//step2: 验证导入文件的标题 列 是否合法
String[] colName = {"第一月", "第二月",。。。};
//详见： 6）判断列标题头是否正确
String resultStr = verificationExcelHeadLineTwo(sheet, colName);
//有返回信息代表有错误，没有返回信息代表验证通过
if (!resultStr.equals("")) {
hashMap.put("code", "0");
hashMap.put("msg", "请使用正确模板上传excel文件。"+resultStr);
return hashMap;
}
//step3: 验证数据是否为空，长度是否过长，是否为纯数字。。。
// 详见7）校验数据是否为空
String verificationDateResultString = verificationDate(sheet);
if (!verificationDateResultString.equals("")) {
hashMap.put("code", "0");
hashMap.put("msg", verificationDateResultString);
return hashMap;
}
//step4: 文件内容合法，做业务逻辑处理（保存）
// 详见9）做业务逻辑处理（保存数据）
String saveResultString = insertData(sheet,xxxEntity);
if (!saveResultString.equals("")) {
hashMap.put("code", "0");
hashMap.put("msg", saveResultString);
return hashMap;
}
//以上都顺利进行，返回前端：导入成功
hashMap.put("code", "1");
hashMap.put("msg", "导入成功！");
return hashMap;
} catch (Exception e) {
System.out.print("[读取excel异常]"+e.getMessage());
hashMap.put("code", "0");
hashMap.put("msg", "读取excel异常");
return hashMap;
}
}
} /**
* 后端判断文件格式方式
*
* @param inStr
* @param fileName
* @return
* @throws Exception
*/
public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook workbook = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (".xls".equals(fileType)) {
workbook = new HSSFWorkbook(inStr);
} else if (".xlsx".equals(fileType)) {
workbook = new XSSFWorkbook(inStr);
} else {
throw new Exception("请上传excel文件！");
}
return workbook;
} /***
* 校验导入的列表Excel文件标题行是否为标准行
*/
public static String verificationExcelHeadLine(Sheet sheet, String[] columnName) throws Exception {
String result = "";
try {
//获取第一行：标题行
Row row = sheet.getRow(0);
if (row != null && row.getLastCellNum() >= columnName.length) {
int lastCellNum = row.getLastCellNum();
//循环遍历指定的数组内容与文件中内容一一对比
for (int idx = 0; idx < lastCellNum; idx++) {
//将格子中的内容转为java的string类型的方法
//详见5）将excel格子中的内容转为java的string类型的方法
String value = getStringCellValue(row.getCell(idx));
if (idx < 4) {
if (value == null || !columnName[idx].equals(value)) {
result = result+"标题行第" + (idx + 1) + "列名称错误！";
}
} else {
if (idx == 4) {
if (value == null) {
result = result+"标题标准表格表头不一致";
}
}
}
}
} else {
result = "上传文件首行不能为空或与标准表格表头不一致！";
}
} catch (Exception ex) {
logger.info("【导入成本】校验数据异常");
}
return result;
} /**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell Excel单元格
* @return String 单元格数据内容
*/
private static String getStringCellValue(Cell cell) {
String strCell = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
if (cell == null) {
return "";
}
return strCell;
}
public String verificationExcelHeadLineTwo(Sheet sheet, String[] columnName) {
String result = "";
try {
int allRowNum = sheet.getLastRowNum();
for(int i = 1; i <= allRowNum; i++) {
Row row = sheet.getRow(i);
if (row != null && row.getCell(1) != null) {
String value = getStringCellValue(row.getCell(1));
if (value == null || !columnName[i-1].equals(value)) {
result = result+"第" +i+"行第2列名称错误！";
}
} else {
result = "请选择正确模板！";
}
}
} catch (Exception ex) {
logger.info("【导入成本】校验数据异常");
}
return result;
} /**
* 校验数据是否为纯数字，是否为空
* @param sheet
* @return
*/
private String verificationDate(Sheet sheet) {
String result = "";
int allRowNum = sheet.getLastRowNum();
if(allRowNum != 12) {
result = "数据行数不为12行，请检查模板；";
return result;
}
try {
//循环行
for(int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
int allCellNum = row.getLastCellNum();
if (row != null && allCellNum == 9) {
//循环列
for (int idx = 0; idx < allCellNum; idx++) {
String value = getStringCellValue(row.getCell(idx));
if(!StringUtils.isNotEmpty(value)) {
result = result+"第"+i+"行"+"第"+idx+"列为空；";
}else {
//第二列以后为纯数字
if(idx>=2) {
//isNumeric为校验是否为数字的方法
//详见：8）校验是否为纯数字
if(!isNumeric(value)) {
result = result+"第"+i+"行"+"第"+idx+"列不是数字格式；";
}
}
}
}
} else {
result = "数据列数不为8列，请检查模板；";
return result;
}
}
} catch (Exception ex) {
logger.info("【导入成本】校验数据异常");
}
return result;
}
/**
* 判断是否为数字：直接去创建BigDecimal，失败就是非数字，BigDecimal校验的比我们厉害
* @param str
* @return
*/
public static boolean isNumeric(String str) {
String bigStr;
try {
bigStr = new BigDecimal(str).toString();
} catch (Exception e) {
return false;//异常 说明包含非数字。
}
return true;
}
private String insertData(Sheet sheet,XXXEntity xxxEntity) {
String result = "";
//构建子表数据
List<XXXEntity> xxxEntityList = new ArrayList<XXXEntity>();
Row row = null;
try {
for (int i =1; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
XXXEntity entity = new XXXEntity();
entity.setMonth(getStringCellValue(row.getCell(0)));
entity.setXXX(
new BigDecimal(
getStringCellValue(row.getCell(1))));
//...
xxxEntityList.add(entity);
}
xxxEntityService.save(jCCostManager);
}catch(Exception e) {
result = "数据格式异常无法保存。";
return result;
}
return result;
}
以上可以实现简单导入导出excel，如有错误，请指正！
希望广大网友有更简便的方法或框架可以提供～