Java POI Mysql与excel间的数据导入导出
本文介绍的是利用Apache poi 的api将mysql中的数据导出到excel表中及将excel表中的数据插入到mysql中。我封装成了工具类，导出的时候想导出哪张表只需要写sql语句就可以了。导入到mysql的时候因为我项目中用到了mybatis有与数据库中对应的实体类，所以导入的时候是通过反射将excel表中的数据封装到实体类中，再返回一个装有实体类的集合，接着你就可以通过mybatis将list中的实体类数据插入到mysql中了。或者拿这个list随便你干什么。
一、准备工作
导入apache poi 的jar包。poi的maven坐标：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
演示所用到的表：
二、从mysql到出到excel中。
导出到excel的主要代码：
/**
* Created by otote Cotter on 2018/11/9 9:21.
* 自定义的poi工具类
* 将mysal中的表导出到excel
* 将excel中的数据导入到mysql
*/
public class MyPoiUtil {
/**
* 导出到excel中
* @param connection 与数据库的连接
* @param sql 查询的sql语句 select * from 表名
* @param tableName excel的sheet名
* @param filePath 输出的文件路径
*/
public static void exportToExcel(Connection connection,String sql,String tableName,String filePath){
ResultSet resultSet = null;
PreparedStatement statement = null;
OutputStream os=null;
//创建一个excel工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//创建表 并设置sheet的名称
HSSFSheet sheet = workbook.createSheet(tableName);
//创建标题行
HSSFRow row = sheet.createRow(0);
//日期格式化
HSSFCellStyle cellStyle = workbook.createCellStyle();
HSSFCreationHelper creationHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
try {
//通过传进来的sql 查询表中的数据
statement = connection.prepareStatement(sql);
//获取结果集
resultSet = statement.executeQuery();
//获取元数据 用来获取字段名
ResultSetMetaData metaData = resultSet.getMetaData();
//每一行的列数
int columnCount = metaData.getColumnCount();
//动态根据字段名设置列名 即标题
for (int i = 0; i < columnCount; i++) {
String labelName = metaData.getColumnLabel(i + 1);
row.createCell(i).setCellValue(labelName);
}
int i=1;
//临时行
HSSFRow tempRow;
//遍历结果集 往excel中写数据
while (resultSet.next()){
//创建临时行 即当前行
tempRow = sheet.createRow(i);
for (int j = 0; j < columnCount; j++) {
//获取当前单元格
HSSFCell tempCell = tempRow.createCell(j);
//获取数据库中与当前格对应的数据
Object temp=resultSet.getObject(j+1);
//如果获取到的数据为空则跳过该单元格
if (temp==null || "".equals(temp)){
continue;
}
//如果获取到的是时间 则格式化时间再写到excel中
if (temp instanceof java.util.Date){
//给日期设置样式
tempCell.setCellStyle(cellStyle);
tempCell.setCellValue((java.util.Date) temp);
}else if(temp instanceof Boolean){
tempCell.setCellValue((Boolean) temp);
}else if (temp instanceof Double){
tempCell.setCellValue((Double) temp);
}else {
tempCell.setCellValue(temp.toString());
}
}
i++;
}
os=new BufferedOutputStream(new FileOutputStream(filePath));
//将excel表格写出到指定的路径下
workbook.write(os);
System.out.println(filePath+"导出成功");
} catch (Exception e) {
e.printStackTrace();
}finally {
//关闭资源
IOUtils.closeQuietly(os);
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试导出：
@Test
public void exprotTest() throws SQLException {
//通过数据源获取与数据库的连接 没配置spring的可用原生的jdbc来获取连接
Connection connection = dataSource.getConnection();
//查询的sql语句
String sql="select * from emp";
//生成的excel表的路径 注意文件名要和数据库中表的名称一致 因为导入时要用到。
String filePath="C:\\Users\\otote\\Desktop\\emp.xls";
//导出
MyPoiUtil.exportToExcel(connection,sql , "emp", filePath);
}
生成的excel表：
excel表与代码比较：
三、从excel表导入到mysql中
准备一个实体类。实体类的属性名称必须与excel表中字段的名称一致。因为这里使用的是通过比较实体类的书名称与表中的字段名称是否一致来将数据封装到实体类中。如果不一致的话就需要通过自定义配置文件将实体类的属性名与excel中的字段名建立映射关系了。
实体类：
public class Emp implements Serializable {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double comm;
private Integer deptno;
private static final long serialVersionUID = 1L;
//省略set、get方法
}
从excel获取list集合
/**
*
* 从excel导入到mysql中
* @param t 与数据库中要插入的表对应的实体类
* @param filePath excel文件的路径
* @param <T>
* @return 实体类的list集合
*/
public static <T> List<T> importToMysql(Class<T> t, String filePath){
//准备一个list用来存放结果
List<T> list=new ArrayList<>();
HSSFWorkbook workbook= null;
try {
//通过文件路径创建一个工工作簿
workbook = new HSSFWorkbook(new FileInputStream(filePath));
} catch (IOException e) {
System.out.println("文件读取失败");
System.out.println(e.getMessage());
}
//获取当前excel的第一个sheet表格 如果有多个sheet就自行遍历
HSSFSheet sheet = workbook.getSheetAt(0);
//获取excel表的第一行 用来获取表的字段名
HSSFRow tempRow = sheet.getRow(0);
//获取总的行数
int lastRowNum = sheet.getLastRowNum();
//遍历行 从第二行开始 第一行为表的字段名
for (int i = 1; i <= lastRowNum; i++) {
HSSFRow row = sheet.getRow(i);
//获取列数
int cells = row.getPhysicalNumberOfCells();
//实例化实体类对象
T tempT= null;
try {
tempT = t.newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
//遍历当前行的每一列
for (int j = 0; j < cells; j++) {
//当前列的值
HSSFCell hsscell = row.getCell(j);
//当前列的名称
HSSFCell cellName= tempRow.getCell(j);
Field field = null;
try {
//根据列名获取实体类与之对应的属性
field = tempT.getClass().getDeclaredField(cellName.toString());
} catch (NoSuchFieldException e) {
System.out.println(field.getName()+"属性获取失败");
System.out.println(e.getMessage());
}
//如果该字段为空 则跳过
if (hsscell==null){
continue;
}
try {
//根据从excel获取到的数据的类型设值
if (field!=null){
//私有属性授权
field.setAccessible(true);
if (hsscell.getCellTypeEnum()== CellType.NUMERIC){
if(HSSFDateUtil.isCellDateFormatted(hsscell)){//日期
if (field.getType()== Date.class){
field.set(tempT,hsscell.getDateCellValue());
}
}else if(field.getType()==Integer.class){
//int类型
field.set(tempT,Integer.valueOf(hsscell.getStringCellValue()));
}else if(field.getType()==Double.class){
//double类型
field.set(tempT,Double.parseDouble(hsscell.toString()) );
}
}else if (hsscell.getCellTypeEnum()==CellType.BOOLEAN){
//布尔值
if (field.getType()==Boolean.class){
field.set(tempT,hsscell.getBooleanCellValue() );
}
}else if(hsscell.getCellTypeEnum()==CellType.STRING){
if (field.getType()==Integer.class){
field.set(tempT,Integer.parseInt(hsscell.toString()));
}else if (field.getType()==Double.class){
field.set(tempT,Double.valueOf(hsscell.toString()) );
}else if (field.getType()==String.class){
field.set(tempT,hsscell.toString() );
}
}
}
} catch (IllegalAccessException e) {
System.out.println(field.getName()+"设值失败");
e.printStackTrace();
}
}
//添加到list集合中
list.add(tempT);
}
//将封装好的list集合返回
return list;
}
测试：
@Test
public void importTest(){
//要导入的excel文件路径
String filePath="C:\\Users\\otote\\Desktop\\emp.xls";
//获取集合 Emp.class为该excel表对应的实体类
List<Emp> empList = MyPoiUtil.importToMysql(Emp.class, filePath);
//遍历查看结果
for (Emp emp : empList) {
System.out.println(emp);
}
}
控制台结果：
Emp [Hash = -460592568, empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20, serialVersionUID=1]
Emp [Hash = 1426257137, empno=7499, ename=ALLEN, job=SALESMAN, mgr=7698, hiredate=Fri Feb 20 00:00:00 CST 1981, sal=1600.0, comm=300.0, deptno=30, serialVersionUID=1]
Emp [Hash = 1288162093, empno=7521, ename=WARD, job=SALESMAN, mgr=7698, hiredate=Sun Feb 22 00:00:00 CST 1981, sal=1250.0, comm=500.0, deptno=30, serialVersionUID=1]
Emp [Hash = 1962304190, empno=7566, ename=JONES, job=MANAGER, mgr=7839, hiredate=Thu Apr 02 00:00:00 CST 1981, sal=2975.0, comm=null, deptno=20, serialVersionUID=1]
Emp [Hash = -657240670, empno=7654, ename=MARTIN, job=SALESMAN, mgr=7698, hiredate=Mon Sep 28 00:00:00 CST 1981, sal=1250.0, comm=1400.0, deptno=30, serialVersionUID=1]
Emp [Hash = 456906682, empno=7698, ename=BLAKE, job=MANAGER, mgr=7839, hiredate=Fri May 01 00:00:00 CST 1981, sal=2850.0, comm=0.1, deptno=30, serialVersionUID=1]
Emp [Hash = 451916084, empno=7782, ename=CLARK, job=MANAGER, mgr=7839, hiredate=Tue Jun 09 00:00:00 CST 1981, sal=2450.0, comm=null, deptno=10, serialVersionUID=1]
Emp [Hash = -1064233035, empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=Sun Apr 19 00:00:00 CDT 1987, sal=3000.0, comm=null, deptno=20, serialVersionUID=1]
Emp [Hash = 1751776396, empno=7839, ename=KING, job=PRESIDENT, mgr=null, hiredate=Tue Nov 17 00:00:00 CST 1981, sal=5000.0, comm=null, deptno=10, serialVersionUID=1]
Emp [Hash = -1419781523, empno=7876, ename=ADAMS, job=CLERK, mgr=7788, hiredate=Sat May 23 00:00:00 CDT 1987, sal=1100.0, comm=null, deptno=20, serialVersionUID=1]
Emp [Hash = -216946009, empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=Thu Dec 03 00:00:00 CST 1981, sal=3000.0, comm=null, deptno=20, serialVersionUID=1]
Emp [Hash = 906278479, empno=7934, ename=MILLER, job=CLERK, mgr=7782, hiredate=Sat Jan 23 00:00:00 CST 1982, sal=1300.0, comm=null, deptno=10, serialVersionUID=1]
Process finished with exit code 0
这样就从excel获取到了所有数据，接着就是插入到数据库中了。可用mybatis插入也可自己通过原生的jdbc插入。