//输入参数为文件输入流
public static Map<String, List<String>> excelColumn2maplist(InputStream is)
{
//存储某一列数据
Map<String, List<String>> maplist = new HashMap<>();
//存储表头字段
List<String> tableHeadfields = new ArrayList<>();
XSSFWorkbook workBook = null;
try
{
workBook = new XSSFWorkbook(is);
}
catch (IOException e)
{
logger.error("读取工作簿出错" + e.getMessage());
}
Sheet sheet = workBook.getSheetAt(0);
Row tableHeadRow = sheet.getRow(0);
for (int i = 0; i < tableHeadRow.getLastCellNum(); i++)
{
tableHeadfields.add(getValue(tableHeadRow, i));
}
//所有表头字段所在列的索引
int severNameIndex = tableHeadfields.indexOf("severName");
int fileNameIndex = tableHeadfields.indexOf("fileName");
int keyIndex = tableHeadfields.indexOf("key");
int valueIndex = tableHeadfields.indexOf("value");
int versionIndex = tableHeadfields.indexOf("version");
int sysEnvRelatedIndex = tableHeadfields.indexOf("sysEnvRelated");
int markIndex = tableHeadfields.indexOf("mark");
int typeIndex = tableHeadfields.indexOf("type");
int validate_resultIndex = tableHeadfields.indexOf("validate_result");
// int tableHeadfieldsSize = tableHeadfields.size();
//将数据列放进map，规则是同一行，后一个字段包括前一个字段作为前缀，以分割符Constants.FieldSeparator（<SpS>） 进行分隔
List<String> severNameList = new ArrayList<>();
List<String> fileNameList = new ArrayList<>();
List<String> keyList = new ArrayList<>();
List<String> valueList = new ArrayList<>();
List<String> versionList = new ArrayList<>();
List<String> sysEnvRelatedList = new ArrayList<>();
List<String> markList = new ArrayList<>();
List<String> typeList = new ArrayList<>();
List<String> validate_resultList = new ArrayList<>();
StringBuilder sb = new StringBuilder();
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++)
{
Row row = sheet.getRow(rowNum);
if (row == null)
{
continue;
}
sb.append(getValue(row, severNameIndex)).append(Constants.FieldSeparator);
severNameList.add(sb.toString());
sb.append(getValue(row, fileNameIndex)).append(Constants.FieldSeparator);
fileNameList.add(sb.toString());
sb.append(getValue(row, keyIndex)).append(Constants.FieldSeparator);
keyList.add(sb.toString());
sb.append(getValue(row, valueIndex)).append(Constants.FieldSeparator);
valueList.add(sb.toString());
sb.append(getValue(row, versionIndex)).append(Constants.FieldSeparator);
versionList.add(sb.toString());
sb.append(getValue(row, sysEnvRelatedIndex)).append(Constants.FieldSeparator);
sysEnvRelatedList.add(sb.toString());
sb.append(getValue(row, markIndex)).append(Constants.FieldSeparator);
markList.add(sb.toString());
sb.append(getValue(row, typeIndex)).append(Constants.FieldSeparator);
typeList.add(sb.toString());
sb.append(getValue(row, validate_resultIndex)).append(Constants.FieldSeparator);
validate_resultList.add(sb.toString());
sb.delete(0, sb.length());
}
maplist.put("severName", severNameList);
maplist.put("fileName", fileNameList);
maplist.put("key", keyList);
maplist.put("value", valueList);
maplist.put("version", versionList);
maplist.put("sysEnvRelated", sysEnvRelatedList);
maplist.put("mark", markList);
maplist.put("type", typeList);
maplist.put("validate_result", validate_resultList);
return maplist;
}
private static String getValue(Row row, int cellNum)
{
if (row.getCell(cellNum) == null)
{
return "";
}
if (row.getCell(cellNum).getCellType() == row.getCell(cellNum).CELL_TYPE_BOOLEAN)
{
return String.valueOf(row.getCell(cellNum).getBooleanCellValue()).trim();
}
else if (row.getCell(cellNum).getCellType() == row.getCell(cellNum).CELL_TYPE_NUMERIC)
{
return String.valueOf(row.getCell(cellNum).getNumericCellValue()).trim();
}
else
{
return String.valueOf(row.getCell(cellNum).getStringCellValue()).trim();
}
}
public static void compare2MapList(Map<String, List<String>> refMaplist, Map<String, List<String>> compareMaplist,
List<String> newAddlist, List<String> removelist, List<ArrayList<String>> updatelist)
{
/* Map<String, List<String>> difflist = new HashMap<>();
List<String> newAddlist = new ArrayList<>();
List<String> removelist = new ArrayList<>();
List<ArrayList<String>> updatelist =new ArrayList<ArrayList<String>>();
difflist.put("newAdd", newAddlist);
difflist.put("remove", removelist);*/
for (Map.Entry<String, List<String>> entry : refMaplist.entrySet())
{
//得到字段名
String reffieldName = entry.getKey();
//得到字段名对应的参考源列表
List<String> reffieldList = entry.getValue();
//得到字段名对应的比较源列表
List<String> comparefieldList = compareMaplist.get(reffieldName);
//1.相对于参考源新添加的字段
for (int i = 0; i < comparefieldList.size(); i++)
{
Boolean IsExist = reffieldList.contains(comparefieldList.get(i));
if (IsExist == false)
{
newAddlist.add(comparefieldList.get(i));
}
}
//2.相对于参考源移除的变量
for (int i = 0; i < reffieldList.size(); i++)
{
Boolean IsExist = comparefieldList.contains(reffieldList.get(i));
if (IsExist == false)
{
removelist.add(reffieldList.get(i));
}
}
//3.参考源,比较源中更改过的
//参考源中存在，比较源中不存在
List<String> AdiffBlist = getDiffABList(reffieldList, comparefieldList);
//比较源中存在，参考源中不存在
List<String> BdiffAlist = getDiffBAList(reffieldList, comparefieldList);
for (int i = 0; i < BdiffAlist.size(); i++)
{
String originBStr = getBeforeLastSeparatorStr(BdiffAlist.get(i));
for (int j = 0; j < AdiffBlist.size(); j++)
{
String originAStr = getBeforeLastSeparatorStr(AdiffBlist.get(j));
if (originBStr.equals(originAStr))
{
ArrayList<String> updateFieldlist = new ArrayList<String>();
//第一个为更改后的
updateFieldlist.add(BdiffAlist.get(i));
//第一个为参考源的
updateFieldlist.add(AdiffBlist.get(j));
updatelist.add(updateFieldlist);
}
}
}
}
}
public static String getBeforeLastSeparatorStr(String str)
{
String[] strings = str.split(Constants.FieldSeparator);
StringBuilder sb = new StringBuilder();
for (int i = 0; i < strings.length - 2; i++)
{
sb.append(strings[i]);
}
return sb.toString();
}
public static List<String> getDiffABList(List<String> listA, List<String> listB)
{
List<String> retainAllResult = new ArrayList<String>();
retainAllResult.addAll(listA);
retainAllResult.retainAll(listB);
List<String> diffABResult = new ArrayList<String>();
diffABResult.addAll(listA);
diffABResult.removeAll(retainAllResult);
return diffABResult;
}
public static List<String> getDiffBAList(List<String> listA, List<String> listB)
{
List<String> retainAllResult = new ArrayList<String>();
retainAllResult.addAll(listB);
retainAllResult.retainAll(listA);
List<String> diffABResult = new ArrayList<String>();
diffABResult.addAll(listB);
diffABResult.removeAll(retainAllResult);
return diffABResult;
}
public static List<String> getRetainABList(List<String> listA, List<String> listB)
{
List<String> retainAllResult = new ArrayList<String>();
retainAllResult.addAll(listA);
retainAllResult.retainAll(listB);
return retainAllResult;
}
public static void main(String[] args)
throws Exception
{
File file = new File("D:\\TestFile\\input.xlsx");
File file1 = new File("D:\\TestFile\\input1.xlsx");
InputStream inputStream = null;
InputStream inputStream1 = null;
try
{
inputStream = new FileInputStream(file);
inputStream1 = new FileInputStream(file1);
}
catch (FileNotFoundException e)
{
e.printStackTrace();
}
Map<String, List<String>> refMaplist = excelColumn2maplist(inputStream);
Map<String, List<String>> compareMaplist = excelColumn2maplist(inputStream1);
List<String> newAddlist = new ArrayList<>();
List<String> removelist = new ArrayList<>();
List<ArrayList<String>> updatelist = new ArrayList<>();
compare2MapList(refMaplist, compareMaplist, newAddlist, removelist, updatelist);
System.out.println(newAddlist);
System.out.println("*************************************");
System.out.println(removelist);
System.out.println("*************************************");
for (int i = 0; i < updatelist.size() - 1; i++)
{
System.out.println(updatelist.get(i));
}
String str1 =
"aggregation-mmpsourceservice<SpS>source_config.properties<SpS>youku.mobile.import.subtypes<SpS>大<SpS>";
String str2 =
"aggregation-mmpsourceservice<SpS>source_config.properties<SpS>youku.mobile.import.subtypes<SpS>大<SpS>";
String str11 = getBeforeLastSeparatorStr(str1);
String str22 = getBeforeLastSeparatorStr(str2);
System.out.println("---------------------------------");
System.out.println(str11);
System.out.println("---------------------------------");
System.out.println(str22);
}
