转 java实现读取excel

本文是读者转载的作品，向作者智慧云端日记致敬。
作者：爱读才能立世
出处：（http://www.cnblogs.com/zyw-205520/p/3762954.html）
版权声明：本文的版权归作者与博客园共有。转载时须注明本文的详细链接，否则作者将保留追究其法律责任。

实现的功能：

连接数据库的工具类:

package com.javen.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBhepler {
/*String driver = “com.microsoft.sqlserver.jdbc.SQLServerDriver”;
String url = “jdbc:sqlserver://127.0.0.1;DatabaseName=javenforexcel”;*/

String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/javenforexcel";


Connection con = null;
ResultSet res = null;

public void DataBase() {
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, "root", "root");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
              System.err.println("装载 JDBC/ODBC 驱动程序失败。" );  
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            System.err.println("无法连接数据库" ); 
            e.printStackTrace();
        }
}

// 查询
public ResultSet  Search(String sql, String str[]) {
    DataBase();
    try {
        PreparedStatement pst =con.prepareStatement(sql);
        if (str != null) {
            for (int i = 0; i < str.length; i++) {
                pst.setString(i + 1, str[i]);
            }
        }
        res = pst.executeQuery();

    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return res;
}

// 增删修改
public int AddU(String sql, String str[]) {
    int a = 0;
    DataBase();
    try {
        PreparedStatement pst = con.prepareStatement(sql);
        if (str != null) {
            for (int i = 0; i < str.length; i++) {
                pst.setString(i + 1, str[i]);
            }
        }
        a = pst.executeUpdate();
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return a;
}

}

表的实体如下:

package com.javen.entity;

/**
* @author Javen
* @Email zyw205@gmail.com
*
*/
public class StuEntity {
private int id;
private String name;
private String sex;
private int num;

public StuEntity() {
}
public StuEntity(int id, String name, String sex, int num) {
    this.id = id;
    this.name = name;
    this.sex = sex;
    this.num = num;
}

@Override
public String toString() {
    return "StuEntity [id=" + id + ", name=" + name + ", sex=" + sex
            + ", num=" + num + "]";
}
public int getId() {
    return id;
}
public void setId(int id) {
    this.id = id;
}
public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}
public String getSex() {
    return sex;
}
public void setSex(String sex) {
    this.sex = sex;
}
public int getNum() {
    return num;
}
public void setNum(int num) {
    this.num = num;
}

}

Java实现Excel导入数据核心类 读取Excel表中所有的数据、操作数据（查询、更新）:

package com.javen.service;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;

import com.javen.db.DBhepler;
import com.javen.entity.StuEntity;

/**
* @author Javen
* @Email zyw205@gmail.com
*
*/
public class StuService {
/**
* 查询stu表中所有的数据
* @return
*/
public static List getAllByDb(){
List list=new ArrayList();
try {
DBhepler db=new DBhepler();
String sql=”select * from stu”;
ResultSet rs= db.Search(sql, null);
while (rs.next()) {
int id=rs.getInt(“id”);
String name=rs.getString(“name”);
String sex=rs.getString(“sex”);
int num=rs.getInt(“num”);

            //System.out.println(id+" "+name+" "+sex+ " "+num);
            list.add(new StuEntity(id, name, sex, num));
        }

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return list;
}

/**
 * 查询指定目录中电子表格中所有的数据
 * @param file 文件完整路径
 * @return
 */
public static List<StuEntity> getAllByExcel(String file){
    List<StuEntity> list=new ArrayList<StuEntity>();
    try {
        Workbook rwb=Workbook.getWorkbook(new File(file));
        Sheet rs=rwb.getSheet("Test Shee 1");//或者rwb.getSheet(0)
        int clos=rs.getColumns();//得到所有的列
        int rows=rs.getRows();//得到所有的行

        System.out.println(clos+" rows:"+rows);
        for (int i = 1; i < rows; i++) {
            for (int j = 0; j < clos; j++) {
                //第一个是列数，第二个是行数
                String id=rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++
                String name=rs.getCell(j++, i).getContents();
                String sex=rs.getCell(j++, i).getContents();
                String num=rs.getCell(j++, i).getContents();

                System.out.println("id:"+id+" name:"+name+" sex:"+sex+" num:"+num);
                list.add(new StuEntity(Integer.parseInt(id), name, sex, Integer.parseInt(num)));
            }
        }
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } 
    return list;

}

/**
 * 通过Id判断是否存在
 * @param id
 * @return
 */
public static boolean isExist(int id){
    try {
        DBhepler db=new DBhepler();
        ResultSet rs=db.Search("select * from stu where id=?", new String[]{id+""});
        if (rs.next()) {
            return true;
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return false;
}

public static void main(String[] args) {
    /*List<StuEntity> all=getAllByDb();
    for (StuEntity stuEntity : all) {
        System.out.println(stuEntity.toString());
    }*/

    System.out.println(isExist(1));

}

}

数据的数据导入到Excel表:

package com.javen.excel;

import java.io.File;
import java.util.List;

import com.javen.entity.StuEntity;
import com.javen.service.StuService;

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

public class TestDbToExcel {

public static void main(String[] args) {
    try {
        WritableWorkbook wwb = null;

           // 创建可写入的Excel工作簿
           String fileName = "D://book.xls";
           File file=new File(fileName);
           if (!file.exists()) {
               file.createNewFile();
           }
           //以fileName为文件名来创建一个Workbook
           wwb = Workbook.createWorkbook(file);

           // 创建工作表
           WritableSheet ws = wwb.createSheet("Test Shee 1", 0);

           //查询数据库中所有的数据
           List<StuEntity> list= StuService.getAllByDb();
           //要插入到的Excel表格的行号，默认从0开始
           Label labelId= new Label(0, 0, "编号(id)");//表示第
           Label labelName= new Label(1, 0, "姓名(name)");
           Label labelSex= new Label(2, 0, "性别(sex)");
           Label labelNum= new Label(3, 0, "薪水(num)");

           ws.addCell(labelId);
           ws.addCell(labelName);
           ws.addCell(labelSex);
           ws.addCell(labelNum);
           for (int i = 0; i < list.size(); i++) {

               Label labelId_i= new Label(0, i+1, list.get(i).getId()+"");
               Label labelName_i= new Label(1, i+1, list.get(i).getName());
               Label labelSex_i= new Label(2, i+1, list.get(i).getSex());
               Label labelNum_i= new Label(3, i+1, list.get(i).getNum()+"");
               ws.addCell(labelId_i);
               ws.addCell(labelName_i);
               ws.addCell(labelSex_i);
               ws.addCell(labelNum_i);
           }

          //写进文档
           wwb.write();
          // 关闭Excel工作簿对象
           wwb.close();

    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } 
}

}

Excel表中的数据导入到MySql数据库

package com.javen.excel;

import java.util.List;

import com.javen.db.DBhepler;
import com.javen.entity.StuEntity;
import com.javen.service.StuService;

/**
* @author Javen
* @Email zyw205@gmail.com
*
*/
public class TestExcelToDb {
public static void main(String[] args) {
//得到表格中所有的数据
List listExcel=StuService.getAllByExcel(“d://book.xls”);
/*//得到数据库表中所有的数据
List listDb=StuService.getAllByDb();*/

    DBhepler db=new DBhepler();

    for (StuEntity stuEntity : listExcel) {
        int id=stuEntity.getId();
        if (!StuService.isExist(id)) {
            //不存在就添加
            String sql="insert into stu (name,sex,num) values(?,?,?)";
            String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+""};
            db.AddU(sql, str);
        }else {
            //存在就更新
            String sql="update stu set name=?,sex=?,num=? where id=?";
            String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+"",id+""};
            db.AddU(sql, str);
        }
    }
}

}

感谢作者，转发收藏，大家交流，谢谢