一.jsp页面
<!-- 文件导入窗口 -->
<div id="implFileWin" class="easyui-window" title="文件导入" data-options="modal:true,closed:true,iconCls:'icon-save'"
style="width: 400px; height: 200px; padding: 5px;">
<form method="POST" enctype="multipart/form-data" id="fileForm" action="<%=basePath%>bomManager/ajaxUpload">
<table cellspacing="1" cellpadding="0" class="tb_search">
<tr>
<td class="td_title">上传文件: </td>
<td> <input id="upfile" type="file" name="upfile"></td>
</tr>
<tr>
<td>
</td>
<td>
<input type="button" value="上传" id="btn" name="btn" >
</td>
</tr>
</table>
</form>
</div>
<script type="text/javascript">
//提交按钮点击触发事件
$('#btn').click(function(){
if(checkData()){
//from表单提交
$('#fileForm').form({
url : '<%=basePath%>bomManager/ajaxUpload',
success:function(param){
if((typeof param) == 'string'){
var data = $.parseJSON(param); //转json字符串为对象
if(data.message.success){
// $('#insertBomForm').form('load',data.bomExcel); //直接给form对象值
$('#insertBomForm').form('load',{
bomGroup: data.bomExcel.bomGroup,
summarize: data.bomExcel.summarize,
materialCode: data.bomExcel.mainMaterialCode,
mainBom: data.bomExcel.mainBom,
replaceCode: data.bomExcel.replaceCode,
count: data.bomExcel.count,
remark1: data.bomExcel.mainRemark,
isDie: data.bomExcel.isDie,
inventoryOrgInput: data.bomExcel.inventoryOrg,
materialCode_0: data.bomExcel.materialCode,
dosage_0: data.bomExcel.dosage,
isMainWafer_0: data.bomExcel.isMainWafer,
isReverse_0: data.bomExcel.isReverse,
reverseWarehouse_0: data.bomExcel.reverseWarehouse,
reversePosition_0: data.bomExcel.reversePosition,
remark_0: data.bomExcel.remark,
existRelationship_0:data.bomExcel.existRelationship
});
$.messager.alert('提示信息',data.message.message,'info');
}else if(data.message.success){
$.messager.alert('提示信息',data.message.message,'error');
}
}
}
});
//form提交
$('#fileForm').submit();
//关闭窗口
$('#implFileWin').window('close');
}
});
//JS校验form表单信息
function checkData(){
var fileDir = $("#upfile").val();
var suffix = fileDir.substr(fileDir.lastIndexOf("."));
if("" == fileDir){
$.messager.alert('提示信息','选择需要导入的Excel文件!','info');
return false;
}
if(".xls" != suffix && ".xlsx" != suffix ){
$.messager.alert('提示信息','选择Excel格式的文件导入!','info');
return false;
}
return true;
}
</script>
二、action层
//excel导入数据
@RequestMapping("/ajaxUpload")
@ResponseBody
public Map<String,Object> ajaxUpload(HttpServletRequest request,HttpServletResponse response,MultipartFile sourceFile,HttpSession httpSession){
Map<String,Object> map = new HashMap<String,Object>();
WSMessage message = new WSMessage();
try {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
InputStream in =null;
MultipartFile file = multipartRequest.getFile("upfile");
String fileName = file.getOriginalFilename();
String suffix = fileName.substring(fileName.lastIndexOf("."));
if(file.isEmpty()){
throw new Exception("文件不存在!");
}
in = file.getInputStream();
String filePath = request.getSession().getServletContext().getRealPath("/");
BOMMaterialExcel bomExcel = bomRequisitionService.readExcel(in,filePath, file.getOriginalFilename(), 1, 0, 0,suffix); //执行读EXCEL操作,读出的数据导入List 2:从第3行开始;0:从第A列开始;0:第0个sheet
map.put("bomExcel", bomExcel);
message.setMessage("Excel导入成功!");
message.setSuccess(true);
map.put("message", message);
} catch (Exception e) {
message.setMessage("Excel导入失败!");
message.setSuccess(true);
map.put("message", message);
e.printStackTrace();
};
return map;
}
三、service层
/**
* @param filepath //文件路径
* @param filename //文件名
* @param startrow //开始行号
* @param startcol //开始列号
* @param sheetnum //sheet
* @return list
*/
@Override
public BOMMaterialExcel readExcel(InputStream fi,String filepath, String filename, int startrow, int startcol, int sheetnum,String suffix) {
BOMMaterialExcel bomExcel = new BOMMaterialExcel();
try {
HSSFWorkbook wb = null;
//判断是07版本还是03版本
if(".xls".equals(suffix) ){
wb = new HSSFWorkbook(fi);
}else{
wb = new XSSFWorkbook(fi);
}
BOMMaterialExcel bomExcel = new BOMMaterialExcel();
HSSFSheet sheet = wb.getSheetAt(sheetnum); //sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号
for (int i = startrow; i < rowNum; i++) { //行循环开始
HSSFRow row = sheet.getRow(i); //行
bomExcel.setBomGroup(getExcelValue(row,0));
bomExcel.setSummarize(getExcelValue(row,1));
bomExcel.setMainMaterialCode(getExcelValue(row,2));
bomExcel.setMainBom(getExcelValue(row,3));
bomExcel.setReplaceCode(getExcelValue(row,4));
bomExcel.setCount(getExcelValue(row,5));
bomExcel.setMainRemark(getExcelValue(row,6));
bomExcel.setIsDie(getExcelValue(row,7));
bomExcel.setInventoryOrg(getExcelValue(row,8));
bomExcel.setMaterialCode(getExcelValue(row,9));
bomExcel.setDosage(getExcelValue(row,10));
bomExcel.setIsMainWafer(getExcelValue(row,11));
bomExcel.setIsReverse(getExcelValue(row,12));
bomExcel.setReverseWarehouse(getExcelValue(row,13));
bomExcel.setReversePosition(getExcelValue(row,14));
bomExcel.setRemark(getExcelValue(row,15));
bomExcel.setExistRelationship(getExcelValue(row,16));
}
} catch (Exception e) {
System.out.println(e);
}
return bomExcel;
}
/**
* 用于判断Excel字段的类型
* @param row 行号
* @param index 第几列
* @return
*/
public String getExcelValue(HSSFRow row,int index){
HSSFCell cell = row.getCell(Short.parseShort(index + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式,并对其进行转换,以便插入数据库
case 0:
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
cellValue = dataFormatter.formatCellValue(cell);
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
return cellValue;
}