java操作EXCEL整理［原创］

package cn.job;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class TyUtils {
    public static void main(String[] args) {
        try {
            diffExcels("E:/date.xls","E:/dates.xls");
        } catch (Exception e) {
            // TODO Auto-generated catch block

            e.printStackTrace();
        }
    }
    /**
     * 判断两份EXCEL表格的内容差异
     * @param 目标EXCEL    (即最新的EXCEL文档)
     * @param 需要对比的EXCEL
     * */
    private static void diffExcels(String muexcel,String oldexcel) throws Exception {
        Workbook rwb = null;
        try {
            /**
             * 将目标的EXCEL导入到一个ArrayList内
             * 存储的格式为new ArrayList<Array()>
             * */
            InputStream is = new FileInputStream(muexcel);
            rwb = Workbook.getWorkbook(is);
            //判断当前的工作簿中有多少个工作表

            String[] sheets = rwb.getSheetNames();
            //遍历全部的工作表

            int sheetsLength = sheets.length;
            ArrayList<ArrayList<String>> arrayLists = new ArrayList<ArrayList<String>>();
            ArrayList<String> results = new ArrayList<String>();
            ArrayList<String> mydiff = new ArrayList<String>();
            for (int i = 0; i < sheetsLength; i++) {
                //处理第I个数据表的数据

                ArrayList<String> sheetArray = new ArrayList<String>();
                Sheet sheet = rwb.getSheet(i);
                int rows = sheet.getRows();
                for (int j = 1; j < rows; j++) {
                    Cell c00 = sheet.getCell(0, j);//第一个数据表里面的第一行第一列的元素值！

                    String strc00 = c00.getContents();
                    if (strc00.equals("")) {
                        continue;
                    }
                    sheetArray.add(strc00.trim());
                }
                arrayLists.add(sheetArray);
            }
            //以下代码将实现将要对比的EXCEL解析过来

            is = new FileInputStream(oldexcel);
            rwb = Workbook.getWorkbook(is);
            //判断当前的工作簿中有多少个工作表

            sheets = rwb.getSheetNames();
            //遍历全部的工作表

            sheetsLength = sheets.length;
            ArrayList<ArrayList<String>> arrayList = new ArrayList<ArrayList<String>>();
            for (int i = 0; i < sheetsLength; i++) {
                //处理第I个数据表的数据

                ArrayList<String> sheetArray = new ArrayList<String>();
                Sheet sheet = rwb.getSheet(i);
                int rows = sheet.getRows();
                for (int j = 1; j < rows; j++) {
                    Cell c00 = sheet.getCell(0, j);//第一个数据表里面的第一行第一列的元素值！

                    String strc00 = c00.getContents();
                    if (strc00.equals("")) {
                        continue;
                    }
                    sheetArray.add(strc00.trim());
                }
                arrayList.add(sheetArray);
            }
            //对比

            for (int i = 0; i < arrayLists.size(); i++) {
                //遍历当前的ARRAYLIST里面的元素

                ArrayList<String> tmp = arrayLists.get(i);
                int length = tmp.size();
                for (int j = 0; j < length; j++) {
                    String string = tmp.get(j);                    
                    if (!arrayList.get(i).contains(string.trim())) {
                        System.out.println("=========");
                        try {
                            results.add(string);
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        
                    }
                }
                
                tmp = arrayList.get(i);
                length = tmp.size();
                for (int j = 0; j < length; j++) {
                    String string = tmp.get(j);                    
                    if (!arrayLists.get(i).contains(string.trim())) {
                        mydiff.add(string);
                    }
                }
                
            }
            System.out.println("****************");
            System.out.println(results);
            System.out.println(mydiff);
        } catch (Exception e) {
            throw new Exception();
        } finally {
            rwb.close();
        }
    }
}