这是我写的一个导数到数据库,首先读取excel文件,并返回一个List,然后你就度List数据调用你保存数据的代码,就可以了。 public List jxlImportExcelToDate(String FileName) { try { //读取excel数据返回到此List List result = new ArrayList();
File uploadFileName = new File(FileName); InputStream is = new FileInputStream(uploadFileName); // 创建一个excel文件对象 jxl.Workbook excel = Workbook.getWorkbook(is); // 获得第一个excel文件的sheet Sheet sheet = excel.getSheet(0); // 获取sheet单元的总行数 int rows = sheet.getRows(); // 获得sheet单元的总列数 int columns = sheet.getColumns();
// 读取数据 for (int r = 0; r < rows; r++) {
//每行数据 String[] rowDates = new String[columns]; for (int c = 0; c < columns; c++) { Cell cell = sheet.getCell(c, r); String cellValue = cell.getContents(); rowDates[c] = cellValue; }
//读取excel数据返回到此List
List result = new ArrayList();
File uploadFileName = new File(FileName);
InputStream is = new FileInputStream(uploadFileName); // 创建一个excel文件对象
jxl.Workbook excel = Workbook.getWorkbook(is);
// 获得第一个excel文件的sheet
Sheet sheet = excel.getSheet(0);
// 获取sheet单元的总行数
int rows = sheet.getRows();
// 获得sheet单元的总列数
int columns = sheet.getColumns();
// 读取数据
for (int r = 0; r < rows; r++) {
//每行数据
String[] rowDates = new String[columns];
for (int c = 0; c < columns; c++) {
Cell cell = sheet.getCell(c, r);
String cellValue = cell.getContents();
rowDates[c] = cellValue;
}
//将值加入到List中返回
result.add(rowDates);
} // 关闭excel对象
excel.close();
System.out.println("Read Excel file sucess!");
return result;
} catch (Exception e) {
System.out.println(e.getMessage());
return null;
} }
/*
* @author pxc
* @version 1.0
* @company ish
* @descript 从目录中读取excel文件,读完后删除
*/
import jxl.*;
import jxl.read.biff.BiffException;
import jxl.write.*;
import java.io.*;
import java.util.*;
import java.text.*;
import java.sql.*;public class ReadExcel extends Thread{
private Workbook workbook;
private Sheet sheet;
private File f;
private Properties setting=new Properties();
private Connection conn=null;
private Statement stmt=null;
private PreparedStatement ps=null;
PreparedStatement pst=null;
private DBPool dbp;
private String ssql="select * from ABOUT_EDI_CASED where edi_truck_no=? ";
/*
* @descript:配置文件
*/
public ReadExcel(){
try{
setting.load(new FileInputStream("config.ini"));
}catch(IOException e){
setting.setProperty("path","e:/EDI/edi.xls");
try{
setting.store(new FileOutputStream("config.ini"),"config");
}catch(Exception ex){
ex.printStackTrace();
}
}
}
/*
* 读取路径
*/
public String getPath(){
return setting.getProperty("path");
}
/*
* 设置路径
*/
public void setPath(String path){
try{
setting.setProperty("path",path);
setting.store(new FileOutputStream("config.ini"),"Path Config");
}catch(IOException e){
System.err.println("设置问题"+e.getMessage());
}
}
/*
* 读取excel文件
*/
public void readExcel(File f){
String insertSql="insert into ABOUT_EDI_CASED(edi_date,truck_no,edi_asn,edi_truck_no,receipt_date,write_date,"
+" apply_date,trans_company,get_date,put_date,edi_write_date,edi_apply_date,edi_out_date,"
+" send_date,over_date,bind_date,arrive_date,bg_time,check_time,re)values(";
//更新语句
String updateSql="update ABOUT_EDI_CASED set edi_date=?,truck_no=?,edi_asn=?," +
"receipt_date=?,write_date=?,apply_date=?,trans_company=?,get_date=?,put_date=?," +
"edi_write_date=?,edi_apply_date=?,edi_out_date=?,send_date=?,over_date=?,bind_date=?," +
"arrive_date=?,bg_time=?,check_time=?,re=? where edi_truck_no=?";
dbp=new DBPool();
// 更新语句
String updateSqlxx="update ABOUT_EDI_CASED set truck_no='111111111111pxcheng' where edi_truck_no='18'";
try{
conn=dbp.getConnection();
stmt=conn.createStatement();
//ps=conn.prepareStatement(updateSql);
}catch(SQLException ex){
System.out.println("创建stmt错误"+ex.getMessage());
}
try{
workbook=Workbook.getWorkbook(f);
sheet=workbook.getSheet(0);
//int r行数 c为列数
int r=sheet.getRows();
int c=sheet.getColumns();
//System.out.println("行有:"+r+" 列有:"+c);
System.out.println("开始读取-----");
for(int i=1;i<r;i++){
String sql=insertSql;
List list=new ArrayList();
for(int j=0;j<c;j++){
//System.out.print(sheet.getCell(j,i).getContents()+" ");
String data=sheet.getCell(j,i).getContents();
list.add(data);
}
for(int k=0;k<list.size();k++){
if(k!=list.size()-1){
sql+="'"+(String)list.get(k)+"',";
}else{
sql+="'"+(String)list.get(k)+"')";
}
/* if(k==3){
ps.setString(20,(String)list.get(3));
// System.out.println(list.size()+"=="+(String)list.get(3));
}else if(k<3){
ps.setString(k+1,(String)list.get(k));
}else if(k>3){
ps.setString(k,(String)list.get(k));
}
ps.setString(k+1,(String)list.get(k));
*/
}
//20060622 edit pxcheng 如果为""不做任何处理
if(((String)list.get(3)).trim().equals("")){
continue;
}
if(isHave((String)list.get(3))){
//System.out.println("有数据《《《《《《《");
String sqld=getSql(list,(String)list.get(3));
stmt.executeUpdate(sqld);
}else{
stmt.executeUpdate(sql);
}
}
System.out.println("写入完毕----");
workbook.close();
}catch(BiffException e){
System.out.println("读excel BiffException "+e.getMessage());
}catch(IOException e){
System.out.println("读excel IOException "+e.getMessage());
}catch(Exception e){
System.out.println("读excel Exception "+e.getMessage());
}
dbp.freeConnection(conn); //释放Connection
}
/*
* 判断数据是否有
*/
public boolean isHave(String value){
try{
pst=conn.prepareStatement(ssql);
pst.setString(1,value);
ResultSet rs=pst.executeQuery();
if(rs.next()){
return true;
}else{
return false;
}
}catch(SQLException e){
System.out.println("查询有无");
return false;
}
}
public String getSql(List list,String value){
return "update ABOUT_EDI_CASED set edi_date='"+(String)list.get(0)+"',truck_no='"+(String)list.get(1)+"',edi_asn='"+(String)list.get(2)+"'," +
"receipt_date='"+(String)list.get(4)+"',write_date='"+(String)list.get(5)+"',apply_date='"+(String)list.get(6)+"',trans_company='"+(String)list.get(7)+"',get_date='"+(String)list.get(8)+"',put_date='"+(String)list.get(9)+"'," +
"edi_write_date='"+(String)list.get(10)+"',edi_apply_date='"+(String)list.get(11)+"',edi_out_date='"+(String)list.get(12)+"',send_date='"+(String)list.get(13)+"',over_date='"+(String)list.get(14)+"',bind_date='"+(String)list.get(15)+"'," +
"arrive_date='"+(String)list.get(16)+"',bg_time='"+(String)list.get(17)+"',check_time='"+(String)list.get(18)+"',re='"+(String)list.get(19)+"' where edi_truck_no='"+value+"'";
}
public void run(){
while(true){
synchronized(this){
try{
Thread.sleep(5000);
}catch(Exception e){}
SimpleDateFormat sf=new SimpleDateFormat("yyMMddHHmmss");
String time=sf.format(new java.util.Date());
File f0=new File(getPath());
//System.out.println(getPath());
if(f0.exists()){
File f1=new File("e:/EDI/"+time+".xls");
f0.renameTo(f1);
readExcel(f1);
f1.delete();
}
try{
Thread.sleep(1000*60*60);
}catch(Exception ex){}
}}
}
public static void main(String args[]){
ReadExcel re=new ReadExcel();
re.start();
/* SimpleDateFormat sf=new SimpleDateFormat("yyMMddHHmmss");
String time=sf.format(new java.util.Date());
ReadExcel re=new ReadExcel();
File f0=new File(re.getPath());
System.out.println(re.getPath());
// File f1=new File("e:/EDI/"+time+".xls");
// f0.renameTo(f1);
re.readExcel(f0);
// try{
// Thread.sleep(10000);
// }catch(Exception e){
// e.printStackTrace();
// }
// f1.delete();*/
}
}
/**
* 上传文件
* @param req
* @return
*/
public void upload(HttpServletRequest req) throws Exception {
String realFilePath = "C:\\"; //上传到服务器后文件对应服务器的绝对路径
String realName = ""; //上传文件的名字 try {
DiskFileUpload upload = new DiskFileUpload();
// 设置允许用户上传文件大小--10MB
upload.setSizeMax(10 * 1024 * 1024);
// 设置最多只允许在内存中存储的数据,单位:字节
upload.setSizeThreshold(4096);
// 设置一旦文件大小超过getSizeThreshold()的值时数据存放在硬盘的目录
upload.setRepositoryPath(realFilePath);
// 开始读取上传信息
List fileItems = upload.parseRequest(req);
if(fileItems != null){
Iterator it = fileItems.iterator();
while (it.hasNext()) {
FileItem item = (FileItem) it.next();
if (!item.isFormField()) {
String name = item.getName();
realName = name.substring(name.lastIndexOf("B"));
long size = item.getSize();
if ((name == null || name.equals("")) && size == 0)
continue;
//保存上传的文件到指定的目录
realName = realName.replace(':', '_');
realName = realName.replace('\\', '_');
File writeFile = new File(realFilePath, realName);
item.write(writeFile);
}
}
} } catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获得Excel中的数据并以Excel返回
* @param path
* @return
*/
public void updateKaoqin(String path, String TimeUp, String TimeDown) throws Exception {
try {
Workbook book = Workbook.getWorkbook(new File(path));
if(book.getNumberOfSheets() > 0){
Sheet sheet = book.getSheet(0);
for (int i = 0; i < sheet.getRows(); i++) {
try {
xingzheng_kaoqin log = new xingzheng_kaoqin();
for (int j = 0; j < sheet.getColumns(); j++) {
Cell cell = sheet.getCell(j, i);
switch (cell.getColumn()) {
case 0 :
log.setRecNo(cell.getContents());
break;
case 2 :
log.setCardNo(cell.getContents());
break;
case 5 :
log.setExpeDate(cell.getContents());
break;
case 6 :
log.setExpeTime(cell.getContents());
break;
}
}
log = format(log);
log.setExpeDateAll(log.getExpeDate()+" "+log.getExpeTime());
if(compare(log.getExpeDate(), TimeUp, TimeDown))
insert(log);
} catch (Exception e1) {
// TODO 自动生成 catch 块
System.out.println("获得Excel数据并插入数据库时发生错误!"+e1.toString());
continue;
}
}
}
book.close();
} catch (NumberFormatException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
} catch (BiffException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
} catch (IOException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
}
如果单元格中的数据是数值类型什么的,只是String cellValue = cell.getContents();
rowDates[c] = cellValue;吗?类型转换在存数据库的时候写吧?谢谢你以前的帮助,在那个spring的配置问题上