Java实现导入Excel数据
1、在页面先做一个按钮绑定事件（这里选择的是Layui的弹出层组件）
<%--导入数据--%>
<div class="allBtnSear" onclick="to_import()">导入数据</div>
<script>
function to_import() {
layer.open({
//基本层类型
type: 2,
//标题
title: ['导入数据'],
//宽高
area: ['600px', '400px'],
//遮罩
shade: 0.01,
//是否点击遮罩关闭
shadeClose: true,
//关闭按钮
closeBtn: 0,
//是否允许拉伸
resize: false,
//内容
content: ['${path}/perform/toImport.do', 'yes'],
success: function (layero, index) {
console.log('弹窗生成成功');
}
});
}
</script>
2、弹窗页代码
<body>
<div class="filePop clearfix">
<div class="clearfix">
<p class="red fl">* 只支持.xls 或 .xlsx格式的Excel文件 </p>
<div class="uploadBtn fr"><input type="file" id="file" name=""></div>
</div>
<div class="showFile clearfix" style="display: none;">
<span id="filename"></span>
<img src="${path}/STATIC/jianguan/image/close.png" onclick="removeFile()" class="fr">
</div>
</div>
<div class="popBtnBox clearfix">
<a class="popBtn ok" href="javascript:;">确认</a>
<a class="popBtn cancel" href="javascript:;">取消</a>
</div>
<script>
$(function () {
var file = $('#file'),
aim = $('#filename');
//检测文件类型并显示文件名
file.on('change', function( e ){
//e.currentTarget.files 是一个数组，如果支持多个文件，则需要遍历
var name = e.currentTarget.files[0].name;
var len = name.indexOf('.xls');
if (len > 0) {
$('.showFile').css('display','block');
aim.text( name );
} else {
$("#file").val(null);
dialogAlert('提示', "只支持.xls或.xlsx格式的Excel文件")
}
});
// 确定按钮
var frameIndex = parent.layer.getFrameIndex(window.name); //先得到当前iframe层的索引
$('.ok').click(function () {
var formData = new FormData();
var mfile = $("#file")[0].files[0]
if (mfile == undefined || mfile == null || mfile == '') {
dialogAlert("提示", "请先上传文件");
return;
}
formData.append("mfile",mfile);
$.ajax({
url: "${path}/perform/passengerFlowImport.do",
type: 'POST',
data: formData,
processData: false,
contentType:false,
success : function(data) {
console.info(data);
if (data === 'success') {
dialogAlert("提示","操作成功！",function(){
parent.layer.close(frameIndex);
});
/*dialogAlert('提示',"导入数据成功");
parent.layer.close(frameIndex); //执行关闭*/
} else {
dialogAlert('提示', "导入数据失败");
}
}
});
});
//取消按钮
$('.cancel').click(function () {
console.log('点击了取消');
parent.layer.close(frameIndex); //执行关闭
});
});
function removeFile() {
$("#file").val(null);
$('#filename').text('');
$('.showFile').css('display','none');
}
</script>
</body>
3、后台Controller层代码
@RequestMapping("/passengerFlowImport")
@ResponseBody
public String importData(MultipartFile mfile){
String result = null;
try {
SysUser user = (SysUser) session.getAttribute("user");
result = performanceService.exportData(user, mfile);
} catch (Exception e) {
e.printStackTrace();
result = "error";
}
return result;
}
4、导出Excel工具类
/**
*file： excel文件
*return:Map保存excel电子表格中的数据，每个键值对表示一个工作簿，key是工作簿的名字，value是工作簿的数据
*/
public Map<String, List<String[]>> importExcel (MultipartFile file) {
Workbook workbook = getWorkBook(file);
Map<String, List<String[]>> map = new HashMap<>();
if(workbook != null){
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
List<String[]> list = new ArrayList<String[]>();
//获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if(sheet == null){
continue;
}
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
//循环除了第一行的所有行
for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
//获得当前行
Row row = sheet.getRow(rowNum);
if(row == null){
continue;
}
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数
int lastCellNum = row.getLastCellNum();
String[] cells = new String[row.getLastCellNum()];
//循环当前行
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
map.put(sheet.getSheetName(), list);
}
}
return map;
}
5、后台Service层代码
@Override
@Transactional
public String exportData(SysUser user, MultipartFile mfile) {
String ret = "error";
String originalFilename = mfile.getOriginalFilename();
// 严重名称是否为空
if (StringUtils.isBlank(originalFilename)) {
return ret;
}
String[] fields = new String[]{"city", "area", "venueName", "venueGrade", "dgrc", "dgrcScore", "zbhd", "zbhdScore", "zbhdgm", "zbhdgmScore", "qgxhd",
"qgxhdScore", "cysjhd", "cysjhdScore", "rxsjhd", "rxshjdScore" , "lmhd", "lmhdScore" , "fwjchdScore", "pthd" , "pthdScore", "xcxzhd" , "xcxzhdScore" , "xchd",
"xchdScore", "jjchdTimes", "jjchdTimesScore", "qzpj", "qzpjScore", "zxl", "zxlScore", "bzjl", "bzjlScore", "xhlj", "xhljScore", "yhtg", "yhtgScore", "score", "rank"};
if (user != null) {
Map<String, List<String[]>> map = exportExcel.importExcel(mfile);
for (Map.Entry<String, List<String[]>> entry : map.entrySet()) {
List<String[]> list = entry.getValue();
for (int i = 0; i < list.size(); i++ ) {
try {
String[] strs = list.get(i);
Map<String, Object> row = new HashMap<>();
for (int t = 0; t < strs.length; t++) {
row.put(fields[t], strs[t]);
}
PerformanceCulture performanceCulture = JSON.parseObject(JSON.toJSONString(row), PerformanceCulture.class);
if (StringUtils.isNotBlank(performanceCulture.getCity())) {
// 根据城市名称找到对应的城市id
Map<String, Object> paramMap = new HashMap<>();
paramMap.put("deptName", performanceCulture.getCity());
String deptId = sysDeptMapper.querySysDeptIdByDeptName(paramMap);
if (StringUtils.isBlank(deptId)) {
throw new RuntimeException("导出错误,城市名称不存在");
}
performanceCulture.setCity(deptId);
} else {
throw new RuntimeException("导出错误,城市名称不存在");
}
if (StringUtils.isNotBlank(performanceCulture.getArea())) {
// 根据区县名称找到对应的区域id
Map<String, Object> paramMap = new HashMap<>();
paramMap.put("deptName", performanceCulture.getArea());
String deptId = sysDeptMapper.querySysDeptIdByDeptName(paramMap);
if (StringUtils.isBlank(deptId)) {
throw new RuntimeException("导出错误,区域名称不存在");
}
performanceCulture.setArea(deptId);
} else {
throw new RuntimeException("导出错误,区域名称不存在");
}
if (StringUtils.isNotBlank(performanceCulture.getVenueName())) {
// 根据场馆，找到对应的场馆id
Map<String, Object> paramMap = new HashMap<>();
paramMap.put("deptName", performanceCulture.getCity());
CmsVenue venue = cmsVenueMapper.queryVenueByVenueName(performanceCulture.getVenueName());
if (venue == null) {
throw new RuntimeException("导出错误,场馆不存在");
}
performanceCulture.setVenueId(venue.getVenueId());
} else {
throw new RuntimeException("导出错误,场馆不存在");
}
PerformanceCulture performanceLibrary = performanceCultureMapper.selectByVenueId(performanceCulture.getVenueId());
if (performanceLibrary == null) {
performanceCulture.setId(UUIDUtils.createUUId());
performanceCultureMapper.insertSelective(performanceCulture);
} else {
performanceCulture.setId(performanceLibrary.getId());
performanceCultureMapper.updateByPrimaryKeySelective(performanceCulture);
}
} catch (Exception e) {
e.printStackTrace();
return "error";
}
}
}
return "success";
} else {
return "noLogin";
}
}
扫一扫
6806
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
