java jxl 筛选_java利用jxl实现Excel导入功能

本次项目实践基于Spring+SpringMvc+MyBatis框架，简单实现了Excel模板导出、和Excel批量导入的功能。实现过程如下：、

1、maven导入所需jar包

net.sourceforge.jexcelapi

jxl

2.6.12

2、创建Excel导出模板

importjava.io.OutputStream;importjava.util.Map;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.springframework.web.servlet.view.document.AbstractExcelView;public class CommodityTypeImportModelExcel extendsAbstractExcelView {

@Overrideprotected void buildExcelDocument(Mapmap, HSSFWorkbook workBook, HttpServletRequest request,

HttpServletResponse response)throwsException {

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)publicModelAndView testTypeExcelExport(HttpServletRequest request, ModelMap model) {

CommodityTypeImportModelExcel commodityTypeImportModelExcel= newCommodityTypeImportModelExcel();return newModelAndView(commodityTypeImportModelExcel);

}

4、在Html添加模板导出控件

商品类型字典模板下载

$(document).ready(function(){

$("#downloadTypeModel").on("click",function(){

location.href="${pageContext.request.contextPath}/shop/testTypeExcelExport";

});

});

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

*@paramfile

*@return

*/

public ListgetAllTypeByExcel(MultipartFile file){

List list=new ArrayList();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= newCommodityTypeDicEntity();

commodityTypeDicEntity.setTypeName(typeName);

commodityTypeDicEntity.setpName(pName);

commodityTypeDicEntity.setLevel(level);

commodityTypeDicEntity.setCreateTime(newDate());

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);

}

}returnlist;

}

7、Dao层添加批量插入方法及清空数据方法

/*** 批量插入商品类型

*@paramlistInsert

*@return

*/

int createCommodityTypeDicList(ListlistInsert);/*** 清空商品类型

*@return

*/

int deleteCommodityTypeDic();

对应Mapping中：

INSERT INTO shell_commodity_type_dictype_name,p_name,level,create_time,VALUES

#{item.typeName,jdbcType=VARCHAR},

#{item.pName,jdbcType=VARCHAR},

#{item.level,jdbcType=VARCHAR},

#{item.createTime,jdbcType=TIMESTAMP},

delete from shell_commodity_type_dic

8、Server层加入Excel导入及数据清空

/***

*@paramcommodityDics

*@return

*/

public Map saveCommodityTypeDic(ListcommodityTypeDics) {//清空商品类型

commodityDao.deleteCommodityTypeDic();

Map resultMap = new HashMap();int count = 0;

count=commodityDao.createCommodityTypeDicList(commodityTypeDics);if(count ==commodityTypeDics.size()){

resultMap.put("resultCode", "0");

resultMap.put("resultMsg", "成功导入"+count+"条数据!");returnresultMap;

}else{

resultMap.put("resultCode", "103");

resultMap.put("resultMsg", "导入失败，请检查导入数据是否正确。");returnresultMap;

}

}

9、controller端实现Excel批量导入

@RequestMapping(value = "/testTypeExcelImport", method =RequestMethod.POST)

@ResponseBodypublic Map testTypeExcelImport(HttpServletRequest request, ModelMap model) throwsException {

Map resultMap = new HashMap();

MultipartHttpServletRequest multipartRequest=(MultipartHttpServletRequest) request;

MultipartFile file= multipartRequest.getFile("commodityTypeExcel");if(file.isEmpty()){

resultMap.put("resultCode", "101");

resultMap.put("resultMsg", "导入失败，文件为空，请检查。");returnresultMap;

}

List commodityTypeDics =commodityService.getAllTypeByExcel(file);if(commodityTypeDics.size()==0){

resultMap.put("resultCode", "102");

resultMap.put("resultMsg", "导入失败，数据不存在，请检查导入信息。");returnresultMap;

}

resultMap=commodityService.saveCommodityTypeDic(commodityTypeDics);returnresultMap;

}

10、Html添加批量导入控件

商品类型字典批量导入

对应Js部分

$(document).ready(function(){//使用 jQuery异步提交表单

$('#commodityTypeExcelSubmit').click(function() {varfile=$("#commodityTypeExcel").val();if(file== "") {

alert("请选择要上传的文件");return false;

}else{//检验文件类型是否正确

varexec=(/[.]/.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;

});

});functionuploadType(){

$("#commodityTypeExcelSubmit").click();

}

自此利用JXl进行批量导入的功能基本完成。

需要注意的点：在第【9】如果出现MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;报错，无法被成功转换，则需要确认：

1、首先在servlet.xml里是否进行了配置(SpringMVC封装了commons-fileupload上传组件)

2、需要检查一下form表单是否有这个属性enctype="multipart/form-data"，对比第【10】步。

3、是否以提交方式进行的导入请求，对比第【10】步中的js部分。

$('#uploadCommodityTypeExcel').ajaxSubmit，这个比较关键。