JXL导入导出Excel到数据库

项目结构，需要用到的第三方jar为:jxl.jar用于读写Excel，mysql-connector-java-5.1.26.jar用于连接MySQL数据库

注意：导入导出的表格在项目名目录下

db.properties，这个文件设置连接数据库的一些属性

dbDriver = com.mysql.jdbc.Driver
dbUrl = jdbc:mysql://localhost:3306/scoreman?useUnicode=true&characterEncoding=UTF-8
dbUserName = root
dbPassword = root

PropertyUtils.java，这个用来读取db.properties的属性

package com.example.Utils;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public class PropertyUtils {
	static String fileName = "/db.properties";
	static Properties propertie = null;

	static String dbDriver;
	static String dbUrl;
	static String dbUserName;
	static String dbPassword;
	static {
		try {
			propertie = new Properties();
			InputStream is = PropertyUtils.class.getResourceAsStream(fileName);
			propertie.load(is);
			dbDriver = propertie.getProperty("dbDriver");
			dbUrl = propertie.getProperty("dbUrl");
			dbUserName = propertie.getProperty("dbUserName");
			dbPassword = propertie.getProperty("dbPassword");
			is.close();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	public static String getDbDriver() {
		return dbDriver;
	}

	public static void setDbDriver(String dbDriver) {
		PropertyUtils.dbDriver = dbDriver;
	}

	public static String getDbUrl() {
		return dbUrl;
	}

	public static void setDbUrl(String dbUrl) {
		PropertyUtils.dbUrl = dbUrl;
	}

	public static String getDbUserName() {
		return dbUserName;
	}

	public static void setDbUserName(String dbUserName) {
		PropertyUtils.dbUserName = dbUserName;
	}

	public static String getDbPassword() {
		return dbPassword;
	}

	public static void setDbPassword(String dbPassword) {
		PropertyUtils.dbPassword = dbPassword;
	}

}

JxlUtils.java，这个里面有两个方法，一个用来将List转成Excel表格 ，另一个用来将Excel表格转成List

package com.example.Utils;

import java.io.File;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class JxlUtils {
	public static void jxlListToExl(List<Map> tableContent,String outPutFileName) {
		WritableWorkbook book = null;
		try {
			File os = new File(System.getProperty("user.dir") + outPutFileName);
			if (!os.exists()) {
				// 如果指定文件不存在，则新建该文件
				os.createNewFile();
			}

			book = Workbook.createWorkbook(os);// 创建一个新的写入工作簿
			WritableSheet sheet = book.createSheet("sheet1", 1);

			List<String> tableHeader = new ArrayList<String>();
			if (tableContent.size() >= 1) {
				Map map = tableContent.get(0);
				Set keySet = map.keySet();
				for (Object keyName : keySet) {
					tableHeader.add(keyName.toString());
					System.out.println(keyName);
				}
			}else{
				return;
			}

			// 第一行写入表头
			for (int i = 0; i < tableHeader.size(); i++) {
				Label lable = new Label(i, 0, tableHeader.get(i));
				sheet.addCell(lable);
			}

			// 后续行写入数据
			for (int i = 0; i < tableContent.size(); i++) {
				Map map = tableContent.get(i);
				for (int j = 0; j < tableHeader.size(); j++) {
					System.out.println(map.get(tableHeader.get(j)));
					Label lable = new Label(j, i + 1, map.get(
							tableHeader.get(j)).toString());
					sheet.addCell(lable);
				}

			}

			book.write();
			System.out.println("工作簿写入数据成功！");
			book.close();// 关闭
		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	public static List<Map> jxlExlToList(String inPutFileName) {
		Workbook book = null;
		List<Map> list = null;
		try {
			File os = new File(System.getProperty("user.dir") + inPutFileName);
			if (!os.exists()) {
				// 如果指定文件不存在，则新建该文件
				os.createNewFile();
			}

			book = Workbook.getWorkbook(os);// 创建一个新的写入工作簿
			Sheet sheet = book.getSheet(0);
			int totalRows = sheet.getRows();
			int totalColumns = sheet.getColumns();
			Cell[] cell = sheet.getRow(0);
			if(totalColumns<=0){
				return null;
			}
			//读取第一行作为Map中的key
			List tableHeaderlist = new ArrayList();
			for (int i = 0; i < totalColumns; i++) {
				tableHeaderlist.add(cell[i].getContents());
			}

			//将每一行存为Map集合，然后存为list
			list = new ArrayList();
			Map rowData = new LinkedHashMap();
			for (int i = 1; i < totalRows; i++) {
				cell = sheet.getRow(i);
				rowData = new LinkedHashMap(totalColumns);
				for (int j = 0; j < totalColumns; j++) {
					rowData.put(tableHeaderlist.get(j), cell[j].getContents());
				}
				list.add(rowData);
			}

			System.out.println("工作簿读取数据成功！");
			
			book.close();// 关闭
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return list;
	}

}

DbUtils.java，这个代码是抄的，作用是将查询的RS结果集转成List

package com.example.Utils;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class DbUtils {
	public static List<Map> rsToList(ResultSet rs) throws java.sql.SQLException {
		if (rs == null)
			return Collections.EMPTY_LIST;
		ResultSetMetaData md = rs.getMetaData(); // 得到结果集(rs)的结构信息，比如字段数、字段名等
		int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数
		List<Map> list = new ArrayList();
		Map rowData = new LinkedHashMap();
		while (rs.next()) {
			rowData = new LinkedHashMap(columnCount);
			for (int i = 1; i <= columnCount; i++) {
				rowData.put(md.getColumnName(i), rs.getObject(i));
			}
			list.add(rowData);
		}
		return list;
	}

}

DBconn.java，里面有两个方法，一个是查询数据库得到List 集合后调用JxlUtils工具类写入Excel，另一个是调用JxlUtils工具类得到List构造Insert语句插入数据库

package com.example.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.example.Utils.DbUtils;
import com.example.Utils.JxlUtils;
import com.example.Utils.PropertyUtils;

public class DBconn {
	static String driver = PropertyUtils.getDbDriver();
	static String url = PropertyUtils.getDbUrl();
	static String user = PropertyUtils.getDbUserName();
	static String psw = PropertyUtils.getDbPassword();

	// 例如dbName = "student";outPutfileName="\\output.xls"
	public static void exportDbToExl(String dbName, String outPutfileName) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String sql = "select * from " + dbName;

		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, psw);
			System.out.println("-------连接成功------");
			pstmt = conn.prepareStatement(sql);
			System.out.println(pstmt);
			rs = pstmt.executeQuery();
			ResultSetMetaData rm = rs.getMetaData();

			List<Map> tableContent = DbUtils.rsToList(rs);

			JxlUtils.jxlListToExl(tableContent, outPutfileName);

			pstmt.close();
			rs.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void importExlToDb(String dbName, String inPutFileName) {
		List<Map> list = JxlUtils.jxlExlToList(inPutFileName);
		System.out.println(list);

		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String sql = "insert into " + dbName + " ";
		// INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

		List<String> columnslist = new ArrayList<String>();
		List<String> valueslist = new ArrayList<String>();
		// 提取表头
		if (list.size() >= 1) {
			Map map = list.get(0);
			Set keySet = map.keySet();
			for (Object keyName : keySet) {
				columnslist.add(keyName.toString());
				valueslist.add("?");
				System.out.println(keyName);
			}
		} else {
			return;
		}
		String columnsStr = columnslist.toString().substring(1,
				columnslist.toString().indexOf("]"));
		String valuesStr = valueslist.toString().substring(1,
				valueslist.toString().indexOf("]"));
		System.out.println(columnsStr);
		System.out.println(valuesStr);
		sql = sql + " (" + columnsStr + ") values (" + valuesStr + ")";
		System.out.println(sql);

		// 写入数据库
		for (int i = 0; i < list.size(); i++) {
			Map map = list.get(i);
			try {
				Class.forName(driver);
				conn = DriverManager.getConnection(url, user, psw);
				System.out.println("-------连接成功------");
				pstmt = conn.prepareStatement(sql);

				for (int j = 0; j < columnslist.size(); j++) {
					System.out.println(map.get(columnslist.get(j)));
					pstmt.setString(j + 1, map.get(columnslist.get(j))
							.toString());

				}

				System.out.println(pstmt);
				pstmt.executeUpdate();
				pstmt.close();
				conn.close();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		//导入数据库完成
		System.out.println("导入数据库完成");

	}
}

Test.java，主类，用于测试最终效果

package com.example;

import com.example.db.DBconn;

public class Test {
	public static void main(String[] args) {
		System.out.println("HelloWorld");
		//例如dbName = "student";outPutfileName="\\output.xls"
		//DBconn.exportDbToExl("dbuser", "\\output3.xls");
		DBconn.importExlToDb("dbuser", "\\output3.xls");

	}
}

只是简单的实现了基本功能，没有考虑数据类型，读写Excel的时候一律当Sting处理的。插入的时候没有考虑主键重复的情况