Java使用Hutool实现读取Excel数据并存入数据库中

java导入Excel表格数据

本文可参考：https://www.hutool.cn/docs/#/poi/Excel读取-ExcelReader

一，引入依赖

	<!-- 导入Excel,版本要兼容,不然报错的 -->
	<dependency>
         <groupId>org.apache.poi</groupId>
         <artifactId>poi-ooxml</artifactId>
         <version>4.1.2</version>
     </dependency>
     <dependency>
         <groupId>cn.hutool</groupId>
         <artifactId>hutool-all</artifactId>
         <version>4.1.2</version>
     </dependency>

二，实体类
创建实体类并生成get set方法

	private Integer id;  //id
	private String department; //部门名称
	private String daydate; //日期
	private Integer dailytarget; //日目标产量
	private String createname; //创建人
	private Date createdate; //创建时间
	private String updatename;  //修改人
	private Date updatedate; //修改时间

三，XML

    <insert id="insertDailyTarget" parameterType="SystemManage.Target.entity.DailyTarget">
        insert into dailytarget (
        department,
        daydate,
        dailytarget,
        createname,
        createdate
        )
        values
        <foreach collection="recordList" item="item" separator=",">
            (
            #{item.department},
            #{item.daydate},
            #{item.dailytarget},
            #{createname},
            getdate()
            )
        </foreach>
    </insert>

四，Dao

@Component
public interface DailyTargetDao {

    /**
     * 日目标批量导入
     * @param recordList
     * @return
     */
    public Integer insertDailyTarget(@Param("recordList") List<DailyTarget> recordList,@Param("createname") String createname);
}

五，Service

@Service
public class DailyTargetService {

    @Autowired
    private DailyTargetDao dailyTargetDao;

    /**
     * 日目标批量导入
     * @param recordList
     * @return
     */
    public Integer insertDailyTarget(List<DailyTarget> recordList,String createname){
        return dailyTargetDao.insertDailyTarget(recordList,createname);
    }
}

六，控制层

 	@ResponseBody
    @RequestMapping(value = "/excelImport")
    public Integer importExcel(@RequestParam("file") MultipartFile file, HttpSession session){
        int count = 0;
        if (file.isEmpty()) {
            System.out.println( "文件为空！");
            return count;
        }
        // 获取上传文件输入流
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
        } catch (Exception e) {
            //return ResponseData.fail(ResponseCodeEnum.ERROR_PARAM_INVALID);
            e.printStackTrace();
        }
        //调用用 hutool 方法读取数据 默认调用第一个sheet
        ExcelReader excelReader = ExcelUtil.getReader(inputStream);
        //execl标题与Bean字段名对应
        excelReader.addHeaderAlias("部门名称","department");
        excelReader.addHeaderAlias("日期","daydate");
        excelReader.addHeaderAlias("日目标产量","dailytarget");
        //读取为Bean列表，Bean中的字段名为标题，字段值为标题对应的单元格值。
        List<DailyTarget> recordList = excelReader.readAll(DailyTarget.class);

        if(recordList.size() > 0){
            count = dailyTargetService.insertDailyTarget(recordList,(String)session.getAttribute("name"));
        }
        return count;
    }

七，数据库结构

create table dailytarget(
    id int identity(1,1) not null,
	department varchar(30),
	daydate varchar(30) not null,
	dailytarget int not null,
	createname varchar(30),
	createdate datetime,
	updatename varchar(30),
	updatedate datetime
)

八，Excel表格数据

九，运行结果