使用Java通过POI读取EXCEL中的数据

桃扇骨 2023-10-05 11:29 56阅读 0赞

这里有个Excel，怎么使用java读取excel中的数据呢？

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkbWluMTIzNDA0_size_16_color_FFFFFF_t_70][]

文件存放位置：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkbWluMTIzNDA0_size_16_color_FFFFFF_t_70 1][]

首先引入poi的jar包

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.17</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
            </dependency>

我这里写了一个People类，目的是想把Excel中的数据读取出来后转成这个类，方便后续进行操作。

@Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class People {
        private String name;
        private String age;
        private String sex;
        private String area;
    }

然后看代码：

/**
     * FileName: MyExcelTest
     * Author:  zp
     * Date:    2020/2020/10/11/10:16
     * Description:
     */
    package excel;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * Description: 
     * @author zpzp6
     * @create 2020/2020/10/11/10:16
     * @since 1.0.0
     */
    public class MyExcelTest {
        public static void main(String[] args) throws IOException {
            //获取工作簿
            XSSFWorkbook book = new XSSFWorkbook("E:\\我的文件\\测试\\测试.xlsx");
            //获取工作表
            XSSFSheet sheet = book.getSheetAt(0);
    //        //第一种读取读取所有数据，实际中不需要
    //        //获取行
    //        for (Row cells : sheet) {
    //            //获取单元格
    //            for (Cell cell : cells) {
    //                //获取单元格中的内容
    //                cell.setCellType(CellType.STRING);
    //                System.out.println(cell.getStringCellValue());
    //            }
    //        }
            List<People> peopleList=new ArrayList<>();
            //普通for循环
            //开始索引0  结束索引
            int lastRowNum = sheet.getLastRowNum();
            System.out.println("最后一行："+lastRowNum);
            for (int i = 1; i <= lastRowNum; i++) {
                //获取单元格
                XSSFRow row = sheet.getRow(i);
                if(row!=null){
                    List<String> list =new ArrayList<>();
                    for (Cell cell : row) {
                        if(cell!=null && !"".equals(cell)){
                            //此处是把单元格都转换成String类型
                            cell.setCellType(CellType.STRING);
                            String cellValue = cell.getStringCellValue();
                            System.out.println("单元格数据："+cellValue);
                            list.add(cellValue);
                        }
                    }
                    if(list.size()>0){
                        People people = new People(list.get(0), list.get(1), list.get(2), list.get(3));
                        peopleList.add(people);
                    }
                }
    
    
            }
            for (People people : peopleList) {
                System.out.println(people);
            }
            //释放资源
            book.close();
        }
    
    }

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkbWluMTIzNDA0_size_16_color_FFFFFF_t_70 2][]

如果有这样的报错是因为打开了Excel文件，关闭就好。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkbWluMTIzNDA0_size_16_color_FFFFFF_t_70 3][]

测试的写得差不多了。那么就来点正式的。

控制层：

@ApiOperation("读取资源文件")
        @PostMapping("/read-file")
        public List<PartyMember> readFile(@RequestParam(required = false) String path, @RequestParam(required = false) MultipartFile file) throws Exception
        {
            return new PartyMember().getExcelData(file);
        }

MultipartFile： 前端可以把excel上传，后端通过此MultipartFile来接收。path可以不写

/**
         * @return * @param null
         * @Author
         * @Description //TODO
         * @Date 2019/8/15 12:14
         * @Param file ：上传的excel文件
         */
        @Transactional
        public List<PartyMember> getExcelData(MultipartFile file) throws IOException {
            List<PartyMember> partyMembers = new ArrayList<>();
            PartyMember member = new PartyMember();
            SnapMember snapMember = new SnapMember();
            String fileName = checkFile(file);
            //获得Workbook工作薄对象
            Workbook workbook = getWorkBook(file);
            //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
            List<List<String>> list = new ArrayList<>();
            if (workbook != null) {
                for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                    //获得当前sheet工作表
                    Sheet sheet = workbook.getSheetAt(sheetNum);
                    if (sheet == null) {
                        continue;
                    }
                    //获得当前sheet的开始行
                    int firstRowNum = sheet.getFirstRowNum();
                    //获得当前sheet的结束行
                    int lastRowNum = sheet.getLastRowNum();
                    //循环除了所有行,如果要循环除第一行以外的就firstRowNum+1
                    for (int rowNum = firstRowNum + 2; rowNum <= lastRowNum; rowNum++) {
                    //业务逻辑
                    }
                       
                }
                workbook.close();
             
            }
            return partyMembers;
        }

/**
         * 检查文件
         *
         * @param file
         * @throws IOException
         */
        public static String checkFile(MultipartFile file) throws IOException {
            //判断文件是否存在
            if (null == file) {
                throw new CustomException("文件不存在！",HttpStatus.BAD_REQUEST);
            }
            //获得文件名
            String fileName = file.getOriginalFilename();
            //判断文件是否是excel文件
            if (!StringUtils.lowerCase(fileName).endsWith("xls") && !StringUtils.lowerCase(fileName).endsWith("xlsx")) {
                throw new CustomException("不是excel文件",HttpStatus.BAD_REQUEST);
            }
            return fileName;
        }
    
        public static Workbook getWorkBook(MultipartFile file) {
            //获得文件名
            String fileName = file.getOriginalFilename();
            //创建Workbook工作薄对象，表示整个excel
            Workbook workbook = null;
            try {
                //获取excel文件的io流
                InputStream is = file.getInputStream();
                //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
                if (StringUtils.lowerCase(fileName).endsWith("xls")) {
                    //2003
                    workbook = new HSSFWorkbook(is);
                } else if (StringUtils.lowerCase(fileName).endsWith("xlsx")) {
                    //2007 及2007以上
                    workbook = new XSSFWorkbook(is);
                }
            } catch (IOException e) {
                e.getMessage();
            }
            return workbook;
        }
    
        public static String getCellValue(Cell cell) {
            String cellValue = "";
            if (cell == null) {
                return cellValue;
            }
            //判断数据的类型
            //判断数据的类型
            switch (cell.getCellTypeEnum()) {
                case NUMERIC: //数字
                    cellValue = stringDateProcess(cell);
                    break;
                case STRING: //字符串
                    cellValue = String.valueOf(cell.getStringCellValue());
                    break;
                case BOOLEAN: //Boolean
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA: //公式
                    cellValue = String.valueOf(cell.getCellFormula());
                    break;
                case BLANK: //空值
                    cellValue = "";
                    break;
                case ERROR: //故障
                    cellValue = "非法字符";
                    break;
                default:
                    cellValue = "未知类型";
                    break;
            }
            return cellValue;
        }
    
    
        public static String stringDateProcess(Cell cell) {
            String result = new String();
            if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                SimpleDateFormat sdf = null;
                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                    sdf = new SimpleDateFormat("HH:mm");
                } else {// 日期
                    sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                }
                Date date = cell.getDateCellValue();
                result = sdf.format(date);
            } else if (cell.getCellStyle().getDataFormat() == 58) {
                // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                double value = cell.getNumericCellValue();
                Date date = org.apache.poi.ss.usermodel.DateUtil
                        .getJavaDate(value);
                result = sdf.format(date);
            } else {
                double value = cell.getNumericCellValue();
                CellStyle style = cell.getCellStyle();
                DecimalFormat format = new DecimalFormat();
                String temp = style.getDataFormatString();
                // 单元格设置成常规
                if (temp.equals("General")) {
                    format.applyPattern("#");
                }
                result = format.format(value);
            }
    
            return result;
        }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkbWluMTIzNDA0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201011112020211.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkbWluMTIzNDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkbWluMTIzNDA0_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2020101111284950.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkbWluMTIzNDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkbWluMTIzNDA0_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201011113105253.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkbWluMTIzNDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkbWluMTIzNDA0_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201011113203533.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkbWluMTIzNDA0,size_16,color_FFFFFF,t_70