@RequestMapping("importshopexcel")
@ResponseBody
public AjaxResult importShopExcel(HttpServletRequest request,MultipartFile file) throws FileUploadException, IOException {
if (file == null) {
return new AjaxResult<String>(false, "请选择文件!");
}
String suffix = this.getSuffixByFilename(file.getOriginalFilename());
if (suffix == null || (!suffix.endsWith(".xls") && !suffix.endsWith(".xlsx"))) {
return new AjaxResult<String>(false, "文件类型:[" + suffix + "]无法处理");
}
List<String> resultObj = new ArrayList<String>();
StringBuffer sb = new StringBuffer();
Workbook wb = null;
Sheet sheet = null;
if(".xls".equals(suffix)){
wb = new HSSFWorkbook(file.getInputStream());
sheet = wb.getSheetAt(0);
}else if(".xlsx".equals(suffix)){
wb = new XSSFWorkbook(file.getInputStream());
sheet = wb.getSheetAt(0);
}
if(sheet != null){
for (int i = 1, j = sheet.getLastRowNum(); i <= j; i++) {
String shopCode = getCellValue(sheet.getRow(i).getCell(1));
if (StringUtils.isEmpty(shopCode)) {
sb.append(MessageFormat.format("第{0}行导入失败,店铺编码不能为空;<br/>", i+1));
continue;
}
String sn = getCellValue(sheet.getRow(i).getCell(2));
if (StringUtils.isEmpty(sn)) {
sb.append(MessageFormat.format("第{0}行导入失败,sn不能为空;<br/>", i+1));
continue;
}
try {
payShopService.saveShop(shopCode, sn);
} catch (Exception e) {
sb.append(MessageFormat.format("第{0}行导入失败("+ e.getMessage() +");<br/>", i+1));
continue;
}
}
}
resultObj.add(sb.toString());
String msg = sb.toString();
AjaxResult result = new AjaxResult<String>();
if(msg.length() > 0){
result.setMsg("文件导入失败:<br/>"+msg);
result.setData(resultObj);
result.setSuccess(false);
}else {
result.setMsg("文件导入成功!");
result.setSuccess(true);
}
return result;
}
public String getSuffixByFilename(String filename) {
return filename.substring(filename.lastIndexOf(".")).toLowerCase();
}
private String getCellValue(Cell cell) {
if(cell == null){
return null;
}
String cellValue = "";
DecimalFormat df = new DecimalFormat("#");
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING: // String -- 1
cellValue = cell.getRichStringCellValue().getString().trim();
break;
case Cell.CELL_TYPE_NUMERIC: // 数值 -- 0
cellValue = df.format(cell.getNumericCellValue()).toString();
break;
case Cell.CELL_TYPE_BOOLEAN: // boolean -- 4
cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
break;
case Cell.CELL_TYPE_FORMULA: // 公式 -- 2
cellValue = cell.getCellFormula();
break;
default:
cellValue = "";
}
return cellValue;