首先看看前台写法:
<form action="poi/upload.do" method="post" enctype="multipart/form-data">
选择文件:<input type="file" name="productFile">
<input type="submit" value="提交">
</form>
因为后台文件上传用到了“MultipartHttpServletRequest”,所以需要配置一下项目,总共配置两处。
1. 需要在spring-mvc,的beans中添加,下面这个
<!-- 定义文件上传解析器 -->
<!-- 3、上传文件需要有此配置,不然会报强转错误: java.lang.ClassCastException: org.apache.catalina.connector.RequestFacade cannot be cast to org.springframework.web.multipart.MultipartHttpServletRequest -->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<!-- 大小单位:bytes -->
<property name="maxUploadSize">
<value>104857600</value>
</property>
<property name="maxInMemorySize">
<value>10240</value>
</property>
<property name="defaultEncoding">
<value>UTF-8</value>
</property>
</bean>
2. 然后添加一个pom依赖,不是maven项目的可以自己去下载一个jar包。
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
后台代码如下:
//EXCEl上传
@RequestMapping("/upload.do")
public void uploadExcel(MultipartHttpServletRequest request) throws Exception{
//获取MultipartHttpServletRequest上传过来的文件。
MultipartFile file=request.getFile("productFile");
//获取上传的文件名称。
String fileName=file.getOriginalFilename();
//根据名称获取文件后缀
String suffix=fileName.substring(fileName.lastIndexOf("."),fileName.length());//通过文件名获取文件格式
//新建一个excel工作簿
Workbook wk=null;
//根据文件后缀判断excel的版本, 并将传过来的文件塞到excel工作簿。03以下版本格式为.xsl, 07以上版本用.xlsx
if(suffix.equals(".xls")) {
wk=new HSSFWorkbook(file.getInputStream());
}else if(suffix.equals(".xlsx")) {
wk=new XSSFWorkbook(file.getInputStream());
}
//将excel工作簿转换为一个list集合,泛型为Project,也就是自己要插入数据库的对象
List<Product> products=poiService.readExcelData(wk);
//将list插入数据库当中
for(Product p:products){
System.out.println("待插入的数据: {} "+p);
int a=userDao.insertSelective(p);
}
}
其中需要主要的是readExcelData(wk),细节如下:
public static List<Product> readExcelData(Workbook wb) throws Exception{
Product product=null;
List<Product> products=new ArrayList<Product>();
Row row=null;
//获取有多少sheet页
int numSheet=wb.getNumberOfSheets();
if (numSheet>0) {
for(int i=0;i<numSheet;i++){
//getSheetAt 获取索引
Sheet sheet=wb.getSheetAt(i);
int numRow=sheet.getLastRowNum();
if (numRow>0) {
for(int j=1;j<=numRow;j++){
//TODO:跳过excel sheet表格头部
row=sheet.getRow(j);
product=new Product();
String name=ExcelUtil.manageCell(row.getCell(1), null);
String unit=ExcelUtil.manageCell(row.getCell(2), null);
Double price=Double.valueOf(ExcelUtil.manageCell(row.getCell(3), null));
String stock=ExcelUtil.manageCell(row.getCell(4), null);
String value=ExcelUtil.manageCell(row.getCell(5), "yyyy-MM-dd");
String remark=ExcelUtil.manageCell(row.getCell(6), null);
product.setName(name);
product.setUnit(unit);
product.setPrice(price);
product.setStock(Double.valueOf(stock));
System.out.println("value:"+value);
product.setPurchaseDate(DateUtil.strToDate(value, "yyyy-MM-dd"));
product.setRemark(remark);
products.add(product);
}
}
}
}
return products;
}
dao层如下:
int insertSelective(Product record);
Mapper层如下:
<insert id="insertSelective" parameterType="cn.temptation.domain.Product" >
insert into product
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="name != null" >
name,
</if>
<if test="unit != null" >
unit,
</if>
<if test="price != null" >
price,
</if>
<if test="stock != null" >
stock,
</if>
<if test="remark != null" >
remark,
</if>
<if test="purchaseDate != null" >
purchase_date,
</if>
<if test="isDelete != null" >
is_delete,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=INTEGER},
</if>
<if test="name != null" >
#{name,jdbcType=VARCHAR},
</if>
<if test="unit != null" >
#{unit,jdbcType=VARCHAR},
</if>
<if test="price != null" >
#{price,jdbcType=DOUBLE},
</if>
<if test="stock != null" >
#{stock,jdbcType=INTEGER},
</if>
<if test="remark != null" >
#{remark,jdbcType=VARCHAR},
</if>
<if test="purchaseDate != null" >
#{purchaseDate,jdbcType=DATE},
</if>
<if test="isDelete != null" >
#{isDelete,jdbcType=INTEGER},
</if>
</trim>
</insert>