Jxl将Excel中的数据写入数据库

今天做了一个将Excel中的数据读出，再写入到数据库指定的表中去，在网上查看了一下资料，通过Jxl完成了此项功能，但是程序可能还有问题，只能对Excel与数据表的结构相同的情况才能写入，否则就会报异常

 

PS：方法也是随便写的，并不能保证一定所有的特殊情况下都能运行，比如当Excel在第一行是个表格头的时候，读取Excel文件的时间就没去过滤这一行，当要写入数据库的时候就可能会出现数据类型不匹配而报错，如果有时间的朋友可以帮着修改一下，最好能将代码也发我一份，我的邮箱是：[e-mail]

 

1.读入Excel

package com.yw.core;

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

import jxl.BooleanCell;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ExcelRead {
	public Map<Integer,List<Object>> readExcel(String pathName,int sheetId) throws BiffException, IOException{
		Workbook book = Workbook.getWorkbook(new File(pathName));
		Sheet sheet = book.getSheet(sheetId);
		Map<Integer,List<Object>> map = new LinkedHashMap<Integer, List<Object>>();
		int rows = sheet.getRows();
		int columns = sheet.getColumns();
		
		for (int i = 0; i < rows; i++) {
			List<Object> list = new ArrayList<Object>();
			for (int j = 0; j < columns; j++) {
				Cell cell = sheet.getCell(j, i);				
				list.add(getExcelDate(cell));
			}
			map.put(i, list);
		}
		System.out.println(map);
		for (Object object : map.values()) {
			System.out.println(object);
		}
		book.close();
		return map;
	}
	
	public Object getExcelDate(Cell cell){
		if(cell.getType()==CellType.NUMBER){
			NumberCell number = (NumberCell) cell;
			return number.getValue();
		}else if (cell.getType() == CellType.LABEL){
			LabelCell label = (LabelCell) cell;
			return label.getString();
		}else if (cell.getType() == CellType.BOOLEAN){
			BooleanCell bool = (BooleanCell) cell;
			return bool.getValue();
		}else if (cell.getType() == CellType.DATE){
			DateCell d = (DateCell) cell;
			return d.getDate();
		}else{
			return cell.getContents();
		}
	}
	
	public static void main(String[] args) throws BiffException, IOException {
		ExcelRead read = new ExcelRead();
		read.readExcel("book1.xls", 0);
		
	}
}

 

2.写入数据库，这里只有二个方法，一个是Insert与Query，本身这里也只是做了个通用的方法，没有想过特别的情况

如果有数据不对，或是为空是否出现问题也没有去进行Check，以后有时间再修改吧

 

package com.yw.core;

import java.io.File;
import java.io.IOException;
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.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class DBManager {
	private Connection conn = null;
	private Config config = new Config();

	public void getConnection(File file) throws ClassNotFoundException, IOException,
			SQLException {
		config.reloadConfig(file);
		System.out.println(config.toString());
		Class.forName(config.getClassname());
		if (conn == null || conn.isClosed()) {
			conn = DriverManager.getConnection(config.getUrl(), config
					.getUsername(), config.getPassword());
		}
	}

	public void close() throws SQLException {
		conn.close();
	}

	public boolean insert(String tableName, Object... obj) throws SQLException {
		int length = obj.length;
		PreparedStatement ps = null;
		StringBuilder builder = new StringBuilder();
		for (int i = 0; i < length; i++) {
			builder.append("?,");
		}
		builder.delete(builder.length()-1, builder.length());
		String sql = "insert into " + tableName + " values("+builder.toString()+")";
		System.out.println(sql);
		
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				ps.setObject(i+1, obj[i]);
			}
			if(ps.executeUpdate()>0){
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			ps.close();
		}
		return false;
	}
	
	public Map<Integer,List<Object>> query(String tableName) throws SQLException{
		
		Map<Integer, List<Object>> map = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSetMetaData metaData = null;
		try {
			map = new LinkedHashMap<Integer, List<Object>>();
			String sql = "select * from " + tableName;
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			int count = 0;
			while(rs.next()){
				List<Object> list = new ArrayList<Object>();
				for (int j = 1; j <= columnCount; j++) {
					list.add(rs.getObject(j));
				}
				map.put(count, list);
				count++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			rs.close();
			ps.close();
		}
		return map;
	}
	
}

 

3.写了一个Config的方法，读取Properties文件的类，用来实时的加载DB的配置信息

 

package com.yw.core;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Properties;

public class Config {
	private String username;
	private String password;
	private String classname;
	private String url;

	public String getUsername() {
		return username;
	}

	public String getPassword() {
		return password;
	}

	public String getClassname() {
		return classname;
	}

	public String getUrl() {
		return url;
	}

	public void reloadConfig(File file) throws IOException{
		FileInputStream in = null;
		if(file == null){
			in = new FileInputStream(new File("config.properties"));
		}else{
			in = new FileInputStream(file);
		}
		
		Properties prop = new Properties();
		prop.load(in);
		username = prop.getProperty("username");
		password = prop.getProperty("password");
		classname = prop.getProperty("classname");
		url = prop.getProperty("url");
                in.close();
	}
	
	public String toString(){
		return username + "\n" + password + "\n" + classname + "\n" + url;
	}
}