jxl读取excel实现导入excel写入数据库
@RequestMapping(params = "method=import", method = RequestMethod.POST) @ResponseBody public String importConfig(HttpServletRequest request, HttpServletResponse response) throws Exception{ response.setCharacterEncoding("GBK"); String result = new String();; try{ //获取请求中的文件 MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request; MultipartFile contentFile = multipartHttpServletRequest.getFile(IMPORT_FILE_NAME); //IMPORT_FILE_NAME为前端中input的name //读取请求中的excel Workbook workbook = Workbook.getWorkbook(contentFile.getInputStream()); Sheet sheet = workbook.getSheet(0); int rows=sheet.getRows(); int columns=sheet.getColumns(); //将excel中的内容读为ConfigInfo对象 List<ConfigInfo> listConfigInfo = new ArrayList<ConfigInfo>(); for(int i=1; i<rows; i++){ ConfigInfo configInfo = new ConfigInfo(); for(int j=0; j<columns; j++){ Cell cell = sheet.getCell(j, i); String cellContent = cell.getContents(); switch (j){ case 0 : configInfo.setDataId(cellContent); case 1 : configInfo.setGroup(cellContent); case 2 : configInfo.setContent(cellContent); } } listConfigInfo.add(configInfo); } //写入数据库 int importFailedNum = 0; for(ConfigInfo configInfo:listConfigInfo){ ConfigInfo configInfoExist = configService.findConfigInfo(configInfo.getDataId(), configInfo.getGroup()); //写入数据库之前判断是否存在该条配置，并记录 if (null != configInfoExist){ importFailedNum++; }else{ try{ configService.addConfigInfo(configInfo.getDataId(), configInfo.getGroup(),configInfo.getContent()); }catch(Exception operationDatabaseException){ log.error("导入配置出错"+operationDatabaseException); return result; } } } result = String.valueOf(importFailedNum); return result; }catch(Exception exception){ log.error("导入配置出错"+exception); return result; } }
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <%@ page contentType="text/html; charset=GBK" pageEncoding="GBK"%> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=GBK" /> <title>导入配置信息</title> <script src="../../../js/jquery.js" type="text/javascript"></script> <script src="../../../js/ajaxfileupload.js" type="text/javascript"></script> <style type="text/css"> body td { color: #333; font-family: Arial, Helvetica, sans-serif; font-size: 10pt; } </style> <script type="text/javascript"> $(function () { $(":button").click(function () { if ($("#contentFile").val().length > 0) { ajaxFileUpload(); } else { alert("请选择文件"); } }) }) function ajaxFileUpload() { $.ajaxFileUpload ( { url: '/diamond-server/admin.do?method=import', //用于文件上传的服务器端请求地址 secureuri: false, //一般设置为false fileElementId: 'contentFile', //文件上传空间的id属性 <input type="file" id="file" name="file" /> dataType: 'JSON', //返回值类型 一般设置为json type:'post', success: function (data, status) //服务器成功响应处理函数 { //判断返回的值 if (data == 0) alert("导入配置成功"); else alert("错误！第"+data+"条配置导入失败，请仔细对比要导入的配置，不能与已存在的配置重复"); if (typeof (data.error) != 'undefined') { if (data.error != '') { alert(data.error); } else { alert(data.msg); } } window.location.href = "list.jsp"; }, error: function (data, status, e)//服务器响应失败处理函数 { alert(e); window.location.href = "list.jsp"; } } ) return false; } </script> </head> <body> <center><h1><strong>导入配置信息</strong></h1></center> <p align='center'> <input type="file" id="contentFile" name="contentFile" /> <input type="button" value="上传" /> </p> </body> </html>
在MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;这句代码执行强转出错。发现我的配置文件里少了对multipart解析器的配置:
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> <property name="maxUploadSize" value="104857600"/> <!-- 文件最大的大小 --> <property name="maxInMemorySize" value="4096"/> </bean>
同时需要依赖commons-fileupload-1.2.jar和commons-io-1.3.1。
