devbox
菜鸟追梦旅行
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Java实现excel文件上传、解析，对象存储_java 网页上传excel文件

作者：菜鸟追梦旅行 | 2024-05-19 06:01:08

赞

踩

java 网页上传excel文件

1、前端上传文件

  1. <Input.Group compact className='iptUpload'>
  2.   <Input style={{width: 'calc(100% - 200px)',}} value={this.state.fileName}/>
  3.   <Upload {...props} style={{ paddingLeft: 15 }} fileList={this.state.fileList}>
  4.     <Button htmlType="button" type="primary">选择文件</Button>
  5.   </Upload>
  6. </Input.Group>
  7. <span>
  8.   <Button className='uploadBts' type="primary" disabled={disabled} onClick={() => {this.uploadClick()}}>上&emsp;传</Button>
  9. </span>
  10.  
  11. // 文件上传
  12.   uploadClick = () => {
  13.     console.log("上传")
  14.     const fileName = this.state.fileName;
  15.     const Operator = this.state.Operator;
  16.     const info = this.state.fileList[0];
  17.     const reader = new FileReader()
  18.     reader.readAsDataURL(info.originFileObj)
  19.     reader.onload = e => {
  20.       const { result } = e.target
  21.       console.log('result',result)
  22. 	  const TransData = {
  23.           TransData:{
  24.             BaseInfo: {
  25.               TradeType: 'http',
  26.               TradeCode: 'S07',
  27.               TradeSeq: tools.getTradeSeq(),
  28.               TradeDate: tools.getToday(),
  29.               TradeTime: tools.getTime(),
  30.               Operator: Operator,
  31.               Key: tools.getKey(),
  32.             },
  33.             InputData: {
  34.               fileName: fileName,
  35.               fileByte: result 
  36.             }
  37.           }
  38.         };
  39. 	  axios({
  40.          method: 'POST',
  41.          url: url + '/fileUrl/uploadFile',
  42.          data: TransData,
  43.        }).then(response => {
  44.         let res = response.data
  45.         if (res.TransData.BaseInfo.TranFlag === '0'){
  46.           message.success("上传成功")
  47.         } else if (res.TransData.BaseInfo.TranFlag  != '0') {
  48.           message.error('上传失败,' + res.TransData.BaseInfo.ErrorMessage)    
  49.         } else {
  50.           message.error('系统异常')
  51.         }
  52.       })
  53.       .catch(response => {
  54.         message.error('系统异常，接口调用失败')
  55.       })
  56.     }
  57.   }

2.后端获取请求报文，解析excel文件存储到数据库，实现对象存储

（1）导入的excel文件sheet页的相关内容      @ExcelProperty(value = {"标题","对应列名"})

  1. package com.entity.pojo;
  2. import com.alibaba.excel.annotation.ExcelProperty;
  3. import lombok.Data;
  4. import java.io.Serializable;
  5.  
  6. /**
  7.  * title: SetReq
  8.  * description:设置上传字段
  9.  */
  10. @Data
  11. public class SetReq implements Serializable {
  12.     @ExcelProperty(value = {"导入","序号"})
  13.     private String serialNo;
  14.     @ExcelProperty(value = {"导入","编码"})
  15.     private String no;
  16.     @ExcelProperty(value = {"导入","名称"})
  17.     private String name;
  18.     @ExcelProperty(value = {"导入","机构编码"})
  19.     private String organ;
  20.     @ExcelProperty(value = {"导入","机构名称"})
  21.     private String organName;
  22.     @ExcelProperty(value = {"导入","收费类型"})
  23.     private String type;
  24.     @ExcelProperty(value = {"导入","生效日期"})
  25.     private String effectiveDate;
  26.     @ExcelProperty(value = {"导入","截止日期"})
  27.     private String expirationDate;
  28.     @ExcelProperty(value = {"导入","备注"})
  29.     private String remarks;
  30. }

（2）Java获取报文，上传，解析

  1. package com.biz;
  2. import com.entity.pojo.*;
  3. import com.util.*;
  4. import org.apache.commons.lang3.StringUtils;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.stereotype.Component;
  7. import org.springframework.web.multipart.MultipartFile;
  8. import java.io.File;
  9. import java.io.FileInputStream;
  10. import java.io.InputStream;
  11. import java.math.BigDecimal;
  12. import java.util.ArrayList;
  13. import java.util.HashMap;
  14. import java.util.List;
  15.  
  16. /**
  17.  * title: UploadBiz
  18.  * description:上传
  19.  */
  20. @Component
  21. public class UploadBiz {
  22.     @Autowired
  23.     private FileUtil fileUtil;
  24.  
  25.     public String upload(String requestJson) throws Exception {
  26.         String responseJson;
  27.         File file = null;
  28.         try {
  29.             // 请求报文转换
  30. 			JSONObject inputData = JSON.parseObject(requestMessage).getJSONObject("TransData").getJSONObject("InputData");
  31. 			String fileByte = inputData.getString("fileByte");
  32. 			
  33.             // 校验文件名是否有中文
  34.             String fileName = inputData.getString("fileName");
  35.             if(this.verifyChinese(fileName)){
  36.                 fileName = "uploadFile.xlsx";
  37.             }
  38.             // 获取文件名称
  39.             fileName = fileUtil.getUploadFileName(fileName);
  40.             // base64转multipartFile
  41.             MultipartFile multipartFile = Base64DecodeMultipartFile.base64Convert(fileByte);
  42.             file = fileUtil.MultipartFileToFile(multipartFile);
  43.             // 校验文件是否存在
  44.             CheckDataUtil.checkFileExists(file);
  45.             // 文件上传
  46.             fileUtil.uploadFile(fileName, file);
  47.             // 文件转为流
  48.             InputStream excelInputStream = new FileInputStream(file);
  49.             //定义返回数据
  50.             List<FailInfo> failList = new ArrayList<>();
  51.  
  52.             //读取整个Excel
  53.             List<SetReq> setReqList = EasyExcelUtil.readExcel(excelInputStream, SetReq.class, new EasyExcelUtil.ExcelListener<>());
  54.             SetReqList.forEach(System.out::println); //打印读取的excel文件
  55.             for (SetReq req : setReqList) {
  56.                 String serialNo = req.getSerialNo();
  57.                 StringBuilder errorMessage = new StringBuilder();
  58.                 //校验上传字段
  59.                 checkFieldData(req, errorMessage);
  60.                 // 如果导入结束给出成功与否提示和导入失败的每条数据的失败原因；
  61.                 if (errorMessage.length() > 0) {
  62.                     FailInfo failInfo = new FailInfo();
  63.                     failInfo.setRow(serialNo);
  64.                     failInfo.setMessage(errorMessage.toString());
  65.                     failList.add(failInfo);
  66.                 }
  67.             }
  68.             //判断导入文件是否有失败的记录，如果有，则导入文件中的所有记录均不导入，修改文件后可重新导入
  69.             if (failList != null && failList.size() > 0) {
  70.                 responseJson = MessageConvertUtil.xStreamToJson(failList);
  71.                 //删除临时文件
  72.                 fileUtil.delete(file);
  73.                 return responseJson;
  74.             }
  75.             // 没有导入失败的记录，则写入数据库
  76.             for (SetReq reqest : setReqList) {
  77.                 int record = insertData(reqest);
  78.                 if (record <= 0) {
  79.                     FailInfo failInfo = new FailInfo();
  80.                     failInfo.setRow(reqest.getSerialNo());
  81.                     failInfo.setMessage("数据库更新失败!");
  82.                     failList.add(failInfo);
  83.                 }
  84.             }
  85.             responseJson = MessageConvertUtil.xStreamToJson(failList);
  86.             //删除临时文件
  87.             fileUtil.delete(file);
  88.             
  89.         }catch (Exception e) {
  90.             //删除临时文件
  91.             fileUtil.delete(file);
  92.             throw e;
  93.         }
  94.         return responseJson;
  95.     }
  96. 	
  97. 	/**
  98.      * 校验文件名是否有中文
  99.      * @param fileName
  100.      * @throws
  101.      */
  102.     public static boolean verifyChinese(String fileName) {
  103.         String pattern = "[\u4e00-\u9fa5]";
  104.         Pattern r = Pattern.compile(pattern);
  105.         Matcher m = r.matcher(fileName);
  106.         return m.find();
  107.     }
  108. }

（3）文件处理类

  1. package com.util;
  2. import com.amazonaws.ClientConfiguration;
  3. import com.amazonaws.Protocol;
  4. import com.amazonaws.auth.AWSCredentials;
  5. import com.amazonaws.auth.BasicAWSCredentials;
  6. import com.amazonaws.services.s3.AmazonS3;
  7. import com.amazonaws.services.s3.AmazonS3Client;
  8. import com.amazonaws.services.s3.S3ClientOptions;
  9. import com.amazonaws.services.s3.model.GeneratePresignedUrlRequest;
  10. import com.amazonaws.services.s3.model.PutObjectRequest;
  11. import org.apache.commons.lang3.StringUtils;
  12. import org.slf4j.Logger;
  13. import org.slf4j.LoggerFactory;
  14. import org.springframework.beans.factory.annotation.Autowired;
  15. import org.springframework.stereotype.Component;
  16. import org.springframework.web.multipart.MultipartFile;
  17. import java.io.*;
  18.  
  19. /**
  20.  * Title: FileUtil
  21.  * Description: 文件处理类
  22.  */
  23. @Component
  24. public class FileUtil {
  25.     private static final Logger log= LoggerFactory.getLogger(FileUtil.class);
  26. 	SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
  27.     @Autowired
  28.     private  UploadLogMapper UploadLogMapper;
  29.     @Autowired
  30.     private UploadConfigCephMapper UploadConfigCephMapper;
  31.  
  32.     /**
  33.      * 文件上传
  34.      *
  35.      * @param fileName
  36.      * @param file
  37.      * @return
  38.      */
  39.     public void uploadFile(String fileName, File file){
  40.         String filePath = "";
  41.         try {
  42.             UploadConfigCeph config = getConfig(1);
  43.             BasicAWSCredentials awsCreds = new BasicAWSCredentials(config.getAccessKey(), config.getSecretKey());
  44.             //根据ceph连接配置，获取连接对象
  45.             AmazonS3 awsConnection = getAWSConnection(config);
  46.             String key = sdf.format(new Date())+"/"+fileName;
  47.  
  48.             PutObjectRequest request = new PutObjectRequest(config.getBucketName(), key, file);
  49.             awsConnection.putObject(request);
  50.             GeneratePresignedUrlRequest requests = new GeneratePresignedUrlRequest(config.getBucketName(), key);
  51.             filePath =  awsConnection.generatePresignedUrl(requests).getPath();
  52.         } catch (Exception e) {
  53.             throw e;
  54.         }
  55.     }
  56.  
  57.     /**
  58.      * 根绝configId获取连接配置
  59.      * @param configId配置表主键
  60.      */
  61.     public UploadConfigCeph getConfig(Integer configId) {
  62.         return UploadConfigCephMapper.selectByPrimaryKey(configId);
  63.     }
  64.  
  65.     /**
  66.      * 根据连接配置，获取连接对象
  67.      * @param config配置类
  68.      * @return com.amazonaws.services.s3.AmazonS3 连接对象
  69.      */
  70.     public AmazonS3 getAWSConnection(UploadConfigCeph config) {
  71.         //获取完整连接路径
  72.         String endPoint = config.getEndpoint();
  73.         //创建AWS配置
  74.         AWSCredentials credentials = new BasicAWSCredentials(config.getAccessKey(), config.getSecretKey());
  75.         ClientConfiguration clientConfig = new ClientConfiguration();
  76.         //设置传输权限HTTPS or HTTP
  77.         if (endPoint.startsWith(CommonValue.HTTPS)) {
  78.             clientConfig.setProtocol(Protocol.HTTPS);
  79.         } else {
  80.             clientConfig.setProtocol(Protocol.HTTP);
  81.         }
  82.         //创建连接
  83.         AmazonS3 conn = new AmazonS3Client(credentials, clientConfig);
  84.         //如果链接是域名，打开域名拼接方式
  85.         conn.setS3ClientOptions(S3ClientOptions.builder().setPathStyleAccess(true).build());
  86.         //发起连接
  87.         conn.setEndpoint(endPoint);
  88.         return conn;
  89.     }
  90.  
  91.     /**
  92.      * 获取文件名称
  93.      * @param fileName
  94.      */
  95.     public String getUploadFileName(String fileName) {
  96.         if (StringUtils.isEmpty(fileName)) {
  97. 			log.info("文件名称fileName为空");
  98.         }
  99.         //获得上传的文件名，例如ooa.jpg,只需要ooa，其前面的后面的都不需要，可以使用年月日时分秒毫秒设置文件名
  100.         String fName = fileName.substring(0, fileName.lastIndexOf(".")) + DateUtil.getDateTime17();
  101.         //获得文件的后缀名
  102.         String fileExtension = fileName.substring(fileName.lastIndexOf(".") + 1);
  103.         return fName +"."+ fileExtension;
  104.     }
  105.  
  106.     /**
  107.      * MultipartFile 转 File
  108.      * @param multipartFile
  109.      */
  110.     public static File MultipartFileToFile(MultipartFile multipartFile) {
  111.         File file = null;
  112.         //判断是否为null
  113.         if (multipartFile.equals("") || multipartFile.getSize() <= 0) {
  114.             return file;
  115.         }
  116.         //MultipartFile转换为File
  117.         InputStream ins = null;
  118.         OutputStream os = null;
  119.         try {
  120.             ins = multipartFile.getInputStream();
  121.             file = new File(multipartFile.getOriginalFilename());
  122.             os = new FileOutputStream(file);
  123.             int bytesRead = 0;
  124.             byte[] buffer = new byte[8192];
  125.             while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
  126.                 os.write(buffer, 0, bytesRead);
  127.             }
  128.         } catch (IOException e) {
  129.             e.printStackTrace();
  130.         }finally {
  131.             if(os != null){
  132.                 try {
  133.                     os.flush();
  134.                     os.close();
  135.                 } catch (IOException e) {
  136.                     e.printStackTrace();
  137.                 }
  138.             }
  139.             if(ins != null){
  140.                 try {
  141.                     ins.close();
  142.                 } catch (IOException e) {
  143.                     e.printStackTrace();
  144.                 }
  145.             }
  146.         }
  147.         return file;
  148.     }
  149.  
  150.     /**
  151.     * 操作完上的文件 需要删除在根目录下生成的文件
  152.     * @param file
  153.     */
  154.     public static void delete(File file){
  155.         File f = new File(file.toURI());
  156.         if (f.delete()){
  157.             System.out.println("删除成功");
  158.         }else {
  159.             System.out.println("删除失败");
  160.         }
  161.     }
  162. }

（3）excel解析

  1. package com.util;
  2. import com.alibaba.excel.EasyExcel;
  3. import com.alibaba.excel.ExcelReader;
  4. import com.alibaba.excel.context.AnalysisContext;
  5. import com.alibaba.excel.event.AnalysisEventListener;
  6. import com.alibaba.excel.read.builder.ExcelReaderBuilder;
  7. import com.sinosoft.microservice.entity.pojo.HandlingFeeRateSetReq;
  8. import lombok.extern.slf4j.Slf4j;
  9. import org.apache.commons.lang3.ObjectUtils;
  10. import java.io.File;
  11. import java.io.FileInputStream;
  12. import java.io.InputStream;
  13. import java.util.ArrayList;
  14. import java.util.List;
  15.  
  16.  
  17. /**
  18.  * ClassName：EasyExcel工具类
  19.  */
  20. @Slf4j
  21. public class EasyExcelUtil {
  22.     /**
  23.      * 读取文件解析监听类，此类供外部实例化使用需要设置为静态类
  24.      */
  25.     public static class ExcelListener<T> extends AnalysisEventListener<T>{
  26.         // 存放读取后的数据
  27.         public List<T> datas = new ArrayList<>();
  28.         // 读取数据，一条一条读取
  29.         @Override
  30.         public void invoke(T t, AnalysisContext analysisContext) {
  31.             datas.add(t);
  32.         }
  33.         // 解析完毕之后执行
  34.         @Override
  35.         public void doAfterAllAnalysed(AnalysisContext analysisContext) {
  36.             log.info("读取数据条数：{}条！", datas.size());
  37.         }
  38.         public List<T> getDatas(){
  39.             return this.datas;
  40.         }
  41.     }
  42.  
  43.     /**
  44.      *  读取Excel文件返回数据集合，不包含表头，默认读取第一个sheet数据
  45.      * @param inputStream 输入流
  46.      * @param tClass 数据映射类
  47.      * @param excelListener 读取监听类
  48.      * @return List 结果集
  49.      */
  50.     public static <T> List<T> readExcel(InputStream inputStream, Class<T> tClass, ExcelListener<T> excelListener){
  51.         if(ObjectUtils.isEmpty(inputStream) || ObjectUtils.isEmpty(tClass) || ObjectUtils.isEmpty(excelListener)){
  52.             return null;
  53.         }
  54.         ExcelReaderBuilder read = EasyExcel.read(inputStream, tClass, excelListener);
  55.                 //.excelType("手续费率导入11111.xlsx".contains(ExcelTypeEnum.XLSX.getValue()) ? ExcelTypeEnum.XLSX : ExcelTypeEnum.XLS);
  56.  
  57.         read.sheet().doRead();
  58.         return excelListener.getDatas();
  59.     }
  60.  
  61.     /**
  62.      * 读取Excel文件返回数据集合，不包含表头，读取第n个sheet数据，不设置sheet就读取全部个sheet数据，不设置sheet就读取全部
  63.      * @param inputStream 输入流
  64.      * @param tClass 数据映射类
  65.      * @param excelListener 读取监听类
  66.      * @return List 结果集
  67.      */
  68.     public static <T> List<T> readExcel(InputStream inputStream, Integer sheetNo, Class<T> tClass, ExcelListener<T> excelListener){
  69.         if(ObjectUtils.isEmpty(inputStream) || ObjectUtils.isEmpty(tClass) || ObjectUtils.isEmpty(excelListener)){
  70.             return null;
  71.         }
  72.         ExcelReaderBuilder read = EasyExcel.read(inputStream, tClass, excelListener);
  73.         if(ObjectUtils.isNotEmpty(sheetNo)){
  74.             read.sheet(sheetNo).doRead();
  75.         }else{
  76.             ExcelReader excelReader = read.build();
  77.             excelReader.readAll();
  78.             excelReader.finish();
  79.         }
  80.         return excelListener.getDatas();
  81.     }
  82. }

注意：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>

Base64DecodeMultipartFile

  1. package com.sinosoft.microservice.util;
  2.  
  3. import org.springframework.web.multipart.MultipartFile;
  4. import sun.misc.BASE64Decoder;
  5.  
  6. import java.io.*;
  7.  
  8. /**
  9.  * base64转为multipartFile工具类
  10.  *
  11.  */
  12.  
  13. public class Base64DecodeMultipartFile implements MultipartFile {
  14.  
  15.     private final byte[] imgContent;
  16.     private final String header;
  17.  
  18.     public Base64DecodeMultipartFile(byte[] imgContent, String header) {
  19.         this.imgContent = imgContent;
  20.         this.header = header.split(";")[0];
  21.     }
  22.  
  23.  
  24.     @Override
  25.     public String getName() {
  26.         return System.currentTimeMillis() + Math.random() + "." + header.split("/")[1];
  27.     }
  28.  
  29.     @Override
  30.     public String getOriginalFilename() {
  31.         return System.currentTimeMillis() + (int) Math.random() * 10000 + "." + header.split("/")[1];
  32.     }
  33.  
  34.     @Override
  35.     public String getContentType() {
  36.         return header.split(":")[1];
  37.     }
  38.  
  39.     @Override
  40.     public boolean isEmpty() {
  41.         return imgContent == null || imgContent.length == 0;
  42.     }
  43.  
  44.     @Override
  45.     public long getSize() {
  46.         return imgContent.length;
  47.     }
  48.  
  49.     @Override
  50.     public byte[] getBytes() throws IOException {
  51.         return imgContent;
  52.     }
  53.  
  54.     @Override
  55.     public InputStream getInputStream() throws IOException {
  56.         return new ByteArrayInputStream(imgContent);
  57.     }
  58.  
  59.     @Override
  60.     public void transferTo(File dest) throws IOException, IllegalStateException {
  61.         new FileOutputStream(dest).write(imgContent);
  62.     }
  63.  
  64.  
  65.     /**
  66.      * base64转multipartFile
  67.      *
  68.      * @param base64
  69.      * @return
  70.      */
  71.     public static MultipartFile base64Convert(String base64) {
  72.         String[] baseStrs = base64.split(",");
  73.         BASE64Decoder decoder = new BASE64Decoder();
  74.         byte[] b = new byte[0];
  75.         try {
  76.             b = decoder.decodeBuffer(baseStrs[1]);
  77.         } catch (IOException e) {
  78.             e.printStackTrace();
  79.         }
  80.         for (int i = 0; i < b.length; ++i) {
  81.             if (b[i] < 0) {
  82.                 b[i] += 256;
  83.             }
  84.         }
  85.         return new Base64DecodeMultipartFile(b, baseStrs[0]);
  86.     }
  87. }

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/菜鸟追梦旅行/article/detail/591755
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号