kettle User Defined Java Class 文件对比

最终版

  1 import com.baozun.util.ExcelReaderWrite ;
  2 import org.apache.poi.ss.usermodel.*;
  3 import java.io.*;
  4 import java.util.ArrayList;
  5 import java.util.HashMap;
  6 import java.util.List;
  7 import java.util.Map;
  8 import java.util.Date;  
  9 import java.text.SimpleDateFormat;  
 10 import org.apache.poi.ss.usermodel.Cell;
 11 import org.apache.poi.ss.usermodel.DataFormatter;
 12 import org.apache.poi.ss.usermodel.DateUtil;
 13 import org.apache.poi.ss.util.NumberToTextConverter;
 14 import org.apache.poi.xssf.usermodel.XSSFCell;
 15 import org.apache.poi.xssf.usermodel.XSSFRow;
 16 import org.apache.poi.xssf.usermodel.XSSFSheet;
 17 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 18 import org.apache.poi.xssf.streaming.SXSSFWorkbook;
 19 
 20 public void writeExcel(List SheetData,File file){
 21 FileOutputStream out = null;
 22 SXSSFWorkbook wk = new SXSSFWorkbook();
 23 Sheet sheet1 = wk.createSheet("sheet1");
 24 int rowNum;
 25 int colNum=0;
 26 String line="";
 27 for (int i = 0; i < SheetData.size(); i++) {
 28         line= (String)SheetData.get(i);
 29         rowNum=i+1;
 30         if (i == 0) {
 31             sheet1.createRow(0).createCell(0).setCellValue("contents");
 32         }
 33         if (null == sheet1.getRow(rowNum)) {
 34             Row row = sheet1.createRow(rowNum);
 35             if (null == row.getCell(colNum)) {
 36                 row.createCell(colNum).setCellValue(line);
 37             } else {
 38                 row.getCell(colNum).setCellValue(line);
 39             }
 40         } else {
 41             sheet1.getRow(rowNum).createCell(colNum).setCellValue(line);
 42         }
 43     }
 44 try{
 45     if (!file.exists()) {
 46         file.createNewFile();
 47     }
 48     out = new FileOutputStream(file);
 49     wk.write(out);
 50     out.flush();
 51     out.close();
 52 }catch(Exception e){
 53     e.printStackTrace();
 54 }
 55 }
 56 
 57   public String getCellValue(XSSFCell cell) {
 58         String cellValue = "";
 59         DataFormatter formatter = new DataFormatter();
 60         if (cell != null) {
 61             switch (cell.getCellType()) {
 62                 case Cell.CELL_TYPE_NUMERIC:
 63                     if (DateUtil.isCellDateFormatted(cell)) {
 64                         cellValue = formatter.formatCellValue(cell);
 65                     } else {
 66                         double value = cell.getNumericCellValue();
 67                         int intValue = (int) value;
 68                         cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
 69                     }
 70                     break;
 71                 case Cell.CELL_TYPE_STRING:
 72                     cellValue = cell.getStringCellValue();
 73                     break;
 74                 case Cell.CELL_TYPE_BOOLEAN:
 75                     cellValue = String.valueOf(cell.getBooleanCellValue());
 76                     break;
 77                 case Cell.CELL_TYPE_FORMULA:
 78                     cellValue = String.valueOf(cell.getCellFormula());
 79                     break;
 80                 case Cell.CELL_TYPE_BLANK:
 81                     cellValue = "";
 82                     break;
 83                 case Cell.CELL_TYPE_ERROR:
 84                     cellValue = "";
 85                     break;
 86                 default:
 87                     cellValue = cell.toString().trim();
 88                     break;
 89             }
 90         }
 91         return cellValue.trim();
 92     }
 93     
 94 public List readExcle(File file,String sheetName){
 95         try {
 96 
 97             XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
 98             XSSFSheet sheet = xssfWorkbook.getSheet(sheetName);
 99             List sheetData = new ArrayList();
100             for (int i = 0; i <sheet.getLastRowNum() ; i++) {
101                 XSSFRow row = sheet.getRow(i);
102                 String line ="";
103                 for (int j = 0; j <row.getPhysicalNumberOfCells() ; j++) {
104                     String stringCellValue = getCellValue(row.getCell(j));
105                     line+=stringCellValue;
106                 }
107                 sheetData.add(line+'
');
108             }
109 
110             return sheetData ;
111 
112         } catch (Exception e) {
113             e.printStackTrace();
114         }
115         return null ;
116     }    
117 
118 public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException {
119 
120 Object[] r = getRow();
121   if (r != null){
122 r = createOutputRow(r, data.outputRowMeta.size());        
123   }else {
124     setOutputDone();
125     return false;
126   }
127 
128   if (first) {
129     first = false;
130   }
131 
132 
133 // 说去输入参数;
134 String apiName=get(Fields.In, "apiName").getString(r);
135 String file1_path=get(Fields.In, "file1_path").getString(r);
136 String file2_path=get(Fields.In, "file2_path").getString(r);
137 String file1Name=get(Fields.In, "file1").getString(r);
138 String file2Name=get(Fields.In, "file2").getString(r);    
139 
140 //-----------------------------------------------------------------------------------------------------------------------
141 String sheetName="sheet1";
142 String    file1=file1_path+file1Name ;
143 List sheetData1=readExcle(new File(file1),sheetName);
144 
145 //数据写入文件用于后续排序 ;
146 File file = new File("D:\temp0066.xlsx");
147 writeExcel(sheetData1,file);
148 //-----------------------------------------------------------------------------------------------------------------------
149 String rs =null;
150 String differRowNo =null;
151 String errMsg =null;
152 String currentDate =null;
153     
154 //get(Fields.Out, "flag").setValue(r, rs);
155 //get(Fields.Out, "differRowNo").setValue(r, differRowNo);
156 //get(Fields.Out, "currentDate").setValue(r, currentDate);
157 //get(Fields.Out, "errMsg").setValue(r, errMsg);
158 //get(Fields.Out, "lines").setValue(r, lines);
159 
160   // Send the row on to the next step.
161   putRow(data.outputRowMeta, r);
162 
163   return true;
164 }

  1 import com.baozun.util.ExcelReaderWrite ;
  2 import org.apache.poi.hssf.usermodel.HSSFDateUtil;
  3 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  4 import org.apache.poi.ss.usermodel.*;
  5 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  6 import java.io.*;
  7 import java.util.ArrayList;
  8 import java.util.HashMap;
  9 import java.util.List;
 10 import java.util.Map;
 11 import java.util.Date;  
 12 import java.text.SimpleDateFormat;  
 13 import com.opencsv.CSVReader;
 14 
 15 
 16 
 17 public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException {
 18   if (first) {
 19     first = false;
 20   }
 21 
 22 Object[] r = getRow();
 23   if (r == null) {
 24     setOutputDone();
 25     return false;
 26   }
 27 
 28   // It is always safest to call createOutputRow() to ensure that your output row's Object[] is large
 29   // enough to handle any new fields you are creating in this step.
 30 r = createOutputRow(r, data.outputRowMeta.size());
 31 
 32 String apiName=get(Fields.In, "apiName").getString(r);
 33 String file1_path=get(Fields.In, "file1_path").getString(r);
 34 String file2_path=get(Fields.In, "file2_path").getString(r);
 35 String file1Name=get(Fields.In, "file1").getString(r);
 36 String file2Name=get(Fields.In, "file2").getString(r);    
 37 /*
 38 logBasic("input_apiName="+apiName);
 39 logBasic("input_file1_path="+file1_path);
 40 logBasic("input_file2_path="+file2_path);
 41 logBasic("input_file1="+file1);
 42 logBasic("input_file2="+file2);
 43 */
 44 //-----------------------------------------------------------------------------------------------------------------------
 45 Boolean rs=null ;
 46 String    differRowNo=null ;
 47 CSVReader reader1 = null;
 48 CSVReader reader2 = null;
 49 String sheetName = null;
 50 String errMsg = null;
 51 List sheetData1=null;
 52 List sheetData2=null;
 53 
 54 sheetName="sheet1";
 55 String    file1=file1_path+file1Name ;
 56 String    file2=file2_path+file2Name ;
 57 
 58 String suffix1=file1.substring(file1.lastIndexOf('.'));
 59 String suffix2=file2.substring(file2.lastIndexOf('.'));
 60 String prodRow=null;
 61 String testRow=null;
 62 
 63 //logBasic("-----------------------------------------"+suffix1.equals(suffix2)+"-----------------------------------------------");
 64   if (!suffix1.equals(suffix2)) {
 65             errMsg="两个文件后缀不一致! "+ suffix1+" <> "+suffix2;
 66 
 67 
 68         } else if (suffix1.equals(".xlsx")) {
 69              sheetData1 = new ExcelReaderWrite(file1, sheetName).getSheetData();
 70              sheetData2 = new ExcelReaderWrite(file2, sheetName).getSheetData();
 71             if (sheetData1.size() == sheetData1.size()) {
 72                 for (int i = 0; i < sheetData1.size(); i++) {
 73                      prodRow = sheetData1.get(i).toString();
 74                      testRow = sheetData2.get(i).toString();
 75                     rs=prodRow.equals(testRow);
 76                     if (!rs) {
 77                         differRowNo ="第 "+ String.valueOf(i + 1)+" 行";
 78                         errMsg="文件内容不一致";
 79                         break;
 80                     }
 81                 }
 82             } else {
 83                errMsg="文件行数不相等 ！ "+sheetData1.size()+" <> "+sheetData1.size();
 84             }
 85 
 86         } else if (suffix1.equals(".csv")) {
 87 //logBasic("-----------------------------"+file1+"---------"+file2+"-----------------------------");
 88 
 89 
 90             try {
 91 
 92                 List file1_AllData =null;
 93                  List file2_AllData =null;
 94                 reader1 = new CSVReader(new FileReader(file1), '`');
 95                 reader2 = new CSVReader(new FileReader(file2), '`');
 96                  file1_AllData = reader1.readAll();
 97                  file2_AllData = reader2.readAll();
 98                 if (file1_AllData.size() == file2_AllData.size()) {
 99                     for (int i = 0; i < file1_AllData.size(); i++) {
100                         String[] file1_line = (String[]) file1_AllData.get(i);
101                         String[] file2_line = (String[]) file2_AllData.get(i);
102                         prodRow=file1_line[0];
103                         testRow=file2_line[0];
104                         rs=prodRow.equals(testRow);
105                         if (!rs) {
106                             differRowNo ="第 "+ String.valueOf(i + 1)+" 行";
107                             errMsg="文件内容不一致";
108                             break;
109                         }
110                     }
111                 } else {
112                errMsg="文件行数不相等 ！ "+file1_AllData.size()+" <> "+file2_AllData.size();
113                 }
114 
115             } catch (Exception e) {
116                 e.printStackTrace();
117             }
118 
119         }
120     
121         
122 
123 Date currentTime = new Date();  
124 SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");  
125 String currentDate = formatter.format(currentTime);
126 logBasic("currentDate="+currentDate); 
127 //-----------------------------------------------------------------------------------------------------------------------
128 
129     
130 get(Fields.Out, "flag").setValue(r, rs);
131 get(Fields.Out, "differRowNo").setValue(r, differRowNo);
132 get(Fields.Out, "currentDate").setValue(r, currentDate);
133 get(Fields.Out, "errMsg").setValue(r, errMsg);
134 get(Fields.Out, "prodRow").setValue(r, file1+":"+prodRow);
135 get(Fields.Out, "testRow").setValue(r, file2+":"+testRow);
136 
137 
138   // Send the row on to the next step.
139   putRow(data.outputRowMeta, r);
140 
141   return true;
142 }