案例使用的框架：jsp+spring+mybaties
<form id="importForm" name="importForm" method="post" target="win" action="importExcel.ht" enctype="multipart/form-data">
<div class="row">
<table id="tableid" class="table-detail" cellpadding="0" cellspacing="0" border="0">
<tr>
<th width="22%">选择文件：</th>
<td width="78%"><input type="file" size="40" name="xmlFile" id="xmlFile"/></td>
</tr>
</table>
</div>
<input type="hidden" name="rq" value="${rq}">
</form>
后台代码
@RequestMapping("importExcel")
@Action(description="导入Excel弹窗")
public void importExcel(MultipartHttpServletRequest request,HttpServletResponse response) throws Exception
{
String rq=request.getParameter("rq");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
MultipartFile fileLoad = request.getFile("xmlFile");
ResultMessage resultMessage = null;
try {
String fileType = fileLoad.getOriginalFilename().substring(fileLoad.getOriginalFilename().lastIndexOf(".") + 1, fileLoad.getOriginalFilename().length());
Workbook wb = null;
if (fileType.equals("xls")) {
wb = new HSSFWorkbook(fileLoad.getInputStream());
} else if (fileType.equals("xlsx")) {
wb = new XSSFWorkbook(fileLoad.getInputStream());
} else {
throw new Exception("读取的不是excel文件");
}
//读取了模板内所有sheet内容
HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);
HSSFCell cell0 = null;
HSSFCell cell1 = null;
Map<String, String> map = new HashMap<String, String>();
//读取excel，处理自己的业务
StringBuffer buf= new StringBuffer();
for(int i=2;i<=97;i++){
cell0 = sheet.getRow(i).getCell(0);
cell1 = sheet.getRow(i).getCell(1);
String key = cell0.getStringCellValue().replace(":", "");
String val = cell1.getStringCellValue();
if(key.startsWith("0")){
key = key.substring(1,key.length());
}
key = "v"+key;
map.put(key, val);
}
String value = map.toString().replace("=", ":");
LoadDate ld = new LoadDate();
ld.setValue(value);
ld.setRq(sdf.parse(rq));
ld.setSbzt("未上报");
for(int i=98;i<=102;i++){
cell0 = sheet.getRow(i).getCell(0);
cell1 = sheet.getRow(i).getCell(1);
String key = cell0.getStringCellValue();
String val = cell1.getStringCellValue();
if("高峰电量".equals(key)){
ld.setYcgfdl(Long.parseLong(val));
}else if("全天电量".equals(key)){
ld.setYcqdl(Long.parseLong(val));
}else if("错峰电力".equals(key)){
ld.setYccfdLi(Long.parseLong(val));
}else if("错峰电量".equals(key)){
ld.setYccfdLl(Long.parseLong(val));
}else if("低谷电量".equals(key)){
ld.setYcdgdl(Long.parseLong(val));
}
}
Long id = UniqueIdUtil.genId();
ld.setId(id);
ld.setSbzt("未上报");
loadDateService.add(ld);
String result="导入数据成功";
if(result.contains("成功")){
resultMessage = new ResultMessage(ResultMessage.Success, "导入成功!");
}else{
resultMessage = new ResultMessage(ResultMessage.Fail, result);
}
writeResultMessage(response.getWriter(), resultMessage);
} catch (Exception ex) {
ex.printStackTrace();
String str = MessageUtil.getMessage();
ex.getLocalizedMessage();
if (StringUtil.isNotEmpty(str)) {
resultMessage = new ResultMessage(ResultMessage.Fail,"导入失败请检查导入的Excel是否正确:" + str);
response.getWriter().print(resultMessage);
} else {
String message = ExceptionUtil.getExceptionMessage(ex);
resultMessage = new ResultMessage(ResultMessage.Fail, "导入失败请检查导入的Excel是否正确");
response.getWriter().print(resultMessage);
}
}
}
个人经验总结
导入报版本不匹配问题
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
MultipartFile fileLoad = request.getFile("xmlFile");
try {
String fileType = fileLoad.getOriginalFilename().substring(
fileLoad.getOriginalFilename().lastIndexOf(".") + 1,
fileLoad.getOriginalFilename().length());
Workbook wb = null;
if (fileType.equals("xls")) {
try {
//07+版本
wb = new HSSFWorkbook(fileLoad.getInputStream());
} catch (Exception e) {
//03版
wb = new XSSFWorkbook(fileLoad.getInputStream());
}
} else if (fileType.equals("xlsx")) {
wb = new XSSFWorkbook(fileLoad.getInputStream());
} else {
throw new Exception("读取的不是excel文件");
}