主要分为以下几步:
1.编写控制器判断读取的Excel版本是07版本,还是03版本的(基本上不用了)
2.读取Excel,(根据列名来获取所在的index)
3.做一些处理,比如字符串类型的数据
4.编写插入(insert语句 )
/**
* 导入功能
*
* @since 1.0
* @param request
* @param file
* @return <br>
* <b>作者: @author </b> <br>
* 创建时间:2016年4月1日 上午10:31:06
*/
@PermissionLogined
@RequestMapping("/manage/import")
@RequiresPermissions(PermissionConstant.U7_MANAGE_IMPORT)
@ResponseBody
public String importFile(HttpServletRequest request, @RequestParam MultipartFile file) {
Long userid = getCurrentUserId(request);
logger.info("用户{}进行U7申购管理页面导入功能", userid);
String fileName = file.getOriginalFilename();
String lastName = fileName.substring(fileName.lastIndexOf("."), fileName.length()).trim();
try {
if (".xls".equals(lastName)) {
readXls(file.getInputStream());<span style="font-family:微软雅黑;">//03低版本</span>没写
} else {
readXlsx(file.getInputStream());
}
} catch (Exception e) {
logger.error("导入失败:" + ExceptionUtils.getFullStackTrace(e));
return JsonUtils.getFailure("导入Excel失败");
}
return JsonUtils.getSuccess();
}
/**
* 导入07版本的excel
*
* @since 1.0
* @param is
* @throws IOException <br>
* <b>作者: @author ZhuShuangShuang</b> <br>
* 创建时间:2016年4月1日 上午10:35:02
*/
private void readXlsx(InputStream is) throws IOException {
XSSFWorkbook book = new XSSFWorkbook(is);
XSSFSheet sheet = book.getSheetAt(0);
XSSFRow row = sheet.getRow(0);
int rowNum = sheet.getLastRowNum();
int colNum = row.getPhysicalNumberOfCells();
int oriOrderNoIndex = -1;
int allremarkIndex = -1;
int expressNoIndex = -1;
for (int i = 0; i < colNum; i++) {
XSSFCell cell = row.getCell(i);
String string = cell.toString();
String name = string.trim();
if (StringUtils.isBlank(name)) {
continue;
}
if (StringUtils.contains("原始单号", name)) {
oriOrderNoIndex = i;
}
if (StringUtils.contains("合并备注", name)) {
allremarkIndex = i;
}
if (StringUtils.contains("物流单号", name)) {
expressNoIndex = i;
}
}
Map<String, CCGJExcelInfoDto> imeiMap = new HashMap<String, CCGJExcelInfoDto>();
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
String oriOrderNo = getStringXLSXCellValue(row.getCell(oriOrderNoIndex));
String allremark = getStringXLSXCellValue(row.getCell(allremarkIndex));
String expressNo = getStringXLSXCellValue(row.getCell(expressNoIndex));
if (StringUtils.isBlank(oriOrderNo)) {
continue;
}
if (StringUtils.isBlank(allremark)) {
continue;
}
if (StringUtils.isBlank(expressNo)) {
continue;
}
allremark = allremark.replace("<SNCode>", "");
allremark = allremark.replace("</SNCode>", "");
allremark = allremark.replace("<SN收集列表>", "");
allremark = allremark.replace("</SN收集列表>", "");
// 根据orderno查询uphoneOrder表的信息
String[] imeis = allremark.split(",");
CCGJExcelInfoDto dto;
if (imeiMap.containsKey(oriOrderNo)) {
dto = imeiMap.get(oriOrderNo);
} else {
dto = new CCGJExcelInfoDto();
}
for (String imei : imeis) {
dto.getImeiCode().add(imei);
}
dto.setExpressNo(expressNo);
imeiMap.put(oriOrderNo, dto);
logger.info("读取到{}订单的imei信息{}",oriOrderNo,dto);
}
uphoneInfoService.updateIMEIS(imeiMap);
}
/**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell Excel单元格
* @return String 单元格数据内容
*/
private static String getStringXLSXCellValue(XSSFCell cell) {
String strCell = "";
if (cell == null) {
return "";
}
// 将数值型参数转成文本格式,该算法不能保证1.00这种类型数值的精确度
DecimalFormat df = (DecimalFormat) NumberFormat.getPercentInstance();
StringBuffer sb = new StringBuffer();
sb.append("0");
df.applyPattern(sb.toString());
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
double value = cell.getNumericCellValue();
while (Double.parseDouble(df.format(value)) != value) {
if ("0".equals(sb.toString())) {
sb.append(".0");
} else {
sb.append("0");
}
df.applyPattern(sb.toString());
}
strCell = df.format(value);
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell == null || "".equals(strCell)) {
return "";
}
return strCell;
}