excel表格文件怎么解析?别慌,Apache早已有解决方式。以下,从应用的角度实现Excel文件上传并解析
语言:Java、Vue
1.文件转base64格式传输
<el-upload
class="action-btn"
:multiple="false"
:show-file-list="false"
:with-credentials="true"
accept=".xlsx,.xls"
name="file"
:headers="headers"
:http-request="uploadFileByBase64"
>
<el-button >上传</el-button>
</el-upload>
uploadFileByBase64(file, fileList) {
this.getBase64(file.file).then(async (res) => {
const fileBase64 = res;
const param = {
fileName: file.file.name,
fileStr: fileBase64,
};
await api.uploadFile({
param,
}).then((v) => {
this.$message.success('文件上传成功!');
console.log('上传结果 v = ', v);
}).catch((err) => {
this.$message.warning('文件上传异常,请检查文件格式!');
});
});
},
getBase64(file) {
return new Promise((resolve, reject) => {
const reader = new FileReader();
let fileResult = '';
reader.readAsDataURL(file);
reader.onload = function () {
fileResult = reader.result;
};
reader.onerror = function (error) {
reject(error);
};
reader.onloadend = function () {
resolve(fileResult);
};
});
},
为什么要使用base64字符串?
2.解析
2.1 base64 -> MultipartFile
public static MultipartFile base64ToMultipart(String fileStr) {
try {
String[] baseStr = fileStr.split(",");
if(baseStr.length != 2) {
log.error("base64ToMultipartToStream 输入base64字符串无文件前缀, fileStr = {}", fileStr);
return null;
}
BASE64Decoder decoder = new BASE64Decoder();
byte[] b = new byte[0];
b = decoder.decodeBuffer(baseStr[1]);
for(int i = 0; i < b.length; ++i) {
if (b[i] < 0) {
b[i] += 256;
}
}
return new BASE64DecodedMultipartFile(b, baseStr[0]);
} catch (IOException e) {
log.error("error")
}
}
2.2 内容校验,实例化
使用apache提供工具包, 详情:https://mvnrepository.com/artifact/org.apache.poi
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.6</version>
</dependency>
private List<BeanParam> readAndConstruct(InputStream file) {
List<BeanParam> resultList = new ArrayList<>();
DecimalFormat decimalFormat = new DecimalFormat("0");
try {
OPCPackage pkg = OPCPackage.open(file);
XSSFWorkbook xwb = new XSSFWorkbook(pkg);
XSSFSheet sheet = xwb.getSheetAt(0);
XSSFRow row = null;
XSSFCell cell = null;
for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
BeanParam vo = new BeanParam();
row = sheet.getRow(i);
if (row == null) {
continue;
}
String categoryStr = "";
cell = row.getCell(0);
if(cell == null){
continue;
}
switch(cell.getCellType()){
case HSSFCell.CELL_TYPE_STRING:
categoryStr = cell.getStringCellValue().trim();break;
case HSSFCell.CELL_TYPE_NUMERIC:
categoryStr = decimalFormat.format(cell.getNumericCellValue());break;
default:
categoryStr = cell.getStringCellValue().trim();break;
}
vo.setCateId(Long.parseLong(categoryStr));
.
.
.
.
.
cell = row.getCell(5);
if(cell == null){
continue;
}
vo.setName(getValByCell(cell));
.
.
.
resultList.add(vo);
}
} catch (Exception e) {
e.printStackTrace();
}
return resultList;
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)