先说说需求。在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("\r\n".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
sqlserver.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
sqlserver.url=jdbc:sqlserver://localhost:1433;DatabaseName=**
sqlserver.user=sa
sqlserver.password=123456