如何检查excel列标题在apache poi中是否按特定顺序排列

Question

我必须检查某些列标题名称是否以特定顺序出现在 Excel 工作表中。这些名称是：名字、姓氏（可选）、电子邮件 ID、电话号码、地址（可选）。

我需要检查输入的 Excel 工作表是否具有必填列以及可选列的任意组合。Ex: First Name, Email Id, Phone No. (All mandatory ones) First Name, Email Id, Phone 总共可能有 4 种情况。

我已将列标题存储在数组列表中。（如果我的问题陈述有有效的方法，请告诉我！）。

            public void readAndUpload() throws FileNotFoundException, IOException 

{

    FileInputStream file = new FileInputStream(new File("path.."));

    HSSFWorkbook workbook = new HSSFWorkbook(file);

    HSSFSheet sheet = workbook.getSheetAt(0);

    HSSFRow row = sheet.getRow(0);

    Iterator<Cell> cellIterator = row.cellIterator();

    ArrayList<String> headerCell = new ArrayList<String>(5);

    while(cellIterator.hasNext())

    {

        Cell cell = cellIterator.next();

        headerCell.add(cell.getStringCellValue());

    }

    ArrayList<String> validHeaders = new ArrayList<>(Arrays.asList("First Name", "Email Id","Phone Number"));

    if(headerCell.contains(validHeaders))

    {

        //validation logic here

    }

    else

    {

        System.out.println("Mandatory fields not present");

    }

    file.close();

}

Answer 1

我从您的要求中了解到，您想要验证以下内容： 1. 如果 excel 中的标题是允许的标题（强制和可选）。2. 如果存在强制标头。3. 强制标头是否有序。这是一种解决方案。public class HeaderProblem {&nbsp; &nbsp; public static void main(String[] args) {&nbsp; &nbsp; &nbsp; &nbsp; List<String> headersFromExcel = Arrays.asList("EMail", "Phone", "Address", "Last Name", "First Name");&nbsp; &nbsp; &nbsp; &nbsp; System.out.println(validate(headersFromExcel));&nbsp; &nbsp; }&nbsp; &nbsp; private static boolean validate(List<String> headers) {&nbsp; &nbsp; &nbsp; &nbsp; List<String> mandatoryHeaders = Arrays.asList("First Name", "EMail", "Phone");&nbsp; &nbsp; &nbsp; &nbsp; List<String> optionalHeaders = Arrays.asList("Last Name", "Address");&nbsp; &nbsp; &nbsp; &nbsp; List<String> allHeaders = new ArrayList<>(mandatoryHeaders);&nbsp; &nbsp; &nbsp; &nbsp; allHeaders.addAll(optionalHeaders);&nbsp; &nbsp; &nbsp; &nbsp; Map<String, Integer> headerIndexMap = IntStream.range(0, headers.size())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .boxed()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .collect(Collectors.toMap(i -> headers.get(i), i -> i));&nbsp; &nbsp; &nbsp; &nbsp; if(!allHeaders.containsAll(headers)) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println("Some headers exist which are not allowed");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return false;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; if (!headers.containsAll(mandatoryHeaders)) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println("Mandatory headers are not present");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return false;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; System.out.println(mandatoryHeaders.stream()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .map(headerIndexMap::get)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .collect(Collectors.toList()));&nbsp; &nbsp; &nbsp; &nbsp; // Check if the manadatory headers are in order&nbsp; &nbsp; &nbsp; &nbsp; Integer result = mandatoryHeaders.stream()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .map(headerIndexMap::get)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .reduce(-1, (x, hi) -> x < hi ? hi : headers.size());&nbsp; &nbsp; &nbsp; &nbsp; if (result == headers.size()) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println("Mandatory headers are not in order");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return false;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; return true;&nbsp; &nbsp; }}在这里，我假设 headersFromExcel 字段是从 excel 中提取的标题列表。还有一件事，在您使用的代码中，if(headerCell.contains(validHeaders))在这里，您应该使用 containsAll() 而不是 contains()，因为 contains 会将传递的参数视为一个对象，并将与 ArrayList 的每个单独单元格进行比较，并且在您的情况下始终返回 false。