使用POI实现excle与mysql数据库的导入导出

Java小技术 专栏收录该内容
4 篇文章 0 订阅

目录

一.实现环境

二.POI简介

三.本博客实现的小目标

四.具体实现步骤

1.项目的基本结构

2.引入相应的jar包依赖(pom.xml的配置):

3.jdbc.properties与spring-jdbc.xml的配置(根据自己的mysql自行修改)

4.实现增删改查的功能接口

5.接口的实现类


一.实现环境

Spring + idea2018 + jdk1.8 + excel2013 + mysql数据库

二.POI简介

Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。是纯java技术.其主要的基本功能模块如下:

  1. HSSF - 提供读写Microsoft Excel格式档案的功能。
  2. XSSF - 提供读写Microsoft Excel OOXML格式档案的功能。
  3. HWPF - 提供读写Microsoft Word格式档案的功能。
  4. HSLF - 提供读写Microsoft PowerPoint格式档案的功能。
  5. HDGF - 提供读写Microsoft Visio格式档案的功能。

三.本博客实现的小目标

利用POI对excle的读取与修改,并实现与mysql数据库的数据交互,包括基本的增删改查.

四.具体实现步骤

1.项目的基本结构

首先先准备一个使用的excle表格,名叫student.xlsx:

对应的Student实体类为:

package com.shsxt.entity;

/**
 * Created on 2018/10/3 18:09
 * Author: Mr Tong
 */

public class Student {

    private Integer s_id; //主键
    private Integer s_no; //序号
    private String s_name; //名字
    private Integer s_age; //年龄
    private String s_sex; //性别
    private double s_grade; //分数

    //有用到
    public Student() {
    }

    //大用处,哈哈哈哈哈
    public Student(Integer s_no, String s_name, Integer s_age, String s_sex, double s_grade) {
        this.s_no = s_no;
        this.s_name = s_name;
        this.s_age = s_age;
        this.s_sex = s_sex;
        this.s_grade = s_grade;
    }


    public Integer getS_id() {
        return s_id;
    }

    public void setS_id(Integer s_id) {
        this.s_id = s_id;
    }

    public Integer getS_no() {
        return s_no;
    }

    public void setS_no(Integer s_no) {
        this.s_no = s_no;
    }

    public String getS_name() {
        return s_name;
    }

    public void setS_name(String s_name) {
        this.s_name = s_name;
    }

    public Integer getS_age() {
        return s_age;
    }

    public void setS_age(Integer s_age) {
        this.s_age = s_age;
    }

    public String getS_sex() {
        return s_sex;
    }

    public void setS_sex(String s_sex) {
        this.s_sex = s_sex;
    }

    public double getS_grade() {
        return s_grade;
    }

    public void setS_grade(double s_grade) {
        this.s_grade = s_grade;
    }
}

2.引入相应的jar包依赖(pom.xml的配置):

<dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>
    <!-- spring 测试环境 -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>4.3.2.RELEASE</version>
      <scope>test</scope>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
    </dependency>

    <!-- spring 框架坐标依赖添加 -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>4.3.2.RELEASE</version>
    </dependency>
    <!-- spring jdbc -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>4.3.2.RELEASE</version>
    </dependency>
    <!-- mysql 驱动包 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.39</version>
    </dependency>
    <!-- c3p0 连接池 -->
    <dependency>
      <groupId>c3p0</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.1.2</version>
    </dependency>
    <!--
          加入aop 坐标
        -->
    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjweaver</artifactId>
      <version>1.8.9</version>
    </dependency>
    <!-- spring 上下文环境 支持 -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context-support</artifactId>
      <version>4.3.2.RELEASE</version>
    </dependency>
  </dependencies>

3.jdbc.properties与spring-jdbc.xml的配置(根据自己的mysql自行修改)

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring?useUnicode=true&characterEncoding=utf8
jdbc.user=root
jdbc.password=root
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd">
    <context:annotation-config/>
    <!--
      配置扫描器
    -->
   <!-- <context:component-scan base-package="com.shsxt"></context:component-scan>-->
    <context:component-scan base-package="com.shsxt"/>

    <!--
       加载properties 配置文件
    -->
    <context:property-placeholder location="classpath:jdbc.properties"/>

    <aop:aspectj-autoproxy/>


    <!--
     配置数据源c3p0
    -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.url}"></property>
        <property name="user" value="${jdbc.user}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>


    <!--
       配置 jdbcTemplate
    -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>



</beans>

4.实现增删改查的功能接口

package com.shsxt.dao;

/**
 * Created on 2018/10/3 18:40
 * Author: Mr Tong
 */

public interface StudentDao {

    /**
     * 批量添加Student实体类的记录
     * @param students
     * @return
     */
    public Integer saveStudentBatch(List<Student> students);

    /**
     * 查询所有的记录
     * @return
     */
    public List<Student> queryAllRecord();

    /**
     * 统计账户记录-聚合查询
     * sum  max  min  count
     * group by   having
     * @return
     */
    public  Integer countStudentsByS_no();

}

5.接口的实现类

package com.shsxt.dao.impl;

/**
 * Created on 2018/10/3 18:45
 * Author: Mr Tong
 */

@Repository
public class StudentDaoImpl implements StudentDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 批量添加Student实体类的记录
     * @param students
     * @return
     */

    @Override
    public Integer saveStudentBatch(List<Student> students) {

        String sql="insert into tb_student(s_no,s_name,s_age,s_sex,s_grade) values(?,?,?,?,?)";

        return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {

                //准备容器接收参数

                //Student student = (Student) students.get(i);
                //Map<Integer,List<Object[]>> map = new HashMap<>();
                //Student student = new Student();
                Student student = students.get(i);
                    ps.setInt(1, student.getS_no());
                    ps.setString(2, student.getS_name());
                    ps.setInt(3, student.getS_age());
                    ps.setString(4, student.getS_sex());
                    ps.setDouble(5, student.getS_grade());
                }

            @Override
            public int getBatchSize() {
                // 批量添加记录的总条数
                return students.size();
            }
        }).length;
    }

    /**
     * 查询所有的记录
     * @return
     */

    @Override
    public List<Student> queryAllRecord() {

        //准备好sql语句,查询所有
        String sql = "select * from tb_student ";

        return jdbcTemplate.query(sql, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet rs, int rowNum) throws SQLException {

                //定义个实体类来接收查询的结果
                Student student = new Student();
                //设置值
                student.setS_no(rs.getInt("s_no"));
                student.setS_name(rs.getString("s_name"));
                student.setS_age(rs.getInt("s_age"));
                student.setS_sex(rs.getString("s_sex"));
                student.setS_grade(rs.getDouble("s_grade"));
                return student;
            }
        });
    }

    /**
     * 统计账户记录-聚合查询
     * sum  max  min  count
     * group by   having
     * @return
     */

    @Override
    public Integer countStudentsByS_no() {

        String sql="select count(1) from tb_student ";

        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

}

 

基本的功能实已经实现啦!现在让我们来测试一下:

测试读取excle表格的数据到mysql数据库的操作(TestToMysql):

/**
 * Created on 2018/10/3 16:59
 * Author: Mr Tong
 */

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring-jdbc.xml"})
public class TestToMysql {

    @Resource
    private StudentDao studentDao;

    @Test
    public void test(){

        //定义一个容器用来接收Excel中的数据
        //List<Student> students = new ArrayList<>();

        //Map<Integer, List<Student>> students = new HashMap<>();
        List<Student> students = new ArrayList<>();
        Student student = new Student();

        //定义一个成员变量,得到下面的每行中单元格cell的数量,并传入方法中
        int cellNums = 0; 

        //得到一个输入流,inp为所读xlsx表格的对象
        try (InputStream inp = new FileInputStream("C:\\Users\\威威\\Desktop\\student.xlsx")) {
            //InputStream inp = new FileInputStream("workbook.xlsx");


            //借助WorkbookFactory创建一个工作簿Workbook
            Workbook wb = WorkbookFactory.create(inp);

            //得到表
            for (Sheet sheet : wb ) {

                //得到表中行的数量,getLastRowNum返回最后一个下标
                int rowNum = sheet.getLastRowNum() + 1;

                //得到一行中单元格的数量,getLastCellNum函数返回最后一个下标+1
                //Row row = sheet.getRow(rowNum);

                for (int i=1; i<rowNum; ++i) {

                    //得到Excel表格的行
                    Row row = sheet.getRow(i);
                    int cellNum = row.getLastCellNum();
                    
                    //赋值给成员变量
                    cellNums = cellNum;

                    //把每行的cell内容加进去
                    /*for (int j = 0; j < cellNum; j++) {
                        //list.add(j, row.getCell(j).getStringCellValue());
                        student.setS_no((int) row.getCell(j).getNumericCellValue());
                        //每次都移除最后一个元素,防止重复(debug可见实质)
                        while (i != 0) {
                            list.remove(5);
                            break;
                        }
                    }*/
                    student.setS_no((int) row.getCell(0).getNumericCellValue());
                    student.setS_name((String) row.getCell(1).getStringCellValue());
                    student.setS_age((int) row.getCell(2).getNumericCellValue());
                    student.setS_sex((String) row.getCell(3).getStringCellValue());
                    student.setS_grade(row.getCell(4).getNumericCellValue());

                    //将最新的一行数据加入到map集合里面
                    students.add(0, student);
                    //调用Dao层方法,进行批量添加
                    studentDao.saveStudentBatch(students);
                    //清空students,哈哈哈
                    students.clear();
                }

            }
        } catch (IOException e) {
            e.printStackTrace();
        }  catch (InvalidFormatException e) {
            e.printStackTrace();
        }

    }

}

测试从数据库中修改记录更新到excle表格的功能(TestToExcle):

/**
 * Created on 2018/10/4 11:21
 * Author: Mr Tong
 */

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring-jdbc.xml"})
public class TestToExcel {

    @Resource
    private StudentDao studentDao;

    @Test
    public void test01(){

        //调用方法得到数据库中的所有记录
        List<Student> list = studentDao.queryAllRecord();
        //System.out.println(list.get(0));

        //得到数据库中记录的条数
        int recodeNums = studentDao.countStudentsByS_no();

        //Reading and Rewriting Workbooks
        try (InputStream inp = new FileInputStream("C:\\Users\\威威\\Desktop\\test.xlsx")) {
            //InputStream inp = new FileInputStream("workbook.xlsx");

            //借助WorkbookFactory创建一个工作簿Workbook
            Workbook wb = WorkbookFactory.create(inp);

            //得到sheet,因为只有一个sheet,所有也不需要遍历啦!
            Sheet sheet = wb.getSheetAt(0);

            //得到表中行的数量,getLastRowNum返回最后一个下标
            int rowNum = sheet.getLastRowNum() + 1;

            //遍历表中的所有行
            for (int i = 1; i < rowNum; i++) {

                //得到Excel表格的行
                Row row = sheet.getRow(i);
                Student student = list.get(i-1);

                //得到单元格的数量
                int cellNum = row.getLastCellNum();

                //遍历单元格,改变单元格中的值
                for (int j = 0; j < cellNum; j++) {

                    //得到对应的单元格
                    Cell cell = row.getCell(j);

                    //给单元格设置对应的值
                   if(j == 0){
                       cell.setCellValue(student.getS_no());
                   }if(j == 1){
                        cell.setCellValue(student.getS_name());
                   }if(j == 2){
                        cell.setCellValue(student.getS_age());
                   }if(j == 3){
                        cell.setCellValue(student.getS_sex());
                   }if(j == 4){
                        cell.setCellValue(student.getS_grade());
                   }
                }
            }

            // Write the output to a file
            try (OutputStream fileOut = new FileOutputStream("C:\\Users\\威威\\Desktop\\test.xlsx")) {
                wb.write(fileOut);
            }
        }  catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
    }
}

PS~~以上就实现了excle与mysql数据库之间的数据交互啦!!!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  • 0
    点赞
  • 3
    评论
  • 5
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 创作都市 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值