SpringBoot 整合POI

IDEA

JDK8

一、项目结构

二、配置文件

application.yml

spring:
  profiles:
    active: dev

application-dev.yml

server:
  port: 8081
  servlet:
    context-path: /hospital
spring:
  datasource:
    druid:
      url: jdbc:mysql://localhost:3306/hospital?zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
      username: root
      password: 1234
      driver-class-name: com.mysql.jdbc.Driver
  application:
    name: hospital
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: update
logging:
  level:
    root: info

三、POM 依赖

 四、创建代码

Role.java

package com.dxj.hospital.domain;

import lombok.NoArgsConstructor;
import lombok.Setter;

import javax.persistence.*;
import java.sql.Timestamp;
/**
 * 角色
 * @author Administrator
 */
@Entity
@Table(name = "role_tab")
@Setter
@NoArgsConstructor
public class Role {
    @Id
     @GeneratedValue(strategy = GenerationType.IDENTITY)
     @Column(name = "r_id")
    Integer id;
    @Column(name = "r_name")
    String name;
    @Column(name = "r_by")
    String by;
    @Column(name = "r_time")
    Timestamp time;
    {
        this.time=new Timestamp(System.currentTimeMillis());
    }

    public Role(Integer id, String name, String by) {
        this.id = id;
        this.name = name;
        this.by = by;
    }

    public Integer getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getBy() {
        return by;
    }

    public Timestamp getTime() {
        return new Timestamp(System.currentTimeMillis());
    }
}

RoleDao.java

package com.dxj.hospital.dao;

import com.dxj.hospital.domain.Role;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * 角色 数据持久层
 * @author Administrator
 */
public interface RoleDao extends JpaRepository<Role,Integer> {
}

RoleService.java

package com.dxj.hospital.service;

import com.dxj.hospital.domain.Role;

import java.util.List;

/**
 * 角色 服务
 * @author Administrator
 */
public interface RoleService {
    /**
     * 添加
     * @param role
     */
    void addRole(Role role);

    /**
     * 修改
     * @param role
     */
    void updateRole(Role role);

    /**
     * id 删除
     * @param id
     */
    void deleteRoleById(Integer id);

    /**
     * 全查询
     * @return
     */
    List<Role> findAllRole();
}

RoleServiceImpl.java

package com.dxj.hospital.service.impl;

import com.dxj.hospital.dao.RoleDao;
import com.dxj.hospital.domain.Role;
import com.dxj.hospital.service.RoleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
/**
 * 角色 服务实现
 * @author Administrator
 */
@Service
public class RoleServiceImpl implements RoleService {
    @Autowired
    private RoleDao roleDao;
    /**
     * 添加
     *
     * @param role
     */
    @Override
    public void addRole(Role role) {
        roleDao.save(role);
    }

    /**
     * 修改
     *
     * @param role
     */
    @Override
    public void updateRole(Role role) {
        roleDao.save(role);
    }

    /**
     * id 删除
     *
     * @param id
     */
    @Override
    public void deleteRoleById(Integer id) {
       roleDao.deleteById(id);
    }

    /**
     * 全查询
     *
     * @return
     */
    @Override
    public List<Role> findAllRole() {
        return roleDao.findAll();
    }
}

ExcelService.java

package com.dxj.hospital.service;


import java.io.IOException;
import java.text.ParseException;

/**
 * excel
 * @author Administrator
 */
public interface ExcelService {
    /**
     *导出 Mysql
     * @throws Exception
     */
    void poiWrite() throws Exception;

    /**
     * 导入 Mysql
     * @throws Exception
     */
    void poiRead() throws Exception;


}

ExcelServiceImpl.java

package com.dxj.hospital.service.impl;

import com.dxj.hospital.dao.RoleDao;
import com.dxj.hospital.domain.Role;
import com.dxj.hospital.service.ExcelService;
import com.dxj.hospital.service.RoleService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @author Administrator
 */
@Service
public class ExcelServiceImpl implements ExcelService {
    @Autowired
    private RoleDao roleDao;
    /**
     * 导出 Mysql
     */
    @Override
    public void poiWrite() throws Exception{
        List<Role> roles=roleDao.findAll();
        File file = new File("D://excel//hospital.xlsx");
        HSSFWorkbook excel = new HSSFWorkbook();
        HSSFSheet sheet = excel.createSheet("角色");

        int i = 0;
        for (Role d : roles) {
            HSSFRow row = sheet.createRow(i++);
            HSSFCell cell0 = row.createCell(0);
            HSSFCell cell1 = row.createCell(1);
            HSSFCell cell2 = row.createCell(2);
            HSSFCell cell3 = row.createCell(3);
            if (d.getId() != null) {
                cell0.setCellValue(d.getId());
            }
            if (d.getName() != null) {
                cell1.setCellValue(d.getName());
            }
            if (d.getBy() != null) {
                cell2.setCellValue(d.getBy());
            }
            if (d.getTime() != null) {
                Timestamp createTime = d.getTime();
                String l = createTime.toString();
                String str = createTime.toString().substring(0, l.lastIndexOf("."));
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                Date date = sdf.parse(str);
                cell3.setCellValue(date);
            }
        }
        excel.write(file);
    }

    /**
     * 导入 Mysql
     */
    @Override
    public void poiRead()  throws Exception {
        ExcelServiceImpl excelService=new ExcelServiceImpl();
        File file = new File("D://excel//hospital.xlsx");
        HSSFWorkbook excel = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet sheet = excel.getSheet("角色");
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i <= lastRowNum; i++) {
            HSSFRow row = sheet.getRow(i);
            String name = (String) getValue(row.getCell(1));
            String by = (String) getValue(row.getCell(2));
            roleDao.save(new Role(null, name, by));
        }
    }
    /**
     * Excel导入MySql时，判断excel中的数据类型
     * @param hssfCell
     * @return
     * @throws Exception
     */
    private static Object getValue(HSSFCell hssfCell) throws Exception {
        Object object = null;
        switch (hssfCell.getCellTypeEnum()) {
            case STRING:
                object = hssfCell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(hssfCell)) {
                    object = hssfCell.getDateCellValue();
                } else {
                    object = hssfCell.getNumericCellValue();
                }
                break;
            case BOOLEAN:
                object = hssfCell.getBooleanCellValue();
                break;
            case FORMULA:
                object = hssfCell.getCellFormula();
                break;
            case BLANK:
                break;
        }
        return object;
    }
}

POIController.java

package com.dxj.hospital.controller;

import com.dxj.hospital.service.ExcelService;
import com.dxj.hospital.util.HttpResp;
import com.dxj.hospital.util.RespCode;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

/**
 * @author Administrator
 */
@RestController
@RequestMapping("/poi")
public class POIController {
    @Autowired
    private ExcelService excelService;

    @GetMapping("/poiWrite")
    public HttpResp poiWrite() throws Exception {
        excelService.poiWrite();
        return new HttpResp(RespCode.RESP_SUCCESS.getCode(),RespCode.RESP_SUCCESS.getMessage(),
                0,null);
    }
    @PutMapping("/poiRead")
    public HttpResp poiRead() throws Exception {
        excelService.poiRead();
        return new HttpResp(RespCode.RESP_SUCCESS.getCode(),RespCode.RESP_SUCCESS.getMessage(),
                0,null);
    }
}

HospitalConfiguration.java

package com.dxj.hospital.configuration;

import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.CorsRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

/**
 * 跨域 http通信 configuration
 * @author Administrator
 */
@Configuration
public class HospitalConfiguration implements WebMvcConfigurer {
    /**
     * 配置注册跨域映射
     *
     * @param registry
     */
    @Override
    public void addCorsMappings(CorsRegistry registry) {
        //设置允许跨域路径
        registry.addMapping("/**")
                //设置允许跨域请求的域名
                .allowedOrigins("*")
                // 是否允许证书 不再默认开启
                .allowCredentials(true)
                // 设置允许的方法
                .allowedMethods("GET", "POST", "PUT", "DELETE")
                // 跨域允许时间
                .maxAge(3600);

    }
}

HttpResp.java

package com.dxj.hospital.util;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * 响应类
 * @author Administrator
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class HttpResp {
    int code;
    String message;
    int count;
    Object data;
}

RespCode.java

package com.dxj.hospital.util;

/**
 * 响应信息
 * @author Administrator
 */

public enum RespCode {
    RESP_SUCCESS(200,"响应成功"),
    RESP_ERROR(201,"响应失败");
    private int code;
    private String message;

    RespCode(int code, String message) {
        this.code = code;
        this.message = message;
    }

    public int getCode() {
        return code;
    }

    public String getMessage() {
        return message;
    }
}

五.Postman 测试 POIController.java