应用 POI 解析 Excel 表格并批量上传到数据库

曾经终败给现在 2023-10-11 15:30 46阅读 0赞

在日常生活中，我们时常会遇到添加用户之类的操作，但是这类操作，只能一位一位的添加。遇到向我这种强迫症晚期患者，会被烦死… 那么应用 POI 解析含有用户信息的 EXCEL 表格会省很多时间。本文针对解析 EXCEL 表格以及将表格上的内容批量上传到数据库提供一项实用的解决方案。  
![在这里插入图片描述][20210407215428723.png]

--------------------

#### 目录 ####

*   *  一、导入 pom 坐标
     *  二、编写解析 Excel 表格的工具类
     *  三、批量上传至服务器
     *  四、整体代码
     *  五、整体效果演示
     *  六、补充

--------------------

### 一、导入 pom 坐标 ###

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>joda-time</groupId>
      <artifactId>joda-time</artifactId>
      <version>2.10.1</version>
    </dependency>

--------------------

### 二、编写解析 Excel 表格的工具类 ###

创建 ParseExcelUtil 工具类，编写解析指定对象表格的静态方法。方法传入的参数分别是上传文件的路径，以及文件名称。

--------------------

**为解析 EXCEL 做准备工作：**

1.  获取解析文件的输入流。
2.  根据文件类型（xls、xlsx）创建一个工作簿。（这样两种xls、xlsx后缀的表格都会被解析）
3.  获取的第一个sheet。（可根据索引检索指定的sheet）
4.  设置存储 list 集合。

**对 EXCEL 进行解析流程如下：**  
![在这里插入图片描述][20210407223401354.png]

--------------------

import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.text.DateFormat;
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    
    public class ParseExcelUtil {
        
    
        private static InputStream in;
        private static Workbook workbook;
        private static Sheet sheet;
    
        public static List<Coach> parseCoachExcel(String path, String fileName) throws IOException, ParseException {
        
            //1.获取解析文件的输入流
            in = new FileInputStream(path+fileName);
            
            //2. 根据文件类型（xls、xlsx）创建一个工作簿，使用excel能操作的这边他都可以操作
            if(fileName.contains("xlsx")){
        
                workbook = new XSSFWorkbook(in);
            }else{
        
                workbook = new HSSFWorkbook(in);
            }
    
            //3. 得到表，第一个sheet
            sheet = workbook.getSheetAt(0);
    
            //设置存储list
            List<Coach> list = new ArrayList<Coach>();
    
    		//这里对第一行标题不解析，如果要解析，那么就需要将索引值改写为0
            for(int rowIndex = 1; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex ++){
        
                //4. 得到行
                Row row = sheet.getRow(rowIndex);
                if (row == null){
        //非空判断
                    continue;
                }
                Coach coach = new Coach();//创建coach对象
                for(int cellIndex = 0; cellIndex < row.getPhysicalNumberOfCells(); cellIndex ++){
        
                    //5. 得到列
                    Cell cell = row.getCell(cellIndex);
                    if(cell==null){
        
                        continue;
                    }
                    int cellType = cell.getCellType();
                    String cellValue = "";
                    switch (cellType){
        
                        case HSSFCell.CELL_TYPE_STRING: //字符串
                            cellValue = cell.getStringCellValue();
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN: //布尔
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_BLANK: //空
                            System.out.print("空！！");
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC: //数字（日期、普通数字）
                            if(HSSFDateUtil.isCellDateFormatted(cell)){
        //日期
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                                cellValue = sdf.format(date);
                            }else{
         //普通数字
                                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                cellValue = cell.toString();
                            }
                            break;
                        case HSSFCell.CELL_TYPE_ERROR: //错误
                            cellValue = "错误";
                            break;
                    }
                    DateFormat format=new SimpleDateFormat("yyyy-MM-dd");
                    switch (cellIndex){
        
                        case 0://number
                            coach.setNumber((String) cellValue);
                            break;
                        case 1://name
                            coach.setName((String)cellValue);
                            break;
                        case 2://sex
                            coach.setSex((String)cellValue);
                            break;
                        case 3://courseNumber
                            coach.setCourseNumber((String)cellValue);
                            break;
                        case 4://birthday
                            coach.setBirthday(format.parse(cellValue));
                            break;
                        case 5://phone
                            coach.setPhone((String)cellValue);
                            break;
                        case 6://ResponsibleClass
                            coach.setResponsibleClass((String)cellValue);
                            break;
                        case 7://registrationDate
                            coach.setRegistrationDate(format.parse(cellValue));
                            break;
                        case 8://effectiveDeadline
                            coach.setEffectiveDeadline(format.parse(cellValue));
                            break;
                    }
                }
                list.add(coach);
            }
            //输入流关闭
            in.close();
            //上传文件删除
            File fileDelete = new File(path+fileName);
            fileDelete.delete();
            
            return list;
        }
    }

--------------------

### 三、批量上传至服务器 ###

Dao 层：

@Insert("<script>"  +
            "insert into coach(id, number, name, sex, courseNumber, birthday, phone, responsibleClass, registrationDate, effectiveDeadline) VALUES " +
            "<foreach collection='list' item='item' index='index' separator=','> " +
            "(null,#{item.number},#{item.name}, #{item.sex}, #{item.courseNumber}, #{item.birthday}, #{item.phone}, #{item.responsibleClass}, #{item.registrationDate}, #{item.effectiveDeadline}) " +
            "</foreach>" +
            "</script>")
    void insertCoachOfBatch(List<Coach> coachList);

注：这里以个人项目中的 Coach 实体类对象进行举例，后文会给出 Coach 对象的实体类代码，读者做参考即可。

--------------------

### 四、整体代码 ###

前端 jsp：

<div class="modal fade" id="myModal-upload" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" data-backdrop="false">
    	<div class="modal-dialog" role="document" id="uploadPopup">
    		<div class="modal-content">
    			<div class="modal-header">
    				<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
    				<h4 class="modal-title" id="myModalLabel-4">批量导入数据</h4>
    			</div>
    			<form class="form-horizontal" id="upload-form" action="${pageContext.request.contextPath}/admin/coach/upload" enctype="multipart/form-data" method="post">
    				<div class="form-group">
    					<label for="chooseFileUpload" class="col-sm-3 control-label" >选择文件</label>
    					<div class="col-sm-9" >
    						<input type="file" class="" id="chooseFileUpload" placeholder=""  name="upload" />
    					</div>
    				</div>
    				<div class="form-group">
    					<div class="col-xs-offset-4 col-sm-offset-4 col-xs-7 col-sm-7" style="color: red; text-align: right;">
    						注意：指定文件格式！
    					</div>
    				</div>
    			</form>
    			<div class="modal-footer">
    				<button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove" aria-hidden="true"></span> 关闭</button>
    				<button type="button" id="btn_submit_upload" class="btn btn-primary" data-dismiss="modal"> 上传</button>
    			</div>
    		</div>
    	</div>
    </div>
    <script>
    	$(function (){
          
    		//文件上传
    		$('#uploadBtn').click(function () {
          
    			$('#myModal-upload').modal();
    		});
    		$('#btn_submit_upload').click(function () {
          
    			$('#upload-form').submit();
    		});
    	})
    </script>

页面效果如下：  
![在这里插入图片描述][20210407224925165.png_pic_left]

--------------------

Controller层:

@Controller
    @RequestMapping("/admin/coach")
    public class CoachController {
        
    
    	@Autowired
        private CoachService coachService;
        
    
        @RequestMapping(path = "/upload")
        public String fileUpload(HttpServletRequest request, MultipartFile upload) throws Exception {
        
            //使用 fileupload 组件完成文件上传
            String path = request.getSession().getServletContext().getRealPath("/upload/");//文件的上传位置
            //判断该路径是否存在
            File file = new File(path);
            if(!file.exists()){
        
                //创建该文件夹
                file.mkdirs();
            }
            //说明上传文件项
            //获取上传文件的名称
            String fileName = upload.getOriginalFilename();
            //完成文件上传
            upload.transferTo(new File(path, fileName));
            coachService.parsingExcel(path, fileName);
    
            return "admin/fileUploadSuccess";
        }
    }

--------------------

Service层:

@Service("coachService")
    public class CoachServiceImpl implements CoachService {
        
    
        @Autowired
        private CoachDao coachDao;
    	
    	@Override
        public void parsingExcel(String path, String fileName) throws IOException, ParseException {
        
            List<Coach> coachList = ParseExcelUtil.parseCoachExcel(path, fileName);
            coachDao.insertCoachOfBatch(coachList);
        }
    }

--------------------

Coach实体类 :

import com.fasterxml.jackson.annotation.JsonFormat;
    import org.springframework.format.annotation.DateTimeFormat;
    import java.io.Serializable;
    import java.util.Date;
    
    public class Coach implements Serializable {
        
    
        private static final long serialVersionUID = 1L;
        private Integer id;
        private String number;
        private String name;
        private String sex;
        private String courseNumber;
        private String phone;
        private String responsibleClass;
    
        @JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
        @DateTimeFormat(pattern = "yyyy-MM-dd")
        private Date birthday;
    
        @JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
        @DateTimeFormat(pattern = "yyyy-MM-dd") //添加数据的时候防止格式不符
        private Date registrationDate;
    
        @JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
        @DateTimeFormat(pattern = "yyyy-MM-dd")
        private Date effectiveDeadline;
    
        //一对一关联
        private Course course;
    
    
        public static long getSerialVersionUID() {
        
            return serialVersionUID;
        }
    
        public Integer getId() {
        
            return id;
        }
    
        public void setId(Integer id) {
        
            this.id = id;
        }
    
        public String getNumber() {
        
            return number;
        }
    
        public void setNumber(String number) {
        
            this.number = number;
        }
    
        public String getName() {
        
            return name;
        }
    
        public void setName(String name) {
        
            this.name = name;
        }
    
        public String getSex() {
        
            return sex;
        }
    
        public void setSex(String sex) {
        
            this.sex = sex;
        }
    
        public String getCourseNumber() {
        
            return courseNumber;
        }
    
        public void setCourseNumber(String courseNumber) {
        
            this.courseNumber = courseNumber;
        }
    
        public String getPhone() {
        
            return phone;
        }
    
        public void setPhone(String phone) {
        
            this.phone = phone;
        }
    
        public String getResponsibleClass() {
        
            return responsibleClass;
        }
    
        public void setResponsibleClass(String responsibleClass) {
        
            this.responsibleClass = responsibleClass;
        }
    
        public Date getBirthday() {
        
            return birthday;
        }
    
        public void setBirthday(Date birthday) {
        
            this.birthday = birthday;
        }
    
        public Date getRegistrationDate() {
        
            return registrationDate;
        }
    
        public void setRegistrationDate(Date registrationDate) {
        
            this.registrationDate = registrationDate;
        }
    
        public Date getEffectiveDeadline() {
        
            return effectiveDeadline;
        }
    
        public void setEffectiveDeadline(Date effectiveDeadline) {
        
            this.effectiveDeadline = effectiveDeadline;
        }
    
        public Course getCourse() {
        
            return course;
        }
    
        public void setCourse(Course course) {
        
            this.course = course;
        }
    
        @Override
        public String toString() {
        
            return "Coach{" +
                    "id=" + id +
                    ", number='" + number + '\'' +
                    ", name='" + name + '\'' +
                    ", sex='" + sex + '\'' +
                    ", courseNumber='" + courseNumber + '\'' +
                    ", phone='" + phone + '\'' +
                    ", responsibleClass='" + responsibleClass + '\'' +
                    ", birthday=" + birthday +
                    ", registrationDate=" + registrationDate +
                    ", effectiveDeadline=" + effectiveDeadline +
                    ", course=" + course +
                    '}';
        }
    }

--------------------

### 五、整体效果演示 ###

![在这里插入图片描述][20210304125852127.gif_pic_center]

--------------------

### 六、补充 ###

当我们在上传文件的时候，**对日期类型解析会失败**。因为 POI 解析的是指定格式的表格，所以对日期字符串格式识别不出，因此需要对日期字段的格式进行指定类型为日期。

![在这里插入图片描述][20210407225920866.png_pic_left]

--------------------

对 POI 解析 EXCEL 表格的相关操作，就先解析到这里。POI 解析EXCEL存在一定的局限性，比如要将数据所对应的列提前指定等。 如果读者还对 POI 解析 EXCEL 表格操作中出现问题，欢迎大家在评论区留言哦~

[20210407215428723.png]: https://img-blog.csdnimg.cn/20210407215428723.png
[20210407223401354.png]: https://img-blog.csdnimg.cn/20210407223401354.png
[20210407224925165.png_pic_left]: https://img-blog.csdnimg.cn/20210407224925165.png?#pic_left
[20210304125852127.gif_pic_center]: https://img-blog.csdnimg.cn/20210304125852127.gif#pic_center
[20210407225920866.png_pic_left]: https://img-blog.csdnimg.cn/20210407225920866.png#pic_left