最近公司要求做一个导入excel文件到数据库的功能,就参照网上的博客做了一个小例子.
首先准备poi-3.17.jar,poi-ooxml-3.17.jar,poi-ooxml-schemas-3.17.jar
前端代码modal.jsp:
我是用的bootstrap插件(modal对话框)
<div class="modal" id="import">
<div class="modal-dialog">
<div class="modal-content">
<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">
<form class="form-horizontal" role="form" id="import">
<div class="form-group">
<label class="col-sm-2 control-label">文件:</label>
<div class="col-sm-5">
<input type="file" name="uploadFile" id="file"/>
</div>
<div class="col-sm-3">
<button type="button" class="btn btn-primary" id="importBtn" >导入</button>
</div>
</div>
<div class="form-group text-center tips">
<p class="message error"></p>
</div>
</form>
</div>
</div>
</div>
</div>
js文件
$("#importBtn").off('click').on('click', function (e) {
var fd = new FormData();
fd.append('uploadFile', $('#file')[0].files[0]);
var fileExtend=$('#file').val().substring($('#file').val().lastIndexOf('.')).toLowerCase();
if(fileExtend !=".xls"){
$("#import").find(".tips").html('<p class="error" style="color:red;">仅支持(.xls)类型的文件</p>');
return ;
}
$.ajax({
type: 'post',
url: 'thresholdManage/import',
data: fd,
dataType:'json',
processData: false,
contentType: false,
success: function (data) {
if(data.success){
$("#import").find(".tips").html('<p class="error" style="color:red;">导入成功!!</p>');
}else{
$("#import").find(".tips").html('<p class="error" style="color:red;">导入失败!!</p>');
}
},
error: function (xhr) {
console.log(xhr);
}
});
});
后台代码:
controller直接调用接口比较简单省略
接口service
public List<TDimThreshold> importFile(MultipartFile mFile, String rootPath) {
// TODO Auto-generated method stub
List<TDimThreshold> thresholdList = new ArrayList<TDimThreshold>();
String fileName = mFile.getOriginalFilename();
String suffix = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
String ym = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
String filePath = "files/" + ym + fileName;
try {
File file = new File(rootPath + filePath);
if (file.exists()) {
file.delete();
file.mkdirs();
}else {
file.mkdirs();
}
mFile.transferTo(file);
if ("xls".equals(suffix) || "XLS".equals(suffix)) {
thresholdList = importXls(file);
m_thresholdDao.importFile(thresholdList);
}else {
return null;
}
} catch (Exception e) {
e.printStackTrace();
}
return thresholdList;
}
private List<TDimThreshold> importXls(File file) {
List<TDimThreshold> thresholdList = new ArrayList<TDimThreshold>();InputStream is = null;
HSSFWorkbook hWorkbook = null;
try {
is = new FileInputStream(file);
hWorkbook = new HSSFWorkbook(is);
HSSFSheet hSheet = hWorkbook.getSheetAt(0);
if (null != hSheet){
for (int i = 1; i < hSheet.getPhysicalNumberOfRows(); i++){
TDimThreshold threshold = new TDimThreshold();
HSSFRow hRow = hSheet.getRow(i);
threshold.setFACTOR_ITEM_ID(new Double(hRow.getCell(0).toString()).shortValue());
threshold.setHIGHT_LEVEL1(Float.parseFloat(hRow.getCell(1).toString()));
threshold.setHIGHT_LEVEL2(Float.parseFloat(hRow.getCell(2).toString()));
threshold.setHIGHT_LEVEL3(Float.parseFloat(hRow.getCell(3).toString()));
threshold.setLOW_LEVEL1(Float.parseFloat(hRow.getCell(4).toString()));
threshold.setLOW_LEVEL2(Float.parseFloat(hRow.getCell(5).toString()));
threshold.setLOW_LEVEL3(Float.parseFloat(hRow.getCell(6).toString()));
threshold.setSCHEME_ID(new Double(hRow.getCell(7).toString()).shortValue());
threshold.setSENSOR_ID((int)Float.parseFloat(hRow.getCell(8).toString()));
thresholdList.add(threshold);
}
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if (null != is) {
try {
is.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (null != hWorkbook) {
try {
hWorkbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return thresholdList;
}
dao层就是批量添加数据,比较简单就不写了
这样我们就完成了一个简单的导入excel文件的例子了
转载博客:http://www.cnblogs.com/littlecharacter/p/5580676.html
如有不足请指正!