使用POI实现excle与mysql数据库的导入导出
目录
3.jdbc.properties与spring-jdbc.xml的配置(根据自己的mysql自行修改)
一.实现环境
Spring + idea2018 + jdk1.8 + excel2013 + mysql数据库
二.POI简介
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。是纯java技术.其主要的基本功能模块如下:
- HSSF － 提供读写Microsoft Excel格式档案的功能。
- XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。
- HWPF － 提供读写Microsoft Word格式档案的功能。
- HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
- 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数据库之间的数据交互啦!!!