参考:https://www.cnblogs.com/zhenghengbin/p/9490511.html
1.maven依赖
<!-- excel -->
<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>
2.前端
<div class="layui-inline" style="margin-right: 0;">
<div class="layui-input-inline" style="width:100%;">
<button class="layui-btn layui-btn-sm" data-type="import" id="importData" onclick="javascript:return false;">导入</button>
</div>
</div>
......
layui.config({
base: '../../../layuiadmin/' //静态资源所在路径
}).extend({
index: 'lib/index' //主入口模块
}).use(['index', 'table', 'upload', 'common'], function () {
let table = layui.table
, admin = layui.admin
, common = layui.common
, $ = layui.$
, form = layui.form
, layer = layui.layer
, upload = layui.upload
;
upload.render({
elem: "#importData",//导入id
url: "/Transaction/importData",
accept: "file",
exts: 'xls|xlsx|xlsm|xlt|xltx|xltm',
before: function(obj) {
layer.msg('文件上传中...', {
icon: 16,
shade: 0.01,
time: 0
})
},
done: function (res) {
layer.close(layer.msg());//关闭上传提示窗口
if (res.code == 0) {
}else{
layer.msg(res.message,{icon:5});
}
}
});
});
3.后端
@Slf4j
@RequestMapping(value = "/Transaction")
@Controller
public class TransactionConroller {
@Autowired
private TransactionHandler transactionHandler;
// 导入
@ApiOperation("上传")
@RequestMapping(value = "/importData", method = RequestMethod.POST, consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
@ResponseBody
public CommonResponse importData(@RequestPart(value = "file") MultipartFile file) {
return transactionHandler.importExcel(file);
}
}
@Slf4j
@Component
public class TransactionHandler {
/**
* 导入
*
* @param file
* @return
*/
public CommonResponse importExcel(MultipartFile file) {
try {
InputStream inputStream = file.getInputStream();
log.info("============fileName=============="+file.getOriginalFilename());
List list = ExcelUtil.readExcel(inputStream, file.getOriginalFilename());
inputStream.close();
for (int i = 0; i < list.size(); i++) {
Object object = list.get(i);
log.info("============object=============="+object.toString());
}
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return CommonResponse.success();
}
}
public class ExcelUtil {
/**
* 处理上传的文件
*
* @param in
* @param fileName
* @return
* @throws Exception
*/
public static List readExcel(InputStream in, String fileName) throws Exception {
List list = new ArrayList<>();
//创建Excel工作薄
Workbook work = getWorkbook(in, fileName);
if (null == work) {
throw new Exception("创建Excel工作薄为空!");
}
Row row = null;
Cell cell = null;
Sheet sheet = work.getSheetAt(0);
// 获取第一个实际行的下标
log.info("======sheet.getFirstRowNum()========="+sheet.getFirstRowNum());
// 获取最后一个实际行的下标
log.info("======sheet.getLastRowNum()========="+sheet.getLastRowNum());
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
// 获取在某行第一个单元格的下标
log.info("======row.getFirstCellNum()========="+row.getFirstCellNum());
// 获取在某行的列数
log.info("======row.getLastCellNum()========="+row.getLastCellNum());
if (row == null || row.getFirstCellNum() == i) {
continue;
}
List<Object> li = new ArrayList<>();
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
cell = row.getCell(j);
li.add(cell);
}
list.add(li);
}
work.close();//这行报错的话 看下导入jar包的版本
return list;
}
/**
* 判断文件格式
*
* @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("."));
log.info("==========fileType========"+fileType);
if (".xls".equals(fileType)) {
workbook = new HSSFWorkbook(inStr);
} else if (".xlsx".equals(fileType)) {
workbook = new XSSFWorkbook(inStr);
} else {
throw new Exception("请上传excel文件!");
}
return workbook;
}
}