《5分钟Java》实现excel文件上传并解析
一、需求说明
通过接口上传一个姓名号码表，返回一个"姓名，号码"格式的的一个String数组。
二、功能实现
1、pom.xml中引入依赖
org.apache.poi
poi
4.1.2
org.apache.poi
poi-ooxml
4.1.2
使用poi去解析excel，poi对应的是excel2003，poi-ooxml对应的是excel2007。
2、实现上传接口
@RestController
@RequestMapping("/testFile")
public class FIleUpDownLoadController {
@Autowired
private FileParserService fileParserService;
@ApiOperation(value = "上传excel文件")
@RequestMapping(value = "/target", method = RequestMethod.POST)
@ResponseBody
public ResponseEntity importTarget(@RequestParam("file") MultipartFile file, HttpServletResponse response) {
ResponseEntity res = new ResponseEntity();
try {
String fileName = file.getOriginalFilename();
if (!fileName.endsWith(".xls")) {
res.setCode(ReturnCodeMsgEnum.IMPORT_EXCEL_FORMAT.getCode());
res.setMsg(ReturnCodeMsgEnum.IMPORT_EXCEL_FORMAT.getMsg());
return res;
}
//接收文件流和解析excel
List rows = fileParserService.splitRows(file);
//无错误，不需要返回下载文件，且执行保存数据库
res.setCode(ReturnCodeMsgEnum.IMPORT_EXCEL_SUCCESS.getCode());
res.setMsg(ReturnCodeMsgEnum.IMPORT_EXCEL_SUCCESS.getMsg());
res.setData(rows);
return res;
}catch(Exception e){
logger.error("importTarget error", e);
res.setCode(ReturnCodeMsgEnum.IMPORT_EXCEL_ERROR.getCode());
res.setMsg(ReturnCodeMsgEnum.IMPORT_EXCEL_ERROR.getMsg());
res.setData(e.getMessage());
return res;
}
}
} 主要就是使用MultipartFile类型的参数进行文件流的上传。通过file.getOriginalFilename()去获取上传文件的名称。
3、流的处理和excel表格的解析
@Service("fileParserService")
public class FileParserServiceImpl implements FileParserService {
public static final String EXCEL_2003 = ".xls";
public static final String EXCEL_2007 = ".xlsx";
public static final String COMMA = ",";
private static final Logger log = LoggerFactory.getLogger(FileParserServiceImpl.class);
@Override
public List splitRows(MultipartFile file) throws FileParserServiceException {
// skip validation
List rows = null;
try (InputStream inputStream = file.getInputStream()) {
String fileName = file.getOriginalFilename();
Workbook workbook = null;
if (fileName.endsWith(EXCEL_2003)) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileName.endsWith(EXCEL_2007)) {
workbook = new XSSFWorkbook(inputStream);
}
if (workbook != null) {
rows = new ArrayList<>();
//sheet页数
int numOfSheet = workbook.getNumberOfSheets();
if(numOfSheet>1){
throw new FileParserServiceException("不支持多个sheet上传");
}
for (int i = 0; i < numOfSheet; i++) {
Sheet sheet = workbook.getSheetAt(i);
if (sheet == null) continue;
//行数
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum == 0) continue;
Row row;
//列数
short lastCellNum = sheet.getRow(1).getLastCellNum();
for (int j = 1; j <= lastRowNum; j++) {
StringBuilder sb = new StringBuilder();
row = sheet.getRow(j);
if (row == null) {
throw new FileParserServiceException("当前文件存在空行,请重新上传");
}
String mNum = "";
Cell cellA = row.getCell(0);
if (cellA != null) {
cellA.setCellType(CellType.STRING);
mNum = cellA.getStringCellValue().trim();
}
sb.append(mNum).append(COMMA);
//遍历每一行
for (int k = 1; k < lastCellNum; k++) {
String res = "";
Cell cell = row.getCell(k);
if (cell == null) {
continue;
}else if(cell.getCellTypeEnum() == CellType.BLANK){
continue;
}
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
Date theDate = cell.getDateCellValue();
short s = cell.getCellStyle().getDataFormat();
if (s == 0x16) {
res = new SimpleDateFormat(Constants.DATE_FORMAT_B).format(theDate);
} else if (s == 0x12 || s == 0x14) {
res = new SimpleDateFormat(Constants.DATE_FORMAT_C).format(theDate);
} else if (s == 0x13 || s == 0x15 || s == 181 || s == 0x2e || s == 176 || s == 177) {
res = new SimpleDateFormat(Constants.DATE_FORMAT_D).format(theDate);
} else if (s == 0x2d) {
res = new SimpleDateFormat(Constants.DATE_FORMAT_E).format(theDate);
} else {
res = new SimpleDateFormat(Constants.DATE_FORMAT_A).format(theDate);
}
} else {
double value = cell.getNumericCellValue();
if (isInt(value)) {
res = String.valueOf(new Double(value).intValue());
} else if (isLong(value)) {
res = new BigDecimal(value).toString();
} else {
res = String.valueOf(value);
}
}
sb.append(res).append(COMMA);
continue;
}
cell.setCellType(CellType.STRING);
res = cell.getStringCellValue();
if(res.contains(":")||res.contains(",")){
throw new FileParserServiceException("当前文件存非法字符(,或:),请重新上传");
}
if (res.isEmpty())
continue;
sb.append(res).append(COMMA);
}
rows.add(sb.substring(0, sb.length() - 1));
}
}
}
} catch (IOException e) {
log.error("splitRows error", e);
}
return rows;
}
} （1）流的获取
file.getInputStream()
（2）流转换成Workbook
Workbook workbook = null;
if (fileName.endsWith(EXCEL_2003)) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileName.endsWith(EXCEL_2007)) {
workbook = new XSSFWorkbook(inputStream);
}
（3）解析excel
主要就是获取sheet页，获取行数，获取列数，遍历获取每个单元格。其中还涉及到单元格数字和字符串的解析问题。
三、测试验证
使用postman工具调用接口验证，符合需求。
localhost:8089/testFile/target