最终版
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 }