使用java将excel中的数据导入到MySQL数据库

Scroll Down

使用java将excel中的数据导入到MySQL数据库

1.创建一个实体类,此类中的属性名字和mysql数据库表中的数据的字段一一对应

public class Files {
	private int id;
	private String username;
	private String path;
	private String filename;
	private String fileattribute;
	private String filelen;
	private String directory;
	
	public Files() {
		super();
	}
	
	public Files(int id, String username, String path, String filename, String fileattribute, String filelen,String directory) {
		super();
		this.id = id;
		this.username = username;
		this.path = path;
		this.filename = filename;
		this.fileattribute = fileattribute;
		this.filelen = filelen;
		this.directory = directory;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPath() {
		return path;
	}
	public void setPath(String path) {
		this.path = path;
	}
	public String getFilename() {
		return filename;
	}
	public void setFilename(String filename) {
		this.filename = filename;
	}
	public String getFileattribute() {
		return fileattribute;
	}
	public void setFileattribute(String fileattribute) {
		this.fileattribute = fileattribute;
	}
	public String getFilelen() {
		return filelen;
	}
	public void setFilelen(String filelen) {
		this.filelen = filelen;
	}
	public String getDirectory() {
		return directory;
	}
	public void setDirectory(String directory) {
		this.directory = directory;
	}

	@Override
	public String toString() {
		return "Files [id=" + id + ", username=" + username + ", path=" + path + ", filename=" + filename
				+ ", fileattribute=" + fileattribute + ", filelen=" + filelen + ", directory=" + directory + "]";
	}
	
}

2.创建一个jdbc程序,用于插入数据

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

public class ToJDBC {
	Connection conn = null;
	PreparedStatement s = null;
	ResultSet rs = null;
	String sql = null;
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static Connection getConnet() throws SQLException {
		String url = "jdbc:mysql://127.0.0.1:3306/hadoop?characterEncoding=UTF-8&useSSL=false";
		String user = "root";
		String ps = "123456";
		Connection conn = DriverManager.getConnection(url, user, ps);
		return conn;
	}

	public void free(PreparedStatement s2, ResultSet rs, Connection conn) {
		if (s2 != null) {
			try {
				s2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void free(Statement s, Connection c) {
		// 数据库的连接时有限资源,相关操作结束后,养成关闭数据库的好习惯
		// 先关闭Statement
		if (s != null)
			try {
				s.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		// 后关闭Connection
		if (c != null)
			try {
				c.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}

	public void InsetFiles(List<Files> list) throws SQLException {
		conn = getConnet();
		// 和 Statement一样,PreparedStatement也是用来执行sql语句的
		for (int i = 0; i < list.size(); i++) {
			sql = "insert into files values('" + list.get(i).getId() + "','" + list.get(i).getUsername() + "','"
					+ list.get(i).getPath() + "','" + list.get(i).getFilename() + "','" + list.get(i).getFileattribute()
					+ "','" + list.get(i).getFilelen() + "','" + list.get(i).getDirectory() + "');";
			s = conn.prepareStatement(sql);
			s.execute();
		}
		free(s, conn);
	}
}

3.开始插入

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class LookExcel {
    /**
     * 查询指定目录中Excel表格中所有数据
     * @param file为文件完整路径
     * @return
     */
    public static List<Files> getAllByExcel(String file){
        List<Files> list = new ArrayList<Files>();
            try {
                Workbook rwb = Workbook.getWorkbook(new File(file));
                Sheet rs = rwb.getSheet("hdfs文件"); //或者rwb.getSheet(0)
                int clos=rs.getColumns();//得到所有的列
                int rows=rs.getRows();//得到所有的行
                System.out.println("clos:"+clos+" rows:"+rows);
                for(int i=1;i<rows;i++){
                    int j=0;
                    String Id = rs.getCell(j++, i).getContents();
                    String username =rs.getCell(j++, i).getContents();
                    String path = rs.getCell(j++, i).getContents();
                    String filename=rs.getCell(j++, i).getContents();
                    String fileattribute=rs.getCell(j++, i).getContents();
                    String filelen=rs.getCell(j++, i).getContents();
                    String directory=rs.getCell(j++, i).getContents();
                    Files files=new Files(Integer.parseInt(Id),username,path,filename,fileattribute,filelen,directory);
                    System.out.println(files);
                    list.add(files);                    
                }
            } catch (BiffException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
            return list;
    }
    public static void main(String[] args) throws SQLException {
        List<Files> list=LookExcel.getAllByExcel("D:\\文件表.xls");
        new ToJDBC().InsetFiles(list);
    }
}

小提示:上面代码的总体思想就是,1.读取excel中的数据到一个集合中,excel每一个行数据当成一个对象存储到集合中,然后使用jdbc将这个集合中的数据插入到MySQL中即可。

注意:excel中的数据的格式一个定要和MySQL表字段对应