java Excel表格导入

来源：互联网发布：亚洲人长相知乎编辑：程序博客网时间：2024/05/17 05:12

上篇文章介绍了Excel表格的导出共能，这次介绍Excel表格导入到保存。

1.我们先在页面创建一个导入按钮，我们要用form表单提交，input file选择文件

<input type="file" style="display: none;" id="import_file" name="import_file" /><a href="javascript:void(0);" id="according-user" class="btn btn-export">导入</a>

input框选用file类型，下面做一个导入的按钮（链接也行）

<form id="staffListForm" action="<c:url value='/staff/list?entId=${entId}'/>" method="post" enctype="multipart/form-data">

使用form表单提交，导入文件enctype="multipart/form-data"必不可少

下面是导入的js，需要做导入文件的判断

/*导入员工*/       $("#according-user").click(function(){$("#import_file").click();});$("#import_file").change(function(){var fileName = $("#import_file").val();if(fileName.length > 1 && fileName ) {  var ldot = fileName.lastIndexOf(".");var type = fileName.substring(ldot + 1).toLowerCase();if(type == "xls" || type == "xlsx") {var fileFrontName = fileName.substring(0,ldot);$("#fileNameDiv").html(fileName);var requestUrl = basePath+"/staff/importStaff";$("#staffListForm").ajaxSubmit({url:requestUrl,// 跳转到 actiontype: "POST",dataType: "json",success:function(data) {    if(!data.status){parent.layer.msg("导入失败！请优先检查好上传文件内容是否正确！",{icon:3},function(){parent.location.reload();});}else{parent.layer.msg("文件 "+fileFrontName + " 导入成功",{icon:1},function(){parent.location.reload();});  }},error:function() {    parent.layer.msg("导入操作异常！",{icon:5},function(){parent.location.reload();});}  });} else {this.value="";layer.msg("文件格式不正确");return;}}});

2.将文件导入并保存

/** * 导入员工 * @param request * @param entStaffInfo * @return */@ResponseBody@RequestMapping(value = "/importStaff", method = {RequestMethod.POST,RequestMethod.GET})public Map<String, Object> importStaff(HttpServletRequest request) {Map<String, Object> result = new HashMap<String, Object>();try {MultipartHttpServletRequest multipartRequest=(MultipartHttpServletRequest)request;        MultipartFile importFile = multipartRequest.getFile("import_file");    ExcelFile ef =new ExcelFile();    List<Map<String, Object>> presentList = null;    if(importFile.getOriginalFilename().toLowerCase().endsWith(".xls")){    presentList = ef.readWithXls(importFile.getInputStream(), 0, 2, new String[]{"name","gender","cellphone", "email","entName","departName","positionName"});     }else{     presentList = ef.readWithXlsx(importFile.getInputStream(), 0, 2, new String[]{"name","gender","cellphone", "email","entName","departName","positionName"});     }    EntStaffInfo entStaffInfo = null;    for(Map<String, Object> map : presentList) {    Map<String,Object> properties = new HashMap<String, Object>();    result.put("status", flag);    }} catch (IOException e) {e.printStackTrace();}return result;}

跟上一篇文章一样，我们需要导入poi的jar包

读取上传的文件需要spring-web-3.2.9.RELEASE.jar

MultipartHttpServletRequest multipartRequest=(MultipartHttpServletRequest)request;
MultipartFile importFile = multipartRequest.getFile("import_file");

配置上传spring-servlet.xml

<!-- 上传文件解释器 --><bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"><property name="defaultEncoding" value="utf-8" /><property name="maxUploadSize" value="10485760" /><property name="maxInMemorySize" value="4096" /><property name="resolveLazily" value="true" /></bean>

读取Excel方法

/** * 读取Excel文件 *  * @param fileStream Excel文件流 * @param sheetIndex Sheet编号(起始为0) * @param titleRow 标题列的高度(如果为0，则表明没有标题行) * @param columns 列名 *  * @return 读取后的Excel数据 */public List<Map<String, Object>> readWithXlsx(InputStream fileStream, int sheetIndex, int titleRow, String[] columns) {Workbook wb = null;try {wb = new XSSFWorkbook(fileStream);} catch (IOException e) {e.printStackTrace();}return this.read(wb, sheetIndex, titleRow, columns);}/** * 读取Excel文件 *  * @param fileStream Excel文件流 * @param sheetIndex Sheet编号(起始为0) * @param titleRow 标题列的高度(如果为0，则表明没有标题行) * @param columns 列名 *  * @return 读取后的Excel数据 */public List<Map<String, Object>> readWithXls(InputStream fileStream, int sheetIndex, int titleRow, String[] columns) {Workbook wb = null;try{wb = new HSSFWorkbook(new POIFSFileSystem(fileStream));}catch(Exception e){e.printStackTrace();}return this.read(wb, sheetIndex, titleRow, columns);}

继续补充完善。。。

0 0