1、前端上传文件
<Input.Group compact className='iptUpload'>
<Input style={{width: 'calc(100% - 200px)',}} value={this.state.fileName}/>
<Upload {...props} style={{ paddingLeft: 15 }} fileList={this.state.fileList}>
<Button htmlType="button" type="primary">选择文件</Button>
</Upload>
</Input.Group>
<span>
<Button className='uploadBts' type="primary" disabled={disabled} onClick={() => {this.uploadClick()}}>上 传</Button>
</span>
// 文件上传
uploadClick = () => {
console.log("上传")
const fileName = this.state.fileName;
const Operator = this.state.Operator;
const info = this.state.fileList[0];
const reader = new FileReader()
reader.readAsDataURL(info.originFileObj)
reader.onload = e => {
const { result } = e.target
console.log('result',result)
const TransData = {
TransData:{
BaseInfo: {
TradeType: 'http',
TradeCode: 'S07',
TradeSeq: tools.getTradeSeq(),
TradeDate: tools.getToday(),
TradeTime: tools.getTime(),
Operator: Operator,
Key: tools.getKey(),
},
InputData: {
fileName: fileName,
fileByte: result
}
}
};
axios({
method: 'POST',
url: url + '/fileUrl/uploadFile',
data: TransData,
}).then(response => {
let res = response.data
if (res.TransData.BaseInfo.TranFlag === '0'){
message.success("上传成功")
} else if (res.TransData.BaseInfo.TranFlag != '0') {
message.error('上传失败,' + res.TransData.BaseInfo.ErrorMessage)
} else {
message.error('系统异常')
}
})
.catch(response => {
message.error('系统异常,接口调用失败')
})
}
}
2.后端获取请求报文,解析excel文件存储到数据库,实现对象存储
(1)导入的excel文件sheet页的相关内容 @ExcelProperty(value = {"标题","对应列名"})
package com.entity.pojo;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.io.Serializable;
/**
* title: SetReq
* description:设置上传字段
*/
@Data
public class SetReq implements Serializable {
@ExcelProperty(value = {"导入","序号"})
private String serialNo;
@ExcelProperty(value = {"导入","编码"})
private String no;
@ExcelProperty(value = {"导入","名称"})
private String name;
@ExcelProperty(value = {"导入","机构编码"})
private String organ;
@ExcelProperty(value = {"导入","机构名称"})
private String organName;
@ExcelProperty(value = {"导入","收费类型"})
private String type;
@ExcelProperty(value = {"导入","生效日期"})
private String effectiveDate;
@ExcelProperty(value = {"导入","截止日期"})
private String expirationDate;
@ExcelProperty(value = {"导入","备注"})
private String remarks;
}
(2)Java获取报文,上传,解析
package com.biz;
import com.entity.pojo.*;
import com.util.*;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
/**
* title: UploadBiz
* description:上传
*/
@Component
public class UploadBiz {
@Autowired
private FileUtil fileUtil;
public String upload(String requestJson) throws Exception {
String responseJson;
File file = null;
try {
// 请求报文转换
JSONObject inputData = JSON.parseObject(requestMessage).getJSONObject("TransData").getJSONObject("InputData");
String fileByte = inputData.getString("fileByte");
// 校验文件名是否有中文
String fileName = inputData.getString("fileName");
if(this.verifyChinese(fileName)){
fileName = "uploadFile.xlsx";
}
// 获取文件名称
fileName = fileUtil.getUploadFileName(fileName);
// base64转multipartFile
MultipartFile multipartFile = Base64DecodeMultipartFile.base64Convert(fileByte);
file = fileUtil.MultipartFileToFile(multipartFile);
// 校验文件是否存在
CheckDataUtil.checkFileExists(file);
// 文件上传
fileUtil.uploadFile(fileName, file);
// 文件转为流
InputStream excelInputStream = new FileInputStream(file);
//定义返回数据
List<FailInfo> failList = new ArrayList<>();
//读取整个Excel
List<SetReq> setReqList = EasyExcelUtil.readExcel(excelInputStream, SetReq.class, new EasyExcelUtil.ExcelListener<>());
SetReqList.forEach(System.out::println); //打印读取的excel文件
for (SetReq req : setReqList) {
String serialNo = req.getSerialNo();
StringBuilder errorMessage = new StringBuilder();
//校验上传字段
checkFieldData(req, errorMessage);
// 如果导入结束给出成功与否提示和导入失败的每条数据的失败原因;
if (errorMessage.length() > 0) {
FailInfo failInfo = new FailInfo();
failInfo.setRow(serialNo);
failInfo.setMessage(errorMessage.toString());
failList.add(failInfo);
}
}
//判断导入文件是否有失败的记录,如果有,则导入文件中的所有记录均不导入,修改文件后可重新导入
if (failList != null && failList.size() > 0) {
responseJson = MessageConvertUtil.xStreamToJson(failList);
//删除临时文件
fileUtil.delete(file);
return responseJson;
}
// 没有导入失败的记录,则写入数据库
for (SetReq reqest : setReqList) {
int record = insertData(reqest);
if (record <= 0) {
FailInfo failInfo = new FailInfo();
failInfo.setRow(reqest.getSerialNo());
failInfo.setMessage("数据库更新失败!");
failList.add(failInfo);
}
}
responseJson = MessageConvertUtil.xStreamToJson(failList);
//删除临时文件
fileUtil.delete(file);
}catch (Exception e) {
//删除临时文件
fileUtil.delete(file);
throw e;
}
return responseJson;
}
/**
* 校验文件名是否有中文
* @param fileName
* @throws
*/
public static boolean verifyChinese(String fileName) {
String pattern = "[\u4e00-\u9fa5]";
Pattern r = Pattern.compile(pattern);
Matcher m = r.matcher(fileName);
return m.find();
}
}
(3)文件处理类
package com.util;
import com.amazonaws.ClientConfiguration;
import com.amazonaws.Protocol;
import com.amazonaws.auth.AWSCredentials;
import com.amazonaws.auth.BasicAWSCredentials;
import com.amazonaws.services.s3.AmazonS3;
import com.amazonaws.services.s3.AmazonS3Client;
import com.amazonaws.services.s3.S3ClientOptions;
import com.amazonaws.services.s3.model.GeneratePresignedUrlRequest;
import com.amazonaws.services.s3.model.PutObjectRequest;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import java.io.*;
/**
* Title: FileUtil
* Description: 文件处理类
*/
@Component
public class FileUtil {
private static final Logger log= LoggerFactory.getLogger(FileUtil.class);
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
@Autowired
private UploadLogMapper UploadLogMapper;
@Autowired
private UploadConfigCephMapper UploadConfigCephMapper;
/**
* 文件上传
*
* @param fileName
* @param file
* @return
*/
public void uploadFile(String fileName, File file){
String filePath = "";
try {
UploadConfigCeph config = getConfig(1);
BasicAWSCredentials awsCreds = new BasicAWSCredentials(config.getAccessKey(), config.getSecretKey());
//根据ceph连接配置,获取连接对象
AmazonS3 awsConnection = getAWSConnection(config);
String key = sdf.format(new Date())+"/"+fileName;
PutObjectRequest request = new PutObjectRequest(config.getBucketName(), key, file);
awsConnection.putObject(request);
GeneratePresignedUrlRequest requests = new GeneratePresignedUrlRequest(config.getBucketName(), key);
filePath = awsConnection.generatePresignedUrl(requests).getPath();
} catch (Exception e) {
throw e;
}
}
/**
* 根绝configId获取连接配置
* @param configId配置表主键
*/
public UploadConfigCeph getConfig(Integer configId) {
return UploadConfigCephMapper.selectByPrimaryKey(configId);
}
/**
* 根据连接配置,获取连接对象
* @param config配置类
* @return com.amazonaws.services.s3.AmazonS3 连接对象
*/
public AmazonS3 getAWSConnection(UploadConfigCeph config) {
//获取完整连接路径
String endPoint = config.getEndpoint();
//创建AWS配置
AWSCredentials credentials = new BasicAWSCredentials(config.getAccessKey(), config.getSecretKey());
ClientConfiguration clientConfig = new ClientConfiguration();
//设置传输权限HTTPS or HTTP
if (endPoint.startsWith(CommonValue.HTTPS)) {
clientConfig.setProtocol(Protocol.HTTPS);
} else {
clientConfig.setProtocol(Protocol.HTTP);
}
//创建连接
AmazonS3 conn = new AmazonS3Client(credentials, clientConfig);
//如果链接是域名,打开域名拼接方式
conn.setS3ClientOptions(S3ClientOptions.builder().setPathStyleAccess(true).build());
//发起连接
conn.setEndpoint(endPoint);
return conn;
}
/**
* 获取文件名称
* @param fileName
*/
public String getUploadFileName(String fileName) {
if (StringUtils.isEmpty(fileName)) {
log.info("文件名称fileName为空");
}
//获得上传的文件名,例如ooa.jpg,只需要ooa,其前面的后面的都不需要,可以使用年月日时分秒毫秒设置文件名
String fName = fileName.substring(0, fileName.lastIndexOf(".")) + DateUtil.getDateTime17();
//获得文件的后缀名
String fileExtension = fileName.substring(fileName.lastIndexOf(".") + 1);
return fName +"."+ fileExtension;
}
/**
* MultipartFile 转 File
* @param multipartFile
*/
public static File MultipartFileToFile(MultipartFile multipartFile) {
File file = null;
//判断是否为null
if (multipartFile.equals("") || multipartFile.getSize() <= 0) {
return file;
}
//MultipartFile转换为File
InputStream ins = null;
OutputStream os = null;
try {
ins = multipartFile.getInputStream();
file = new File(multipartFile.getOriginalFilename());
os = new FileOutputStream(file);
int bytesRead = 0;
byte[] buffer = new byte[8192];
while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
os.write(buffer, 0, bytesRead);
}
} catch (IOException e) {
e.printStackTrace();
}finally {
if(os != null){
try {
os.flush();
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(ins != null){
try {
ins.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return file;
}
/**
* 操作完上的文件 需要删除在根目录下生成的文件
* @param file
*/
public static void delete(File file){
File f = new File(file.toURI());
if (f.delete()){
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
}
}
(3)excel解析
package com.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.sinosoft.microservice.entity.pojo.HandlingFeeRateSetReq;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* ClassName:EasyExcel工具类
*/
@Slf4j
public class EasyExcelUtil {
/**
* 读取文件解析监听类,此类供外部实例化使用需要设置为静态类
*/
public static class ExcelListener<T> extends AnalysisEventListener<T>{
// 存放读取后的数据
public List<T> datas = new ArrayList<>();
// 读取数据,一条一条读取
@Override
public void invoke(T t, AnalysisContext analysisContext) {
datas.add(t);
}
// 解析完毕之后执行
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("读取数据条数:{}条!", datas.size());
}
public List<T> getDatas(){
return this.datas;
}
}
/**
* 读取Excel文件返回数据集合,不包含表头,默认读取第一个sheet数据
* @param inputStream 输入流
* @param tClass 数据映射类
* @param excelListener 读取监听类
* @return List 结果集
*/
public static <T> List<T> readExcel(InputStream inputStream, Class<T> tClass, ExcelListener<T> excelListener){
if(ObjectUtils.isEmpty(inputStream) || ObjectUtils.isEmpty(tClass) || ObjectUtils.isEmpty(excelListener)){
return null;
}
ExcelReaderBuilder read = EasyExcel.read(inputStream, tClass, excelListener);
//.excelType("手续费率导入11111.xlsx".contains(ExcelTypeEnum.XLSX.getValue()) ? ExcelTypeEnum.XLSX : ExcelTypeEnum.XLS);
read.sheet().doRead();
return excelListener.getDatas();
}
/**
* 读取Excel文件返回数据集合,不包含表头,读取第n个sheet数据,不设置sheet就读取全部个sheet数据,不设置sheet就读取全部
* @param inputStream 输入流
* @param tClass 数据映射类
* @param excelListener 读取监听类
* @return List 结果集
*/
public static <T> List<T> readExcel(InputStream inputStream, Integer sheetNo, Class<T> tClass, ExcelListener<T> excelListener){
if(ObjectUtils.isEmpty(inputStream) || ObjectUtils.isEmpty(tClass) || ObjectUtils.isEmpty(excelListener)){
return null;
}
ExcelReaderBuilder read = EasyExcel.read(inputStream, tClass, excelListener);
if(ObjectUtils.isNotEmpty(sheetNo)){
read.sheet(sheetNo).doRead();
}else{
ExcelReader excelReader = read.build();
excelReader.readAll();
excelReader.finish();
}
return excelListener.getDatas();
}
}
注意:easyexcel要解析wps的需要使用高版本的依赖(低版本只能解析office),例如:
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.0.5</version> </dependency>
主要用到的依赖
<dependency> <groupId>com.amazonaws</groupId> <artifactId>aws-java-sdk-s3</artifactId> <version>1.11.98</version> </dependency>
package com.sinosoft.microservice.util;
import org.springframework.web.multipart.MultipartFile;
import sun.misc.BASE64Decoder;
import java.io.*;
/**
* base64转为multipartFile工具类
*
*/
public class Base64DecodeMultipartFile implements MultipartFile {
private final byte[] imgContent;
private final String header;
public Base64DecodeMultipartFile(byte[] imgContent, String header) {
this.imgContent = imgContent;
this.header = header.split(";")[0];
}
@Override
public String getName() {
return System.currentTimeMillis() + Math.random() + "." + header.split("/")[1];
}
@Override
public String getOriginalFilename() {
return System.currentTimeMillis() + (int) Math.random() * 10000 + "." + header.split("/")[1];
}
@Override
public String getContentType() {
return header.split(":")[1];
}
@Override
public boolean isEmpty() {
return imgContent == null || imgContent.length == 0;
}
@Override
public long getSize() {
return imgContent.length;
}
@Override
public byte[] getBytes() throws IOException {
return imgContent;
}
@Override
public InputStream getInputStream() throws IOException {
return new ByteArrayInputStream(imgContent);
}
@Override
public void transferTo(File dest) throws IOException, IllegalStateException {
new FileOutputStream(dest).write(imgContent);
}
/**
* base64转multipartFile
*
* @param base64
* @return
*/
public static MultipartFile base64Convert(String base64) {
String[] baseStrs = base64.split(",");
BASE64Decoder decoder = new BASE64Decoder();
byte[] b = new byte[0];
try {
b = decoder.decodeBuffer(baseStrs[1]);
} catch (IOException e) {
e.printStackTrace();
}
for (int i = 0; i < b.length; ++i) {
if (b[i] < 0) {
b[i] += 256;
}
}
return new Base64DecodeMultipartFile(b, baseStrs[0]);
}
}