【问题描述】
1.使用Java语言读取Excel文件中的数据,并将读取到的数据添加到数据库中。
【开发环境】
JavaSE 1.8、Ecilpse2019-06、mysql8.0、MyBatis
【资源准备】
1.读取Excel需要用到的jar包:
点击下载链接:https://pan.baidu.com/s/1gNiq4j8bS0xCmDvu4pSixg&shfl=sharepset
提取码:lsj9
(项目源码下载在本文末附录)
2.名为student.xlsx的Excel文件内容如下图所示:
3.根据文件表头信息,在数据库中创建一张学生信息表student,具体设计如下图所示:
【项目部署】
1.新建一个java项目,向其中导入上面下载的读取Excel需要用到的jar包,并部署项目如下图所示:
【程序代码】
1.读取Excel文件的核心类:/testExcel/src/com/poi/ReadExcelUtils.java
package com.poi;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelUtils {
private Workbook wb;// 工作簿对象
private Sheet sheet;// 工作表对象
private Row row;// 行对象
public ReadExcelUtils() {}
// 读取文件,判断格式
public ReadExcelUtils(String filepath) {
// 文件路径为空时自动跳过
if (filepath == null) {
return;
}
// 获取文件后缀名
String ext = filepath.substring(filepath.lastIndexOf("."));
try {
InputStream is = new FileInputStream(filepath);
if (".xls".equals(ext)) {
wb = new HSSFWorkbook(is);
} else if (".xlsx".equals(ext)) {
wb = new XSSFWorkbook(is);
} else {
wb = null;
}
} catch (Exception e) {
e.printStackTrace();
}
}
// 读取表头
public String[] readExcelTitle() throws Exception {
if (wb == null) {
throw new Exception("工作簿Workbook对象为空!");
}
sheet = wb.getSheetAt(0);// 读取第一张(索引为零)表格
row = sheet.getRow(0);// 获取第一张表格的第一行
// 获取标题总列数
int colNum = row.getPhysicalNumberOfCells();
System.out.println("colNum:" + colNum);
String[] title = new String[colNum];// 以列数作为数组元素个数创建数组
// 将表头字段装进数组
for (int i = 0; i < colNum; i++) {
// title[i] = getStringCellValue(row.getCell((short) i));
title[i] = row.getCell(i).getCellFormula();
}
return title;
}
// 读取 Excel 数据内容,返回包含单元格数据内容的Map对象
public Map<Integer, Map<Integer, Object>> readExcelContent() throws Exception {
if (wb == null) {
throw new Exception("工作簿Workbook对象为空!");
}
Map<Integer, Map<Integer, Object>> content = new HashMap<Integer, Map<Integer, Object>>();
// 获取工作簿的第一张表
sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
// 获取第一行
row = sheet.getRow(0);
// 获取列数
int colNum = row.getPhysicalNumberOfCells();
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i <= rowNum; i++) {
// 第二行开始
row = sheet.getRow(i);
int j = 0;
Map<Integer, Object> cellValue = new HashMap<Integer, Object>();
// 读取该行的每列数据,并存入map集合中
while (j < colNum) {
Object obj = getCellFormatValue(row.getCell(j));
cellValue.put(j, obj);
j++;
}
// 将每行数据装入map数组中
content.put(i, cellValue);
}
return content;
}
// 根据Cell类型设置数据
private Object getCellFormatValue(Cell cell) {
Object cellvalue = "";
if (cell != null) {
// 判断当前单元格的类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {// 如果当前Cell的Type为NUMERIC
cellvalue = (int) cell.getNumericCellValue();
break;
}
case Cell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (DateUtil.isCellDateFormatted(cell)) {
// data格式是带时分秒的:2013-7-10 0:00:00
// cellvalue = cell.getDateCellValue().toLocaleString();
// data格式是不带带时分秒的:2013-7-10
Date date = cell.getDateCellValue();
cellvalue = date;
} else {// 如果是纯数字
// 取得当前Cell的数值
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为字符串
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
default:// 默认的Cell值
cellvalue = "";
}
} else {
cellvalue = "";
}
return cellvalue;
}
//供外部调用的方法
public Map<Integer, Map<Integer, Object>> ReadExcelcontent(String filepath) {
Map<Integer, Map<Integer, Object>> map = null;
try {
ReadExcelUtils excelReader = new ReadExcelUtils(filepath);
map = excelReader.readExcelContent();
} catch (FileNotFoundException e) {
System.out.println("未找到指定路径的文件!");
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return map;
}
}
2.业务具体操作类:/testExcel/src/com/poi/ReadExceltoStuList.java
package com.poi;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import com.pojo.Student;
/**
* @author HackerAC
*/
public class ReadExceltoStuList {
public List<Student> GetExcelInfo(){
ReadExcelUtils readexcel = new ReadExcelUtils();
String filepath = "students.xlsx";
Map<Integer, Map<Integer, Object>> map = readexcel.ReadExcelcontent(filepath);
//学生集合
List<Student> stuList=new ArrayList<Student>();
for (int i = 1; i <= map.size(); i++) {
Student stu=new Student();
stu.setStu_Num((int) map.get(i).get(0));
stu.setStu_name((String) map.get(i).get(1));
stu.setStu_Sex((String) map.get(i).get(2));
stu.setAcademy((String) map.get(i).get(3));
stu.setProfession((String) map.get(i).get(4));
stu.setStu_class( String.valueOf(map.get(i).get(5)));
stuList.add(stu);
}
return stuList;
}
}
3.MyBatis接口:
/testExcel/src/com/mapper/IStuGrade.java
/**
* @author HackerAC
*/
package com.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.pojo.Student;
public interface IStuGrade {
//批量插入
public void InserStuList(@Param("stulist") List<Student> stulist);
}
4.MyBatis接口对应的Mapper文件:
/testExcel/src/com/mapper/IStuGrade.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mapper.IStuGrade">
<sql id="mysql">
(stu_Num,stu_Name,stu_Sex,academy,profession,stu_Class)
</sql>
<!-- 批量插入 -->
<insert id="InserStuList">
insert into student <include refid="mysql"/> values
<trim suffixOverrides=",">
<foreach collection="stulist" item="item" >
( #{item.stu_Num}, #{item.stu_Name}, #{item.stu_Sex}, #{item.academy}, #{item.profession}, #{item.stu_Class}),
</foreach>
</trim>
</insert>
</mapper>
5.测试类:
/testExcel/src/com/test/test.java
package com.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.mapper.IStuGrade;
import com.poi.ReadExceltoStuList;
import com.pojo.Student;
/**
* @author HackerAC
*/
public class test {
private SqlSession session = null;
private SqlSessionFactory build;
private List<Student> stuList = null;
@Before
public void init() {
InputStream is;
try {
is = Resources.getResourceAsStream("mybatis-config.xml");
build = new SqlSessionFactoryBuilder().build(is);
session = build.openSession();
} catch (IOException e) {
e.printStackTrace();
}
}
@After
public void destory() {
if (session != null) {
session.close();
}
}
// 读取Excel文件中的数据批量插入到mysql数据库
@Test
public void testInserstulist() {
session.close();
session = build.openSession(ExecutorType.BATCH, true);
ReadExceltoStuList EtS = new ReadExceltoStuList();
stuList = EtS.GetExcelInfo();
session.getMapper(IStuGrade.class).InserStuList(stuList);
session.commit();
}
}
【运行结果】
1.使用JUnit4运行测试类中的testInserstulist()方法。
2.刷新并查看数据库中表student的数据信息,如下图所示:
至此,完成。
【说明及附录】
1.本文未展示MyBatis环境的搭建,可通过点击此处查看本次搭建的MyBatis环境。
2.项目源码:链接:https://pan.baidu.com/s/1so-1PD-vwimwVWmfh13hRA&shfl=sharepset
提取码:lz38