HTML代码:
<div class="toolbar_btn top5">
<form id="uploadForm" action="${ctx}/cruiseRoom/showExcelInfo.json"
method="post" enctype="multipart/form-data">
<table>
<tr>
<td><input type="file" id="importExcel" name="file"></td>
<td><input type="button" value="导入Excel" id="btnImport"
onclick="upLoad()" class="l-button l-button-submit" /></td>
<td width="20%"></td>
<td><input type="button" value="模板下载" id="downFile" class="l-button l-button-submit" /> </td>
<%-- <td><a value="模板下载" src="${filesvrUrl }/filesvr/downLoad?id='cruiseRoom001'" class="l-button l-button-submit"></a></td> --%>
<!-- <td width="20%">
<input type="button" value="模板上传" id="upLoadFile" onclick="upLoadFile()" class="l-button l-button-submit" /> </td> -->
</tr>
</table>
</form>
</div>
js代码:
function upLoad(){
var txtIn = document.getElementById("importExcel");
var fileName=txtIn.files[0].name;
var reg=/^(.)+\.(xls|xlsx)$/i;
if (!reg.test(fileName)) {
$.ligerDialog.warn("文件格式不对!");
return;
}
var formData = new FormData($( "#uploadForm" )[0]);
$.ajax({
url: "${ctx}/cruiseRoom/showExcelInfo.json",
contentType:"application/json",
type: 'post',
data: formData,
async: false,
cache: false,
contentType: false,
processData: false,
success: function(data){
if(data.success){
var list=data.content;
loadData(list);
flag=true;
}
}
})
}
function downFile(){
var url="${url}";
var knName="${knName}";
window.location.href = ctx + "/visa/download?url=" + url + "&knName=" + knName;
}
controller的代码:
@RequestMapping(value = "/showExcelInfo.json", method = RequestMethod.POST)
@ResponseBody
public AjaxResult importExcel(@RequestParam(value = "file", required = false)MultipartFile file){
AjaxResult ajaxResult = new AjaxResult(true, "批量导入成功!");
List<CruiseRoom> list = null;
try {
list = new ArrayList<CruiseRoom>();
InputStream input = file.getInputStream();// 按此可以得到流
Workbook wb = null;
int i = 0; int m = 0; String info = "";
//默认xls
try{
wb = new HSSFWorkbook(input);
}catch(Exception e){
//如果为空,则用xlsx再试一次
input = file.getInputStream();
wb = new XSSFWorkbook(input);
}
//如果为空,则格式不对
if(wb==null){
throw new BusinessException("EXCEL 格式不对");
}
Sheet sheet = wb.getSheetAt(0); // 获得第一个表单
Map<String,String> typeMap = dictToMapService.getMapByDictType(DictType.CRUISE_ROOM_TYPE.getKey());
Iterator<Row> rows = sheet.rowIterator(); // 获得第一个表单的迭代器
while (rows.hasNext()) {
Row row = rows.next(); // 获得行数据
i = row.getRowNum(); // 获得行号从0开始
Iterator<Cell> cells = row.cellIterator(); // 获得第一行的迭代器
if (i > 0) {
CruiseRoom vo = new CruiseRoom();
while (cells.hasNext()) {
Cell cell = cells.next();
m = cell.getColumnIndex();// 获得列号从0开始
switch (cell.getCellType()) {
// 根据cell中的类型来输出数据
case HSSFCell.CELL_TYPE_NUMERIC:
info = (int)cell.getNumericCellValue()+"";
break;
case HSSFCell.CELL_TYPE_STRING:
info = cell.getStringCellValue() + "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
info = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA:
info = cell.getCellFormula() + "";
break;
default:
info = "";
break;
}
switch (m) {
case 0:
/*Set set=typeMap.entrySet();
Iterator it=set.iterator();
while(it.hasNext()) {
Map.Entry entry=(Map.Entry)it.next();
if(entry.getValue().equals(info.trim())) {
vo.setType(info.trim());
}
}*/
vo.setName(info.trim());
break;
case 1:
Set set=typeMap.entrySet();
Iterator it=set.iterator();
while(it.hasNext()) {
Map.Entry entry=(Map.Entry)it.next();
if(entry.getValue().equals(info.trim())) {
vo.setType(info.trim());
}
}
break;
case 2:
vo.setWindows(info.trim());
break;
case 3:
vo.setArea(info.trim());
break;
/* case 4:
vo.setMinGuestNum(Integer.parseInt(info.trim()));
break;*/
case 4:
vo.setMaxGuestNum(Integer.parseInt(info.trim()));
break;
case 5:
vo.setFloor(info.trim());
break;
case 6:
vo.setFacility(info.trim());
break;
case 7:
vo.setStatus((DataStatusType.AVAILABLY.getValue()));
break;
case 8:
vo.setRemark(info.trim());
break;
default:
break;
}
}
list.add(vo);
}
}
ajaxResult.setContent(list);
} catch (BusinessException e) {
ajaxResult.setSuccess(false);
ajaxResult.setMessage(e.getMessage());
logger.error("批量导入出错: "+e.getMessage(), e);
} catch (IOException e) {
ajaxResult.setSuccess(false);
ajaxResult.setMessage(e.getMessage());
logger.error("批量导入出错: "+e.getMessage(), e);
}
return ajaxResult;
}