SpringBoot+LayUI+poi 实现Excel文件上传下载
1.前端
更多样式及参数可参考官网：https://www.layui.com/doc/modules/upload.html
<div class="content_mid100per">
<div id="layout">
<form class="layui-form" enctype="multipart/form-data">
<div class="layui-form-item">
<label class="layui-form-label"></label>
<img src="${basePath}/images/moban.png" width="300px" height="100px"/>
</div>
<div class="layui-form-item">
<label class="layui-form-label">模板下载</label>
<button type="button" class="layui-btn layui-btn-normal download-btn">下载模板</button>
</div>
<div class="layui-form-item">
<label class="layui-form-label">选择文件</label>
<button type="button" class="layui-btn layui-btn-normal" id="importData">选择文件</button>
<button type="button" class="layui-btn" id="sendMessage">发送短信</button>
</div>
</form>
</div>
</div>js部分：
<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: "#importData", //导入id
url: "${basePath}/sms/importData",
auto: false, // 与bindAction一起使用，不直接上传，当点击#sendMessage按钮时才上传
bindAction: '#sendMessage',
accept: "file",
size: 50,
/* 允许上传文件的后缀 */
exts: 'xls|xlsx|xlsm|xlt|xltx|xltm',
/* 上传完毕回调 */
done: function (res) {
if(res.code==0){
iconValue=6;
}else if(res.code==500){
iconValue=5;
}
layer.msg(res.msg, {icon: iconValue,time: 2000},function(){
window.location.reload();
});
},
error: function(){
//请求异常回调
alert("服务器出小差啦，请稍后再试");
}
});
$('.download-btn').click(function() {
window.location.href = '${basePath}/sms/download'
});
})
</script>后端下载：关于下载的方式有很多种，这是我工作中所应用的一种，将customer.xlsx模板存到项目的resources下，当前端调用下载文件接口时，下载模板并讲下载文件重命名为短信发送模板.xlsx
@RequestMapping("download")
@SysLog(value = "下载模板")
public void downloadTemp(HttpServletResponse response) throws FileNotFoundException {
InputStream inputStream = null;
try {
// 下载文件的模板存放位置
File file = new File("..\\sms_server\\src\\main\\resources\\customer.xlsx");
inputStream = new FileInputStream(file);
SmsController.fileDownload(file.getName(), inputStream, response);
} catch (Exception e) {
log.error("出现异常了，请稍后重试"+e.getMessage());
}
}
//文件下载方法，可以放在util中
public static void fileDownload(String filename, InputStream input, HttpServletResponse response) {
try {
filename="短信发送模板.xlsx";
byte[] buffer = new byte[4096];
int readLength = 0;
response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("utf-8"), "ISO-8859-1"));
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
while ((readLength = input.read(buffer)) > 0) {
byte[] bytes = new byte[readLength];
System.arraycopy(buffer, 0, bytes, 0, readLength);
toClient.write(bytes);
}
input.close();
toClient.flush();
toClient.close();
} catch (Exception e) {
e.printStackTrace();
}
}后端上传：
读取Excel文件使用的是poi方法，还要一种EasyExcel方法。
pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.11</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.11</version>
</dependency>controller
@RequestMapping(value = "/importData", method = RequestMethod.POST, consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
@SysLog(value = "短信自定义群发")
@ResponseBody
public ResultUtil importData(@RequestPart(value = "file") MultipartFile file) {
return transactionHandler.importExcel(file);
}package com.taiji.sms.util;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import com.taiji.sms.dao.SmsListDao;
import com.taiji.sms.entity.SmsListEntity;
import com.taiji.sms.foundation.common.ResultUtil;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@Component
public class TransactionHandler {
@Autowired
private SmsListDao smsListDao;
/**
* 导入
*
* @param file
* @return
*/
public ResultUtil importExcel(MultipartFile file) {
try {
InputStream inputStream = file.getInputStream();
HashMap<String, Object> resultMap = ExcelUtil.readExcel(inputStream, file.getOriginalFilename());
inputStream.close();
if (resultMap.get("code").equals("-1")) {
String resuString=resultMap.get("msg").toString();
return ResultUtil.error(resuString);
}
else {
List<SmsListEntity> list=(List<SmsListEntity>)resultMap.get("msg");
for(SmsListEntity smsListEntity : list) {
// 这部分为我自己的业务逻辑代码，读取验证通过的excel文件内容插入数据库，后续dao层逻辑不在赘述
smsListDao.insertSMSList(smsListEntity);
smsListDao.insertReportStatus(smsListEntity.getSmId(),"99");
}
}
} catch (Exception e) {
log.error("importData发生异常"+e.getMessage());
}
return ResultUtil.ok("上传成功");
}
}
Util:
package com.taiji.sms.util;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.UUID;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.taiji.sms.entity.SmsListEntity;
import com.taiji.sms.foundation.common.HttpContextUtils;
import lombok.extern.slf4j.Slf4j;
@Slf4j
public class ExcelUtil {
/**
* 处理上传的文件
*
* @param in
* @param fileName
* @return
* @throws Exception
*/
public static HashMap<String, Object> readExcel(InputStream in, String fileName) throws Exception {
HashMap<String, Object> hashMap=new HashMap<String, Object>();
String username=(String)HttpContextUtils.getValueFromHttpSession("UserName");
List<SmsListEntity> list = new ArrayList<SmsListEntity>();
//创建Excel工作薄
try {
Workbook work = getWorkbook(in, fileName);
if (null == work) {
throw new Exception("创建Excel工作薄为空！");
}
Row row = null;
Cell cell = null;
Sheet sheet = work.getSheetAt(0);
//验证导入文件的标题头是否合法
String[] columnName = {"手机号", "短信内容"};
String resultString=verificationStudentExcelHeadLine(sheet, columnName);
if (!resultString.equals("验证通过")) {
hashMap.put("code", "-1");
hashMap.put("msg", "请按照模板上传excel文件");
return hashMap;
}
// 获取第一列和第二列数据存入list
List oneCellString=verificationDataReasonable(sheet,0);
List twoCellString=verificationDataReasonable(sheet,1);
// 判断每一列数据是否合法 例如：我这里的需求是：第一列必须为合法手机号，第二列短信内容不得超过300字符
HashMap<String, String> resultVerification=verificationCellReasonable(oneCellString,twoCellString);
if (resultVerification.get("code").equals("-1")) {
hashMap.put("code", "-1");
hashMap.put("msg",resultVerification.get("message"));
return hashMap;
}
for (int i =1; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
String phoneString=row.getCell(0).toString();
String messageString=row.getCell(1).toString();
SmsListEntity smsListEntity=new SmsListEntity();
smsListEntity.setPhonenumber(phoneString);
smsListEntity.setMessage(messageString);
smsListEntity.setUsername(username);
smsListEntity.setSmId(UUID.randomUUID().toString().replaceAll("-", ""));
list.add(smsListEntity);
}
work.close();//这行报错的话 看下导入jar包的版本
} catch (Exception e) {
log.error("[error----->readExcel异常]"+e.getMessage());
}
hashMap.put("code", "0");
hashMap.put("msg",list);
return hashMap;
}
/**
* 验证第一列数据是否为合法电话号、第二列数据是否内容不超过三百字
* @param oneCellString
* @param twoCellString
* @return
*/
private static HashMap<String, String> verificationCellReasonable(List oneCellString, List twoCellString) {
HashMap<String, String> hashMap=new HashMap<String, String>();
if (oneCellString.size()==0||twoCellString.size()==0) {
hashMap.put("code", "-1");
hashMap.put("message", "上传内容不能为空");
return hashMap;
}
// 验证手机号的正则表达式
for (Object object : oneCellString) {
if ((object == null || "".equals(object)) || !(object.toString().matches("[1][3578]\\d{9}"))) {
hashMap.put("code", "-1");
hashMap.put("message", "表中包含不合法手机号！");
return hashMap;
}
}
for (Object object1: twoCellString) {
if((object1 == null || "".equals(object1)) || object1.toString().length()>300) {
hashMap.put("code", "-1");
hashMap.put("message", "短信内容不得为空且不得超过300字");
return hashMap;
}
}
hashMap.put("code", "1");
hashMap.put("message", "验证通过");
return hashMap;
}
/**
* 获取后一列数据存入list
* @param sheet
* @return
*/
private static List verificationDataReasonable(Sheet sheet,Integer index) {
List resultList = new ArrayList();
Row row = null;
Cell cell = null;
int rows = sheet.getLastRowNum();
for (int i = 1; i <= rows; i++) {
cell = sheet.getRow(i).getCell(index);
resultList.add(cell);
}
return resultList;
}
/**
* 后端判断文件格式方式，其实这部分前端已经做过了判断，但是需求要求前后端均要判断
*
* @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 verificationStudentExcelHeadLine(Sheet sheet, String[] columnName) throws Exception {
String result = null;
try {
Row row = sheet.getRow(0);
if (row != null && row.getLastCellNum() >= columnName.length) {
int lastCellNum = row.getLastCellNum();
for (int idx = 0; idx < lastCellNum; idx++) {
// 将Cell数据转换位String
String value = getStringCellValue(row.getCell(idx));
if (idx < 4) {
if (StringUtils.isBlank(value) || !columnName[idx].equals(value)) {
result = "标题行第" + (idx + 1) + "列名称错误！";
}
} else {
if (idx == 4) {
if (StringUtils.isBlank(value)) {
result = "标题与导出学员的表格表头不一致";
}
}
}
}
} else {
result = "上传文件首行不能为空或与导出学员的表格表头不一致！";
}
result="验证通过";
} catch (Exception ex) {
log.error("【ERROR】--->验证表格标题出现异常"+ex.getMessage());
}
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;
}
}
用到的方法比较繁琐，有更好方法的小伙伴欢迎留言哦-------------------------------end-