转 java实现读取excel

心已赠人 2022-07-16 09:25 195阅读 0赞

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

实现的功能：

![这里写图片描述][SouthEast]

连接数据库的工具类:

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

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

[http_www.cnblogs.com_zyw-205520_p_3762954.html]: http://www.cnblogs.com/zyw-205520/p/3762954.html
[SouthEast]: /images/20220716/1bd44035f3da4f588bfdba031babf3c3.png