excel读入数据库

时间：2024-01-03 13:02:02

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);

            }

        }

    }

}

数据库设计

excel读入数据库

项目结构：

excel读入数据库

相关文章