自己也整理出一个简单的excel导入例子,亲试可成功!建议可用于数据量较少的地方。
从文件上传,到java处理,都较为详细
1.html上传文件
<form id="uploadForm" method="post" action="importexcel.ajax" enctype="multipart/form-data">
<input id="files" name="files" type="file" style="width:200px;"/>
</form>
<button onclick="importexcel();" type="button"><font style="color:#930093;">导入excel表</font></button>
2.js实现
function importexcel(){
if(files == ""){
alert("请选择要上传的文件");
}else{
//扩展名的文件名
var files = document.getElementById("files").value;
//扩展名的文件名
var bbb=files.lastIndexOf(".");
var ccc=files.substring(bbb+1);
if(ccc=='xlsx'){
$("#uploadForm").submit();
setTimeout("fun()",1500);
}else{
alert("请选择.xlsx文件");
}
}
}
3.Java实现,最后用一个list接收即可,自行做业务处理
@RequestMapping(value = "/importexcel.ajax", method = RequestMethod.POST)
@ResponseBody
public viod importexcel(HttpServletRequest request, HttpServletResponse response){
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile multipartFile = multipartRequest.getFile("files");
InputStream is = null;
try {
is = multipartFile.getInputStream();
if(is!=null) {
Workbook wb = WorkbookFactory.create(is);
List<Dto> list = new ArrayList<Dto>();//自己定义接收数据
int rowCount = 0;
boolean temp = true;
Sheet st = wb.getSheetAt(0);
int rowNum = st.getLastRowNum(); //获取Excel最后一行索引,从零开始,所以获取到的是表中最后一行行数减一
int colNum = st.getRow(1).getLastCellNum();//获取Excel列数
for (int r = 2; r <= rowNum-2; r++) {//读取每一行,第一行为标题,从第二行开始
Row row = st.getRow(r);
HongbaoCalcItemDto h = new HongbaoCalcItemDto();
for (int l = 0; l < colNum; l++) {//读取每一行的每一列
Cell cell = row.getCell(l);
switch (l) {//0 1 2 3 自定义列数接受自己想要的数据
case 0:
h.setMemberAccount(cell.getStringCellValue());
break;
case 2:
h.setCalcDate(DateUtil.string2Date(cell.getStringCellValue(), DateUtil.FULL_FORMAT));
break;
case 3:
Double cashBonus = cell.getNumericCellValue();
h.setCashBonus(new BigDecimal(String.valueOf(cashBonus)));
break;
case 4:
Double points = cell.getNumericCellValue();
h.setPoints(points.intValue());
break;
}
}
list.add(h);
}
if(list.size()>0){
//得到数据,自行处理业务
}
}
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
}