1.jsp页面获取导入的文件路径
1.1
<tr>
<th class="th"><span>导入excel表格:</span></th>
<td align="left">
<input class="hidden" id="arcId" value="${archiveId}"/>
<input align="left" type="file" id="importPath" name="importPath"/>
<input type="button" value="开始导入" οnclick="_importExcel();"/><br>
</td>
</tr>
1.2
//开始导入Excel表
function _importExcel(){
var archiveId=$("#arcId").val();
//获取导入文件的格式
var importPath=$("#importPath").val();
var point = importPath.lastIndexOf(".");
var type = importPath.substr(point);
if(type==".xls"||type==".XLS"||type==".xlsx"||type==".XLSX"){
$.ajaxFileUpload({
//处理文件上传操作的服务器端地址(可以传参数,已亲测可用)
url:"${basePath}docAndInfra/importExcel.do?archiveId="+archiveId,
secureuri:false, //是否启用安全提交,默认为false
fileElementId:'importPath', //文件选择框的id属性
dataType:'json', //服务器返回的格式,可以是json或xml等
success:function(data, status){ //服务器响应成功时的处理函数
if(data.flag){
$.messager.alert("操作提示","导入完成!可以选择文件继续导入");
}else{
$.messager.alert("操作提示","导入失败!请确认Excel文件模版格式是否正确!")
}
},
error:function(data, status, e){ //服务器响应失败时的处理函数
$('#result').html('Excel导入失败,请重试!!');
}
});
}else{
$.messager.alert("操作提示","导入文件的格式错误!");
}
}
注意:获取导入文件的路径需要用到这个js文件,它是根据同名的异步处理方法ajaxFileUpload来处理路径问题
2.后台接收请求
/**
* 开始导入excel表格数据
* @param archiveId
* @param importPath
* 开始导入excel表格数据
* @param archiveId
* @param importPath
MultipartFile 是处理文件路径的类,不能单纯的使用String类型来接受文件的路径
* @return
*/
@RequestMapping("importExcel.do")
@ResponseBody
public Map<String,Object> importExcel(@RequestParam(value="archiveId",required=false)String archiveId,@RequestParam(value="importPath")MultipartFile importPath,
HttpServletRequest request, HttpServletResponse response)throws Exception{
* @return
*/
@RequestMapping("importExcel.do")
@ResponseBody
public Map<String,Object> importExcel(@RequestParam(value="archiveId",required=false)String archiveId,@RequestParam(value="importPath")MultipartFile importPath,
HttpServletRequest request, HttpServletResponse response)throws Exception{
boolean flag=docAndInfraService.importExcel(archiveId,importPath,request,response,getCurrentUser());
Map<String,Object> map=new HashMap<String,Object>();
map.put("flag", flag);
return map;
}
3.service 处理请求
3.1 处理方法
/**
* 将导入的Excel表中的数据插入到数据库中
* @param archiveId
* 插入案卷的ID
* @param importPath
* Excel表格的路径名称
* @return
*/
@Transactional
public Boolean importExcel(String archiveId,MultipartFile importPath,HttpServletRequest request,
HttpServletResponse response,CurrentUser currentUser)throws Exception{
//将Excel上传到upload文件夹下
String serverPath=importUpLoad(importPath, response, request);
boolean flag=false;
String identyNo="";
Integer retention=null;
String fcYear="";
Integer status=1;
String dirNum="";
String archiveNo="";
int index=3;
if(archiveId!=null&&!archiveId.equals("")){
DocAndInfraArchive docArchive=findArchiveById(archiveId);
identyNo=docArchive.getIdentyNo();
retention=docArchive.getRetention();
fcYear=docArchive.getFcYear();
dirNum=docArchive.getDirNum();
archiveNo=docArchive.getArchiveNo();
index=2;
}
try {
// 构造 Workbook 对象,execelFile 是传入文件路径(获得Excel工作区)
Workbook book = null;
if(serverPath.endsWith("xls") || serverPath.endsWith("XLS")){
// Excel 2003获取方法
book = new HSSFWorkbook(new FileInputStream(serverPath));
}
if(serverPath.endsWith("xlsx") || serverPath.endsWith("XLSX")){
// Excel 2007获取方法
book = new XSSFWorkbook(new FileInputStream(serverPath));
}
// 读取表格的第一个sheet页
Sheet sheet = book.getSheetAt(0);
// 定义 row、cell
Row row;
DocAndInfraInner docInner=null;
if(archiveId==null||archiveId.equals("")){
fcYear=sheet.getRow(1).getCell(1).toString();
retention=Integer.parseInt(sheet.getRow(1).getCell(5).toString());
}
// 总共有多少行,从0开始
int totalRows = sheet.getLastRowNum() ;
for (int i = index; i <= totalRows; i++) {
row = sheet.getRow(i);
// 处理空行
if(row == null){
continue ;
}
docInner=new DocAndInfraInner();
String filePath=null;
if(archiveId!=null&&!archiveId.equals("")){
docInner.setIdentyNo(identyNo);
docInner.setRetention(retention);
docInner.setArcYear(fcYear);
docInner.setArchiveId(archiveId);
filePath="fileUpLoad"+"\\"+fcYear+"\\"+retention+"\\"+dirNum+"_"+archiveNo;
}
if(archiveId==null||archiveId.equals("")){
docInner.setIdentyNo("ws");
docInner.setArcYear(fcYear);
docInner.setRetention(retention);
filePath="fileUpLoad"+"\\"+fcYear+"\\"+retention+"\\00_"+row.getCell(0).toString();
}
docInner.setFilePath(filePath);
docInner.setFondsNum("FE2");
docInner.setStatus(status);
docInner.setCreator(currentUser.getUsername());
docInner.setModifier(currentUser.getUsername());
docInner.setCreateDate(DateUtils.getNow());
docInner.setModifyDate(DateUtils.getNow());
docInner.setFileNum(row.getCell(0).toString());
docInner.setResponsibility(row.getCell(1).toString());
docInner.setDocNo(row.getCell(2).toString());
docInner.setFileName(row.getCell(3).toString());
docInner.setWrittenTime(row.getCell(4).toString());
docInner.setStartPage(row.getCell(5).toString());
docInner.setPageCount(row.getCell(6).toString());
docInner.setRemark(row.getCell(7).toString());
//确保插入卷内文书的唯一性,存在则跳过!
DocAndInfraInner doc2=findSameInner(docInner);
if(doc2!=null){
continue;
}
docAndInfraInnerDao.save(docInner);
}
flag=true;
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return flag;
}
3.2 调用的方法
/**
* 将Excel上传到项目webapp文件的upload文件夹下
* @param importPath
* @param response
* @param request
* @return
* @throws Exception
*/
@Transactional
private String importUpLoad(MultipartFile importPath,HttpServletResponse response,HttpServletRequest request)throws Exception{
Long lTime=new Date().getTime();
String filePath = request.getSession().getServletContext().getRealPath("upload");
String savePath=filePath+"/importUpload";
File file=new File(savePath);
if(file==null||!file.exists()){
file.mkdirs();
}else{
//删除超过时间的缓存文件
deleteFile(savePath, lTime);
}
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=utf-8");
FileOutputStream os = null;
InputStream in = importPath.getInputStream();
os = new FileOutputStream(filePath+"/importUpload/"+importPath.getOriginalFilename());
byte b[] = new byte[1024];
while (in.read(b) != -1) {
os.write(b);
}
os.flush();
in.close();
os.close();
return savePath+"/"+importPath.getOriginalFilename();
}
/**
* 根据目录删除文件
*
* @param path
* @param time
* @since 2014-6-24
* @author Sunm
*/
private void deleteFile(String path, long time) {
File file = new File(path);
if (file.isDirectory()) {
File[] files = file.listFiles();
if (files == null || files.length == 0) {
file.delete();
} else {
for (File f : files) {
if (f.isDirectory()) {
deleteFile(f.getPath(), time);
}
if (time != 0) {
long lastModifyTime = f.lastModified();
if (time - lastModifyTime <= 1000 * 3600) {
continue;
}
}
f.delete();
}
}
}
}