excel读入数据库
POI3.9效率大幅度提高，支持xls以及xlsx。
首先需要POI的JAR包，MAVEN配置如下：
<!-- excel start --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>2.6.0</version> </dependency> <dependency> <groupId>dom4j</groupId> <artifactId>dom4j</artifactId> <version>1.6.1</version> </dependency> <!-- excel end -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.35</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.1.1</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5-pre10</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${springversion}</version>
<type>jar</type>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${springversion}</version>
<type>jar</type>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${springversion}</version>
<type>jar</type>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${springversion}</version>
<type>jar</type>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${springversion}</version>
<type>jar</type>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${springversion}</version>
<type>jar</type>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${springversion}</version>
<type>jar</type>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>${springversion}</version>
<type>jar</type>
<scope>compile</scope>
</dependency>
<!-- cglib -->
<dependency>
<groupId>cglib</groupId>
<artifactId>cglib</artifactId>
<version>2.2.2</version>
</dependency>
ReadExcel.java
package excel;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import mysql.mapper.StudentMapper;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import Student.Student;
public class ReadExcel {
private StudentMapper studentMapper;
public ReadExcel(){
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext-dao.xml");
studentMapper = (StudentMapper) ctx.getBean("studentMapper");
}
public List<Student> readExcel(String path) throws IOException {
if (path == null || path.equals("")) {
return null;
}
String postfix = getPostfix(path);
if (!"".equals(postfix)) {
if ("xls".equals(postfix)) {
return readXls(path);
} else if ("xlsx".equals(path)) {
return readXlsx(path);
}
} else {
System.out.println(" : Not the Excel file!");
}
return null;
}
public List<Student> readXlsx(String path) throws IOException {
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
Student student = null;
List<Student> list = new ArrayList<Student>();
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
student = new Student();
XSSFCell no = xssfRow.getCell(0);
XSSFCell name = xssfRow.getCell(1);
XSSFCell age = xssfRow.getCell(2);
XSSFCell score = xssfRow.getCell(3);
student.setNo(getValue(no));
student.setName(getValue(name));
student.setAge(getValue(age));
student.setScore(Float.valueOf(getValue(score)));
studentMapper.insert(student);
list.add(student);
}
}
}
return list;
}
public List<Student> readXls(String path) throws IOException {
System.out.println("processing...");
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
Student student = null;
List<Student> list = new ArrayList<Student>();
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
student = new Student();
HSSFCell no = hssfRow.getCell(0);
HSSFCell name = hssfRow.getCell(1);
HSSFCell age = hssfRow.getCell(2);
HSSFCell score = hssfRow.getCell(3);
student.setNo(getValue(no));
student.setName(getValue(name));
student.setAge(getValue(age));
student.setScore(Float.valueOf(getValue(score)));
studentMapper.insert(student);
list.add(student);
}
}
}
return list;
}
@SuppressWarnings("static-access")
private String getValue(XSSFCell xssfRow) {
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfRow.getNumericCellValue());
} else {
return String.valueOf(xssfRow.getStringCellValue());
}
}
@SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
public String getPostfix(String path){
if(path == null || path.equals(" ")){
return "";
}
if(path.contains(".")){
return path.substring(path.lastIndexOf(".") + 1, path.length());
}
return "";
}
}
applicationContext-dao.xml
<?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" 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-4.0.xsd"> <!-- 配置数据源, 整合其他框架, 事务等. --> <!-- 加载配置文件 --> <context:property-placeholder location="classpath:db.properties" /> <!-- 数据源，使用dbcp --> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <property name="driverClass" value="${jdbc.driverClass}" /> <property name="jdbcUrl" value="${jdbc.jdbcUrl}" /> <property name="user" value="${jdbc.user}" /> <property name="password" value="${jdbc.password}" /> <property name="minPoolSize" value="${jdbc.miniPoolSize}" /> <property name="maxPoolSize" value="${jdbc.maxPoolSize}" /> <property name="initialPoolSize" value="${jdbc.initialPoolSize}" /> <property name="maxIdleTime" value="${jdbc.maxIdleTime}" /> <property name="acquireIncrement" value="${jdbc.acquireIncrement}" /> <property name="acquireRetryAttempts" value="${jdbc.acquireRetryAttempts}" /> <property name="acquireRetryDelay" value="${jdbc.acquireRetryDelay}" /> <property name="testConnectionOnCheckin" value="${jdbc.testConnectionOnCheckin}" /> <property name="automaticTestTable" value="${jdbc.automaticTestTable}" /> <property name="idleConnectionTestPeriod" value="${jdbc.idleConnectionTestPeriod}" /> <property name="checkoutTimeout" value="${jdbc.checkoutTimeout}" /> </bean> <!-- sqlSessinFactory --> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <!-- 加载mybatis的配置文件 --> <property name="configLocation" value="classpath:SqlMapConfig.xml" /> <!-- 数据源 --> <property name="dataSource" ref="dataSource" /> </bean> <!-- mapper配置 MapperFactoryBean：根据mapper接口生成代理对象，和下面方式二选一 --> <!-- <bean id="userMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"> <property name="mapperInterface" value="com.alibaba.mapper.UserMapper"/> <property name="sqlSessionFactory" ref="sqlSessionFactory"/> </bean> --> <!-- mapper批量扫描，从mapper包中扫描出mapper接口，自动创建代理对象并且在spring容器中注册 遵循规范：将mapper.java和mapper.xml映射文件名称保持一致，且在一个目录 中 自动扫描出来的mapper的bean的id为mapper类名（首字母小写） --> <!-- 指定扫描的包名 如果扫描多个包，每个包中间使用半角逗号分隔 --> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="station.mapper"/> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/> </bean> </beans>
db.properties
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl = jdbc:mysql://localhost:3306/test
jdbc.user = root
jdbc.password = 123456
jdbc.miniPoolSize = 1
jdbc.maxPoolSize = 20
jdbc.initialPoolSize = 1
jdbc.maxIdleTime = 25000
jdbc.acquireIncrement = 1
jdbc.acquireRetryAttempts = 30
jdbc.acquireRetryDelay = 1000
jdbc.testConnectionOnCheckin = true
jdbc.automaticTestTable = c3p0TestTable
jdbc.idleConnectionTestPeriod = 18000
jdbc.checkoutTimeout=3000
SqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <!-- 打开延迟加载 的开关 --> <setting name="lazyLoadingEnabled" value="true" /> <!-- 将积极加载改为消极加载即按需要加载 --> <setting name="aggressiveLazyLoading" value="false" /> <!-- 开启二级缓存 --> <setting name="cacheEnabled" value="true" /> </settings> <!-- 别名定义 --> <typeAliases> <!-- 批量别名定义 指定包名，mybatis自动扫描包中的po类，自动定义别名，别名就是类名（首字母大写或小写都可以） --> <package name="Student" /> </typeAliases> <!-- 加载 映射文件 --> <!-- <mappers> --> <!-- <mapper resource="mybatis/UserMapper.xml" /> --> <!-- 批量加载mapper 指定mapper接口的包名，mybatis自动扫描包下边所有mapper接口进行加载 遵循一些规范：需要将mapper接口类名和mapper.xml映射文件名称保持一致，且在一个目录 中 上边规范的前提是：使用的是mapper代理方法 和spring整合后，使用mapper扫描器，这里不需要配置了 --> <!-- <package name="mysql.mapper"/> --> <!-- </mappers> --> </configuration>
测试类：
package client; import java.io.IOException; import java.util.List; import Student.Student; import excel.ReadExcel; public class Client { public static void main(String[] args) throws IOException{ List<Student> list = new ReadExcel().readExcel("D:\\student.xls"); if(list != null){ for(Student student:list){ System.out.println(student); } } } }
数据库设计
项目结构：
浙公网安备 33010602011771号