Jxl将Excel中的数据写入数据库--->现成code

来源：互联网发布：raphael js api 编辑：程序博客网时间：2024/05/20 02:28

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

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

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

1.读入Excel

Java代码

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 = ; i < rows; i++) {
List<Object> list = new ArrayList<Object>();
for (int j = ; 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", );
}
}

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

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

Java代码

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 = ; 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 = ; i < obj.length; i++) {
ps.setObject(i+1, obj[i]);
}
if(ps.executeUpdate()>){
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 = ;
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的配置信息

Java代码

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;
}
}

PS：

使用jxl读取excel，首先需要把jxl.jar导入工程中:

1：首先需要构建workbook

2：然后读取sheet

3：最后读取单元个cell或者读取row

代码如下：

1Workbook wb = Workbook.getWorkbook(new File("E:/接口.xls"));
2Sheet sheet = wb.getSheet(0);
3for (int i = 0; i < sheet.getRows(); i++) {
4   Cell[] cell = sheet.getRow(i);
5   for (int j = 0; j < cell.length; j++) {
6    System.out.println("cell["+j+"]:" + cell[j].getContents());
7   }
8}

就是这么简单，但是有一点需要注意的是，目前还不支持2007版的excel，只支持2003的版本。

需要你可以直接把07的另存为03就可以读取了！