使用Maven将Excel导入数据库

经常需要用到excel导入数据库的同学，其实可以不用每次费事编写POI-java代码，现在maven可以方便的半自动导入，只需要配置好相关设置，方便了许多
示例使用mysql DB

excel内容格式如下

Paste_Image.png

按照excel格式建立对应的表结构

SET SESSION FOREIGN_KEY_CHECKS=0;

/* Drop Tables */
DROP TABLE learn_maven_list;

/* Create Tables */

CREATE TABLE `learn_maven_list` (
    `id` VARCHAR(64) NOT NULL COLLATE 'utf8_bin',
    `license_no` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
    `appler` VARCHAR(255) NOT NULL COLLATE 'utf8_bin',
    `farm` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
    `address` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
    `contect` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
    `product` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
    `large` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
    `date` DATE NULL DEFAULT NULL,
    `pirorid` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
    PRIMARY KEY (`id`),
    INDEX `appler` (`appler`, `id`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB;

注意表名应该和excel的sheet名一致，如果有多个sheet，依次建立表结构即可

Paste_Image.png

POM文件如下

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.mycompany.app</groupId>
  <artifactId>my-app</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>my-app</name>
  <url>http://maven.apache.org</url>

  <properties>
        <mysql.driver.version>5.1.13</mysql.driver.version>     
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.driver.version}</version>
            <scope>runtime</scope>
        </dependency>    
  </dependencies>
  
    <profiles>
        <!-- 初始化数据库 -->         
        <profile>
            <id>init-db</id>
            <dependencies>
                <dependency>
                    <groupId>org.dbunit</groupId>
                    <artifactId>dbunit</artifactId>
                    <version>2.4.8</version>
                </dependency>
                <dependency>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                    <version>3.2-FINAL</version>
                </dependency>
            </dependencies>
            <build>
                <plugins>
                    <plugin>
                        <groupId>org.apache.maven.plugins</groupId>
                        <artifactId>maven-antrun-plugin</artifactId>
                        <version>1.7</version>
                        <configuration>
                            <target>
                                <!-- mysql -->
                                <property name="dbunit.datatype" value="org.dbunit.ext.mysql.MySqlDataTypeFactory" />
                                <property file="wee.properties" />                                  
                                <sql driver="${jdbc.driver}" url="${jdbc.url}" userid="${jdbc.username}" password="${jdbc.password}" 
                                    onerror="continue" encoding="${project.build.sourceEncoding}">
                                    <classpath refid="maven.test.classpath" />
                                    <transaction src="db/cms/wee_data.sql"/>
                                </sql>
                                <taskdef name="dbunit" classname="org.dbunit.ant.DbUnitTask" classpathref="maven.test.classpath" />
                                <!-- mysql、mssql -->
                                <dbunit driver="${jdbc.driver}" url="${jdbc.url}" userid="${jdbc.username}" password="${jdbc.password}">
                                    <dbconfig>
                                        <property name="datatypeFactory" value="${dbunit.datatype}" />
                                    </dbconfig>
                                    <classpath refid="maven.test.classpath" />
                                    <operation type="INSERT" src="db/cms/wee_data.xls" format="xls" transaction="true"/>
                                </dbunit>
                            </target>
                        </configuration>
                    </plugin>
                </plugins>
            </build>
        </profile>
    </profiles>
</project>

文件中的wee.properties中需要定义好datasouce

#============================#
#===== Database sttings =====#
#============================#

#mysql database setting
jdbc.type=mysql
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/learn_maven?useUnicode=true&characterEncoding=utf-8
jdbc.username=root
jdbc.password=

最后执行批处理文件

@echo off
echo [INFO] Re-create the schema and provision the sample data.
pause

cd %~dp0
cd ..

set path=%MAVEN_HOME%/bin;%windir%/system32;%path%
call mvn antrun:run -Pinit-db

cd db
pause

导入成功

Paste_Image.png