用java将Excel表格导入导出数据库

创建实体类

实体类里的属性和数据库表的列保持一致

package grgbanking.entity;

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


}

创建连接数据库的工具类

这里采用mysql的连接，注意要标明编码格式，防止出现乱码

package grgbanking;

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

public class DbUtil {
    String driver = "com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://localhost:3306/study?characterEncoding=utf8";
    Connection conn = null;
    ResultSet res = null;
    public void DataBase(){
        try{
            Class.forName(driver);
            conn = DriverManager.getConnection(url, "root", "HUI1212");

        }catch(ClassNotFoundException e){
            System.out.println("驱动加载失败");
            e.printStackTrace();
        }catch(SQLException e){
            System.out.println("无法连接数据库");
            e.printStackTrace();
        }
    }
    /*
     * 查询
     */
    public ResultSet Search(String sql, String str[]){
        DataBase();
        try{
            PreparedStatement pst = conn.prepareStatement(sql);
            if(str != null){
                for(int i = 1; i < str.length; i++ ){
                    pst.setString(i+1, str[i]);
                }
            }
            res = pst.executeQuery();
        }catch(Exception e){
            e.printStackTrace();
        }
        return res;
    }
    /*
     * 增删查
     */
    public int State(String sql, String str[]){
        int a = 0;
        DataBase();
        try {
            PreparedStatement pst = conn.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;
    }
}

创建一个service类

用于查询数据库和本地的电子表格里面的数据

package grgbanking.services;

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

import grgbanking.DbUtil;
import grgbanking.entity.StuEntity;
import jxl.Sheet;
import jxl.Workbook;

/**
 * 
 * @author cjyun
 * @category 读取Excel表中所有的数据、操作数据（查询、更新）
 */
public class StuService {
    public static List<StuEntity> list = new ArrayList<StuEntity>();

    /**
     * 查询表中平数据
     */
    public static List<StuEntity> getAllDb(){

        try {
            DbUtil db = new DbUtil();
            String sql = "select * from stu";
            ResultSet res = db.Search(sql, null);
            while (res.next()){
                int id = res.getInt("id");
                String name = res.getString("name");
                String sex = res.getString("sex");
                int num = res.getInt("num");
                list.add(new StuEntity(id,name,sex,num));
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return list;
    }
    /**
     * 查询电子表中的数据
     */
    public static List<StuEntity> getAllByExcel(String file){

        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();
                    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是否存在
     */
    public static boolean isExist(int id){
        try {
            DbUtil db = new DbUtil();
            String sql = "select * from stu where id ="+id;
            System.out.println(sql);
            ResultSet res = db.Search(sql, new String[]{id+""});
            if(res.next()){
                return true;
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return false;
    }
    public static void main(String[] args) {
        /*List<StuEntity> all = getAllDb();
        for(StuEntity stu:all){
            System.out.println(stu.toString());
        }*/
        System.out.println(isExist(5));
    }
}

创建控制层

1. 从数据库导入Excel
   ## 新建SQLToExcel类 ##

package grgbanking.controller;

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

import grgbanking.entity.StuEntity;
import grgbanking.services.StuService;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class SQLToExcel {
    public static void main(String[] args) {
        try {
            WritableWorkbook wwb = null;
            String fileName = "D://book.xls";
            File file = new File(fileName);
            if(!file.exists()){
                file.createNewFile();
            }
            wwb = Workbook.createWorkbook(file);
            WritableSheet ws = wwb.createSheet("Test Shee 1", 0);
            List<StuEntity> list = StuService.getAllDb();
            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(0,i+1,list.get(i).getName());
                Label labelSex_i= new Label(0,i+1,list.get(i).getSex());
                Label labelNum_i= new Label(0,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: handle exception
            e.printStackTrace();
        }
    }
}

1. 从Excel导入数据库
   ## 新建ExcelToSQL类 ##

package grgbanking.controller;

import java.util.List;

import grgbanking.DbUtil;
import grgbanking.entity.StuEntity;
import grgbanking.services.StuService;

public class ExcelToSQL {
    public static void main(String[] args) {
        //得到表格中所有的数据
        List<StuEntity> listExcel=StuService.getAllByExcel("d://book.xls");
        /*//得到数据库表中所有的数据
        List<StuEntity> listDb=StuService.getAllByDb();*/

        DbUtil db=new DbUtil();

        for (StuEntity stuEntity : listExcel) {
            int id=stuEntity.getId();
           System.out.println(stuEntity.getName());
            if (!StuService.isExist(id)) {
                //不存在就添加
                String sql="insert into stu (id,name,sex,num) values(?,?,?,?)";
                String[] str=new String[]{id+"",stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+""};
                db.State(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.State(sql, str);
            }
        }
    }}

总结

不管是从Excel导入到数据库中，还是从数据库中导入到Excel。首先都要获取到表格的列和行的数据，然后再同过java的一些方法将之写入。不同的是，Excel中必须按照顺序进行写入和输出。