先说说需求。在SQL server数据库中的表里存在一些数据，现在整理的Excel文档中也存在一些数据，现在需要通过根据比较某个字段值（唯一）来判断出，在库中有但excel中没有的数据。
大概的思路就是先循环遍历所有的excel文件
import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Properties; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.ss.util.CellReference; public class ReadExcel { private static Integer counts = 0; private static Connection conn = null; private static PreparedStatement pre = null; private static Connection conn2 = null; private static PreparedStatement pre2 = null; private static List<Map<String, Object>> list = new ArrayList<Map<String,Object>>(); private static List<String> listDiff = new ArrayList<String>(); public static void main(String[] args){ Properties prop = read(); String path = prop.getProperty("file.path"); getDirectory(path); sqlServiceData(list); closeConn(conn2, pre2); writeToTxt(listDiff); } public static void sqlServiceData(List<Map<String, Object>> list){ Properties prop = read(); String driver = prop.getProperty("sqlserver.driver"); String url = prop.getProperty("sqlserver.url"); String user = prop.getProperty("sqlserver.user"); String psd = prop.getProperty("sqlserver.password"); conn2 = getConn(driver, url, user, psd); String sql = "select TheName from UTB_GBNT_NYZH_INFO union select TheName from UTB_GBNT_PROJ_INFO union select TheName from UTB_KZST_PROJ_INFO"; //select COLUMN_NAME from information_schema.COLUMNS where table_name = 'UTB_KZST_PROJ_INFO'; try { pre2 = conn2.prepareStatement(sql); ResultSet rs = pre2.executeQuery(); while(rs.next()){ int flag = 0; for (int i = 0; i < list.size(); i++) { if(list.get(i).get("XMMC").equals(rs.getString(1))){ flag = 1; break; } else continue; } if(flag == 0){ listDiff.add(rs.getString(1)); } //System.out.println(rs.getString(1)); } //查询表中字段名称 /*ResultSetMetaData data = rs.getMetaData(); for (int i = 1; i < data.getColumnCount(); i++) { String name = data.getColumnName(i); System.out.println(name); }*/ } catch (SQLException e) { e.printStackTrace(); } } public static void writeToTxt(List<String> listDiff){ Properties prop = read(); String path = prop.getProperty("file.savePath"); File file = new File(path); try { if(!file.exists()){ file.createNewFile(); } FileOutputStream fos = new FileOutputStream(file); for (int i = 0; i < listDiff.size(); i++) { fos.write(listDiff.get(i).getBytes("utf-8")); fos.write(" ".getBytes("utf-8")); } fos.flush(); fos.close(); System.out.println("共有"+listDiff.size()+"条数据在Excel文件中没有找到！"); } catch (Exception e) { e.printStackTrace(); } } public static void readExcel(String path){ InputStream inputStream; try { inputStream = new FileInputStream(path); Workbook workbook = WorkbookFactory.create(inputStream); Sheet sheet = workbook.getSheetAt(0); DataFormatter formatter = new DataFormatter(); for (Row row : sheet) { Map<String, Object> map = new HashMap<String, Object>(); for (Cell cell : row) { CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex()); if(cellRef.formatAsString().contains("B") && !formatter.formatCellValue(cell).equals("") && !formatter.formatCellValue(cell).contains("地级市")){ //单元格名称 /*System.out.print(cellRef.formatAsString()); 获取单元格的文本值*/ //System.out.println(formatter.formatCellValue(cell)); map.put("DJ",formatter.formatCellValue(cell)); } if(cellRef.formatAsString().contains("C") && !formatter.formatCellValue(cell).equals("") && !formatter.formatCellValue(cell).contains("市")){ map.put("XJ",formatter.formatCellValue(cell)); //System.out.println(formatter.formatCellValue(cell)); } if(cellRef.formatAsString().contains("D") && formatter.formatCellValue(cell).contains("年")){ map.put("XMMC",formatter.formatCellValue(cell)); //System.out.println(formatter.formatCellValue(cell)); } if(cellRef.formatAsString().contains("E") && !formatter.formatCellValue(cell).equals("") && !formatter.formatCellValue(cell).contains("编号")){ map.put("XMBH",formatter.formatCellValue(cell)); // System.out.println(formatter.formatCellValue(cell)); } if(cellRef.formatAsString().contains("F") && !formatter.formatCellValue(cell).equals("") && !formatter.formatCellValue(cell).contains("年度")){ map.put("NF",formatter.formatCellValue(cell)); //System.out.println(formatter.formatCellValue(cell)); } else{ continue; } } if(!map.isEmpty()){ list.add(map); } } } catch (Exception e) { e.printStackTrace(); } } //1,首先需要找到目录为“项目账单”的文件夹 public static void getDirectory(String strPath) { File dir = new File(strPath); File[] files = dir.listFiles(); // 该文件目录下文件全部放入数组 //String fileDirectory = ""; if (files != null ) { for (int i = 0; i < files.length; i++) { String fileName = files[i].getName(); if (files[i].isDirectory()) { // 判断是否是对应的目录 if(fileName.contains("项目账单")){ getSpecific(files[i].getAbsolutePath()); } else getDirectory(files[i].getAbsolutePath()); }else{ continue; } } } } //2,遍历具体的账单 .xls文件 public static void getSpecific(String strPath) { File dir = new File(strPath); File[] files = dir.listFiles(); // 遍历该文件目录下所有xls文件 //String fileDirectory = ""; if (files != null ) { for (int i = 0; i < files.length; i++) { String fileName = files[i].getName(); if (files[i].isDirectory()) { // 如果是文件夹继续遍历 getSpecific(files[i].getAbsolutePath()); }else if(fileName.endsWith(".xls")){ readExcel(files[i].getAbsolutePath()); }else{ continue; } } } } //获取连接 public static Connection getConn(String driver,String url,String user,String psd){ try { Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } try { conn = DriverManager.getConnection(url,user,psd); } catch (Exception e1) { e1.printStackTrace(); } return conn; } //关闭所有连接 public static void closeConn (Connection conn,PreparedStatement pre){ try { if(pre != null){ pre.close(); pre = null; } if(conn != null){ conn.close(); conn = null; } } catch (Exception e) { e.printStackTrace(); } } //获取配置文件访问对象 public static Properties read() { Properties pro = new Properties(); String path = Class.class.getClass().getResource("/").getPath(); File file = new File(path+"\application.properties"); try { FileInputStream in = new FileInputStream(file); pro.load(in); } catch (Exception e) { System.out.println(e); } return pro; } public static void getType(Cell cell){ //获取值并自己格式化 switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING:// 字符串型 System.out.println(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC:// 数值型 if (DateUtil.isCellDateFormatted(cell)) { // 如果是date类型则 ，获取该cell的date值 System.out.println(cell.getDateCellValue()); } else {// 纯数字 System.out.println(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN:// 布尔 System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA:// 公式型 System.out.println(cell.getCellFormula()); break; case Cell.CELL_TYPE_BLANK:// 空值 System.out.println(); break; case Cell.CELL_TYPE_ERROR: // 故障 System.out.println(); break; default: System.out.println(); } } }
然后使用poi读取excel，然后取出所有的需要数据，放到list中，然后通过JDBC取出数据库中的数据进行循环比较，最后将结果通过IO写入到文档中。
程序需要以下jar包支持：
1） sqljdbc42.jar
2） poi-3.6-20091214.jar
3） poi-3.8-20120326.jar
4） poi-ooxml-3.8-20120326.jar
5）poi-ooxml-schemas-3.8-20120326.jar
最后注意，为了保证程序的准确性，需保证excel文件中的数据格式保持一致。且在Java文件的同级目录下存在着application.properties文件。
配置文件中的信息如下：
##file.path->指定需要遍历的文件夹路径
file.path=C:\Users\**\Desktop
file.path=C:\Users\**\Desktop
##查找结果存放的路径
file.savePath=C:\**\Excel.txt
file.savePath=C:\**\Excel.txt
##SqlServer数据库连接信息
sqlserver.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
sqlserver.url=jdbc:sqlserver://localhost:1433;DatabaseName=**
sqlserver.user=sa
sqlserver.password=123456