读取Excel文件存储在实体类中

爱被打了一巴掌 2021-12-14 14:37 316阅读 0赞

1.Maven文件

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.15</version>
            </dependency>
            
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.15</version>
            </dependency>
            
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-examples</artifactId>
                <version>3.15</version>
            </dependency>
            
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-excelant</artifactId>
                <version>3.15</version>
            </dependency>
            
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>3.15</version>
            </dependency>

2.实体类对象

1 package com.mz.monotoring.Domain;
     2 
     3 /**
     4  * @author sjl
     5  * @date 2019-03-28 10:09
     6  */
     7 public class User {
     8     private String name;
     9 
    10     private String sex;
    11 
    12     private String age;
    13 
    14     private String money;
    15 
    16     public String getName() {
    17         return name;
    18     }
    19 
    20     public void setName(String name) {
    21         this.name = name;
    22     }
    23 
    24     public String getSex() {
    25         return sex;
    26     }
    27 
    28     public void setSex(String sex) {
    29         this.sex = sex;
    30     }
    31 
    32     public String getAge() {
    33         return age;
    34     }
    35 
    36     public void setAge(String age) {
    37         this.age = age;
    38     }
    39 
    40     public String getMoney() {
    41         return money;
    42     }
    43 
    44     public void setMoney(String money) {
    45         this.money = money;
    46     }
    47 }

3.Controller层接收Base64位的Excel文件(Postman测试界面)

![1623047-20190328120419179-1530469986.jpg][]

4.Controller层

1 package com.mz.monotoring.Controller;
     2 
     3 import com.mz.monotoring.Domain.User;
     4 import com.mz.monotoring.Util.Base64File;
     5 import com.mz.monotoring.Util.ReadExcelUtils;
     6 import org.springframework.mock.web.MockMultipartFile;
     7 import org.springframework.stereotype.Controller;
     8 import org.springframework.web.bind.annotation.RequestBody;
     9 import org.springframework.web.bind.annotation.RequestMapping;
    10 import org.springframework.web.bind.annotation.ResponseBody;
    11 import org.springframework.web.multipart.MultipartFile;
    12 
    13 import java.io.File;
    14 import java.io.FileInputStream;
    15 import java.io.IOException;
    16 import java.util.List;
    17 import java.util.Map;
    18 import java.util.UUID;
    19 
    20 /**
    21  * @author sjl
    22  * @date 2019-03-26 13:56
    23  */
    24 @Controller
    25 public class ExcelController {
    26 
    27     @RequestMapping("excel")
    28     @ResponseBody
    29     public List<User> excel(@RequestBody Map map) {
    30         List<User> excelInfo = null;
    31         //读取到的Excel保存的位置
    32         //linux保存路径
    33         //String path1 = "/usr/local/tomcatusps/apache-tomcat-8.5.15/Root/excel/";
    34         try {
    35             //Window保存路径
    36             String path1 = "F:\\excel";
    37             File files = new File(path1);
    38             files.mkdir();
    39 
    40             String[] w1 = map.get("key").toString().split(",");
    41             String s1 = new StringBuilder().append(UUID.randomUUID().toString()).append(".").append("xlsx").toString();
    42             String s = new StringBuilder().append(path1).append("\\").append(s1).toString();
    43             Base64File.decoderBase64File(w1[1], path1 + "\\" + s1);
    44             ReadExcelUtils excelUtils = new ReadExcelUtils();
    45             File newFile = new File(s);
    46             FileInputStream input = new FileInputStream(newFile);
    47             MultipartFile multipartFile = new MockMultipartFile("excelUtils", newFile.getName(), "text/plain", input);
    48 
    49             excelInfo = excelUtils.getExcelInfo(multipartFile);
    50         } catch (IOException e) {
    51             e.printStackTrace();
    52         }
    53         return excelInfo;
    54     }
    55 }

5.Base64File将读取到的base64位字符串转化为文件存储至本地

1 package com.mz.monotoring.Util;
     2 
     3 import sun.misc.BASE64Decoder;
     4 
     5 import java.io.FileOutputStream;
     6 import java.io.IOException;
     7 
     8 public class Base64File {
     9 
    10     /**
    11      * 将base64字符解码保存文件
    12      *
    13      * @param base64Code
    14      * @param targetPath
    15      * @throws Exception
    16      */
    17 
    18     public static void decoderBase64File(String base64Code, String targetPath) {
    19         byte[] buffer;
    20         FileOutputStream out = null;
    21         try {
    22             buffer = new BASE64Decoder().decodeBuffer(base64Code);
    23             out = new FileOutputStream(targetPath);
    24             out.write(buffer);
    25         } catch (IOException e) {
    26             e.printStackTrace();
    27         } finally {
    28             try {
    29                 if (out != null) {
    30                     out.close();
    31                 }
    32             } catch (IOException e) {
    33                 e.printStackTrace();
    34             }
    35         }
    36     }
    37 
    38 }

6.读取文件   判断是什么版本Excel并且循环读取每层数据存储实体类中

1 package com.mz.monotoring.Util;
      2 
      3 
      4 import com.mz.monotoring.Domain.User;
      5 import org.apache.poi.hssf.usermodel.HSSFCell;
      6 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
      7 import org.apache.poi.ss.usermodel.Cell;
      8 import org.apache.poi.ss.usermodel.Row;
      9 import org.apache.poi.ss.usermodel.Sheet;
     10 import org.apache.poi.ss.usermodel.Workbook;
     11 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
     12 import org.springframework.web.multipart.MultipartFile;
     13 
     14 import java.io.IOException;
     15 import java.io.InputStream;
     16 import java.util.*;
     17 
     18 /**
     19  * @author sjl
     20  * @date 2019-03-27 11:11
     21  */
     22 public class ReadExcelUtils {
     23     // 总行数
     24     private int totalRows = 0;
     25     // 总条数
     26     private int totalCells = 0;
     27     // 错误信息接收器
     28     private String errorMsg;
     29 
     30     //构造方法
     31     public ReadExcelUtils() {
     32     }
     33 
     34     public int getTotalRows() {
     35         return totalRows;
     36     }
     37 
     38     public int getTotalCells() {
     39         return totalCells;
     40     }
     41 
     42     public String getErrorMsg() {
     43         return errorMsg;
     44     }
     45 
     46 
     47     public List<User> getExcelInfo(MultipartFile mFile) {
     48         //获取文件名
     49         String fileName = mFile.getOriginalFilename();
     50         try {
     51             //验证文件名是否合格
     52             if (!validateExcel(fileName)) {
     53                 return null;
     54             }
     55             //根据文件名判断文件是2003版本还是2007版本
     56             boolean isExcel2003 = true;
     57             if (isExcel2007(fileName)) {
     58                 isExcel2003 = false;
     59             }
     60             return createExcel(mFile.getInputStream(), isExcel2003);
     61         } catch (IOException e) {
     62             e.printStackTrace();
     63         }
     64         return null;
     65     }
     66 
     67     /**
     68      * 根据excel里面的内容读取客户信息
     69      *
     70      * @param is          输入流
     71      * @param isExcel2003 excel是2003还是2007版本
     72      * @return
     73      * @throws IOException
     74      */
     75     private List<User> createExcel(InputStream is, boolean isExcel2003) {
     76         try {
     77             Workbook wb = null;
     78             //当excel是2003时,创建excel2003
     79             if (isExcel2003) {
     80                 wb = new HSSFWorkbook(is);
     81             } else {
     82                 wb = new XSSFWorkbook(is);
     83             }
     84             //读取Excel里面的信息
     85             return readExcelValue(wb);
     86         } catch (IOException e) {
     87             e.printStackTrace();
     88         }
     89         return null;
     90     }
     91 
     92     private List<User> readExcelValue(Workbook wb) {
     93         //得到都一个shell
     94         Sheet sheet = wb.getSheetAt(0);
     95         //得到Excel的行数
     96         this.totalRows = sheet.getPhysicalNumberOfRows();
     97         //得到Excel的列数(前提是有行数)
     98         if (totalRows >= 1 && sheet.getRow(0) != null) {
     99             this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
    100         }
    101         List<User> userList = new ArrayList<>();
    102         //循环Excel行数
    103         for (int r = 0; r < totalRows; r++) {
    104             Row row = sheet.getRow(r);
    105             if (row == null) {
    106                 continue;
    107             }
    108             //循环Excel的列
    109             Map<String, Object> map = new HashMap<>(16);
    110             if (isRowEmpty(row) == false) {
    111                 User user = new User();
    112                 for (int c = 0; c < this.totalCells; c++) {
    113                     Cell cell = row.getCell(c);
    114                     if (cell != null) {
    115                         if (c == 0) {
    116                             //判断其中是否包含空格
    117                             if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
    118                                 String name = String.valueOf(cell.getNumericCellValue());
    119                                 user.setName(name.substring(0, name.length() - 2 > 0 ? name.length() - 2 : 1));
    120                             } else if (cell.getStringCellValue() != null) {
    121                                 //导入姓名
    122                                 user.setName(cell.getStringCellValue());
    123                             } else {
    124                                 user.setName("");
    125                             }
    126                         } else if (c == 1) {
    127                             if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
    128                                 String sex = String.valueOf(cell.getNumericCellValue());
    129                                 user.setSex(sex.substring(0, sex.length() - 2 > 0 ? sex.length() - 2 : 1));
    130                             } else if (cell.getStringCellValue() != null) {
    131                                 //性别
    132                                 user.setSex(cell.getStringCellValue());
    133                             } else {
    134                                 user.setSex("");
    135                             }
    136                         } else if (c == 2) {
    137                             if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
    138                                 String age = String.valueOf(cell.getNumericCellValue());
    139                                 user.setAge(age.substring(0, age.length() - 2 > 0 ? age.length() - 2 : 1));
    140                             } else if (cell.getStringCellValue() != null) {
    141                                 //年龄
    142                                 user.setAge(cell.getStringCellValue());
    143                             } else {
    144                                 user.setAge("");
    145                             }
    146                         } else if (c == 3) {
    147                             if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
    148                                 String money = String.valueOf(cell.getNumericCellValue());
    149                                 user.setMoney(money.substring(0, money.length() - 2 > 0 ? money.length() - 2 : 1));
    150                             } else if (cell.getStringCellValue() != null) {
    151                                 //工资
    152                                 user.setMoney(cell.getStringCellValue());
    153                             } else {
    154                                 user.setMoney("");
    155                             }
    156                         }
    157                     }
    158                 }
    159                 //将读取到的数据添加到list集合中
    160                 userList.add(user);
    161             }
    162         }
    163 
    164 
    165         return userList;
    166     }
    167 
    168     /**
    169      * 判断EXCEL是否为空
    170      *
    171      * @param row
    172      * @return
    173      */
    174     private boolean isRowEmpty(Row row) {
    175         for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
    176             Cell cell = row.getCell(c);
    177             if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
    178                 return false;
    179             }
    180 
    181         }
    182         return false;
    183     }
    184 
    185     /**
    186      * 验证EXCEL文件
    187      *
    188      * @param fileName
    189      * @return
    190      */
    191     private boolean validateExcel(String fileName) {
    192         if (fileName == null || !(isExcel2003(fileName) || isExcel2007(fileName))) {
    193             errorMsg = "文件不是excel格式";
    194             return false;
    195         }
    196         return true;
    197     }
    198 
    199     /**
    200      * 是否是2007的excel，返回true是2007
    201      *
    202      * @param fileName
    203      * @return
    204      */
    205     private boolean isExcel2007(String fileName) {
    206         return fileName.matches("^.+\\.(?i)(xlsx)$");
    207     }
    208 
    209     /**
    210      * 是否是2003的excel，返回true是2003
    211      *
    212      * @param fileName
    213      * @return
    214      */
    215     private boolean isExcel2003(String fileName) {
    216         return fileName.matches("^.+\\.(?i)(xls)$");
    217     }
    218 }

7.读取的Excel文件内容截图

![1623047-20190328121202623-359450671.png][]

8.Postman测试返回的List<User>数据

![1623047-20190328121322367-1764002275.png][]

转载于:https://www.cnblogs.com/shijl/p/10614062.html

[1623047-20190328120419179-1530469986.jpg]: /images/20211214/40b1f64cdcb4426abfef1786da08a13f.png
[1623047-20190328121202623-359450671.png]: /images/20211214/5ec52025be704340808dd3c94c78616e.png
[1623047-20190328121322367-1764002275.png]: /images/20211214/56a302cc42824aba9bd3dbef505516d2.png