本次项目实践基于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