Java读取Excel并与SqlServer库中的数据比较

先说说需求。在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.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