通过页面将Excel导入,即上传到服务器文件夹下
<%--导入
--%>
<div id="table_upload_window" class="easyui-dialog" title="上传业务模块Excel" style="width:350px; height:250px;top:20px;" data-options="modal:true,closed:true,
buttons:[{text:'取消',handler:function(){$('#table_upload_window').dialog('close');$('#uploadInfoDiv').html('');}}]">
<form id="table_upload_form" method="post" enctype="multipart/form-data" style="margin:5px;padding:5px">
<table>
<tr>
<td width=50 height=40>文 件:</td>
<td><input id = "tableExcel" type="file" name="file" class="filebox" style="length:200px"/></td>
</span></tr>
<tr>
<td colspan="2" align="middle" height=40><input type="button" value="上 传"
οnclick="uploadFile();"/></td>
</tr>
<tr>
<div style="color:red" align="center">提示:请使用下载的模版进行导入</div>
</tr>
</table>
</form>
<div id="uploadInfoDiv" style="display: none;">
</div>
</div>
function upload(){
$("#table_upload_form").form('clear');
disableForm('table_upload_form', false);
$("#table_upload_window").dialog('setTitle', "导入Excel").dialog('open');
}
function uploadFile(){
//$.messager.progress();<span style="white-space:pre"> </span>// 显示进度条
$('#table_upload_form').form('submit', {
url: '${ctx}/readorexport/readExceltable',
onSubmit: function(){
var isValid = true;
var files = document.getElementById("tableExcel").value;
if(files.length == ""){
isValid = false;
$.messager.alert("提示信息","请选择上传文件");
// 如果表单是无效的则隐藏进度条
//$.messager.progress('close');
}
return isValid;<span style="white-space:pre"> </span>// 返回false终止表单提交
},
success: function(data){
debugger;
var messJson = eval('('+data+')');
if(messJson.mess=='success'){
$("#tablegrid").datagrid('reload');
$.messager.alert("提示信息", "导入成功");
$("#table_upload_window").dialog('close');
//$("#uploadInfoDiv").css("display",'block');
// 先清空
//$("#uploadInfoDiv").empty();
//var mess = '<span style="color: red;">'+messJson.batchAddInfo+'</span>';
// 后添加错误信息
//$("#uploadInfoDiv").append(mess);
//$.messager.alert("提示信息", mess);
$.messager.alert("提示信息", "导入成功");
$(this).form('clear');
}else{
var mess = '<span style="color: red;">'+messJson.mess+'</span>';
$("#uploadInfoDiv").css("display",'block');
// 先清空
$("#uploadInfoDiv").empty();
// 后添加错误信息
$("#uploadInfoDiv").append(mess);
}
}
});
}
//下载模版
function download1(){
<span style="white-space:pre"> </span>window.open("${ctx}/template/template_tablemanage.xls");
}
<span style="font-family: Arial, Helvetica, sans-serif;">Controller</span>
<pre name="code" class="java"><span style="font-size:12px;">@RequestMapping(value = "/readExceltable", method = RequestMethod.POST)
@ResponseBody
public Map<String, Object> readExceltable(@RequestParam MultipartFile file){
Map<String, Object> result = new HashMap<String, Object>();
//获取允许上传的文件类型
List<String> fileExtentionNameList = Arrays.asList(new String[] {"xls", ".xls"});
try{
//获取文件后缀
String fileExtentionName = FilenameUtils.getExtension(file.getOriginalFilename());
if(fileExtentionNameList.contains(fileExtentionName)){
InputStream in = file.getInputStream();
try{
//获取Excel中的内容
Map<String, List<Map<String, String>>> excelReaderMap = ExcelReader.excelReader(in);
if(excelReaderMap ==null || excelReaderMap.size() ==0){
logger.info("Excel文件没有数据");
result.put("mess", "Excel文件没有数据...");
return result;
}
logger.info("Excel文件读取成功...");
logger.info("批量添加start...");
//批量添加
result = this.tablemanageService.addBatchTable(excelReaderMap);
logger.info("批量添加end...");
}catch (IOException e) {
result.put("mess", "Excel文件内容错误!");
logger.error("Excel文件内容错误!",e);
return result;
}
} else {
result.put("mess", "类型[" + fileExtentionName + "]的文件不能上传");
logger.error("类型[" + fileExtentionName + "]的文件不能上传");
return result;
}
} catch (IOException e) {
logger.error("文件上传失败", e);
result.put("mess", "文件上传失败");
}
return result;
}</span>
</pre><pre name="code" class="html">Service实现类
<pre name="code" class="java">@Override
public Map<String, Object> addBatchTable(Map<String, List<Map<String, String>>> excelReaderMap) {
Map<String, Object> result = new HashMap<String, Object>();
if (excelReaderMap == null || excelReaderMap.size() == 0) {
result.put("mess", "Excel文件没有数据...");
return result;
}
//存放Excel所有数据的List
List<Tablemanage> tablemanagelist = new ArrayList<Tablemanage>();
List<Propertymanage> propertymanagelist = new ArrayList<Propertymanage>();
for (String systemName : excelReaderMap.keySet()) {
logger.info("正在添加系统 [" + systemName + "] 的数据");
//处理库表信息
if(systemName.equals("库表信息")){
List<Map<String, String>> tablemanageMapList = excelReaderMap.get(systemName);
int s = 1;
for (Map<String, String> rowMap : tablemanageMapList) {
s=s+1;
Tablemanage tablemanage = new Tablemanage();
String tablenameString = rowMap.get("表名(必输)").trim();
//校验得到的表名是否为空
if(tablenameString.equals("")){
logger.error("保存系统[" + systemName + "]时,第["+ s +"]行数据,表名为空");
result.put("mess", "保存系统[" + systemName + "]时,第["+ s +"]行数据,表名为空");
return result;
}
//校验表名是否存在
Tablemanage tablemanage2 = tableService.getById(tablenameString);
if (null == tablemanage2) {
tablemanage.setTablename(tablenameString);
}else {
logger.error("保存系统[" + systemName + "]时,第["+ s +"]行数据,表名为["+tablenameString+"]的数据已存在");
result.put("mess", "保存系统[" + systemName + "]时,第["+ s +"]行数据,表名为["+tablenameString+"]的数据已存在");
return result;
}
//英文名称
String field02String = rowMap.get("英文名称(必输)").trim();
if(field02String.equals("")){
logger.error("保存系统[" + systemName + "]时,第["+ s +"]行数据,英文名称为空");
result.put("mess", "保存系统[" + systemName + "]时,第["+ s +"]行数据,英文名称为空");
return result;
}
tablemanage.setField02(field02String);
String field03String = rowMap.get("系统类型(必输)").trim();
if (field03String.equals("新")) {
tablemanage.setField03("0");
}else {
if (field03String.equals("旧")) {
tablemanage.setField03("1");
}else {
logger.error("保存系统[" + systemName + "]时,第["+ s +"]行数据,系统类型异常!");
result.put("mess","保存系统[" + systemName + "]时,第["+ s +"]行数据,系统类型异常!");
return result;
}
}
String field04String = rowMap.get("数据源类型(必输)").trim();
if (field04String.equals("交易文件")) {
tablemanage.setField04("0");
}else {
if (field04String.equals("其他库表")) {
tablemanage.setField04("1");
}else {
logger.error("保存系统[" + systemName + "]时,第["+ s +"]行数据,数据源类型异常!");
result.put("mess","保存系统[" + systemName + "]时,第["+ s +"]行数据,数据源类型异常!");
return result;
}
}
tablemanage.setText(rowMap.get("描述").trim());
tablemanage.setId(UUIDGenerator.getUUID());
tablemanage.setIsdelete(BTSConstants.CONNECTOR_TYPE_ISDELETE);
tablemanage.setField01(DateFormat.getCurrentDatatimeFormatString());
tablemanagelist.add(tablemanage);
}
}
//处理库表对应字段信息
if (systemName.equals("库表对应字段信息")) {
List<Map<String, String>> propertyMapList = excelReaderMap.get(systemName);
//判断对应字段信息表是否为空
if (propertyMapList.size()>0) {
int s = 1;
for (Map<String, String> rowMap : propertyMapList) {
s=s+1;
Propertymanage propertymanage = new Propertymanage();
//所属表
String tname = rowMap.get("所属表").trim();
//判断所属表是否为空
if (tname.equals("")) {
logger.error("保存系统[" + systemName + "]时,第["+ s +"]行数据,所属表为空");
result.put("mess", "保存系统[" + systemName + "]时,第["+ s +"]行数据,所属表为空");
return result;
}else {
//遍历,从list中获取表名所对应的库表信息中的所属表对照
for (int i = 0; i < tablemanagelist.size(); i++) {
if (tablemanagelist.get(i).getTablename().equals(tname)) {
//获取对应的表的id
String idsString = tablemanagelist.get(i).getId();
propertymanage.setTablemanageid(idsString);
}
}
}
String filednameString = rowMap.get("字段名称(必输)").trim();
if (filednameString.equals("")) {
logger.error("保存系统[" + systemName + "]时,第["+ s +"]行数据,字段名称为空");
result.put("mess", "保存系统[" + systemName + "]时,第["+ s +"]行数据,字段名称为空");
return result;
}
propertymanage.setFiledname(filednameString);
String field01String = rowMap.get("英文名称(必输)").trim();
if (field01String.equals("")) {
logger.error("保存系统[" + systemName + "]时,第["+ s +"]行数据,英文名称为空");
result.put("mess", "保存系统[" + systemName + "]时,第["+ s +"]行数据,英文名称为空");
return result;
}
propertymanage.setField01(field01String);
//校验字段长度是否为空
String fieldlengthstring = rowMap.get("字段长度(必输)").trim();
if (fieldlengthstring.equals("")) {
logger.error("保存系统[" + systemName + "]时,第["+ s +"]行数据,字段长度[" + fieldlengthstring + "]为空");
result.put("mess", "保存系统[" + systemName + "]时,第["+ s +"]行数据,字段长度[" + fieldlengthstring + "]为空");
return result;
}
propertymanage.setFieldlength(fieldlengthstring);
//校验字段类型是否为空
String typeString = rowMap.get("字段类型(必输)").trim();
if (typeString.equals("")) {
logger.error("保存系统[" + systemName + "]时,第["+ s +"]行数据,字段类型[" + typeString + "]为空");
result.put("mess", "保存系统[" + systemName + "]时,第["+ s +"]行数据,字段类型[" + typeString + "]为空");
//failureCount++;
return result;
}
propertymanage.setType(typeString);
propertymanage.setField02(rowMap.get("缺省值").trim());
String field04string = rowMap.get("是否必输(必输)").trim();
if (field04string.equals("是")) {
propertymanage.setField04("0");
}else {
if (field04string.equals("否")) {
propertymanage.setField04("1");
}else {
logger.error("保存系统[" + systemName + "]时,第["+ s +"]行数据,是否必输[" + field04string +"]错误!");
result.put("mess","保存系统[" + systemName + "]时,第["+ s +"]行数据,是否必输[" + field04string +"]错误!");
return result;
}
}
propertymanage.setId(UUIDGenerator.getUUID());
propertymanage.setField03(rowMap.get("代码字段范围").trim());
propertymanage.setText(rowMap.get("字段说明").trim());
propertymanage.setCreatedate(DateFormat.getCurrentDatatimeFormatString());
propertymanage.setIsdelete(BTSConstants.CONNECTOR_TYPE_ISDELETE);
propertymanagelist.add(propertymanage);
}
//批量添加库表
tablemanagedao.insertBatch(tablemanagelist);
//批量添加字段
propertymanageDAO.insertBatch(propertymanagelist);
}else {
if (tablemanagelist.size()>0) {
//批量添加库表
tablemanagedao.insertBatch(tablemanagelist);
result.put("mess", "success");
return result;
}else {
result.put("mess","Excel无数据");
return result;
}
}
}
}
result.put("mess", "success");
return result;
}
因为这个项目需要对每个数据进行校验,如果某个数据出现错误,将会显示到前台页面,具体到某行某列,错误原因等,可以让用户更加清晰的知道自己导入的数据错在哪里,除此之外,如果数据出现错误,导入的数据将不会成功,因为在对每条数据进行校验的时候,成功的话将会把每条数据放到list中,如果所有数据全部正确,将会把这些数据批量添加进数据库。需要导出的话可进行留言!