今天做了一个将Excel中的数据读出,再写入到数据库指定的表中去,在网上查看了一下资料,通过Jxl完成了此项功能,但是程序可能还有问题,只能对Excel与数据表的结构相同的情况才能写入,否则就会报异常
PS:方法也是随便写的,并不能保证一定所有的特殊情况下都能运行,比如当Excel在第一行是个表格头的时候,读取Excel文件的时间就没去过滤这一行,当要写入数据库的时候就可能会出现数据类型不匹配而报错,如果有时间的朋友可以帮着修改一下,最好能将代码也发我一份,我的邮箱是:kyo153@163.com
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;
}
}