描述:开发中总会遇到上传Excel文件、解析文件、入库操作
本篇主要分享一下,POI解析Excel文件的代码,为下一篇调用
cell.getStringCellValue()
解析数值,出现多位小数的BUG铺垫。
个人建议:这里只把核心的部分分享出来,按照这个顺序往下看,应该可以看得懂
一、maven依赖包
<!-- Poi Dependency Begin -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
<exclusions>
<exclusion>
<artifactId>commons-codec</artifactId>
<groupId>commons-codec</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>3.0.2</version>
<exclusions>
<exclusion>
<artifactId>stax-api</artifactId>
<groupId>stax</groupId>
</exclusion>
</exclusions>
</dependency>
<!-- Poi Dependency End -->
二、核心代码
2.1 接收实体,将解析的数据封装到实体里面
// 解析EXCEL数据分装到该实体里面
public class RateBank implements Serializable {
private Integer standby3;
private String standby6;
private String standby7;
private String standby8;
private String standby9;
private String standby10;
private String queryNo;
public Integer getStandby3() {
return standby3;
}
public void setStandby3(Integer standby3) {
this.standby3 = standby3;
}
public String getStandby6() {
return standby6;
}
public void setStandby6(String standby6) {
this.standby6 = standby6;
}
public String getStandby7() {
return standby7;
}
public void setStandby7(String standby7) {
this.standby7 = standby7;
}
public String getStandby8() {
return standby8;
}
public void setStandby8(String standby8) {
this.standby8 = standby8;
}
public String getStandby9() {
return standby9;
}
public void setStandby9(String standby9) {
this.standby9 = standby9;
}
public String getStandby10() {
return standby10;
}
public void setStandby10(String standby10) {
this.standby10 = standby10;
}
public String getQueryNo() {
return queryNo;
}
public void setQueryNo(String queryNo) {
this.queryNo = queryNo;
}
}
2.2 主流程的代码,为了方便看,我将所有的方法写在一个类里面
// 2.2.0 上传的入口方法
/**
* 上传文件
*
* @return 返回结果
* @author huangwenbin
*/
@RequestMapping("/upload")
@ResponseBody
public Object uploadFile(@RequestParam("type") String type) {
// 获取到页面上传文件
MultipartFile file = (MultipartFile) getValue("file");
if (file == null) {
logger.error("上传文件为空");
return getResultErrorMap("上传文件为空!");
}
if (file.getSize() > limitSize) {
return getResultErrorMap("文件过大请重新上传");
}
final String fileUrl;
final boolean validateSuccess;
try {
// 这个是调用核心的校验方法,看2.2.1
Map<String, Object> resultMap = this.validateRateFile(IOUtils.toByteArray(file.getInputStream()) , file.getOriginalFilename());
if (resultMap.size() == 0) {
logger.error("上传文件为空");
return getResultErrorMap("上传文件为空!");
}
// 如果校验结果为不通过:false
validateSuccess = (boolean) resultMap.get(VALIDATE_KEY_SUCCESS);
fileUrl = (String) resultMap.get(RATE_BANK_KEY_URL);
} catch (IOException e) {
logger.error("文件上传失败", e);
return getResultErrorMap(e.getMessage());
}
//响应参数
Map<String, Object> params = new HashMap<>(8);
params.put("fileName", file.getOriginalFilename());
params.put("size", file.getSize());
params.put("validateSuccess", validateSuccess);
params.put("fileUrl", fileUrl);
return getResultSuccessMap(params);
}
// 2.2.1 开始解析文件流
/**
* 校验EXCEL
* 校验EXCEL
* @param bytes
* @param originalFilename
* @return
*/
public Map<String, Object> validateRateFile(byte[] bytes, String originalFilename) {
// 定义返回的结果
Map<String, Object> resultMap = new HashMap<String, Object>(4);
// 输入流或者文件名称为空
if (bytes == null || StringUtil.isBlank(originalFilename)) {
return resultMap;
}
// 开始进行解析操作
try {
// 拷贝InputStream用于重复读取
ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes);
byteArrayInputStream.mark(0);
// 校验汇款明细,返回校验结果 , 这里校验的核心逻辑,详细看下面:2.2.2
Map<String, Object> rateBankDataMap = this.getRateBankData(byteArrayInputStream);
byteArrayInputStream.reset();
// 如果校验结果为不通过:false
boolean validateSuccess = (boolean) rateBankDataMap.get(VALIDATE_KEY_SUCCESS);
// 获取到校验返回明细内容
List<RateBank> rateBankList = (List<RateBank>) rateBankDataMap.get(RATE_BANK_DATA);
if (!validateSuccess) {
// 将错误信息写入上传来的Excel中
Workbook wb = WorkbookFactory.create(byteArrayInputStream);
Sheet sheet = wb.getSheetAt(0);
for (RateBank rateBank : rateBankList) {
Row row = sheet.getRow(rateBank.getStandby3());
Cell cell = row.createCell(5);
cell.setCellValue(rateBank.getQueryNo());
}
// 将Workbook写入到ByteArrayOutputStream
ByteArrayOutputStream baos = new ByteArrayOutputStream();
wb.write(baos);
wb.close();
// 用ByteArrayOutputStream的内容覆盖原有ByteArrayInputStream
byteArrayInputStream = new ByteArrayInputStream(baos.toByteArray());
baos.close();
}
// 上传文件到FastDFS 。 这行大家可忽略,调用上传文件到服务器
String filePath = FastDFSUtil.upload(byteArrayInputStream, originalFilename);
byteArrayInputStream.close();
resultMap.put(VALIDATE_KEY_SUCCESS, validateSuccess);
resultMap.put(RATE_BANK_KEY_URL, filePath);
} catch (IOException | EncryptedDocumentException | InvalidFormatException e) {
throw new BusinessException("文件处理失败", e).print();
}
logger.info("RateBankService.validateRateFile 文件处理结果:{}", JSON.toJSONString(resultMap));
return resultMap;
}
// 2.2.2 解析并校验Excel数据问题
/**
* 解析并校验Excel数据
* @param is
* @return
* @throws BusinessException
*/
private Map<String, Object> getRateBankData(InputStream is) throws BusinessException {
boolean validateSuccess = true;
// 定义返回结果对象
Map<String, Object> resultMap = new HashMap<>();
try (Workbook wb = WorkbookFactory.create(is)) {
Sheet sheet = wb.getSheetAt(0);
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
logger.info("获取到Sheet:{},总行数:{}", sheet.getSheetName(), physicalNumberOfRows);
// 模板只有2条,则认为模板错误,毕竟没有真正内容需要处理
if (physicalNumberOfRows <= 1) {
throw new BusinessException("模板解析错误,请从第二行开始记录明细").print();
}
List<RateBank> rateBankList = new ArrayList<>(physicalNumberOfRows);
for (int i = 1; i < physicalNumberOfRows; i++) {
Row row = sheet.getRow(i);
RateBank rateBank = new RateBank();
// 银行渠道 卖出买入币种 汇率价格 发布时间 有效时间 这里不用指定的字段,因为都是String类型,故找备用字段暂时存
rateBank.setStandby3(row.getRowNum());
rateBank.setStandby6(getStringValue(row.getCell(0)));
rateBank.setStandby7(getStringValue(row.getCell(1)));
rateBank.setStandby8(getStringValue(row.getCell(2)));
rateBank.setStandby9(getStringValue(row.getCell(3)));
rateBank.setStandby10(getStringValue(row.getCell(4)));
// 校验明细对象,详细看下文的校验2.2.3
String validateResult = this.validateResult(rateBank);
// 校验返回结果非空则出现错误
if (StringUtil.isNotBlank(validateResult)) {
validateSuccess = false;
// 利用QueryNo临时存储错误信息
rateBank.setQueryNo(validateResult);
}
rateBankList.add(rateBank);
}
resultMap.put(RATE_BANK_DATA, rateBankList);
}catch (EncryptedDocumentException | InvalidFormatException | IOException e) {
throw new BusinessException("文件读取错误", e).print();
}
resultMap.put(VALIDATE_KEY_SUCCESS, validateSuccess);
return resultMap;
}
// 2.2.3 校验Excel内容
先附上所有的常量
public static String RATE_CHANNEL_IS_NO_EMPTY = " XX渠道不能为空";
public static String RATE_DATA_IS_N0_EMPTY = " XX对应的数据不能为空";
public static String CURRENCY_ONLY_SUPPORT_MESSAGE = " 仅支持大写字母及/";
public static String CURRENCY_ERROR_MESSAGE = " 代码错误";
public static String RATE_PRICE_ONLY_SUPPORT_MESSAGE = " 仅支持数字及半角";
public static String TIME_ONLY_SUPPORT_MESSAGE = " 时间仅支持数字";
public static String PUBLIC_TIME_ERROR_MESSAGE = " 时间仅支持数字";
public static String VALID_TIME_ERROR_MESSAGE = " 时间仅支持数字";
public static String TIME_FORMAT_ERROR = "时间格式化错误";
public static String DATE_TIME_FORMAT = "yyyyMMddHHmmss";
/** 校验返回结果KEY:成功标记 */
public static final String VALIDATE_KEY_SUCCESS = "VALIDATE_KEY_SUCCESS";
/** 校验返回结果KEY:文件路径 */
public static final String RATE_BANK_KEY_URL = "RATE_BANK_KEY_URL";
public static final String RATE_BANK_DATA = "RATE_BANK_DATA";
public static final String RATE_BANK_FILE_URL = "rateBankfileUrl";
/**
* 文件限制大小
*/
private long limitSize = 2 * 1024 * 1024;
/**字母和数字*/
private static final String NOCHINESE2 = "^[A-Z/]+$";
/** 金额验证正则:数字、小数点,最大五位小数 */
private static final String Rate_REGEX = "\\d+(.\\d{1,5})?";
/** 纯数字验证正则 */
private static final String DIGIT_REGEX = "\\d*";
/**
* 校验数据
* @param rateBank
* @return
*/
private String validateResult(RateBank rateBank){
StringJoiner result = new StringJoiner("");
// 1. 判断XX是否需存在
if(StringUtils.isEmpty(rateBank.getStandby6())){
result.add(";XX不可为空");
}else{
// 校验4位数字编码
String bankType = rateBank.getStandby6();
if(!Pattern.matches(DIGIT_REGEX, bankType) || bankType.length() != 4){
result.add(";XX格式错误,仅支持4位数字编码,请修改");
}else{
// 数据库查重
// 忽略这行
}
}
// 2. 判断买入卖出币种
if(StringUtil.isEmpty(rateBank.getStandby7())){
result.add(";XX不可为空");
}else{
String[] curtypeArray = rateBank.getStandby7().split("/");
if(!Pattern.matches(NOCHINESE2, rateBank.getStandby7()) || curtypeArray.length != 2){
result.add(";XX仅支持大写字母及/");
}else{
if(CurType.getByLetterCode(curtypeArray[0]) == null ||
CurType.getByLetterCode(curtypeArray[1]) == null
){
result.add(";XX代码错误");
}
}
}
// 3. 汇率价格
if(StringUtil.isEmpty(rateBank.getStandby8())){
result.add(";XX不可为空");
}else{
String ratePrice = rateBank.getStandby8();
if(!Pattern.matches(Rate_REGEX, ratePrice)) {
result.add(";XX仅支持数字及半角");
}
}
// 4. XX时间
if(StringUtil.isEmpty(rateBank.getStandby9())){
result.add(";XX时间不能为空");
}else{
String publicTime = rateBank.getStandby9();
if(!Pattern.matches(DIGIT_REGEX, publicTime)){
result.add(";XX仅支持数字");
}else{
judgeTimeFormat(result, publicTime, ";发布时间");
}
}
// 5.XX时间
if(StringUtil.isEmpty(rateBank.getStandby10())){
result.add(";XX时间不能为空");
}else{
String validTime = rateBank.getStandby10();
if(!Pattern.matches(DIGIT_REGEX, validTime)){
result.add(";时间仅支持数字");
}else{
judgeTimeFormat(result, validTime, ";XX时间");
}
}
return result.toString();
}
2.3 附上小小的工具类方法
/**
* 判断时间格式是否正确
* @param result
* @param publicTime
*/
private void judgeTimeFormat(StringJoiner result, String publicTime, String title){
DateFormat format = new SimpleDateFormat(DATE_TIME_FORMAT);
try {
format.parse(publicTime);
} catch (ParseException e) {
result.add(title + "时间格式化出错");
}
}
/**
* 读取Cell内容
* @param cell
* @return
* @author yangziran
*/
private static String getStringValue(Cell cell) {
if (cell == null) {
return null;
}
cell.setCellType(CellType.STRING);
return cell.getStringCellValue();
}
致此就可以了
三、重点
上面的核心代码在于:
下面的代码是将Excel,每个格子的属性设置为:String类型,这样就可以拿到完整的值了
2.2.2
使用:Workbook wb = WorkbookFactory.create(is)读取字节数组流后
调用POI的方法,
rateBank.setStandby6(getStringValue(row.getCell(0)));
rateBank.setStandby7(getStringValue(row.getCell(1)));
getStringValue是自己包装的方法
/**
* 读取Cell内容
* @param cell
* @return
* @author yangziran
*/
private static String getStringValue(Cell cell) {
if (cell == null) {
return null;
}
cell.setCellType(CellType.STRING);
return cell.getStringCellValue();
}
POI接口:cell.getStringCellValue();
四:解析非整数型数字出错
在以往的经验中,都是将Excel设置成字符串,然后使用字符串的方式去获取数据,这样就能完整的将Excel拷贝出来。
cell.setCellType(CellType.STRING);
公司测试的同事,上传了5.89327,结果被解析成:5.8932799999999999999
详细看下一篇