比较在excel中存在，并且在数据库中存在的表_excel中的数据在数据库中哪些存在-优快云博客

本文介绍了一个用于比较Excel表格与数据库中表名的Java工具。该工具读取指定的Excel文件，并从本地MySQL数据库获取表名列表，然后找出两者之间的共同表名。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

package com.zry.excel.tools;

import java.util.Comparator;
import java.util.HashSet;
import java.util.Iterator;
import java.util.TreeSet;

public class MeasureDemo {

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static void main(String[] args) {
		HashSet excelTables = ReadWriteExcel.getExcelTables();
		HashSet databaseTables = new ReadDataBase().getResultSet("show tables");
		TreeSet existSet = new TreeSet(new Comparator(){
			public int compare(Object arg0, Object arg1) {
				String s1 = (String)arg0;
				String s2 = (String)arg1;
				return s1.compareTo(s2);
			}
		});
		Iterator it = excelTables.iterator();
		int count=0;
		while(it.hasNext()){
			String tablename = (String)it.next();
			if(databaseTables.contains(tablename)){
				existSet.add(tablename);
				count++;
			}
		}
		it = existSet.iterator();
		while(it.hasNext()){
			System.out.println(it.next());
		}
		System.out.println("找到了"+count);
	}

}

package com.zry.excel.tools;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;

public class ReadDataBase {
	Connection conn = null;
	public ReadDataBase() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/gap", "root", "root");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public HashSet getResultSet(String sql) {
		HashSet set = new HashSet();
		Statement stmt;
		int count=0;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				set.add(rs.getString(1).toUpperCase());
				count++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("database中有"+count);
		return set;
	}
}

package com.zry.excel.tools;  
  
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashSet;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
  
public class ReadWriteExcel {  
    @SuppressWarnings({ "unchecked", "rawtypes" })
	public static HashSet getExcelTables(){  
        Workbook  workbook = null;  
        try {  
            InputStream is=new FileInputStream("C:/tables.xls");  
            workbook= Workbook.getWorkbook(is);  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (BiffException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
        HashSet set = new HashSet();
        int count=0;
        for(int i=0;i<1;i++){  
            Sheet sheet = workbook.getSheet(i);  
            if(sheet != null){  
                int rows=sheet.getRows();  
                for(int j=0;j<rows;j++){  
                    for (int k = 1; k < 2; k++) {  
                         Cell cell = sheet.getCell(k,j);  
                         if(cell != null){  
                             Object cellVal = cell.getContents();  
                             if(cellVal != null && !cellVal.equals("")){  
                            	 set.add(cell.getContents().toUpperCase());
                                count++;
                             }  
                         }  
                    }  
                }  
            }  
            System.out.println("excel中有"+count);
        }  
        return set;
    }  
}

源代码工程参见

http://download.youkuaiyun.com/detail/hongqishi/4229583