1、maven环境下,pom.xml的配置:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
2、Spring配置文件spring-servlet.xml:
<!-- 支持上传文件 -->
<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="defaultEncoding" value="utf-8"></property>
<property name="maxUploadSize" value="10485760000"></property>
<property name="maxInMemorySize" value="40960"></property>
</bean>
3、ExcelUtil
package com.hyc.www.utils;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil {
public static List<String> getDataByExcel(InputStream is) throws IOException {
List<String> result = new ArrayList<String>();
//创建Excel,读取文件内容
XSSFWorkbook workbook = null;
//获取第一个工作表
XSSFSheet sheet = null;
try {
workbook = new XSSFWorkbook(is);
//获取第一个工作表
sheet = workbook.getSheetAt(0);
//获取sheet中第一行行号
int firstRowNum = sheet.getFirstRowNum();
//获取sheet中最后一行行号
int lastRowNum = sheet.getLastRowNum();
//循环插入数据
for(int i=firstRowNum+1;i<=lastRowNum;i++){
XSSFRow row = sheet.getRow(i);
XSSFCell goodsNumCell = row.getCell(0);//
if(goodsNumCell!=null){
String goodsNum = goodsNumCell.getStringCellValue();
System.out.println("-------------------goodsNum:"+goodsNum);
result.add(goodsNum);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if(workbook!=null) {
workbook.close();
}
}
return result;
}
}
4、Controller:
@RequestMapping(value="/uploadExcel",method=RequestMethod.POST,produces = "text/html;charset=UTF-8")
@ResponseBody
public String uploadExcel(@RequestParam("file") CommonsMultipartFile file,HttpServletRequest request) {
String inId = request.getParameter("inId");
System.out.println("-------------------inId:"+inId);
String skuCode = request.getParameter("skuCode");
System.out.println("-------------------skuCode:"+skuCode);
File fo = new File(file.getOriginalFilename());
try {
FileUtils.copyInputStreamToFile(file.getInputStream(),fo);
List<String> result = ExcelUtil.getDataByExcel(FileUtils.openInputStream(fo));
return "success";
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return "fail";
}
}
5、前端页面:
使用jquery.form.js 插件异步上传文件:
<form id="formDemo">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal"><span aria-hidden="true">×</span><span class="sr-only">Close</span></button>
<h4 class="modal-title">导入产品序列号</h4>
</div>
<div class="modal-body">
<div class="row">
<div class="form-group" style="text-align: center;">
<label title="选择文件" for="inputImage" class="btn btn-primary" >
<input type="file" accept="excel/*" name="file" id="inputImage" >
</label>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-white" data-dismiss="modal">关闭</button>
<button type="button" class="btn btn-primary" id="importBtnSave">上传</button>
</div>
</form>
//上传导入文件
$("#importBtnSave").click(function(){
let selectedRows = $("#editStockInDetailTab").bootstrapTable('getSelections');
if (selectedRows.length <= 0) {
alert('请选择要导入的数据');
return;
}
if (selectedRows.length > 1) {
alert('一次只能选择一行导入');
return;
}
let skuCode = selectedRows[0].skuCode;
let inId = $("#editInId").val();
$("#formDemo").ajaxSubmit({
url: baseUrl + '/Inventory/repair/StockIn/uploadExcel?inId='+inId+'&skuCode='+skuCode,
type: "post", /*设置表单以post方法提交*/
dataType: "json", /*设置返回值类型为文本*/
success: function (data) {
alert(data);
console.info(data);
},
error: function (error) {
alert(error);
console.info(error);
}
});
});