能实现一对多关联的通用EXCEL导入功能

发一个本人写的通用EXCEL导入功能，看了一下其他人发过的类似功能的导入组件，相比起来，我写的这个优点在于，能实现数据的一对一、一对多关联；直接使用SQL语句插入数据，不用通过实体类，执行效率会高一些。缺点在于，需要进行XML文件的配置，会有点麻烦。

首先举例说下使用方法。要导入以下两个EXCEL文件

--------student.xls--------------

学号	姓名	性别
2010001	张三	男
2010002	李四	女

------------sex.xls---------------

代码标题

1 男

2 女

其中学生信息表中的性别字段为外键，XML配置如下

EXCEL文件与数据表映射关系excel-table-mapping.xml

<?xml version="1.0" encoding="UTF-8"?>

<import-name>sex</import-name>

<first-row>1</first-row>

<last-row>0</last-row>

<table-name>t_sex</table-name>

<xml-name>com/ronghz/excel/t_sex.xml</xml-name>

</mapping>

<import-name>student</import-name>

<first-row>1</first-row>

<last-row>0</last-row>

<table-name>t_student</table-name>

<xml-name>com/ronghz/excel/t_student.xml</xml-name>

</mapping>

</mappings>

EXCEL文件列与数据库字段的映射关系

t_sex.xml

<?xml version="1.0" encoding="UTF-8"?>

<excel-order>0</excel-order>

<excel-name>代码</excel-name>

<column-name>code</column-name>

<column-type>varchar</column-type>

</mapping>

<excel-order>1</excel-order>

<excel-name>内容</excel-name>

<column-name>title</column-name>

<column-type>varchar</column-type>

</mapping>

</mappings>

t_student.xml

<?xml version="1.0" encoding="UTF-8"?>

<excel-order>0</excel-order>

<excel-name>学号</excel-name>

<column-name>code</column-name>

<column-type>varchar</column-type>

</mapping>

<excel-order>1</excel-order>

<excel-name>姓名</excel-name>

<column-name>name</column-name>

<column-type>varchar</column-type>

</mapping>

<excel-order>2</excel-order>

<excel-name>性别</excel-name>

<column-name>sex</column-name>

<column-type>foreignkey</column-type>

<outtable-name>t_sex</outtable-name>

<outtable-key>t_sex_id</outtable-key>

<outtable-value>title</outtable-value>

</mapping>

</mappings>

调用相关类执行导入

public static void main(String[] args) {

ImportExcel ie = new ImportExcel();

try {

ie.execute("sex","D:/sex.xls");

ie.execute("student","D:/student.xls");

} catch (SQLException e) {

e.printStackTrace();

} catch (ParseException e) {

e.printStackTrace();

}

建立数据库

CREATE DATABASE `xls`;

CREATE TABLE `xls`.`t_sex` (

`t_sex_id` int(10) unsigned NOT NULL auto_increment,

`code` varchar(45) NOT NULL,

`title` varchar(45) NOT NULL,

PRIMARY KEY (`t_sex_id`)

);

CREATE TABLE `xls`.`t_student` (

`t_student_id` int(10) unsigned NOT NULL auto_increment,

`code` varchar(45) NOT NULL,

`name` varchar(45) default NULL,

`sex` int(10) unsigned default NULL,

PRIMARY KEY (`t_student_id`);

主要代码解释：

一共包含5个类：

Conn.java 数据库连接类

FileMapping.java

ImportExcel.java

TableMapping.java

TableMappingArray.java

ImportExcel.java的代码package com.ronghz.excel;

import java.io.*;

import java.sql.*;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import jxl.*;

import jxl.read.biff.BiffException;

/**

* Email:[email protected]

* Blog:http://blog.csdn.net/dreamdiary

* 欢迎转载；转载时请注明出处

public class ImportExcel {

/**

* 执行导入操作

* @param importName导入的名称

* @param excelPath文件路径

* @return

* @throws SQLException

* @throws ParseException

public boolean execute(String importName, String excelPath) throws SQLException, ParseException {

FileMapping fileMapping = new FileMapping(importName);// 通过导入类型名，找到对应的表名、字段映射配置文件

String xmlName = fileMapping.getXmlName();

TableMappingArray mappingArray = new TableMappingArray(xmlName);// 把以上代码找到的XML文件读取到数组中

TableMapping[] array = mappingArray.getArray();

Conn conn = new Conn();

try {

conn.beginTrans();

int haveFK = 0;// 外键关联的数目

String addTempColumn = "ALTER TABLE " + fileMapping.getTableName();

String delTempColumn = "ALTER TABLE " + fileMapping.getTableName();

String[] updateFK = new String[array.length];

String preSql = "UPDATE $tableName$ t1 SET $tableKey$=(SELECT $outtablKey$ FROM $outtableName$ t2 WHERE t1.$tableKeyTmp$ = t2.$outtableValue$)"

+ " WHERE EXISTS ( SELECT 1 FROM $outtableName$ t3 WHERE t1.$tableKeyTmp$=t3.$outtableValue$)";

String columns = "";

String values = "";

for (int i = 0; i < array.length; i++) {

if (array[i].getColumnType() == TableMapping.TYPE_FOREIGNKEY) {//如果该列类型属于外键

// 生成插入数据的SQL语句

columns += array[i].getColumnName() + "_tmp,";

// 增加临时字段用于保存导入的数据，导入完成后再将临时字段删除

addTempColumn += " ADD COLUMN " + array[i].getColumnName() + "_tmp VARCHAR(100),";

delTempColumn += " DROP COLUMN " + array[i].getColumnName() + "_tmp,";

// 把临时字段的数据转换成外键

String update = preSql.replace("$tableName$", fileMapping.getTableName());

update = update.replace("$tableKey$", array[i].getColumnName());

update = update.replace("$tableKeyTmp$", array[i].getColumnName() + "_tmp");

update = update.replace("$outtableName$", array[i].getOuttableName());

update = update.replace("$outtablKey$", array[i].getOuttableKey());

update = update.replace("$outtableValue$", array[i].getOuttableValue());

updateFK[haveFK++] = update;

} else {

columns += array[i].getColumnName() + ",";

}

values += "?,";

}

if (haveFK > 0) {//如果有类型为外键的列

conn.executeUpdate(addTempColumn.substring(0, addTempColumn.length() - 1));

}

//读取EXCEL文件的内容

String insertSQL = "INSERT INTO " + fileMapping.getTableName() + " (" + columns.substring(0, columns.length() - 1)+ ") " +

"VALUES (" + values.substring(0, values.length() - 1) + ")";

Workbook book = null;

book = Workbook.getWorkbook(new java.io.File(excelPath));

jxl.Sheet sheet = book.getSheet(0);

int sheetRows = sheet.getRows() > fileMapping.getLastRow() ? sheet.getRows() : fileMapping.getLastRow();

Cell cell;

PreparedStatement ps = conn.prepareStatement(insertSQL);

for (int row = fileMapping.getFirstRow(), count = 0; row < sheetRows; row++, count++) {

for (int i = 0; i < array.length; i++) {

cell = sheet.getCell(array[i].getExcelOrder(), row);

//根据数据库类型进行转换

switch (array[i].getColumnType()) {

case TableMapping.TYPE_INT:

ps.setInt(i + 1, cell.getContents().equals("") ? 0 : Integer.parseInt(cell.getContents()));

break;

case TableMapping.TYPE_DOUBLE:

ps.setDouble(i + 1, cell.getContents().equals("") ? 0 : Double.parseDouble(cell.getContents()));

break;

case TableMapping.TYPE_DATE:

ps.setTimestamp(i + 1, new Timestamp(parse(cell.getContents(), array[i].getColumnFormat()).getTime()));

break;

case TableMapping.TYPE_DATETIME:

ps.setTimestamp(i + 1, new Timestamp(parse(cell.getContents(), array[i].getColumnFormat()).getTime()));

break;

case TableMapping.TYPE_FOREIGNKEY:// 外键字段作为字符串类型插入到临时字段中

ps.setString(i + 1, cell.getContents());

break;

case TableMapping.TYPE_STRING:

ps.setString(i + 1, cell.getContents());

break;

}

ps.addBatch();

if (count > 500) {

count = 0;

ps.executeBatch();

}

ps.executeBatch();

if (haveFK > 0) {// 把临时字段的数据转换成外键

for (int i = 0; i < haveFK; i++) {

conn.executeUpdate(updateFK[i]);

}

//删除临时字段

conn.executeUpdate(delTempColumn.substring(0, delTempColumn.length() - 1));

}

conn.commit();

return true;

} catch (BiffException ex) {

System.out.println("Read Excel file failed!");

System.out.println("File name: " + excelPath);

} catch (IOException ex) {

System.out.println("Read Excel file failed!");

System.out.println("File name: " + excelPath);

} catch (SQLException e) {

conn.rollback();

System.out.println("DateBase ERROR!");

e.printStackTrace();

throw e;

} finally {

if (conn != null) {

conn.close();

conn = null;

}

return true;

}

public java.util.Date parse(String time, String format) throws ParseException {

if (time == null) {

return null;

} else {

SimpleDateFormat sdf = new SimpleDateFormat(format);

java.util.Date d = sdf.parse(time);

return d;

}

public static void main(String[] args) {

ImportExcel ie = new ImportExcel();

try {

ie.execute("sex","D:/sex.xls");

ie.execute("student","D:/student.xls");

} catch (SQLException e) {

e.printStackTrace();

} catch (ParseException e) {

e.printStackTrace();

}

如果要应用于WEB项目，只需要增加上传文件的操作，并把上传后的文件路径传到execute()函数中就可以了。

代码比较多，不全部贴出来了，需要的请到以下地址下载http://download.csdn.net/source/2464234

能实现一对多关联的通用EXCEL导入功能

继续阅读

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Small tricks

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

GridView终极用法(一)

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述