java利用jxl实现Excel导入功能
本次项目实践基于Spring+SpringMvc+MyBatis框架，简单实现了Excel模板导出、和Excel批量导入的功能。实现过程如下：、
1、maven导入所需jar包
<dependency> <groupId>net.sourceforge.jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6.12</version> </dependency>
2、创建Excel导出模板
import java.io.OutputStream; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.springframework.web.servlet.view.document.AbstractExcelView; public class CommodityTypeImportModelExcel extends AbstractExcelView { @Override protected void buildExcelDocument(Map<String, Object> map, HSSFWorkbook workBook, HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFSheet sheet = workBook.createSheet("Sheet1"); sheet.setDefaultColumnWidth(20); HSSFCell cell00 = getCell(sheet, 0, 0); setText(cell00, "商品类型名称"); HSSFCell cell01 = getCell(sheet, 0, 1); setText(cell01, "上级类型"); HSSFCell cell02 = getCell(sheet, 0, 2); setText(cell02, "级别"); String filename = new String("商品类型模板.xls".getBytes("utf-8"), "iso8859-1"); response.setHeader("Content-Type", "application/octet-stream"); response.setHeader("Content-disposition", "attachment;filename=" + filename); OutputStream ouputStream = response.getOutputStream(); workBook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } }
3、controller端实现模板导出
@RequestMapping(value = "/testTypeExcelExport", method = RequestMethod.GET) public ModelAndView testTypeExcelExport(HttpServletRequest request, ModelMap model) { CommodityTypeImportModelExcel commodityTypeImportModelExcel = new CommodityTypeImportModelExcel(); return new ModelAndView(commodityTypeImportModelExcel); }
4、在Html添加模板导出控件
<a id="downloadTypeModel">商品类型字典模板下载</a> <script type="text/javascript"> $(document).ready(function(){ $("#downloadTypeModel").on("click",function(){ location.href ="${pageContext.request.contextPath}/shop/testTypeExcelExport"; }); }); </script>
5、创建需要导入Excel的实体
import java.util.Date;
public class CommodityTypeDicEntity {
private Integer id;
private String typeName;
private String pName;
private String level;
private Date createTime;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTypeName() {
return typeName;
}
public void setTypeName(String typeName) {
this.typeName = typeName;
}
public String getpName() {
return pName;
}
public void setpName(String pName) {
this.pName = pName;
}
public String getLevel() {
return level;
}
public void setLevel(String level) {
this.level = level;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
}
6、Server层利用JXL获取Excel是数据并将数据添加到List中，返回
import jxl.Sheet;
import jxl.Workbook;
/** * 利用JXL获取Excel数据并将数据添加到List * @param file * @return */ public List<CommodityTypeDicEntity> getAllTypeByExcel(MultipartFile file){ List<CommodityTypeDicEntity> list=new ArrayList<CommodityTypeDicEntity>(); try { Workbook rwb=Workbook.getWorkbook(file.getInputStream()); Sheet rs=rwb.getSheet("Sheet1");//或者rwb.getSheet(0) //校验 if(null==rs){ //throw new Exception("表格不存在！"); return null; } int clos=rs.getColumns();//得到所有的列 int rows=rs.getRows();//得到所有的行 for (int i = 1; i < rows; i++) { for (int j = 0; j < clos; j++) { //第一个是列数，第二个是行数 String typeName=rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++ String pName=rs.getCell(j++, i).getContents(); String level=rs.getCell(j++, i).getContents(); CommodityTypeDicEntity commodityTypeDicEntity = new CommodityTypeDicEntity(); commodityTypeDicEntity.setTypeName(typeName); commodityTypeDicEntity.setpName(pName); commodityTypeDicEntity.setLevel(level); commodityTypeDicEntity.setCreateTime(new Date()); list.add(commodityTypeDicEntity); } } } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); logger.error("解析Excel出错",e); } finally { try { file.getInputStream().close(); } catch (IOException e) { // TODO Auto-generated catch block //e.printStackTrace(); logger.error("文件流关闭出错",e); } } return list; }
7、Dao层添加批量插入方法及清空数据方法
/** * 批量插入商品类型 * @param listInsert * @return */ int createCommodityTypeDicList(List<CommodityTypeDicEntity> listInsert); /** * 清空商品类型 * @return */ int deleteCommodityTypeDic();
对应Mapping中：
<insert id="createCommodityTypeDicList" parameterType="java.util.List"> INSERT INTO shell_commodity_type_dic <trim prefix="(" suffix=")" suffixOverrides=","> type_name,p_name,level,create_time, </trim> VALUES <foreach collection="list" item="item" index="index" separator=","> <trim prefix="(" suffix=")" suffixOverrides=","> #{item.typeName,jdbcType=VARCHAR}, #{item.pName,jdbcType=VARCHAR}, #{item.level,jdbcType=VARCHAR}, #{item.createTime,jdbcType=TIMESTAMP}, </trim> </foreach> </insert> <delete id="deleteCommodityTypeDic" parameterType="int"> delete from shell_commodity_type_dic </delete>
8、Server层加入Excel导入及数据清空
/** * * @param commodityDics * @return */ public Map<String, Object> saveCommodityTypeDic(List<CommodityTypeDicEntity> commodityTypeDics) { //清空商品类型 commodityDao.deleteCommodityTypeDic(); Map<String, Object> resultMap = new HashMap<String, Object>(); int count = 0; count = commodityDao.createCommodityTypeDicList(commodityTypeDics); if(count == commodityTypeDics.size()){ resultMap.put("resultCode", "0"); resultMap.put("resultMsg", "成功导入"+count+"条数据!"); return resultMap; }else{ resultMap.put("resultCode", "103"); resultMap.put("resultMsg", "导入失败，请检查导入数据是否正确。"); return resultMap; } }
9、controller端实现Excel批量导入
@RequestMapping(value = "/testTypeExcelImport", method = RequestMethod.POST) @ResponseBody public Map<String, Object> testTypeExcelImport(HttpServletRequest request, ModelMap model) throws Exception { Map<String, Object> resultMap = new HashMap<String, Object>(); MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; MultipartFile file = multipartRequest.getFile("commodityTypeExcel"); if(file.isEmpty()){ resultMap.put("resultCode", "101"); resultMap.put("resultMsg", "导入失败，文件为空，请检查。"); return resultMap; } List<CommodityTypeDicEntity> commodityTypeDics = commodityService.getAllTypeByExcel(file); if(commodityTypeDics.size()==0){ resultMap.put("resultCode", "102"); resultMap.put("resultMsg", "导入失败，数据不存在，请检查导入信息。"); return resultMap; } resultMap = commodityService.saveCommodityTypeDic(commodityTypeDics); return resultMap; }
10、Html添加批量导入控件
<a id="batchTypeImport">商品类型字典批量导入</a> <form id="uploadCommodityTypeExcel" style="display:none" name="uploadCommodityTypeExcel" method="post" enctype="multipart/form-data" > <input type="file" style="display:none" name="commodityTypeExcel" id="commodityTypeExcel" onChange="uploadType()"/> <input type="submit" style="display:none" id="commodityTypeExcelSubmit"> </form>
对应Js部分
<script type="text/javascript"> $(document).ready(function(){ // 使用 jQuery异步提交表单 $('#commodityTypeExcelSubmit').click(function() { var file = $("#commodityTypeExcel").val(); if (file == "") { alert("请选择要上传的文件"); return false; } else { //检验文件类型是否正确 var exec = (/[.]/.exec(file)) ? /[^.]+$/.exec(file.toLowerCase()) : ''; if (exec != "xls") { alert("文件格式不对，请上传Excel文件!(扩展名xls)"); return false; } } $('#uploadCommodityTypeExcel').ajaxSubmit({ url:'${pageContext.request.contextPath}/shop/testTypeExcel', data:$('#uploadCommodityTypeExcel').serialize(), type:"POST", beforeSend:function() { ajaxbg.show(); }, success:function(msg) { ajaxbg.hide(); alert(msg.resultMsg); }, error:function(){ ajaxbg.hide(); alert("导入失败!"); } }); return false; }); }); function uploadType(){ $("#commodityTypeExcelSubmit").click(); }
</script>
自此利用JXl进行批量导入的功能基本完成。
需要注意的点：在第【9】如果出现MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;报错，无法被成功转换，则需要确认：
1、首先在servlet.xml里是否进行了配置（SpringMVC封装了commons-fileupload上传组件）
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> <property name="maxUploadSize" value="31457280" /> <property name="maxInMemorySize" value="4096" /> </bean>
2、需要检查一下form表单是否有这个属性enctype="multipart/form-data"，对比第【10】步。
3、是否以提交方式进行的导入请求，对比第【10】步中的js部分。
$('#uploadCommodityTypeExcel').ajaxSubmit，这个比较关键。
页面在批量导入时需要在页面上出现“正在导入。。。”遮罩效果的，请参考：http://www.cnblogs.com/conswin/p/7251341.html
