本次项目实践基于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,这个比较关键。